MySQL 优化

参考文章:讲真，MySQL索引优化看这篇文章就够了.
感谢作者提供的知识分享,如果涉及侵权,请联系本人删除,谢谢

一、MySQL——索引基础

“索引（在MySQL中也叫“键key”）是存储引擎快速找到记录的一种数据结构。”
——《高性能MySQL》
索引类型:
- 主键索引 PRIMARY KE:特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引。注意：一个表只能有一个主键。
- 唯一索引 UNIQUE:唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
- 普通索引 INDEX:这是最基本的索引，它没有任何限制。
- 组合索引 INDEX: 即一个索引包含多个列，多用于避免回表查询。
- 全文索引 FULLTEXT:也称全文检索，是目前搜索引擎使用的一种关键技术。

3、索引设计的原则

适合索引的列是出现在where子句中的列，或者连接子句中指定的列；
基数较小的类，索引效果较差，没有必要在此列建立索引；
使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间；
不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

MySQL 优化常用命令:

查看表索引情况: SHOW INDEX FROM user;

mysql> 
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         |          46 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

查看执行计划: EXPLAIN SELECT * FROM user WHERE id = 10;

mysql>  EXPLAIN SELECT * FROM user WHERE id = 10;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.02 sec)

查看索引的使用情况:SHOW STATUS LIKE 'Handler_read%':

mysql> SHOW STATUS LIKE 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 47    |
+-----------------------+-------+
7 rows in set (0.27 sec)

# Handler_read_key：如果索引正在工作，Handler_read_key的值将很高。
# Handler_read_rnd_next：数据文件中读取下一行的请求数，如果正在进行大量的表扫描，值将较高，则说明索引利用不理想。

二、MySQL——索引优化实战

1、索引相关的重要概念

基数:单个列唯一键（distict_keys）的数量叫做基数。查询中返回表中30%内的数据会走索引，返回超过30%数据就使用全表扫描。当然这个结论太绝对了，也并不是绝对的30%，只是一个大概的范围。
回表:当对一个列创建索引之后，索引会包含该列的键值及键值对应行所在的rowid。通过索引中记录的rowid访问表中的数据就叫回表。回表次数太多会严重影响SQL性能，如果回表次数太多，就不应该走索引扫描，应该直接走全表扫描。EXPLAIN命令结果中的Using Index意味着不会回表，通过索引就可以获得主要的数据。Using Where则意味着需要回表取数据。

image.png

索引优化规则：

2.1 如果MySQL估计使用索引比全表扫描还慢，则不会使用索引。
返回数据的比例是重要的指标，比例越低越容易命中索引。记住这个范围值——30%，后面所讲的内容都是建立在返回数据的比例在30%以内的基础上。
2.2 前导模糊查询不能命中索引。EXPLAIN SELECT * FROM user WHERE name LIKE '%s%';
非前导模糊查询则可以使用索引，可优化为使用非前导模糊查询: EXPLAIN SELECT * FROM user WHERE name LIKE 's%';

image.png
2.3 数据类型出现隐式转换的时候不会命中索引，特别是当列类型是字符串，一定要将字符常量值用引号引起来。
EXPLAIN SELECT * FROM user WHERE name=1;

image.png

EXPLAIN SELECT * FROM user WHERE name='1';

image.png
2.4 复合索引的情况下，查询条件不包含索引列最左边部分（不满足最左原则），不会命中符合索引。注意，最左原则并不是说是查询条件的顺序,而是查询条件中是否包含索引最左列字段.
2.5 union、in、or都能够命中索引，建议使用in。(查询的CPU消耗：or>in>union)
2.6 用or分割开的条件，如果or前的条件中列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到。因为or后面的条件列中没有索引，那么后面的查询肯定要走全表扫描，在存在全表扫描的情况下，就没有必要多一次索引扫描增加IO访问。
EXPLAIN SELECT * FROM payment WHERE customer_id = 203 OR amount = 3.96;

image.png
2.7 负向条件查询不能使用索引，可以优化为in查询。负向条件有：!=、<>、not in、not exists、not like等。
2.8 范围条件查询可以命中索引。范围条件有：<、<=、>、>=、between等。
2.9 数据库执行计算不会命中索引。

EXPLAIN SELECT * FROM user WHERE age>24;  # 可以命中索引
EXPLAIN SELECT * FROM user WHERE age+1>24;   # 不能命中索引

2.10 利用覆盖索引进行查询，避免回表。被查询的列，数据能从索引中取得，而不用通过行定位符row-locator再到row上获取，即“被查询列要被所建的索引覆盖”，这能够加速查询速度。当查询其他列时，就需要回表查询，这也是为什么要避免SELECT*的原因之一.
2.11 建立索引的列，不允许为null。
2.12 更新十分频繁的字段上不宜建立索引：因为更新操作会变更B+树，重建索引。这个过程是十分消耗数据库性能的。
2.13 区分度不大的字段上不宜建立索引：类似于性别这种区分度不大的字段，建立索引的意义不大。因为不能有效过滤数据，性能和全表扫描相当。另外返回数据的比例在30%以外的情况下，优化器不会选择使用索引。
2.14 业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。虽然唯一索引会影响insert速度，但是对于查询的速度提升是非常明显的。另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，在并发的情况下，依然有脏数据产生。
2.15 多表关联时，要保证关联字段上一定有索引。
2.16 创建索引时避免以下错误观念：索引越多越好，认为一个查询就需要建一个索引；宁缺勿滥，认为索引会消耗空间、严重拖慢更新和新增速度；抵制唯一索引，认为业务的唯一性一律需要在应用层通过“先查后插”方式解决；过早优化，在不了解系统的情况下就开始优化。

三、数据库索引背后的数据结构

第一部分开头我们简单提到，索引是存储引擎快速找到记录的一种数据结构。进一步说，在数据库系统里，这种数据结构要满足特定查找算法，即这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法

image.png

1、B-Tree

B-Tree是一种平衡的多路查找(又称排序)树，在文件系统中和数据库系统中有所应用，主要用作文件的索引。其中的B就表示平衡(Balance) 。

image.png

B-Tree是一个非常有效率的索引数据结构。这主要得益于B-Tree的度可以非常大，高度会变的非常小，只需要二分几次就可以找到数据。

2、B+Tree

B+Tree是B-Tree的一种变种。一般来说，B+Tree比B-Tree更适合实现外存储索引结构.

image.png

区别于B-Tree:
- 每个节点的指针上限为2d而不是2d+1；
- 内节点不存储data，只存储key；叶子节点不存储指针。

3、带有顺序访问指针的B+Tree

一般在数据库系统或者文件系统中，并不是直接使用B+Tree作为索引数据结构的，而是在B+Tree的基础上做了优化，增加了顺序访问指针，提升了区间查询的性能。

image.png

如上图所示，在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。
例如要查询18到30之间的数据记录，只要先找到18，然后顺着顺序访问指针就可以访问到所有的数据节点。这样就提升了区间查询的性能。数据库的索引全扫描index和索引范围扫描range就是基于此实现的。