天津综合网站建设商店,wap浏览器下载,空间信息网站,岳阳网站推广目录 一、了解Explain1.Explain介绍 二、Explain相关字段1.partitions2.filtered3.SHOW WARNINGS命令 三、Explain比较重要字段1.id2.select_type3.table4.type5.possible_keys6.key7.key_len8.ref9.rows10.Extra 四、索引优化实战#xff08;遵循原则#xff09;1.全值匹配2… 目录 一、了解Explain1.Explain介绍 二、Explain相关字段1.partitions2.filtered3.SHOW WARNINGS命令 三、Explain比较重要字段1.id2.select_type3.table4.type5.possible_keys6.key7.key_len8.ref9.rows10.Extra 四、索引优化实战遵循原则1.全值匹配2.最左前缀法则3.不在索引列上做任何操作计算、函数、自动or手动类型转换会导致索引失效而转向全表扫描4.存储引擎不能使用索引中范围条件右边的列5.尽量使用覆盖索引只访问索引的查询索引列包含查询列减少 select * 语句6.mysql在使用不等于或者not in not exists 的时候无法使用索引会导致全表扫描7.is null,is not null 一般情况下也无法使用索引8.like以通配符开头$abc...mysql索引失效会变成全表扫描操作9.字符串不加单引号索引失效10.少用or或in11.范围查询优化 本章内容都是mysql基于5.7版本讲解。 一、了解Explain
1.Explain介绍
EXPLAIN语句提供了有关MySQL如何执行语句的信息。EXPLAIN适用于SELECT、DELETE、INSERT、REPLACE和UPDATE语句。
使用EXPLAIN关键字可以模拟优化器执行SQL语句分析你的查询语句或是结构的性能瓶颈在select语句之前增加explain关键字MySQL会在查询上设置一个标记执行查询会返回执行计划的信息而不是执行这条SQL。 注意如果from中包含子查询仍会执行该子查询将结果放入临时表中
官方文档 二、Explain相关字段
首先创建了三张表如下 演员表 CREATE TABLE actor (id INT(11) NOT NULL,name VARCHAR(45) DEFAULT NULL,update_time DATETIME DEFAULT NULL,PRIMARY KEY (id)) ENGINEINNODB DEFAULT CHARSETutf8;这些表自己插入几条数据就行。
电影表增加了一个单值索引
CREATE TABLE film (id INT(11) NOT NULL AUTO_INCREMENT,name VARCHAR(10) DEFAULT NULL,PRIMARY KEY (id),KEY idx_name (name)) ENGINEINNODB DEFAULT CHARSETutf8;演员电影关联表增加了一个联合索引
CREATE TABLE film_actor (id INT(11) NOT NULL,film_id INT(11) NOT NULL COMMENT 电影表主键,actor_id INT(11) NOT NULL COMMENT 演员表主键,remark VARCHAR(255) DEFAULT NULL COMMENT 额外增加的字段,PRIMARY KEY (id),KEY idx_film_actor_id (film_id,actor_id)
) ENGINEINNODB DEFAULT CHARSETutf81.partitions
5.7版本执行explain时默认是有这个一列的但是更早之前的版本可能没有需要增加一个PARTITIONS 关键字。 就是我们这张表是否有分区大多数情况都是不用的用的话也是进行分库分表不做分区。没有分区的话就是NULL。可以忽略这个字段。
旧版本EXPLAIN PARTITIONS SELECT * FROM actor WHERE id 1;5.7之后版本EXPLAIN SELECT * FROM actor WHERE id 1;2.filtered
5.7版本执行explain时默认是有这个一列的但是更早之前的版本可能没有需要增加一个EXTENDED 关键字。
旧版本EXPLAIN EXTENDED SELECT * FROM actor WHERE id 1;5.7之后版本EXPLAIN SELECT * FROM actor WHERE id 1;filtered 列是一个半分比的值rows *filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数前一个表指 explain 中的id值比当前表id值小的表。
3.SHOW WARNINGS命令
EXPLAIN SELECT * FROM actor WHERE id 1;
SHOW WARNINGS;SHOW WARNINGS执行结果。
select 1 AS id,Li AS name,2023-09-04 19:02:55 AS update_time from org_47.actor where 1结果2即mysql对sql语句优化过后的一个结果这个结果可能不可以直接运行。
三、Explain比较重要字段
1.id
id列的编号是 select 的序列号有几个 select 就有几个id并且id的顺序是按 select 出现的顺序增长的。 id列越大执行优先级越高id相同则从上往下执行id为NULL最后执行。
2.select_type
表示对应行是简单还是复杂的查询。
simple简单查询。查询不包含子查询和unionprimary复杂查询中最外层的 selectsubquery包含在 select 中的子查询不在 from 子句中derived包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中也称为派生表derived的英文含义
》sql举例《
简单查询
EXPLAIN SELECT * FROM actor WHERE id 1包含DERIVED的查询 前提需要先关闭mysql5.7新特性对衍生表的合并优化。 未关闭之前
EXPLAIN SELECT (SELECT 1 FROM actor WHERE id 1) FROM (SELECT * FROM film WHERE id 1) der关闭mysql5.7新特性对衍生表的合并优化
SET SESSION optimizer_switchderived_mergeoff;EXPLAIN SELECT (SELECT 1 FROM actor WHERE id 1) FROM (SELECT * FROM film WHERE id 1) derid越大越先执行如果id值相等则按照行顺序执行。 从这个例子结合3.table看可以看出mysql先去查询film表中数据然后去子查询actor表最后进行衍生表临时表查询。
3.table
这一列表示 explain 的一行正在访问哪个表。 当 from 子句中有子查询时table列是 格式表示当前查询依赖 idN 的查询于是先执行 idN 的查 询。当有 union 时UNION RESULT 的 table 列的值为union1,21和2表示参与 union 的 select 行id。 例如上方例子中其中3依赖id中的3。
4.type
这一列表示关联类型或访问类型即MySQL决定如何查找表中的行查找数据行记录的大概范围。 依次从最优到最差分别为system const eq_ref ref range index ALL 一般来说得保证查询达到range级别最好达到ref。
NULLmysql能够在优化阶段分解查询语句在执行阶段用不着再访问表或索引。例如在索引列中选取最小值可 以单独查找索引来完成不需要在执行时访问表。这种情况比较少见 》sql举例《
EXPLAIN SELECT MIN(id) FROM film;这个例子中table列为null表示在优化阶段分解查询语句在执行阶段用不着再访问表或索引就能得到结果。
const, systemmysql能对查询的某部分进行优化并将其转化成一个常量可以看show warnings 的结果。用于 primary key 或 unique key 的所有列与常数比较时所以表最多有一个匹配行读取1次速度比较快。system是 const的特例表里只有一条元组匹配时为system。 》sql举例《
EXPLAIN SELECT * FROM (SELECT * FROM film WHERE id 1) tmp;
SHOW WARNINGS;/* select#1 */ SELECT 1 AS id,电影1 AS name FROM DUALeq_refprimary key 或 unique key 索引的所有部分被连接使用 最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了简单的 select 查询不会出现这种 type 》sql举例《
EXPLAIN SELECT * FROM film_actor LEFT JOIN film ON film_actor.film_id film.id关联查询时两张表的数据都要查询所以id的值相同按照第一行先执行然后第二行再执行的顺序。
ref相比 eq_ref不使用唯一索引而是使用普通索引二级索引或者唯一性索引的部分前缀索引要和某个值相比较可能会找到多个符合条件的行。 》sql举例1《
EXPLAIN SELECT * FROM film WHERE NAME film1;这个sql使用到了这张表的二级索引idx_name 》sql举例2《 关联表查询idx_film_actor_id是film_id和actor_id的联合索引这里使用到了film_actor的左边前缀film_id部分。
EXPLAIN SELECT film_id FROM film LEFT JOIN film_actor ON film.id film_actor.film_idrange范围扫描通常出现在 in(), between , ,, 等操作中。使用一个索引来检索给定范围的行。 》sql举例《
EXPLAIN SELECT * FROM actor WHERE id 1;index扫描全索引就能拿到结果一般是扫描某个二级索引这种扫描不会从索引树根节点开始快速查找而是直接对二级索引的叶子节点遍历和扫描速度还是比较慢的这种查询一般为使用覆盖索引二级索引一般比较小所以这种通常比ALL快一些。
这里来讲讲覆盖索引 假设我们有一张表user_info有三列分别为idnamesex。增加一个单值索引KEY idx_name (name)。
CREATE TABLE user_info (id int(11) NOT NULL AUTO_INCREMENT,name varchar(255) DEFAULT NULL COMMENT 姓名,sex varchar(2) DEFAULT NULL COMMENT 性别,PRIMARY KEY (id),KEY idx_name (name)
) ENGINEInnoDB DEFAULT CHARSETutf8表数据
按照我们第一个博客中讲到的此时主键索引和二级索引对应的B树数据结构长这样。
主键索引
二级索引 sql语句
EXPLAIN SELECT id,NAME FROM user_info在有name这列的索引时我们只查询name和id时。在二级索引的数据结构中我们可以看到叶子节点是完全满足我们查询的内容的而且相比于主键索引主键索引的叶子节点的数据更多查询数据时需要把查询的叶子节点中的数据加载到内存主键索引占用空间更多二级数据少占用空间少。。所以mysql选择去二级索引里去查询。二级索引涵盖了要查询的列内容。走二级索引查询就可满足条件的这种方式即覆盖索引。覆盖索引不是一个索引而是一种方式。
ALL即全表扫描扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。
5.possible_keys
这一列显示查询可能使用哪些索引来查找。 explain 时可能出现 possible_keys 列有内容而 key 显示 NULL 的情况这种情况是因为表中数据不多mysql认为索引 对此查询帮助不大选择了全表查询。 如果该列是NULL则没有相关的索引。在这种情况下可以通过检查 where 子句看是否可以创造一个适当的索引来提 高查询性能然后用 explain 查看效果。 请结合上边几个例子进行理解。
6.key
这一列显示mysql实际采用哪个索引来优化对该表的访问。 如果没有使用索引则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引在查询中使用 force index、ignore index。 请结合上边几个例子进行理解。
7.key_len
这一列显示了mysql在索引里使用的字节数通过这个值可以算出具体使用了索引中的哪些列。 》sql举例《
EXPLAIN SELECT * FROM film_actor WHERE film_id 999;通过key列我们知道这个查询使用到了idx_film_actor_id索引这个索引是一个联合索引film_id, actor_id两个列。 都为int类型一个int占4个字节两个则为448个字节。 通过key_len列为4我们知道它这个查询只用到了部分索引也就是用到了film_id相关的索引为什么是film_id而不是actor_id呢这里结合上一博客的最左原理就不难知道联合索引的优先级是先film_id在actor_id结合在一起排序的如果要在联合索引中查询actor_id那么必须保证film_id是有顺序的因为是actor_id是在film_id的基础上有序的。在我们不知道ken_len时通过最左原理也能推断出来有了ken_len就更加方便了。
EXPLAIN SELECT * FROM film_actor WHERE film_id 999 AND actor_id 666;这个例子就表示所有字段都用上了索引。
key_len计算规则如下
字符串char(n)和varchar(n)5.0.3以后版本中n均代表字符数而不是字节数如果是utf-8一个数字或字母占1个字节一个汉字占3个字节 (1)char(n)如果存汉字长度就是 3n 字节 (2)varchar(n)如果存汉字则长度是 3n 2 字节加的2字节用来存储字符串长度因为varchar是变长字符串 (3)varchar是变长字符串数值类型 (1)tinyint1字节 (2)smallint2字节 (3)int4字节 (4)bigint8字节时间类型 (1)date3字节 (2)timestamp4字节 (3)datetime8字节
如果字段允许为 NULL需要1字节记录是否为 NULL 索引最大长度是768字节当字符串过长时mysql会做一个类似左前缀索引的处理将前半部分的字符提取出来做索引。
8.ref
这一列显示了在key列记录的索引中表查找值所用到的列或常量常见的有const常量字段名表名 . 列名。
EXPLAIN SELECT film_id FROM film LEFT JOIN film_actor ON film.id film_actor.film_id;9.rows
这一列是mysql估计要读取并检测的行数注意这个不是结果集里的行数。
10.Extra
这一列展示的是额外信息。常见的重要值如下
Using index使用覆盖索引。
EXPLAIN SELECT film_id FROM film_actor WHERE film_id 1;Using where使用 where 语句来处理结果并且查询的列未被索引覆盖。
EXPLAIN SELECT * FROM actor WHERE NAME qqqq;Using index condition查询的列不完全被索引覆盖where条件中是一个前导列的范围。
EXPLAIN SELECT * FROM film_actor WHERE film_id 1;Using temporarymysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的首先是想到用索引来优化。
EXPLAIN SELECT DISTINCT NAME FROM actor给改变name增加一个索引就可以解决这个问题。 之前举过的例子film表中的name是加过索引的。
EXPLAIN SELECT DISTINCT NAME FROM filmUsing filesort将用外部排序而不是索引排序数据较小时在内存排序否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。后续会详细讲解
EXPLAIN SELECT * FROM actor ORDER BY NAME;EXPLAIN SELECT * FROM film ORDER BY NAME;Select tables optimized away使用某些聚合函数比如 max、min来访问存在索引的某个字段时。 四、索引优化实战遵循原则
创建表
CREATE TABLE employees (id int(11) NOT NULL AUTO_INCREMENT,name varchar(24) NOT NULL DEFAULT COMMENT 姓名,age int(11) NOT NULL DEFAULT 0 COMMENT 年龄,position varchar(20) NOT NULL DEFAULT COMMENT 职位,hire_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 入职时间,PRIMARY KEY (id),KEY idx_name_age_position (name,age,position) USING BTREE
) ENGINEInnoDB AUTO_INCREMENT4 DEFAULT CHARSETutf8 COMMENT员工记录表INSERT INTO employees(NAME,age,POSITION,hire_time) VALUES(LiHua,22,manager,NOW());
INSERT INTO employees(NAME,age,POSITION,hire_time) VALUES(HanLei,23,dev,NOW());
INSERT INTO employees(NAME,age,POSITION,hire_time) VALUES(Lucy,23,dev,NOW());1.全值匹配
条件中的字段要完全匹配
》sql举例《
EXPLAIN SELECT * FROM employees WHERE NAME LiLei;这个key_len是怎么来的name是varchar24。通过上边的公式3n2计算3*24274
EXPLAIN SELECT * FROM employees WHERE LEFT(NAME,3) LiLei;不了解left函数的他接受两个参数分别为Leftstrlenstr为你要截取的字符串len为从左开始截取几位。
SELECT LEFT(LiLei,3) AS 结果2.最左前缀法则
如果索引了多列要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。 之前内容也详细讲过了这里只举例子。
EXPLAIN SELECT * FROM employees WHERE NAME Bill AND age 31;EXPLAIN SELECT * FROM employees WHERE age 30 AND POSITION dev;EXPLAIN SELECT * FROM employees WHERE POSITION manager;3.不在索引列上做任何操作计算、函数、自动or手动类型转换会导致索引失效而转向全表扫描
》sql举例《 添加索引
ALTER TABLE employees ADD INDEX idx_hire_time (hire_time) USING BTREE ;EXPLAIN select * from employees where date(hire_time) 2018‐09‐30;DATE日期函数只提取日期的年月日。
SELECT DATE(2018-09-30 :00:00:00) AS 日期为什么改成年月日查询就不走索引呢 原因因为索引树里边保存的数据是年月日时分秒保存的单独年月日是对应不上的是没有办法完全匹配上的导致无法走索引。 优化思路
EXPLAIN SELECT * FROM employees WHERE hire_time 2018-09-30 00:00:00
AND hire_time 2018-09-30 23:59:59演示完毕删除索引
ALTER TABLE employees DROP INDEX idx_hire_time4.存储引擎不能使用索引中范围条件右边的列
》sql举例《
EXPLAIN SELECT * FROM employees WHERE NAME LiLei AND age 22 AND POSITION manager;通过ken_len可以看出都三个条件都走索引了。 》sql举例《
EXPLAIN SELECT * FROM employees WHERE NAME LiLei AND age 22 AND POSITION manager;通过key_len可以看出只用到了索引中name和age字段position字段没有走索引。 为什么会是这样呢 首先在name确定的情况下age相对于name是有序的position是有序的。 但是第二条件是age大于的情况即在age不确定的情况下position不一定是有序的。但是age相对于确定的name下肯定是有序的。索引age能走索引但是position是不可以的。 也就是说在name‘LiLei’ AND age 22 范围下position不是连续出现的即不是有序的。可能age23下有一部分内容age24下没有然后age30下又有了。这总情况下需要在这个范围扫描一下数据看那些符合POSITION ‘manager’。
5.尽量使用覆盖索引只访问索引的查询索引列包含查询列减少 select * 语句
覆盖索引上边已经讲过了为什么不推荐使用select * 是因为你要走覆盖索引那索引中的叶子节点不是保存的所有列数据可能就是某几列的索引所以尽量查询贴近二级索引相关数据。 》sql举例《
EXPLAIN SELECT NAME,age FROM employees WHERE NAME LiLei AND age 23 AND POSITION manager;EXPLAIN SELECT * FROM employees WHERE NAME LiLei AND age 23 AND POSITION manager;6.mysql在使用不等于或者not in not exists 的时候无法使用索引会导致全表扫描 小于、 大于、 、 这些mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引
EXPLAIN SELECT * FROM employees WHERE NAME ! LiHua;通过key列可以看出没有走索引为啥还有这种情况呢 因为mysql走不走索引是有一套计算逻辑的它可能认为你直接扫描全表可能比索引要快一般情况表数据少的话可能有种情况。
7.is null,is not null 一般情况下也无法使用索引
EXPLAIN SELECT * FROM employees WHERE NAME IS NULL8.like以通配符开头‘$abc…’mysql索引失效会变成全表扫描操作
EXPLAIN SELECT * FROM employees WHERE NAME LIKE %LiEXPLAIN SELECT * FROM employees WHERE NAME LIKE Li%结合B树的结构不难看出前百分号即后缀包含Li的字符串这个范围中不一定在B树是有序的没有办法走索引。 但是后百分号前边Li是固定那同样都是Li开头的数据在B树保存的肯定是有序的所以是能走索引的。
那如果我就需要用到前百分号怎么能优化一下呢那就尽量让他走覆盖索引。
EXPLAIN SELECT NAME,age,POSITION FROM employees WHERE NAME LIKE %Li把type为ALL变成了index。
like的后百分号匹配相当于常量查询查询。
like KK%相当于常量%KK和%KK% 相当于范围
9.字符串不加单引号索引失效
EXPLAIN SELECT * FROM employees WHERE NAME 1000;EXPLAIN SELECT * FROM employees WHERE NAME 1000;10.少用or或in
用它查询时mysql不一定使用索引mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引详见后续博客范围查询优化内容。
EXPLAIN SELECT * FROM employees WHERE NAME LiHua OR NAME HanLei;11.范围查询优化
添加一个索引
ALTER TABLE employees ADD INDEX idx_age (age) USING BTREE ;EXPLAIN SELECT * FROM employees WHERE age 1 AND age 2000;没走索引原因mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。比如这个例子可能是 由于单次数据量查询过大导致优化器最终选择不走索引 优化方法可以将大的范围拆分成多个小范围。
EXPLAIN SELECT * FROM employees WHERE age 1 AND age 1000;EXPLAIN SELECT * FROM employees WHERE age 1001 AND age 2000;为什么第一条没走但是第二条走了呢 mysql其实自己有一套怎么查询的逻辑会对每种方案进行一个评分最终按照分数高的方案来执行这种情况即选择全表扫描还是走索引查询。评分相关内容在后边博客中会讲到这里先引出来一下这块逻辑。
最后去掉索引。
ALTER TABLE employees DROP INDEX idx_age;
