1.索引概念
在数据库字段上添加，提高查询效率的一种数据结构。一个字段可以添加一个索引，多个字段可以联合后添加索引，索引就相当于目录。
索引的优点
- 大大减少服务器需要扫描的数据量，也就是IO量
- 帮助服务器避免排序和临时表（尽量避免文件排序，而是使用索引排序）
- 将随机IO变成顺序IO
任何数据库中主键自动添加索引，任何一条记录都有在硬盘上物理存储的编号。MySQL中unique约束，也自动添加索引。
索引的分类
功能逻辑
- 主键索引(唯一且非空)
- 唯一索引(唯一可为空)
- 普通索引(普通字段的索引)
- 全文索引(一般是varchar,char,text类型建立的，但很少用)
- 组合索引(多个字的建立的索引)
物理实现
- 聚簇索引
- 非聚簇索引
底层实现
- B树
- B+树
- Hash
- 二叉查找树
- 红黑树
什么时候添加索引
- 数据量大
- 该字段经常在where后面做条件
- 该字段很少DML操作。（DML重新生成索引）
索引失效
- 模糊查询时，%或者—开头不会使用索引
- 隐式类型转换，索引字段与条件或关联字段的类型不一致。
- 条件中对索引列进行运算或使用函数
-- 无法使用索引
EXPLAIN SELECT * FROM test_idx WHERE SUBSTR(bid, 1) = '1';
EXPLAIN SELECT * FROM test_idx WHERE id - 1 = 1;
-- 可以使用索引
EXPLAIN SELECT * FROM test_idx WHERE id = 4 - 2;
EXPLAIN SELECT * FROM test_idx WHERE id = TIME_TO_SEC(1);
- 使用OR且存在非索引列,OR包含的所有列必须都是独立索引才有可能用到索引
- IS NULL可以使用索引，IS NOT NULL，in、not exists无法使用索引
- 不等于（!= 或 <>）索引失效，主键可以
2.索引的相关语句
创建
create index 索引名 on 表名(字段名)；
删除
drop index 索引名 on 表名；
查看查询语是否使用索引
explain select....
mysql> explain select * from t_stu where id=1;--无索引
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t_stu | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> create index id_index on t_stu(id);--创建索引
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from t_stu where id=1;--有索引
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t_stu | NULL | ref | id_index | id_index | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
3.技术名词
1.回表
InnoDB-主键索引
叶子节点存储的是具体的行数据
InnoDB-非主键索引
非主键索引的叶子节点存储的是主键值
回表：对于非主键字段，先查询对应的主键，再从主键的索引中查到最终的数据。
MyISAM
叶子节点存储的是行数据的地址，额外需要一次寻址，多一次IO
2.覆盖索引
在非主键索引上可以查询到所需要的字段，不需要回表再次查询就叫覆盖索引。
3.最左匹配
组合索引中 先匹配左边，再继续向后匹配；
比如user表中有name+age组成的联合索引，select * from user where name=“纪” and age = 18 就符合最左匹配，可以用的索引。而select * from user where age = 18就不符合，用不到这个索引。
举例：
如果是下面两个sql怎么建索引
select * from user where name="纪先生" and age = 18
select * from user where age = 18
由于最左匹配原则：只需要建立一个组合索引age+name即可（同时出现时，顺序不同也可以）
如果是下面三个sql又该怎么建索引呢
select * from user where name="纪先生" and age = 18
select * from user where age = 18
select * from user where name= "纪先生"
建立name+age和age两个索引，或者建立age+name和name两个索引
看着这两种索引方式都可以，3个sql 也都能使用到索引，其实name+age和age更好，因为索引也是需要持久化存储的，占用磁盘空间，读取的时候也是占用内存的，name+age和age+name这两个占用是一样的，但是name和age单独比较，肯定age占用空间更少，name更长（索引越大，IO次数可能更多）
4. 索引下推
组合索引中尽量利用索引信息，来尽可能的减少回表的次数
案例：name+age的组合索引
如果没有索引下推的查询是 在组合索引中通过name查询所有匹配的数据，然后回表根据ID查询对于的数据行，之后在筛选出符合age条件的数据。
索引下推就是组合索引中通过name查询匹配再根据age找到符合的数据ID，然后回表根据ID查询对应行数据，明显会减少数据的条数
5.索引匹配方式
1.全值匹配
指和某个索引中的所有列进行匹配，例如使用数据库sakila中的staff表
新建一个三个字段的联合索引：
mysql> alter table staff add index index_n1(first_name,last_name,username);
执行sql:
mysql> explain select * from staff where first_name="Mike" and last_name="Hillyer" and username="Mike"
其中的ref是三个const， 用到三个字段，能全匹配一条数据
2. 最左前缀匹配
只匹配组合索引中前面几个字段
执行sql:
explain select * from staff where first_name="Mike" and last_name="Hillyer";
ref只出现2个const,比上面全值匹配少一个，就只匹配了前面两个字段
3. 匹配列前缀
可以匹配某一列的的开头部分，像like属性
执行sql:
explain select * from staff where first_name like "Mi%";
type=range ,是个范围查询，可以匹配一个字段的一部分，而不需要全值匹配
所以如果有模糊匹配的字段在建立索引的时候不要放在索引的最前面，否则有索引也不能使用，如下
4. 匹配一个范围值
可以查找某一个范围的数据
范围的条件：>,>=,<,<=,between
explain select * from staff where first_name > "Mike";
范围列可以用到索引，但是范围列后面的列就无法用到索引了(索引最多用于一个范围列)
比如一个组合索引age+name 如果查询条件是where age>18 and name="纪"后面的name是用不到的索引的。
关于不等于是否走索引的问题
结论：只有主键会走，唯一键和普通索引都不会走。
在employee表中建了唯一索引employee_num和联合索引employee_num+name，结果就是下图的执行情况。
5. 精确匹配某一列并范围匹配另一列
可以查询第一列的全部和另一列的部分
explain select * from staff where first_name = "Mike" and last_name like "Hill%";
6. 只访问索引的查询
查询的时候只需要访问索引，不需要访问数据行，其实就是索引覆盖
explain select first_name,last_name,username from staff where first_name="Mike" and last_name="Hillyer";
extra=Using index 说明是使用了索引覆盖，不需要再次回表查询。
