Mysql索引

一.索引相关概念

• Mysql索引是什么：

1）定义：索引（Index）是帮助MySQL高效获取数据的数据结构
2）本质：索引是数据结构(排好序的快速查找数据结构)
3）位置：索引本身很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上

• 优点：

1）提高检索效率，降低I/O成本
2）通过索引对数据进行排序，降低数据排序成本，降低CPU消耗

• 缺点：

1）虽然提高了查询效率，但降低了更新效率(每次INSERT、UPDATE、DEIETE，Mysql不仅要保存数据，还要更新索引文件)
2）索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录，所以索引也要占用空间

• 覆盖索引：

1）select查询的数据列在索引中就能获取，不必从数据表中获取，即查询列被使用的索引覆盖
2）索引是一个高效找到行的方法，通过检索索引就可以读取想要的数据，不需要再去数据表中读取行数据了。如果一个索引包含了(覆盖了)满足查询语句中的字段与条件的数据就叫覆盖索引
3）非聚集组合索引的一种形式，它包含在查询里的Select、Join和Where字句里用到的所有的列，即索引字段正好覆盖查询语句select(查询字段)与查询条件(wher子句)中所涉及的所有字段
4）不是所有类型的索引都可以成为覆盖索引，覆盖索引必须存储索引的列，而哈希索引、空间索引和全文索引都不存储索引列的值，所以Mysql只能使用B-Tree索引覆盖索引
5）不当发起一个索引覆盖查询，在EXPLAIN的Extra列可以看到"Using index"的信息

• 聚簇索引：

索引分为聚簇索引和非聚簇索引（又叫二级索引）
以一本英文课本为例，要找第8课，直接翻书，若先翻到第5课，则往后翻，再翻到第10课，则又往前翻。这本书本身就是一个索引，即“聚簇索引”。
如果要找"fire”这个单词，会翻到书后面的附录，这个附录是按字母排序的，找到F字母那一块，再找到"fire”，对应的会是它在第几课。这个附录，为“非聚簇索引”。
由此可见，聚簇索引，索引的顺序就是数据存放的顺序，所以，很容易理解，一张数据表只能有一个聚簇索引
聚簇索引优点：
1）聚簇索引将索引和数据行保存在同一个Tree中，查询通过聚簇索引可以直接获取数据，相比非聚簇索引需要第二次查询（非覆盖索引的情况下）效率要高
2）聚簇索引对于范围查询的效率很高，因为其数据是按照大小排列的
聚簇索引缺点：
1）聚簇索引在插入新行和更新主键时，可能导致“页分裂”问题，代价较高

• MyISAM和InnoDB存储引擎的区别：

1）InnoDB支持事务，MyISAM不支持事务
2）InnoDB支持表、行级锁，MyISAM只支持表级锁
3）InnoDB和MyIsam叶子节点存储的数据不一样

• MyISAM存储引擎B+tree的叶子节点中data不是数据本身，而是数据存放的地址，主索引和辅助索引没什么区别，只是主索引是唯一的，这里的主索引和辅助索引都是非聚簇索引。myisam引擎的索引文件和数据文件是独立分开的
• InnoDB存储引擎B+tree中叶子节点中data存储的是数据本身，key为主键，这是聚簇索引。在innodb中，即存储主键索引值，又存储行数据

---

二.索引相关数据结构对比

• 搜素二叉树：
  每个节点有两个子节点，数据量的增大必然导致高度的快速增加，进而导致I/O次数的增加，不适合作为大量数据存储的基础结构
  
• BTree：又称平衡多路查找树
• 一棵m阶的B 树 (m叉树)的特性如下：

1）树中的每个节点最多包含m个子节点(m>=2)
2）除根结点和叶子节点外，每个节点至少包含(m/2)个子节点
3）若根结点不是叶子几点，则至少有2个子节点
4）所有叶子节点都出现在同一层，叶子节点不包含任何关键字信息(可以看作外部节点或查询失败节点，实际上这些节点不存在，指向这些节点的指针为null)
5）所有非终端节点包含n个关键字的信息， (P1，K1，P2，K2，P3，…，Kn，Pn+1)，
其中：
a） Ki (i=1…n)为关键字，且关键字按顺序升序排序K(i-1)< Ki
b） Pi为指向子树根的接点，且指针P(i)指向子树种所有结点的关键字均小于Ki，但都大于K(i-1)
c） 关键字的个数n必须满足： (m / 2)-1<= n <= m-1

