MySQL索引

最新推荐文章于 2023-07-25 11:38:34 发布

一个追梦的少年

最新推荐文章于 2023-07-25 11:38:34 发布

阅读量1.2k

点赞数 10

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_56999918/article/details/127910065

版权

索引

索引的相关概念

什么是索引？索引其实就是一个数据结构。当表中的数据量到达几十万甚至上百万的时候，SQL查询所花费的时间会很长，导致业务超时出错，此时就需要用索引来加速SQL查询。
由于索引也是需要存储成索引文件的，因此对索引的使用也会涉及磁盘I/O操作。如果索引创建过多，
使用不当，会造成SQL查询时，进行大量无用的磁盘I/O操作，降低了SQL的查询效率，适得其反，因此掌握良好的索引创建原则非常重要。

索引分类

索引是创建在表上的，是对数据库表中一列或者多列的值进行排序的一种结果。索引的核心是提高查询的速度！物理上(聚集索引&非聚集索引)/逻辑上(…)
索引的优点：提高查询效率。
索引的缺点：索引并非越多越好，过多的索引会导致CPU使用率居高不下，由于数据的改变，会造成索引文件的改动，过多的磁盘I/O造成CPU负荷太重。下面我们来看看这个索引分为哪几类。
在这里插入图片描述
下面我们一一来解释一下他们分别代表的是什么意思？
**1、主键索引：**使用Primary Key修饰的字段会自动创建索引(MyISAM, InnoDB).通过一段sql来展示一下这个如何创建主键索引

create table `User`(
 id int primary key auto_increment,
 name varchar(20)
 )

我们可以使用show create table User查看是否含有这个主键。

mysql> show create table user;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                    |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

**2.唯一键索引：**使用UNIQUE修饰的字段，值不能够重复，主键索引就隶属于唯一性索引。

