Effective MySQL之SQL语句最优化--索引_MySQL

胖涛酱_9357

胖涛酱_9357

2016-06-01

978人浏览

原创

bitsCN.com

effective mysql之sql语句最优化--索引

 

1 两个索引取并集组合

[sql] 

ALTER TABLE album ADD INDEX name_release (name,first_released);  

EXPLAIN SELECT a.name, ar.name,  

a.first_released  

  FROM album a  

 INNER JOIN artist ar USING (artist_id)  

 WHERE a.name = 'Greatest Hits'  

 ORDER BY a.first_released;  

mysql> EXPLAIN SELECT a.name, ar.name,  

    -> a.first_released  

    ->   FROM album a  

    ->  INNER JOIN artist ar USING (artist_id)  

    ->  WHERE a.name = 'Greatest Hits'  

    ->  ORDER BY a.first_released;  

+----+-------------+-------+--------+--------------------------------+--------------+---------+-------------------+------+-------------+  

| id | select_type | table | type   | possible_keys                  | key          | key_len | ref               | rows | Extra       |  

+----+-------------+-------+--------+--------------------------------+--------------+---------+-------------------+------+-------------+  

|  1 | SIMPLE      | a     | ref    | name_release,name_2,name_part2 | name_release | 257     | const             |  659 | Using where |  

|  1 | SIMPLE      | ar    | eq_ref | PRIMARY                        | PRIMARY      | 4       | union.a.artist_id |    1 |             |  

+----+-------------+-------+--------+--------------------------------+--------------+---------+-------------------+------+-------------+  

2 rows in set (0.00 sec)  

  

ALTER TABLE album ADD INDEX name_release (name,first_released);  

 

 

MySQL 可以在WHERE、ORDER BY 以及GROUP BY 列中使用索引;然而,一般来说MySQL 在一个表上只选择一个索引。

从MySQL 5.0 开始,在个别例外情况中优化器可能会使用一个以上的索引,但是在早期的版本中这样做会导致查询运行更加缓慢。

 

2 两个索引取并集

第一种: 最常见的索引合并的操作是两个索引取并集,当用户对两个有很

高基数的索引执行OR 操作时会出现这种这种索引合并操作。请

看下面的示例:

 

[sql] 

 SET @@session.optimizer_switch='index_merge_intersection=on';  

   

 EXPLAIN SELECT artist_id, name  

 FROM artist  

 WHERE name = 'Queen'  

 OR founded = 1942/G  

   

mysql>  EXPLAIN SELECT artist_id, name  

    ->  FROM artist  

    ->  WHERE name = 'Queen'  

    ->  OR founded = 1942;  

+----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+  

| id | select_type | table  | type        | possible_keys | key          | key_len | ref  | rows | Extra                                  |  

+----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+  

|  1 | SIMPLE      | artist | index_merge | name,founded  | name,founded | 257,2   | NULL |  499 | Using union(name,founded); Using where |  

+----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+  

1 row in set (0.01 sec)  

 

 

Extra: Using union(name,founded); 采用了union的联合索引模式,取合集.

注意

在MySQL 5.1 中首次引入了optimizer_switch 系统变量,可以

通过启用或禁用这个变量来控制这些附加选项。

 

2 第二种类型的索引合并是对两个有少量唯一值的索引取交集,如下所示:

[sql] 

SET @@session.optimizer_switch='index_merge_intersection=on';  

EXPLAIN SELECT artist_id, name  

 FROM artist  

  WHERE type = 'Band'  

 AND founded = 1942;  

   

 mysql> SET @@session.optimizer_switch='index_merge_intersection=on';  

Query OK, 0 rows affected (0.00 sec)  

  

mysql>   

mysql>   

mysql> EXPLAIN SELECT artist_id, name  

    ->  FROM artist  

    ->   WHERE type = 'Band'  

    ->  AND founded = 1942;  

+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+  

| id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows | Extra       |  

+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+  

|  1 | SIMPLE      | artist | ref  | founded       | founded | 2       | const |  498 | Using where |  

+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+  

1 row in set (0.00 sec)  

Extra: Using intersect(founded,type); Using where 这里由于是AND,所以只需要取2个索引中最高效的那个索引来进行遍历取值.

