MySQL优化（1）--------常用的优化步骤
在开始博客之前，还是同样的给一个大概的目录结构，实则即为一般MySQL的优化步骤
1、查看SQL的执行频率—————使用show status命令
2、定位哪些需要优化的SQL————通过慢查询记录+show processlist命令查看当前线程
3、分析为什么SQL执行效率低————使用explain/desc命令分析
- 相关列简单解释：type、table、select_type…
4、对症下药采取优化措施———–举例采取index进行优化
- 如何使用索引？
- 使用索引应该注意的事项
- 查看索引使用情况
主要参考资料：《深入浅出MySQL》，https://dev.mysql.com/doc/refman/8.0/en/statement-optimization.html
一、查看SQL执行频率
使用show [session|gobal] status命令了解SQL执行频率、线程缓存内的线程的数量、当前打开的连接的数量、获得的表的锁的次数等。
比如执行show status like \’Com_%\’查看每个语句执行的次数即频率，其中Com_xxx中xxx表示就是语句，比如Com_select：执行select操作的次数。
1 mysql> use test; 2 Database changed 3 mysql> show status like \'Com_%\'; 4 +-----------------------------+-------+ 5 | Variable_name | Value | 6 +-----------------------------+-------+ 7 | Com_admin_commands | 0 | 8 | Com_assign_to_keycache | 0 | 9 | Com_alter_db | 0 | 10 | Com_alter_db_upgrade | 0 | 11 | Com_alter_event | 0 | 12 | Com_alter_function | 0 | 13 | Com_alter_instance | 0 | 14 | Com_alter_procedure | 0 | 15 | Com_alter_server | 0 | 16 | Com_alter_table | 0 | 17 | Com_alter_tablespace | 0 | 18 | Com_alter_user | 0 | 19 | Com_analyze | 0 | 20 | Com_begin | 0 | 21 | Com_binlog | 0 | 22 | Com_call_procedure | 0 | 23 | Com_change_db | 2 | 24 | Com_change_master | 0 | 25 | Com_change_repl_filter | 0 | 26 | Com_check | 0 | 27 | Com_checksum | 0 | 28 | Com_commit | 0 | 29 | Com_create_db | 0 | 30 | Com_create_event | 0 | 31 | Com_create_function | 0 | 32 | Com_create_index | 0 |
..............................
比如执行show status like \’slow_queries\’查看慢查询次数（黑人问号？？什么是慢查询呢？就是通过设置查询时间阈值long_query_time（0-10s）并打开开关show_query_log（1=OFF/0=ON），当超过这个阈值的查询都称之为慢查询,通常用来划分执行SQL效率）
mysql> show status like \'slow_queries\'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Slow_queries | 0 | +---------------+-------+ 1 row in set
比如执行show status like \’uptime\’查看服务工作时间（即运行时间）：
mysql> show status like \'uptime\'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Uptime | 21645 | +---------------+-------+ 1 row in set
比如执行show status like \’connections\’查看MySQL连接数：
mysql> show status like \'connections\'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Connections | 6 | +---------------+-------+ 1 row in set
通过show [session|gobal] status命令很清楚地看到哪些SQL执行效率不如人意，但是具体是怎么个不如意法，还得继续往下看，使用EXPLAIN命令分析具体的SQL语句
二、定位效率低的SQL
上面也提到过慢查询这个概念主要是用来划分效率低的SQL，但是慢查询是在整个查询结束后才记录的，所以光是靠慢查询日志是跟踪不了效率低的SQL。一般有两种方式定位效率低的SQL：
1、通过慢查询日志查看效率低的SQL语句，慢查询日志是通过show_query_log_file指定存储路径的，里面记录所有超过long_query_time的SQL语句（关于日志的查看，日后再一步研究学习），但是需要慢查询日志的产生是在查询结束后才有的。
2、通过show processlist命令查看当前MySQL进行的线程，可以看到线程的状态信息
mysql> show processlist; +----+------+-----------------+------+---------+------+----------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------------+------+---------+------+----------+------------------+ | 2 | root | localhost:58377 | NULL | Sleep | 2091 | | NULL | | 3 | root | localhost:58382 | test | Sleep | 2083 | | NULL | | 4 | root | localhost:58386 | test | Sleep | 2082 | | NULL | | 5 | root | localhost:59092 | test | Query | 0 | starting | show processlist | +----+------+-----------------+------+---------+------+----------+------------------+ 4 rows in set
其中主要的是state字段，表示当前SQL语句线程的状态，如Sleeping 表示正在等待客户端发送新请求，Sending data把查询到的data结果发送给客户端等等，具体请看https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html
三、 查看分析效率低的SQL
MYSQL 5.6.3以前只能EXPLAIN SELECT; MYSQL5.6.3以后就可以EXPLAIN SELECT,UPDATE,DELETE，现在我们先创建一个user_table的表，之后分析select* from user where name=\’\’语句
mysql> create table user(id int, name varchar(10),password varchar(32),primary key(id))engine=InnoDB; Query OK, 0 rows affected
之后插入三条数据：
mysql> insert into user values(1,\'Zhangsan\',replace(UUID(),\'-\',\'\')),(2,\'Lisi\',replace(UUID(),\'-\',\'\')),(3,\'Wangwu\',replace(UUID(),\'-\',\'\')); Query OK, 3 rows affected Records: 3 Duplicates: 0 Warnings: 0 mysql> select* from user; +----+----------+----------------------------------+ | id | name | password | +----+----------+----------------------------------+ | 1 | Zhangsan | 2d7284808e5111e8af74201a060059ce | | 2 | Lisi | 2d73641c8e5111e8af74201a060059ce | | 3 | Wangwu | 2d73670c8e5111e8af74201a060059ce | +----+----------+----------------------------------+ 3 rows in set
下面以分析select*from user where name=\’Lisi\’语句为例：
mysql> explain select*from user where name=\'Lisi\'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set
下面讲解select_type等常见列的含义的：
（1）select_type：表示SELECT的类型，主要有：
- SIMPLE：简单表，没有表连接或者子查询
- PRIMARY：主查询，即最外城的查询
- UNION：UNION中的第二个或者后面的语句
- SUBQUERY：子查询中的第一个SELECT
（2）table：结果输出的表
（3）type：表示表的连接类型，性能由好到差为：
- system：常量表
- const：单表中最多有一行匹配，比如primary key，unique index
- eq_ref：多表连接中使用primary key，unique index
- ref：使用普通索引
- ref_or_null：与ref类似，但是包含了NULL查询
- index_merge：索引合并优化
- unique_subquery：in后面是一个查询主键字段的子查询
- index_subquery：in后面是非唯一索引字段的子查询
- range：单表中范围查看，使用like模糊查询
- index：对于后面每一行都通过查询索引得到数据
- all：表示全表查询
（3）possible_key：查询时可能使用的索引
（4）key：表示实际使用的索引
（5）key_len：索引字段的长度
（6）rows：查询时实际扫描的行数
（7）Extra：执行情况的说明和描述
（8）partitions：分区数目
（9）filtered：查询过滤的表占的百分比，比如这里查询的记录是name=Lisi的记录，占三条记录的33.3%
四、 关于索引的优化
1、使用索引优化的举例
上个例子我们看到到执行explain select*from user where name=\’Lisi\’，扫描了3行（全部行数）使用了全表搜索all。如果实际业务中name是经常用到查询的字段（是指经常跟在where后的字段，不是select后的字段）并且数据量很大的情况呢？这时候就需要索引了（索引经常用到where后面的字段比select后面的字段效果更好，或者说就是要使用在where后面的字段上）
增加name前缀索引（这里只是举例，并没有选择最合适的前缀）：
mysql> create index index_name on user(name(2)); Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0
执行explain分析
mysql> explain select*from user where name = \'Lisi\'; +----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | user | NULL | ref | index_name | index_name | 9 | const | 1 | 100 | Using where | +----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+ 1 row in set
可以看到type变为ref、rows降为1（实际上只要使用了索引都是1），filtered过滤百分比为100%，实际用到的索引为index_name。如果数据量很大的话使用索引就是很好的优化措施，对于如何选择索引，什么时候用索引，我做出了如下总结：
2、如何高效使用索引？
(1) 创建多列索引时，只要查询条件中用到最左边的列，索引一般都会被用到
我们创建一张没有索引的表user_1:
mysql> show create table user_1; +--------+--------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+--------------------------------------------------------------------------------------------------------------------------+ | user_1 | CREATE TABLE `user_1` ( `id` int(11) DEFAULT NULL, `name` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +--------+--------------------------------------------------------------------------------------------------------------------------+ 1 row in set
之后同样插入数据：
mysql> select *from user_1; +----+----------+ | id | name | +----+----------+ | 1 | Zhangsan | | 2 | Lisi | +----+----------+ 2 rows in set
创建多列索引index_id_name
mysql> create index index_id_name on user_1(id,name); Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0
实验查询explain分析name与id
mysql> explain select * from user_1 where id=1; +----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | user_1 | NULL | ref | index_id_name | index_id_name | 5 | const | 1 | 100 | Using index | +----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+ 1 row in set mysql> explain select * from user_1 where name=\'Lisi\'; +----+-------------+--------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | user_1 | NULL | index | NULL | index_id_name | 38 | NULL | 2 | 50 | Using where; Using index | +----+-------------+--------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+ 1 row in set
可以看到使用最左列id的时候，rows为1，并且Extra明确使用了index，key的值为id_name_index，type的值为ref，而where不用到id，而是name的话，rows的值为2。filtered为50%，虽然key是index_id_name，但是表明是索引（个人理解，应该不太准确）
(2) 使用like的查询，只有%不是第一个字符并且%后面是常量的情况下，索引才可能会被使用。
执行explain select *from user where name like ‘%Li’后type为ALL且key的值为NULL，执行explain select *from user where name like ‘Li%’后key值不为空为index_name。
mysql> explain select*from user where name like \'%Li\'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set mysql> explain select*from user where name like \'Li%\'; +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | user | NULL | range | index_name | index_name | 9 | NULL | 1 | 100 | Using where | +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+ 1 row in set
(3) 如果对打的文本进行搜索，使用全文索引而不是用like ‘%…%’（只有MyISAM支持全文索引）。
(4) 如果列名是索引，使用column_name is null将使用索引。
mysql> explain select*from user where name is null; +----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | user | NULL | ref | index_name | index_name | 9 | const | 1 | 100 | Using where | +----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+ 1 row in set mysql> explain select*from user where password is null; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set
3、哪些情况下即使有索引也用不到？
(1) MySQL使用MEMORY/HEAP引擎（使用的HASH索引），并且WHERE条件中不会使用”=”,in等进行索引列，那么不会用到索引（这是关于引擎部分特点，之后会介绍）。
(2) 用OR分隔开的条件，如果OR前面的条件中的列有索引，而后面的列没有索引，那么涉及到的列索引不会被使用。
执行命令show index from user可以看出password字段并没有使用任何索引，而id使用了两个索引，但是where id=1 or password=\’2d7284808e5111e8af74201a060059ce\’ 导致没有使用id列的primary索引与id_name_index索引
mysql> show index from user; +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | | | user | 1 | index_name | 1 | name | A | 3 | 2 | NULL | YES | BTREE | | | | user | 1 | id_name_index | 1 | id | A | 3 | NULL | NULL | | BTREE | | | | user | 1 | id_name_index | 2 | name | A | 3 | NULL | NULL | YES | BTREE | | | +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 4 rows in set mysql> explain select*from user where id=1 or password=\'2d7284808e5111e8af74201a060059ce\'; +----+-------------+-------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | user | NULL | ALL | PRIMARY,id_name_index | NULL | NULL | NULL | 3 | 55.56 | Using where | +----+-------------+-------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ 1 row in set
(3) 不是用到复合索引中的第一列即最左边的列的话，索引就不起作用（上面已经介绍）。
(4) 如果like是以%开头的(上面已经介绍)
(5) 如果列类型是字符串，那么where条件中字符常量值不用’’引号引起来的话，那就不会失去索引效果，这是因为MySQL会把输入的常量值进行转换再使用索引。
select * from user_1 where name =250，其中name的索引为name_index，并且是varchar字符串类型，但是并没有将250用引号变成’250’，那么explain之后的ref仍然为NULL,rows为3
mysql> show index from user_1; +--------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | user_1 | 1 | index_id_name | 1 | id | A | 2 | NULL | NULL | YES | BTREE | | | | user_1 | 1 | index_id_name | 2 | name | A | 2 | NULL | NULL | YES | BTREE | | | | user_1 | 1 | name_index | 1 | name | A | 3 | 5 | NULL | YES | BTREE | | | +--------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set mysql> explain select*from user_1 where name=250; +----+-------------+--------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | user_1 | NULL | index | name_index | index_id_name | 38 | NULL | 3 | 33.33 | Using where; Using index | +----+-------------+--------+------------+-------+---------------+---------------+---------+------+------+----------+--------------------------+ 1 row in set mysql> explain select*from user_1 where name=\'250\'; +----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | user_1 | NULL | ref | name_index | name_index | 18 | const | 1 | 100 | Using where | +----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------------+ 1 row in set
4、查看索引的使用情况
执行show status like ‘Handler_read%’可以看到一个值Handler_read_key，它代表一行被索引值读的次数，如果值很低说明增加索引得到的性能改善不高，因为索引并不经常使用。
mysql> show status like \'Handler_read%\' ; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 3 | | Handler_read_key | 5 | | Handler_read_last | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 20 | +-----------------------+-------+ 7 rows in set
（1）Handler_read_first：索引中第一条被读的次数。如果较高，它表示服务器正执行大量全索引扫描；
（2）Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的性能改善不高，因为索引不经常使用。
（3）Handler_read_next ：按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。
（4）Handler_read_prev：按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY … DESC。
（5）Handler_read_rnd ：根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应该建立索引来补救。
（6）Handler_read_rnd_next：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。
注：以上6点来自于网络总结，其中比较重要的两个参数是Handler_read_key与Handler_read_rnd_next。