MySQL笔记 | 7.MySQL中不能不学的索引（下）

系列文章目录

提示：所有文章的目录
1.了解SQL的执行过程
2.Docker下搭建MySQL&查看BinLog文件
3.MySQL中涉及的锁
4.MySQL数据库设计-字段类型
5.在Docker中搭建主备
6.MySQL笔记 | 6.MySQL中不能不学的索引（上）

---

前言

上一篇，我们对索引有了一个大概的认识，接下来我们对索引的原来进行学习。

---

提示：以下是本篇文章正文内容

一、索引的更新过程

数据操作的流程：
数据更新 -》记录change buffer（这个过程会产生merge） -》数据通过buffer pool读入内存，提高内存利用率

merge 的执行流程是这样的：

1. 从磁盘读入数据页到内存（老版本的数据页）；
2. 从 change buffer 里找出这个数据页的 change buffer 记录 (可能有多个），依次应用，得到新版数据页；
3. 写 redo log。这个 redo log 包含了数据的变更和 change buffer 的变更。

1. 什么是change buffer?

作用：主要节省的是随机读磁盘的IO消耗。

在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。

2. change buffer 场景

1. 考虑一：更新性能的影响，选择普通索引
2. 考虑二：如果先查询一次判断是否违反唯一性约束，在进行更新，就没必要使用change buffer，因为直接内存更新会更快。

思考问题：如果把普通索引都改成了唯一锁会有什么影响？

是不是会在大量数据插入的时候，造成阻塞。
插入大量数据-》先查询一次判断是否违反唯一性约束

3. change buffer涉及指令

1. 查询最大changge Buffer值
   show variables like '%innodb_change_buffer_max_size%';
   含义：最大不能超过buffer Pool的25%

|Variable                     |Value
|innodb_change_buffer_max_size|25

2. 查询最大Buffer Pool值
   show variables like '%innodb_buffer_pool_size%';

4.redo log和change buffer的区别

回顾下WAL:write Ahead logging ，先写日志在写磁盘。

Update的流程，将值写入新行，更新到内存，写入redo log，成功再写入bin log提交。

作用上的区别：
redo log的作用:为了节省随便写带来的性能消耗，改为顺序写
change buffer的作用:为了节省随机读磁盘的IO消耗。

5.哪些情况下会刷新change buffer？

1. 有一个后台线程，会认为数据库空闲时；
2. 数据库缓冲池不够用时；
3. 数据库正常关闭时；
4. redo log写满时

6.change buffer 一开始是写内存的，那么如果这个时候机器掉电重启，会不会导致 change buffer 丢失呢？

1. 不会丢失，我们把 change buffer 的操作也记录到 redo log

二、索引的采样统计

1.重新统计索引

整体扫描的代价太大，所以采用采样统计。
得到错误的扫描行数，通过analyze table t 命令，可以用来重新统计索引信息.

优化器的逻辑：
优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。

mysql> analyze table t ;
+-----------+---------+----------+----------+
| Table     | Op      | Msg_type | Msg_text |
+-----------+---------+----------+----------+
| ssmTest.t | analyze | status   | OK       |
+-----------+---------+----------+----------+
1 row in set (0.03 sec)

mysql> explain select * from t where a between 10000 and 20000;
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+
|  1 | SIMPLE      | t     | NULL       | range | a             | a    | 5       | NULL | 10001 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t force index(a) where a between 10000 and 20000;
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+
|  1 | SIMPLE      | t     | NULL       | range | a             | a    | 5       | NULL | 10001 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

2.通过分析一个sql 来看索引和扫描行的问题

步骤一：分析一下，如果先进行a索引，扫描索引a的前1000个值，然后在进行回表查询每一行。

mysql> explain select * from t where (a between 1 and 1000)  and (b between 50000 and 100000) order by b limit 1;
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                              |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+------------------------------------+
|  1 | SIMPLE      | t     | NULL       | range | a,b           | b    | 5       | NULL | 50128 |     1.00 | Using index condition; Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

步骤二：使用analyze 重新统计信息

analyze table t ;
+-----------+---------+----------+----------+
| Table     | Op      | Msg_type | Msg_text |
+-----------+---------+----------+----------+
| ssmTest.t | analyze | status   | OK       |
+-----------+---------+----------+----------+
1 row in set (0.02 sec)

mysql> explain select * from t where (a between 1 and 1000)  and (b between 50000 and 100000) order by b limit 1;
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                              |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+------------------------------------+
|  1 | SIMPLE      | t     | NULL       | range | a,b           | b    | 5       | NULL | 48869 |     1.02 | Using index condition; Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

步骤三：用强制索引来看一下

mysql> explain select * from t force index(a) where (a between 1 and 1000)  and (b between 50000 and 100000) order by b limit 1;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t     | NULL       | range | a             | a    | 5       | NULL | 1000 |    11.11 | Using index condition; Using where; Using filesort |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

可以看出强制a索引，扫描行数为1000

mysql> explain select * from t force index(b) where (a between 1 and 1000)  and (b between 50000 and 100000) order by b limit 1;
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                              |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+------------------------------------+
|  1 | SIMPLE      | t     | NULL       | range | b             | b    | 5       | NULL | 48869 |    11.11 | Using index condition; Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+------------------------------------+
1 row in set, 1 warning (0.01 sec)

可以看出强制b索引，扫描行数为50000

3.前缀索引的选择

mysql> alter table SUser add index index1(email);
或
mysql> alter table SUser add index index2(email(6));

这两个索引的区别：

1. 索引1是email的全索引，索引2是email前六个字节的索引
2. 相对于索引1，索引2更节省空间

如果使用的是 index1（即 email 整个字符串的索引结构），执行顺序是这样的：

1. 从 index1 索引树找到满足索引值是’zhangssxyz@xxx.com’的这条记录，取得 ID2 的值；
2. 到主键上查到主键值是 ID2 的行，判断 email 的值是正确的，将这行记录加入结果集；
3. 取 index1 索引树上刚刚查到的位置的下一条记录，发现已经不满足 email='zhangssxyz@xxx.com’的条件了，循环结束。
   这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。

如果使用的是 index2（即 email(6) 索引结构），执行顺序是这样的：

1. 从 index2 索引树找到满足索引值是’zhangs’的记录，找到的第一个是 ID1；
2. 到主键上查到主键值是 ID1 的行，判断出 email 的值不是’zhangssxyz@xxx.com’，这行记录丢弃；
3. 取 index2 上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出 ID2，再到 ID 索引上取整行然后判断，这次值对了，将这行记录加入结果集；
4. 重复上一步，直到在 idxe2 上取到的值不是’zhangs’时，循环结束。
   在这个过程中，要回主键索引取 4 次数据，也就是扫描了 4 行。

为了避免这样的事情发生，我们可以通过计算前缀的区分度来设置前置索引的字节。

select count(distinct left(email,4);

其他方式：通过crc32()，计算出四位校验码，来减少索引占用的空间。
缺点：需要新增一个哈希字段来保存

三、索引的设计与管理

查询优化程序统计信息数据

Field含义：
database_name：数据库名
table_name： 表名
index_name ：索引名
last_update：最后一次更新时间
stat_name ：统计名
stat_value ：统计值
sample_size ：样本大小
stat_description：统计说明-索引对应的字段名

MySQL [dc_rtsm]> desc mysql.innodb_index_stats;
+------------------+---------------------+------+-----+-------------------+-----------------------------+
| Field            | Type                | Null | Key | Default           | Extra                       |
+------------------+---------------------+------+-----+-------------------+-----------------------------+
| database_name    | varchar(64)         | NO   | PRI | NULL              |                             |
| table_name       | varchar(64)         | NO   | PRI | NULL              |                             |
| index_name       | varchar(64)         | NO   | PRI | NULL              |                             |
| last_update      | timestamp           | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| stat_name        | varchar(64)         | NO   | PRI | NULL              |                             |
| stat_value       | bigint(20) unsigned | NO   |     | NULL              |                             |
| sample_size      | bigint(20) unsigned | YES  |     | NULL              |                             |
| stat_description | varchar(1024)       | NO   |     | NULL              |                             |
+------------------+---------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)

MySQL [dc_rtsm]> SELECT table_name,index_name,stat_name, stat_value,stat_description FROM mysql.innodb_index_stats WHERE table_name = 'drug_info' and index_name = 'PRIMARY';
+------------+------------+--------------+------------+-----------------------------------+
| table_name | index_name | stat_name    | stat_value | stat_description                  |
+------------+------------+--------------+------------+-----------------------------------+
| drug_info  | PRIMARY    | n_diff_pfx01 |          2 | id                                |
| drug_info  | PRIMARY    | n_diff_pfx02 |          2 | leave,id                                |
| drug_info  | PRIMARY    | n_leaf_pages |          1 | Number of leaf pages in the index |
| drug_info  | PRIMARY    | size         |          1 | Number of pages in the index      |
+------------+------------+--------------+------------+-----------------------------------+
3 rows in set (1.79 sec)

n_diff_pfx01主键索引，2条记录。
n_diff_pfx02联合索引(leave,id)，2条记录。
n_leaf_pages叶子节点1，1条记录。
size索引所有页数，1条记录。
非叶子节点数为，size-n_leaf_pages
可以通过innodb_index_stats来确认，索引的选择性。

1.如何解决乱建索引，造成资源的浪费？

可以通过sys.schema_unused_indexes来查看，哪些索引一直未被使用过

MySQL [dc_rtsm]> select * from sys.schema_unused_indexes where object_schema = 'dc_rtsm';

+---------------+---------------+-----------------------------+
| object_schema | object_name   | index_name                  |
+---------------+---------------+-----------------------------+
| dc_rtsm       | crf_mo        | UKabrab2d0kuffp9b0tfg4lvhv8 |
| dc_rtsm       | data_page     | idx_subject_visit_page_no   |
| dc_rtsm       | data_page_mo  | UKa5a49soco8wbywms2yuvqn9pv |
| dc_rtsm       | data_page_mo  | IDX_Data_Page_Mo_Id         |
| dc_rtsm       | data_point_mo | UKa6w2vqduch6a5m1kl26sr323u |
| dc_rtsm       | visit_mo      | IDX_Visit_Mo_Id             |
+---------------+---------------+-----------------------------+
6 rows in set (1 min 57.40 sec)

MySQL 8.0 版本推出了索引不可见（Invisible）功能。在删除废弃索引前，用户可以将索引设置为对优化器不可见，然后观察业务是否有影响。

ALTER TABLE t1 

ALTER INDEX idx_name INVISIBLE/VISIBLE;