MySQL--SQL 优化-白红宇

MySQL--SQL 优化

阅读量：4935 次

发布时间：2019-06-11

本文共 15732 字，大约阅读时间需要 52 分钟。

1) 优化 SQL 语句的一般步骤

1.1> 通过 SHOW STATUS 命令了解各种 SQL 的执行频率

MySQL 客户端连接成功后，通过

　　　　　　SHOW [SESSION|GLOBAL] STATUS

　　命令可以提供服务器状态信息。可以根据需要加上参数 SESSION 或 GLOBAL 来显示 SESSION 级（当前连接）的统计结果和 GLOBAL 级（自数据库上次启动至今）的统计结果。如果不写，默认使用的是 SESSION。

　　也可以在操作系统上使用

　　　　　　mysqladmin extended-status

　　命令获得这些信息。

SHOW STATUS LIKE 'Com_%';

　　　　Com_xxx 表示每个 xxx 语句执行的次数。

　　　　Com_select : 执行 SELECT 操作的次数，一次查询只累加1.

　　　　Com_insert : 执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次。

　　　　Com_update : 执行 UPDATE 操作的次数。

　　　　Com_delete : 执行 DELETE 操作的次数。

　　上面这些参数对于所有存储引擎的表操作都会进行累计。

　　下面这几个参数只是针对 InnoDB 存储引擎的，累加的算法也略有不同。

　　　　Innodb_rows_read : SELECT 查询返回的行数。

　　　　Innodb_rows_inserted : 执行 INSERT 操作插入的行数。

　　　　Innodb_rows_updated : 执行 UPDATE 操作更新的行数。

　　　　Innodb_rows_deleted : 执行 DELETE 操作删除的行数。

　　通过以上几个参数，可以很容易地了解当前数据库的应用是以插入更新为主还是以查询操作为主，以及各种类型的 SQL 大致的执行比例是多少。对于更新操作的计数，是对执行次数的计数，不论提交还是回滚都会进行累加。

对于事务型的应用，通过 Com_commit 和 Com_rollback 可以了解事务提交和回滚的情况，对于回滚操作非常频繁的数据库，可能意味着应用编写存在问题。

此外，以下几个参数便于用户了解数据库的基本情况：

　　　　Connections : 试图连接 MySQL 服务器的次数。

　　　　Uptime：服务器工作时间。

　　　　Slow_queries：慢查询的次数。

1.2> 定位执行效率较低的 SQL 语句

可以通过以下两种方式定位执行效率较低的 SQL 语句。

　　　　通过慢查询日志定位那些执行效率较低的 SQL 语句，用 --log-slow-quires[=file_name] 选项启动时，mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。

　　　　慢查询日志在查询结束以后才记录，所以在应用反应执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用 show processlist 命令查看当前 MySQL 在进行的线程，包括线程的状态。是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

1.3> 通过 EXPLAIN 分析低效 SQL 的执行计划

通过以上步骤查询到效率低的 SQL 语句后，可以通过 EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

select_type : 表示 SELECT 的类型，常见的取值有 SIMPLE (简单表，即不使用表连接或者子查询)、PRIMARY (主查询，即外层的查询)、UNION (UNION 中的第二个或者后面的查询的语句)、SUBQUERY (子查询中的第一个 SELECT) 等。

table : 输出结果集的表。

type : 表示 MySQL 在表中找到所需行的方式，或者叫访问类型。常见类型如下：

　　　　　　ALL、index、range、ref、er_ref、const/system、NULL

　　　从左至右，性能由最差到最好。

　　　　ALL : 全表扫描。

　　　　index : 索引全扫描，MySQL 遍历整个索引来查询匹配的行。

　　　　range : 索引范围扫描，常见于 >、>=、<、<=、BETWEEN 等操作符。

　　　　ref: 使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值的记录行。ref 还经常出现在 join 操作中。

　　　　eq_ref: 类似 ref，区别就是在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配；简单来说，就是多表连接中使用 PRIMARY KEY 或者 UNIQUE INDEX 作为关联条件。

　　　 const/system : 单表中最多有一个匹配行，查询起来非常迅速，所以这个匹配行中的其他列的值可以被优化器在当前查询中当做常量来处理，例如，根据主键 PRIMARY KEY 或者唯一索引 UNIQUE INDEX 进行的查询。

