MYSQL-索引优化
共 4814字,需浏览 10分钟
·
2021-03-07 10:16
Q
索引是什么?
在关系型数据库中,索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构,它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单
简单来说:索引就是图书的目录,能够帮助快速定位相关内容
Q
索引的数据结构?
MySQL索引的数据结构是树,常用的存储引擎innodb采用的是B+Tree
Q
什么是B+树?
B+树是B树变体,相对于B树
叶子结点的值包含了所有的值;
所有父结点的值是重复了叶子结点的值;
父结点只起索引查找的作用,同时所有叶子结点也也构成了一条有序的链表。
推荐使用该地址查看B+树的节点创建过程及查找过程
https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html
Q
导致SQL执行慢的原因?
1.硬件问题。如网络速度慢,内存不足,I/O吞吐量小,磁盘空间满了等。
2.没有索引或者索引失效。(一般在互联网公司,DBA会在半夜把表锁了,重新建立一遍索引,因为当你删除某个数据的时候,索引的树结构就不完整了。所以互联网公司的数据做的是假删除.一是为了做数据分析,二是为了不破坏索引 )
3.数据过多(分库分表)
4.服务器调优及各个参数设置(调整my.cnf)
Q
排查SQL执行慢的方式
1、开启慢查询日志,设置一定的阈值(比如超过2秒就是慢SQL),生产环境可以跑一天,通过日志排查哪些SQL比较慢
2、Explain和慢SQL分析:分析工具可采用mysqldumpslow或pt-query-digest
3、Show Profile可以查询到执行每一条SQL都做了什么事情,这些事分别花费了多少秒
4、服务器的参数设置
Q
使用MySQL慢查询日志对有效率问题的SQL进行监控
--查看是否开启慢查询日志
show variables like 'slow_query_log';
--查看慢查询日志的存储路径
show variables like 'slow_query_log_file';
(docker中mysql需要到mysql容器中查询)
--设置慢查询日志的路径
set global slow_query_log_file='/home/mysql/sql_log/mysql-slow.log';
--设置将未使用索引的sql记录到慢查询日志中
set global log_queries_not_using_indexes=on;
--将超过多少秒的查询记录到慢查询日志中
set global long_query_time=1
--------------------------------------------
--慢查询日志的结构
--执行时间
# Time: 2021-03-02T12:12:11.585386Z
--执行SQL的主机信息
# User@Host: root[root] @ localhost [] Id: 10
--SQL的执行信息
# Query_time: 0.001445 Lock_time: 0.000224 Rows_sent: 1 Rows_examined: 1
--SQL执行时间
SET timestamp=1614687131;
--SQL执行内容
show variables like 'slow_query_log_file';
Q
explain的使用
explain的使用方式:
explain select * from tableA;
explain返回的参数:
参数名 | 含义 |
id | 当前查询语句中,每个查询sql的编号 |
select_type | 查询的类型,主要用于区分普通查询、联合查询、嵌套的复杂查询 |
table | select语句关联的表 |
partitions | 表所使用的分区 |
type | 检索类型 |
possible_key | 可能用到的索引,如果为空,则没有可能用到索引 |
key | 实际使用到的索引,如果为空,则没有索引 |
key_len | 使用索引的长度,在不影响精度的情况下,长度越小越好 |
ref | 显示索引的哪一列或常量用于查找索引上的值 |
rows | MySQL认为用于返回请求所必须扫描检索的数据行数,越大越不好 |
filtered | 一个百分比的值,和rows 列的值一起使用,可以估计出查询执行计划(QEP)中的前一个表的结果集,从而确定join操作的循环次数。小表驱动大表,减轻连接的次数 |
Extra | 关于MYSQL如何解析查询的额外信息 |
主要参数再解释:
select_type:
查询的类型,主要是用于区别普通查询,联合查询,嵌套的复杂查询
simple:简单的select 查询,查询中不包含子查询或者union
primary:查询中若包含任何复杂的子查询,最外层查询则被标记为primary
subquery:在select或where 列表中包含了子查询
derived:在from列表中包含的子查询被标记为derived(衍生)MySQL会递归执行这些子查询,把结果放在临时表里。
union:若第二个select出现在union之后,则被标记为union,若union包含在from子句的子查询中,外层select将被标记为:derived
union result:从union表获取结果的select
type:
常见的有:all , index , range , ref , eq_ref , const , system , null 八个级别。
性能从最优到最差的排序:system > const > eq_ref > ref > range > index > all
all:(full table scan)全表扫描,数据量很大(百万级别)全表扫描会非常慢。
index:(full index scan)全索引文件扫描,效率比all好一些
range:只检索给定范围的行,使用索引来匹配行。缩小范围,自然比全表扫描和全索引文件扫描要快。sql语句中一般会有between,in,>,< 等查询。
ref:非唯一性索引扫描,返回所有匹配某个单独值的行。
eq_ref:唯一性索引扫描,返回匹配某个单独值的唯一行
const:表示通过索引一次就可以找到,const用于比较primary key 或者unique索引。因为只匹配一行数据,所以很快,若将主键至于where列表中,MySQL就能将该查询转换为一个常量。
system:表只有一条记录(等于系统表),这是const类型的特列
extra:
Using filesort:说明MySQL会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序” 。出现这个就要立刻优化sql。
Using temporary:使用了临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和 分组查询 group by。出现这个更要立刻优化sql。
Using index:表示相应的select 操作中使用了覆盖索引(Covering index),避免访问了表的数据行,效果不错!如果同时出现Using where,表明索引被用来执行索引键值的查找。如果没有同时出现Using where,表示索引用来读取数据而非执行查找动作。
覆盖索引(Covering Index) :也叫索引覆盖,就是select 的数据列只用从索引中就能够取得,不必读取数据行,MySQL可以利用索引返回select 列表中的字段,而不必根据索引再次读取数据文件。
Using index condition:在5.6版本后加入的新特性,优化器会在索引存在的情况下,通过符合RANGE范围的条数 和 总数的比例来选择是使用索引还是进行全表遍历。
Using where:表明使用了where 过滤
Using join buffer:表明使用了连接缓存
impossible where:where 语句的值总是false,不可用,不能用来获取任何元素
distinct:优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作。
参考:https://www.jianshu.com/p/838d5ff61ccc
Q
索引失效的情况
1、范围查询之后的索引会失效
跳过范围查询的字段建立索引:比如查询sql为
select id,author_id from article where category_id = 1 and comments > 1 order by views desc limit 1
建立联合索引应该跳过comments,只包含category_id和views
2、左连接左表索引失效,右连接右表索引失效
因为左连接的左表是主表,左表必定要全表扫描,所以应该将索引建在右表上;
因为右连接的右边是主表,右表必定要全表扫描,所以应该将索引建在坐标上;
3、违背最佳左前缀原则的条件查询将导致索引失效
如设置索引为idx_nameAgePos,条件中如果有以下情况,将导致索引失效
(1)name未出现在条件中(带头大哥丢失)
(2)name = '' and pos = ''(中间兄弟断掉)
总结:使用复合索引条件查询的条件中:带头大哥不能死,中间兄弟不能断
4、不在索引列上做任何操作(计算、函数、(自动或手动)类型转换),会导致索引失效
5、存储引擎不能使用索引中范围条件右边的列
6、尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select *
7、mysql在使用不等于(!= 或者<>)得时候无法使用索引会导致全表扫描
8、is null,is not null也无法使用索引
9、like以通配符开头('%abc..'),索引会失效,变成全表扫描
'abc%'不会导致索引失效
如果必须要用'%abc%'时,那就使用覆盖索引
10、字符串不加单引号索引会失效
11、少用or,用它来连接时索引会失效
Q
索引优化方式
1、最好使用全值匹配查询
2、一定要遵守最佳左前缀规则
3、尽量不要在索引列上进行计算
4、范围查询之后的索引会失效
5、LIKE模糊查询时%要写在最右边
6、覆盖索引不要使用*查询,尽可能只查询所需字段
7、尽量不使用!=、<>、or
Q
in和exists如何抉择
优化原则:小表驱动大表,即小的数据集驱动大的数据集
select * from A where id in (select id from B)
等价于
for select id from B
for select * from A where A.id = B.id
当B表的数据集小于A表的数据集时,用in优于exists
select * from A where id exists (select 1 from B where B.id = A.id)
等价于
for select id from A
for select * from B where B.id = A.id
当A表的数据集小于B表的数据集时,用exists优于in
注意:A表和B表的ID字段应建立索引
索引失效的原因,不知道怎么写,就推荐看下这个视频吧https://www.bilibili.com/video/BV1Sp4y1e7W6/?spm_id_from=trigger_reload