3 第三种类型的索引合并操作和对两个索引取并集比较类似,但它需要先经过排序:

[sql] 

EXPLAIN SELECT artist_id, name  

 FROM artist  

 WHERE name = 'Queen'  

  OR (founded BETWEEN 1942 AND 1950);  

  mysql> EXPLAIN SELECT artist_id, name  

    ->  FROM artist  

    ->  WHERE name = 'Queen'  

    ->   OR (founded BETWEEN 1942 AND 1950);  

+----+-------------+--------+-------------+---------------+--------------+---------+------+------+---------------------------------------------+  

| id | select_type | table  | type        | possible_keys | key          | key_len | ref  | rows | Extra                                       |  

+----+-------------+--------+-------------+---------------+--------------+---------+------+------+---------------------------------------------+  

|  1 | SIMPLE      | artist | index_merge | name,founded  | name,founded | 257,2   | NULL | 5900 | Using sort_union(name,founded); Using where |  

+----+-------------+--------+-------------+---------------+--------------+---------+------+------+---------------------------------------------+  

1 row in set (0.00 sec)  

 

4 数个索引合并的情况

在创建这些示例的过程中,还发现一种以前在任何客户端的查询中未曾出现过的新情况。以下是三个索引合并的示例:

[sql] 

mysql> EXPLAIN SELECT artist_id, name  

  FROM artist  

  WHERE name = 'Queen'  

 OR (type = 'Band' AND founded = '1942');  

 .....  

mysql> EXPLAIN SELECT artist_id, name  

    ->   FROM artist  

    ->   WHERE name = 'Queen'  

    ->  OR (type = 'Band' AND founded = '1942');  

+----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+  

| id | select_type | table  | type        | possible_keys | key          | key_len | ref  | rows | Extra                                  |  

+----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+  

|  1 | SIMPLE      | artist | index_merge | name,founded  | name,founded | 257,2   | NULL |  499 | Using union(name,founded); Using where |  

+----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+  

1 row in set (0.00 sec)  

 

技巧

应该经常评估多列索引是否比让优化器合并索列效率更高。多个单列索引和多个多列索引到底哪个更有优势?这个问题

只有结合特定应用程序的查询类型和查询容量才能给出答案。在各种不同的查询条件下,将一些高基数列上的那些单列索引进行

索引合并能够带来很高的灵活性。数据库写操作的性能参考因素也同样会影响到获取数据的最优的数据访问路径。

 

5 创建更好的MySQL 索引

主要用的比较多的2个特殊的索引

通过使用索引,查询的执行时间可以从秒的数量级减少到毫秒数量级,这样的性能改进能够为你的应用程序的性能带来飞跃。

合理的调整你的索引对优化来说是非常重要的,尤其是对于高吞吐量的应用程序。即使对执行时间的改进仅仅是数毫秒,但对于

一个每秒执行1000 次的查询来说这也是非常有意义的性能提升。例如,把一个原本需要20 毫秒执行的每秒运行1 000 次的查询的

执行之间缩短4 毫秒,这对于优化SQL 语句来说是至关重要的。我们将使用第4 章介绍的方法创建多列索引,并在这一基础

上创建更好的覆盖索引。

● 创建覆盖索引

ALTER TABLE artist

 DROP INDEX founded,

 ADD INDEX founded_name (founded,name);

 在InnoDB 中,主码的值会被附加在非主码索引的每个对应记录后面,因此没有必要在非主码索引中指定主码。

这一重要特性意味着InnoDB 引擎中所有非主码索引都隐含主码列了。并且对于那些从MyISAM 存储引擎转换过来的表,通常会

在它们InnoDB 表索引中将主码添加为最后一个元素。 当QEP 在Extra 列中显示Using index 时,这并不意味着在访

问底层表数据时使用到了索引,这表示只有这个索引才是满足查询所有要求的。这种索引可以为大型查询或者频繁执行的查询带

来显著的性能提升,它被称为覆盖索引。覆盖索引得名于它满足了查询中给定表用到的所有的列。想

要创建一个覆盖索引,这个索引必须包含指定表上包括WHERE语句、ORDER BY 语句、GROUP BY 语句(如果有的话)以及

