二、索引
索引分类:哈希(通过数组+链表),有序数组,搜索树(底层就是B+树)
1.索引:
Mysql索引主要有两种结构:B+Tree索引和Hash索引。我们平常所说的索引,如果没有特别指明,一般都是指B树结构组织的索引(B+Tree索引)。
其中key值表示没有用到索引
(1)单索引:加在某一列上
(3)主键索引效率高 主键索引是自带的 不用创建
主键索引 普通索引
主键索引效率比普通索引效率高
这种情况下普通索引需要回表,但并不是普通索引都要回表的,取决于要查询的内容 比如只需要查询id=100 就不需要回表了
2.创建索引
(1)普通索引:
加上索引后,key值不为null
explain +sql语句: 查看执行计划 通过这条执行计划加sql语句可以知道某个字段有没有加索引,但是加了索引,mysql不一定按着索引查询,这取决于mysql的服务器
其中key值表示有索引
(2) 组合索引:
最左前缀原则,也可以提高查询效率,
(name,age)如果按名字查询的多就把name放在第一个。
创建组合索引:
----->alter table t1 add index t1_ns(name,age);
------->explain select * from t1 where name=‘w5’;
------>explain select * from t1 where age=11;
3.索引失效
(1)条件中有or
没有or时,使用的是主键索引
有or时,索引失效
【注】:要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引
(2)对于多列索引,不是使用的第一部分,则不会使用索引
** (3) like查询是以%开头**
(i)like以%结尾不会失效:
(ii)like以%开头会失效:
(4)如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引
(5)类型转换
4.sql优化——索引优化(最重要的!!!)避免回表 ???
(1)选择索引列:
创建索引简单,但是在哪些列上创建索引则需要好好思考。可以考虑在where字句中出现列或者join字句中出现的列上建索引
(2)最左前缀原则:
B+树的数据结构决定了在使用索引的时候必须遵守最左前缀原则,在创建联合索引的时候,尽量将经常参与查询的字段放在联合索引的最左边。
(3)like的使用 【注】like以%开头会索引失效
一般情况下不建议使用like操作,如果非使用不可的话,需要注意:like '%abd%'不会使用索引,而like ‘aaa%’可以使用索引。这也是前面的最左前缀原则的一个使用场景。
(4)不能使用索引说明
mysql会按照联合索引从左往右进行匹配,直到遇到范围查询,如:>,<,between,like等就停止匹配,a = 1 and b =2 and c > 3 and d = 4,如果建立(a,b,c,d)顺序的索引,d是不会使用索引的。但如果联合索引是(a,b,d,c)的话,则a b d c都可以使用到索引,只是最终c是一个范围值。
(5)order by
order by排序有两种排序方式:using filesort使用算法在内存中排序以及使用mysql的索引进行排序;我们在部分情况下希望的是使用索引。
(i)
select test_index where id = 3 order by id desc;
//如果ID是单列索引,则order by会使用索引
(ii)
select test_index where id = 3 order by name desc;
//如果ID是单列索引,name不是索引或者name也是单列索引,则order by不会使用索引。
因为Mysql的一次查询只会从众多索引中选择一个索引,而这次查询中使用的是ID列索引,而不是name列索引。在这种场景下,如果想让order by也使用索引的话,就建立联合索引(id,name),这里需要注意最左前缀原则,不要建立这样的联合索引(name,id)。
最后需要注意mysql对排序记录的大小有限制:max_length_for_sort_data 默认为1024;也就意味着如果需要排序的数据量大于1024,则order by不会使用索引,而是使用using filesort。