数据库——索引

二、索引

索引分类：哈希（通过数组+链表），有序数组，搜索树（底层就是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。