SELECT 语句中的所有列。

[Comment]:随着数据容量的增加,尤其是超过内存和磁盘最大容量的时候,为一个大型列创建索引可能

会对系统整体性能有影响。覆盖索引对于那些使用了很多较小长度的主码和外键约束的大型规范化模式来说是理想的优化方式。

● 创建局部列的索引

[sql] 

ALTER TABLE artist  

 DROP INDEX name,  

  ADD INDEX name_part(name(20));  

 

  这里主要考虑的是如何减小索引占用的空间。一个更小的索引意味着更少的磁盘I/O 开销,而这又意味着能更快地访问到需

要访问的行,尤其是当磁盘上的索引和数据列远大于可用的系统内存时。这样获得的性能改进将会超过一个非唯一的并且拥有低

基数的索引带来的影响。局部索引是否适用取决于数据是如何访问的。之前介绍覆盖索引时,你可以看到记录一个短小版本的name 列不会对执行过

的SQL 语句有任何好处。最大的益处只有当你在被索引的列上添加限制条件时才能体现出来。

[sql] 

EXPLAIN SELECT artist_id,name,founded  

 FROM artist  

 WHERE name LIKE 'Queen%';  

 mysql> EXPLAIN SELECT artist_id,name,founded  

    ->  FROM artist  

    ->  WHERE name LIKE 'Queen%';  

+----+-------------+--------+-------+---------------+------+---------+------+------+-------------+  

| id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows | Extra       |  

+----+-------------+--------+-------+---------------+------+---------+------+------+-------------+  

|  1 | SIMPLE      | artist | range | name          | name | 257     | NULL |   93 | Using where |  

+----+-------------+--------+-------+---------------+------+---------+------+------+-------------+  

1 row in set (0.00 sec)  

 

在这个示例中,Extra后面没有出现Using Index,所以在索引中记录全名并没有带来额外的益处。

而所提供的局部列索引满足了WHERE 条件。如何选择合适的长度取决于数据的分布以及访问路径。目前没有准确的方法计算索

引的恰当长度。因此对给定范围的列长度内的唯一值数目的比较

是必不可少的。

count了下SELECT count(*) FROM artist WHERE name LIKE 'Queen%'; 才93条记录,而SELECT count(*) FROM artist;有577983条记录,按照普遍的情况,可以走索引,难道是name(20)的20定义的太长了?

[sql] 

ALTER TABLE artist  

 DROP INDEX name_part,  

  ADD INDEX name_part2(name(10));  

  

  mysql> ALTER TABLE artist  

    ->  DROP INDEX name_part,  

    ->   ADD INDEX name_part2(name(10));  

Query OK, 0 rows affected (3.41 sec)  

Records: 0  Duplicates: 0  Warnings: 0  

  

mysql> EXPLAIN SELECT artist_id,name,founded  

    ->  FROM artist  

    ->  WHERE name LIKE 'Queen%';  

+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+  

| id | select_type | table  | type  | possible_keys | key        | key_len | ref  | rows | Extra       |  

+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+  

|  1 | SIMPLE      | artist | range | name_part2    | name_part2 | 12      | NULL |   93 | Using where |  

+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+  

1 row in set (0.00 sec)  

  

看结果,再用name(5) 试试看。  

mysql> ALTER TABLE artist  

    ->  DROP INDEX name_part2,  

    ->   ADD INDEX name_part3(name(5));  

Query OK, 0 rows affected (3.21 sec)  

Records: 0  Duplicates: 0  Warnings: 0  

  

mysql> EXPLAIN SELECT artist_id,name,founded  

    ->  FROM artist  

    ->  WHERE name LIKE 'Queen%';  

+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+  

| id | select_type | table  | type  | possible_keys | key        | key_len | ref  | rows | Extra       |  

+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+  

|  1 | SIMPLE      | artist | range | name_part3    | name_part3 | 7       | NULL |   93 | Using where |  

+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+  

1 row in set (0.00 sec)  

 

看来局部索引对like的效果不是很明显的,可能跟数据分布范围有关,也许这93条数据全部打散在各个数据库块中,

所以导致解析器认为不能简单地通过数次index就能遍历出数据,故而Extra栏里面就没有出现Using Index的提示。

  

