between and 走索引吗_开发人员不可错过的MySQL索引和查询优化

一、索引相关

1.索引基数

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

2.索引失效原因

• 对索引列运算，运算包括(+、-、*、/、！、<>、%、like'%_'(%放在前面)
• 类型错误，如字段类型为varchar，where条件用number。
• 对索引应用内部函数，这种情况下应该建立基于函数的索引
• 如select * from template t where ROUND(t.logicdb_id) = 1
• 此时应该建ROUND(t.logicdb_id)为索引，mysql8.0开始支持函数索引，5.7可以通过虚拟列的方式来支持，之前只能新建一个ROUND(t.logicdb_id)列然后去维护
• 如果条件有or，即使其中有条件带索引也不会使用(这也是为什么建议少使用or的原因)，如果想使用or，又想索引有效，只能将or条件中的每个列加上索引
• 如果列类型是字符串，那一定要在条件中数据使用引号，否则不使用索引；
• B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走
• 组合索引遵循最左原则

3.索引的建立

• 最重要的肯定是根据业务经常查询的语句
• 尽量选择区分度高的列作为索引，区分度的公式是 COUNT(DISTINCT col) / COUNT(*)。表示字段不重复的比率，比率越大我们扫描的记录数就越少
• 如果业务中唯一特性最好建立唯一键，一方面可以保证数据的正确性，另一方面索引的效率能大大提高

二、EXPLIAN中有用的信息

1.基本用法

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

2.提高性能的特性

• 索引覆盖(covering index)：需要查询的数据在索引上都可以查到不需要回表 EXTRA列显示using index
• ICP特性(Index Condition Pushdown)：本来index仅仅是data access的一种访问模式，存数引擎通过索引回表获取的数据会传递到MySQL server层进行where条件过滤,5.6版本开始当ICP打开时，如果部分where条件能使用索引的字段，MySQL server会把这部分下推到引擎层，可以利用index过滤的where条件在存储引擎层进行数据过滤。EXTRA显示using index condition。需要了解mysql的架构图分为server和存储引擎层