MySQL优化
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：
- 查询次数多，且每次查询占用时间长的sql。通常为pt-query-digest分析的前几个查询。
- IO比较大的查询。注意pt-query-digest分析中的Rows examine项。
- 未命中索引的sql。注意pt-query-digest分析中Rows examine与Rows send的对比。
1.2 explain查询分析SQL的执行计划
explain extended SELECT …：运行SHOW WARNINGS 可得到被MySQL优化器优化后的查询语句。
执行计划包含的信息
-
id：表示查询中执行select子句或操作表的顺序
执行计划id
id相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行。
-
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
-
table：显示的查询表名
- 如果查询使用了别名，那么这里显示的是别名
- 如果不涉及对数据表的操作，那么这显示为null
- 如果显示为尖括号括起来的<derived N>，就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生
- 如果是尖括号括起来的<union M,N>，与<derived N>类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集
-
partitions：这列是建立在表是分区表上
-
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类型是扫描的索引树。以下两种情况会触发：
- 如果索引是查询的覆盖索引，就是说索引查询的数据可以满足查询中所需的所有数据，则只扫描索引树，不需要回表查询。 在这种情况下，explain 的 Extra 列的结果是 Using index。仅索引扫描通常比ALL快，因为索引的大小通常小于表数据。
- 全表扫描会按索引的顺序来查找数据行。使用索引不会出现在Extra列中。
-
ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行。这个就是全表扫描数据文件，然后在server层进行过滤返回符合要求的记录。
ALL
-
-
possible_key：查询可能使用到的索引都会在这里列出来。
-
key：查询真正使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。
-
key_len：用于处理查询的索引长度，如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的列，这里不会计算进去。key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。
-
ref：指出哪些列或常量被用于查找索引列上的值
- 如果是使用的常数等值查询，这里会显示const
- 如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段
- 如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func
-
rows：这里是执行计划中估算的扫描行数，不是精确值
-
filtered：这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例
-
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层进行过滤。
对查询性能影响最大的几个列是：
- select_type：查询类型
- type：连接使用了何种类型
- rows：查询数据需要查询的行
- key：查询真正使用到的索引
- extra：额外的信息
尽量让自己的SQL用上索引，避免让extra里面出现file sort(文件排序),using temporary(使用临时表)。
MySQL执行计划的局限：
- explain不会告诉关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
- explain不考虑各种Cache
- explain不能显示MySQL在执行查询时所作的优化工作
- 部分统计信息是估算的，并非精确值
- explain只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划
1.3 具体优化
-
count()、max()优化
max(colA)：在colA上加索引优化。
count()与count(colA)：count()包含NULL的统计。
- 任何情况下select count(*) from table 最优选择
- 杜绝select count(colunm) from table
实践中发现：如果数据库没有主键，count(1) 比count() 快，如果有主键，且主键作为条件，那么count(1) 比count() 快。如果表里面只有一个字段那么是count(*)最快。
-
子查询优化
通常情况下，把子查询优化成join查询。注意一对多时，可能出现数据重复。
-
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); -
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行。
-
distinct优化
尽量不要使用distinct，可使用加索引、group by代替，具体情况具体分析。
2. 索引优化
-
建立合适的索引：
- 在where、group by、order by、on中出现的列加索引，某些时候需要select中的列也加索引，即使用覆盖索引。
- 索引字段越小越好。以页为单位存储，索引字段小，页中存储的数据多，一次IO获取的数据行越大，效率更高。
- 离散度大（可选择性更高）的列放在联合索引的前面。
索引会加快查询效率，但是会减小写入效率。
-
删除重复、冗余索引：使用工具查找重复、冗余索引。
pt-duplicate-key-checker \ -uroot \ -p '实际的密码' \ -h 127.0.0.1 -
删除不用的索引
3. 数据库结构优化
-
选择数据类型只要遵循“小而简单”的原则就好，越小的数据类型通常会更快，占用更少的磁盘、内存，处理时需要的CPU周期也更少。越简单的数据类型在计算时需要更少的CPU周期，比如，整型就比字符操作代价低，因而会使用整型来存储ip地址，使用DATETIME或者INT（更好）来存储时间，而不是使用字符串：
- 计划在列上创建索引，就应该将该列设置为NOT NULL。（NULL 其实并不是空值，而是要占用空间，所以mysql在进行比较的时候，NULL 会参与字段比较，所以对效率有一部分影响。B树索引时不会存储NULL值，所以如果索引的字段可以为NULL，索引的效率会下降很多。）
- 对整数类型指定宽度，比如INT(11)，没有任何卵用。INT使用32位（4个字节）存储空间，那么它的表示范围已经确定，所以INT(1)和INT(20)对于存储和计算是相同的。
- UNSIGNED表示不允许负值，大致可以使正数的上限提高一倍。比如TINYINT存储范围是-128 ~ 127，而UNSIGNED TINYINT存储的范围却是0 - 255。
- 通常来讲，没有太大的必要使用DECIMAL数据类型。即使是在需要存储财务数据时，仍然可以使用BIGINT。比如需要精确到万分之一，那么可以将数据乘以一百万然后使用BIGINT存储。这样可以避免浮点数计算不准确和DECIMAL精确计算代价高的问题。
- TIMESTAMP使用4个字节存储空间，DATETIME使用8个字节存储空间。因而，TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的范围小得多，而且TIMESTAMP的值因时区不同而不同。
- 大多数情况下没有使用枚举类型的必要，其中一个缺点是枚举的字符串列表是固定的，添加和删除字符串（枚举选项）必须使用ALTER TABLE（如果只是在列表末尾追加元素，不需要重建表）
- schema的列不要太多。原因是存储引擎的API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列，这个转换过程的代价是非常高的。如果列太多而实际使用的列又很少的话，有可能会导致CPU占用过高。
- 大表ALTER TABLE非常耗时，MySQL执行大部分修改表结果操作的方法是用新的结构创建一个张空表，从旧表中查出所有的数据插入新表，然后再删除旧表。尤其当内存不足而表又很大，而且还有很大索引的情况下，耗时更久。
总结下，可以记住下面几个：
- 使用可以存下数据的最小的数据类型：int存时间、bigint存ip（INET_ATON、INET_NTOA）
- 使用简单的数据类型。int要比varchar在mysql处理上简单
- 尽可能使用not null定义字段
- 尽量少用text类型，非用不可时最好考虑分表
-
范式优化
符合第三范式
-
反范式优化
增加冗余，以空间换时间
-
垂直拆分
把一个字段多的表拆分成几个表（不常用的字段放在一个表中，大字段放在一个表中，经常一起用的字段放在一个表中）
-
水平拆分
4. 系统配置优化
- 操作系统配置优化（增加tcp连接数、修改最大打开文件数量）
- MySQL配置文件优化（InnoDB缓冲池大小）
