MySQL调优(六)-索引优化不能不了解的基本知识

最新推荐文章于 2023-06-27 15:44:22 发布

极致人生

最新推荐文章于 2023-06-27 15:44:22 发布

阅读量153

点赞数

分类专栏： MySQL调优文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_40780670/article/details/108887299

版权

MySQL调优专栏收录该内容

13 篇文章 2 订阅

订阅专栏

MySQL的索引优化是我们在日常工作中使用相对较多的优化方式，但也不能盲目的创建索引，使用不当的可能适得其反。本文从索引的基本知识到各种索引的使用场景了解MySQL的索引优化。

一、环境准备

索引优化之前我们需要将MySQL官方的sakila数据库安装到本地：

ZIP格式：http://downloads.mysql.com/docs/sakila-db.zip
tar格式 http://downloads.mysql.com/docs/sakila-db.tar.gz
官方文档 [http://dev.mysql.com/doc/sakila/en/index.html]
解压后得到三个文件：
1. sakila-schema.sql 文件包含创建Sakila数据库的结构：表、视图、存储过程和触发器
2. sakila-data.sql文件包含：使用 INSERT语句填充数据及在初始数据加载后，必须创建的触发器的定义
3. sakila.mwb文件是一个MySQL Workbench数据模型，可以在MySQL的工作台打开查看数据库结构。
```sql
--登录mysql
mysql -uroot -p123456
--导入表的结构数据
source /root/sakila-schema.sql
--导入表的数据
source /root/sakila-data.sql

二、索引基本知识

在这里插入图片描述
2.1 索引的优点
1)减少服务器需要扫描的数据量
2)帮助服务器避免排序和临时表
3)将随机io变成顺序io

2.2 索引的用处
1）快速查找匹配where子句的行
2）如果表具有多列索引，则优化器可以使用索引的任何最左前缀来查找行
3）查找特定索引的min或max值
4）如果排序或分组是在可用索引的最左前缀上完成的，则对表进行排序和分组
5）某些情况下，可用优化查询以检索值而无需查询数据行

2.3 索引的分类
主键索引，唯一索引，普通索引，全文索引，组合索引

2.4 面试技术名词：回表，覆盖索引，最左匹配、索引下推
回表
简单来说就是数据库根据索引（非主键）找到了指定的记录所在行后，还需要根据主键再次到数据块里获取数据。再例如，虽然只查询索引里的列，但是需要回表过滤掉其他行。
这先要从InnoDB的索引实现说起，InnoDB有两大类索引：
聚集索引(clustered index)
普通索引(secondary index)
InnoDB聚集索引和普通索引有什么差异？
InnoDB聚集索引的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引：
（1）如果表定义了PK，则PK就是聚集索引；
（2）如果表没有定义PK，则第一个not NULL unique列是聚集索引；
（3）否则，InnoDB会创建一个隐藏的row-id作为聚集索引；
画外音：所以PK查询非常快，直接定位行记录。
InnoDB普通索引的叶子节点存储主键值。
画外音：注意，不是存储行记录头指针，MyISAM的索引叶子节点存储记录指针。
举个栗子，不妨设有表：
t(id PK, name KEY, sex, flag);
画外音：id是聚集索引，name是普通索引。
表中有四条记录：
1, shenjian, m, A
3, zhangsan, m, A
5, lisi, m, A
9, wangwu, f, B
在这里插入图片描述
两个B+树索引分别如上图：
（1）id为PK，聚集索引，叶子节点存储行记录；
（2）name为KEY，普通索引，叶子节点存储PK值，即id；
既然从普通索引无法直接定位行记录，那普通索引的查询过程是怎么样的呢？
通常情况下，需要扫码两遍索引树。
例如：
select * from t where name=‘lisi’;
是如何执行的呢？
在这里插入图片描述
如粉红色路径，需要扫码两遍索引树：
（1）先通过普通索引定位到主键值id=5；
（2）再通过聚集索引定位到行记录；
这就是所谓的回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。

覆盖索引
查询的列都在索引列中、数据直接从索引中获取不需要回表查找对应行数据。
实现：将要查询的列，包含在组合索引中
表现：在explain 查询计划中Extra输出为Using index 时则表示使用了覆盖索引。

哪些场景可以利用索引覆盖来优化SQL？
场景1：全表count查询优化：
在这里插入图片描述

场景2：多列查询回表优化

select id,name,sex ... where name='shenjian'; -- id 为主键,name为索引列

这个例子中，将单列索引(name)升级为联合索引(name, sex)，即可避免回表。

场景3：分页查询

select id,name,sex ... order by name limit 500,100;
--将单列索引(name)升级为联合索引(name, sex)，也可以避免回表。

最左匹配
组合索引中最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。
当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；
但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。
比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了，这个是非常重要的性质，即索引的最左匹配特性。
构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树。
例子：假如创建一个（a,b)的联合索引，那么它的索引树是这样的
在这里插入图片描述
可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。所以b = 2这种查询条件没有办法利用索引，因为联合索引首先是按a排序的，b是无序的。
同时我们还可以发现在a值相等的情况下，b值又是按顺序排列的，但是这种顺序是相对的。所以最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引。
最左匹配原则：假如建立联合索引（a,b,c）

-- 都从最左边开始连续匹配，用到了索引
select * from table_name where a = '1' 
select * from table_name where a = '1' and b = '2'  
select * from table_name where a = '1' and b = '2' and c = '3'
-- 这些没有从最左边开始，最后查询没有用到索引，用的是全表扫描 
select * from table_name where  b = '2' 
select * from table_name where  c = '3'
select * from table_name where  b = '1' and c = '3' 
-- 如果不连续时，只用到了a列的索引，b列和c列都没有用到 
select * from table_name where a = '1' and c = '3'

索引下推
“索引条件下推”，称为 Index Condition Pushdown (ICP)，这是MySQL提供的用某一个索引对一个特定的表从表中获取元组”，注意我们这里特意强调了“一个”，这是因为这样的索引优化不是用于多表连接而是用于单表扫描，确切地说，是单表利用索引进行扫描以获取数据的一种方式。在Mysql5.6的版本上推出，用于优化查询。
在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件。
在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。
索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。
实践
在开始之前先先准备一张用户表(user)，其中主要几个字段有：id、name、age、address。建立联合索引（name，age）。
假设有一个需求，要求匹配姓名第一个为陈的所有用户，sql语句如下：

SELECT * from user where  name like '陈%'

根据 “最佳左前缀” 的原则，这里使用了联合索引（name，age）进行了查询，性能要比全表扫描肯定要高。
问题来了，如果有其他的条件呢？假设又有一个需求，要求匹配姓名第一个字为陈，年龄为20岁的用户，此时的sql语句如下：

SELECT * from user where  name like '陈%' and age=20

这条sql语句应该如何执行呢？下面对Mysql5.6之前版本和之后版本进行分析。
Mysql5.6之前的版本：
5.6之前的版本是没有索引下推这个优化的，因此执行的过程如下图：
在这里插入图片描述

Mysql5.6及之后版本：
5.6版本添加了索引下推这个优化，执行的过程如下图：
在这里插入图片描述
InnoDB并没有忽略age这个字段，而是在索引内部就判断了age是否等于20，对于不等于20的记录直接跳过，因此在(name,age)这棵索引树中只匹配到了一个记录，此时拿着这个id去主键索引树中回表查询全部数据，这个过程只需要回表一次。
我们可以实战分析一下，因此陈某装了Mysql5.6版本的Mysql，解析了上述的语句，如下图：
在这里插入图片描述
根据explain解析结果可以看出Extra的值为Using index condition，表示已经使用了索引下推。
总结：索引下推在非主键索引上的优化，可以有效减少回表的次数，大大提升了查询的效率。

2.5 索引采用的数据结构
哈希表：内车型存储引擎Memory
B+树：innoDB,MyISAM

2.6 索引匹配方式
1)全值匹配，指和索引中的所有列进行匹配

EXPLAIN SELECT * from staffs where name='July' and age='23' and pos ='dev';

2)匹配最左前缀，只匹配索引的前面几列

EXPLAIN SELECT * from staffs where name='July' and age='23';
EXPLAIN SELECT * from staffs where name='July' ;

3)匹配列前缀，可以匹配某列的开头部分

EXPLAIN SELECT * from staffs where name like 'J%';

4)匹配范围值，可以查找某个范围的数据

EXPLAIN SELECT * from staffs where name > 'July';

5)精确匹配某一列并范围匹配另一列，查询第一列的全部和第二列的部分

EXPLAIN SELECT * from staffs where name = 'July' and age > '23';

6)只访问索引的查询，查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引

explain select name,age,pos from staffs where name = 'July' and age = 25 and pos = 'dev';

在这里插入图片描述

三、哈希索引

在这里插入图片描述
场景：
当需要存储大量的URL，并且根据URL进行搜索查找，如果使用B+树，存储的内容就会很大
select id from url where url=""，也可以利用将url使用CRC32做哈希，可以使用以下查询方式：
select id fom url where url="" and url_crc=CRC32("")此查询性能较高原因是使用体积很小的索引来完成查找

四、组合索引

在这里插入图片描述
场景：创建组合索引时，列的排序默认为asc ,如果查询时即使用desc又使用asc,索引将失效,
如果第一列为范围查询比如> 后面索引列将失效。

五、聚簇索引与非聚簇索引

在这里插入图片描述
场景：
mysql 大批量数据迁移时，可以将创建索引的属性off掉（主键和唯一键会自动添加索引）提高效率，防止索引频繁更新。

极致人生

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL调优(六)-索引优化不能不了解的基本知识

MySQL的索引优化是我们在日常工作中使用相对较多的优化方式，但也不能盲目的创建索引，使用不当的可能适得其反。本文从索引的基本知识到各种索引的使用场景了解MySQL的索引优化。一、环境准备索引优化之前我们需要将MySQL官方的sakila数据库安装到本地：ZIP格式：http://downloads.mysql.com/docs/sakila-db.ziptar格式 http://downloads.mysql.com/docs/sakila-db.tar.gz官方文档 [http://dev.m
复制链接

扫一扫