MySQL索引及执行计划
时间：2020-02-05 23:21:45
收藏：0
阅读：84
MySQL索引及执行计划
索引
合理的建立索引可以加快数据查询，例如，学校图书管为每一本书编号，根据编号可以快速锁定一本书所在位置。MySQL索引默认B+树索引。索引虽然能够提高检索效率，但同时也会降低更新的速度，因为
insert 、update、delete也会操作索引文件，会调整因为更新等操作带来的键值变化后的索引信息。
索引类型
- 主键索引：唯一索引，并且并指定为
primary key，每个表中只能有一个主键 - 唯一索引：索引列的所有值都只能出现一次，即值必须唯一，值可以为空
- 普通索引：基本的索引类型，值可以为空，没有唯一性限制
- 复合索引：一个索引中包含多个列
何时建立索引
- 主键自动创建唯一索引
- 频繁作为查询条件的字段适合创建索引
- 查询中与其他表关联的字段适合创建索引
- 频繁更新的字段不适合创建索引，因为在更新记录的同时会更新索引
- where条件中使用不到的字段不需要创建索引
- 在高并发下，在单值索引和复合索引中，复合索引更合适
- order by字段添加索引可以提高排序速度
不需要索引
- 表记录较少
- 经常增删改的表
- 数据重复且分布平均的表，如性别
执行计划
可以使用
explain关键字模拟优化器执行sql查询语句，从而分析查询语句或者表结构的性能瓶颈。通过查看执行计划，可以了解到表的读取顺序、数据读取操作、哪些索引可以使用、哪些索引被实际使用、表之间的引用、每张表中有多少行被优化器查询
explain select * from tz_order a left join tz_order_item b on b.order_number = a.order_number where b.order_number = 1146347329394184192| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | b | ALL | order_number | 7 | 14.29 | Using where | ||||
| 1 | SIMPLE | a | eq_ref | order_number_unique_ind | order_number_unique_ind | 152 | yami_shops.b.order_number | 1 | 100 |
id
表示实际操作中加载表的顺序，可以相同也可以不相同，如果id相同则从上往下依次加载执行，不相同则id越大对应的表越先加载。
select_type
- SIMPLE：简单的select查询，查询中不包含子查询或者union
- PRIMARY：查询中若包含任何复杂的子查询，最外层的最后加载的标记为此
- SUBQUERY：在select或者where中包含子查询
- DERIVED：在from列表中包含的子查询被标记为Derived（衍生），mysql会递归执行这些子查询，把结果放到临时表中
- UNION：若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层的select为标记为derived
- UNION RESULT：从union表中获取结果的select
type
- System：表中只有一条记录
- Const：表示通过索引一次就可以找到，用于主键索引或者唯一索引。因为只需要匹配一行，查询速度往往很快，mysql可以将该查询转换成一个常量
- Eq_ref：唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配，用于主键索引或者唯一索引
- Ref：非唯一索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，返回所有匹配某个单独值的行，可能找到多行。
- Rang：只检索给定范围的行，使用一个索引来选择行，一般where中使用了
<、>、in、between这种范围扫描。 - Index：index只遍历所引树，因为索引文件比数据文件小，所以读取索引文件比读全表快。
- All：全表扫描，从硬盘读取
possible_key
可能应用到这张表的索引，查询涉及到的字段上若存在索引，则列出，但是不一定会使用。
key
实际使用到的索引，如果是null，则没有使用索引，如果查询中使用了覆盖索引，则该索引仅会出现在key列表中
key_len
表示索引中使用的字节数，计算查询中索引的长度，在不损失精度的情况下，越小越好，这个值显示的是索引字段的最大可能长度，不是实际长度
ref
显示索引的哪一列被使用
rows
根据表统计信息以及索引选用情况，大致估算找到所需记录需要扫描的行数
extra
- Using filesort：mysql会对数据使用一个外部索引排序，而不是按照表内的索引进行读取，mysql中无法利用索引完成的排序操作叫做
文件排序 - Using temporary：使用了临时表保存中结果，mysql对查询结果排序使用了临时表。一般来说，是因为order by排序或者分组查询group by时候没有按照索引走。使用了临时表，更加严重影响效率。
- Using index：表示相应的select操作中使用到了覆盖索引，避免访问表的数据行。如果同时出现Using where，表示索引被用来执行索引值的查找，没有同时出现 Using where表示索引用来读取数据而非执行查找。
表驱动
select * from A where id in (select id from B)--> B的数据小于Aselect * from B where exists (select 1 from A where A.id = B.id)---> B的数据小于A
原文：https://www.cnblogs.com/QullLee/p/12266967.html
评论（0）