　　　　NULL : MySQL 不用访问表或者索引，直接就能够得到结果。

　　　　类型 type 还有其他值，如 ref_or_null(与 ref 类似，区别在于条件中包含对 NULL 的查询)、index_merge(索引合并优化)、unique_subquery(in 的后面是一个查询关键字段的子查询)、index_subquery(与 unique_subquery 类似，区别在于 in 的后面是查询非唯一索引字段的子查询)等。

possible_keys : 表示查询时可能使用的索引

key : 表示实际使用的索引

key_len : 使用到索引字段的长度

rows : 扫描行的数量

Extra : 执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息

MySQL 4.1 开始引入了 explain extended 命令，通过 explain extended 加上 show warnings，我们能够看到在 SQL 真正被执行之前优化器做了哪些 SQL 改写。

explain extended 输出结果中多了 filtered 字段，同时 warning 的 message 字段能够看到优化器优化之后的语句。

在遇到复杂的 SQL 时，我们可以利用 explain extended 的结果来迅速地获取一个更清新易读的 SQL。

MySQL 5.1 开始支持分区功能，同时 explain 命令也增加了对分区的支持。可以通过 explain partitions 命令查看 SQL 所访问的分区。

有时候仅仅通过 explain 分析执行计划并不能很快地定位 SQL 的问题，这个时候可以选择 profile 联合分析。

1.4> 通过 show profile 分析 SQL

MySQL CONG 5.0.37 版本开始增加了对 show profiles 和 show profile 语句的支持。通过 have_profiling 参数，能够看到当前 MySQL 是否支持 profile。

　　默认 profiling 是关闭的，可以通过 set 语句在 Session 级别开启 profiling:

　　　　　　SET profiling = 1

设置保留 profiling 的数目，缺省为 15，范围为 0 至 100，为 0 时将禁用 profiling

　　　　　　set profiling_history_size = n;(n 范围 0 至 100)

通过 profile 我们能够更清楚的了解 SQL 执行的过程。例如，我们知道 MyISAM 表有原数据的缓存(例如行数，即 COUNT(*)值)，那么对一个 MyISAM 表的 COUNT(*) 是不需要消耗太多资源的，而对于 InnoDB 来说，就没有这种元数据缓存，COUNT(*) 执行的较慢。

通过

　　　　　　SHOW PROFILE FOR QUERY query_id

　　语句能够看到执行过程中线程的每个状态和消耗的时间。

　　 Sending data 状态表示 MySQL 线程表示 MySQL 线程开始访问数据行并把结果返回给客户端，而不仅仅是返回结果给客户端。由于在 Sending data 状态下，MySQL 线程往往需要做大量的磁盘读取操作，所以经常是整个查询中耗时最长的状态。

1 set @query_id := 342; 2  3 SELECT  4     STATE, 5     SUM(duration) AS Total_R, 6     ROUND(  7         100 * SUM(duration) / ( 8                         SELECT  9                             SUM(duration)10                         FROM 11                             information_schema.profiling 12                         WHERE        13                             query_id = @query_id14     ), 2) AS Pct_R,15     COUNT(*) AS Calls,16     SUM(duration) / COUNT(*) AS "R/Call" 17 FROM18     information_schema.profiling 19 WHERE20     query_id = @query_id21 GROUP BY22     state23 ORDER BY24     Total_R DESC;

在获取到最消耗时间的线程状态后，MySQL 支持进一步选择 all、cpu、block io、context、switch、page faults 等明细类型来查看 MySQL 在使用什么资源上耗费了过高的时间。

选择查看 CPU 的耗费时间

　　　　　　SHOW PROFILE CPU FOR QUERY query_id

查看 SQL 解析执行过程中每个步骤对应的源码的文件、函数名以及具体的源文件行数

　　　　　　SHOW PROFILE SOURCR FOR QUERY query_id

show profile 能够在做 SQL 优化时帮助我们了解时间都耗费到哪里去了，而 MySQL 5.6 则通过 trace 文件进一步向我们展示了优化器是如何选择执行计划的。

1.5> 通过 trace 分析优化器如何选择执行计划

MySQL 5.6 提供了对 SQL 的跟踪 trace，通过 trace 文件能够进一步了解为什么优化器选择 A 计划而不选择 B 计划，帮助我们更好地理解优化器的行为。

　　使用方式：首先打开 trace，设置格式为 JSON，设置 trace 最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整显示。

