mysql专题

前言

最近在繁忙的业务中碰到了许许多多的sql优化问题（mysql的优化），从此类业务中我获得不少优化技巧，因此写下这篇文章分享给各位。

sql的执行顺序

1.from 2.on 3.join 4.where 5.group by 6.having 7.select 8.distinct 9.limit 10.函数或者自定义函数(不在where里面而在select 后面 from前面的函数)

优化技巧

1.索引
都知道索引是优化查询速度的最大的功臣，但是索引为何速度快，为什么全表的速度慢，这个问题也可以从mysql的数据结构中获取解答，我们在添加索引的时候有索引方法与索引类型两种，但是我们用的最多的还是nomal加btree，可能大家会问为什么这样干，那些工作经验长的点的人也许会说别人都是这么建索引的，所以你这样建肯定没错，索引中用的比较多的还是唯一索引和nonal索引，唯一索引：字面意思，这个值只能有一个，重复就会报错，那么索引方法该选择btree,还是说hash,对于这种类型的索引一般来说都是hash，众所周知，java里面的hashmap，号称查询奇快，因为在它进行hash值转化的时候内存的所在位置已经确定好了，知道hash值等于它的内存地址。nomal也就是可以重复值，这类的索引可以做范围查询，btree索引方法就显得非常重要了，btree的数据结构是平衡二叉树演化而来，但是btree是多路的查找树，因此获取一个范围值的速度肯定优于hash。由此可得hash获取单一数据速度快，btree范围查询速度快

2.多用explain

以上sql有两个连接查询，explain分析到了它的索引使用情况key能够表示，type表示的是连接类型，all代表整表查询,因为这个sql没有在user表上使用索引，但是在另外一张表关联的时候使用到了主键索引，因此这个连接查询的速度很快，ref与eq_ref其实都差不多,真正差距大的是unique_subquery，index_subquery ，range，index ，ALL，
row这个参数非常重要，一般我调优的时候主要看这个，这代表着查询的行数，如果使用到了索引这个rows的值会非常小.
patitions这个参数也许大家没怎么接触过，但是这个参数在大量数据存储的时候尤其重要，它的意义是分区，分区表非常少见，mysql分区通常是range分区，并且使用时间来分，比如一个表存的数据是2014-2019年的，我们可以每一年分一个区，这样当我们相要查询2016年的数据就会从2016这个分区进行查询，这样就能减少数据库行数，增加单表容量

索引的触发条件

一般来说索引的触发条件最常见的无非三种>,<,=也有几乎没什么作用的like '内容%‘这种情况。

优化诀窍

1.子查询能不写就不写，如果要写的话必须小，或者能够在里面分页也行。
2.不要在where 条件里面写函数，这样的话每一行数据都会走这个函数，并且索引直接失效，写函数的最好地方是select 后面
3.碰到or这种查询条件并且查询速度慢的话，可以使用union或者union all
4.函数不要写在子查询里面，除非万不得已
5.case when能不写就不写，也许有人使用行转列用到了，但是仅限于千把条数据玩玩。所以那些行转列其实用处不大，宁可使用缓存,或者存储过程定时更新一张临时表，因为case when太多数据，线程直接卡死。
6.合理建立分区表
7.索引建立在那些<,>,=的键上面