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

目录

一、索引相关

二、EXPLIAN中有用的信息

三、字段类型和编码

四、SQL语句总结

五、踩坑

六、千万大表在线修改

七、慢查询日志

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

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

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

一、索引相关

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.组合索引遵循最左原则

索引的建立

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版本开始当ICP打开时，如果部分where条件能使用索引的字段，MySQL server会把这部分下推到引擎层，可以利用index过滤的where条件在存储引擎层进行数据过滤。EXTRA显示using index condition。需要了解mysql的架构图分为server和存储引擎层

3、索引合并(index merge)：对多个索引分别进行条件扫描，然后将它们各自的结果进行合并(intersect/union)。一般用OR会用到，如果是AND条件，考虑建立复合索引。EXPLAIN显示的索引类型会显示index_merge，EXTRA会显示具体的合并算法和用到的索引

extra字段

1、using filesort： 说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序” ，其实不一定是文件排序，内部使用的是快排

2、using temporary： 使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by

3、using index： 表示相应的SELECT操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错。

4、impossible where： WHERE子句的值总是false，不能用来获取任何元组

5、select tables optimized away： 在没有GROUP BY子句的情况下基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作， 不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化

6、distinct： 优化distinct操作，在找到第一匹配的元祖后即停止找同样值的操作

using filesort,using temporary这两项出现时需要注意下，这两项是十分耗费性能的，在使用group by的时候，虽然没有使用order by，如果没有索引，是可能同时出现using filesort,using temporary的，因为group by就是先排序在分组，如果没有排序的需要，可以加上一个order by NULL来避免排序，这样using filesort就会去除，能提升一点性能。

type字段

system：表只有一行记录(等于系统表)，这是const类型的特例，平时不会出现

const：如果通过索引依次就找到了，const用于比较主键索引或者unique索引。 因为只能匹配一行数据，所以很快。如果将主键置于where列表中，MySQL就能将该查询转换为一个常量

eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配 某个单独值的行，然而它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体

range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where语句中出现between、<、>、in等的查询，这种范围扫描索引比全表扫描要好，因为只需要开始于缩印的某一点，而结束于另一点，不用扫描全部索引

index：Full Index Scan ，index与ALL的区别为index类型只遍历索引树，这通常比ALL快，因为索引文件通常比数据文件小。 (也就是说虽然ALL和index都是读全表， 但index是从索引中读取的，而ALL是从硬盘读取的)

all：Full Table Scan，遍历全表获得匹配的行

三、字段类型和编码

1、mysql返回字符串长度：CHARACTER_LENGTH方法(CHAR_LENGTH一样的)返回的是字符数，LENGTH函数返回的是字节数，一个汉字三个字节

2、varvhar等字段建立索引长度计算语句：select count(distinct left(test,5))/count(*) from table; 越趋近1越好

（编辑：晋中站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!