数据库索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

索引分单列索引和组合索引。

一、什么时候需要索引

1、索引特点

（1）索引储存了指向表中某一行的指针。
（2）用户不用在查询语句中指定使用哪个索引
（3）在定义primary key或unique约束后系统自动在相应的列上创建索引
（4）用户也能按自己的需求，对指定单个字段或多个字段，添加索引
（5）与全表扫描相比更有效率，但也有相反的情况。
（6）使用索引的全部意义就是通过缩小一张表中需要查询的记录/行的数目来加快搜索的速度。
（7）索引是一种数据结构 。常用B树结构储存数据，因为数据在B树结构中是有序的，并且增删改查都可以在对数时间内完成，时间复杂度低。其他数据结构的索引比如有哈希表索引、R-Tree索引、位图索引等。
（8）只要某查询条件中包含复合索引中的第一个列，该查询就会走索引，如果不包含，就不会走索引。（可查看另一个博主的实验 https://blog.csdn.net/tw7752/article/details/44595281）

2、什么时候【要】创建索引

（1）表经常进行 SELECT 操作
（2）表很大(记录超多)，记录内容分布范围很广
（3）列名经常在 WHERE 子句或连接条件中出现

3、什么时候【不要】创建索引

（1）表经常进行 INSERT/UPDATE/DELETE 操作
（2）表很小(记录超少)
（3）列名不经常作为连接条件或出现在 WHERE 子句中

二、单列索引

单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

比如主键索引。主键索引是一个特殊的聚类索引

PRIMARY KEY  -- 建表时自接指定
create table student(
	sno char(9),
	sname char(20) not null,
	sex char(2),
	primary key (sno)
	);

在数据库关系图中创建表时若定义了主键，系统将自动创建主键索引，主键索引是唯一索引的特定类型。一般指定一张表拥有物理属性不同值的列为主键。

手动在student表上创建非主键索引。

CREATE INDEX name_index
ON student(sname)

聚类索引
聚类索引是区别于非聚类索引的索引，其中聚类索引中，表中的行物理排列顺序与索引排列顺序相同，如字典中的字母排序，正文如目录，
对某一列创建聚类索引时应该遵循以下几个原则：
（1）该列的键值应该有某种可排序的可知意义，例如日期、地点，大数目的不同值情况下不应建立聚合索引，避免使用自增的id列作为默认的聚类索引。
（2）当有需要频繁更新的列时（不一定是索引列），不应该使用聚类索引。
（3）既不能绝大多数都相同，又不能只有极少数相同。该列可被经常分组排序，比如班级、成绩。

表在创建主键的时候必需依赖于索引，默认是将主键列作为表的聚类索引，也就是说：

create table student(...
Sname char(40) primary key;
)

等同于

create clustered index Sname_index on student (Sname);

于是在其他列建立聚类索引时系统会拒绝。
那么，在创建主键的时候是否也可以声明主键列为非聚类索引呢？
对Sname列创建非聚类索引

create table student(...
Sname char(40),
constraint Sname_index primary key nonclustered (Sname) ;
)

这时候表里面没有聚类索引，于是我们
对student表的class列建立聚类索引：

create clustered index class_index on student (class);

非聚类索引
聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致。
聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续。
因为只能有一个聚类索引，所以在为指定为聚类索引时，将创建非聚类索引。
可以有多个。

三、组合索引（复合索引）

复合索引是由多个列建立的索引。
给student表的Sname列和class列创建复合索引

create index name_class_index on student (Sname,class);

或者

ALTER TABLE `student` ADD INDEX( `username`, `name`); 

只要某查询条件中包含复合索引中的第一个列，该查询就会使用索引，如果不包含，就不会使用索引。

select * from student where Sname='庞先生' and class='计算机一班';

前导列一定要是使用最频繁的列

如果查询条件中的条件顺序没有按照索引的顺序，或者缺少一项，例如

select * from student where class='计算机一班' and Sname='庞先生' class='计算机一班';

将不会使用索引。

对一张表来说,如果有一个复合索引student(Sname,class)就没有必要同时建立一个单索引 student(Sname)。

四、覆盖索引

什么是覆盖索引，网络上有下面三种理解：

解释一： 就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。
解释二： 索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫 做覆盖索引。
解释三：是非聚集组合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建立索引的字段正好是覆盖查询语句[select子句]与查询条件[Where子句]中所涉及的字段，也即，索引包含了查询正在查找的所有数据）。

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引

使用 explain [查询语句] 来查看执行计划

• type ：值为 all ，表示进行了全表扫描，这里为 index 表示使用了索引
• possible_key：值为 NULL，说明没有WHERE条件时查询优化器无法通过索引检索数据
• key：查询时用到的列或者是索引名，这里的值为 primary 表示使用主键索引
• Extra：值为 Using index ，表示当前是一个被索引覆盖的查询(也叫作索引覆盖查询)

总结：覆盖索引的优化及限制
覆盖索引是一种非常强大的工具，能大大提高查询性能，只需要读取索引而不需要读取数据，有以下优点：

1、索引项通常比记录要小，所以MySQL访问更少的数据。

2、索引都按值得大小存储，相对于随机访问记录，需要更少的I/O。

3、数据引擎能更好的缓存索引，比如MyISAM只缓存索引。

4、覆盖索引对InnoDB尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引包含查询所需的数据，就不再需要在聚集索引中查找了。

限制：

1、覆盖索引也并不适用于任意的索引类型，索引必须存储列的值。

2、Hash和full-text索引不存储值，因此MySQL只能使用BTree。

3、不同的存储引擎实现覆盖索引都是不同的，并不是所有的存储引擎都支持覆盖索引。

4、如果要使用覆盖索引，一定要注意SELECT列表值取出需要的列，不可以SELECT * ，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

五、索引的数据结构

1、B+树索引

聚集索引的B+树索引（id是PK，叶子节点存储行记录）：

普通索引的B+树索引（name是KEY，叶子节点存储PK值，即id）：

2、B树索引

3、哈希索引

六、索引失效的情况

组合索引使用 or 索引失效 如
a=1 or b=2 or c=3

索引条件为 is null / is not null 索引失效(看清况，测试过 null 比较多时，
not null 会走索引)

索引条件 like ‘%xxx’, 索引失效； like ‘xxxx%’ 索引生效

索引列参加计算 如 t.score/10 > 10 失效， 应改成 t.score > 10*10

索引列不要使用NOT （ != 、 <> ）如 t.score! = 10 失效，改成：t.score > 10 or t.score < 10

索引列上发生类型转换, 例如
VARCHAR2 类型的索引列 ，写成 where id = 1 ,应该 改成 where id = ‘1’ ( oracle实验)