MySQL：索引

一、索引的介绍

本篇博客是以InnoDB存储引擎为例进行介绍的

1.为何要有索引

索引是用来提高数据库的查询性能的，能够让查询速度快上成百上千倍
可是查询速度的提高是以插入、删除、更新的效率为代价的

常见的索引分为：

主键索引
唯一键索引
普通索引
全文索引

2.MySQL与磁盘交互的基本单位

MySQL作为数据库，天生就是为了处理数据而生，有着更高的IO场景，因此为了提高IO吞吐量，MySQL进行IO的基本单位是16KB
InnoDB存储引擎：
在这里插入图片描述
16384 byte就是16KB

这个16KB在MySQL当中称为一个page

3.Buffer Pool

MySQL在程序运行时，就已经在服务器内部申请了一个名为Buffer Pool的内存空间，来进行各种缓存
MySQL的CURD操作都是在这个Buffer Pool当中进行的，而MySQL以特定的刷新策略把这些数据定期刷新到磁盘当中
MySQL搞这么一个中间层，就是为了缓存数据，提高磁盘IO的吞吐量，从而降低磁盘IO次数，提高CURD的效率

4.MySQL为何要以page为单位进行IO交互

首先，我们要先明确：

影响IO效率的原因不是IO数据量的大小，而是IO次数

因此局部性原理在这里就有用武之地了：
时间局部性：已访问的数据在接下来一段时间内很有可能被再次访问
空间局部性：已访问的数据周围的数据很有可能会被再次访问

因此，当每个数据被访问时，在接下来一段时间内，它和它周围的数据都很有可能会被再次访问

而这个数据和它周围的数据在同一个page当中的概率是很大的，因此MySQL就按照page为单位进行缓存加载，以空间换时间来提高效率

二、page的管理方式

MySQL当中有很多数据表文件，每张表会存在多份page，因此势必就需要找到一种合适的数据结构来对其进行管理

1.有无索引的对照

1.无索引

这是一个没有主键索引的表

mysql> desc role_non_index;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    |int| YES  ||NULL||| name  |varchar(32)| NO   ||NULL||| age   |int| NO   ||NULL||+-------+-------------+------+-----+---------+-------+3 rows in set(0.00 sec)

在这里插入图片描述

2.有索引

mysql> desc role;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    |int| NO   | PRI |NULL||| name  |varchar(32)| NO   ||NULL||| age   |int| NO   ||NULL||+-------+-------------+------+-----+---------+-------+3 rows in set(0.00 sec)

在这里插入图片描述
经过这个对比，我们发现，MySQL会自动将插入我们的数据按照主键进行排序

换言之，MySQL会维护我们数据的按主键自增的次序
为何要这么做呢？

肯定是为了提高查询效率，就像是二叉搜索树提高查询效率的方式一样（维护某种既定规则来进行层层的条件筛选）

2.单page数据结构

到目前为止，我们已经知道了，MySQL要在CURD操作时时刻维护数据按照主键索引的有序性

因此：势必需要能够在任意位置进行O(1)的插入删除

因此，现在有两种选择：
在这里插入图片描述
看起来这个哈希表是非常好的，但是他有一个致命缺陷：不支持O(1)的范围查找

而我们知道：select用范围查找的应用需求是很大的，因此哈希表的方案暂时不考虑

因此对于单个page来说，它内部就是这样的：
在这里插入图片描述
数据记录当中的数据都是有序的，这个好处我们目前只能看出：
它在一定程度上优化了数据记录的查询效率

这个设计的其他好处我们在下面是可以看到的

3.多page数据结构

在单表数据不断被插入的情况下，MySQL会在当前page的容量不足的情况下，自动开辟新的page来存储数据，然后通过指针的方式将page组织起来
在这里插入图片描述
比方说你要看《三国演义》这本书，你想看草船借箭那里，你有两种方法：

从前往后一页一页翻（假设你从来没读过）
一页一页查目录，根据目录指明的页数直接定位

因为目录肯定比正文页数少，因此可以一页一页翻

所以本质上，书的目录就是为了加速定位效率的，是一种以空间换时间的方法

所以为了提高page内部数据项的查找效率，我们给page内部加上目录
所谓目录其实就是（主键索引值+指向对应数据项的指针）

因为数据项是按照主键进行升序排序的，所以就完美适配了目录，就像是书当中的页码都是升序的一样

因此数据项要按照主键升序，正是为了方便引入目录

4.加目录之后的page

