1 索引的使用

概念和分类

索引是帮助MySQL高效获取数据的数据结构。索引的本质：索引是数据结构。简单理解为：排好序的快速查找的数据结构。查找+排序。

功能逻辑	能够建立索引的种类分为：主键索引唯一索引普通索引三种	普通索引：即一个索引只包含单个列，一个表可以有多个单列索引，加速查询排序使用。唯一索引：索引列的值必须唯一，但允许有空值（可以有null，null可以是多个）主键索引：唯一索引+列不可以有null值复合索引：即一个索引包含多个列，专门用于组合搜索，其效率大于索引合并
物理实现	聚集索引非聚集索引	聚簇索引(聚集索引)：并不是一种单独的索引类型，而是一种数据存储方式。具体细节取决于不同的实现，InnoDB的聚簇索引其实就是在同一个结构中保存了B-Tree索引（技术上来说是B+Tree）和数据行。非聚簇索引：不是聚簇索引，就是非聚簇索引
存储数据结构	BTree索引（B-Tree或B+Tree索引） Hash索引 full-index全文索引 R-Tree索引
其他	覆盖索引	覆盖索引：select的数据列只能从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。简单言之即：查询列要被所建的索引覆盖。覆盖索引只是一种查询的一种效果。有如下索引：`alter table users add index idx_user(nickname,mobileno,email);` 同时有如下查询语句：`select nickname, mobileno, email from users where nickname like '%xx%';或select nickname, mobileno from users where nickname like '%xx%';`,则nickname、mobileno、email则为覆盖索引。
	全文索引	对文本的内容进行分词，进行搜索

索引的选择

哪些情况下需要建立索引	1. 主键自动建立唯一主键 1. 频繁作为查询条件的字段 1. 查询中与其他表关联的字段，外键关系已经建立索引，如员工-部门表，部门表的部门ID应该建索引。 1. 用于排序字段 1. 查询中统计或分组的字段(分组的前提必排序) 1. 在高并发的情况倾向于建立组合索引
哪些情况下不需要建立索引	1. 频繁更新的字段 1. 表记录太少 1. Where字段用不到的字段 1. 数据重复过多的字段(国籍、民族、性别)

覆盖索引

如何理解

理解方式一：当能通过读取索引就可以得到想要的数据，那就不需要读取真实的数据行了，即一个索引包含了满足查询结果的数据就叫做覆盖索引。
理解方式二：非聚簇复合索引的一种形式，它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列（即建索引的字段正好是覆盖查询条件中所涉及的字段）。

简单说就是：覆盖索引不是一种真实的索引，是索引结合了查询后的效果，即索引列+主键包含SELECT到FROM之间查询的列即为覆盖索引

覆盖索引的利弊
好处

避免Innodb表进行索引的二次查询（回表）
可以把随机I/O变成顺序I/O加快查询效率：因为非聚集索引会把主键值也添加到二级索引内节点中的目录项记录，所以是顺序IO的。

弊端：索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这是业务DBA，或者称为业务数据架构师的工作。
优化案例

//1.0 创建索引
create index idx_age_classid_name on student (age,classid,name); //2.0 查询：索引失效（优化器判断这还得回表啊，得不偿失，就不会使用索引） EXPLAIN SELECT SQL_NO_CACHE FROM student ORDER BY age,classid; //3.0 使用到了覆盖索引+主键，则会用到索引（优化器判断不用回表即可从索引的叶子节点底部进行排序，效率更高） EXPLAIN SELECT SQL_NO_CACHE age,classid,name,id FROM student ORDER BY age,classid;

索引下推：using index condition

概念
Index Condition Pushdown（ICP）是MySQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式。

在不使用ICP索引扫描的过程

如果没有ICP，存储引擎会遍历索引以定位聚集索引中的行，并将它们返回给MySQL服务器，由MySQL服务器评估WHERE后面的条件是否保留行。

storage层：只将满足index key条件的索引记录对应的整行记录取出，返回给server层。
server 层：对返回的数据，使用后面的where条件过滤，直至返回最后一行。
1. 使用ICP扫描的过程

启用ICP后，如果部分WHERE条件可以仅使用索引中的列进行筛选，则MySQL服务器会把这部分WHERE条件放到存储引擎筛选。然后，存储引擎通过使用索引条目来筛选数据，并且只有在满足这一条件时才从表中读取行。

storage层：首先将index key条件满足的索引记录区间确定，然后在索引上使用index filter进行过滤。将满足的index filter条件的索引记录才去回表取出整行记录返回server层。不满足index filter条件的索引记录丢弃，不回表、也不会返回server层。
server 层：对返回的数据，使用table filter条件做最后的过滤。
1. 技术总结
ICP可以选择开启或关闭。
当SQL使用覆盖索引时，不支持ICP。因为这种情况下使用ICP不会回表，也就不能减少I/O。
并非全部where条件都可以用ICP筛选，如果where条件的字段不在索引列中，还是要读取整表的记录到server端做where过滤。
1. 一句话总结

ICP可以减少存储引擎必须访问回表的次数和MySQL服务器必须访问存储引擎的次数。但是，ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例。

2 索引的增删改查

查看所有索引
show index from tablename\G;
3 索引的创建和设计原则 - 图4
创建索引

方式一：通过create指定
方式二：通过alter更改 ```sql //方式一：创建表时创建索引 CREATE TABLE IF NOT EXISTS stuinfo(
1.0 配置类型/主键约束
id INT PRIMARY KEY unique auto_increment, —主键PRIMARY KEY约束&唯一约束&自增长约束
2.0 配置带索引名的索引
index [mobileno_index] (mobileno(10))
3.0 指定唯一索引
unique [mobileno_index2] (mobileno(10))
);

//方式二：直接创建索引 //创建普通索引 create index mobile_index on user(mobileno);

//方式三：使用alter关键字添加不同的索引 ALTER TABLE tbl_name ADD PRIMARY KEY (column_list);//该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。 ALTER TABLE tbl_name ADD UNIQUE index_name (column_list);//这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。 ALTER TABLE tbl_name ADD INDEX index_name (column_list);//添加普通索引，索引值可出现多次。 ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);//该语句指定了索引为 FULLTEXT ，用于全文索引。

**删除索引**
   - 方式一：通过drop
   - 方式二：通过alter
   - 如果要删除的主键有自增长，需要先去除掉自增长。重点：删除主键时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。
```sql
//1.0 删除普通索引(包含唯一索引)
alter table tablename drop index indexname;
drop index indexname on tablename;
//2.0 删除主键索引 
//用如下的方法去除上面的自增长标识列
show index from user;
//要删除主键的自增长:不书写auto_increment就是删除自增长
alter table user modify COLUMN id int;
//然后再drop主键
alter table user drop primary key;

3 索引的创建和设计原则 - 图5

3 索引的创建原则

详见顶部文档

3 索引的创建和设计原则