尚硅谷MySQL数据库高级-02-索引优化分析

转载参考：

• https://github.com/RingoTangs/LearningNote/blob/master/MySQL/MySQL.md
• https://blog.youkuaiyun.com/qq_21579045/article/details/99702766

索引优化分析

性能下降SQL慢

• 查询语句写的烂
• 索引失效
  • 单值索引
  • 复合索引
• 关联查询太多join（设计缺陷或不得已的需求）
• 服务器调优及各个参数设置（缓冲、线程数等）

SQL执行顺序

• 手写
  

• 机读
  

• 总结
  

常见通用的Join查询

SELECT * from tbl_emp a left join tbl_dept b on a.deptId = b.id;

SELECT * from tbl_emp a right join tbl_dept b on a.deptId = b.id;


SELECT * from tbl_emp a inner join tbl_dept b on a.deptId = b.id;


SELECT * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null;

SELECT * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.id is null;


/* MySQL不支持FULL OUTER JOIN这种语法 可以改成 Union */

SELECT * from tbl_emp a left join tbl_dept b on a.deptId = b.id
union
SELECT * from tbl_emp a right join tbl_dept b on a.deptId = b.id;


SELECT * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null
union
SELECT * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.id is null;

Union和Union All区别:
* Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
* Union All：对两个结果集进行并集操作，包括重复行，不进行排序；

索引简介

是什么

• MySQL官方对索引的定义为：索引（INDEX）是帮助MySQL高效获取数据的数据结果。从而可以获得索引的本质：索引是排好序的快速查找数据结构。
• 索引的目的在于提高查询效率，可以类比字典的目录。如果要查mysql这个这个单词，我们肯定要先定位到m字母，然后从上往下找y字母，再找剩下的sql。如果没有索引，那么可能需要a—z，这样全字典扫描，如果我想找Java开头的单词呢？如果我想找Oracle开头的单词呢？？？
• 重点：索引会影响到MySQL查找(WHERE的查询条件)和排序(ORDER BY)两大功能！
• 除了数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

• 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。
• 我们平时所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种数据结构的索引之外，还有哈希索引（Hash Index）等。
• 一般企业数据是伪删除，一方面大数据，一方面是破坏索引

索引的优势和劣势

• 优势：
  • 查找：类似大学图书馆的书目索引，提高数据检索的效率，降低数据库的IO成本。
  • 排序：通过索引対数据进行排序，降低数据排序的成本，降低了CPU的消耗。
• 劣势：
  • 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
  • 虽然索引大大提高了查询速度，但是同时会降低表的更新速度，例如对表频繁的进行INSERT、UPDATE和DELETE。因为更新表的时候，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加的索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
  • 索引只是提高效率的一个因素，如果MySQL有大数据量的表，就需要花时间研究建立最优秀的索引。

索引分类

• 单值索引：一个索引只包含单个列，一个表可以有多个单列索引。
• 唯一索引：索引列的值必须唯一，但是允许空值。
• 复合索引：一个索引包含多个字段。

命令语句

• 创建：

  • create [unique] index indexname on mytable(columnname(length));

  • alter mytable add [unique] index [indexname] on (columnname(length))

  • 如果是char，varchar类型，length可以小于字段实际长度；如果是blob和text类型，必须指定length。

• 删除：drop index [indexname] on mytable;

• 查看：show index from table_name\G

• 使用alter命令

/* 1、该语句添加一个主键，这意味着索引值必须是唯一的，并且不能为NULL */
ALTER TABLE tabName ADD PRIMARY KEY(column_list);

/* 2、该语句创建索引的键值必须是唯一的(除了NULL之外，NULL可能会出现多次) */
ALTER TABLE tabName ADD UNIQUE indexName(column_list);

/* 3、该语句创建普通索引，索引值可以出现多次 */
ALTER TABLE tabName ADD INDEX indexName(column_list);

/* 4、该语句指定了索引为FULLTEXT，用于全文检索 */
ALTER TABLE tabName ADD FULLTEXT indexName(column_list);

索引结构

• BTree索引。

  • 索引原理
    
    

• Hash索引。

• Full-text全文索引。

• R-Tree索引。

哪些情况需要建索引

• 主键自动建立主键索引（唯一 + 非空）。
• 频繁作为查询条件的字段应该创建索引。
• 查询中与其他表关联的字段，外键关系建立索引。
• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。
• 查询中统计或者分组字段（group by也和索引有关）。

哪些情况不要建索引

• 表记录太少
• 经常增删改的表
  • Why：提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
• 频繁更新的字段不适合创建索引
  • 因为每次更新不单单是更新了记录，还会更新索引，加重IO负担
• where条件里用不到的字段不创建索引
• 单键/组合索引的选择问题。
  • who？（在高并发下倾向创建组合索引）
• 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
  

性能分析

MySQL Query Optimizer

MySQL常见瓶颈

• CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
• IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
• 服务器硬件的性能瓶颈：top，free，iostat和vmstat来查看系统的性能状态

Explain

是什么（查看执行计划）

SQL的执行计划，使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的。

能干嘛

• id：表的读取顺序。
• select_type：数据读取操作的操作类型。
• possible_keys：哪些索引可以使用。
• key：哪些索引被实际使用。
• ref：表之间的引用。
• rows：每张表有多少行被优化器查询。

怎么玩

explain + SQL

各字段解释

id

• id：表的读取和加载顺序。

• 值有以下三种情况：

  • id相同，执行顺序由上至下。
  • id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。
  • id相同不同，同时存在。永远是id大的优先级最高，id相等的时候顺序执行。

