MySQL索引详细

一、简介

索引用于快速找出在某个列中有一特定值的行。

不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。

如果有了索引，那么会将该Phone字段，通过一定的方法进行存储，好让查询该字段上的信息时，能够快速找到对应的数据，

二、优缺点

2.1优点

1. 所有的MySql列类型(字段类型)都可以被索引，也就是可以给任意字段设置索引
2. 大大加快数据的查询速度

2.2缺点

创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加

索引也需要占空间，数据表中的数据也会有最大上限的 如果我们有大量的索引，索引文件可能会比数据文件更快达到上线值

当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度。

三、分类

操作：

查看索引：show index from

删除索引：drop index <索引名> on <表名>

3.1 单列索引

1.主键索引（自动创建）

create table user(id varchar(20) primary key,name varchar(20))

2.普通索引

MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

-建表时创建(此时索引名和列名相同)
create table user(id varchar(20) primary key, name varchar(20),key(name))
-建表后创建
create index name_index on user(name)

3.唯一索引

索引列中的值必须是唯一的，但是允许为空值，

-建表示创建
create table user(id varchar(20) primary key, name varchar(20),unique(name))
-建表后创建
create unique index name_index on user(name)

3.2 组合索引(复合索引)

在表中的多个字段组合上创建的索引 只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。

-建表示创建
create table user (id varchar(20) primary key, name varchar(20), age int,key(name,key))
-建表后创建
create index name_age_index on user(name,age)

MySQL 最左前缀原则

mysql 建立多列索引（联合索引）有最左前缀的原则，即最左优先，如：

如果有一个 2 列的索引 (col1, col2)，则已经对 (col1)、(col1, col2) 上建立了索引； 如果有一个 3 列索引 (col1, col2, col3)，则已经对 (col1)、(col1, col2)、(col1, col2, col3) 上建立了索引；

并且mysql 具有查询优化器：

MySQL 的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引，所以 MySQL 不存在 where 子句的顺序问题而造成索引失效

四、数据结构

在mysql 使用InnoDB存储引擎时，首先会将插入的数据按照主键进行排序从而形成一个单向链表，然后为了提高查找效率，mysql就将单向链表升级成了B+树。

一般高度为3的B+数可存储的记录为10亿左右，因此对于我们2-3层树就足够了。

4.1 B+树特点

B+树是B树的一种变形，比B树具有更广泛的应用

• 为所有叶子结点增加一个链指针；
• 所有关键字都在叶子结点出现；
• 非叶子结点作为叶子结点的索引；B+树总是到叶子结点才命中；

4.2为什么使用B+数

五、聚簇/非聚簇索引

5.1 聚簇索引

定义

将数据与索引放到了一起，索引结构的叶子节点保存了行数据。

简介

1.如果一个主键被定义了，那么这个主键就是作为聚集索引

1. 如果没有主键被定义，那么该表的第一个唯一非空索引被作为聚集索引
2. 如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列，改列的值会随着数据的插入自增。
3. 自增主键会把数据自动向后插入，避免了插入过程中的聚集索引排序问题。聚集索引的排序，必然会带来大范围的数据的物理移动，这里面带来的磁盘IO性能损耗是非常大的。

注意

​ 主键索引一定是聚簇索引（在mysql数据库innodb引擎里面，主键的确就是聚集索引。）

5.2 非聚簇索引（辅助索引）

将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置。

辅助索引访问数据总是需要二次查找

叶子节点存储的是主键值：（不存主键地址原因）如果数据记录发生了页裂变导致数据地址变了，那辅助索引也要更新，对于这种情况来说存储主键更好

5.3注意

建议使用int 自增作为主键

原因：

聚簇索引的数据在索引中存放顺序与物理存放顺序是一样的，那么，只要索引是相邻的，对应的数据在磁盘上也是相邻的。 如果不是自增id，那么在添加记录时就会不断地调整数据的地址、数据的物理地址、分页，而不是直接添加到索引树的末尾。 如果是自增的，就简单了，直接一页一页逐步添加，索引结构相对紧凑，并且磁盘碎片也少，效率高。

六、无法使用索引

情况一：查询语句中使用like关键字

​ 如果使用like关键字，并且匹配字符串时的第一个字符为“%”,那么就无法查询索引树，因为不知道要查询什么。但如果%在后面，就可以 使用。

情况二：查询语句中使用多列索引

​ 不满足最左前缀原则时

情况三：查询语句中使用or关键字

​ 只有当or两边的字段都创建了索引，才可以使用索引，否则无法使用。

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/115168.html原文链接：https://javaforall.cn