深入浅出MySQL之优化

深入浅出MySQL之优化

最近经常问别人MySQL优化，发现大家对这块熟悉，但是不够了解。因此反思总结下，MySQL优化，会有哪些方面呢？本文将其分成应用优化，查询缓存优化，内存管理优化，并发参数调整和锁问题，以及常用MySQL技巧

1. 应用优化

在实际生产环境中，由于数据库本身的性能局限，就必须要对 前台的应用进行一些优化，来降低数据库的访问压力。

1. 1. 使用连接池

对于访问数据库来说，建立连接的代价是比较昂贵的，因为我们频繁的创建关闭连接，是比较耗费资源的，我们有 必要建立 数据库连接池，以提高访问的性能。

1. 1. 减少MySQL的访问

避免对数据进行重复检索，比如查一次能获取所需字段，就不要分多次查询，减少对数据库的无用重复请求。

增加cache层

我们在应用中可以增加缓存来减轻数据库负担，缓存层有多种，也有很多实现方式。因此可以部分数据从数据库中抽取出来放到应用端以文本方式存储， 或者使用框架(Mybatis, Hibernate)提供的一 级缓存/二级缓存，或者使用redis数据库来缓存数据。

1. 1. 负载均衡

负载均衡是应用中使用非常普遍的一种优化方法，它的机制就是利用某种均衡算法，将固定的负载量分布到不同的服务器上， 以此来降低单台服务器的负载，达到优化的效果。

利用MySQL复制分流查询。通过MySQL的主从复制，实现读写分离，使增删改操作走主节点，查询操作走从节点，从而可以降低单台服务器的 读写压力。

采用分布式数据库架构。分布式数据库架构适合大数据量、负载高的情况，它有良好的拓展性和高可用性。通过在多台服务器之间分布数 据，可以实现在多台服务器之间的负载均衡，提高访问效率。

1. MySQL中查询缓存优化
   1. 开启Mysql的查询缓存，当执行完全相同的SQL语句的时候，服务器就会直接从缓存中读取结果，当数据被修改，之前的缓存会失效，修改比较频繁的表不适合做查询缓存。MySQL的查询缓存默认是关闭的，需要手动配置参数 query_cache_type ， 来开启查询缓存。

查询缓存select选项。可以在SELECT语句中指定两个与查询缓存相关的选项 ：

SQL_CACHE : 如果查询结果是可缓存的，并且 query_cache_type 系统变量的值为ON或 DEMAND ，则缓存查询结果 。

SQL_NO_CACHE : 服务器不使用查询缓存。它既不检查查询缓存，也不检查结果是否已缓存，也不缓存查询结果。

例子：

查询缓存失效有下列几种：

1. SQL语句不一致。
2. 查询语句有些不确定时，不会缓存。如：now（），uuid（），current_date()等部分函数。
3. 不使用任何表查询。
4. 查询mysql，information_schema或者performance_schema数据库中的表，不会走缓存。
5. 在存储过程，触发器或者事件主体中执行查询。
6. 如果表更改，则使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除。

1. 内存管理优化

MySQL内存优化原则：

1） 将尽量多的内存分配给MySQL做缓存，但要给操作系统和其他程序预留足够内存。

2） MyISAM 存储引擎的数据文件读取依赖于操作系统自身的IO缓存，因此，如果有MyISAM表，就要预留更多的 内存给操作系统做IO缓存。

3） 排序区、连接区等缓存是分配给每个数据库会话（session）专用的，其默认值的设置要根据最大连接数合理 分配，如果设置太大，不但浪费资源，而且在并发连接较高时会导致物理内存耗尽。

InnoDB内存优化：

nnodb用一块内存区做IO缓存池，该缓存池不仅用来缓存innodb的索引块，而且也用来缓存innodb的数据块。

Innodb_buffer_pool_size

       该变量决定了 innodb 存储引擎表数据和索引数据的最大缓存区大小。在保证操作系统及其他程序有足够内存可用的情况下，innodb_buffer_pool_size 的值越大，缓存命中率越高，访问InnoDB表需要的磁盘I/O 就越少，性能也就越高。

Innodb_log_buffer_size

       决定了innodb重做日志缓存的大小，对于可能产生大量更新记录的大事务，增加innodb_log_buffer_size的大小，可以避免innodb在事务提交前就执行不必要的日志写入磁盘操作。

1. 并发参数调整和锁问题

从实现上来说，MySQL Server是多线程结构，包括后台线程和客户服务线程。多线程可以有效利用服务器资源，提高数据库的并发性能。在Mysql中，控制并发连接和线程的主要参数包括 max_connections、back_log、 thread_cache_size、table_open_cahce。