在这里插入图片描述
下面我们看看多page：

注意：
新插入的数据不一定就会在新page当中，因为MySQL在插入数据时，会根据主键索引的顺序来找到该数据应该存放的位置

新page当中的数据取决于具体由哪个page进行了页分裂，原page会将一部分数据放到新page当中，目的是平均分配两个page当中的空间使用，提高空间利用率

5.给page带上目录

当page数量特别多时，查询效率还是不够高，那么怎么办呢？
给page加上page
在这里插入图片描述
非叶子节点当中的数据项当中记录的是：每个page的最小主键值及其指针

通过这种方式就可以让目录的管理级别从行级别直接跃迁到page级别
就像是一级目录和二级目录的关系一样

这个非叶子节点被称为页目录
如果继续往上搭：
在这里插入图片描述
至此，我们就给page构建完了用来组织主键索引的数据结构：B+树

6.小小复盘

page分为目录页和数据页，只有数据页才会存放具体数据，而目录页只存放下级page当中的最小键值
查找的时候，从上往下进行查找，只加载一部分目录页到内存即可，大大减少了磁盘IO次数这里的BTREE指的是B+树，而不是B树

三、为何InnoDB要选择B+，而不选择其他数据结构

1.链表

链表的查找效率是O(N)，太慢了

2.二叉平衡搜索树

在数据库索引这里，每遍历一个page就要进行一次磁盘IO，且遍历到底层数据page所需次数就是树的高度

而AVL和红黑是高瘦型的选手，B+是矮胖型的选手

这就意味着，存放等量数据时，B+的高度更低，因此磁盘IO次数更少，效率更高

因此，不是AVL和红黑不行，只是B+在这里更好

3.哈希

MySQL当中有些数据库引擎是支持哈希的，只不过最常用的InnoDB和MyISAM不支持哈希

哈希的效率也很高，好的情况下，增删查改效率逼近O(1)

但是哈希不支持O(1)的范围查找，因此哈希就比不过搜索树系列了

4.B树

在线数据结构演示链接
B树的非叶子节点也会存放数据，而B+树只有非叶子节点才会存放数据
在这里插入图片描述

很明显可以看到：非叶子节点明明可以存放更多的指针，所以，B+树：

效率：B树的非叶子节点要存放数据，牺牲了很大本可以用来存放下级page的最小键值和指针的空间，因此高度只会比B+高，效率会更低

范围查找：B+的叶子节点是通过双向链表全部直接相连的，因此他比B树更适合范围查找

四、聚集索引和非聚集索引

1.介绍

聚集索引：将用户数据和索引数据放到一起
非聚集索引：将用户数据和索引数据进行分离

其中，InnoDB就是聚集索引，而MyISAM是非聚集索引

MyISAM同样使用B+树作为索引结果，只不过叶子节点的data域存放的是数据记录的地址，下图当中col1是主键
在这里插入图片描述
这也就是MyISAM最大的特点：索引数据跟用户数据是分离的，即：叶子节点没有数据，而是数据存放的地址

2.索引介绍

由于MyISAM是非聚集索引，因此MyISAM的普通索引、唯一键索引和主键索引都是这么存储的

叶子节点存放的都是用户数据的地址，这几棵B+树的唯一区别就是分类所依据的字段不同，仅此而已

而对于InnoDB这种聚集索引来说，只有主键索引那棵B+树才会存放用户数据

而唯一键索引和普通索引当中叶子节点的data域存放的是对应的主键索引值

因此对于唯一键索引和普通索引来说：
非聚集索引时：都是一次索引即可
聚集索引时：需要先通过第一次索引找到对应的主键索引值，然后通过主键索引值再到主键索引那棵B+树上进行二次索引（这个过程叫做回表查询）

因此MyISAM的读效率是快于InnoDB的

五、索引操作

1.主键索引

1.创建索引

第一种方式：建表时在字段后面声明索引

create table ifnot exists test_table(
    id int primary key,
    name varchar(32));

第二种方式：建表时在表最后声明索引

create table ifnot exists test_table(
     id int,
     name varchar(32),
     primary key(id));

第三种：创建表之后，再添加主键

createtableifnotexists test_table(
     id int,
     name varchar(32));altertable test_table addprimarykey(id);

2.主键索引的特点

mysql> desc test_table;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    |int| NO   | PRI |NULL||| name  |varchar(32)| YES  ||NULL||+-------+-------------+------+-----+---------+-------+2 rows in set(0.01 sec)