　　　　　　SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;

　　　　　　SET OPTIMIZER_TRACE_MAX_MEN_SIZE=1000000;

　　接下来执行想做 trace 的 SQL 语句。

　　最后检查 INFORMATIION_SCHEMA.OPTIMIZER_TRACE 就可以知道 MySQL 是如何执行 SQL 的。

2) 索引问题

2.1> 索引的分类存储

索引系在 MySQL 的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型。

MySQL 目前提供了以下 4 中索引：

　　　　B-Tree 索引：最常见的索引类型，大部分引擎都支持 B 树索引。

　　　　HASH 索引：只有 Memory 引擎支持，使用场景简单。

　　　　R-Tree 索引（空间索引）：空间索引是 MyISAM 的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少。

　　　　Full-text（全文索引）：全文索引也是 MyISAM 的一个特殊索引类型，主要用于全文索引，InnoDB 从 MySQL 5.6 版本开始提供对全文索引的支持。

MySQL 目前不支持函数索引，但是能对列的前面某一部分进行索引，即前缀索引。这个特性可以大大缩小索引文件的大小，但前缀索引也有缺点，在排序 Order By 和分组 Group By 操作的时候无法使用。

常用引擎支持的索引类型对比：

索引	MyISAM 引擎	InnoDB 引擎	Memory 引擎
B-Tree 索引	支持	支持	支持
HASH 索引	不支持	不支持	支持
R-Tree 索引	支持	不支持	不支持
Full-text 索引	支持	支持	不支持

比较常用到的索引就是 B-Tree 索引和 HASH 索引。HASH 索引相对简单，只有 Memory/Heap 引擎支持 HASH 索引。HASH 索引适用于 Key-Value 查询，通过 HASH 索引要比通过 B-Tree 索引查询更迅速；HASH 索引不适用范围查询，例如 <、>、<=、>= 这类操作。如果使用 Memory/Heap 引擎并且 where 条件中不适用 = 进行索引列，那么不会用到索引。Memory/Heap 引擎只有在 = 的条件下才会使用索引。

2.2> MySQL 如何使用索引

　　B-Tree 索引是最常见的索引，构造类似二叉树，能根据键值提供一行或者一个行集的快速访问，通常只需要很少的读操作就可以找到正确的行。不过，B-Tree 索引中的 B 不代表二叉树，而是代表平衡树。B-Tree 索引并不是一颗二叉树。

　　可以利用 B-Tree 索引进行全关键字。关键字范围和关键字前缀查询。

　　2.2.1# MySQL 中能够使用索引的典型场景

　　　　匹配全值（Match the full value），对索引中所有列都指定具体指，即是对索引中的所有列都有等值匹配的条件。

　　　　匹配值的范围查询（Match a range of values），对索引的值能够进行范围查找。

　　　　匹配最左前缀（Match a leftmost prefix），仅仅使用索引中的最左边列进行查找。

　　　　仅仅是对索引进行查询（Index only query），当查询的列都在索引的字段中时，查询的效率更高。

　　　　匹配列前缀（Match a column prefix），仅仅使用索引的第一列，并且只包含索引第一列的开头一部分进行查找。

　　　　能够实现索引匹配部分精确而其他部分进行范围匹配（Match one part exactly and match a range on another part）。

　　　　如果列名是索引，那么使用 column_name is null 就会使用索引（区别于 Oracle）。

　　　　MySQL 5.6 引入了 Index Condition Pushdown（ICP）的特性，进一步优化了查询。Pushdown表示操作下放，某些情况下的条件过滤操作下放到存储引擎。

　　2.2.2# 存在索引但不能使用缩影的典型场景

　　　　以 % 开头的 LIKE 查询不能够利用 B-Tree 索引，执行计划中 key 的值为 NULL 表示没有使用索引。

　　　　因为 B-Tree 索引的结构，所以以 % 开头的查询很自然就没法利用索引了，一般都推荐使用全文索引(Fulltext)来解决类似的全文检索问题。

　　　　数据类型出现隐式转换的时候也不会使用索引，特别是当类型是字符串，那么一定记得在 where 条件中把字符常量值用引号引起来，否则即便这个列上有索引，MySQL 也不会用到，因为 MySQL 默认把输入的常量值进行转换以后才进行检索。

