MySQL性能优化相关知识整理

MySQL性能优化

MySQL的日志文件

（1）binlog二进制日志文件：5.1之前，所有的binlog都是基于SQL语句级别的。应用这种格式的binlog进行数据恢复时，如果SQL语句带有rand或uuid函数，恢复出来的数据会出现数据不一致问题。5.1之后，引入了binlog_format参数。这个参数有可能选值statement和row，statement就是之前5.1的格式。row记录则是行更改的情况，可以避免之前出现的数据不一致的问题。

做主从复制时，就需要使用row格式。并且还需要借助mysqlbinlog工具来解析和查看binlog中的内容。如果需要用binlog来恢复数据，通常就是用mysqlbinlog工具把binlog中的内容解析出来，然后把解析结果整个发给MYSQL执行。

（2）redolog重做日志文件：ib_logfile0、ib_logfile1事InnoDB引擎特有的，用于记录InnoDB引擎下事务的日志，它记录每页更改的物理情况。

这里需要明白一件事：有了binlog,为什么还需要redolog？

因为两者分工不同，binlog主要用来做数据归档，但是并不具备崩溃恢复的能力，也就是说如果系统崩溃，重启后可能有部分数据会丢失。InnoDB将对页面的所有操作写入redolog，并在数据库启动时从此文件进行恢复操作。

这里需要注意的一个配置是：innodb log，当设置过大时，可能会导致系统崩溃后恢复需要很长时间，设置过小时，在一个事务产生大量日志时，需要多次切换重做日志文件。

（3）undolog:回滚日志，记录事务性操作修改数据之前的信息。

（4）errorlog:错误日志，记录MySQL Server每次启动和关闭的详细信息，以及运行过程中所有较为严重的警告和错误信息。

（5）slow.log：慢查询日志：记录运行时间超过long_query_time值的SQL。

（6）general_log:记录建立的client连接和运行的语句。

InnoDB锁机制的介绍

InnoDB的行级锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据才使用。在不通过索引条件查询的时候，InnoDB使用的是表级锁，而不是行级锁。

InnoDB行级锁的种类分为：

（1）单个行记录的记录锁（record locks）：锁定索引中的一条记录，比如id = 5

（2）区间锁/间隙锁（Gap locks）:锁定一个范围，锁定在第一个或最后一个索引记录之前的区间上。

在MySQL的repeatable-read这个事务隔离级别，为了避免幻读现象，引入了区间锁，他只锁定行记录数据的范围，不包含记录本身，不允许在此范围内插上任何数据。

如：select * from t2 where score < 80 lock in share mode.在上面加入了一个共享锁。

在另一个事务中，往t2表插入score = 74的数据，就不会插入成功，出现了锁超时，因为在score < 80 的这个区间内，不允许有任何数据插入，区间锁的功能得以体现。但如果插入score = 90的数据是成功的，因为不在这个区间内。

（3）Next-key lock:上面两种的结合。当InnoDB扫描索引记录时，会先对选中的索引记录加上记录锁，再对索引记录两边的区间加上区间锁。

等待锁和死锁

锁等待是指一个事务过程中产生的锁，其他事务需要等待上一个事务释放它的锁才能占用该资源。如果该事务一直不释放，就需要持续等待下去，直到超过了锁等待时间，会报一个等待超时的错误。

在MySQL中，两个或两个以上的事务相互持有和请求锁，并形成一个循环的依赖关系，就会产生死锁，也就是锁资源请求产生了死循环的现象。innodb会自动检测死锁，立即回滚其中某个事务，并且返回一个错误。innodb存储引擎有一个后台的锁监控线程，g该线程负责查看可能的死锁问题，并自动告知用户。

当两个或多个事务相互持有对方需要的锁时，就会产生死锁，如何避免死锁？

（1）不同程序会并发存取多个表或者涉及多行记录时，尽量约定以相同的顺序访问表，可以大大降低死锁的机会。

（2）对应用程序进行调整，在某些情况下，把大事务分解成多个小事务，使得锁能够更快被释放，及时提交或者回滚事务，可以减少死锁发生的概率。

（3）同一个事务中，尽可能做到 一次锁定所需要的资源，减少死锁发生的概率。

（4）为表添加合理的索引，不使用索引将会对表的每一行记录上锁，死锁概率就会增加。

（5）对于非常容易产生死锁的业务部分，可以尝试使用升级锁粒度，通过表锁定来减少死锁产生的概率。

锁问题的监控

show engine innodb status 查看是否存在锁表情况

通过 select * from information_schema.INNODB_LOCKS\G;查看锁情况

通过 select * from information_schema.INNODB_LOCKS_waits\G;查看锁阻塞情况

SQL语句性能优化

MySQL查询过程：

（1）客户端向MySQL服务器发送一条查询请求。

（2）服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果，否则进入（3）.

（3）服务器进行SQL解析，预处理，再由优化器生成对应的执行计划。

（4）MySQL根据执行计划，调用存储引擎的API来执行查询

（5）将结果返回给客户端。即使查询不需要返回结果给客户端，MySQL仍然会返回这个查询的一些信息，比如查询影响到的行数。

注意：

