导图社区 mysql
mysql索引部分导图,本图整理了索引、explain详解与索引实战、索引实战的内容,一起来学习吧。
编辑于2023-03-29 13:56:40 上海MySQL
第一节:索引
什么是索引:索引是能帮助mysql高效查询的一种排好序的数据结构
索引数据结构
二叉树
红黑树
Hash表
B-tree
B+tree(mysql默认使用)
存储引擎
MYISAM
MYI文件
存放的是索引
MYD文件
存放的是数据
非聚簇索引
找到索引在MYI文件中存放的位置再去MYD文件中查找对应的数据
INNODB
IBD文件
存放的是索引和数据
聚簇索引
叶子节点直接存放的就是索引索引对应的行数据,都在ibd文件中
优点
innodb支持行锁,是为处理巨大数据量的最大性能设计的,cpu效率是任何其他磁盘型数据库能比的,完全与mysql整合,在主内存中缓存数据和索引来维持自己的缓冲池,表空间可以包含很多文件,而myisam每个表存在分离的文件中,innodb可以是任何尺寸,即使文件尺寸被限制在2GB的操作系统中
面试点
使用B+tree的好处
1.B+tree只有叶子节点存放数据,非叶子节点只存放索引(冗余),一次读取可以在内存中存放更多的索引,B树需要每层都遍历,增加了内存的置换次数
2.B+树的指针是相互指向的,链式结构,如果要查询出全部的数据,只需要通过链进行O(N)进行顺序遍历就行了
3.B树只适合随机检索,B+树随机检索和顺序检索都可以
4.B+树的查询更加稳定,如果是随机检索的话B+tree是每一次查询都要查询到叶子节点,如果是顺序查询的话B树靠近根节点的元素查询快
5.B+树的空间利用率高,减少了磁盘IO的次数,因为索引文件本身也是一个很大的文件
为什么innodb必须使用主键,并且推荐自增的整型主键
1.innodb的数据结构必须需要一个主键才能组织起来,有主键的话直接就可以生成B+树,没有的话mysql会找一个所有数据不想等的列设置主键生成,没有不相等的列就会新增隐藏列
2.查找数据的时候是有比较的,整型的占用空间小,1<2肯定比uuid的字符串快
3.自增有利于范围查找,mysql会默认对索引进行排序,如果是自增只需要顺序的往后插入就行了,如果不是自增就会往已经存满元素的节点插入,会导致分裂,有可能还会导致平衡
为什么非主键索引存放的是主键值
一致性和节省空间
1.一致性:如果存放的也是数据,那插入的时候就要保证主键索引和非主键索引都存放成功,增加了复杂度
tips
B+树存储数据计算示例
非叶子节点:mysql一页是16KB,bigint是8字节,指针算6字节,16KB/8+6=1170
叶子节点:16KB/(索引+指针+数据)1Kb = 16
总存储量:h为3的话就是1170*1170*3=2千万
只需要三次磁盘IO
第二节:explain详解与索引实战
介绍
explain可以模拟优化器执行sql语句,分析查询语句或者是结构的性能瓶颈
explain中的列
id:查询的序列号,一个查询对应一个id,id越大查询的优先级越高
select_type
1.simple 简单查询,不包含自查询和union
2.primary 复杂查询最外层的查询
3.subquery 子查询,不在from子句中
4.derived 派生表,不在from子句中的表
table
1当前正在访问的表
2.<derived N> 派生表,当前表来自id=N的子查询,先查询id=N的子查询
type
system>const>eq_ref>ref>range>index>all
possible
可能用到索引的列
key
真正用到索引的列
key_len
索引的长度,根据这些可以推断出走了哪些索引,varchar是3N+2个字节,int4字节
ref
表查找值所用到的值或者常量
rows
扫描了多少行
Extra
1.using index:查询的列使用了索引覆盖
2.using where:查询的列被索引覆盖,使用了where子句进行查询,通常需要回表去拿非索引列的信息
3.using index condition ,查询的列不完全被索引覆盖
4.using temporary. 产生了临时表,通常需要优化
5.using filesort 产生了外部排序而不用索引排序,数据量大的时候走了磁盘排序,通常需要优化
6.select tables optimized away:使用了某些聚合函数,如min,max
第三节:索引实战
常见场景梳理
范围查找
联合索引的第一个字段用了范围查找数据量大的时候不走索引,数据量小的时候第一个字段可能走索引,带等号以后数据量大的也不走索引,但是数据量小的时候全部联合索引字段可能会走索引
select * from employees where name >'Lilei' and age = 22 and position = 'manage'
force index强制走索引,看上去rows少了很多,最终效率不一定有全表扫描的效率高
select *from employees force INDEX(IDX_AGE_NAME) where name >'Lilei' and age = 22 and position = 'manage';
第二个字段加索引:数据量大的时候会走前两个索引,数据量小的时候全部走索引
优化:索引覆盖,直接在二级索引树上可以拿到全部的数据,避免了回表
in和or
in和or在拼的条件过多的时候不会走索引,是由具体数据决定的
官方文档说明,in里面的条件超过查询数据表的百分之30的时候不会走索引,索引我们不能只说数据量大的时候走索引数据量小的时候不走索引,这种说法是不正确的
or前后2个字段必须是索引字段,否则也不会走索引
like KK%
无论数据量大小都会走索引
mylsq5.6之后实现了索引下推
mysql在根据%前面的字段匹配到对应的索引字段以后不会立刻去回表过滤相应的记录,而是再过滤一下另外的联合索引字段,过滤完成后直接回表根据主键拿到对应的行数据
order by
数据量大的时候根据索引字段去排序不会走索引
优化总结:sql支持index和filesort两种方式排序,如果order by的字段不在索引列上就会产生using filesort,尽量做到索引覆盖,遵循索引建立时候的最左前缀原则
group by
跟order by类似,本质是先排序再分组,遵照索引创建顺序的最左前缀原则
优化:如果不需要排序的话可以加上order by null禁止排序(能写在where条件中的尽量不要写在having中)
trace工具
set session optimizer_trace="enabled=on",end_markers_in_json=on;开启
set session optimizer_trace="enabled=off";关闭
SELECT * FROM information_schema.OPTIMIZER_TRACE;查询分析sql执行的cost成本
慢查询优化
慢查询又叫做慢查询日志,运行时间超过long_query_time的sql会被放到slow_query_log日志当中,默认是10s
sql数据问题
1.当DBA反馈给我们慢sql的时候,首先要定位到是上游哪个业务调用了这个sql,如果是新上的需求有可能是业务变动导致没有走之前的索引,要及时维护
2.有可能之前的sql被其他业务调用但是开发人员没有测试索引字段,查询条件没有走索引
3.如果确认新上的需求会走原来的索引但是没走,那就需要explain看下sql执行计划,看看是不是索引需要维护,join关联是不是过多,数据量上去以后之前的查询条件是不是有问题
硬件问题
服务器响应延迟,性能差
调整服务器参数
发布系统违规导致的慢查询
子主题
公司真实场景:发布系统使用goinception作为底层数据库sql工具,此配置为处理DDL操作使用gh-ost模式进行,而发现在业务高峰期使用了2个修改表字段的sql语句,而业务表有可能存在大量的并发查询,gh-ost同步完数据进行幻影表和原表的切换,这个过程会加写锁,此时锁等待前边的事物条件完成再加锁,同时会阻塞后边的查询事物,锁等待4s超时失败后重复发起加锁操作,这就导致了后面的查询每次都要等4s再查询,前端响应就会缓慢
分页查询优化
根据连续自增的主键排序分页
select *from employees limit 10000,5; --优化后 select *from employees where id>10000 limit 5;
根据非主键字段的排序分页
select *from employees order by name limit 10000,5; --优化后 select *from employees e inner join(select id from employees order by name limit 10000,5) ed on e.id = ed.id
join关联查询优化
NLJ算法
关联的被驱动表带索引
BNL算法
关联的被驱动表不带索引
straight_join指定关联的驱动表
in和exists
select *from A where id in (select id from B)
select *from A where exists (select 1 from B where A.id = B.id)
A表的数据大于B表的数据的时候In的优先级高,反之exists的优先级高
count(*)的优化
count(*)>=count(1)>=count(二级索引)>=count(id)
count(*)mysql内部做了优化,并非全字段扫描,而是逐行累加,效率很高不需要其方式替代