　　　　复合索引的情况下，假如查询条件不包含索引列最左边部分，即不满足最左原则 Leftmost，是不会使用复合索引的。

　　　　如果 MySQL 估计使用索引比全表扫描还慢，则不使用索引。

　　　　用 OR 分割开的条件，如果 OR 前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

　　2.2.3# 查看索引的使用情况

　　　　如果索引正在工作，Handler_read_key 的值将很高，这个值代表了一个行被索引值读的次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使用。

　　　　Handler_read_rnd_next 的值高意味着查询运行低效，并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果正进行大量的表扫描，　　　　　　　　Handler_read_rnd_next 的值较高，则通常说明表索引不正确或写入的查询没有利用索引。

　　　　　　SHOW STATUS LIKE 'Handler_read%'

3) 两个简单实用的优化方法

3.1> 定期分析表和检查表

分析表的语法如下：

　　　　　　ANALYZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE tbl_name[, tbl_name] ...

　　本语句用于分析和存储表的关键字分布，分布的结果将可以使得系统得到准确的统计信息，使得 SQL 能够生成正确的执行计划。如果用户感觉实际执行计划并不是预期的执行计划，执行一次分析表可能会解决问题。在分析期间，使用一个读取锁定对表进行锁定。这对于 MyISAM、BDB 和 InnoDB 表有作用。对于 MyISAM 表，本语句与使用 myisamchk -a相当。

检查表的语法如下：

　　　　　　CHECK TABLE table_name[, table_name] ... [option] = {QUICK|FAST|MEDIUM|EXTENDED|CHANGED}

　　检查表的作用是检查一个或多个表是否有错误。CHECK TABLE 对 MyISAM 和 InnoDB 表有作用。对于 MyISAM 表，关键字统计数据被更新。

CHECK TABLE 而可以检查视图是否有错误。

3.2> 定期优化表

优化表的语法如下：

　　　　　　OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE tbl_name[, tbl_name] ...

　　如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表(含有 VARCHAR、BLOB 或 TEXT 列的表)进行了很多更改，则应使用 OPTIMIZE TABLE 命令来进行表优化。这个命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费，但 OPTIMIZE TABLE 命令只对 MyISAM、BDB 或 InnoDB 表起作用。

对于 InnoDB 引擎的表来说，通过设置 innodb_file_per_table 参数，设置 InnoDB 为独立表空间模式，这样每个数据库的每个表都会生成一个独立的 idb 文件，用于存储表的数据和索引，这样可以在一定程度上减轻 InnoDB 表的空间回收问题。另外，在删除大量数据后，InnoDB 表可以通过 ALTER TABLE 但是不修改引擎的方式来回收不用的空间。

　　　　　　ALTER TABLE table_name ENGINE=engine_name

ANALYZE、CHECK、OPTIMIZE、ALTER TABLE 执行期间将对表进行锁定，因此一定注意要在数据库不繁忙的时候执行相关的操作。

4) 常用的 SQL 优化

4.1> 大批量插入数据

当用 load 命令导入数据的时候，适当的设置可以提高导入的速度。

对于 MyISAM 存储引擎的表，可以通过以下方式快速地导入大量的数据。

　　　　　　ALTER TABLE tbl_name DISABLE KEYS;

　　　　　　loading the data

　　　　　　ALTER TABLE tbl_name ENABLE KYES;

DISABLE KEYS 和 ENABLE KEYS 用来打开或关闭 MyISAM 表非唯一索引的更新。在导入大量的数据到一个非空的 MyISAM 表时，通过设置这两个命令，可以提高导入的效率。对于导入大量数据到一个空的 MyISAM 表，默认就是先导入数据然后才创建索引的，所以不用进行设置。

1 alter table film_test2 disable keys;2 load data infile '/home/mysql/file_test.txt' into table film_test2;3 alter table film_test2 enable keys;

　　上面是对 MyISAM 表进行导入时的优化措施，对于 InnoDB 类型的表，这种方式并不能提高导入数据的效率，可以有一下几种方式提高 InnoDB 表的导入效率。

因为 InnoDB 类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。

在导入数据前执行 SET UNIQUE_CHECKS = 0，关闭唯一性校验，在导入结束后执行 SET UNIQUE_CHECKS = 1，恢复唯一性校验，可以提高导入的效率。（好像没吊用）

如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT = 0，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT = 1，打开自动提交，也可以提高导入的效率。

