背景
MySQL中在对某个字段做包含匹配时可以用like。
先看这个结构和结果
CREATE TABLE `tb` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) DEFAULT NULL,
`title` varchar(128) NOT NULL,
`memo` varchar(2000) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `title` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
mysql> explain select * from tb where title like ‘%abcd%’;
+—-+————-+——-+——+—————+——+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+——+————-+
| 1 | SIMPLE | tb | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
+—-+————-+——-+——+—————+——+———+——+——+————-+
1 row in set (1.65 sec)
|
由于like用的是 ‘%xx%’, 不符合前缀匹配的规则,因此用不上索引title,只能作全表扫描。
问题
以上为官方回答。但是如果是在 InnoDB这种聚集索引组织的表中,假设这个表单行很大,比如后面还有若干个类似memo的字段。
这样聚集索引会很大,导致全表扫描需要读更多的磁盘。而理想情况应该是这个流程
1) 遍历title索引,从中读取和过滤所有title中匹配like条件的id
2) 用id到聚簇索引中读数据。
在单行很大,而like能够过滤掉比较多语句的情况下,上面的流程肯定比全表扫描更快,也更省资源。
FORCE INDEX行不行?
第一个反应是用force index。
mysql> explain select * from tb force index(title) where title like ‘%abcd%’;
+—-+————-+——-+——+—————+——+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+——+————-+
| 1 | SIMPLE | tb | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
+—-+————-+——-+——+—————+——+———+——+——+————-+
1 row in set (0.00 sec)
|
显然不行。原因是通常情况下,force index只能从possible_keys中强制选择某一个索引,但是这个查询的possible_keys是NULL, force index 无效。
覆盖索引
我们想到覆盖索引,试验这个语句。
mysql> explain select id from tb where title like ‘%abcd%’;
+—-+————-+——-+——-+—————+——-+———+——+——+————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+——-+———+——+——+————————–+
| 1 | SIMPLE | tb | index | NULL | title | 386 | NULL | 1 | Using where; Using index |
+—-+————-+——-+——-+—————+——-+———+——+——+————————–+
1 row in set (0.00 sec)
|
我们看到这个语句用上了title索引,而且Using index表明用上了覆盖索引。
有同学可能会疑惑,这里possible_keys是NULL, 为什么key用上了title,应了那句“nothing imposible”?
实际上在MySQL优化器里面专门加了这一段,在type= JT_ALL时,会特别扫一下所有能够满足的覆盖索引,并找长度最短的那个。
这么做的考虑就是基于选择小的索引,减少读盘。重要的是,这个优化对于现有的引擎是通用的。
因此上面说的“通常情况下”的例外就是:force index可以强制使用覆盖索引。比如常见的 select count(*) from tb. 这时候你force index所有已存在的索引都是可以生效的。
权宜之计
了解了覆盖索引的效果,我们可以把查询改写为如下,以满足我们最开始希望的执行流程。
mysql> explain Select * from (select id from tb where title like ‘%a’) t1 join tb using (id);
+—-+————-+————-+——–+—————+————+———+——-+——+————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+————-+——–+—————+————+———+——-+——+————————–+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | tb | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| 2 | DERIVED | tb | index | NULL | idx_userid | 386 | NULL | 1 | Using where; Using index |
+—-+————-+————-+——–+—————+————+———+——-+——+————————–+
3 rows in set (0.00 sec)
|
从explain结果中看执行流程是按照我们之前描述的那样,但是引入了JOIN。
补充说明
JOIN写法还会引入primary key查询的时候是随机查询,因此最终的效率受like的过滤效果影响。
这个改写对性能的提升效果取决于要使用的索引与总数据量的大小比较,需要作应用测试。
分享到:
相关推荐
《Effective MySQL之SQL语句最优化》是由MySQL专家Ronald Bradford撰著,书 中提供了很多可以用于改进数据库和应用程序性能的最佳实践技巧,并对这些技巧 做了详细的解释。本书希望能够通过一步步详细介绍SQL优化...
MySQL SQL优化的小册子。 对优化这块重点阐述了相关原理与技术手段。
mysql insert语句mysql insert语句mysql insert语句mysql insert语句mysql insert语句mysql insert语句mysql insert语句mysql insert语句mysql insert语句mysql insert语句mysql insert语句mysql insert语句mysql ...
《Effective MySQL之SQL语句最优化》提供了很多可以用于改进数据库和应用程序性能的最佳实践技巧,并对这些技巧做了详细的解释。《Effective MySQL之SQL语句最优化》希望能够通过一步步详细介绍SQL优化的方法,帮助...
2023最新mysql的sql语句优化方法技巧面试题总结.docx2023最新mysql的sql语句优化方法技巧面试题总结.docx2023最新mysql的sql语句优化方法技巧面试题总结.docx2023最新mysql的sql语句优化方法技巧面试题总结.docx2023...
《Effective MySQL之SQL语句最优化》数据库SQL
MYSQL语句大全MYSQL语句大全MYSQL语句大全MYSQL语句大全MYSQL语句大全MYSQL语句大全
mysql批量语句,传入list 批量修改mysql批量语句,传入list 批量修改mysql批量语句,传入list 批量修改
Effective MySQL之SQL语句最优化提供了很多可以用于改进数据库和应用程序性能的最佳实践技巧,并对这些技巧做了详细的解释。
MySQL 报错:Parameter index out of range (1 > number of parameters, which is 0)——MySQL 数据库 like 语句通配符模糊查询小结 前言 今天在使用MySQL语句执行增删改查操作时,控制台报出了以下错误:Parameter...
sql语句优化.chmmssql优化 mysql优化 oracle优化
mysql的优化 数据库(表)设计合理 我们的表设计要符合3NF 3范式(规范的模式) , 有时我们需要适当的逆范式 sql语句的优化(索引,常用小技巧.) 数据的配置(缓存设大) 适当硬件配置和操作系统 (读写分离.)
在一些情况下,MySQL可以使用索引优化DISTINCT操作,但需要活学活用.本文涉及一个不能利用索引完成DISTINCT操作的实例. 实例1 使用索引优化DISTINCT操作 create table m11 (a int, b int, c int, d int, primary ...
《EffectiveMySQL之SQL语句最优化》提供了很多可以用于改进数据库和应用程序性能的最佳实践技巧,并对这些技巧做了详细的解释。《EffectiveMySQL之SQL语句最优化》希望能够通过一步步详细介绍SQL优化的方法,帮助...
MySql常用的语句 数据库版本:mysql Ver 14.12 Distrib 5.0.22, for Win32 实验数据库:test1 安装路径:c:\mysql 一、基本命令 为了操作的方便,建议先将mysql 的安装目录加入到path 环境变量中,如:将 c:\mysql\...
MySQL语法语句大全MySQL语法语句大全MySQL语法语句大全
常用MySQL语句大全常用MySQL语句大全常用MySQL语句大全常用MySQL语句大全常用MySQL语句大全常用MySQL语句大全常用MySQL语句大全常用MySQL语句大全常用MySQL语句大全常用MySQL语句大全常用MySQL语句大全常用MySQL语句...
mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化...