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越好

（编辑：河北网）

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