4.2> 优化 INSERT 语句

当进行数据 INSERT 的时候，可以考虑用以下几种优化方式。

　　　　如果同时从同一客户插入很多行，应尽量使用多个值表的 INSERT 语句，这种方式将大大缩减客户端与数据库之间的连接、关闭等消耗，使得效率比分开执行的单个 INSERT 语句快（在大部分情况下，使用多个值表的 INSERT 语句能比单个 INSERT 语句快上好几倍）。

　　　　如果从不同客户插入很多行，可以通过使用 INSERT DELAYED 语句得到更高的速度。DELAYED 的含义是让 INSERT 语句马上执行，其实数据都被放在内存的队列中，并没有真正的写入磁盘，这比每条语句分别插入要快的多；LOW_PRIORITY 刚好相反，在所有其他用户对表的读写完成后才进行插入。

　　　　将索引文件和数据文件分在不同的磁盘上存放（利用建表中的选项）。

　　　　如果进行批量插入，可以通过增加 bulk_insert_buffer_size 变量值的方法来提高速度，但是，这只能对 MyISAM 表使用。

　　　　当从一个文本文件装载一个表时，使用 LOAD DATA INFILE。这通常比使用很多 INSERT 语句快 20 倍。

4.3> 优化 ORDER BY 语句

MySQL 中的两种排序方式：

　　　　第一种通过有序索引顺序扫描直接返回有序数据，这种方式在使用 explain 分析查询的时候显示为 Using Index，不需要额外的排序，操作效率较高。

　　　　第二种是通过对返回数据进行排序，也就是通常说的 Filesort 排序，所有不是通过索引直接返回排序结果的排序都叫 Filesort 排序。FileSort 并不代表通过磁盘文件进行排序，而只是说明进行了一个排序操作，至于排序操作是否使用了磁盘文件或临时表等，则取决于 MySQL 服务器对排序参数的设置和需要排序数据的大小。

Filesort 是通过相应的排序算法，将取得的数据在 sort_buffer_size 系统变量设置的内存排序区中进行排序，如果内存装载不下，他就会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集。sort_buffer_size 设置的排序区是每个线程独占的，所以同一个时刻，MySQL 中存在多个 sort buffer 排序区。

了解了 MySQL 排序的方式，优化目标就清晰了：尽量减少额外的排序，通过索引直接返回有序数据。WHERE 条件和 ORDER BY 使用相同的索引，并且 ORDER BY 的顺序和索引顺序相同，并且 ORDER BY 的字段都是升序或者都是降序。否则肯定需要额外的排序操作，这样就会出现 Filesort。

总结，下列 SQL 可以使用索引：

　　　　　　SELECT * FROM table_name ORDER BY key_part1, key_part2, ...;

　　　　　　SELECT * FROM table_name WHERE key_part1=1 ORDER BY key_pary1 DESC, key_part2 DESC;

　　　　　　SELECT * FROM table_name ORDER BY key_part1 DESC, key_part2 DESC;

但是在以下几种情况下则不能使用索引：

　　　　　　SELECT * FROM table_name ORDER BY key_part1 DESC, key_part2 ASC;#ORDER BY 的字段混合 ASC 和 DESC

　　　　　　SELECT * FROM table_name WHERE key2=constant ORDER BY key1;#用于查询行的关键字与 ORDER BY 中所使用的不相同

　　　　　　SELECT * FROM table_name ORDER BY key1, key2;#对不同的关键字使用 ORDER BY

对 Filesort 的优化：

　　　　通过创建合适的索引能够减少 Filesort 出现，但是在某些情况下，条件限制不能让 Filesort 消失，那就需要想办法加快 Filesort 的操作。对于 Filesort，MySQL 有两种排序算法。

　　　　　　两次扫描算法(Two Passes)：首先根据条件取出排序字段和行指针信息，之后在排序区 sort buffer 中排序。如果排序区 sort buffer 不够，则在临时表 Temporary Table 中存储排序结果。完成排序后根据行指针回表读取记录。该算法是 MySQL 4.1 之前采用的算法，需要两次访问数据，第一次获取排序字段和行指针信息，第二次根据行指针获取记录，尤其是第二次读取操作可能导致大量随机 I/O 操作；优点是排序的时候内存开销较少。

