MySQL优化

数据准备：https://dev.mysql.com/doc/index-other.html 上的sakila数据库

数据库版本：MySQL 5.5.19

视频教程

1. SQL语句优化

1.1 开启慢查询日志

show variables like 'slow_query_log'; -- 查看是否开启慢查询日志

set global log_queries_not_using_indexes = on; -- 设置慢查询日志包括未设置索引的sql查询

set global long_query_time = 1; -- 设置慢查询日志时间为1s

set global slow_query_log = on; -- 开启慢查询日志

show variables like 'slow%'; -- 查看慢查询日志存储位置

MySQL慢查询日志格式

慢查询日志分析工具：mysqldumpslow、pt-query-digest

慢查询日志中发现有问题的sql：

1. 查询次数多，且每次查询占用时间长的sql。通常为pt-query-digest分析的前几个查询。
2. IO比较大的查询。注意pt-query-digest分析中的Rows examine项。
3. 未命中索引的sql。注意pt-query-digest分析中Rows examine与Rows send的对比。

1.2 explain查询分析SQL的执行计划

explain extended SELECT …：运行SHOW WARNINGS 可得到被MySQL优化器优化后的查询语句。

执行计划包含的信息

1. id：表示查询中执行select子句或操作表的顺序

   执行计划id

   id相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行。

2. select_type：表示查询中每个select子句的类型

   • simple：查询中不包含子查询或者UNION
   • primary：包含union操作或者子查询的select，最外层查询则被标记为PRIMARY
   • union：若第二个SELECT出现在UNION之后，则被标记为UNION
   • derived：from列表中包含的子查询被标记为DERIVED
   • subquery：除了from列表中包含的子查询外，其他地方出现的子查询都可能是subquery，SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY

   • union result：包含union的结果集，因为它不需要参与查询，所以id字段为null

     
     union result

3. table：显示的查询表名

   • 如果查询使用了别名，那么这里显示的是别名
   • 如果不涉及对数据表的操作，那么这显示为null
   • 如果显示为尖括号括起来的<derived N>，就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生
   • 如果是尖括号括起来的<union M,N>，与<derived N>类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集

4. partitions：这列是建立在表是分区表上

5. type：查询结果类型

   type优劣顺序

   • NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引

     
     NULL实例

   • const：使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。system是const类型的特例，当查询的表只有一行的情况下，使用system

     
     const实例

     # 单一主键
     SELECT * FROM tbl_name WHERE primary_key=1;
     # 联合主键
     SELECT * FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2;
     

   • eq-ref：出现在要连接表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为not null，唯一索引和主键如果是多列时，只有所有的列都用作比较时才会出现eq_ref。触发条件：只匹配到一行的时候。

     
     eq-ref实例

     # 多表关联查询，单行匹配
     SELECT * FROM ref_table,other_table
     WHERE ref_table.key_column=other_table.column;
     
     # 多表关联查询，联合索引，多行匹配
     SELECT * FROM ref_table,other_table
     WHERE ref_table.key_column_part1=other_table.column
     AND ref_table.key_column_part2=1;
     

   • ref：非唯一性索引扫描，返回匹配某个单独值的所有行。常见于唯一索引的非唯一前缀进行的查找。与eq_ref不同的是匹配到了多行。

     
     ref实例

     # 根据索引（非主键，非唯一索引），匹配到多行
     SELECT * FROM ref_table WHERE key_column=expr;
     
     # 多表关联查询，单个索引，多行匹配
     SELECT * FROM ref_table,other_table
     WHERE ref_table.key_column=other_table.column;
     
     # 多表关联查询，联合索引，多行匹配
     SELECT * FROM ref_table,other_table
     WHERE ref_table.key_column_part1=other_table.column
     AND ref_table.key_column_part2=1;
     

   • ref_or_null：类似于ref，但是可以搜索包含null值的行，实际用的不多

     
     ref_or_null

   • index_merge：出现在使用一张表中的多个索引时，mysql会将这多个索引合并到一起。官方排序这个在ref_or_null之后，实际上由于要读取所个索引，性能可能大部分时间都不如range

     
     index_merge

   • range：索引范围扫描，常见于使用>, <, is null, between, in, like等运算符的查询中

     
     range实例

     # 范围查找
     SELECT * FROM tbl_name
     WHERE key_column BETWEEN 10 and 20;
     
     # 范围查找
     SELECT * FROM tbl_name
     WHERE key_column IN (10,20,30);
     
     # 多条件加范围查找
     SELECT * FROM tbl_name
     WHERE key_part1 = 10 AND key_part2 IN (10,20,30);
     

   • index：Full Index Scan，index与ALL区别为index类型只遍历索引树。常见于使用索引列就可以处理不需要读取数据文件的查询

     
     index

     index类型和ALL类型一样，区别就是index类型是扫描的索引树。以下两种情况会触发：

     1. 如果索引是查询的覆盖索引，就是说索引查询的数据可以满足查询中所需的所有数据，则只扫描索引树，不需要回表查询。 在这种情况下，explain 的 Extra 列的结果是 Using index。仅索引扫描通常比ALL快，因为索引的大小通常小于表数据。
     2. 全表扫描会按索引的顺序来查找数据行。使用索引不会出现在Extra列中。

   • ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行。这个就是全表扫描数据文件，然后在server层进行过滤返回符合要求的记录。

     
     ALL

