MySQL索引与性能调优

1.explain

使用explain关键字分析SQL语句的效率与执行情况是基础;

例:

mysql> EXPLAIN SELECT * FROM `user` WHERE `User_id`!='';
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | range | PRIMARY       | PRIMARY | 108     | NULL | 1250 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.03 sec)

各列的含义如下:

• id:
  SELECT查询的标识符.每个SELECT都会自动分配一个唯一的标识符.
  如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行.
  id如果相同,可以认为是一组,从上往下顺序执行.
  在所有组中,id值越大,优先级越高,越先执行.

• select_type:
  查询中每个SELECT子句的类型.

SIMPLE(简单SELECT,不使用UNION或子查询等)
PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)
UNION(UNION中的第二个或后面的SELECT语句)
DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
UNION RESULT(UNION的结果)
SUBQUERY(子查询中的第一个SELECT)
DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)
DERIVED(派生表的SELECT, FROM子句的子查询)
UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

• table: 查询涉及的表或衍生表

• partitions: 匹配的分区

• type: 访问类型 由好到差system > const > eq_ref > ref > range > index > ALL

system: 表中只有一条数据. 这个类型是特殊的 const 类型.
const: 针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可.
eq_ref: 此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =, 查询效率较高.
ref: 此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了 最左前缀 规则索引的查询.
range: 表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中.
当 type 是 range 时, 那么 EXPLAIN 输出的 ref 字段为 NULL, 并且 key_len 字段是此次查询中使用到的索引的最长的那个.
index: 表示全索引扫描(full index scan), 和 ALL 类型类似, 只不过 ALL 类型是全表扫描, 而 index 类型则仅仅扫描所有的索引, 而不扫描数据.
index 类型通常出现在: 所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据. 当是这种情况时, Extra 字段 会显示 Using index.
ALL: 表示全表扫描, 这个类型的查询是性能最差的查询之一. 通常来说, 我们的查询不应该出现 ALL 类型的查询, 因为这样的查询在数据量大的情况下, 对数据库的性能是巨大的灾难. 如一个查询是 ALL 类型查询, 那么一般来说可以对相应的字段添加索引来避免.

• possible_keys: 表示 MySQL 在查询时, 能够使用到的索引. 注意, 即使有些索引在 possible_keys 中出现, 但是并不表示此索引会真正地被 MySQL 使用到. MySQL 在查询时具体使用了哪些索引, 由 key 字段决定.

• key: 此次查询中确切使用到的索引.

• key_len: 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）.
  不损失精确性的情况下，长度越短越好

• ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

• rows: 显示此查询一共扫描了多少行. MySQL 查询优化器根据统计信息, 估算 SQL 要查找到结果集需要扫描读取的数据行数.
  这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好.

• filtered: 表示此查询条件所过滤的数据的百分比

• extra: 额外的信息
  该列包含MySQL解决查询的详细信息,有以下几种情况：

Using where:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询
Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”
Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
Impossible where：这个值强调了where语句会导致没有符合条件的行。
Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

2.index 索引

• 建立索引时应从实际情况着手,枚举所有可能产生的SQL,分析其出现频率与出现情况,酌情添加索引;
• 添加索引的核心目的是为了使用索引时尽可能多的过滤掉记录行,覆盖最多行数的索引应该被优先使用;
• 枚举类型可以忽略;
• 实际情况中记录数过少时,可酌情不添加索引,因为实际上对效率并无太大提升;
• 当单个语句可能使用多个索引时,应确保先被触发的索引涵盖更多的过滤项,可通过强制执行索引或调整查询语句字段顺序实现;

查看索引:

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 | User_id     | A         |        2518 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          0 | Email    |            1 | Email       | A         |        2518 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          0 | Account  |            1 | Account     | A         |        2518 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.04 sec)

添加索引:

// 添加索引
ALTER TABLE `game` ADD INDEX idx_ended(`ended`);
// 添加UNIQUE索引
ALTER TABLE `game` ADD UNIQUE (`ended`);
// 添加PRIMARY KEY索引
ALTER TABLE `game` ADD PRIMARY KEY (`ended`);

删除索引:
可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句，DROP INDEX可以在ALTER TABLE内部作为一条语句处理，语法如下。

DROP INDEX idx_ended ON `game`;            // 删除掉table_name中的索引index_name
ALTER TABLE `game` DROP INDEX idx_ended;   // 同上
ALTER TABLE `game` DROP PRIMARY `ended`;   // 只在删除PRIMARY KEY索引时使用

其中第3条语句因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。
如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

3.最左前缀原则

该原则指的是所有索引或复合索引在匹配查询条件时,只会从左到右依次进行匹配.若最左查询条件未匹配到任何索引字段,则该索引不成立.
如:

// 建立一个user和game字段的复合索引
ALTER TABLE `score` ADD INDEX idx_user_game_ended(`user`, `game`, `ended`);

这时索引情况如下:

mysql> show index from score;
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| score |          0 | PRIMARY       |            1 | id          | A         |       66363 |     NULL | NULL   |      | BTREE      |         |               |
| score |          1 | idx_user_game_ended |            1 | user        | A         |         200 |     NULL | NULL   |      | BTREE      |         |               |
| score |          1 | idx_user_game_ended |            2 | game        | A         |         200 |     NULL | NULL   |      | BTREE      |         |               |
| score |          1 | idx_user_game_ended |            3 | ended       | A         |         200 |     NULL | NULL   |      | BTREE      |         |               |

+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

然后我们尝试使用explan看看什么情况下索引不会被正常使用:

