【MySQL中的索引】

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

索引在日常开发中使用的频率是非常高的，本文介绍下为什么要使用索引，MySQL中各种索引的区别以及应该使用索引的场景

---

一、索引介绍

1.什么是索引？
可以理解为一种排好序的可以高效获取数据的数据结构，索引有单独的文件存储在磁盘中
2.索引有哪些优点？
可以提高检索速度，降低数据库的IO成本，通过索引可以提高排序的效率
3.索引有哪些缺点？
在对表进行增删改操作的时候，也要对相应的索引文件进行操作，降低操作的速度，并且索引也会占用一定的磁盘空间

二、MySQL中的索引结构

MySQL默认的存储引擎InnoDB就是用的B+tree实现索引结构
B+Tree是一种多路平衡查找树(一个节点可以有多个子节点，任意节点的所有子树高度不大于1)
在B+数中，所有的数据都存储在叶子节点上，非叶子节点只能存储KEY
B+Tree特点：
1.每个节点可以有多个子节点----矮从而减少IO次数–随机查找快
2.非叶子节点只存储键值信息。
3.页与页之间是双向链表相连，每页中行数据是以单向链表相连—利于范围查询
4.数据记录都存放在叶子节点中。
聚簇索引和非聚簇索引：聚簇索引存储的是主键值以及行数据，非聚簇索引存储的是索引字段以及主键值不存储行数据
主键索引
概念：设定为主键后数据库会自动建立索引，innodb为聚簇索引
随表一起建索引

 CREATE    TABLE    customer    (
id  INT(10)   UNSIGNED	 AUTO_INCREMENT ,
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
VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name)
);

单独建单值索引：

CREATE	INDEX  idx_customer_name  ON customer(customer_name);
alter table customer add index mycu(customer_no);

唯一索引
概念：索引列的值必须唯一，但允许有空值
随表一起创建：

CREATE TABLE customer	(id	INT(10)	UNSIGNED	AUTO_INCREMENT	,customer_no	VARCHAR(200),customer_name
VARCHAR(200),						
PRIMARY KEY(id),				
UNIQUE (customer_no)						
);						

单独建唯一索引：

CREATE UNIQUE index idx_customer_no  ON  customer (customer_no);
Alter Table customer add unique myunique(customer_no);

联合索引
概念：即一个索引包含多个列
随表一起建索引：

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);

联合索引的最左匹配原则：
首先根据联合索引中最左边的、也就是第一个字段进行排序，在第一个字段排序的基础上，再对联合索引中后面的第二个字段进行排序，依此类推

示例：在t 表创建了联合索引（a,b,c），相当于创建了(a),(a,b),(a,b,c) 三个索引。
基本语法

创建	CREATE	[UNIQUE ]	INDEX [indexName] ON  TABLE_NAME (column))
删除	DROP  INDEX  [indexName]  ON  mytable;
查看	SHOW  INDEX  FROM  TABLE_NAME \G

也可以使用 Alter来添加索引

ALTER TABLE  TABLE_NAME ADD PRIMARY KEY (column_list) : 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为 NULL。
ALTER  TABLE  TABLE_NAME  ADD INDEX  index_name (column_list): 添加普通索引，索引值可出现多次。
ALTER  TABLE  TABLE_NAME  ADD UINQUE  INDEX  index_name (column_list): 添加唯一索引。
ALTER  TABLE  TABLE_NAME  DROP  PRIMARY  KEY; 删除主键，之前先删除自增属性，AlTER TABLE TABLE_NAME  MODIFY COLUMN id INT;

三、索引创建时机

适合创建索引的情况
1.主键自动建立唯一索引；
2.频繁作为查询条件的字段应该创建索引
3.查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

不适合创建索引的情况
1.表记录太少
2.经常增删改的表或者字段
3.Where 条件里用不到的字段不创建索引
4.过滤性不好的字段不适合建索引

explain关键字
explain 是用来分析 SELECT 查询语句的，可以观察索引是否被使用到。
EXPLAIN SELECT * FROM student

重点关注字段：
type：访问类型，要是显示 ALL ，可要小心了，意思是全表扫描，性能最差，查询有很大的优化空间，如果显示的是 index ，说明会使用索引来优化查询。

key：具体使用的索引名，这里没有。
rows：扫描的行数。

索引失效的场景
1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
2.对于联合索引，不是使用的第一部分，则不会使用索引（靠左原则）
3.like查询以%开头的列索引会失效
4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
5.不等于(！= ，<> )，EXISTS，not in，is not null等会导致索引失效
6.如果MySQL估计使用全表扫描要比使用索引快,则不使用索引