MySQL索引

s_persist

已于 2022-11-12 17:48:28 修改

阅读量176

点赞数

分类专栏：数据库文章标签： mysql 数据库 java

于 2022-10-01 18:08:15 首次发布

本文链接：https://blog.csdn.net/qq_52145272/article/details/127134174

版权

数据库专栏收录该内容

10 篇文章 0 订阅

订阅专栏

一、认识索引

1.1 什么是索引

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

比如常见的数组，它的索引就是它的下标，我们可以通过索引直接找到对应的数据。

索引的出现就是为了提高数据库查找的性能，而且创建索引也是很简单的。但是增加索引会带来一下其他的问题，就像在数组中间插入数据，需要移动后面所有的数据，甚至还可能要扩容。数据库索引也类似，比如插入、更新、删除一个数据的效率会降低，因为要修改索引，会增大IO交换量。

索引的价值：提高一个海量数据的检索速度。

常见索引分为：

主键索引(primary key)
唯一索引(unique)
普通索引(index)
全文索引(fulltext)–解决中子文索引问题。

1.2 MySQL与磁盘交互基本单位

而 MySQL 作为一款应用软件，可以想象成一种特殊的文件系统。它有着更高的IO场景，所以，为了提高基本的IO效率， MySQL 的InnoDB 引擎IO的基本单位是 16KB，也就是说每次和磁盘交互的单位是 16KB。
这个基本数据单元，在 MySQL 这里叫做page

mysql> system clear;
mysql> show global status like "innodb_page_size";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)

为什么交互的基本单位为Page（页）

由冯诺依曼体系可知，IO的时间远远大于CPU运算的时间，如果每次要那个数据就加载那个数据，这样IO的次数就提高了，程序所需要的时间就会提高，效率也就降低了。因此，mysql提高效率的本质是减少IO。
根据局部性原理，连续查找的数据一般是在一个小范围的，因此我们进行IO的时候就加载一个page到内存之中，如果下次数据在这个区间之间，就不需要进行IO，提高了程序的效率。

MySQL会预先向内存申请一个大空间Buffer Pool，用来和磁盘交互，存放page。
显然对于海量数据，MySQL同时加载多个page，那么就需要对多个page进行管理。在页之间是通过双向链表连接起来的，页内的数据是通过单向链表连接的，这样插入和删除的效率高。但是链表的查找效率是非常低的，这就要通过目录来解决了。
目录，就是每本书都会有的，如果我们知道想要查找的内容主标题，就可以通过目录快速定位到某一个章节，这样就提高链表的查询效率。

1.3 提高单个page的查找速率

索引是通过在一个page内部建立页目录的方式来提高页内的查找效率。

首先创建一个简单的表，设置有主键，数据的插入是无序的，但查询结果却是按主键排序的，这是为什么？

mysql> select * from user; --发现竟然默认是有序的！是谁干的呢？排序有什么好处呢？
+----+-----+-----------+
| id | age | name |
+----+-----+-----------+
| 1 | 56 | 欧阳锋 |
| 2 | 26 | 黄蓉 |
| 3 | 18 | 杨过 |
| 4 | 16 | 小龙女 |
| 5 | 36 | 郭靖 |
+----+-----+-----------+
5 rows in set (0.00 sec)

显然对于这几个数据，它们是存储在同页内。数据之间是通过主键关联起来的，所以为了提高查找效率，MySQL会依赖主键自动排序。
在这里插入图片描述

对于有主键索引的数据，可以通过主键建立页目录，类似于书目录，这是用空间换时间的做法。

对于上面的数据建立页目录，如下：
在这里插入图片描述
这样查找id = 4的数据就可以减少一半的搜索次数。所以MySQL使用主键索引自动排序的目的就是为了方便建立目录。

1.4 提高多个page的查找速率

其实解决方法类似，给所有的page也带上目录，用一个page来充当目录，里面的每一项指向一页。

使用一个目录项来指向某一页，而这个目录项存放的就是将要指向的页中存放的最小数据的键值。
和页内目录不同的地方在于，这种目录管理的级别是页，而页内目录管理的级别是行。
其中，每个目录项的构成是：键值+指针。
加入一个页无法存放所有的目录，需要扩展出新的页来充当页目录，对于这两个页目录，同样需要一个页目录来管理，如下图。

