系列文章目录
提示:所有文章的目录
1.了解SQL的执行过程
2.Docker下搭建MySQL&查看BinLog文件
3.MySQL中涉及的锁
4.MySQL数据库设计-字段类型
5.在Docker中搭建主备
6.MySQL笔记 | 6.MySQL中不能不学的索引(上)
前言
上一篇,我们对索引有了一个大概的认识,接下来我们对索引的原来进行学习。提示:以下是本篇文章正文内容
一、索引的更新过程
数据操作的流程:
数据更新 -》记录change buffer(这个过程会产生merge) -》数据通过buffer pool读入内存,提高内存利用率
merge 的执行流程是这样的:
- 从磁盘读入数据页到内存(老版本的数据页);
- 从 change buffer 里找出这个数据页的 change buffer 记录 (可能有多个),依次应用,得到新版数据页;
- 写 redo log。这个 redo log 包含了数据的变更和 change buffer 的变更。
1. 什么是change buffer?
作用:主要节省的是随机读磁盘的IO消耗。
在不影响数据一致性的前提下,InooDB 会将这些更新操作缓存在 change buffer 中,这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候,将数据页读入内存,然后执行 change buffer 中与这个页有关的操作。
2. change buffer 场景
- 考虑一:更新性能的影响,选择普通索引
- 考虑二:如果先查询一次判断是否违反唯一性约束,在进行更新,就没必要使用change buffer,因为直接内存更新会更快。
思考问题:如果把普通索引都改成了唯一锁会有什么影响?
是不是会在大量数据插入的时候,造成阻塞。
插入大量数据-》先查询一次判断是否违反唯一性约束
3. change buffer涉及指令
- 查询最大changge Buffer值
show variables like '%innodb_change_buffer_max_size%';
含义:最大不能超过buffer Pool的25%
|Variable |Value
|innodb_change_buffer_max_size|25
- 查询最大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?
- 有一个后台线程,会认为数据库空闲时;
- 数据库缓冲池不够用时;
- 数据库正常关闭时;
- redo log写满时
6.change buffer 一开始是写内存的,那么如果这个时候机器掉电重启,会不会导致 change buffer 丢失呢?
- 不会丢失,我们把 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是email的全索引,索引2是email前六个字节的索引
- 相对于索引1,索引2更节省空间
如果使用的是 index1(即 email 整个字符串的索引结构),执行顺序是这样的:
- 从 index1 索引树找到满足索引值是’zhangssxyz@xxx.com’的这条记录,取得 ID2 的值;
- 到主键上查到主键值是 ID2 的行,判断 email 的值是正确的,将这行记录加入结果集;
- 取 index1 索引树上刚刚查到的位置的下一条记录,发现已经不满足 email='zhangssxyz@xxx.com’的条件了,循环结束。
这个过程中,只需要回主键索引取一次数据,所以系统认为只扫描了一行。
如果使用的是 index2(即 email(6) 索引结构),执行顺序是这样的:
- 从 index2 索引树找到满足索引值是’zhangs’的记录,找到的第一个是 ID1;
- 到主键上查到主键值是 ID1 的行,判断出 email 的值不是’zhangssxyz@xxx.com’,这行记录丢弃;
- 取 index2 上刚刚查到的位置的下一条记录,发现仍然是’zhangs’,取出 ID2,再到 ID 索引上取整行然后判断,这次值对了,将这行记录加入结果集;
- 重复上一步,直到在 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;