• 衍生：DERIVED

select_type

• select_type：数据查询的类型，主要是用于区别，普通查询、联合查询、子查询等的复杂查询。

  • SIMPLE：简单的SELECT查询，查询中不包含子查询或者UNION 。
  • PRIMARY：查询中如果包含任何复杂的子部分，最外层查询则被标记为PRIMARY。
  • SUBQUERY：在SELECT或者WHERE子句中包含了子查询。
  • DERIVED：在FROM子句中包含的子查询被标记为DERIVED(衍生)，MySQL会递归执行这些子查询，把结果放在临时表中。
  • UNION：如果第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVED。
  • UNION RESULT：从UNION表获取结果的SELECT。

table

• 显示这一行的数据是关于哪些表的。

type

• 访问类型排列

• 从最好到最差依次是：system>const>eq_ref>ref>range>index>ALL。除了ALL没有用到索引，其他级别都用到索引了。

• 一般来说，得保证查询至少达到range级别，最好达到ref。

• system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计。

• const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转化为一个常量。

• eq_ref：唯一性索引扫描，读取本表中和关联表表中的每行组合成的一行，查出来只有一条记录。除 了 system 和 const 类型之外, 这是最好的联接类型。

• ref：非唯一性索引扫描，返回本表和关联表某个值匹配的所有行，查出来有多条记录。

• range：只检索给定范围的行，一般就是在WHERE语句中出现了BETWEEN、< >、in等的查询。这种范围扫描索引比全表扫描要好，因为它只需要开始于索引树的某一点，而结束于另一点，不用扫描全部索引。

• index：Full Index Scan，全索引扫描，index和ALL的区别为index类型只遍历索引树。也就是说虽然ALL和index都是读全表，但是index是从索引中读的，ALL是从磁盘中读取的。

• ALL：Full Table Scan，没有用到索引，全表扫描。

possible_keys

• 显示可能应用在这张表中的索引，一个或者多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

key

• 实际使用的索引。如果为NULL，则没有使用索引。
• 查询中如果使用了覆盖索引，则该索引仅仅出现在key列表中。（覆盖索引：查询的字段与建立的复合索引的个数一一吻合）

key_len

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

ref

• 显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。查询中与其它表关联的字段，外键关系建立索引。

rows

• 根据表统计信息及索引选用情况，大致估算出找到所需的记录需要读取的行数。

Extra

• 包含不适合在其他列中显示但十分重要的额外信息。
• Using filesort：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作成为"文件内排序"。

• Using temporary：使用了临时表保存中间结果，MySQL在対查询结果排序时使用了临时表。常见于排序order by和分组查询group by。临时表対系统性能损耗很大。
  

• Using index：表示相应的SELECT操作中使用了覆盖索引，避免访问了表的数据行，效率不错！如果同时出现Using where，表示索引被用来执行索引键值的查找；如果没有同时出现Using where，表明索引用来读取数据而非执行查找动作。

• Using where：表明使用了where过滤。

• Using join buffer：使用了连接缓存。

• impossible where：where子句的值总是false，不能用来获取任何元组。（查询语句中where的条件不可能被满足，恒为False）

• select tables optimized away：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

• distinct：优化distinct操作，在找到第一匹配的元组后即停止找相同值的动作。

最左原则，按楼梯走。跳楼梯就不能Using index。

case

索引优化

索引单表优化案例

• 编写SQL语句并查看SQL执行计划。

mysql>  SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-----------+
| id | author_id |
+----+-----------+
|  5 |         1 |
+----+-----------+
1 row in set (0.00 sec)

mysql> explain SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | article | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

• 创建和查看索引idx_article_ccv

mysql> create index idx_article_ccv on article(category_id,comments,views);
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from article;
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| article |          0 | PRIMARY         |            1 | id          | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            1 | category_id | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            2 | comments    | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            3 | views       | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.04 sec)

• 查看现在SQL语句的执行计划
  • 我们发现，创建符合索引idx_article_ccv之后，虽然解决了全表扫描的问题，但是Extra居然还是用的Using filesort，为什么？

mysql> explain SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | article | NULL       | range | idx_article_ccv | idx_article_ccv | 8       | NULL |    2 |   100.00 | Using index condition; Using filesort |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

• 因为type是range ，决定尝试把修改非range
  • 推论：当comments > 1的时候order by排序views字段索引就用不上，但是当comments = 1的时候order by排序views字段索引就可以用上！！！所以，范围之后的索引会失效。

mysql> explain SELECT id,author_id FROM article WHERE category_id = 1 AND comments = 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys   | key             | key_len | ref         | rows | filtered | Extra       |
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | article | NULL       | ref  | idx_article_ccv | idx_article_ccv | 8       | const,const |    1 |   100.00 | Using where |
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

• 我们现在知道范围之后的索引会失效，原来的索引idx_article_ccv最后一个字段views会失效，那么我们如果删除这个范围字段的索引，创建新的索引idx_article_cv

mysql> drop index idx_article_ccv on article;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index idx_article_ccv on article (category_id,views);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from  article;
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| article |          0 | PRIMARY         |            1 | id          | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            1 | category_id | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            2 | views       | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

• 再次看一下SQL执行计划

mysql> explain SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | article | NULL       | range | idx_article_ccv | idx_article_ccv | 4       | NULL |    3 |    33.33 | Using index condition; Using where |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

索引两表优化案例

• 不创建索引的情况下，SQL的执行计划
  • book和class两张表都是没有使用索引，全表扫描，需要进行优化，

mysql> explain select * from book left join class on book.card = class.card;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | book  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | class | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+