存在一个目录页来管理页目录，目录页中的数据存放的就是指向的那一页中最小的数据。有数据，就可通过比较，找到该访问那个Page，进而通过指针，找到下一个Page。

在这里插入图片描述

可以发现，这就是一个B+ 树，所有的有效数据都在叶子结点上，查找到某一个含有效数据的页，就可以查找左右相关的页（叶子结点之间通过双向链表连接），同样符合局部性原理。

总结一下有索引的情况下的查找：

Page分为目录页和数据页。目录页只放各个下级Page的最小键值。
查找的时候，自定向下找，只需要加载部分目录页到内存，即可完成算法的整个查找过程，大大减少了IO次数

1.5 没有显式指定索引怎么办

上面的组织形式是以主键为索引的组织形式，也就是先按照主键进行排序放到数据页中，再用目录页将数据页组织成B+树。如果没有设置主键该以谁为索引呢？

当我们没有手动设置索引的时候， InnoDB 会自动使用表的隐藏主键DB_ROW_ID : 6 byte，隐含的自增ID，用它作为索引，但是因为它是隐藏的，无法使用它来查找，所以查找效率会很低。

1.6 普通索引（第二索引）

有时候我们搜索数据并不是通过主键（或者唯一键）来搜索的，也有可能是通过非主键列来搜索，而主键索引又是通过主键来完成的，所以主键索引就失效了。这时候可以通过建立普通（辅助）索引来解决这一问题。

比如我们想寻找name字段的一条信息，就可以给name字段创建普通索引。普通索引也是一棵B+树，但是和上面主键的B+树有一些区别：

该B+树的叶子节点存放的不再是完整的用户记录，而是只记录name列和主键值；
目录页记录除了存储索引列（name）和页号之外，同时还存储了主键值。之所以存主键值，主要是因为数据页和目录页不再按照主键进行排序，而是按照name排序。按照name排序时，会出现name相同的情况，此时就可以根据主键进行排序。
此时通过普通索引，就能够找到name对应的主键了，然后我们就可以再从主键索引中找到name对应的完整信息。这个过程称为回表。

1.7 复合索引

我们的主键也有可能是复合主键。
包含多个列的主键始终会自动以复合索引的形式创建索引，其列的顺序是它们在表定义中出现的顺序，而不是在主键定义中指定的顺序。

假设我们以id列和name列作为主键，那么在系统创建主键索引时，自然也是创建一棵B+树，但就不是以id为索引了，而是以id和name为索引，当id字段相同时，则会按照name排序。并且在搜索时，也是先匹配id字段，然后再name字段。

同理，我们也可以对多个非主键的字段建立普通的复合索引，其方式和上面类似，比如假设我们为name列和phone列建立联合索引，创建一棵B+树：

叶子节点存放的是name列、phone列和主键值；
目录页记录除了存储索引列（name和phone）和页号之外，同时还存储了主键值。按照name排序时，会出现name相同则按照phone排序，phone再相同则按照主键进行排序。

二、InnoDB 为什么采用B+树

链表
链表是线性结构的，查找的时候需要线性遍历。
二叉搜索树
在某些场景下，二叉搜索树会退化成链表。
AVL树和红黑树
和B+树相比，B+树的层数更低，每层进行一次IO，树越矮，IO的次数越少，AVL和红黑树相对B+树来说，比B+树更高。
Hash
官方的索引实现方式中， MySQL 是支持HASH的，不过 InnoDB 和 MyISAM 并不支持。Hash跟进其算法特征，决定了虽然有时候也很快(O(1))，不过，在面对范围查找就明显不行。
B树
B树非叶子节点，既有数据，又有Page指针；而B+，只有叶子节点有数据，其他目录页，只有键值和Page指针。
B+树非叶子节点不存储数据，这样一个节点就可以存储更多的key。可以使得树更矮，所以IO操作次数更少。
B+叶子节点，全部相连，而B没有。叶子节点相连，更便于进行范围查找。

三、聚簇索引 VS 非聚簇索引

上面InnoDB使用的索引结构就是聚簇索引

