mysql(三): explain，索引优化

最新推荐文章于 2022-04-03 19:49:37 发布
最新推荐文章于 2022-04-03 19:49:37 发布
阅读量180 收藏
点赞数
分类专栏： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_35688140/article/details/101987407
版权

1.MySQL性能优化之查看执行计划explain

1.1 介绍explain

  • MySQL 提供了一个 EXPLAIN 命令, 它可以对 SELECT 语句进行分析, 并输出 SELECT 执行的详细信息, 以供开发人员针对性优化.

  • 使用explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。

  • EXPLAIN 命令用法十分简单, 在 SELECT 语句前加上 explain 就可以了, 例如:
    在这里插入图片描述

1.2 参数说明

expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra,下面对这些字段进行解释：

  • id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
  • select_type: SELECT 查询的类型.
  • table: 查询的是哪个表
  • partitions: 匹配的分区
  • type: join 类型
  • possible_keys: 此次查询中可能选用的索引
  • key: 此次查询中确切使用到的索引.
  • ref: 哪个字段或常数与 key 一起被使用
  • rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
  • filtered: 表示此查询条件所过滤的数据的百分比
  • extra: 额外的信息

1.3 select_type列说明

  • SIMPLE, 表示此查询不包含 UNION 查询或子查询
  • PRIMARY, 表示此查询是最外层的查询
  • UNION, 表示此查询是 UNION 的第二或随后的查询
  • DEPENDENT UNION, UNION 中的第二个或后面的查询语句, 取决于外面的查询
  • UNION RESULT, UNION 的结果
  • SUBQUERY, 子查询中的第一个 SELECT
  • DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果.

1.4 type列说明

通常来说, 不同的 type 类型的性能关系如下:
ALL < index < range ~ index_merge < ref < eq_ref < const < system

类型含义
system表只有一行
const表最多只有一行匹配，通用用于主键或者唯一索引比较时
eq_ref每次与之前的表合并行都只在该表读取一行，这是除了system，const之外最好的一种，特点是使用=，而且索引的所有部分都参与join且索引是主键或非空唯一键的索引
ref如果每次只匹配少数行，那就是比较好的一种，使用=或<=>，可以是左覆盖索引或非主键或非唯一键
fulltext全文搜索
ref_or_null与ref类似，但包括NULL
index_merge表示出现了索引合并优化(包括交集，并集以及交集之间的并集)，但不包括跨表和全文索引。这个比较复杂，目前的理解是合并单表的范围索引扫描（如果成本估算比普通的range要更优的话
unique_subquery在in子查询中，就是value in (select…)把形如“select unique_key_column”的子查询替换。PS：所以不一定in子句中使用子查询就是低效的！
index_subquery同上，但把形如”select non_unique_key_column“的子查询替换
range常数值的范围
index(a).当查询是索引覆盖的，即所有数据均可从索引树获取的时候（Extra中有Using Index）；(b).以索引顺序从索引中查找数据行的全表扫描（无 Using Index）；©.如果Extra中Using Index与Using Where同时出现的话，则是利用索引查找键值的意思；(d).如单独出现，则是用读索引来代替读行，但不用于查找
all全表扫描

2.索引优化

本文选用MySQL官方文档中提供的示例数据库之一：employees。这个数据库关系复杂度适中，且数据量较大。下图是这个数据库的E-R关系图（引用自MySQL官方手册）：
在这里插入图片描述

2.1 最左前缀匹配

索引的最左前缀和和B+Tree中的“最左前缀原理”有关，举例来说就是如果设置了组合索引<col1,col2,col3>那么以下3中情况可以使用索引：col1，<col1,col2>，<col1,col2,col3>，其它的列，比如<col2,col3>，<col1,col3>，col2，col3等等都是不能使用索引的。

在titles表的主索引为<emp_no, title, from_date>，当查询条件精确匹配索引的左边连续一个或几个列时，如<emp_no>或<emp_no, title>，索引可以被用到，但是只能用到一部分，即条件所组成的最左前缀。上面的查询从分析结果看用到了PRIMARY索引，但是key_len为4，说明只用到了索引的第一列前缀。

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001';
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | titles | ref  | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+

