索引是帮助MySQL搞笑获取数据的排好序的数据结构。

索引的数据结构

• B树
• B+树

• Hash表

  B树与B+树的区别

• B树的所有节点既存放 键(key) 也存放 数据(data);而B+树只有叶子节点存放 key 和 data，其他内节点只存放key。B+树IO读取次数更少。

• B树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
• B树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

  Hash索引和 B+树索引优劣分析

1. Hash索引快
   1. Hash索引指的就是Hash表，最大的优点就是能够在很短的时间内，根据Hash函数定位到数据所在的位置，这是B+树所不能比的。
2. Hash索引存在Hash冲突
3. Hash索引不支持顺序和范围查询(Hash索引不支持顺序和范围查询是它最大的缺点。

   索引类型

   | 主键索引 | 二级索引（辅助索引） | | :—-: | :—-: | | 索引的key是主键 | 索引的key其他字段，value是主键 |

一张表只能有一个主键索引，可以有多个二级索引。

二级索引

唯一索引，普通索引，前缀索引等索引属于二级索引。

1. 唯一索引(Unique Key) ：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为NULL，一张表允许创建多个唯一索引。建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
2. 普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和NULL。
3. 前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。

4. 全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6之前只有MYISAM引擎支持全文索引，5.6之后InnoDB也支持了全文索引。

   聚集索引

   即索引结构和数据一起存放的索引，主键索引属于聚集索引。

   优缺点

   优点：查询速度非常快，定位到节点就定位到了数据。
   缺点：

5. 依赖于有序的数据：因为B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或UUID这种又长又难比较的数据，插入或查找的速度肯定比较慢。

6. 更新代价大： 如果对索引列的数据被修改时，那么对应的索引也将会被修改， 而且况聚集索引的叶子节点还存放着数据，修改代价肯定是较大的， 所以对于主键索引来说，主键一般都是不可被修改的。

   非聚集索引

   即索引结构和数据分开存放的索引。
   二级索引属于非聚集索引。
   非聚集索引的叶子节点并不一定存放数据的指针， 因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。

   优缺点

   优点：更新代价比聚集索引小。
   缺点：

7. 依赖于有序数据；

8. 可能会回表查询。

   非聚集索引不回表查询的情况

   用户准备使用SQL查询用户名，而用户名字段正好建立了索引。

SELECT name FROM table WHERE username='guang19';

那么这个索引的key本身就是name，查到对应的name直接返回就行了，无需回表查询。

覆盖索引

上面提到的非聚集索引不回表查询的情况就是覆盖索引，覆盖索引就是需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了， 而无需回表查询。

最左前缀原则

假设创建的联合索引由三个字段组成：

ALTER TABLE tableName ADD INDEX index_name (num,name,age)

那么当查询的条件有为:num / (num AND name) / (num AND name AND age)时，索引才生效。所以在创建联合索引时，尽量把查询最频繁的那个字段作为最左(第一个)字段。查询的时候也尽量以这个字段为第一条件。

索引创建注意点

1. 在经常需要搜索的列上，可以加快搜索的速度；
   
2. 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
   
3. 在经常需要排序的列上创 建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
   
4. 对于中到大型表索引都是非常有效的，但是特大型表的话维护开销会很大，不适合建索引
   
5. 在经常用在连接的列上，这 些列主要是一些外键，可以加快连接的速度；
   
6. 避免 where 子句中对宇段施加函数，这会造成无法命中索引。
   
7. 在使用InnoDB时使用与业务无关的自增主键作为主键，即使用逻辑主键，而不要使用业务主键。
8. 删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗 MySQL 5.7 可以通过查询 sys 库的 chema_unused_indexes 视图来查询哪些索引从未被使用
9. 在使用 limit offset 查询缓慢时，可以借助索引来提高性能

   创建索引

   CREATE TABLE

   CREATE TABLE TABLE_NAME (.... [INDEX | UNIQUE | PRIMARY KEY]([COLUMN]));

   CREATE TABLE tb_stu_info
   (
   id INT NOT NULL,
   name CHAR(45) DEFAULT NULL,
   dept_id INT DEFAULT NULL,
   age INT DEFAULT NULL,
   height INT DEFAULT NULL,
   INDEX(height)
   );

   ALTER TABLE

   ALTER TABLE TABLE_NAME ADD [INDEX | UNIQUE | PRIMARY KEY] [INDEX_NAME] (COLUMN);

   推荐阅读

• 干货：mysql索引的数据结构
• MySQL 添加索引，删除索引及其用法
• mysql如何添加索引