3.1 非聚簇索引

MyISAM 引擎同样使用B+树作为索引结果，叶节点的data域存放的是数据记录的地址。下图为 MyISAM 表的主索引，
Col1 为主键。

在这里插入图片描述
其中， MyISAM 最大的特点是，将索引Page和数据Page分离，也就是叶子节点没有数据，只有对应数据的地址。
而 InnoDB 是将索引和数据放在一起的。

MyISAM 这种用户数据与索引数据分离的索引方案，叫做非聚簇索引

3.2 两种差别

MyISAM的主键索引（非聚簇索引）的叶子节点存储对应数据的地址；
InnoDB 的主键索引（聚簇索引）的叶子节点则是存储用户的整条数据。
MyISAM的普通索引其叶子节点也只是存储对应数据的地址，和主键索引没区别；
InnoDB 的普通索引其叶子节点存储索引值和对应的主键，想找到对应的数据还需要进行回表操作。
聚簇索引主键的插入速度要比非聚簇索引主键的插入速度慢很多。
相比之下，聚簇索引适合排序，非聚簇索引不适合用在排序的场合。因为聚簇索引本身已经是按照物理顺序放置的，排序很快。非聚簇索引则没有按序存放，需要额外消耗资源来排序。

四、索引相关操作

4.1 创建索引

4.1.1 创建主键索引

创建主键之后，MySQL会默认使用主键创建主键索引，所以创建主键的方式就是创建主键索引的方式。

第一种方式
在创建表的时候，直接在字段名后指定 primary key

create table table_name (id int primary key, name varchar(10));

第二种方式：
在创建表的最后，指定某列或某几列为主键索引

create table table_name (id int , name varchar(10), primary key(id) );

第三种方式：
创建表以后再添加主键

create table table_name (id int , name varchar(10));
alter table table_name add primary key(id);

主键索引的特点：

一个表中，最多有一个主键索引，当然可以是复合索引
主键索引的效率高（主键不可重复）
创建主键索引的列，它的值不能为null，且不能重复
主键索引的列基本上是int

4.1.2 创建唯一键索引

同理

第一种方式

-- 在表定义时，在某列后直接指定unique唯一属性。
create table table_name (id int primary key, name varchar(30) unique);

第二种方式

--创建表时，在表的后面指定某列或某几列为unique
create table table_name (id int primary key, name varchar(30), unique(name));

第三种方式

