MySql优化随笔
每次百度sql优化都是那么几个东西,这次自己来写一下自己的总结
一、数据库是怎么存数据的
首先你要知道数据库中的数据是怎么存储的,数据库将数据放置在一个叫做数据页的结构中,这个数据页中的数据用单链表串起来,ok,到这,我只要找到这个数据页就能遍历他,也就是能查到所有数据了,但是一个页放不了所有数据,所以呢,得在增加一个页,我们成为页溢出,或者叫页分裂,一大堆数据得用多个页储存,多个页用双向链表串起来,这样数据就都能查到了.
光查到还不行,我们还得更快的查到,所以数据存放就是个问题了,不能乱放,得有顺序的放,有顺序的放就会导致放的时候比较麻烦(比如像arraylist一样,插入得把后面的数据全部移动才能完成中间的插入),但是这样方便查找,所以我们得按顺序去存放数据,但是刚才也说了我们不能把所有数据都放到一块,所以数据放到页中,多个页散乱起来也不好管理,所以还要有一个管理页的东西,把页管理起来,自然而然就会形成一个叫树的结构,这样我们只要有根节点在手,所有数据都能查找到了(这个根在mysql中是位置不变的,固定的),由于有序,这就是一个搜索树.
二、树
刚才说到树,我们听过的有二叉树,二叉搜索树,平衡二叉树,红黑树,树就是一种能更快的查找数据的数据结构,那么mysql中用的是哪种树呢?
上面的都不是(所以上面那个图没画成二叉树),我们知道,树的查找跟树的深度有关系,所以为了更快的查找我们不能一个节点只能有2个儿子,我们让他有多个,这样下来,一个瘦高的树就会变的矮胖,更加方便查找,这样的一棵树我们称之为多路搜索树(传说中的B-树,不念B减树,就是B树,中间是杠不是减号,就是多路搜索树),B树有个特点,他的每个节点都能储存数据,这样的话我们要遍历树中的内容,必须使用中序遍历,这样搞就很麻烦,于是出现了B-树另外一种变种,B+树,B+树比B-树更加矮胖,说明他查找效率更高,而且B+树规定所以内容必须储存在叶子节点中,就是树的最下面一层,而且这最下面一层还得用链表串起来这样我们要遍历所有数据就更好查找了,直接遍历最下面一层的链表就行了,所以我们的mysql用的就是这种树,叫B+树
具体树的数据结构可以自己去看关于数据结构的书,这里参考小灰的漫画数据结构
B-树 https://mp.weixin.qq.com/s/rDCEFzoKHIjyHfI_bsz5Rw
B+树 https://mp.weixin.qq.com/s/jRZMMONW3QP43dsDKIV9VQ
B+树示意图
二、索引
B+树是搜索树,所以上面那些没有存值的节点,自然就类似于一个目录,这个目录,就是我们说的索引,一个索引就对应一颗B+树
数据库在存储数据时,会根据主键,建立一颗向上面一样的B+树(如果没有主键,数据库会在储存数据的时候自动给你一个隐藏列作为主键),而我们说的优化,就是如何更好的使用这个树,这才是sql优化的关键点.
这个树是有序的,所以你要是随便在中间插入数据,一定会调整整个的树的结构,(为了保证有序性嘛),所以我们要知道的第一点,索引会影响插入,修改,删除的效率.
接下来我们说说如何利用这个树(索引)
索引的分类
1.聚簇索引(就是主键索引)
表自动会创建这个索引,这个B+树,会把主键的值作为索引KEY按顺序储存,每个叶子节点会将整个数据内容全部储存下来,也就是你一条数据里有什么,我这个叶子节点中就有什么,最慢的查询,全表扫描,就光用我这颗树就行,我啥都有
2.非聚簇索引(二级索引)
除了主键的这个索引,其他索引都是非聚簇索引,也就是你自己建立的那个索引,就相当于在表空间下,新建立了一个B+树,这个树跟上面不一样,这个树是用你指定的列的值作为KEY按顺序储存,你指定的 列的值和主键的id 作为值储存在叶子节点中,也就是说,你要是根据我来查询,查完之后还得拿着主键id再去聚簇索引中查找一遍,才能拿到所有的值,这个过程称之为回表,那就又有一个优化点,尽量减少回表操作
查询非聚簇索引是顺序io / 回表是随机io,顺序io要比随机io快很多 ,如果回表查询量大,偏向于使用全表扫描,直接访问聚簇索引
3.联合索引(这个本质还是个二级索引)
这颗树关键点如下:
假如按照A/B/C三列来建立了一个联合索引,联合索引是一棵树, 分别给ABC三列建立二级索引,是三棵树
假如按照A/B/C三列来建立了一个联合索引,会先按照A的值作为KEY,当A相同时,再按照B的值作为KEY,当B相同时,再按照C的值作为KEY的顺序来储存数据,叶子节点中数据是这三列的值和主键id
看到这里是不是感觉跟order by字段好相似,这个确实跟order by有关系
三、索引的优化
1.索引不是万能的,不能随便加索引
上面说索引会影响插入,修改,删除的效率,这个影响不止一点点,因为一个索引就是一棵树,你要是在某列上建立了多个索引,当修改这列时,会重新调整相关的好几颗树,效率就浪费在这里了,所以索引对查询有好处,对修改没有一点好处,不能随便建立索引
2.能用主键做条件,就别用别的
根据主键查询只会访问聚簇索引,而且聚簇索引中包含其他所有数据,所以说这是最快的访问方式
3.根据列中数据的分散性,来决定是否添加索引
列中数据越分散,通过条件筛选后留下来的数据就会越少,比如身份证号,等值查询一次匹配一个,性别,等值查询一次匹配一坨,我们知道我们二级索引是可能需要回表的,你查询出来一坨的时间其实并不多,但是拿着你返回的一坨id去回表,这就浪费时间了,所以,列中数据越分散,加索引后的查询效率会越高
4.在有索引的列中少使用null,或者说少用null作为条件去查询
因为数据库中null不等于null,所以即便是唯一约束,都无法限制多个null的插入,所以一般某列设置为唯一约束,都要限制非空,否则可能没有唯一的意义了
5.巧用联合索引
第一点 : 联合索引建立的顺序就是按顺序一级一级的建立,所以我们查询的时候有个最左匹配特性,比如按照name,age,sex建立联合索引,你按照name,age,sex三列查询(顺序无所谓,查询优化器会帮你做优化)就能用上索引,你按照name,age两列也能用到索引,你按照name单列也可以,因为这些都是联合索引的全部或者局部,但是你要是按照age或者sex单列,或者age,sex两列这就不行了,因为我name是不知道是不是有序的,这时优化器可能就更偏向于使用全表扫描了
第二点 : 我们上面说了,联合索引的叶子节点中数据是这三列的值和主键id,如果你查询后显示的条件也是这三列,那么数据在二级索引的B+树中就有,就不会去回表了,少一步回表操作,当然更快,这叫覆盖索引
6.利用索引覆盖
原因如上,同时,我们也要求显示的列尽量不要用*,生产环境中,需要什么查什么,不要随便写*,目的就是为了碰上覆盖索引
7.关于排序
单列排序能直接使用索引,但是如果是多列排序就要考虑一下了,mysql中规定规定使用联合索引的各个排序列的排序顺序必须是一致的.在多列排序中,如果要用就只能使用联合索引,而联合索引建立的B+树有特定顺序,如果sql中多列排序顺序与B+树中顺序统一,则可以使用,但是如果建立了ABC联合索引,但是order by A,B desc ,C 这样就无法使用联合索引了
mysql8的新特性,降序索引 https://www.cnblogs.com/ivictor/p/9072361.html
8.关于模糊查询
我们都知道模糊查询会全表扫描,但是为什么呢?因为建树的时候我是根据数据的值一个一个比较出顺序建立的树,但是你给我一个%开头的数据,比如%aaa%,那就是匹配谁都可以了啊,所以只能用全表扫描了.但是索引可以匹配列前缀,如果你给我的是 aaa% 以固定的字符串开头的数据,那我也能利用索引,因为 aaa开头的数据一定是存放在二级索引某一片连续的区域的,但是如果你要是%aaa就不行了,如果非要这样做,可以在入库时将数据的逆序字符串也存入数据库,也建立索引这样就能利用这个逆序列做 aaa%的匹配了
9.只为用于搜索、排序或分组的列创建索引
也就是只为 where order by group by 后面的列建立索引,不要为查询出来显示的列建立索引
10.不要冗余索引
比如我们建立了联合索引ABC,和单列的非聚簇索引A,如果单纯查找A='aaa'的数据,其实这两个索引理论上我们都能利用到,而联合索引的功能比单列的A的索引还广,所以完全可以删除掉A索引,功能没有差别,还少维护一颗B+树
11.保证索引列是以单独列的形式出现
a = 5+3 可以利用索引, 但是 a-3 = 5 就无法利用索引了 ,因为没有一棵树中的值对应着 a-3的值,同时优化器也不会自动优化这种写法
12.索引中的数据占用的空间尽量小
比如bitint效率就会高于int,因为你占用数据空间越小,表示我同样大小的地方就能多存数据,同一个节点数据越多,B+树就越矮胖,反之,就越瘦高,影响查找的层数,所以某些大字段又不需要全值匹配的,可以在索引上限制只对前多少个字符建立索引
13. or 的使用,不要和没有索引的条件or在一起
or使用起来要小心,因为有优化器的存在,比如我们有一条语句select * from table where a>1 or b='a',这里的a有索引,而b没有索引,优化器在优化SQL时,会把没有索引的条件作为true(因为筛选要进行全表扫描才行),那么sql会被优化成为select * from table where a>1 or TRUE, or TRUE ,那就是true ,接着优化就是select * from table where true,所以要全表扫描,a的索引也没用了,也就是说使用到索引的搜索条件和没有使用该索引的搜索条件使用or连接起来后是无法使用该索引的.
14.联表查询时,尽量让数据量小的表做主表
用小表去驱动大表,减少子表被匹配次数
15.关于子查询的order 和 group
子查询中order by 是没用的,不要加
子查询中,sql中如果没有聚合函数以及HAVING子句,group by也是没有用的
以上两个写了也会被查询优化器优化掉
16.关于不相关子查询
不相关就是说子查询可以单独执行,不依赖外部条件,想要优化,就按照单表去优化就行
17.in 后面的集合不要太大
in 后面的集合中数据如果用到索引,优化器会一个一个扫描,但是如果太大了,就会自己直接估算一个值,可能就不会用到索引了,这个阈值跟eq_range_index_dive_limit参数有关
查询eq_range_index_dive_limit : SHOW VARIABLES LIKE '%dive%' 默认是200,也就是说集合结果超过200个,mysql会自己估算一个成本
四、查询优化器
现在的优化器策略选择是基于成本优化,在sql真正查询之前,优化器会根据我们sql携带的条件和表中数据量(这是一个估值),来选择使用哪种执行计划(比如数据量会影响执行计划的选择,表中一共10条数据,某种情况下,优化器可能就不用索引,直接全表扫描了),所以你的sql具体执行的时候是使用的哪个索引,单纯看sql是看不出来的,需要查询一下执行计划,也就是使用 EXPLAIN 关键字
查询优化器有时候会对sql进行索引合并,既然是索引合并,就得在条件中使用多个索引,但是具体是否合并索引,得看查询优化器做出的成本分析
索引合并(index merge) , 有三种合并索引情况
1.intersection(取交集) , 比如 select * from table where a=? and b=? , ab两列分别建立二级索引,正常情况是按照a筛选数据,回表,然后再回表结果中按照b筛选数据,如果使用了索引合并, 会先查询a条件中的主键,再查询b条件中的主键,然后对两份主键取交集,最后回表,这样做的好处是:索引是按照顺序排好的,所以只需要两次顺序IO,回表时随机IO即可,顺序IO贼快,目的是减少回表的操作
(原文)使用条件:
情况一：二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况。比如联合索引abc,条件中只用了a没有用bc,就不能索引合并
情况二:主键列可以是范围匹配,聚簇索引和二级索引合并的时候,二级索引必须是等值匹配,二级索引值相同时,是按照主键从小到大排序的,所以可以索引合并
2.union(取并集), , 比如 select * from table where a=? or b=? , ab两列分别建立二级索引,正常情况是按照a筛选数据,回表,然后再回表结果中按照b筛选数据,如果使用了索引合并, 会先查询a条件中的主键,再查询b条件中的主键,然后对两份主键取并集,最后回表,这样做的好处是:索引是按照顺序排好的,所以只需要两次顺序IO,回表时随机IO即可,顺序IO贼快
(原文)使用条件:
情况一：二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况。比如联合索引abc,条件中只用了a没有用bc,就不能索引合并
情况二:主键列可以是范围匹配,聚簇索引和二级索引合并的时候,二级索引必须是等值匹配,二级索引值相同时,是按照主键从小到大排序的,所以可以索引合并
情况三:or两边的结果是intersection合并后的结果,可以使用索引合并
3.Sort-Union(先排序后,再取并集),因为union必须使用等值,之后二级索引等值才能利用二级索引中的已经排好序的主键进行取交集操作,所以如果select * from table where a>? or b<? ,是不能直接union的,但是可以先查询a条件中的主键,再查询b条件中的主键,然后对两份主键各自排序,之后再取并集
(原文)使用条件:
在上面union的基础上,还需要保证查找出的主键少,否则排序就会很浪费时间,查询优化器也就不会这样合并索引了
五、单表查询的过程及速度
根据查询优化器计算出来的方式,我们有如下几种查询的过程
1.const 速度为常量,这是最快的一种查询速度,一般的根据主键等值查找,或者根据唯一约束的二级索引查找,一次只查到一条,就是这么快(这时虽然会回表,但是也是会很快),但是条件中不能有跟null产生关系的
2.ref 速度也挺快,一般是由于普通二级索引等值匹配,但由于二级索引并不限制索引列值的唯一性,所以可能找到多条对应的记录,再根据这些记录回表查找, 也就是说使用二级索引来执行查询的代价取决于等值匹配到的二级索引记录条数.如果匹配的记录较少,则回表的代价还是比较低的,但如果匹配的记录太多了,可能会直接使用全表扫描(这中间有个叫查询优化器的东西,在sql执行之前,会在表的元数据表中先评判一下用哪种方式查询,比如在这里,表中一共10w条数据,查询优化器估摸了一下大约有9.9w数据会被匹配到,就会直接跳过索引,使用全表扫描)
这里关于null要做一个解释
因为数据库中null != null,所以不论是普通的二级索引,还是唯一二级索引,它们的索引列对包含NULL值的数量并不限制,所以我们采用key IS NULL这种形式的搜索条件##最多##只能使用ref的访问方法，而不是const的访问方法。
3.ref_or_null 比上面慢一点,因为条件中多了一些带null值的条件,可能回表次数就会偏多
4.range 范围查询时,比如用 in or关联的条件,速度可能就不会太快了,因为有回表操作
5.index 使用覆盖索引时,也就是说我们可以直接通过遍历二级索引的叶子节点的记录来比较某个条件是否成立，把匹配成功的二级索引记录的列的值直接加到结果集中就行了。由于二级索引记录比聚簇索记录小的多（聚簇索引记录要存储所有用户定义的列以及所谓的隐藏列，而二级索引记录只需要存放索引列和主键），而且这个过程也不用进行回表操作，所以直接遍历二级索引比直接遍历聚簇索引的成本要小很多
6.all 全表扫描,直接访问聚簇索引
六、联表查询过程
联表查询中,有主表和子表,对于主表其实跟上面查询方式一样,然后主表结果根据关联条件去循环匹配子表数据,也就是主表访问一次,子表访问多次(看主表筛选剩下几条)
两张表联接查询相当于两层for循环,三张就是三层
要想加快联表查询速度,需要优化子表查询
1.eq_ref 类似于单表中的const,意味着关于子表查询条件上加入了主键或者唯一索引
剩下的和单表一样
join buffer:如果每次匹配被驱动表都重新将被驱动表加载到内存,这样的话太耗费时间了,所以提出了join buffer这么一块空间,就相当于预加载,一次加载,多次利用,可以调节join_buffer_size这个参数的大小,来加快联表查询速度
七、EXPLAIN
语法 : EXPLAIN {sql语句}
这个表是用来显示结果的,他本质不是用来优化sql的,他的作用是指出你当前sql的一个状态,从中你可以找到下一步应该优化的点,优化sql需要用到上面说的那些方法
id:在一个大的查询语句中每个SELECT关键字都对应一个唯一的id,如果为null,说明是临时表
select_type:有如下几个值
SIMPLE: 简单查询,不包含UNION或者子查询的查询都算作是SIMPLE类型
PRIMARY:主要的查询,对于包含UNION、UNION ALL或者子查询的大查询,主要的那个查询:比如最左侧,或者最外层查询
UNION:不相关联接查询,对于包含UNION、UNION ALL或者子查询的大查询,除了PRIMARY,剩下的都是UNION
DEPENDENT UNION:相关联接查询,对于包含UNION、UNION ALL或者子查询的大查询,如果其他查询都和外层的查询有关联关系,那么类型就是DEPENDENT UNION
UNION RESULT:union关键字有去重效果,去重时会将所有数据放在一个临时表中,这个临时表类型就是UNION RESULT
SUBQUERY:不相关子查询(mysql会将不相关子查询转成一张临时表写入内存或者硬盘,查询时可能直接将sql改写为联接查询,子表只访问一次即可)
DEPENDENT SUBQUERY:相关子查询(mysql无法优化相关子查询,所以可能多次访问子表)
DERIVED:对于采用物化的方式执行的包含派生表的查询，该派生表对应的子查询的select_type就是DERIVED
MATERIALIZED:当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，该子查询对应的select_type属性就是MATERIALIZED
type:有如下几个值
system:如果你表的引擎是MyISAM,就是这个类型,表示精确查询
const:
eq_ref:
ref:
fulltext:全文索引
ref_or_null:
index_merge:索引合并
unique_subquery:如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键进行等值匹配的话，就是unique_subquery
index_subquery:如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到普通索引进行等值匹配的话,就是index_subquery
range:
index:用到了索引,但是需要将索引整个扫描一遍,可以理解为有索引时最低效率
all
上面没写的就是跟单表和联表中写的一样
possible_keys:可能用到的索引,每一种可能用到的索引都会让优化器去计算一遍成本,所以尽量减少这里的值
key:查询时真正使用的索引
ref:联接时用到的外键
rows:扫描的行数,越小越好
filtered:在联表查询中有意义,这个值是一个比例,越小越好,表示在主子表关联后扫描出的rows中,大概有多少比例的数据满足联表最后查出的结果
比如主子表关联条件为 on t1.key = t2.key 这个条件筛选出的rows为100行
同时还有条件是where t1.kkeeyy = ? 这个条件从上面100行中大概只有10行
那么filtered就是10,表示10%
extra:额外的备注,比如你where条件是否用到了索引,是否使用了buffer pool,都会显示在这里
补充1:上面的explain关键字展示的表并不能看出成本 可以在explain和sql语句中间加上 FORMAT=JSON ,就能看到一个json数据
EXPLAIN FORMAT=JSON {sql语句}
补充2:使用explain语句查询之后,可以接着执行 SHOW WARNINGS 这条语句
如果显示code=1003,那么massage就会显示mysql重写后的sql,但是这个sql不是标准sql不能执行,意会即可
八、optimizer trace
通过optimizer trace可以查看优化器优化的过程
1.首先查看优化器过程记录是否开启:默认关闭
SHOW VARIABLES LIKE 'optimizer_trace';
2.接着打开这个功能:
SET optimizer_trace="enabled=on";
SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000; # 这条命令用来设置trace可用的空间大小，如果输出的内容太多，会被截断，要看全部的数据，把这个值调大
3.然后执行sql语句
select ....
4.接着查看优化过程
SELECT * FROM information_schema.OPTIMIZER_TRACE;
四列：分别是 QUERY 查询语句
TRACE 我们重点查看的内容，优化过程
MISSING_BYTES_BEYOND_MAX_MEM_SIZE 因空间不足而忽略掉的文本字节数，为0表示没有忽略，>0表示trace内容显示不全，可以设置
SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;
INSUFFICIENT_PRIVILEGES 权限是否可用，0可用 1不可用
5.关闭优化器记录
SET optimizer_trace="enabled=off";
这里注意一点,我测试时在sqlyog上,发现总也查不到第4步的数据,但是在命令行中执行是可以看到结果的,说明sqlyog工具会在select语句后执行其他sql,所以建议在命令行中测试(navcat没有试过,大家可以自行测试)
trace内容大致分为3个阶段，准备阶段，优化阶段（挨个索引成本分析，分析可用还是不可用），执行阶段（最终阶段）
准备阶段（join_preparation） ： 就是你那条sql语句
优化阶段（join_optimization） ： 转化条件（将一些无用的条件，位置不对的条件【比如on后面的非关联关系的条件会被放到where后】删除或者转移位置 ），分析每张表的每一个索引是否可用（所以我认为如果索引太多，会增加分析过程），预估不同单表的访问成本，尝试增加一些其他辅助条件
执行阶段（join_execution） ： 最终选择的执行结果
九、写在后面
参考文章 : 掘金小册中的<<MySql是怎样运行的:从根上理解MySql> >
作者 文章写的还是很详实的,通过阅读他的这本小册,写了上边的这篇随笔,这本小册读完让我对mysql中的一些原理又略知了三四,但是本人对于小册中很多细节还未能吸收,所以上面的文字描述并不是类似于教学一样教读者怎么做,权当自己的一篇随笔,记录一些关键知识点,简单串联了一下,如果有缘能看到这篇文章,建议可以去我提供的链接或者自己搜索其中知识点,已获得更加清晰的了解.
这篇文章能起到抛砖引玉的作用,足矣
浙公网安备 33010602011771号