　　　　　　一次扫描算法(Single Pass)：一次性取出满足条件的行的所有字段，然后在排序区 sort buffer 中排序后直接输出结果集。排序的时候内存开销比较大，但是排序效率比两次扫描算法要高。

　　　　　　MySQL 通过比较系统变量 max_length_for_sort_data 的大小和 Query 语句取出的字段总大小来判断使用哪种排序算法。如果 max_length_for_sort_data 更大，那么使用第二种优化之后的算法；否则使用第一种算法。

　　　　　　适当加大系统变量 max_length_sort_data 的值，能够让 MySQL 选择更优化的 Filesort 排序算法。当然，假如 max_length_for_sort_data 设置过大，会造成 CPU 利用率过低和磁盘 I/O 过高，CPU 和 I/O 利用平衡就足够了。

　　　　　　适当加大 sort_buffer_size 排序区，尽量让排序在内存中完成，而不是通过创建临时表放在文件中进行；当然也不能无限制加大 sort_buffer_size 排序区，因为 sort_buffer_size 参数是每个线程独占的，设置过大，会导致服务器 SWAP 严重，要考虑数据库活动连接数和服务器内存的大小来适当设置排序区。

　　　　　　尽量只使用必要的字段，SELECT 具体的字段名称，而不是 SELECT * 选择所有字段，这样可以减少排序区的使用，提高 SQL 性能。

4.4> 优化 GROUP BY 语句

默认情况下，MySQL 对所有 GROUP BY col1, col2, ... 的字段进行排序。这与在查询中指定 ORDER BY col1, col2, ... 类似。因此，如果显式包括一个包含相同列的 ORDER BY 子句，则对 MySQL 的实际执行性能没什么影响。

如果查询包括 GROUP BY 但用户想要避免排序结果的消耗，则可以指定 ORDER BY NULL 禁止排序。

4.5> 优化嵌套查询

MySQL 4.1 开始支持 SQL 的子查询。这个技术可以使用 SELECT 语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性地完成很多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效的连接（JOIN）替代。

连接查询子所以比子查询的效率高，是因为 MySQL 不需要再内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。

4.6> MySQL 如何优化 OR 条件

对于含有 OR 的查询子句，如果要利用索引，则 OR 之间的每个条件列都必须用到索引；如果没有索引，则应该考虑增加索引。

4.7> 优化分页查询

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头痛的分页场景是“limit 1000, 20”，此时 MySQL 排序出前 1020 条记录后仅仅需要返回第 1001 到 1020 条记录，前 1000 条记录都会被抛弃，查询和排序的代价非常高。

4.7.1# 第一种优化思路

在索引上完成排序分页的操作，最后根据主键关联回原表查询所需要的其他列内容。

4.7.2# 第二种优化思路

把 LIMIT 查询转换成某个位置的查询。把 LIMIT m, n 转换成 LIMIT n 的查询。只适合在排序字段不会出现重复值的特定环境，能够减轻分页翻页的压力；如果排序字段出现大量重复值，而仍进行这种优化，那么分页结果可能丢失部分记录，不适用这种方式进行优化。

4.8> 使用 SQL 提示

SQL 提示（SQL HINT）是优化数据库的一个重要手段，简单来说就是在 SQL 语句中加入一些人为的提示来达到优化操作的目的。

USE INDEX：在查询语句中表名的后面，添加 USE INDEX 来提供希望 MySQL 去参考的索引列表，就可以让 MySQL 不再考虑其他可用的索引。

1 SELECT2     COUNT(*)3 FROM4     rental USE INDEX (idx_rental_date);

IGNORE INDEX：如果用户只是单纯地想让 MySQL 忽略一个或者多个索引，则可以使用 IGNORE INDEX 作为 HINT。

1 SELECT2     COUNT(*)3 FROM4     rental IGNORE INDEX (idx_rental_date);

FORCE INDEX：为强制 MySQL 使用一个特定的索引，可以在查询中使用 FORCE INDEX 作为 HINT。

1 SELECT2     COUNT(*)3 FROM4     rental FORCE INDEX (idx_fk_inventory_id)5 WHERE6     inventory_id > 1;

5) 常用 SQL 技巧

5.1> 正则表达式的使用

MySQL 利用 REGEXP 命令提供给用户扩展的正则表达式功能，REGEXP 实现的功能类似 UNIX 上 GREP 和 SED 的功能，并且 REGEXP 在进行模式匹配时是区分大小写的。

