MySQL(一)-数据库索引

一、什么是索引

索引可以快速查询特定值的行，没有索引MySQL必须从低一行开始，从上往下查询，表越大，消耗就越大。
如果有索引，MySQL可以快速确定要查询数据所在的位置，而不必看所有的数据，减少资源的消耗。

如上图，如果没有索引，要查询22，我们需要依次查询5次才能差的我们所要的数据；

如上图，如果有索引，我们只需要查询一次就可以定位22所在的位置，创建索引为值为key，内存地址为value，此处用的二叉树为示例。

二、索引的数据结构

  常见的数据结构有二叉树、红黑树、hash表、BTree、B+Tree，Mysq用的是B+Tree作为索引的数据结构，为什么不用其他的数据接口，我们依依分析。

1、为什么不用二叉树？

二叉树特定：左边子节点始终比父节点小，右边子节点始终比父节点大。

缺点：如果把id作为索引，id又是递增的，那么节点就是递增，这样就是一条递增的链表了，与无索引无区别。

 2、为什么不是红黑树？

红黑树特定：平稳，通过自身的算法，解决了二叉树单项递增的问题。

缺点：每一层存放的数据是有限的，每一层只能存放2的n次方的数据，如果数据量巨大，查询的速度不够快。
例子：如果红黑树有50层，要查询的id正好在最底层，那么查询次数至少要查询50次。

3、为什么不用链表?

特定：通过hash算法可以直接定位要查询数据的位置，查询速度快。

缺点：只能查询单个条件的数据，当我们查询id<50就不行了。
MySQL还是保留了hash索引的选择，当我们不需要进行范围查询的时候，就可以选择hash。

4、为什么不是BTree？

特定：1.结构是一个多叉平衡树，单个节点可以存放多个数据，每个节点可以有很多个分支即为多叉；
2.每个节点存放的数据从中往右为递增；
3.通过对度的控制(每个节点存放的数据)，可以控制每个树的搞得和优先查询速度。
这样我们可以看到，BTree比红黑树要小很多。

缺点：
我们可以控制度(每个节点存放的数据)来控制树的大写，但是无法无限制的控制度的大小，因为mysql在读取数据是从磁盘中读取数据到cpu，在由cpu交给数据库。
如果cpu每次读取的大写是10k，节点的大写是100k，那么就需要10次I/O来获取数据，cpu消耗太大。
如果说节点的数据设置成10k，限制节点的大小，那么节点存放的数据又是有限的。

5、为什么是B+Tree？

特点：
1.如果节点不是业主节点，只存放索引值，不存放数据，这样大大节省了节点的存储空间。
2.数据记录由叶子节点保存
3.叶子节点建立顺序指针，不需要访问上一块数据，提高区间性能访问。

1.预读：磁盘会顺序预读一部分长度的数据【页的整数背】，放入内存zhong。
2.局部性原理，一个地方的数据被用到是，附近的数据马上通知被使用。
3.节点的大写正好等于一个页，这样只需要进行一次io。
4、B+Tree的度(每个节点索引的大写)一般都可以过百，树的高度小。

三、MyISAM存储引擎的索引和InnoDB存储引擎的索引

数据库存储引擎【又称为表类型,作用于表】是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。

不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以 获得特定的功能

也就是说，存储引擎限定了数据库表的存储机制和索引等信息

MyISAM和InnoDB这两个存储引擎都使用B+树作为索引的结构，但是这两种存储引擎对索引的具体实现方式方面是不同的。下面来具体介绍一下这两种存储引擎的索引具体是如何实现的。

1、MyISAM存储引擎

MyISAM引擎（表类型）采用的是非聚集型索引，即索引文件与数据文件分离，叶子节点保存索引及目标数据记录的文件地址。

且对于MyISAM存储引擎而言，主键索引及非主键索引完全一致。

 所以MyISAM存储引擎中索引的实现特征是数据和索引分离，这被称为“非聚簇索引”。

我们创建一根表，如下：

创建索引：

 查询数据文件如下：

当我们在MyISAM存储引擎中创建一个表时，这个表会相应生成三个文件

​ 1、.frm文件，这是表定义文件。

​ 2、.myi文件，这个表存储了数据的索引。

​ 3、.md文件，这个表存储的是数据。

2、InnoDB引擎下的索引

        InnoDB引擎（表类型）使用聚集型索引，，即索引文件与数据文件不分离，叶子节点保存索引及目标数据记录。

        InnoDB引擎对于主键索引和非主键索引的设计结构并不一致。
        InnoDB引擎主键索引结构如下，叶子节点存放索引和数据记录【以id为主键】。

        InnoDB引擎非主键索引结构如下，叶子节点存放索引和主键值（这就是为什么InnoDB要求表格一定要有主键）【以age为索引】。

        我们创建innoDB，查询数据文件只有 t_logger.ibd文件，这里说明，数据和索引是放在一起的，如下只有ibd文件：

 四、联合索引结构

