1前言
上一篇：MySQL系列：索引(上)
回表 覆盖索引 降序索引 最左前缀原则 解决 docker mysql 不能输入中文问题 其它版本 MySQL 联合索引 索引下推 索引尽量不要做修改操作
2继续
init
[2021-09-04 15:21:55] Connected
> use aaaqi
[2021-09-04 15:21:55] completed in 70 ms
aaaqi> create table aaaqi_demo5 (
id int primary key,
k int NOT NULL DEFAULT 0,
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB
[2021-09-04 15:22:57] completed in 115 ms
aaaqi> insert into aaaqi_demo5 values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg')
[2021-09-04 15:22:57] 6 rows affected in 75 ms
如果执行
select * from aaaqi_demo5 where k between 3 and 5;
需要执行几次树的搜索操作，会扫描多少行？
这条 sql 查询语句的执行流程：
在 k 索引树上找到 k = 3 的记录，取得 id = 300； 再到 id 索引树查到 id = 300 对应的 R3； 在 k 索引树取下一个值 k = 5，取得 id = 500； 再回到 id 索引树查到 id = 500 对应的 R4； 在 k 索引树取下一个值 k = 6，不满足条件，循环结束
这个过程中，回到主键索引树搜索的过程，叫回表。可以看到这个查询过程读了 k 索引树的 3 条记录（步骤 1、3、5），回表了 2 次（步骤 2、步骤 4）。
这个栗子中，由于查询结果所需要的数据只在主键索引上有，所以必须回表，不过这个是可以进行索引优化从而避免回表过程的。
3覆盖索引
select id from aaaqi_demo5 where k between 3 and 5;
这条 sql 只查询 id 的值，而 id 的值已经在索引树了，所以不需要回表。也就是说，这个查询里面的索引 k 已经覆盖了 查询需求，这个称为 覆盖索引 。
那个绿色的就是覆盖索引：
覆盖索引 可以减少树的搜索次数，显著的提升查询性能，所以这是一个常用的性能优化手段。
4降序索引
MySQL 8.0 的新特性，主要用来解决多列排序可能无法使用索引的问题，从而可以覆盖更多的应用场景。
栗子 init：
aaaqi> CREATE TABLE `aaaqi_demo6`
(
`id` int(11) NOT NULL,
`name` varchar(32) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `age` (`age`)
) ENGINE = InnoDB
[2021-09-04 18:22:55] [HY000][1681] Integer display width is deprecated and will be removed in a future release.
[2021-09-04 18:22:55] [HY000][1681] Integer display width is deprecated and will be removed in a future release.
[2021-09-04 18:22:55] completed in 102 ms
如果是降序的话，无法使用索引，虽然可以相反顺序扫描，但性能会受到影响。
创建索引：
aaaqi> alter table aaaqi.aaaqi_demo6 add index age_desc(age desc)
[2021-09-04 18:33:24] completed in 79 ms
创建降序索引后出现了Using filesort ...
5最左前缀原则
合理设计索引可以减少索引个数，B+树这种索引结构，可以利用索引的最左前缀 来定位记录。
栗子，假设 用(name,age)这个联合索引来分析。
当查询所有名字是阿琦 的人时，可以快速定位到 id4，然后向后遍历得到所有所需的结果。
如果查询所有名字的第一个字阿 的人
mysql> explain select * from aaaqi_demo6 where name like '阿%';
这时，也能够用上这个索引，查到第一个符合条件的记录是 id4，然后 向后遍历，直到不满足条件为止。
6解决 docker mysql 不能输入中文问题
7联合索引
aaaqi> CREATE TABLE `aaaqi_demo7`
(
`id` int NOT NULL,
`name` varchar(32) DEFAULT NULL,
`age` int DEFAULT NULL,
`number` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `age` (`name`,`age`,`number`)
) ENGINE = InnoDB
[2021-09-04 20:26:57] completed in 87 ms
上面 KEY age 害，直接拷贝的 aaaqi_demo6 表结构
注意截图里面的 KEY age 索引名。
mysql> explain select * from aaaqi_demo7 where name='阿琦' and age=10 and number=6;
mysql> explain select * from aaaqi_demo7 where name like '阿%' and age=10 and number=6;
mysql> explain select * from aaaqi_demo7 where name='阿琦' and age=10;
in select * from aaaqi_demo7 where age=10 and name='阿琦';
mysql> explain select * from aaaqi_demo7 where age=10 and number=6;
mysql> explain select * from aaaqi_demo7 where number=6;
看到了吧，怎么写都是使用索引了的。
MySQL版本号 - Server version: 8.0.23 MySQL Community Server - GPL
官网描述：https://dev.mysql.com/doc/refman/8.0/en/index-merge-optimization.html
8其它版本 MySQL 联合索引
select * from test where a=? and b=? and c=?；查询效率最高，索引全覆盖。
select * from test where a=? and b=?；索引覆盖a和b。
select * from test where b=? and a=?；经过mysql的查询分析器的优化，索引覆盖a和b。
select * from test where a=?；索引覆盖a。
select * from test where b=? and c=?；没有a列，不走索引，索引失效。
select * from test where c=?；没有a列，不走索引，索引失效。
这个应该是 5.6、5.7版本的 MySQL，具体版本不详，懒得再弄个低版本的 MySQL 去测试了，网上基本都是这样。
那么问题来了，8.0.X 和 5.X 版本不一样，面试官问联合索引相关的问题得注意了。
9索引下推
假设 名字第一个字是阿，而且年龄是 10 岁的所有人：
select * from aaaqi.aaaqi_demo7 where name like '阿%' and age=10;
在两个图里面，每一个虚线箭头表示回表一次。
图 01 中, name,age 索引里面去掉了 age 的值，这个过程 InnoDB 并不会去看 age 的值，只是按顺序把“name 第一个字是阿”的记录一条条取出来回表。因此，需要回表 4 次。
图 01 、图 02 的区别是，InnoDB 在 name,age 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。在这个例子中，只需要对 id104、id105 这两条记录回表取数据判断，就只需要回表 2 次。
MySQL 8.0 索引下推 这个也做了优化，详情见官网：https://dev.mysql.com/doc/refman/8.0/en/index-condition-pushdown-optimization.html
10索引尽量不要做修改操作
因为索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。
主键就别瞎搞了，不论是删除主键还是创建主键，都会将整个表重建。
从下午 14 左右一直淦到 21：40，完事估计 22：00 点多了... .