2.2 查询条件用到了组合索引的某两列，但是中间某一个列未提供。

接着1，索引有三列，根据最左前最匹配也无法匹配到<emp_no,from_date>这两列，

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND from_date='1986-06-26';
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | titles | ref  | PRIMARY       | PRIMARY | 4       | const |    1 | Using where |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+

为了加快查询速度：
（1）补全三列，形成完整的索引
首先我们看下title一共有几种不同的值：

SELECT DISTINCT(title) FROM employees.titles;
+--------------------+
| title              |
+--------------------+
| Senior Engineer    |
| Staff              |
| Engineer           |
| Senior Staff       |
| Assistant Engineer |
| Technique Leader   |
| Manager            |
+--------------------+

只有7种。在这种成为“坑”的列值比较少的情况下，可以考虑用“IN”来填补这个“坑”从而形成最左前缀：

EXPLAIN SELECT * FROM employees.titles
WHERE emp_no='10001'
AND title IN ('Senior Engineer', 'Staff', 'Engineer', 'Senior Staff', 'Assistant Engineer', 'Technique Leader', 'Manager')
AND from_date='1986-06-26';
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | titles | range | PRIMARY       | PRIMARY | 59      | NULL |    7 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

（2）添加辅助索引
“填坑”后性能提升了一点。如果经过emp_no筛选后余下很多数据，则填坑以后查询的性能优势会更加明显。当然，如果title的值很多，用填坑就不合适了，必须建立辅助索引。像本文中就可以添加一个<emp_no,from_date>的辅助索引。

2.3 匹配某列的前缀字符串

此时可以用到索引,如果通配符%不出现在开头，则可以用到索引，但根据具体情况不同可能只会用其中一个前缀.

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title LIKE 'Senior%';
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | titles | range | PRIMARY       | PRIMARY | 56      | NULL |    1 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

2.4 带索引的模糊查询优化

在上面已经提到，使用LIKE进行模糊查询的时候，’%aaa%'不会使用索引，也就是索引会失效。如果是这种情况，只能使用全文索引来进行优化（上文有讲到）。

2.5 使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

2.6 范围查询

EXPLAIN SELECT * FROM employees.titles WHERE emp_no < '10010' and title='Senior Engineer';
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | titles | range | PRIMARY       | PRIMARY | 4       | NULL |   16 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引。同时，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。

EXPLAIN SELECT * FROM employees.titles
WHERE emp_no < '10010'
AND title='Senior Engineer'
AND from_date BETWEEN '1986-01-01' AND '1986-12-31';
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | titles | range | PRIMARY       | PRIMARY | 4       | NULL |   16 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

可以看到索引对第二个范围索引无能为力。这里特别要说明MySQL一个有意思的地方，那就是仅用explain可能无法区分范围索引和多值匹配，因为在type中这两者都显示为range。同时，用了“between”并不意味着就是范围查询，例如下面的查询：

EXPLAIN SELECT * FROM employees.titles
WHERE emp_no BETWEEN '10001' AND '10010'
AND title='Senior Engineer'
AND from_date BETWEEN '1986-01-01' AND '1986-12-31';
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | titles | range | PRIMARY       | PRIMARY | 59      | NULL |   16 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

看起来是用了两个范围查询，但作用于emp_no上的“BETWEEN”实际上相当于“IN”，也就是说emp_no实际是多值精确匹配。可以看到这个查询用到了索引全部三个列。因此在MySQL中要谨慎地区分多值匹配和范围匹配，否则会对MySQL的行为产生困惑。

3.索引的选择

索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值。
显然选择性的取值范围为(0, 1]，选择性越高的索引价值越大，这是由B+Tree的性质决定的。例如，上文用到的employees.titles表，如果title字段经常被单独查询，是否需要建索引，我们看一下它的选择性：title一共7种，但是数据集却有40多万行的数据。

SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;
+-------------+
| Selectivity |
+-------------+
|      0.0000 |
+-------------+

title的选择性不足0.0001（精确值为0.00001579），所以实在没有什么必要为其单独建索引。

