DEPENDENT SUBQUERY
/DEPENDENT UNION)const
/field_name
/func
)System Name | Color | Text on Visual Diagram | Tooltip Related Information |
---|---|---|---|
SYSTEM | Blue | Single row: system constant | Very low cost |
CONST | Blue | Single row: constant | Very low cost |
EQ_REF | Green | Unique Key Lookup | Low cost -- The optimizer is able to find an index that it can use to retrieve the required records. It is fast because the index search directly leads to the page with all the row data |
REF | Green | Non-Unique Key Lookup | Low-medium -- Low if the number of matching rows is small; higher as the number of rows increases |
FULLTEXT | Yellow | Fulltext Index Search | Specialized FULLTEXT search. Low -- for this specialized search requirement |
REF_OR_NULL | Green | Key Lookup + Fetch NULL Values | Low-medium -- if the number of matching rows is small; higher as the number of rows increases |
INDEX_MERGE | Green | Index Merge | Medium -- look for a better index selection in the query to improve performance |
UNIQUE_SUBQUERY | Orange | Unique Key Lookup into table of subquery | Low -- Used for efficient Subquery processing |
INDEX_SUBQUERY | Orange | Non-Unique Key Lookup into table of subquery | Low -- Used for efficient Subquery processing |
RANGE | Orange | Index Range Scan | Medium -- partial index scan |
INDEX | Red | Full Index Scan | High -- especially for large indexes |
ALL | Red | Full Table Scan | Very High -- very costly for large tables, but less of an impact for small ones. No usable indexes were found for the table, which forces the optimizer to search every row. This could also mean that the search range is so broad that the index would be useless. |
UNKNOWN | Black | unknown | Note: This is the default, in case a match cannot be determined |
CREATE TABLE `test_like` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键', `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '账号', `age` int NOT NULL DEFAULT '0' COMMENT '年龄', `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '邮箱' PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
测试数据
INSERT into test_like (id, name, age, email)values(1, 'aaa', 111, '1111'),(2, 'aaa', 111, '1111'),(3, 'aaa', 111, '1111'),(4, 'aaa', 111, '1111'),(5, 'aaa', 111, '1111');
-- 单索引 索引覆盖,通过索引idx_on_name的B+树的页节点,可以直接过滤到ALTER table test_like add key idx_on_name (`name`);
验证案例
explain select * from test_like where name like '11%';
单列索引,通配符在右侧,select * 会走索引,type=range检索性能好
explain select * from test_like where name like '%11%';
单列索引,通配符在左右两侧,select * 不会走索引,type=ALL检索性能差
explain select * from test_like where name like '%11';
单列索引,通配符在左侧,select * 不会走索引,type=ALL检索性能差
加了单列索引还不够,因为select 列 和 where 条件语句 两个变量,都可能导致sql执行效果走了全表扫描,性能非常差。想要性能过得去,得确保通配符在右侧。
单列索引,通配符在右侧,select [索引列] 会走索引,type=range检索性能好
explain select * from test_like where name like '%11%';
单列索引,通配符在右侧,select [索引列] 会走索引, type=index,检索性能和二级索引树的数据量相关;sql性能随着二级索引树节点数量变多,性能变差
explain select * from test_like where name like '%11';
单列索引,通配符在左侧,select [索引列] 会走索引, type=index,检索性能和二级索引树的数据量相关;sql性能随着二级索引树节点数量变多,性能变差
案例一的select * 性能优化,可以通过案例二的方法去优化:
我们对表的 name、age设置了联合索引
-- 联合索引 索引覆盖,通过联合索引idx_on_name_age的页节点,可以直接过滤到ALTER table test_like add key idx_on_name_age (`name`, `age`);
测试结果
explain select name from test_like where name like '11%';
联合索引,通配符在右侧,select [索引列] 会走索引,type=range检索性能好
explain select name from test_like where name like '%11%';
联合索引,通配符在两侧,select [索引列] 会走索引,type=index,检索性能随索引数据量变大而变差
explain select name from test_like where name like '%11';
联合索引,通配符在左侧,select [索引列] 会走索引,type=index,检索性能随索引数据量变大而变差
explain select age from test_like where name like '11%';
联合索引,通配符在右侧,select [索引列] 会走索引,type=range,检索性能好
explain select age from test_like where name like '%11%';
联合索引,通配符在两侧,select [索引列] 会走索引,type=ALL,检索性能差
explain select age from test_like where name like '%11';
联合索引,通配符在左侧,select [索引列] 会走索引,type=ALL,检索性能差
explain select name,age from test_like where name like '11%';
联合索引,通配符在右侧,select [索引列] 会走索引,type=range,检索性能好
explain select name,age from test_like where name like '%11%';
联合索引,通配符在两侧,select [索引列] 会走索引,type=ALL,检索性能差
explain select name,age from test_like where name like '%11';
联合索引,通配符在左侧,select [索引列] 会走索引,type=ALL,检索性能差
explain select name,age,email from test_like where name like '11%';
联合索引,通配符在右侧,select [索引列] 会走索引,type=range,检索性能好
explain select name,age,email from test_like where name like '%11%';
联合索引,通配符在两侧,select [索引列] 会走索引,type=ALL,检索性能差
explain select name,age,email from test_like where name like '%11';
联合索引,通配符在左侧,select [索引列] 会走索引,type=ALL,检索性能差
EXPLAIN in SQL:https://www.geeksforgeeks.org/explain-in-sql/
官网Mysql的EXPLAIN信息描述:
https://dev.mysql.com/doc/workbench/en/wb-performance-explain.html
其他文章
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。