MySQL基本知识点梳理和查询优化总结

本文主要是总结了工作中一些常用的操作，以及不合理的操作，在对慢查询进行优化时收集的一些有用的资料和信息，本文适合有mysql基础的开发人员。

目录

• 一、索引相关

• 二、EXPLIAN中有用的信息

• 三、字段类型和编码

• 四、SQL语句总结

• 五、踩坑

• 六、千万大表在线修改

• 七、慢查询日志

• 八、查看sql进程和杀死进程

• 九、一些数据库性能的思考

一、索引相关

1、索引基数：

基数是数据列所包含的不同值的数量。例如，某个数据列包含值1、3、7、4、7、3，那么它的基数就是4。索引的基数相对于数据表行数较高（也就是说，列中包含很多不同的值，重复的值很少）的时候，它的工作效果最好。如果某数据列含有很多不同的年龄，索引会很快地分辨数据行。如果某个数据列用于记录性别（只有"M"和"F"两种值），那么索引的用处就不大。如果值出现的几率几乎相等，那么无论搜索哪个值都可能得到一半的数据行。在这些情况下，最好根本不要使用索引，因为查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。惯用的百分比界线是"30%"。

2、索引失效原因：

1、对索引列运算，运算包括（+、-、*、/、！、<>、%、like'%_'（%放在前面）。

2、类型错误，如字段类型为varchar，where条件用number。

3、对索引应用内部函数，这种情况下应该建立基于函数的索引如select * from template t where ROUND(t.logicdb_id) = 1此时应该建ROUND(t.logicdb_id)为索引，mysql8.0开始支持函数索引，5.7可以通过虚拟列的方式来支持，之前只能新建一个ROUND(t.logicdb_id)列然后去维护。

4、如果条件有or，即使其中有条件带索引也不会使用（这也是为什么建议少使用or的原因），如果想使用or，又想索引有效，只能将or条件中的每个列加上索引。

5、如果列类型是字符串，那一定要在条件中数据使用引号，否则不使用索引。

6、B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走。

7、组合索引遵循最左原则。

3、索引的建立

1、最重要的肯定是根据业务经常查询的语句。

2、尽量选择区分度高的列作为索引，区分度的公式是 COUNT(DISTINCT col) / COUNT(*)。表示字段不重复的比率，比率越大我们扫描的记录数就越少。

3、如果业务中唯一特性最好建立唯一键，一方面可以保证数据的正确性，另一方面索引的效率能大大提高。

二、EXPLIAN中有用的信息

基本用法

1、desc 或者 explain 加上你的sql。

2、extended explain加上你的sql，然后通过show warnings可以查看实际执行的语句，这一点也是非常有用的，很多时候不同的写法经过sql分析之后实际执行的代码是一样的。

提高性能的特性

1、索引覆盖(covering index)： 需要查询的数据在索引上都可以查到不需要回表 EXTRA列显示using index。

2、ICP特性(Index Condition Pushdown)： 本来index仅仅是data access的一种访问模式，存数引擎通过索引回表获取的数据会传递到MySQL server层进行where条件过滤,5.6