4.选择前缀索引选取多长的前缀

employees表只有一个索引<emp_no>，那么如果我们想按名字搜索一个人，就只能全表扫描了：

EXPLAIN SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido';
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 300024 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

如果频繁按名字搜索员工，这样显然效率很低，因此我们可以考虑建索引。有两种选择，建<first_name>或<first_name, last_name>，看下两个索引的选择性：

SELECT count(DISTINCT(first_name))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.0042 |
+-------------+
SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.9313 |
+-------------+

<first_name>显然选择性太低，<first_name, last_name>选择性很好，但是first_name和last_name加起来长度为30，有没有兼顾长度和选择性的办法？可以考虑用first_name和last_name的前几个字符建立索引，例如<first_name, left(last_name, 3)>，看看其选择性：

SELECT count(DISTINCT(concat(first_name, left(last_name, 3))))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.7879 |
+-------------+

选择性还不错，但离0.9313还是有点距离，那么把last_name前缀加到4：

SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.9007 |
+-------------+

这时选择性已经很理想了，而这个索引的长度只有18，比<first_name, last_name>短了接近一半，我们把这个前缀索引 建上：

ALTER TABLE employees.employees
ADD INDEX `first_name_last_name4` (first_name, last_name(4));

此时再执行一遍按名字查询，比较分析一下与建索引前的结果：

SHOW PROFILES;
+----------+------------+---------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                           |
+----------+------------+---------------------------------------------------------------------------------+
|       87 | 0.11941700 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
|       90 | 0.00092400 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
+----------+------------+---------------------------------------------------------------------------------+

性能的提升是显著的，查询速度提高了120多倍。

前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于ORDER BY和GROUP BY操作，也不能用于Covering index（即当索引本身包含查询所需全部数据时，不再访问数据文件本身）。

5. 主键索引

如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置：
在这里插入图片描述
此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

因此，只要可以，请尽量在InnoDB上采用自增字段做主键。

参考文献：
https://blog.csdn.net/tongdanping/article/details/79878302
https://www.jianshu.com/p/f4f6b4776c7d