MySQL 5.0 中可以使用的模式序列：

序列	序列说明
^	在字符串的开始处进行匹配
$	在字符串的末尾处进行匹配
.	匹配任意单个字符，包括换行符
[...]	匹配出括号内的任意字符
[^...]	匹配非列在方括号内的任何字符
a*	匹配零个或多个a（包括空串）
a+	匹配1个或多个a（不包括空串）
a?	匹配1个或零个a
a1\|a2	匹配a1或a2
a(m)	匹配m个a
a(m,)	匹配m个或更多个a
a(m,n)	匹配m到n个a
a(,n)	匹配0到n个a
(...)	将模式元素组成单一元素

1 SELECT2     first_name,3     email4 FROM5     customer6 WHERE7     email REGEXP 'sakilacustomer[,.]org$'

有些情况下，采用正则表达式使得比用 LIKE 更加简单易读。

5.2> 巧用 RAND() 提取随机行

大多数数据库都会提供产生随机数的包或者函数，通过这些包或者函数可以产生用户需要的随机数，也可以用来从数据表中抽取随机产生的记录，这对一些抽样分析统计是非常有用的。

ORACLE 中用 DBMS_RANDOM 包产生随机数，而在 MySQL 中，产生随机数的方法是 RAND() 函数。

SELECT    *FROM     categoryORDER BY    rand()LIMIT 5;

5.3> 利用 GROUP BY 的 WITH ROLLUP 子句

在 SQL 语句中，使用 GROUP BY 的 WITH ROLLUP 字句可以检索出更多的分组聚合信息，它不仅仅能像一般的 GROUP BY 语句那样检索出各组的聚合信息，还能检索出本组类的整体聚合信息。

WITH ROLLUP 反映的是一种 OLAP 思想，也就是说这一个 GROUP BY 语句执行完成后可以满足用户想要得到的任何一个分组以及分组组合的聚合信息值。

5.4> 用 BIT GROUP FUNCTIONS 做统计

BIT_AND、BIT_OR 一般用途就是做数值之间的逻辑位运算。当把它们与 GROUP BY 子句联合使用的时候就可以做一些其他的任务。

5.5> 数据库名、表名大小写问题

在 MySQL 中，数据库对应操作系统下的数据目录。数据库中的每个表至少对应数据库目录中的一个文件（也可能是多个，这取决于存储引擎）。因此，所使用操作系统的大小写敏感性决定了数据库名和表名的大小写敏感性。

在大多数 UNIX 环境中，由于操作系统对大小写的敏感性导致了数据库名和表名对大小写敏感性，而在 WINDOWS 中，由于操作系统本身对大小写不敏感，因此在 WINDOWS 下的 MySQL 数据库名和表名对大小写也不敏感。

列、索引、存储子程序和触发器名在任何平台上对大小写不敏感。默认情况下，表别名在 UNIX 中对大小写敏感，但在 WINDOWS 或 MAC OS X 中对大小写不敏感。

在 MySQL 中，如何在硬盘上保存、使用表名和数据库名是由 lower_case_tables_name 系统变量决定的，用户可以在启动 MySQL 服务时设置这个系统变量。

lower_case_tables_name 可以采用如下表中的值

值	含义
0	使用 CREATE TABLE 或 CREATE DATABASE 语句指定的大写和小写在硬盘上保存表名和数据库名。名称对大小写敏感。在 UNIX 系统中的默认设置就是这个值。
1	表名在硬盘上以小写保存，名称对大小写敏感。MySQL 将所有表名转换为小写以便存储和查找。该值为 Windows 和 Mac OS X 系统中的默认值。
2	表名和数据库名在硬盘上使用 CREATE TABLE 或 CREATE DATABASE 语句指定的大小写进行保存，但 MySQL 将它们转换为小写以便查找。此值只在对大小写不敏感的文件系统上适用。

在 UNIX 中使用 lower_case_tables_name = 0，而在 Windows 中使用 lower_case_tables_name = 2，这样可以保留数据库名和表名的大小写。不利之处是必须确保在 Windows 中所有 SQL 语句总是正确的使用大小写来引用数据库名和表名，如果 SQL 语句中没有正确引用数据库名和表名的大小写，那么虽然在 Windows 中能正确执行，但是如果将查询转移到 UNIX 中，大小写不正确，将会导致查询失败。