索引小技巧(个人整理)
个人整理,如果有什么不对的,知识不能误导 请留言谢谢
Explain (table);
查看表的基本信息,字段名 字段类型 索引 是否null 默认等基本数据。
查看索引情况的使用
show indexes from table_name;
字段的区分度查询
select COUNT(DISTINCT column_name)/COUNT(*) from table_name
区分度越大 索引效果就越好。
建立索引
一 区分度大的
通过上面的公式 取得区分度。
联合索引创建的时候,摆放字段顺序,应该是优先为区分度大的 字段放前面。效果才是最好。
二 最左前缀匹配原则
MySQL会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如
select * from t_base_user where type="10" and created_at<"2017-11-03" and status=1, (该语句仅作为演示)
在上述语句中,status就不会走索引,因为遇到<时,MySQL已经停止匹配,此时走的索引为:(type,created_at),其先后顺序是可以调整的,而走不到status索引,此时需要修改语句为:
select * from t_base_user where type=10 and status=1 and created_at<"2017-11-03"
即可走status索引。
三 函数运算
不要在索引列上,进行函数运算,否则索引会失效。因为b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。
四 索引失效情况罗列
1 打破最左前缀的规则
2 在索引列上做任何操作(函数,计算,类型转换),会导致全表扫描
3 存储引擎不能使用索引中范围条件右边的列。——范围之后索引失效。(< ,> between and,) 比如 where a ='a' and b > 5 and c ='c' 那么 只有范围条件后面的c 索引是失效的。
5、尽量使用覆盖索引(只访问索引的查询(索引和查询列一致)),减少select*。——按需取数据用多少取多少。
6、在MYSQL使用不等于(<,>,!=)的时候无法使用索引,会导致索引失效。
7、is null或者is not null 也会导致无法使用索引。
8、like以通配符开头('%abc...')MYSQL索引失效会变成全表扫描的操作。——覆盖索引。
两边均有%时和左边有%时,索引会失效,而%在右边时,索引可用。
解决like'%字符串%'时索引不被使用的方法:添加覆盖索引。
9、字符串不加单引号索引失效。
10、少用or,用它来连接时索引会失效。