目录
索引(index)是帮助MySQL高效获取数据的数据结构(有序)。
在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据, 这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。
索引就像是书籍的目录一样,我们可以通过目录快速查找到自己想要看的篇章,不用一页页去翻找。
表结构及其数据如下:
假如我们要执行的SQL语句为 : select * from user where age = 42;
在无索引情况下,就需要从第一行开始扫描,一直扫描到最后一行,我们称之为 全表扫描,性能很 低。
如果我们针对于这张表建立了索引,假设索引结构就是二叉树,那么也就意味着,会对age这个字段建立一个二叉树的索引结构。
此时我们在进行查询时,只需要扫描几次就可以找到数据了,极大的提高的查询的效率
注: 这里只是假设索引的结构是二叉树,介绍一下索引的大概原理,只是一个示意图,并 不是索引的真实结构。
优势:
劣势:
MySQL的索引是在存储引擎层实现的,不同的存储引擎有不同的索引结构,主要包含以下几种:
上述是MySQL中所支持的所有的索引结构,接下来,我们再来看看不同的存储引擎对于索引结构的支持情况。
索引 | InnoDB | MyISAM | Memory |
---|---|---|---|
B+tree索引 | 支持 | 支持 | 支持 |
Hash 索引 | 不支持 | 不支持 | 支持 |
R-tree 索引 | 不支持 | 支持 | 不支持 |
Full-text | 5.6版本之后支持 | 支持 | 不支持 |
注意: 我们平常所说的索引,如果没有特别指明,都是指B+树结构组织的索引。
假如说MySQL的索引结构采用二叉树的数据结构,比较理想的结构如下:
如果主键是顺序插入的,则会形成一个单向链表,结构如下
所以,如果选择二叉树作为索引结构,会存在以下缺点:
此时大家可能会想到,我们可以选择红黑树,红黑树是一颗自平衡二叉树,那这样即使是顺序插入数 据,最终形成的数据结构也是一颗平衡的二叉树,结构如下:
但是,即使如此,由于红黑树也是一颗二叉树,所以也会存在一个缺点:
所以,在MySQL的索引结构中,并没有选择二叉树或者红黑树,而选择的是B+Tree,那么什么是 B+Tree呢?
B-Tree,B树是一种多叉路衡查找树,相对于二叉树,B树每个节点可以有多个分支,即多叉。
以一颗最大度数(max-degree)为5(5阶)的b-tree为例,那这个B树每个节点最多存储4个key,5个指针:
注: 树的度数指的是一个节点的子节点个数。
特点:
B+Tree是B-Tree的变种,我们以一颗最大度数(max-degree)为4(4阶)的b+tree为例,来看一下其结构示意图:
我们可以看到,两部分:
特点:
上述我们所看到的结构是标准的B+Tree的数据结构,接下来,我们再来看看MySQL中优化之后的 B+Tree。
MySQL索引数据结构对经典的B+Tree进行了优化。
在原B+Tree的基础上,增加一个指向相邻叶子节点的链表指针,就形成了带有顺序指针的B+Tree,提高区间访问的性能,利于排序。
哈希索引就是采用一定的hash算法,将键值换算成新的hash值,映射到对应的槽位上,然后存储在 hash表中。
如果两个(或多个)键值,映射到一个相同的槽位上,他们就产生了hash冲突(也称为hash碰撞),可 以通过链表来解决。
特点:
在MySQL中,支持hash索引的是Memory存储引擎。
而InnoDB中具有自适应hash功能,hash索引是 InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的。
面试题:
为什么InnoDB存储引擎选择使用B+tree索引结构?
答案:
在MySQL数据库,将索引的具体类型主要分为以下几类:
主键索引、唯一索引、常规索引、全文索引。
分类 | 含义 | 特点 | 关键字 |
---|---|---|---|
主键 索引 | 针对于表中主键创建的索引 | 默认自动创建, 只能 有一个 | PRIMARY |
唯一 索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | UNIQUE |
常规索引 | 快速定位特定数据 | 可以有多个 | |
全文索引 | 全文索引查找的是文本中的关键词,而不是比 较索引中的值 | 可以有多个 | FULLTEXT |
而在在InnoDB存储引擎中,根据索引的存储形式,又可以分为以下两种:
分类 | 含义 | 特点 |
---|---|---|
聚集索引(ClusteredIndex) | 将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据 | 必须有,而且只 有一个 |
二级索引(SecondaryIndex) | 将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
聚集索引选取规则:
聚集索引和二级索引的具体结构如下:
聚集索引的叶子节点下挂的是这一行的数据 。 二级索引的叶子节点下挂的是该字段值对应的主键值。
当我们执行如下的SQL语句时,具体的查找过程是什么样子的。
select *
from emp
where name = '金庸'
过程如下:
1、由于是根据name字段进行查询,所以先根据name='金庸'到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 金庸 对应的主键值 1。 2、由于查询返回的数据是*,所以此时,还需要根据主键值1,到聚集索引中查找1对应的记录,最终找到1对应的行row。 3、最终拿到这一行的数据,直接返回即可。
回表查询:
这种先到二级索引中查找数据,找到主键值,然后再到聚集索引中根据主键值,获取 数据的方式,就称之为回表查询。
思考题:
以下两条SQL语句,那个执行效率高? 为什么? 1、 select * from emp where id = 1 ; 2、select * from user where name = '金庸' ; 备注: id为主键,name字段创建的有索引;
1、创建索引
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name,... ) ;
2、查看索引
SHOW INDEX FROM table_name ;
3、 删除索引
DROP INDEX index_name ON table_name ; 1
MySQL 客户端连接成功后,通过 show [session|global] status 命令可以提供服务器状态信息。
通过如下指令,可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次:
注: 1、session 是查看当前会话 ; 2、global 是查询全局数据 ;
SHOW GLOBAL STATUS LIKE 'Com_______';
Com_delete: 删除次数 Com_insert: 插入次数 Com_select: 查询次数 Com_update: 更新次数
通过上述指令,我们可以查看到当前数据库到底是以查询为主,还是以增删改为主,从而为数据 库优化提供参考依据。 如果是以增删改为主,我们可以考虑不对其进行索引的优化。 如果是以查询为主,那么就要考虑对数据库的索引进行优化了。
那么通过查询SQL的执行频次,我们就能够知道当前数据库到底是增删改为主,还是查询为主。 那假 如说是以查询为主,我们又该如何定位针对于那些查询语句进行优化呢?
次数我们可以借助于慢查询日志。
慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位:秒,默认10秒)的所有 SQL语句的日志。
MySQL的慢查询日志默认没有开启,我们可以查看一下系统变量 slow_query_log。
SHOW variables like 'slow_query_log';
如果你没有开启,需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息:
注: 如果大家在上面提供的位置没有找到my.cnf,可以去C:\ProgramData\MySQL\MySQL Server 8.0找my.ini,我自己就是这样的。
第一个红框就是开启MySQL慢日志查询开关 ,第二个就是设置慢日志的时间为2秒,SQL语句执行时间超过2秒,就会视为慢查询,记录慢查询日志。
配置完毕之后,通过以下指令重新启动MySQL服务器进行测试。
systemctl restart mysqld
测试:
执行如下SQL语句 :
现在没插入什么数据:
select * from tb_user;
这次插入100w条数据,再次查询
select count(*) from tb_sku;
检查慢查询日志 :
最终我们发现,在慢查询日志中,只会记录执行时间超多我们预设时间(10s)的SQL,执行较快的SQL是不会记录的。
那这样,通过慢查询日志,就可以定位出执行效率比较低的SQL,从而有针对性的进行优化。
show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。
通过have_profiling参数,能够看到当前MySQL是否支持profile操作:
SELECT @@have_profiling ;
可以看到,当前MySQL是支持 profile操作的,如果开关是关闭的。可以通过set语句在
session/global级别开启profiling:
SET [session/global] profiling = 1;
开关已经打开了,接下来,我们所执行的SQL语句,都会被MySQL记录,并记录执行时间消耗到哪儿去了。
我们直接执行如下的SQL语句:
select * from tb_user;
select * from tb_user where id = 1;
select * from tb_user where name = '白起';
select count(*) from tb_sku;
执行一系列的业务SQL的操作,然后通过如下指令查看指令的执行耗时:
1、查看每一条SQL的耗时基本情况
show profiles;
2、查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
3、查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;
查看每一条SQL的耗时情况:
查看指定SQL各个阶段的耗时情况 :
EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息,包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
语法:
直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;
Explain 执行计划中各个字段的含义:
这里就做一个简单的示例,我用一张有100w数据的表,对其进行操作。
这张表中id为主键,有主键索引,而其他字段是没有建立索引的。 我们先来查询其中的一条记录,看看里面的字段情况,执行如下SQL:
select * from tb_sku where id = 50000;
可以看到即使有100w的数据,根据id进行数据查询,性能依然很快,因为主键id是有索引的。 那么接下来,我们再来根据 sn 字段进行查询,执行如下SQL:
SELECT * FROM tb_sku WHERE sn = '1000000031450050000';
我们可以看到根据sn字段进行查询,查询返回了一条数据,结果耗时 13 s 608 ms,就是因为sn没有索引,而造成查询效率很低。
那么我们可以针对于sn字段,建立一个索引,建立了索引之后,我们再次根据sn进行查询,再来看一下查询耗时情况。
创建索引:
create index idx_sku_sn on tb_sku(sn) ;
然后再次执行相同的SQL语句,再次查看SQL的耗时。
我们明显会看到,sn字段建立了索引之后,查询性能大大提升。建立索引前后,查询耗时都不是一个数量级的
如果索引了多列(联合索引),要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始,并且不跳过索引中的列。如果跳跃某一列,索引将会部分失效(后面的字段索引失效)。
例如,我给t_user表创建联合索引,age,sex,status,如果我在进行查询时,最左边的列age不存在,那么索引全部失效。 而且中间不能跳过某一列,否则该列后面的字段索引将失效。
这里有一个思考题:
如果我进行sql语句编写,将age和status进行位置交换,这时候是否满足最左前缀法则?
答案:
满足,最左前缀法则中指的最左边的列,是指在查询时,联合索引的最左边的字段(即是第一个字段)必须存在,与我们编写SQL时,条件编写的先后顺序无关。
注:
联合索引中,出现范围查询(>,<),范围查询右侧的列索引失效。 在业务允许的情况下,尽可能的使用类似于 >= 或 <= 这类的范围查询,而避免使用 > 或 < 。
6.3.1 索引列运算
不要在索引列上进行运算操作, 索引将失效。
就例如在t_user表上,索引列是age,如果在age用AVG函数做平均值进行运算操作后,索引失效。
6.3.2 字符串不加引号
字符串类型字段使用时,不加引号,索引将失效。
为什么呢?因为数据库存在隐式类型转换,索引将失效。
6.3.3 模糊查询
如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效。
就是我们在进行模糊查询的时候 '%1%' ,百分号不能在前面,这样子索引会失效,'1%',要像这样子才行。
6.3.4 or连接条件
用or分割开的条件, 如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到。
select * from tb_user where id = 10 or age = 23;
就比如上面这条语句,如果id 有索引 ,但是 age没有索引,这时候索引会失效,所以进行or查询时,最好两边的字段都建立了索引。
6.3.5 数据分布影响
如果MySQL评估使用索引比全表更慢,则不使用索引。
select * from tb_user where phone >= '17799990005';
select * from tb_user where phone >= '17799990015';
就比如上面两条语句,只是传入的值不一样,最终的执行计划也完全不一样,为什么?
因为MySQL在查询时,会评估使用索引的效率与走全表扫描的效率,如果走全表扫描更快,则放弃索引,走全表扫描。 因为索引是用来索引少量数据的,如果通过索引查询返回大批量的数据,则还不如走全表扫描来的快,此时索引就会失效。
SQL提示,是优化数据库的一个重要手段,简单来说,就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
1、 use index : 建议MySQL使用哪一个索引完成此次查询(仅仅是建议,mysql内部还会再次进行评估)。
explain select * from 表名 use index(索引名) where 条件
2、 ignore index : 忽略指定的索引。
explain select * from 表名 ignore index(索引名) where 条件
3、force index : 强制使用索引。
explain select * from 表明 force index(索引名) where 条件
尽量使用覆盖索引,减少select *。
那么什么是覆盖索引呢? 覆盖索引是指查询使用了索引,并且需要返回的列,在该索引中已经全部能够找到 。
这就相当于,我们的表里有id,age,sex这些字段,然后 id 和 age都有索引。 如果我们在进行查询的时候 直接使用 select * ,返回全部字段,这个时候就会触发回表查询,什么回表查询? 本来我们如果只返回 age,而不是 * 号 返回全部数据,这样会走二级索引,到age字段的二级索引中进行匹配查找。在二级索引中查找到 age 对应的主键值,然后返回数据 。 如果由于查询返回的数据是*,所以此时,还需要根据主键值,到聚集索引中查找主键值对应的记录,最终找到 主键值 对应的行row。 这就需要两次索引扫描,也就是需要回表查询,性能相对较差一点。
那遇到这种情况该如何进行优化?
那就是把没有索引的字段加上索引。
当字段类型为字符串(varchar,text,longtext等)时,有时候需要索引很长的字符串,这会让索引变得很大,查询时,浪费大量的磁盘IO, 影响查询效率。
此时可以只将字符串的一部分前缀,建 立索引,这样可以大大节约索引空间,从而提高索引效率。
语法如下:
create index idx_xxxx on table_name(column(n)) ;
示例: 为tb_user表的email字段,建立长度为5的前缀索引。
create index idx_email_5 on tb_user(email(5));
前缀长度:
可以根据索引的选择性来决定,而选择性是指不重复的索引值(基数)和数据表的记录总数的比值, 索引选择性越高则查询效率越高, 唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。
select count(distinct email) / count(*) from tb_user ;
在业务场景中,如果存在多个查询条件,考虑针对于查询字段建立索引时,建议建立联合索引,而非单列索引。
因为如果,我们使用单列索引,一条查询语句,存在多个索引,MySQL只会选择其中一个,这样剩下的肯定会走回表查询降低性能的。