优化 SQL 语句的一般步骤

通过 show status 命令了解各种 SQL 的执行频率

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息，也可以在操作系统上使用 mysqladmin extended-status 命令获得这些消息。show [session|global] status 可以根据需要加上参数 session 或者 global 来显示 session 级（当前连接）的统计结果和 global级（自数据库上次启动至今）的统计结果。如果不写，默认使用的参数是 session

show status like 'com_%';

返回的结果 com_xxx 表示每个 xxx 语句执行的次数，通过比较关心的是 com_select，com_insert，com_update，com_delete

上面这些参数对于所有存储引擎的表操作都会进行累计。下面这几个参数只是针对 innoDB 存储引擎的，累加的算法也略有不同

show status like 'InnoDB_rows%';

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

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

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

• connections：试图连接 MySQL 服务器的次数
• uptime：服务器工作时间
• slow_queries：慢查询的次数

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

• 通过慢查询日志定位那些执行效率较低的 SQL 语句 用 --log-slow-queries[=file_name] 选项启动时，mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件
• 慢查询日志在查询结束之后才记录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用 show processlist 命令查看当前 MySQL 在进行的线程，包括线程的状态，是否锁表等，可以实时的查看 SQL 的执行情况，同时对一些锁表操作进行优化

通过 explain 分析低效 SQL 的执行计划

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

exlain select * from tablename;

简单说明以下返回的参数：

• select_type：表示 select 类型，常见的取值有 simple（简单表，即不用表连接或者子查询），primary（主查询，即外层的查询），union（union 中的第二个或者后面的查询语句），subquery（子查询中第一个 select）等
• table：输出结果集的表
• type：表示 MySQL 在表中找到所需行的方式，或者叫访问类型，常见的类型有 all，index，range，ref，eq_ref，const / system，null，左右到右，性能由最差到最好
  • all：全表扫描，MySQL 遍历全表来找到匹配的行
  • index：索引全扫描，遍历整个索引来查询匹配的行
  • range：索引范围扫描，常见与 <，<=，>，>=，between 等操作符
  • ref：使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值的记录行
  • eq_ref：类似 ref，区别就在使用的索引是唯一的索引，对于每个索引键值，表中只有一条记录匹配；简单来说，就是多表连接中使用 primary key 或者 unique index 作为关联条件
  • const / system：单表中最多有一个匹配行，查询起来非常迅速，所以这个匹配行中的其他列值可以被优化器在当前查询中当作常量来处理
  • null：不用访问表或者索引就能直接得到结果
• possible_keys：表示查询时可能使用的索引
• key：表示实际使用的索引
• key_len：使用索引字段的长度
• rows：扫描行的数量
• extra：执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息