• BTree图解析：

1）每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址
2）两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35

• 查找过程：查找数据项29

1）第一次IO，先把磁盘块1由磁盘加载到内存，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计
2）第二次IO，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，29在26和30之间，锁定磁盘块3的P2指针，
3）第三次IO，通过指针加载磁盘块8到内存，同时内存中做二分查找找到29，结束查询

真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高

• BTree缺点：

1）每个节点中有key、指针和data，而每一个页的存储空间是有限的，如果data数据较大时就会导致每个节点（即一个页）能存储的key的数量很小
2）当存储的数据量很大时，同样会导致B-Tree的深度较大，增加查询时的磁盘I/O次数，进而影响查询效率

• B+Tree：B+Tree是在BTree基础上的一种优化，innoDB存储引擎就是用B+Tree实现其索引结构的
• B+Tree和BTree的区别：

1）非叶子节点只存储键值(key)和指针
2）所有叶子节点间都有链指针
3）数据记录都放在叶子节点中

1）通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找

2）InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值[10^3 ]
也就是说一个3阶的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿 条记录

3）实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2-4层， mysql的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作

• 思考：为什么说B+树比B树更适合实际应用中操作系统的文件索引和数据库索引？

1）B+Tree的磁盘读写代价更低
B+Tree的内部结点并没有指向关键字具体数据的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了

2） B+Tree的查询效率更加稳定
由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

---

三.Mysql索引分类

• 基本语法：

#创建
CREATE  [UNIQUE ]  INDEX [indexName] ON table_name(column)) 

#删除
DROP INDEX [indexName] ON table_name

#查看
SHOW INDEX FROM table_name

#ALERT命令有四种方式来添加数据表的索引：

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 ，用于全文索引。 

• 单值索引：

#单值索引，即一个索引只包含单个列，一个表可以有多个单列索引
随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
 PRIMARY KEY(id),
 KEY (customer_name)
);
单独建单值索引：
CREATE  INDEX idx_customer_name ON customer(customer_name); 
删除索引：
DROP INDEX idx_customer_name  on customer;

• 唯一索引：

#唯一索引，索引列的值必须唯一，但允许有空值
随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
 PRIMARY KEY(id),
 KEY (customer_name),
 UNIQUE (customer_no)
);
单独建唯一索引：
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no); 
删除索引：
DROP INDEX idx_customer_no on customer ;

• 主键索引：

#设定为主键后数据库会自动建立索引，innodb为聚簇索引
随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
 PRIMARY KEY(id) 
); 
CREATE TABLE customer2 (id INT(10) UNSIGNED   ,customer_no VARCHAR(200),customer_name VARCHAR(200),
 PRIMARY KEY(id) 
); 
单独建主键索引：
ALTER TABLE customer 
add PRIMARY KEY customer(customer_no);  
删除建主键索引：
ALTER TABLE customer 
drop PRIMARY KEY ;  
修改建主键索引：
必须先删除掉(drop)原索引，再新建(add)索引

• 复合索引：

#一个索引包含多个列
随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
 PRIMARY KEY(id),
 KEY (customer_name),
 UNIQUE (customer_name),
 KEY (customer_no,customer_name)
);
单独建索引：
CREATE  INDEX idx_no_name ON customer(customer_no,customer_name);  
删除索引：
DROP INDEX idx_no_name  on customer ;

---

四.哪些情况下需要创建索引

• 主键自动建立唯一索引
• 频繁作为查询条件的字段
• 查询中排序的字段
• 查询中统计或分组的字段
• 单键/组合索引的选择问题，组合索引性价比更高
• 查询中与其它表关联的字段，外键关系字段建立索引

---

五.哪些情况下不需要创建索引

• 数据量不大的表
• 经常增删改的表