目录
索引创建原则
避免索引失效
(1)全值匹配,对索引中所有列都指定具体值
(2)最左前缀法则
(3)范围查询右边的列,不能使用索引
(4)不要在索引列上进行运算操作,索引将失效
(5)字符串不加单引号,造成索引失效
(6)用or分割开的条件, 如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到
(7)以%开头的Like模糊查询,索引失效。
(8)如果MySQL评估使用索引比全表更慢,则不使用索引。
(9)in 走索引, not in 索引失效
索引创建原则
索引的设计可以遵循一些已有的原则,创建索引的时候请尽量考虑符合这些原则,便于提升索引的使用效率,更高效的使用索引。
1. 字段内容可识别度不能低于70%,字段内数据唯一值的个数不能低于70%
例如:一个表数据只有50行,那么性别和年龄哪个字段适合创建索引,明显是年龄,因为年龄的唯一值个数比较多,性别只有两个选项 。性别的识别度是50%。 男 女
2. 经常使用where条件搜索的字段,例如user表的id name等字段。
3. 经常使用表连接的字段(内连接、外连接),可以加快连接的速度。
4. 经常排序的字段 order by,因为索引已经是排过序的,这样一来可以利用索引的排序,加快排序查询速度。
* 注意:那是不是在数据库表字段中尽量多建索引呢?肯定是不是的。因为索引的建立和维护都是需要耗时的 创建表时需要通过数据库去维护索引,添加记录、更新、修改时,也需要更新索引,会间接影响数据库的 效率。
避免索引失效
(1)全值匹配,对索引中所有列都指定具体值
该情况下,索引生效,执行效率高。
explain select * from tb_seller where name='小米科技有限公司' and status='1' and address='上海市';
(2)最左前缀法则
如果索引了多列,这里指的是复合索引(联合索引),要遵守最左前缀法则。指的是查询从索引的最左前列开始,并且不跳过索引中的列。
注意:如果条件中包含了复合索引的全部字段,那么可以不考虑前后顺序。
匹配最左前缀法则,走索引:
1.explain select * from tb_seller where name='小米科技有限公司'; -- key_len表示索引字段的长度即占字节个数,不同的编码表计算方式不一致
2.explain select * from tb_seller where name='小米科技有限公司' and status='1';
3.explain select * from tb_seller where name='小米科技有限公司' and status='1' and address='上海市';
违反最左前缀法则 , 索引失效:
4.explain select * from tb_seller where status='1';
5.explain select * from tb_seller where status='1' and address='上海市';
如果符合最左法则,但是出现跳跃某一列,只有最左列索引生效:
6.explain select * from tb_seller where name='小米科技有限公司' and address='上海市';
注意:上述sql语句跳跃了status这一列,所以上述sql语句只是对索引name生效,key_len的结果403只是name索引的长度,而address索引字段并没有起到所以效果。
注意:如果条件中包含了复合索引的全部字段,那么可以不考虑前后顺序。
explain select * from tb_seller where address='上海市' and status='1' and name='小米科技有限公司' ;
(3)范围查询右边的列,不能使用索引
1.explain select * from tb_seller where name='小米科技有限公司' and status='1' and address='上海市';
2.explain select * from tb_seller where name='小米科技有限公司' and status>'1' and address='上海市'; -- 只有name和status索引生效
根据前面的两个字段name , status 查询是走索引的, 但是最后一个条件address 没有用到索引。
(4)不要在索引列上进行运算操作,索引将失效
1.-- 3 表示索引 2 表示截取2个字符
select * from tb_seller where substring(name,3,2)='科技';
2.explain select * from tb_seller where substring(name,3,2)='科技';
(5)字符串不加单引号,造成索引失效
1.explain select * from tb_seller where name='小米科技有限公司' and status='1';
2.explain select * from tb_seller where name='小米科技有限公司' and status=1; -- 这里name索引字段生效,status索引字段是无效的
由于,在查询时,没有对字符串加单引号,MySQL的查询优化器,会自动的进行类型转换,造成索引失效。
(6)用or分割开的条件, 如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到
示例,name字段是索引列 , 而createtime不是索引列,中间是or进行连接是不走索引的 :
1.explain select * from tb_seller where name='传智播客教育科技有限公司' and createtime = '2088-01-01 12:00:00';
2.explain select * from tb_seller where name='传智播客教育科技有限公司' or createtime = '2088-01-01 12:00:00';
(7)以%开头的Like模糊查询,索引失效。
如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效。
1.explain select * from tb_seller where name like '传智播客%';
2.explain select * from tb_seller where name like '%传智播客';
3.explain select * from tb_seller where name like '%传智播客%';
解决方案 :通过覆盖索引来解决.
1.explain select sellerid from tb_seller where name like '%传智播客%';
2.explain select sellerid,name from tb_seller where name like '%传智播客%';
3.explain select sellerid,name,status,address from tb_seller where name like '%传智播客%';
4.explain select sellerid,name,status,address,password from tb_seller where name like '%传智播客%';
说明:解决上述sql语句索引失效情况需要使用覆盖索引,而password子字段无索引,所以索引失效。
(8)如果MySQL评估使用索引比全表更慢,则不使用索引。
1.show index from tb_seller; -- 查看下索引
2.create index idx_address on tb_seller(address); -- 单独创建 address字段为索引
3.explain select * from tb_seller where address='北京市'; -- 走索引,反而效率更低,全表扫描
4.explain select * from tb_seller where address='上海市';
(9)in 走索引, not in 索引失效
1.explain select * from tb_seller where sellerid in('baidu','huawei','xiaomi');
2.explain select * from tb_seller where sellerid not in('baidu','huawei','xiaomi');