主键索引不可重复，具有唯一性
主键不能为空，且不允许重复
主键索引的列一般都是int
主键索引的效率高（因为字段不允许重复）

2.唯一键索引

1.创建索引

跟主键索引一样，只不过在同一张表上唯一键索引可以有很多
且它的关键字是unique
第一种方法：

create table ifnot exists test_table( 
      id int unique,
      name varchar(32) unique
);

第二种方法：

create table ifnot exists test_table( 
     id int, 
     name varchar(32),unique(id),unique(name));

注意：unique(id,name)这是给（id，name）添加复合索引，而不是给id和name分别添加索引

第三种方法：

altertable test_table addunique(id);altertable test_table addunique(name);

alter添加唯一键索引，只能分开添加，不允许这么添加：

mysql> alter table test_table add unique(id),unique(name);
ERROR 1064(42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'unique(name)' at line 1
mysql> alter table test_table add unique(id),(name);
ERROR 1064(42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(name)' at line 1
mysql> alter table test_table add unique(id),(name);

2.唯一键索引的特点

mysql> desc test_table;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    |int| YES  | UNI |NULL||| name  |varchar(32)| YES  | UNI |NULL||+-------+-------------+------+-----+---------+-------+2 rows in set(0.00 sec)

一个表当中可以有多个唯一键索引
字段允许为空，但是不允许重复
如果一个唯一键索引加上not null约束，那么它的约束性跟主键一样
查询效率高（因为字段不允许重复）

3.普通索引

普通索引跟主键/唯一键索引的创建方式有所不同

1.创建

第一种方式：建表时在表最后声明索引

create table ifnot exists test_table( 
        id int,
         name varchar(32),index(id),index(name));

第二种方式：建完表之后指定某列为普通索引

alter table test_table add index(id);
alter table test_table add index(name);

第三种方式：建完表为某列创建一个普通索引

create index index_id on test_table(id);
create index index_name on test_table(name);
分别创建了一个名为index_id和index_name的普通索引

2.普通索引的特点

mysql> desc test_table;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    |int| YES  | MUL |NULL||| name  |varchar(32)| YES  | MUL |NULL||+-------+-------------+------+-----+---------+-------+2 rows in set(0.00 sec)

一个表当中可以有多个普通索引
普通索引对应的列允许存在重复值

4.查询、删除索引

1.查询索引

show keys from 表名

mysql> show keys from test_table;+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| Table      | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| test_table |0| PRIMARY    |1| id          | A         |0|NULL|NULL|| BTREE      ||| YES     |NULL|| test_table |0| name       |1| name        | A         |0|NULL|NULL| YES  | BTREE      ||| YES     |NULL|| test_table |1| index_name |1| name        | A         |0|NULL|NULL| YES  | BTREE      ||| YES     |NULL|+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+3 rows in set(0.00 sec)

第一个是建立在id上的主键索引，第二个是建立在name上的唯一键索引，第三个是建立在name上的普通索引

show index from 表名

mysql> show index from test_table;+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| Table      | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| test_table |0| PRIMARY    |1| id          | A         |0|NULL|NULL|| BTREE      ||| YES     |NULL|| test_table |0| name       |1| name        | A         |0|NULL|NULL| YES  | BTREE      ||| YES     |NULL|| test_table |1| index_name |1| name        | A         |0|NULL|NULL| YES  | BTREE      ||| YES     |NULL|+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+3 rows in set(0.00 sec)

desc表名

mysql> desc test_table;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    |int| NO   | PRI |NULL||| name  |varchar(32)| YES  | UNI |NULL||+-------+-------------+------+-----+---------+-------+2 rows in set(0.00 sec)

2.删除索引

删除主键索引：alter table 表名 drop primary key
删除其他索引：alter table 表名 drop index 索引名
drop index 索引名 on 表名 这个索引名就是show index from 表名显示的Key_name字段的值

六、全文索引

1.介绍

当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。
其中，只有MyISAM支持全文索引，而且默认只支持英文，不支持中文。如果想要对中文进行检索，可以使用sphinx的中文版(coreseek)

2.使用

1.建表，插入数据

mysql> create table ifnot exists articles(-> id intunsigned auto_increment 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

2.以前的查询方式

比方说我们要查询body当中包含database的数据
可以利用模糊匹配的方式进行查询

mysql> select * from articles where body like '%database%';+----+-------------------+------------------------------------------+| id | title             | body                                     |+----+-------------------+------------------------------------------+|1| MySQL Tutorial    | DBMS stands for DataBase ...||5| MySQL vs. YourSQL | In the following database comparison ...|+----+-------------------+------------------------------------------+2 rows in set(0.00 sec)

这样并没有用到所谓的全文索引，我们可以使用explain来看看这条SQL用没用

mysql> explain select * from articles where body like '%database%'\G
***************************1. row ***************************
           id:1
  select_type: SIMPLE
        table: articles
   partitions:NULL
         type: ALL
possible_keys:NULL
          key:NULL//  key为 NULL 表示没有用到索引
      key_len:NULL
          ref:NULL
         rows:6
     filtered:16.67
        Extra: Using where
1 row in set,1warning(0.00 sec)

3.使用全文索引

mysql> select * from articles where match(title,body)against('database');+----+-------------------+------------------------------------------+| id | title             | body                                     |+----+-------------------+------------------------------------------+|5| MySQL vs. YourSQL | In the following database comparison ...||1| MySQL Tutorial    | DBMS stands for DataBase ...|+----+-------------------+------------------------------------------+2 rows in set(0.00 sec)

mysql> explain select * from articles where match(title,body)against('database')\G
***************************1. row ***************************
           id:1
  select_type: SIMPLE
        table: articles
   partitions:NULL
         type: fulltext
possible_keys: title
          key: title // key 为 title 用到了全文索引
      key_len:0
          ref:const
         rows:1
     filtered:100.00
        Extra: Using where
1 row in set,1warning(0.00 sec)

七、索引创建原则

1.原则

比较频繁作为查询条件的字段应该创建索引
更新非常频繁的字段不适合创建索引
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
不会作为查询条件的字段不应该创建索引

2.复合索引

1.复合索引

复合索引是基于表中两个或两个以上的列创建的索引。
这种索引允许数据库在执行查询时利用多个列的组合来快速定位数据

比如：

create index 索引名 on 表名(column1, column2,..., columnN);

复合索引的顺序非常重要，因为有索引最左匹配原则

2.索引最左匹配原则

MySQL匹配复合索引时，会从索引的最左列开始匹配，并且只有匹配了索引的最左前缀，索引才能被使用

比如现在有一个复合索引（A,B,C）：
where A =? （完全利用索引）
where A =?and B =? （完全利用索引）
where A =?and B =?and C =? （完全利用索引）
where B =? （无法利用索引，因为跳过了最左列A）
where A =?and C =? （只能利用索引的A部分，因为跳过了B）

3.索引覆盖

索引覆盖是一种查询优化技术，当查询只需要访问索引当中的数据时，MySQL直接通过索引来返回结果，而无需回表查询对应数据，从而提高查询效率

例子：

create table ifnot exists user(
    id int primary key,
    name varchar(32),
    age int);
create index index_idx on user(age,name);

比方说我要查询年龄大于20岁的所有用户

select username, age from users where age >20;

此时就可以直接从复合索引index_idx当中读取数据了，而无需进行回表查询
提高了查询效率

以上就是MySQL：索引的全部内容哦~

标签： mysql 索引

本文转载自: https://blog.csdn.net/Wzs040810/article/details/141801327
版权归原作者 program-learner 所有，如有侵权，请联系我们删除。

MySQL：索引

MySQL：索引

一、索引的介绍

1.为何要有索引

2.MySQL与磁盘交互的基本单位

3.Buffer Pool

4.MySQL为何要以page为单位进行IO交互

二、page的管理方式

1.有无索引的对照

1.无索引

2.有索引

2.单page数据结构

3.多page数据结构

4.加目录之后的page

5.给page带上目录

6.小小复盘

三、为何InnoDB要选择B+，而不选择其他数据结构

1.链表

2.二叉平衡搜索树

3.哈希

4.B树

四、聚集索引和非聚集索引

1.介绍

2.索引介绍

五、索引操作

1.主键索引

1.创建索引

2.主键索引的特点

2.唯一键索引

1.创建索引

2.唯一键索引的特点

3.普通索引

1.创建

2.普通索引的特点

4.查询、删除索引

1.查询索引

2.删除索引

六、全文索引

1.介绍

2.使用

1.建表，插入数据

2.以前的查询方式

3.使用全文索引

七、索引创建原则

1.原则

2.复合索引

1.复合索引

2.索引最左匹配原则

3.索引覆盖

发表评论

“MySQL：索引”的评论:

关于作者

overfit同步小助手

相关阅读

文章导航