我们创建一张表，并创建联合索引如下：

{name,age,sex}

那么如果有数据，索引的结果就如下：

  联合索引简单理解就是将索引字段放到一起作为一个整体的索引，比如需要匹配下列语句。

SELECT * FROM sys_user WHERE `name`='张三' AND age=21 AND sex='男'

由于将三个字段作为联合索引，当查询上述语句的时候：

首先匹配name，即查找name为张三的索引，就会匹配到上图非叶子节点中的｛张三 21 男｝这一节点，则不需要往右进行匹配。

接着当子节点name相同，往下查找进行age的匹配，最后匹配sex。

也就是说，联合索引按照设定索引的时候的字段顺序进行匹配。

五、索引左前缀原则

索引左前缀原则：如果索引为多列联合索引，查询从索引的最左列开始并且不跳过索引中的列，索引才会生效。

也就是说，如上述联合索引（name，age，sex），只有当where条件里为name，name&age,name&age&sex这三种情况，联合索引才会生效，当然，与在where里面写的顺序无关。

如下sql，会走索引的查询条件如下：

EXPLAIN SELECT * FROM sys_user WHERE `name`='张三' 
EXPLAIN SELECT * FROM sys_user WHERE `name`='张三' AND age=21 
EXPLAIN SELECT * FROM sys_user WHERE `name`='张三' AND age=21 AND sex='男'
EXPLAIN SELECT * FROM sys_user WHERE  sex='男' AND `name`='张三'AND age=21 

如下情况则不会走索引：

EXPLAIN SELECT * FROM sys_user WHERE sex = '男' # 不走索引，因为跳过了列
EXPLAIN SELECT * FROM sys_user WHERE age = 35 # 不走索引，因为跳过了列
EXPLAIN SELECT * FROM sys_user WHERE `name` = '张三' and sex='男' # name走索引，sex不走索引，因为跳过了列
EXPLAIN SELECT * FROM sys_user WHERE age = 20 and sex = '男' # 都不走索引，因为跳过了列

为什么跳过列会导致索引失败呢？

因为上述查询是联合索引数据结果，查询需要遵循索引字段顺序，所有越过会导致无法查询对应的值，导致联合索引失效。

六、索引类型

        Mysql目前主要支持以下几种类型索引：普通索引（NORMAL），唯一索引（UNIQUE），主键索引（PRIMARY KEY），联合索引、全文索引（FULLTEXT）和空间索引（SPATIAL）。

1、单列索引

1.1、普通索引---NORMAL

这个就是最基本的索引，没有任何限制。

创建索引：

CREATE TABLE `sys_user` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`name` char(255) CHARACTER NOT NULL ,
`age` int(11) NULL,
`sex` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
INDEX normal_index (name(length))# 建表的同时为name字段创建索引【length代表索引长度，只有字符串类型字段才可以设置索引长度】

)

# 修改表结构的时候为name字段创建索引
ALTER TABLE sys_user ADD INDEX normal_index ON (name(length))

# 直接为name字段创建索引
CREATE INDEX normal_index ON sys_user(name(length))

删除索引：

DROP INDEX index_name ON table

1.2、唯一索引---UNIQUE

        表示唯一的，不可以重复，用来约束数据中么一列不能重复，如用户表的用户id，也可以用来约束联合索引，联合不重复情况。

CREATE TABLE `sys_user` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`name` char(255) CHARACTER NOT NULL ,
`age` int(11) NULL,
`sex` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
UNIQUE normal_index (name(length))# 建表的同时为name字段创建索引【length代表索引长度，只有字符串类型字段才可以设置索引长度】
)

# 修改表结构的时候为name字段创建索引
ALTER TABLE sys_user ADD UNIQUE unique_index ON (name(length))

# 直接为name字段创建索引
CREATE UNIQUE INDEX unique_index ON sys_user(name(length))

1.3、主键索引---PRIMARY KEY

特殊的唯一索引，一个表只能由一个主键，且主键不能为空。一般在建表时创建主键索引。

CREATE TABLE `sys_user` (# 建表的同时为id字段创建主键索引
`id` int(11) NOT NULL AUTO_INCREMENT ,
`name` char(255) CHARACTER NOT NULL ,
`age` int(11) NULL,
`sex` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)

在建表时如果没有指定主键会怎么样？

简单回答：

一般情况下，这样做并不会出现什么不好的情况，因为InnoDB会自动帮你创建一个不可见的、长度为6字节的row_id，而且这个row_id是由InnoDB维护全局的dictsys.row_id，每次插入一条数据时都会让全局row_id加一（未定义主键的表会使用全局row_id作为主键id）。

但是如果全局row_id一直涨，直到涨到248-1时，这时候再加一就会让低48位的row_id都为0，此时如果再插入一条数据，它拿到的row_id就是0，这样的话就有可能存在主键冲突的。