explain extended 命令可以获取更详细的信息，配合 show warnings` 查看警告

explain extended select * from xixi;

explain partitions 命令查看 SQL 所访问的分区

explain partitions select * from xixi;

通过 show profile 分析 SQL

通过 have_profiling 参数，能够查看当前 MySQL 是否支持 profile

select @@have_profiling;

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

select @@profilinng;
set profiling=1;

执行一个查询后，通过 show profiles 语句查看 query_id 和消耗的时间

通过 show profile for query query_id 语句能够查看执行过程中线程的每个状态消耗的时间

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

show profile cpu for query 1;

还可以通过 show profile source for query 查看 SQL 解析执行过程中每个步骤对应的源码文件，函数名以及具体的源文件行数

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

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

set optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_men_size=1000000;

先执行一个查询，然后检查 information_schema.optimizer_trace 就可以知道 MySQL 是如何执行 SQL 的

select * from information_schema.optimizer_trace \G;

索引问题

索引的存储分类

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

• B-Tree 索引：最常见的索引类型，大部分引擎都支持 B 树索引
• hash 索引：只有 memory 引擎支持
• R-Tree 索引（空间索引）：空间索引是 MyISAM 的一个特殊索引类型，主要用于地理空间数据类型
• Full-text（全文索引）

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

create index indexname on tablename(col(10))

MySQL 如何使用索引

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

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 就会使用索引
• Index Condition Pushdown （ICP）特性，进一步优化查询

存在索引当不能使用索引的典型场景

• 以 % 开头的 like 查询不能够利用 B-Tree 索引
• 数据类型出现隐式转换的时候也不会使用索引，特别是当列类型是字符串，那么一定记得在 where 条件中把字符常量值用引号引起来，否则即便这个列上有索引，MySQL 也不会用到，因为 MySQL 默认把输入的常量值进行转换以后才进行检索
• 复合索引的情况下，假如查询条件不包含索引列最左边部分，即不满足最左原则 leftmost，是不会使用复合索引的
• 如果 MySQL 估计使用索引比全表扫描更慢，则不使用索引
• 用 or 分割开的条件，如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到

查看索引使用情况

show status like 'handler_read%';

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

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

两个简单实用的优化方法

定期分析表和检查表

分析表的语法如下：

analyze [local|no_write_to_binlog] table tablename[,tablename]...

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

检查表的语法如下：

check table tablename[,tablename]... [option]... option={quick|fast|medium|extended|changed}

检查表的作用是检查一个或多个表是否有错误

定期优化表

优化表的语法如下：

optimize [local|no_write_to_binlog] table tablename[,tablename]...

如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表进行了很多更改，则应使用 optimize table 命令来进行表优化。这个命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费

常用 SQL 的优化

大批量插入数据

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

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

alter table tablename disable keys;
loading the data
alter table tablename enable keys;

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

提高 InnoDB 表的导入效率

1. 因为 InnoDB 类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率
2. 在导入数据前执行 set unique_checks=0，关闭唯一性校验，在导入结束后执行 set unique_checks=1，恢复唯一性校验，可以提高导入的效率
3. 如果应用使用自动提交的方式，建议在导入前执行 set autocommit=0，关闭自动提交，导入结束后再执行 set autocommit=1，打开自动提交，也可以提高导入的效率

优化 insert 语句

• 如果同时从同一客户插入很多行，应尽量使用多个值表的 insert 语句，这种方式将大大缩减客户端与数据库之间的连接，关闭等消耗，使得效率比分开执行的单个 insert 语句快

    insert into tablename values(1,2),(3,4)...
  

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

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

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

• 当从一个文本文件装载一个表时，使用 load date infile。这通常比使用很多 insert 语句快 20 倍

优化 order by 语句

MySQL 中有两种排序方式

• 第一种通过有序索引顺序扫描直接返回有序数据，这种方式在使用 explain 分析查询的时候显示为 using index，不需要额外的排序，操作效率较高
• 第二种是通过对返回数据进行排序，也就是通常说的 filesort 排序，所有不是通过索引直接俄返回排序结果的排序都叫 filesort 排序。filesort 并不代表通过磁盘文件进行排序，而只是说明进行了一个排序操作，至于排序操作是否使用了磁盘文件或临时表等，则取决于 MySQL 服务器对排序参数的设置和需要排序数据的大小

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

filesort 的优化

filesort 有两种算法

• 两次扫描算法：首先根据条件去除排序字段和行指针信息，之后在排序区 sort buffer 中排序。如果排序区 sort buffer 不够，则在临时表 temporary table 中存储排序结果。完成排序后根据行指针回表读取记录
• 一次扫描算法：一次性取出满足条件的行的所有字段，然后在排序区 sort buffer 中排序后直接输出结果集。排序的时候内存开销比较大，但是排序效率比两次扫描算法要高

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

适当加大系统变量 max_length_for_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 性能

优化 group by 语句

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

如果查询包括 group by 但用户想要避免排序结果的消耗，则可以指定 order by null 禁止排序

优化嵌套查询

子查询可以被更有效率的连接（join）替代，连接之所以更有效率一些，是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作

MySQL 如何优化 OR 条件

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

优化分页查询

• 在索引上完成排序分页的操作，最后根据主键关联回原表查询所需要的其他列内容
• 把 limit 查询转换成某个位置的查询

使用 SQL 提示

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

select sql_buffer_result * from...

这个语句将强制 MySQL 生成一个临时结果集。只要临时结果集生成后，所有表上的锁定均被释放

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

添加 ignore index 让 MySQL 忽略一个或者多个索引

添加 force index 强制 MySQL 使用一个特定的索引

常用 SQL 技巧

正则表达式的使用

MySQL 利用 regexp 命令提供给用户扩展正则表达式功能

select 'xixi' regexp '^xi';

巧用 rand() 提取随机行

rand() 函数与 order by 子句可以一起完成随机抽取行的功能

select * from xixi order by rand();

利用 group by 的 with rollup 子句

在 SQL 语句中，使用 group by 的 with rollup 子句可以检索出更多的聚合信息，它不仅仅能像一般的 group by 语句那样检索出各组的聚合信息，能还检索出本组类的整体聚合信息

当使用 rollup 时，不能同时使用 order by 子句进行结果排序，换言之，rollup 和 order by 是互相排斥的，此外，limit 用在 rollup 后面

用 bit group functions 做统计

group by 语句和 bit_or()，bit_and() 函数可以配合做一些统计

数据库名，表名大小写问题

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

使用外键需要注意的问题

在 MySQL 中，InnoDB 存储引擎支持对外部关键字约束条件的检查。而对于其他类型存储引擎的表，当使用 references tablename(col) 子句定义列时可以使用外部关键字，但是该子句没有实际的效果，只作为备忘录或注释来提醒用户目前正定义的列指向另一个表中的一个列