6. possible_key：查询可能使用到的索引都会在这里列出来。

7. key：查询真正使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。

8. key_len：用于处理查询的索引长度，如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的列，这里不会计算进去。key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。

9. ref：指出哪些列或常量被用于查找索引列上的值

   • 如果是使用的常数等值查询，这里会显示const
   • 如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段
   • 如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

10. rows：这里是执行计划中估算的扫描行数，不是精确值

11. filtered：这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例

12. extra：有几十种，常用的有：

    • distinct：在select部分使用了distinc关键字
    • no tables used：不带from子句的查询或者from dual查询
    • using filesort：排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中。MySQL中无法利用索引完成的排序操作称为“文件排序”
    • using index：查询时不需要回表查询，直接通过索引就可以获取查询的数据。即使用了覆盖索引。
    • using intersect：表示使用and连接各个索引的条件时，该信息表示从处理结果获取交集
    • using union：表示使用or连接各个使用索引的条件时，该信息表示从处理结果获取并集
    • using temporary：表示使用了临时表存储中间结果。临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量，used_tmp_table，used_tmp_disk_table才能看出来。常见于排序和分组查询。
    • using where：表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤。

对查询性能影响最大的几个列是：

1. select_type：查询类型
2. type：连接使用了何种类型
3. rows：查询数据需要查询的行
4. key：查询真正使用到的索引
5. extra：额外的信息

尽量让自己的SQL用上索引，避免让extra里面出现file sort(文件排序),using temporary(使用临时表)。

MySQL执行计划的局限：

1. explain不会告诉关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
2. explain不考虑各种Cache
3. explain不能显示MySQL在执行查询时所作的优化工作
4. 部分统计信息是估算的，并非精确值
5. explain只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划

1.3 具体优化

1. count()、max()优化

   max(colA)：在colA上加索引优化。

   count()与count(colA)：count()包含NULL的统计。

   1. 任何情况下select count(*) from table 最优选择
   2. 杜绝select count(colunm) from table

   实践中发现：如果数据库没有主键，count(1) 比count() 快，如果有主键，且主键作为条件，那么count(1) 比count() 快。如果表里面只有一个字段那么是count(*)最快。

2. 子查询优化

   通常情况下，把子查询优化成join查询。注意一对多时，可能出现数据重复。

3. group by优化

   原sql：

   select actor.name, count(*)
   from film_actor inner join actor using(actor_id)
   group by film_actor.actor_id;
   

   优化后的sql：连接中使用子查询

   select actor.name, c.cnt
   from actor inner join
       (select actor_id, count(*) as cnt) from film_actor group by actor_id) as c using(actor_id);
   

4. limit优化

   原sql：

   select film_id, description from film order by title limit 50, 5;
   

   优化步骤1：使用有索引的列或主键进行order by操作

   select film_id, description from film order by film_id limit 50, 5;
   

   上面sql虽然只需要5行数据，但会扫描55行。进一步优化见下面：

   优化步骤2：记录上次返回的主键，在下次查询时使用主键过滤

   select film_id, description from film where film_id > 55 and film_id <= 60
   order by film_id limit 50, 5;
   

   上面sql只扫描5行。