1. 1. max_connections

采用max_connections 控制允许连接到MySQL数据库的最大数量，默认值是 151。如果状态变量 connection_errors_max_connections 不为零，并且一直增长，则说明不断有连接请求因数据库连接数已达到允许最大值而失败，这是可以考虑增大max_connections 的值。

Mysql 最大可支持的连接数，取决于很多因素，包括给定操作系统平台的线程库的质量、内存大小、每个连接的负荷、CPU的处理速度，期望的响应时间等。在Linux 平台下，性能好的服务器，支持 500-1000 个连接不是难事，需要根据服务器性能进行评估设定。

1. 1. back_log

back_log 参数控制MySQL监听TCP端口时设置的积压请求栈大小。如果MySql的连接数达到max_connections时， 新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过 back_log，将不被授予连接资源，将会报错。5.6.6 版本之前默认值为 50 ， 之后的版本默认为 50 +（max_connections / 5）， 但最大不超过900。如果需要数据库在较短的时间内处理大量连接请求。可以考虑适当增大back_log 的值。  

1. 1. table_open_cache

该参数用来控制所有SQL语句执行线程可打开表缓存的数量， 而在执行SQL语句时，每一个SQL执行线程至少要打开 1个表缓存。该参数的值应该根据设置的最大连接数 max_connections 以及每个连接执行关联查询中涉及的表的最大数量来设定 ：

max_connections x N ；

1. 1. thread_cache_size

为了加快连接数据库的速度，MySQL 会缓存一定数量的客户服务线程以备重用，通过参数 thread_cache_size 可 控制 MySQL 缓存客户服务线程的数量。

1. 1. innodb_lock_wait_timeout

该参数是用来设置InnoDB 事务等待行锁的时间，默认值是50ms,可以根据需要进行动态设置。对于需要快速反馈的业务系统来说，可以将行锁的等待时间调小，以避免事务长时间挂起；对于后台运行的批量处理程序来说，可以将行锁的等待时间调，以避免发生大的回滚操作。

1. 锁问题

MySQL中的锁要详细了解，篇幅较大，本节大致概过。

锁是计算机协调多个进程或线程并发访问某一资源的机制（避免争抢）。在数据库中，除传统的计算资源（如 CPU、RAM、I/O 等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

锁分类：

从对数据操作的粒度分 ：

1） 表锁：操作时，会锁定整个表。

2） 行锁：操作时，会锁定当前操作行。

从对数据操作的类型分：

1. 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
2. 写锁（排它锁）：当前操作没有完成之前，它会阻断其他写锁和读锁。
   1. Innodb行锁

行锁特点 ：

偏向InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度 也最高。InnoDB 与 MyISAM 的最大不同有两点：一是支持事务；二是采用了行级锁。

InnoDB 实现了以下两种类型的行锁。

共享锁（S）：又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

排他锁（X）：又称为写锁，简称X锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他 锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行 读取和修改。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)； 对于普通SELECT语句，InnoDB不会加任何锁；

1. 1. 锁概述

InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面带来了性能损耗可能比表锁会更高一些，但是 在整体并发处理能力方面要远远由于MyISAM的表锁的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM 相比就会有比较明显的优势。

但是，InnoDB的行级锁同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不 能比MyISAM高，甚至可能会更差。

优化建议：

1）尽可能让所有数据检索都能通过索引来完成，避免无索引行锁升级为表锁。

2）合理设计索引，尽量缩小锁的范围。

3）尽可能减少索引条件，及索引范围，避免间隙锁。

4）尽量控制事务大小，减少锁定资源量和时间长度。

5）尽可使用低级别事务隔离（但是需要业务层面满足需求）。

6.    常用MySQL技巧

       SQL的执行顺序。举例如下：

       编写顺序：

       ELECT DISTINCT

           <select list>

FROM

           <left_table> <join_type> JOIN

           <right_table> ON <join_condition> WHERE

           <where_condition>

GROUP BY

           <group_by_list>

HAVING

           <having_condition>

ORDER BY

           <order_by_condition>

LIMIT

           <limit_params>

       执行顺序：

       FROM    <left_table>

ON      <join_condition>  <join_type>    

JOIN     <right_table>

WHERE   <where_condition>

GROUP BY    <group_by_list>

HAVING      <having_condition>

SELECT DISTINCT     <select list>

ORDER BY    <order_by_condition>

LIMIT       <limit_params>

正则表达式的运用，以及常用的函数，如数字函数，字符串函数和日期函数等。

总结：本篇文章大致说到一些工作中常常用到的优化技巧，可做参考，更细致请关注公众号，后续陆续推送。