mysql> EXPLAIN SELECT * FROM `score` WHERE `user`='xxx';
+----+-------------+-------+------+---------------------+---------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys       | key                 | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------------+---------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | score | ref  | idx_user_game_ended | idx_user_game_ended | 4       | const |    1 | Using where |
+----+-------------+-------+------+---------------------+---------------------+---------+-------+------+-------------+
1 row in set, 1 warning (0.00 sec)

上例单独查询索引中最左条件,可用.

mysql> EXPLAIN SELECT * FROM `score` WHERE `game`='xxx';
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | score | ALL  | NULL          | NULL | NULL    | NULL | 66363 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

上例单独查询索引中的中间条件,不可用.

mysql> EXPLAIN SELECT * FROM `score` WHERE `user`='ss' and `game`='xxx';
+----+-------------+-------+------+---------------------+---------------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys       | key                 | key_len | ref         | rows | Extra       |
+----+-------------+-------+------+---------------------+---------------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | score | ref  | idx_user_game_ended | idx_user_game_ended | 8       | const,const |    1 | Using where |
+----+-------------+-------+------+---------------------+---------------------+---------+-------------+------+-------------+
1 row in set, 2 warnings (0.00 sec)

上例复合查询索引中最左两个条件,可用.

mysql> EXPLAIN SELECT * FROM `score` WHERE `user`='ss' and `game`='xxx' and `ended`='20180228';
+----+-------------+-------+------+---------------------+---------------------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys       | key                 | key_len | ref               | rows | Extra       |
+----+-------------+-------+------+---------------------+---------------------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | score | ref  | idx_user_game_ended | idx_user_game_ended | 9       | const,const,const |    1 | Using where |
+----+-------------+-------+------+---------------------+---------------------+---------+-------------------+------+-------------+
1 row in set, 3 warnings (0.00 sec)

上例复合查询索引中全部条件,可用.

mysql> EXPLAIN SELECT * FROM `score` WHERE `game`='xxx' and `ended`='20180228';
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | score | ALL  | NULL          | NULL | NULL    | NULL | 66363 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

上例复合查询索引中最右两个条件,不可用.

实际上在设计索引优化时,结合最左原则,尽量减少每次索引查询后的结果数量才是优化性能的本质.

4.index_merge 索引合并

索引合并指的是对多个索引分别进行条件扫描，然后将它们各自的结果进行合并，即union, intersection，以及它们的组合(先内部intersect然后在外面union)
索引合并本质上是满足最左原则的情况下对索引条件进行优化.

官方说明文档

这里有一篇文章写的很详细了,自认为写的不比他好,这里就不再赘述:
https://www.cnblogs.com/digdeep/archive/2015/11/18/4975977.html

关于复合索引

5.使用show processlist关注线程运行情况

mysql> show processlist;
+------+------+-----------+------+---------+------+-------+------------------+
| Id   | User | Host      | db   | Command | Time | State | Info             |
+------+------+-----------+------+---------+------+-------+------------------+
| 3978 | root | localhost | demo | Query   |    0 | NULL  | show processlist |
+------+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

id列，用户登录mysql时，系统分配的"connection_id"，可以使用函数connection_id()查看
user列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句
host列，显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户
db列，显示这个进程目前连接的是哪个数据库
command列，显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等
time列，显示这个状态持续的时间，单位是秒
state列，显示使用当前连接的sql语句的状态，很重要的列。state描述的是语句执行中的某一个状态。一个sql语句，以查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成
info列，显示这个sql语句，是判断问题语句的一个重要依据

关于state状态

Checking table
　正在检查数据表（这是自动的）。
Closing tables
　正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。
Connect Out
　复制从服务器正在连接主服务器。
Copying to tmp table on disk
　由于临时结果集大于tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。
Creating tmp table
　正在创建临时表以存放部分查询结果。
deleting from main table
　服务器正在执行多表删除中的第一部分，刚删除第一个表。
deleting from reference tables
　服务器正在执行多表删除中的第二部分，正在删除其他表的记录。
Flushing tables
　正在执行FLUSH TABLES，等待其他线程关闭数据表。
Killed
　发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么kill请求会在锁释放时马上生效。
Locked
　被其他查询锁住了。
Sending data
　正在处理SELECT查询的记录，同时正在把结果发送给客户端。
Sorting for group
　正在为GROUP BY做排序。
Sorting for order
　正在为ORDER BY做排序。
Opening tables
　这个过程应该会很快，除非受到其他因素的干扰。例如，在执ALTER TABLE或LOCK TABLE语句行完以前，数据表无法被其他线程打开。正尝试打开一个表。
Removing duplicates
　正在执行一个SELECT DISTINCT方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端。
Reopen table
　获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表。
Repair by sorting
　修复指令正在排序以创建索引。
Repair with keycache
　修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by sorting慢些。
Searching rows for update
　正在讲符合条件的记录找出来以备更新。它必须在UPDATE要修改相关的记录之前就完成了。
Sleeping
　正在等待客户端发送新请求.
System lock
　正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表，那么可以通过增加–skip-external-locking参数来禁止外部系统锁。
Upgrading lock
　INSERT DELAYED正在尝试取得一个锁表以插入新记录。
Updating
　正在搜索匹配的记录，并且修改它们。
User Lock
　正在等待GET_LOCK()。
Waiting for tables
　该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。
Waiting for handler insert
　INSERT DELAYED已经处理完了所有待处理的插入操作，正在等待新的请求。

---

Ps:如有新的领悟,随时不定期更新本文.