创建表以后再添加唯一键
create table table_name (id int primary key, name varchar(30)）； 
alter table table_name  add unique(name);

唯一索引的特点：

一个表中，可以有多个唯一索引
查询效率高。
如果在某一列建立唯一索引，必须保证这列不能有重复数据。
如果一个唯一索引上指定not null，等价于主键索引。

4.1.3 普通索引的创建

第一种方式

create table table_name (
id int primary key,
name varchar(10),
index(name) --在表的定义最后，指定某列为索引
);

第二种方式

create table table_name (id int primary key,name varchar(10));
alter table table_name add index(name); -- 添加普通索引

第三种方式

create table table_name (id int primary key,name varchar(10));
create index idx_name on table_name(name); -- 创建一个索引名为 idx_name 的索引，索引列为name

普通索引的特点：

一个表中可以有多个普通索引，普通索引在实际开发中用的比较多
如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引

4.1.4 全文索引的创建

当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。MySQL提供全文索引机制，但是有要求，要求表的存储引擎必须是MyISAM，而且默认的全文索引支持英文，不支持中文。如果对中文进行全文检索，可以使用sphinx的中文版(coreseek)。

类似于搜索引擎，根据部分关键字，查找含有关键字的文章。

全文索引关键字为FULLTEXT。

Database changed
mysql> CREATE TABLE articles (
    -> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    -> title VARCHAR(200),
    -> body TEXT,
    -> FULLTEXT (title,body) -- 根据文章标题和主体创建全文索引
    -> )engine=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO articles (title,body) VALUES
    -> ('MySQL Tutorial','DBMS stands for DataBase ...'),
    -> ('How To Use MySQL Well','After you went through a ...'),
    -> ('Optimizing MySQL','In this tutorial we will show ...'),
    -> ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    -> ('MySQL vs. YourSQL','In the following database comparison ...'),
    -> ('MySQL Security','When configured properly, MySQL ...');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

查询包含mysqld的文章

这样使用查询，虽然能够查到数据，但是没有使用到全文索引，只是普通的条件查询。

mysql> select * from articles where body like '%mysqld%';
+----+-------------------+-------------------------------------+
| id | title             | body                                |
+----+-------------------+-------------------------------------+
|  4 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. ... |
+----+-------------------+-------------------------------------+
1 row in set (0.00 sec)

使用全文索引查询

SELECT * FROM 表名WHERE MATCH (索引) AGAINST (查找内容);

mysql> select * from articles where match (title, body) against ('mysqld');
+----+-------------------+-------------------------------------+
| id | title             | body                                |
+----+-------------------+-------------------------------------+
|  4 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. ... |
+----+-------------------+-------------------------------------+
1 row in set (0.00 sec)

4.1.5 索引创建原则

比较频繁作为查询条件的字段应该创建索引
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
更新非常频繁的字段不适合作创建索引
不会出现在where子句中的字段不该创建索引，也就是说索引是用来条件查找的，否则没意义

4.2 查询索引

第一种方法：
show keys from 表名 \G;
第二种方法：
show index from 表名\G;
第三种方法：
desc 表明;

比如查询前面的全文索引：
有两个索引，三个字段

mysql> show index from articles \G
*************************** 1. row ***************************
        Table: articles
   Non_unique: 0     -- 0表示唯一索引
     Key_name: PRIMARY -- 索引的名字
 Seq_in_index: 1     -- 索引中的列序列号，从1开始
  Column_name: id    -- 索引的字段名
    Collation: A     -- 校对规则，列以什么方式存储在索引中，大概意思就是字符序
  Cardinality: 6     -- 基数的意思，表示索引中唯一值的数目的估计值
     Sub_part: NULL  -- 前置索引的意思，如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL
       Packed: NULL  -- 指示关键字如何被压缩。如果没有被压缩，则为NULL
         Null:       -- 如果列含有NULL，则含有YES
   Index_type: BTREE -- 平衡树索引，B+ 树
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: articles
   Non_unique: 1
     Key_name: title
 Seq_in_index: 1
  Column_name: title
    Collation: NULL
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: FULLTEXT
      Comment: 
Index_comment: 
*************************** 3. row ***************************
        Table: articles
   Non_unique: 1
     Key_name: title
 Seq_in_index: 2
  Column_name: body
    Collation: NULL
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: FULLTEXT
      Comment: 
Index_comment: 
3 rows in set (0.01 sec)

4.3 删除索引

第一种方法-删除主键索引： alter table 表名 drop primary key;
第二种方法-其他索引的删除： alter table 表名 drop index 索引名； 索引名就是show keys from 表名中的Key_name 字段，如果为复合索引，名字默认为第一个字段。

mysql> alter table table_name drop index idx_name;

第三种方法方法： drop index 索引名 on 表名;

mysql> drop index name on table_name;

4.4 explain 关键字

作用：它可以对 SQL 语句进行分析，并输出 SQL 执行的详细信息，以供开发人员针对性优化。

例如：
在这里插入图片描述

五、索引覆盖、索引最左匹配原则、索引下推

索引覆盖
主键索引：叶子节点保存数据。
辅助索引：叶子节点保存主键值。
如果辅助索引上已经存在我们需要的数据，那么引擎就不会去主键上去搜索数据了。
索引最左匹配原则
还是id和name为复合索引。
它们在索引过程中是以id排序，id相同才以name排序。此时如果以name查找，就不能用这个复合索引，因为并不是以name排序的。这时候应该以name创建一个普通索引。
索引下推
还是id和name为复合索引。
我们要查询所有id为10，name为’%川’的人的信息，把%加在name字段前面的时候，是无法利用索引的顺序性来进行快速比较的，也就是说这条查询语句中只有id字段可以使用索引进行快速比较和过滤。所以会筛选出所有id为10的主键，然后进行回表操作，如果id为10的信息过多，就会产生多次回表操作。
索引下推就是过滤的动作由下层的存储引擎层通过使用索引来完成，而减少不必要的回表操作。