总结:在索引中正确的定义列(包括定义列的顺序和位置)能够改变索引的实际使用效果。好的索引能够为一个执行缓慢的查询带来

巨大的性能提升。索引也可能使原来执行很快的查询的执行时间减少若干毫秒。在高并发系统中,将1 000 000 条查询减少几毫秒

将会显著改善性能,并且获得更大的容量和扩展性。为SQL 查询创建最优索引可以认为是一项艺术。

 

bitsCN.com

PHP速学视频免费教程(入门到精通)
PHP速学视频免费教程(入门到精通)

PHP怎么学习?PHP怎么入门?PHP在哪学?PHP怎么学才快?不用担心,这里为大家提供了PHP速学教程(入门到精通),有需要的小伙伴保存下载就能学习啦!

下载

相关标签:

本站声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

相关专题

更多
GoFrame框架Web开发与路由中间件
GoFrame框架Web开发与路由中间件

本专题聚焦GoFrame框架的Web开发,拆解路由注册与匹配规则、请求参数获取、参数校验与中间件执行顺序,说明统一响应、JWT鉴权、文件上传与接口文档生成的落地方式,并补充优雅重启与测试实践。

2026.11.17

0

15

GoFrame框架安装与gf命令行工具
GoFrame框架安装与gf命令行工具

本专题围绕GoFrame框架的安装与工具链配置展开,讲清Go版本要求、gf命令行的安装方式、代理镜像与依赖准备,说明项目初始化、热编译、交叉编译打包的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.17

0

15

GoFrame框架零基础入门
GoFrame框架零基础入门

本专题整理GoFrame框架的入门知识,从模块化设计理念、环境准备到跑通第一个Web服务,讲清目录分层、配置加载、日志与中间件的用法,并对比Gin、Go-Zero的差异,帮助Go开发者建立整体认知。

2026.11.17

0

15

SQLAlchemy 一对多关系怎么建立
SQLAlchemy 一对多关系怎么建立

SQLAlchemy 一对多关系怎么建立?本专题整理父子表外键、relationship 双向映射、关联查询、级联删除和加载优化等相关文章,帮助你理解并实现常见的 ORM 关系建模。

2026.11.17

0

10

SQLAlchemy 如何增删改查
SQLAlchemy 如何增删改查

SQLAlchemy 如何增删改查?本专题整理 ORM 新增记录、条件查询、更新和删除、Session 事务提交与批量处理等实用文章,帮助你掌握 Python 数据库操作流程。

2026.11.17

0

11

SQLAlchemy ORM 模型怎么写
SQLAlchemy ORM 模型怎么写

SQLAlchemy ORM 模型怎么写?本专题整理模型类定义、Mapped 字段映射、主键与约束、表结构创建和常见报错的相关文章,帮助你从基础配置到实际建模逐步掌握 SQLAlchemy。

2026.11.17

0

16

Go-Zero API开发使用教程
Go-Zero API开发使用教程

本专题聚焦Go-Zero的API开发,拆解.api描述文件的语法结构、路由与分组定义、参数绑定与校验规则,说明统一响应体、JWT鉴权、跨域与接口文档生成的落地方式,并给出网关聚合与接口测试的实践建议。

2026.11.12

160

15

Go-Zero安装与goctl环境配置教程
Go-Zero安装与goctl环境配置教程

本专题围绕Go-Zero的安装与工具链配置展开,讲清Go版本要求、goctl安装方式、代理镜像、protoc依赖与环境变量设置,说明多版本共存、升级回退与CI流水线安装的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.12

120

15

Go-Zero零基础入门教程
Go-Zero零基础入门教程

本专题整理Go-Zero的入门知识,从框架定位、环境准备到跑通第一个API服务,讲清目录分层、配置加载、日志与中间件的基本用法,并对比Gin、Kratos的差异,帮助Go开发者建立整体认知,判断它是否适合当前项目。

2026.11.12

100

15

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
热门推荐
/
最新课程
phpStudy极速入门视频教程
phpStudy极速入门视频教程

共6课时 | 54.7万人学习

独孤九贱(4)_PHP视频教程
独孤九贱(4)_PHP视频教程

共89课时 | 134.3万人学习