mysql索引

什么是索引？

MySQL官方对索引的定义为：索引是帮助mysql高效获取数据的数据结构。所以说索引的本质是：索引结构。
索引的目的在于提高查询效率，可以类比字典、火车站的车次表、图书的目录等。
可以简单的理解为”排好序的快速查找数据结构“，数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向数据），这样就可以在这些数据结构上实现高级查找算法，这种数据结构，就是索引。

索引优势

提高数据检索效率，降低数据库IO成本

索引劣势

创建索引和维护索引也需要耗费时间，具体地，当对表中的数据进行增加，删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率

索引的类型

主键索引：数据列不允许重复，不允许为null,一个表只能有一个主键
唯一索引：数据列不允许重复，允许为NULL,一个表允许多个列创建唯一索引
普通索引：基本的索引类型，没有唯一性的限制（数据列可以重复），允许为NULL值

对于如下sql语句：where a=1 and b=1；where b=1；where b=1 order by time desc，表的索引该如何建立？

基于索引的最左匹配原则，想让上述的3条sql语句的查询都能使用到索引，需要为表建立如下2个索引:

index idx_a_b(a,b),
idnex idx_b_time(b,time)

创建索引的原则

• 只为用于搜索、排序或者分组的列创建索引；
• 索引列的类型尽量小
• 视情况只对字符串值的前缀建立索引
• 避免冗余和重复索引

索引建立或者使用的原则

建立索引的情况：
1、每个数据表中主键索引是必须的
2、频繁作为查询条件的字段应该创建索引
4、如果字段有重复可以考虑普通索引，普通索引的要求是字段可以重复
5、如果字段没有重复，可以考虑唯一索引，唯一索引的要求是字段不能重复
6、定义有外键的数据列一定建立索引，在联合查询的时候，外键是索引的，查询会走索引提高查询效率
7、如果是复合索引，要求复合最左匹配原则
8、索引不宜建立过多，索引本身也需要占用空间（索引的建立根据具体的需求情况合理指定即可，我认为）
9、索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。

什么情况下不宜建立索引？

1、更新频繁的字段不要使用索引，字段更新需要重新维护索引的生成
2、表中数据量比较小的时候不要建立索引（百量级的就不需要建立索引全表扫描性价比更高，十万量级的就需要考虑建立索引了）
3、不作为搜索条件的字段可以考虑不使用索引

B树和B+树的区别

首先要明白索引是在存储引擎层面实现的，而不是server层面。不是所有的存储引擎都支持索引类型。即使多个存储引擎支持某一索引类型，它们的实现和行为也可能有所差别。
B树：

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键字划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。
B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。

非叶子节点只存储键值信息，数据记录都存放在叶子节点中

使用B+树的好处

由于B+树的内部节点只存放键，不存放值，因此，一次读取，可以在内存页中获取更多的键，有利于更快地缩小查找范围。

百万级别或以上的数据如何删除

关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。
1、所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）
2、然后删除其中无用数据（此过程需要不到两分钟）
3、删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。
4、与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。

前缀索引

前缀索引就是基于原始索引字段，截取前面指定的字符个数或者字节数来做的索引。
语法：index(field(10))，使用字段值的前10个字符建立索引，默认是使用字段的全部内容建立索引。
前提：前缀的标识度高。比如密码就适合建立前缀索引，因为密码几乎各不相同。
实操的难度：在于前缀截取的长度。
我们可以利用select count(*)/count(distinct left(password,prefixLen));，通过从调整prefixLen的值（从1自增）查看不同前缀长度的一个平均匹配度，接近1时就可以了（表示一个密码的前prefixLen个字符几乎能确定唯一一条记录）

创建索引的三种方式

第一种方式：在执行CREATE TABLE时创建索引

CREATE TABLE user_index2 (
	id INT auto_increment PRIMARY KEY,
	first_name VARCHAR (16),
	last_name VARCHAR (16),
	id_card VARCHAR (18),
	information text,
	KEY name (first_name, last_name),
	FULLTEXT KEY (information),
	UNIQUE KEY (id_card)
);

第二种方式：使用ALTER TABLE命令去增加索引

ALTER TABLE table_name ADD INDEX index_name (column_list);

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。
其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。
索引名index_name可自己命名，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。
第三种方式：使用CREATE INDEX命令创建

CREATE INDEX index_name ON table_name (column_list);

CREATE INDEX可对表增加普通索引或UNIQUE索引。（但是，不能创建PRIMARY KEY索引）

Innodb的索引模型

在InnoDb中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。InnoDb使用了B+树索引模型，所以数据都是存储在B+树中的。
每一个索引在InnoDb里面对应一棵B+树。
假设，我们有一个主键列为ID的表，表中有字段k,并且在k上有索引。
这个表的建表语句是：

mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

表中R1~R5的（ID,k）值分别为(100,1)、（200、2）、（300，3）、（500，5）和（600，6），两棵树的示例示意图如下。

从图中不难看出，根据叶子节点的内容，索引类型分为主键索引和非主键索引。

主键索引（聚簇索引）与非主键索引（非聚簇索引）的区别

主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也称为聚簇索引。
非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引。

回表

先查询非主键索引获取到结果，在根据结果到主键索引中获取数据，此过程称为回表。

覆盖索引

覆盖索引即从非主键索引中就能查到的记录，而不需要查询主键索引中的记录，避免了回表的产生减少了树的搜索次数，显著提升性能。
或者：
在用到非主键索引（二级索引）时，要根据主键值进行回表操作查询数据，若此时，要查询的数据在二级索引中就存在，则不进行回表操作，称为覆盖索引。

索引下推

索引下推全称Index Condition PushDown,简称ICP，是Mysql5.6版本后引入的新特性，是针对查询条件是二级索引的场景做的查询优化。具体点，在根据查询条件对二级索引进行查询时，不忙着将查询得到的二级索引记录进行回表查询，而是根据二级索引列的其他查询条件（查询条件是二级索引列但没有用上二级索引的）进一步过滤，仅将符合后者查询条件的二级索引记录进行回表，这样做减少了回表的次数，减少了随机IO，提升了效率。使用到了索引下推的查询语句的执行计划中，Extra列是Using index condition。