s索引的学习对于数据库优化有着至关重要的作用，深入理解索引机制，才能更好的优化查询机制

一、索引分类

索引的使用会影响where条件查询以及order by排序，MySQL索引类型如下

从存储结构划分：可分为B Tree索引、Hash索引、FuLLTEXT全文索引、R Tree索引
从应用层次划分：可分为主键索引、普通索引、唯一索引、复合索引、全文索引
从索引键类型划分：可分为主键索引、辅助索引
从数据存储和键类型划分：可分为聚簇索引（聚集索引）、非聚簇索引

1、普通索引

普通索引是最基本的索引类型，建立在普通字段上，没有任何限制

创建普通索引的三种方式

直接添加索引

1	CREATE INDEX <索引的名字> ON tablename (字段名);

修改表结构添加索引

1	ALTER TABLE tablename ADD INDEX [索引的名字] (字段名);

创建表时添加索引

1	CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名) );

2、主键索引

主键索引是一种特殊的唯一值，不允许为空。创建主键索引有两种方式

修改表结构添加索引

1	ALTER TABLE tablename ADD PRIMARY KEY (字段名);

创建表时添加索引

1	CREATE TABLE tablename ( [...], PRIMARY KEY (字段名) );

3、唯一索引

与普通索引不同的时，唯一索引的列不能有重复值，但可以为空，在创建、修改表列字段属性为唯一约束时，自动添加唯一索引

创建唯一索引的三种方式

直接添加唯一索引

1	CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);

修改表结构添加索引

1	ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);

创建表时添加索引

1	CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (字段名) ;

4、复合索引

单一索引是指索引只作用在一列上，复合索引是指用户在多个列上建立索引，这种索引叫复合索引（组合索引），复合索引可以代替多个单一索引，相比多个单一索引复合索引所需的开销更小。但要注意最左前缀原则

创建复合索引的三种方式

直接添加复合索引

1	create index <索引名称> on table(字段名1，字段名2);

修改表结构添加索引

1	ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1，字段名2...);

创建表时添加索引

1	CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名1，字段名2...) );

复合索引使用时注意事项：

什么时候使用复合索引：

添加复合索引要根据where条件建立，不要过多使用索引，过多无用的索引会影响表新增更新数据
如果表已经有了(col1,col2)，就没有必要再单独建立(col1)，如果有了（col1）索引，如果需要查询col1和col2条件，可以建立(col1,col2)列复合索引，对查询速度有一定的提高。

5、全文索引

查询操作在数据量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很低。如果使用全文索引，查询速度会比like快很多倍。在MySQL 5.6 以前的版本，只有MyISAM存储引擎支持全文索引，从MySQL 5.6开始MyISAM和InnoDB存储引擎均支持。

创建全文索引的三种方式（注意只能对字符串和文本类型列生效）：

直接添加全文索引

1	CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名);

修改表结构添加全文索引

1	ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);

创建表时添加全文索引