LUK流
关注
专栏目录
使用explain执行计划对SQL进行性能分析
wwwzhouzy的专栏
09-01 607
一、什么是执行计划 执行计划是用于计算数据库优化器处理SQL的资源成本，在MySQL中使用执行计划的命令是explain（也可以写为desc、describe），通过该命令可以查看SQL在真正执行时所需要经历的一系列过程，通过执行结果可以看出一条复杂的SQL查找顺序、是否有通过索引查询等信息。执行计划命令所带的SQL并不会真正执行，所以不会担心占用系统资源，用于对SQL进行分析优化非常有用。如下示例： mysql > EXPLAIN SELECT id,name FROM table; #ex
关于mysql 索引自动优化机制: 索引选择性（Cardinality：索引基数）
奋斗、
01-22 9852
关于mysql 索引自动优化机制: 索引选择性（Cardinality：索引基数）
mysql cardinality 索引选择
在路上
02-17 2995
官方文档的解释： An estimate of the number of unique values in the index. This is updated by running ANALYZE TABLE or myisamchk -a. Cardinality is counted based on statistics stored as integers, so the value
cbo优化中的基数(cardinality)介绍
我爱海
04-07 1430
CBO（基于代价的优化器）是RBO（基于规则的优化器）的替代品，从9i开始oracle就建议用户使用RBO来进行SQL的优化。CBO大概的优化原理很简单，他通过对象上的统计信息来计算各个执行计划的代价，然后选择代价较小的执行计划来运行。所以对于CBO来说对象（比如表，索引）上的统计信息就显得十分的重要，不仅要有统计信息，还要保证统计信息是准确的，不准确的统计信息可能会带来灾难性的结果。那么orac
尚硅谷mysql高级：索引、优化
08-12
5. 复合索引：当需要基于多列进行排序或过滤时，可以创建复合索引，索引顺序很重要，应根据查询条件的频率和选择性进行调整。 6. 空间索引：用于地理空间数据，如坐标位置。 二、MySQL优化 1. 查询优化：通过编写...
MySQL如何基于Explain关键字优化索引功能
12-14
explain显示了MySQL如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。简单讲，它的作用就是分析查询性能。 　explain关键字的使用方法很简单，就是把它放在select查询语句的...
MySQL性能优化秘籍：EXPLAIN深度解析与应用实战
06-26
MySQL开发通常指的是使用MySQL数据库进行的软件开发活动。MySQL是一个流行的开源关系型数据库管理系统（RDBMS），广泛用于Web开发和...6. **性能优化**：开发者需要了解如何优化查询性能，包括使用索引、优化SQL语句等
深入解析：使用EXPLAIN优化MySQL查询之旅
最新发布
07-17
### 深入解析：使用 EXPLAIN 优化 MySQL 查询之旅 #### 一、MySQL简介与特点 MySQL作为一款流行的开源关系型数据库管理系统(RDBMS)，因其强大的功能与灵活性，在Web应用程序开发领域占据着举足轻重的地位。它不仅...
MySQL技术大全：开发、优化与运维实战实例源代码.rar
07-03
- **索引与查询优化**：深入理解B树和哈希索引的工作原理，以及如何通过EXPLAIN分析和优化SQL查询性能。 2. **MySQL性能优化**： - **索引优化**：探讨不同类型的索引（如主键、唯一键、全文索引）及其在性能上的...
Cardinality指什么？
clt3617的博客
04-25 551
在性能优化向导中经常会出现Cardinality，Cardinality到底是指什么，看看doc的准确解释：Cardinality is the number of unique values in a column in re...
查询计划中集的势（Cardinality）的计算
欧辰的专栏
04-02 3001
当使用CBO模式的优化器时，oracle在生成查询计划时，会计算各个访问路径的代价，选择代价最小的访问路径作为查询计划。这个选择过程我们可以通过做一个10053的trace来观察。 在做代价估算时，有一个很重要的参数作为代价计算的因数，这就扫描字段的集的势（cardinality）。那么这个值是如何计算的呢？这个值的计算根据索引情况及查询条件不同而不同，因而它的计算也比较复杂。下面我们只讨论在使
Cardinality基数 Selectivity选择性 Density 密度
jnrjian的博客
04-03 1255
------Cardinality用同一个词，但是意义不同的， 和Selectivity对应的是num_distinct=num_rows/density 和density对应的是预估SQL返回行数 retrived_rows=num_rows/num_distinct。 Oracle在文档中，有时用基数来指一个操作返回的记录行数，有时也将基数指不重复的值的数量，因此基数与选择性在《SQL优化核心思想》中也有如下解释： 这个列基数用来考虑建index的 1、Cardinalit...
Mysql中explain用法和结果字段的含义介绍
阿飞云漫步
07-18 9215
使用 explain 查询和分析SQl的执行记录，可以进行sql的性能优化！ explain用法 mysql&gt; explain select * from students; +----+-------------+----------+------+---------------+------+---------+------+------+-------+ | id ...
Mysql索引之-cardinality
热门推荐
weigeshikebi的博客
05-09 3万+
我们前面介绍了Mysql索引，现在看一下，如何在系统运行中，查看索引的情况呢？查看索引show index from tb_name;
执行计划的cardinality基数（rows）评估
cuimiwan3140的博客
05-28 1376
执行计划的cardinality基数（rows）评估： 执行计划的cardinality列（基数）是说从数据表结果集或者索引返回多少行数据。 基数=表行数据*...
【sql调优】cardinality测试与简析
09-10 514
版本1024，9208 昨天一个puber的公司生产数据库有点问题，在执行一个过程的时候IO上到100%，后来找到这个过程中一段很差的sql，具体sql不给出了，把实际的表缩小了10倍，造了相似的数据来模拟。也趁此机会测试一些...
深入理解MySQL：Explain详解与优化
“mysql分享-explain讲解” MySQL是一个广泛使用的开源关系型数据库管理系统，...通过深入理解MySQL的存储引擎、`EXPLAIN`工具以及索引策略，你可以更有效地管理和优化你的数据库，解决慢查询问题，提升整体系统性能。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值