拓展：

InnoDB会自动帮你创建一个不可见的、长度为6字节的row_id，而且InnoDB维护了一个全局的dictsys.row_id，所以未定义主键的表都会共享该row_id，每次插入一条数据都把全局row_id当成主键id，然后全局row_id加1。

该全局row_id在代码实现上使用的事bigint unsigned类型，但实际上只给row_id保留了6字节，所以这种设计就会存在一个问题：如果全局row_id一直涨，直到2的48次幂-1时，这个时候再加1，row_id的低48位都会变为0，如果再插入新一行数据时，拿到的row_id就为0，这样的话就存在主键冲突的可能，所以为了避免这种隐患，每个表都需要一个主键。

2、联合索引

        指多个字段上创建的索引，遵循索引最左前缀原理【不算是一种类型，而是一种结构】，创建联合索引的时候是可以指定索引类型的。

CREATE TABLE `sys_user` (# 建表的同时为name,age,sex字段创建索引
`id` int(11) NOT NULL AUTO_INCREMENT ,
`name` char(255) CHARACTER NOT NULL ,
`age` int(11) NULL,
`sex` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
INDEX union_index (name,age,sex)
)

# 修改表结构的时候为name,age,sex字段创建索引
ALTER TABLE sys_user ADD INDEX union_index ON (name,age,sex)

# 直接为name,age,sex字段创建索引
CREATE INDEX union_index ON sys_user(name,age,sex)

 # 为name,age,sex字段创建唯一索引，需全部匹配完整
CREATE UNIQUE INDEX union_index ON sys_user(name,age,sex)

3、全文索引---FULLTEXT

        主要用来查找文本中的关键字，而不是直接与索引中的值相比较。【仅能正在char、varchar，text 列上创建】，FULLTEXT索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。FULLTEXT索引配合match against操作使用，而不是一般的where语句加like。

创建索引：

CREATE TABLE `sys_user` (
# 建表的同时为name字段创建索引【length代表索引长度，只有字符串类型字段才可以设置索引长度】
`id` int(11) NOT NULL AUTO_INCREMENT ,
`name` char(255) CHARACTER NOT NULL ,
`age` int(11) NULL,
`sex` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
FULLTEXT fulltext_index (name(length))
)

# 修改表结构的时候为name字段创建索引
ALTER TABLE sys_user ADD FULLTEXT fulltext_index ON (name(length))

# 直接为name字段创建索引
CREATE FULLTEXT INDEX fulltext_index ON sys_user(name(length))

使用案例如下：

# MATCH 指定目标匹配字段
# AGAINST 指定全文检索的关键字
# IN BOOLEAN MODE 表示包含即可，而不理会是否在开头
select * from sys_user where MATCH (`name`) AGAINST ('xiao' IN BOOLEAN MODE);

4、空间索引---SPATIAL

        Mysql在5.7之后支持OpenGIS几何数据模型，同时还支持空间索引。空间索引通过R树【即多维的B树】实现，仅支持两个维度的数据，且仅能应用在空间数据上。

        没有用过，就是这么任性，不写了。

七、索引失效情况分析

        Mysql是否使用索引并不一定依据索引规则，会考量匹配条件的数据量与数据总量比例决定是否走索引【数据量极少的时候不用索引可能更快】。

        看了前面的，可以发现索引也不是一定按照我们的想法，各种查询的条件都会走索引，下面总结一下索引失效的原因。

注意

我们创建索引 {age,sex},不在是{name,age,sex}，在创建name单独索引

我们创建索引 {age,sex},不在是{name,age,sex}，在创建name单独索引

我们创建索引 {age,sex},不在是{name,age,sex}，在创建name单独索引

我们创建索引 {age,sex},不在是{name,age,sex}，在创建name单独索引

我们创建索引 {age,sex},不在是{name,age,sex}，在创建name单独索引

我们创建索引 {age,sex},不在是{name,age,sex}，在创建name单独索引

1、or查询条件

用or查询条件时，需要满足or查询条件的字段全部都有索引，否则索引会失效。

此外，or查询条件时，联合索引不生效，哪怕遵循是左前缀原则。

 我们可以用执行计划查看，此次查询sql是否使用了索引。

2、不遵循最左前缀原则

不遵循最左前缀原则，查询会使用索引失败：

 3、like查询以%开头

当我们右like的时候，是可以用索引查询的：

 当我们使用左like的时候索引失效：

 4、需要进行类型转换

 5、where中索引列有运算

 6、where中索引列使用了函数

 7、where中索引列使用了不等于（!= 或 <>）

索引数据结构BTree以等于为规则去查找节点

 8、存储引擎不能使用索引中范围条件右边的列

当age用范围查询时：

 八、总结

啊切~  啊切~  啊切~  

多喝热水