1	CREATE TABLE tablename ( [...], FULLTEXT KEY [索引的名字] (字段名) ;

与常见的like模糊查询不同，全文索引使用match和against关键字，比如

1	select * from user where match(username) against('aaa');

全文索引使用注意事项：

全文索引必须在字符串、文本字段上建立。
- 全文索引字段值必须在最小字符和最大字符之间的才会有效。（innodb：3-84；myisam：4-84）
全文索引字段值要进行切词处理，按syntax字符进行切割，例如b+aaa，切分成b和aaa
全文索引匹配查询，默认使用的是等值匹配，例如a匹配a，不会匹配ab,ac。如果想匹配可以在布尔模式下搜索a*

二、索引的原理

三、索引的分析和优化

1、Explain

MySQL提供的explain命令，可以对select语句进行分析，并输出select 查询执行的详细信息，可以供开发人员查看，进而帮助开发人员针对性优化。例如

1	EXPLAIN SELECT * from user WHERE id < 3;

EXPLAIN 命令的输出内容大致如下：

（一）select_type

表示查询的类型，常见的值有以下情况

SIMPLE：表示查询语句不包含子查询或union
PRIMARY：表示此查询是最外层查询
UNION：表示此查询是UNION的第二个或后续的查询
DEPENDENT UNION：UNION中的第二个或后续的查询语句，使用了外面查询结果
UNION RESULT：UNION的结果
SUBQUERY：SELECT子查询语句
SUBQUERY：SELECT子查询语句

最常见的查询类型就是SIMPLE，表示我们查询没有子查询也没有用到union查询

（二）type

表示存储引擎查询数据时采用的方式。比较重要的一个属性，通过它可以判断出查询是全表扫描还是基于索引的部分扫描。常用属性值如下，从上至下效率依次增强。

ALL：表示全表扫描，性能最差。
index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。
range：表示使用索引范围查询。使用>、>=、<、<=、in等等。
ref：表示使用非唯一索引进行单值查询。
eq_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一行结果。
const：表示使用主键或唯一索引做等值查询，常量查询。
NULL：表示不用访问表，速度最快

（三）possible_keys

表示查询时能够使用到的索引。注意并不一定会真正使用，显示的是索引名称。

（四）key

表示查询时真正使用到的索引，显示的是索引名称。

（五）rows

MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录。原则上rows是越少效率越高，可以直观的了解到SQL效率高低。

（六）key_len

表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引。
key_len的计算规则如下：

字符串类型
字符串长度跟字符集有关：latin1=1、gbk=2、utf8=3、utf8mb4=4
char(n)：n*字符集长度
varchar(n)：n * 字符集长度 + 2字节
数值类型
TINYINT：1个字节
SMALLINT：2个字节
MEDIUMINT：3个字节
INT、FLOAT：4个字节
BIGINT、DOUBLE：8个字节
时间类型
DATE：3个字节
TIMESTAMP：4个字节
DATETIME：8个字节
字段属性
NULL属性占用1个字节，如果一个字段设置了NOT NULL，则没有此项。

（七）Extra

Extra表示很多额外的信息，各种操作会在Extra提示相关信息，常见几种如下：

Using where
表示查询需要通过索引回表查询数据。
Using index
表示查询需要通过索引，索引就可以满足所需数据。
Using filesort
表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有Using filesort建议优化。
Using temprorary
查询使用到了临时表，一般出现于去重、分组等操作。

2、索引失效的七种情况

（一）like ‘%XX’

（二）where 索引列使用了函数

（三）违反最左前缀原则

（四）有or关键字

（五）where 索引列有运算

（六）需要类型转换

（七）如果MySQL觉得全表扫描更快（例如范围查询，结果数据较多）

索引失效的7种情况

3、回表查询

在之前介绍过，InnoDB索引有聚簇索引和辅助索引。聚簇索引的叶子节点存储行记录，InnoDB必须要有，且只有一个。辅助索引的叶子节点存储的是主键值和索引字段值，通过辅助索引无法直接定位行记录，通常情况下，需要扫码两遍索引树。先通过辅助索引定位主键值，然后再通过聚簇索引定位行记录，这就叫做回表查询，它的性能比扫一遍索引树低。
总结：通过索引查询主键值，然后再去聚簇索引查询记录信息

4、索引覆盖

简单来说，通过索引就能满足查询结果。不需要再去查询行记录。

在MySQL官网，类似的说法出现在explain查询计划优化章节，即explain的输出结果Extra字段为Using index时，能够触发索引覆盖

不管是SQL-Server官网，还是MySQL官网，都表达了：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快，这就叫做索引覆盖。
实现索引覆盖最常见的方法就是：将被查询的字段，建立到组合索引。

5、索引与排序

MySQL支持filesort和index两种方式排序

（一）filesort

先把结果查出，然后在缓存或磁盘进行排序操作，效率较低。

双路排序

需要两次磁盘扫描读取，最终得到用户数据。第一次将排序字段读取出来，然后排序；第二次去读取其他字段数据

单路排序

（二）index

使用index是指利用索引自动实现排序，不需另做排序操作，效率会比较高。

六、NULL查询

问题：如果MySQL的某一列含有NULL值，那么包含该列的索引是否生效？

对MySQL来说，NULL是一个特殊的值，从概念上来讲，NULL意味着“一个未知值“，它的处理方式与其他值有些不同，比如：不能使用=、<、>这样的运算符，对NULL做算数运算的结果都是NULL,count时不会包括NULL行等，NULL比空字符串需要更多的存储空间等。

答：可以，虽然MySQL可以再含有NULL的列上使用索引，但是NULL和其它数据还是有区别的，不建议列上允许未NULL值，最好设置为NOT NULL，并给一个默认值，比如0和空字符串 ‘’ 等，如果时datatime类型，也可以设置系统当前时间或某个固定的特殊值，例如’1970-01-01 00:00:00’ 。

注意：设置默认值的时候，例如0，不要和系统数据产生干扰，如果这个字段0本身就有含义，就不能使用了。

程序小牛

MySQL索引学习