mysql> create table user(
    -> id int,
    -> name varchar(20),
    -> unique key(name)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> show create table user;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                               |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3.普通索引：没有任何限制条件，可以给任何类型的字段创建普通索引(创建新表&已创建表，数量是不限的，一张表的一次sql查询只能用一个索引 where a=1 and b=‘M’)

mysql> create table user(
    -> id int,
    -> name varchar(20),
    -> index(name)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> show create table user;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                        |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

4.联合索引

mysql> create table user(
    -> id int,
    -> name varchar(20),
    -> age int,
    -> index(id,name)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> show create table user;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                         |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  KEY `id` (`id`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

如果此时表已经存在了，我们可以使用这个alter table来更改。下面一一给出这些方法来这个添加这个索引

//唯一键索引
CREATE UNIQUE INDEX IndexName ON `TableName`(`字段名`(length));
# 或者
ALTER TABLE TableName ADD UNIQUE (column_list); 

//普通索引
CREATE INDEX IndexName ON `TableName`(`字段名`(length));
# 或者
ALTER TABLE TableName ADD INDEX IndexName(`字段名`(length));

//主键索引
ALTER TABLE TableName ADD PRIMARY KEY(column_list);

5.删除这个索引

DROP INDEX 索引名 ON 表名;
alter table user drop primary key;

索引的底层数据结构及其原理

MySQL支持两种索引，一种的B-树索引，一种是哈希索引，大家知道，B-树和哈希表在数据查询时的效率是非常高的。这里我们主要讨论一下MySQL InnoDB存储引擎，基于B-树（但实际上MySQL采用的是B+树结构）的索引结构。B-树是一种m阶平衡树，叶子节点都在同一层，由于每一个节点存储的数据量比较大，索引整个B-树的层数是非常低的，基本上不超过三层。
由于磁盘的读取也是按block块操作的（内存是按page页面操作的），因此B-树的节点大小一般设置为
和磁盘块大小一致，这样一个B-树节点，就可以通过一次磁盘I/O把一个磁盘块的数据全部存储下来，
所以当使用B-树存储索引的时候，磁盘I/O的操作次数是最少的（MySQL的读写效率，主要集中在磁盘
I/O上）
在这里插入图片描述
下面我们来看看这个B+树

为什么MySQL存储引擎最终选择这个B+树作为存储引擎而不是这个B树了？下面我们一起来讨论一下
为什么MySQL存储引擎选择了B+树作为这个存储引擎。

首先我们来看一下B树和B+树有什么不同，下面几点是博主任务不同的地方

B+树非叶子节点不存储数据的，仅存储键值(索引地址)，而B树节点中不仅存储键值，也会存储数据。B+树之所以这么做是因为在数据库中页的大小是固定的，innodb中页的默认大小是16KB。如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数会再次减少，数据查询的效率也会更快。
B+树索引的所有数据均存储在叶子节点，且数据是按照顺序排列的。B+树使得范围查找，排序查找，分组查找以及去重查找变得简单高效
B+树各个页之间是通过双向链表连接，叶子节点中的数据是通过单向链表连接的。我们通过双向链表和单向链表连接的方式可以找到表中所有的数据。

通过以上的分析我们也就能够知道这个MySQL索引引擎为什么选择这个B+树主要有以下原因

B+树相对B树的高度更低磁盘IO的次数更少。
B+树的数据存放到叶子节点并且叶子节点通过这个指针串联起来，更适合范围查找。
B+树理论上层数更低索引效率会比B树效率好一些

叶子节点被串到一个链表当中形成一个有序链表如果要进行索引的索引&整表搜索直接遍历叶子节点的有序链表即可！或者做范围查询的时候直接遍历叶子节点的有序链表即可。

主键索引&二级索引

InnoDB存储引擎数据和索引在一起假设我们有一张表student 那么在对应的数据库文件当中有出现这个student.frm和这个student.ibd(数据和索引文件）。下面我们来构造一个场景。假设我们有这样一张表

mysql> select * from user;
+----+------+---------+
| id | age  | name    |
+----+------+---------+
|  1 |   20 | zhansan |
|  2 |   18 | lisi    |
|  3 |   21 | ksy     |
|  4 |   28 | lyz     |
+----+------+---------+
4 rows in set (0.00 sec)

场景一: id为主键

select * from user;

那么搜索的是整颗索引树。

select * from user where id=4;

通过类似二分搜索的方式找到4所在的data此时就拿到数据了。

select * from user where id<4;

在B+树的叶子节点进行遍历即可。下面我们通过explain来分析一下这个SQL的执行计划

mysql> explain select * from user where id<4;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    3 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

下面我们来看看这个场景二:name 不是主键也不是索引

select * from user where name='ksy';

我们可以使用这个explain 来看一下这个SQL的执行计划

mysql> explain select * from user where name='ksy';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

此时我们发现它就是这个整表搜索了，效率就比较的低。

下面我们来看看这个场景三：id为主键，name为这个二级索引（普通索引也叫做辅助索引树）

mysql> explain select * from user where name='ksy';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | name          | name | 83      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+

此时我们需要引入一个总要的概念叫做回表。首先需要搜索整个name所在的二级索引树，找到ksy对应的主键id，然后再拿整个id回表再主键索引树上搜索id的那一行记录。

如果我们的查询语句改为这个 select name,id from user where name='ksy’此时又会有什么变化吗？

+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ref  | name          | name | 83      | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

此时我们发现和上面的*相比好像出现了这个Using index。这也就是我们所说的索引覆盖。因为再name所在的二级索引树上直接就能找到了所有就不需要回表去进行查找了。这也叫做索引覆盖。

同样的是上面这个场景如果我们的查询语句是这个

explain select * from user where age=20 order by name

如果我们只给这个name添加索引会有什么问题吗？下面我们使用explain来看一下

mysql> explain select * from user where age=20 order by name;                                                                                                              
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+

此时我们发现出现了这个using filesort 出现了这个额外的排序并且全表搜索了。如果我们给age添加索引此时也任然会有这个using filesort 这是因为一次查询只能用到一个索引。此时我们就可以使用这个联合索引

mysql> create index age_name on user(age,name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from user where age=20 order by name;                                                                                                             
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | user  | NULL       | ref  | age_name      | age_name | 5       | const |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

此时我们就发现这个using filesort消失了。我们的优化也做到了

聚集和非聚集索引

InnoDB 中的索引详解，InnoDB 的数据文件本身就是索引文件，表数据文件本身就是按 B+ 树组织的一个索引结构，其叶子节点的键值就是表的主键，这种数据存储方式也被称为聚簇索引。由此可见，聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。聚簇索引的叶子节点都包含主键值、事务 ID、用于事务 MVCC 的回滚指针以及所有的剩余列。InnoDB存储引擎数据和索引在一起假设我们有一张表student 那么在对应的数据库文件当中有出现这个student.frm和这个student.ibd(数据和索引文件）。
在这里插入图片描述

MyISAM 中的索引详解
MyISAM 存储引擎的索引文件和数据文件是分开的，MyISAM 引擎按照数据插入顺序，将数据文件存储在磁盘上，例如下图中 99 条记录从上到下依次存储。MyISAM 引擎使用 B+ 树作为索引结构，叶节点存放的是数据记录的行指针，图中为了方便阅读以行号代替。
在这里插入图片描述
在 MyISAM 引擎中，对主键列建立的主索引和对其他列建立的辅助索引在结构上没有区别，主键索引就是一个名为 Primary 的唯一非空索引。
总结一下，MyISAM 引擎中索引查询的步骤为，先按照 B+ 树查询到叶子节点，如果指定的键值存在，则取出其对应的行指针的值，然后通过行指针，读取相应数据行的记录。也就是说在MyISAM这个存储引擎下叶子节点存储的是这个数据的地址。在这里需要注意的是这个B+树的二级索引是不涉及这个回表操作的。二级索引和主键索引存储的都是这个数据的地址。

下面我们来看看这个辅助索引吧
辅助索引也叫非聚簇索引，二级索引等。同 MyISAM 引擎的辅助索引实现不同，InnoDB 的辅助索引，其叶子节点存储的不是行指针而是主键值，得到主键值再要查询具体行数据的话，要去聚簇索引中再查找一次，也叫回表。这样的策略优势是减少了当出现行移动或者数据页分裂时二级索引的维护工作。
在这里插入图片描述

哈西索引&&自适应哈西索引

哈希索引是一种基于哈希表的索引结构，它是一种需要精确匹配才生效的索引结构。
实现原理：对索引列计算哈希值把记录映射到哈希槽中，然后指向对应记录行的地址。因此，在查询的时候只要正确匹配到索引列，就能在O(1)的时间复杂度内查到记录。以下是一个哈希索引的示例，左边是哈希槽，右边是对应的数据列
在这里插入图片描述
那为什么这个Innodb这个存储引擎不使用这个哈西索引了？这个索引的要求是这个索引的效率要好
磁盘的IO花费要少。不选择它的原因

1.由哈西表当中的特性决定哈西表当中的元素没有任何顺序。如果用它那么只能进行等值比较，像这种模糊匹配就无法做到了和这种范围搜索和order by是都适合的
2.没有办法处理磁盘上的数据加载到内存当中构建这个高效的数据结构因为它没有办法减少磁盘IO次数

下面我们来聊一下这个自适应哈西索引

我们上面聊了这个Innodb存储引擎下我们使用这个普通索引需要再去这个主键索引树上再去找一遍。如果InnoDB存储引擎检测到同样的二级索引被不断使用那么它会根据这个二级索引，在内存上根据二级索引树(B+）上的二级索引值，在内存上构建一个哈西索引来加速搜索。（注意是等值比较，这是哈西索引的优势）。

在这里插入图片描述
从以上可以知道，哈希表查找最优情况下是查找一次.而InnoDB使用的是B+树，最优情况下的查找次数根据层数决定。因此为了提高查询效率，InnoDB便允许使用自适应哈希来提高性能。

可以通过参数 innodb_adaptive_hash_index 来决定是否开启。默认是打开的

mysql> show variables like "innodb_adaptive_hash_index"
    -> ;
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_adaptive_hash_index | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)

我们发现这个默认是打开的。存储引擎会自动对个索引页上的查询进行监控，如果能够通过使用自适应哈希索引来提高查询效率，其便会自动创建自适应哈希索引，不需要开发人员或运维人员进行任何设置操作。自适应哈希索引是对innodb的缓冲池的B+树页进行创建，不是对整张表创建，因此速度很快。

但是自适应哈西索引的维护也是需要耗费性能的，并不是说自适应哈西索引在任何时候都能提供性能，我们需要根据这个参数指标来具体分析是否需要打开或者关闭这个自适应哈西索引。自适应哈西索引内部也是又这个锁的如果很多的线程阻塞在这个自适应哈西分区的锁上。此时我们可以考虑这个关闭自适应哈西索引。我们可以使用这个命令查看

show engine status\G;

当1.RW-latch等待的线程数量（自适应哈西索引默认分配了8个分区）同一个分区等待的线程数量过多
2.走自适应哈西索引的频率低和二级索引的频率高。此时我们可以考虑关闭这个自适应哈西索引

索引和慢查询日志

MySQL可以设置慢查询日志，当SQL执行的时间超过我们设定的时间，那么这些SQL就会被记录在慢查询日志当中，然后我们通过查看日志，用explain分析这些SQL的执行计划，来判定为什么效率低下，是没有使用到索引？还是索引本身创建的有问题？或者是索引使用到了，但是由于表的数据量太大，花费的时间就是很长，那么此时我们可以把表分成n个小表，比如订单表按年份分成多个小表等。
慢查询日志相关的参数如下所示：

mysql> show variables like '%slow_query%';
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| slow_query_log      | ON                              |
| slow_query_log_file | /www/server/data/mysql-slow.log |
+---------------------+---------------------------------+
2 rows in set (0.00 sec)

慢查询日志记录了包含所有执行时间超过参数 long_query_time（单位：秒）所设置值的 SQL语句的日志，在MySQL上用命令可以查看，如下：

mysql> show variables like 'long%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 3.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

这个值是可以修改的可以根据我们的业务需求设置

mysql> set long_query_time =0.1;
Query OK, 0 rows affected (0.00 sec)

现在修改成超过1秒的SQL都会被记录在慢查询日志当中！可以设置为0.01秒，表示10毫秒。
慢查询日志，默认名称是host_name-slow.log。通过查询慢查询日志，发现项目运行过程中，上面这条SQL语句的执行时间超过了设定的慢查询时间，那么接下来就需要用explain分析一下该SQL的执行计划了，根据具体情况找出SQL和索引该怎么去优化。

索引优化

下面我们说一下这个索引优化，大致有那些优化
1.经常作为where条件过滤的字段考虑添加索引

select * from user where name='zhangsan';

像这个作为这个过滤条件的我们最好这个添加索引
2.字符串列创建索引时，尽量规定索引的长度，而不能让索引值的长度key_len过长
3.索引字段涉及类型强转、mysql函数调用、表达式计算等，索引就用不上了

select * from user where name=123;

此时name是一个字符串而这个123是个整数类型不匹配，此时需要强转就用不到索引了。

4.最左前缀原则
最左前缀原则指的是，查询从联合索引的最左列开始，并且不跳过索引中的列。如下：

select * from user where name=xx and city=xx ;

可以命中索引这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，如 city= xx and name ＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。

由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDER BY子句也遵循此规则。

一个追梦的少年

关注

10
点赞
踩
5

收藏

觉得还不错? 一键收藏
打赏
7
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