数据库调优经验记录【MySQL】

其原理及本质点击这里 说明：这篇文章很枯燥，但好在全部是干货，如果你能耐心看完相信我，会有收货的。

索引创建

1、选择索引的时候务必要考虑列的离散性

如果你看过我上面推荐的原理的文章你就会知道，索引再创建的过程中是又hashCode处理的，既然是hashCode就必然存在碰撞的可能。所以不能以数据相近的列作为索引。
举例：“性别”列一般存0、1、2，这种是坚决不能做索引的，因为一旦hashCode基本上就会出现碰撞，数据量稍微大一点就会大幅度降低性能。

2、选择索引的时候要考虑热点检索列进行创建

比如用户表，一般检索最多的是 姓名、手机、身份证，几乎不会有通过爱好、身高等这些去检索，所以前三个是可以考虑使用索引的

3、创建索引时要注意数量

一般情况下不要超过6个，一张表如果过多的索引会导致insert and update效率大幅度降低。
当然，如果你的这张表不存在update操作是可以根据实际业务场景进行调整的

4、联合索引应在你的考虑范围之内

比如用户表，在某个场景下，频繁的使用姓名+手机号查询，这个时候你就要考虑使用联合索引了

5、唯一索引特定场景可以考虑

一般情况下不会用唯一索引，因为这个在insert的时候是又一定的性能损耗的，当然可以忽略不计。主要是根据业务需求进行考虑。
比如：某种业务场景中，用户表中的身份证号是不允许重复的，这个时候就可以考虑身份证号列使用唯一索引

6、尽量避免使用外键约束

主要是为了避坑，特别是在业务版本迭代的时候，牵扯到索引列调整时，如果使用了外键约束，就必须先处理约束再操作，额外增加了一些工作量，适得其反。
当然它也不是一无是处，起码在联合查询效率上会高出很多。
总体来讲弊大于利吧

7、业务关联字段创建索引

一般情况下，在业务关联的字段会创建索引。
比如：用户表ID是本表索引
用户扩展表的user_id关联了用户表的ID，那么用户扩展表的user_id必然是索引

8、Order By 字段可以考虑使用索引

注意是考虑使用，不是必须的。
比如：用户消费记录表中，可以根据用户ID、消费时间分组。用户ID是索引，但是消费时间久不是索引。

9、不创建索引的场景

9.1 数据量非常小
9.2 存在大量重复数据
比如只要两个字段的业务关系表

10、索引创建的空间考虑

创建索引的列，其数据内容不能过大，比如你存储的一个大json，数据类型是text，这就不能使用索引了。

11、统一编码格式

如果编码格式不统一，导致隐式字符编码转换
果两个表的字符集不一样，一个是utf8mb4，一个是utf8，因为utf8mb4是utf8的超集，所以一旦两个字符比较，就会转换为utf8mb4再比较。转换的过程相当于加了CONVERT(id USING utf8mb4)函数，那又回到上面的问题了，用到函数就用不上索引了。

12、特殊情况下使用前缀索引

13、建表时注意索引列靠前放

总结

联合索引使用过程中遵循哪些原则?
最左前缀
最常使用的字段放置联合索引的前面
离散度高
离散度高的字段选择性将会更好.
最少空间

索引使用

1、最左前原则

这个不用多说，小白都知道的，where子句后的条件中，带有索引的必然是放在最左边的，为啥？因为mysql按照顺序进行解读和筛查过滤数据的

2、模糊查询

百分号放最右
放左边会导致索引失效

3、覆盖索引查询

4、避免索引参与计算

尽量在sql查询语句中把索引列参与到计算中

5、索引列尽量避免or

使用or会导致索引失效

6、in和exists中通常情况下使用EXISTS

in是不走索引的

7、避免 隐式类型转换

select * from t where id = 1如果id是字符类型的，1是数字类型的，你用explain会发现走了全表扫描，根本用不上索引，为啥呢？因为MySQL底层会对你的比较进行转换，相当于加了 CAST( id AS signed int) 这样的一个函数，上面说过函数会导致走不上索引。

Where

1、避免无效条件

遇到过很多小菜鸟在使用Mybatis的时候用where 1=1的。。。。。劝你不要这样哈，最好用标签体

2、使用where替换having

避免使用HAVING字句，因为HAVING只会在检索出所有记录之后才对结果集进行过滤，而where则是在聚合前 刷选记录，如果能通过where字句限制记录的数目，那就能减少这方面的开销。HAVING中的条件一般用于聚合函数 的过滤，除此之外，应该将条件写在where字句中

3、使用表的别名

当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减 少哪些友列名歧义引起的语法错误。

4、用union all替换union

当SQL语句需要union两个查询结果集合时，即使检索结果中不会有重复的记录，如果使用union这两个结果集 同样会尝试进行合并，然后在输出最终结果前进行排序，因此如果可以判断检索结果中不会有重复的记录时候，应 该用union all，这样效率就会因此得到提高

5、最终的还是最左前原则

6、尽量避免在 where 子句中对字段进行 null 值判断

导致引擎放弃使用索引而进行全表扫描

7、避免 隐式类型转换

select * from t where id = 1如果id是字符类型的，1是数字类型的，你用explain会发现走了全表扫描，根本用不上索引，为啥呢？因为MySQL底层会对你的比较进行转换，相当于加了 CAST( id AS signed int) 这样的一个函数，上面说过函数会导致走不上索引

8、考虑between and

select

1、不要select *

用哪些字段查哪些

2、注意 count(1)与count(*)

如果你的数据表没有主键，那么count(1)比count()快
如果有主键的话，那主键（联合主键）作为count的条件也比count()要快
如果你的表只有一个字段的话那count()就是最快的啦
count() count(1) 两者比较。主要还是要count(1)所相对应的数据字段。
如果count(1)是聚索引,id,那肯定是count(1)快。但是差的很小的。
因为count(),自动会优化指定到那一个字段。所以没必要去count(?)，用count(),sql会帮你完成优化的

3、用union all替换union

当SQL语句需要union两个查询结果集合时，即使检索结果中不会有重复的记录，如果使用union这两个结果集
同样会尝试进行合并，然后在输出最终结果前进行排序，因此如果可以判断检索结果中不会有重复的记录时候，应
该用union all，这样效率就会因此得到提高

其他

建表时字段类型及长度

用varchar/nvarchar 代替 char/nchar
尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。

根据数据量考虑横向或者纵向分表

核心数据与非核心数据可以考虑查分主表和附属表

核心思想

1、空间考虑 : 建表、字段属性、索引

2、时间考虑：查询与更新

3、根据存储过程排查