查询尽量简单而且只返回必须的数据—减少通信间数据包的大小和数量，降低不必要的I/O，因此尽量避免select * 以及加上limit限制。

查询缓存是通过query cache进行操作。query cache 和 buffer pool 缓存机制有很大区别：

query cache 缓存的是SQL语句及对应的结果集，缓存在内存。（两个查询语句在任何字符上稍微有点不同，如空格，注释，都会导致query cache缓存不会命中），并且查询语句中包含任何不确定的函数（如now(),current_data(））器查询结果都不会被缓存。

buffer pool中缓存的是整张表中的数据，缓存在内存，即使SQL语句怎么变，只要数据都在内存，那么命中率就是1.

既然是缓存，那么缓存结果何时失效呐？

对于querycache这个查询缓存来说，如果数据表被更改，那么和这个表相关的全部cache都会无效并删除。对于密集写的操作，启用查询缓存后很可能会造成频繁的缓存失效，间接引发内存激增及CPU飙升。因此通常建议禁用query cache。

常见高性能索引

使用索引----提高查询效率，本质：不断缩小获取数据的筛选范围，同时把随机事件变成顺序事件，找出我们想要锁定的数据。

索引往往以索引文件的形式存储在磁盘上。因此索引查找的过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级。减少磁盘I/O及必须压缩树的高度。

innodb使用的是B+Tree,该结构尽量减少了查找过程中磁盘I/O的存取次数，只在叶子节点存储数据，所有叶子节点包含一个链指针；其他内层非叶子节点只存储索引数据；只利用索引快速定位数据索引范围，先定位索引，再通过索引高效，快速定位数据。

为表设置索引是要付出代价的：一是增加了数据库的存储空间，而是在插入和修改数据时要花费较多的时间，因为索引也要随之变动。

聚集索引和辅助索引（二级索引，非聚集索引）

数据库中的B+Tree索引可以分为聚集索引和非聚集索引，不同在于叶子节点存放的是否是一整行的记录数据，非聚集索引的叶子节点并不包含行记录的全部数据，而是存储行数据的聚集索引键，即主键。当通过非聚集索引来查询数据时，innodb存储引擎会遍历非聚集索引找到主键，然后通过主键在聚集索引中找到完整的行记录数据。

慢SQL语句优化思路

对慢SQL语句优化一般可以按下面几步思路：

开启慢查询日志，通过设置超过几秒（默认10s）为慢SQL语句，抓取慢SQL语句；

通过explain查看执行计划，对慢SQL语句分析；

创建索引并调整语句，再查看执行计划，对比调优结果。

抓取慢SQL语句

使用mysqldumpslow进行分析

命令为 mysqldumpslow -t 10 /data/mysql/mysql-slow.log

利用explain分析查询语句

explain命令是查看优化器如何决定执行查询的主要方法，比如是全表索引还是索引扫描，从而知道MySQL如何处理SQL语句以及查询语句是否走了合理的索引。

使用explain，只需要在查询中的select关键字之前增加explain这个词即可，MySQL会在查询上设置一个标记，当执行查询时返回关于执行计划中的每一步信息，而不是执行它。

explain命令生成执行计划：首先关注查询类型type，如果出现all关键字，代表全表扫描，没有用到任何index；再看key列，如果时null，代表没有使用索引；然后再看rows列，该列数值越大意味着需要扫描的行数越多，相应耗时越长；最后看extra列，要避免出现using file sort （索引失效）或 using temporary（需要使用临时表存储结果）这样的字眼，影响性能。

索引使用的原则

（1）表一定要有主键，显示定义且与业务无关，采用自增列使数据顺序插入.

（2）经常被查询的列，经常用于表连接的列，经常排序分组的列，需要创建索引。

（3）创建索引之前，还要查看索引的选择性（不重复的索引值和表的记录总数的比值），越接近1越适合创建索引。

（4）组合索引字段数不建议超过5个。

（5）合理利用覆盖索引，只获取必要字段，指定字段能有效利用索引覆盖。

（6）使用explain判断SQL语句是否合理使用了索引等。

（7）单张表的索引建议控制在5个以内

（8）不建议在频繁更新的字段上上建立索引

（9）避免在where条件中在索引列上进行计算或使用函数，会导致索引不被使用而进行全表扫描

（10）要进行join查询，那么join的字段必须类型相同并建立索引，否则进行全表扫描。

（11）隐式类型转换会使索引失效，导致全表扫描。

MySQL配置参数优化

max_connections:最大连接数，默认151

query_cache_type = 0,query_cache_size = 0:关闭查询缓存

innodb_io_capacity = 3000,innodb_io_capacity_max = 6000(对于服务器有SSD硬盘)：从缓存区刷新脏页时，一次刷新脏页的数量。

innodb_log_file_size:设置redo log的大小，2G

innodb_flush_method = fdatasync(默认)：决定数据和日志刷新到磁盘的方式

innodb_max_dirty_pages_pct:脏页占innodb buffer pool的比例，影响每秒刷新脏页的数量。建议设为50，当脏块达到innodb_buffer_pool_size的50%时触发检查点，写磁盘。

binlog_format = row,让数据更加安全可靠，主从复制过程中不会丢失数据。

lepus数据库监控管理系统（天兔）