理解MySQL索引和执行计划分析

最新推荐文章于 2022-10-04 00:58:05 发布
最新推荐文章于 2022-10-04 00:58:05 发布
阅读量124 收藏
点赞数
分类专栏： MySQL之路 文章标签： mysql 索引
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zhen1819/article/details/110955745
版权

一、索引的作用

通常，对MySQL数据库中数据的读取操作要多于写入操作，索引的作用就是为了优化查询，提升查询速度。

二、索引的分类

索引一般包括：BTree、HASH、RTree、Fulltext。

三、BTree索引结构分类

BTree包括：B-Tree、B+Tree、B*Tree。其中B+Tree和B*Tree的使用较多。

四、BTree种类

4.1、聚集索引

聚集索引（cluster index），也叫主键索引。在生成聚集索引时，会选择主键作为聚集索引列（主键一般在建表时加入）。如果没有主键，会自动选择唯一键。
聚集索引的结构：按照聚集索引列的值的顺序，顺序存储数据页，作为叶节点。根节点和枝节点只存储下层的最小值和指针。

4.2、普通二级索引

也叫辅助索引，通常和聚集索引一起使用，作为聚集索引的辅助。
创建辅助索引时，选择表中的某个列作为索引键。然后将整个列的值提取出来，做排序。将排序后的值，均匀的分布到BTree索引的叶节点中，进而生成枝节点，最终生成根节点。注意：应当尽量控制索引树的高度，来减少索引树遍历的次数。叶节点同时会存储原表的数据行的指针，通过指针可以找到行中其他列的数据，这个动作称为回表查询，伴随着随机IO。
如果同时有聚集索引，则叶节点保存指向该值对应的主键的指针，通过该指针根据聚集索引直接查找对应的数据，而不必进行回表查询。

4.3、覆盖索引

覆盖索引，也称为联合索引。
通常根据业务需求，将需要频繁查询的列，联合起来建立BTree，大量减少回表查询，从而减少随机IO。

4.4、唯一索引

唯一索引（unique index）：列中的值必须是唯一、不重复的。

索引树的高度

索引本身也会占用磁盘空间，取决于数据行的多少和作为索引的列的值的长度，行数越多、值的长度越长，索引树的高度也越高。因为在MySQL中，一个数据页的长度为16K，存储的数据有限，在数据量大、值越长时，将会使用更多的数据页。
数据行数过多时，可以通过拆分表或者分布式来解决；
作为索引的列的值的长度太长时，通常会采用前缀索引。

五、索引的基本管理

5.1、创建索引

创建单列普通索引：

语法：
ALTER TABLE `表名` ADD INDEX 索引名称(列名称);
例如：
ALTER TABLE students ADD INDEX cardid_idx(cardid);

创建覆盖索引：

语法：
ALTER TABLE `表名` ADD INDEX 索引名称(列名,列名,...);
例如：
ALTER TABLE students ADD INDEX idx_cid_n(name,cardid);

创建前缀索引：
在作为索引列的值的长度较大时，考虑使用前缀索引，如下，只取“name”字段的前10个字符：

ALTER TABLE students ADD INDEX idx_name(name(10));

创建唯一索引：
首先判断列中是否有重复的值，可以使用以下语句来判断，判断“name”字段是否有重复的值：

SELECT name,COUNT(name) AS cname FROM students GROUP BY name HAVING cname>1;

选择没有重复值的列创建唯一索引：

ALTER TABLE students ADD UNIQUE INDEX idx_cid(cardid);

5.2、查看索引

语法：
SHOW INDEX FROM `表名`;
例如：
SHOW INDEX FROM students;

5.3、删除索引

语法：
ALTER TABLE `表名` DROP INDEX 索引名称;
例如：
ALTER TABLE students DROP INDEX cardid_idx;

六、执行计划分析

创建将要用到的表：

CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `cardid` int(18) DEFAULT NULL,
  `chinese` int(3) DEFAULT NULL,
  `english` int(3) DEFAULT NULL,
  `mathematics` int(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;
INSERT INTO `students` (`id`, `name`, `cardid`, `chinese`, `english`, `mathematics`) VALUES (1, '小王', 111111111, 67, 89, 90);
INSERT INTO `students` (`id`, `name`, `cardid`, `chinese`, `english`, `mathematics`) VALUES (2, '小赵', 222222222, 66, 77, 88);
INSERT INTO `students` (`id`, `name`, `cardid`, `chinese`, `english`, `mathematics`) VALUES (3, '小李', 333333333, 65, 79, 83);
INSERT INTO `students` (`id`, `name`, `cardid`, `chinese`, `english`, `mathematics`) VALUES (4, '小王', 444444444, 91, 89, 54);
INSERT INTO `students` (`id`, `name`, `cardid`, `chinese`, `english`, `mathematics`) VALUES (5, '小张', 555555555, 78, 80, 74);

6.1、使用EXPLAIN执行计划分析

通常使用“EXPLAIN”或“DESCRIBE”来进行执行计划分析：

mysql> EXPLAIN SELECT * FROM students;
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | students | ALL  | NULL          | NULL | NULL    | NULL |    4 | NULL  |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
  • select_type：指明是单表查询还是多表查询；
  • table：查询操作的表；
  • type：索引类型
  • possible_keys：可能会用到的索引；
  • key：实际使用的索引；
  • Extra：额外信息。

6.2、索引类型

  • ALL：全表扫描
mysql> EXPLAIN SELECT * FROM students;
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | students | ALL  | NULL          | NULL | NULL    | NULL |    4 | NULL  |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
  • index：全索引扫描
mysql> EXPLAIN SELECT id FROM students;
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | students | index | NULL          | PRIMARY | 4       | NULL |    4 | Using index |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
  • range：范围索引扫描
    通常在SELECT范围查询中，使用<，>，<=，>=，IN，OR，BETWEEN..ON..操作符时出现：
mysql> EXPLAIN SELECT * FROM students WHERE id<3;
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | students | range | PRIMARY       | PRIMARY | 4       | NULL |    2 | Using where |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
  • ref：辅助索引的等值查询
mysql> ALTER TABLE students ADD INDEX idx_cid(cardid);        #使用“cardid”列创建辅助索引
mysql> EXPLAIN SELECT * FROM students WHERE cardid=111111111;
+----+-------------+----------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table    | type | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+----------+------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | students | ref  | idx_cid       | idx_cid | 5       | const |    1 | NULL  |
+----+-------------+----------+------+---------------+---------+---------+-------+------+-------+
  • system/const：主键或唯一键的等值查询
mysql> EXPLAIN SELECT * FROM students WHERE id=3;
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | students | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
  • NULL：索引中不包含查询的值
mysql> EXPLAIN SELECT * FROM students WHERE id=1000;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+

6.3、额外信息Extra

看下面的查询语句：

mysql> EXPLAIN SELECT * FROM students WHERE name="小王" ORDER BY cardid;
+----+-------------+----------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+----------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | students | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where; Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+-----------------------------+

在"Extra"中出现了“Using filesort”信息。说明在进行查询时，会对数据进行排序，这将会消耗CPU时间，在数据量大的表中，将会影响查询速度。
在创建索引时，会对数据进行排序，所以考虑将WHERE和ORDER查询的列作为联合索引列，这样可以减少查询时的排序。因为在执行查询语句时，WHERE条件的优先级是高于ORDER优先级的，所以这里将“name”列作为第一索引列：

mysql> ALTER TABLE students ADD INDEX idx_cid_n(name,cardid);
mysql> EXPLAIN SELECT * FROM students WHERE name="小王" ORDER BY cardid;
+----+-------------+----------+------+---------------+-----------+---------+-------+------+-------------+
| id | select_type | table    | type | possible_keys | key       | key_len | ref   | rows | Extra       |
+----+-------------+----------+------+---------------+-----------+---------+-------+------+-------------+
|  1 | SIMPLE      | students | ref  | idx_cid_n     | idx_cid_n | 767     | const |    2 | Using where |
+----+-------------+----------+------+---------------+-----------+---------+-------+------+-------------+

七、索引的应用规范

  • 建表时一定要有主键；
  • 选择唯一性索引；同一个表中不能有同名索引；
  • 为经常需要排序、分组、联合操作的字段建立索引，可以大大提高查询效率；
  • 为经常需要作为WHERE查询条件的字段建立索引；
  • 如果作为索引的列的值很长，尽量使用前缀索引；
  • 索引的条目不能过多，不使用的索引要删除；
  • 索引的维护要避开业务高峰期。因为创建索引时会对数据进行排序，数据量很大时，将会占用较多的CPU资源。
「已注销」
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL 执行计划分析（EXPLAIN） 和 索引分析
Yi_Ran_2018的博客
04-06 313
一. 执行计划分析（EXPLAIN） 1.id 每个SELECT语句都会自动分配的一个唯一标识符。 2.select_type simple：简单select查询，不需要union操作或者不包含子查询。 primary：一个需要union操作或者含有子查询的select。 union：union连接的两个select查询。 dependent union: 与union一样，出现在union 或uni...
MySQL执行计划的深入分析
09-09
MySQL执行计划是数据库管理员和...总之，深入理解MySQL执行计划能帮助我们更好地诊断和解决性能问题，从而提高数据库的整体效率。在日常工作中，定期分析执行计划并进行相应的优化，是确保数据库高效运行的重要实践。
mysql索引和explain执行计划分析
aryoyo的博客
06-12 244
mysql索引：MyISAM引擎使用B+Tree作为索引结构，和DB2的索引一样，叶节点的data域存放的是数据记录的地址，InnoDB的引擎的cluster index叶子节点存放的就是数据本身，如果表没有primary key或者unique  key，那么InnoDB默认会以隐藏字段DB_ROW_ID作为key创建cluster index，二级索引的叶子节点则存放的是主键的值.疑惑：clu...
mysql---索引与执行计划
程序yuan同学
11-01 464
文章目录索引入门什么是索引MySQL中的索引索引的分类基础语法执行语法什么是执行语法执行计划的作用执行计划的语法执行计划详解 索引入门 什么是索引 MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。 可以得到索引的本质：索引是数据结构。 上面的理解比较抽象，举一个例子，平时看任何一本书，首先看到的都是目录，通过目录去查询书籍里面的内容会非常的迅速。 上图就是一本金瓶梅的书，书籍的目录是按顺序放置的，有第一节，第二节它本身就是一种顺序存放的数据结构，是一种顺序结构。 另
mysql explain 索引执行分析
仅做个人笔记
06-17 212
转自：https://www.cnblogs.com/tufujie/p/9413852.html 仅作个人备份，浏览请看原文 在日常工作中，我们会有时会开慢查询去记录一些执行时间比较久的SQL语句，找出这些SQL语句并不意味着完事了，些时我们常常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。所以我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及.
MySql（二）索引与执行计划
你能找到这里，说明你有经天纬地之才，扭转乾坤只能，鬼神不测之计包藏宇宙之志，真乃乱世之枭雄，治世之能臣也
05-15 501
什么是索引： 在关系型数据库种，索引是一种单独的、物理的对数据库表种一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值得集合和相应的指向表中物理标识，这些值的数据页的逻辑指针清单。 （什么意思：索引的作用相当于图书的目录，可以根据目录中页码快速找到所需要的内容） 索引的分类： 1、普通索引：即一个索引只包含单个列，一个表可以有多个单列索引； 2、唯一索引：索引列的值必须唯一，但允许有空值，并且多个空值； 3、主键索引：与唯一索引之间的区别就在于不允许有空值，创建主键时会自动创建此索
mysql索引对排序的影响实例分析
09-09
在本实例分析中，我们将深入探讨MySQL索引如何影响排序速度，通过具体的操作步骤和测试来展示其效果。 首先，创建一个名为`t15`的测试表，包含`id`、`cat_id`、`price`和`name`四个字段，其中`id`是主键。接着，...
MySQL索引用法实例分析
09-10
总结，理解并正确使用MySQL索引及其相关的查询优化策略是提升数据库性能的关键。合理地创建复合索引、避免 `IN` 和 `OR` 引起的全表扫描，以及掌握 `UNION` 和 `UNION ALL` 的正确用法，都能有效地提高查询效率，...
mysql执行计划介绍
09-10
MySQL执行计划是数据库管理系统在处理SQL查询时的预估策略，它是分析和优化SQL语句性能的关键工具。通过查看执行计划，我们可以了解MySQL如何解析、优化以及执行SQL语句，从而找出潜在的性能瓶颈，提升数据库性能。 ...
MYSQL explain 执行计划
09-11
MySQL的`EXPLAIN`命令是数据库管理员和开发者用于分析SQL查询执行计划的重要工具。通过`EXPLAIN`，我们可以深入理解MySQL如何处理`SELECT`语句，优化查询性能，特别是涉及多表连接的情况。以下是`EXPLAIN`输出的各列...
索引和执行计划——《mongoDB 从入门到内卷再到入土》
qq_46207392的博客
08-02 110
内卷能让一个群体的内部变得异常的精致，但是本质上却完完全全没有任何的进步，并且会导致更多没必要的资源浪费以及带来更加激烈的竞争。它是zbzy社会下劳动力过剩的必然现象。 索引 索引类型 MongoDB索引使用B-Tree数据结构 单字段索引 MongoDB支持在文档的单个字段上创建用户定义的升序/降序索引，称为单字段索引（Single Field Index） 对于单个字段索引和排序操作，索引键的排序顺序（即升序或降序）并不重要，因为MongoDB可以在任何方向上遍历索引 复合索引 MongoDB还.
【Oracle性能优化一】执行计划与索引类型分析
weixin_30825581的博客
08-06 282
一条sql的好坏，主要来源两个方面： 1、 从数据库层面：取决于优化器所采用的数据访问方式和数据处理的方式决定 2、从业务方面来讲：这条sql在业务上是不是一条好的sql 我们以oracle 11g为例子进行分析。 一、数据的访问方式 【没有索引】 如果一张表没有建立索引，那么优化器采用的数据访问方式也会截然不同，这就取决于oracle的数据访问方式，下边列举两种： 1、并行访问 2、多数据...
索引大小对语句执行速度的影响
weixin_30381317的博客
06-28 110
先看下面这条语句：mysql> select count(*) from search_hash;+----------+| count(*) |+----------+| 1665476 |+----------+1 row in set (0.53 sec) 哇，执行的速度非常快，100多万行只用了不到0.5秒。explian分析一下执行计划。mysql> explain...
九、索引与执行计划、索引的分类
one大白(●—●)的博客
03-04 177
文章标题索引入门生活中的索引MySql 中的索引谈下 B+Tree二分查找二叉树(Binary Tree)平衡二叉树（AVL-树）平衡二叉树的遍历平衡二叉树的旋转B+树B+树的定义B+树的作用B+树的插入操作索引的分类基础语法 上篇-mysql gap锁与慢查询 索引入门 ###索引是什么 生活中的索引 MySQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构。 可以得到索引的本质：索引是数据结构。 上面的理解比较抽象，举一个例子，平时看任何一本书，首先看到的都是目录，通
基础优化-索引及执行计划（七）
董坤的博客
10-04 491
基础优化-索引及执行计划
mysql性能优化系列7-索引与执行计划
哼哼的博客
05-20 375
1. 索引 1.1 索引分类 （1）主键索引 根据主键建立索引，不允许重复，不允许空值 （2）唯一索引 索引的列值必须唯一，允许空值 （3）普通索引 普通列构建的索引，无限制 （4）全文索引 用大文本对象的列构建的索引 （5）组合索引 多个列组合构建索引，每个列不允许有空值。组合索引遵循最左前缀原则 1.2 实现原理 MySQL数据库支持多种索引类型。 （1）哈希索引 只有memory存储引擎支持...
MySQL— 索引
weixin_30672019的博客
06-12 5051
目录 一、索引 二、索引类型 三、索引种类 四、操作索引 五、创建索引的时机 六、命中索引 七、其它注意事项 八、LIMIT分页 九、执行计划 十、慢查询日志 一、索引 MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。 打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MyS...
Oracle执行计划中的索引
iteye_6274的博客
05-19 283
Oracle执行计划根据索引的类型与where限制条件的不同，有5种类型的索引扫描，分别是：索引唯一扫描（index unique scan），索引范围扫描（index range scan），索引全扫描（index full scan），索引快速扫描（index fast full scan）索引跳跃式扫描 (index skip scan)。 1、索引唯一扫描（index unique sca...
MySQL的索引的使用与执行计划分析
kangswx的博客
07-04 419
数据表中建立索引的目的： B+Tree索引：就是为表建立"目录"，索引的目的就是为了防止全表扫描，索引的存储形式是由存储引擎决定。 数据表中索引分类： 从存储结构上来划分：BTree索引(B-Tree或B+Tree索引)，Hash索引、full-index全文索引、R-Tree索引。 从应用分层类划分：普通索引、唯一索引、复合索引 从数据的物理顺序与键值的逻辑(索引)顺序关系：聚集索...
mysql索引及执行计划
最新发布
08-15
MySQL索引是一种数据结构，用于提高数据库查询的速度。它通过创建一个有序的数据结构来存储表中的某一列或多列的值，并通过快速查找和访问来加快查询操作。 执行计划是MySQL查询优化器生成的一个指令序列，用于执行...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值