5. distinct优化

   尽量不要使用distinct，可使用加索引、group by代替，具体情况具体分析。

2. 索引优化

1. 建立合适的索引：

   1. 在where、group by、order by、on中出现的列加索引，某些时候需要select中的列也加索引，即使用覆盖索引。
   2. 索引字段越小越好。以页为单位存储，索引字段小，页中存储的数据多，一次IO获取的数据行越大，效率更高。
   3. 离散度大（可选择性更高）的列放在联合索引的前面。

   索引会加快查询效率，但是会减小写入效率。

2. 删除重复、冗余索引：使用工具查找重复、冗余索引。

   pt-duplicate-key-checker \
   -uroot \
   -p '实际的密码' \
   -h 127.0.0.1
   

3. 删除不用的索引

3. 数据库结构优化

1. 选择数据类型只要遵循“小而简单”的原则就好，越小的数据类型通常会更快，占用更少的磁盘、内存，处理时需要的CPU周期也更少。越简单的数据类型在计算时需要更少的CPU周期，比如，整型就比字符操作代价低，因而会使用整型来存储ip地址，使用DATETIME或者INT（更好）来存储时间，而不是使用字符串：

   1. 计划在列上创建索引，就应该将该列设置为NOT NULL。（NULL 其实并不是空值，而是要占用空间，所以mysql在进行比较的时候，NULL 会参与字段比较，所以对效率有一部分影响。B树索引时不会存储NULL值，所以如果索引的字段可以为NULL，索引的效率会下降很多。）
   2. 对整数类型指定宽度，比如INT(11)，没有任何卵用。INT使用32位（4个字节）存储空间，那么它的表示范围已经确定，所以INT(1)和INT(20)对于存储和计算是相同的。
   3. UNSIGNED表示不允许负值，大致可以使正数的上限提高一倍。比如TINYINT存储范围是-128 ~ 127，而UNSIGNED TINYINT存储的范围却是0 - 255。
   4. 通常来讲，没有太大的必要使用DECIMAL数据类型。即使是在需要存储财务数据时，仍然可以使用BIGINT。比如需要精确到万分之一，那么可以将数据乘以一百万然后使用BIGINT存储。这样可以避免浮点数计算不准确和DECIMAL精确计算代价高的问题。
   5. TIMESTAMP使用4个字节存储空间，DATETIME使用8个字节存储空间。因而，TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的范围小得多，而且TIMESTAMP的值因时区不同而不同。
   6. 大多数情况下没有使用枚举类型的必要，其中一个缺点是枚举的字符串列表是固定的，添加和删除字符串（枚举选项）必须使用ALTER TABLE（如果只是在列表末尾追加元素，不需要重建表）
   7. schema的列不要太多。原因是存储引擎的API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列，这个转换过程的代价是非常高的。如果列太多而实际使用的列又很少的话，有可能会导致CPU占用过高。
   8. 大表ALTER TABLE非常耗时，MySQL执行大部分修改表结果操作的方法是用新的结构创建一个张空表，从旧表中查出所有的数据插入新表，然后再删除旧表。尤其当内存不足而表又很大，而且还有很大索引的情况下，耗时更久。

   总结下，可以记住下面几个：

   1. 使用可以存下数据的最小的数据类型：int存时间、bigint存ip（INET_ATON、INET_NTOA）
   2. 使用简单的数据类型。int要比varchar在mysql处理上简单
   3. 尽可能使用not null定义字段
   4. 尽量少用text类型，非用不可时最好考虑分表

2. 范式优化

   符合第三范式

3. 反范式优化

   增加冗余，以空间换时间

4. 垂直拆分

   把一个字段多的表拆分成几个表（不常用的字段放在一个表中，大字段放在一个表中，经常一起用的字段放在一个表中）

5. 水平拆分

4. 系统配置优化

1. 操作系统配置优化（增加tcp连接数、修改最大打开文件数量）
2. MySQL配置文件优化（InnoDB缓冲池大小）

5. 服务器硬件优化