深入浅出mysql_sql优化

十八. sql 优化


18.1 优化 SQL 语句的一般步骤


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


语法：


SHOW [SESSION|GLOBAL] STATUS 
服务器状态信息，session级：当前连接的统计结果；
GLOBAL级：自数据库上次启动至今的统计结果； 默认参数是 session；


范例


下面的命令显示了当前 session 中所有统计参数的值：
mysql> show status like 'Com_%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_event | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 1 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
……


返回参数说明
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：慢查询的次数。


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


通过慢查询日志定位那些执行效率较低的 SQL 语句，用--log-slow-queries[=file_name]选项启动时，mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。具体可以查看本书第 26 章中日志管理的相关部分。


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


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


是什么


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


范例


mysql> explain select sum(moneys) from sales a,company b where a.company_id = b.id and a.year
= 2006\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
type: ref
possible_keys: ind_company_id
key: ind_company_id
key_len: 5
ref: sakila.a.company_id
rows: 1
Extra: Using where; Using index
2 rows in set (0.00 sec)


返回字段解析：


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


2.table：输出结果集的表。


3. type：表示表的连接类型，性能由好到差的连接类型为
 system（表中仅有一行，即常量表）、
 const（单表中最多有一个匹配行，例如 primary key 或者 unique index）、
 eq_ref（对于前面的每一行，在此表中只查询一条记录，简单来说，就是多表连接中使用 primary key或者 unique index）、
 ref （与 eq_ref 类似，区别在于不是使用 primarykey 或者 unique index，而是使用普通的索引）、
 ref_or_null（与 ref 类似，区别在于条件中包含对 NULL 的查询）、
 index_merge(索引合并优化)、
 unique_subquery（in的后面是一个查询主键字段的子查询）、
 index_subquery（与 unique_subquery 类似，区别在于 in 的后面是查询非唯一索引 字段的子查询）、
 range（单表中的范围查询）、
 index（对于前面的每一行，都通过查询索引来得到数据）、
 all（对于前面的每一行，都通过全表扫描来得到数据）。


type:表示mysql在表中找到所需行的方式，或者叫访问类型，常见的类型有：
性能一次变好：ALL、INDEX、RANGE、REF、EQ_REF、const，system、NULL：


type = ALL 全表扫描、MySQL遍历全表找到匹配的行


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


type=range 索引范围扫描，常见于 <、 <=、 >、 >=、 between 等操作


type=ref 使用非唯一索引扫描 或者使用唯一索引的前缀扫描，返回匹配某个单独值的记录行


type=eq_ref 类似ref 区别在于使用的索引是唯一索引，对于每个索引键表中只有 一条记录匹配到；简单的说 就是多表连接中使用primary key 或者 unique index 作为关联条件。


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


type = NULL 不用访问表或者索引就行直接得到结构
例如 SELECT 1 from dual WHERE 1；


type的类型还有其他值：
ref_or_nul：和ref类似 区别在于条件中包含了对null的查询
index_merge： 索引合并优化
unique_subquery :in的后面是一个查询主键字段的子查询
index_subquery:与unique_subquery类似，区别在于in的后面是一个查询非唯一索引字段的子查询；


4 possible_keys：表示查询时，可能使用的索引。


5 key：表示实际使用的索引。


6 key_len：索引字段的长度。


7 rows：扫描行的数量。


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


18.1.4 通过show profile 分析sql


是什么


通过profile，我们能够清楚的知道SQL的执行过程。 5.0.37后 加入了对show profile 和show profiles的支持。


通过 having_profiling参数，能够查询是否支持profile
select @@profiling 
默认是关闭的，通过设置set 语句可以开启session级别的profiling:
set profiling=1;


范例


例子
>select count(*) from  payment:
>show profiles; //可以查询到该sql 的query ID为4；假设为4
>show profile for query 4; //能够查询到执行过程中线程的每个状态和消耗的时间；


show profile 能够帮助我们在做sql 优化时知道时间都消耗到哪里去了


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


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


使用方式：
首先打开track，设置格式为JSON，设置track最大能有使用内存的大小，避免解析过程中因为内存过小而不能完整显示。
>set optimizer_trace="enable=on",END_MARKERS_IN_JSON=ON;
>set optimizer_trace_max_mem_size = 1000000;
接下来执行想做trace的SQL语句。
select * from  .....;


最后 检查 INFORMATION_SCHEMA.OPTIMIZER_TRACE 就可以知道mysql 是如何执行sql 的
>SELECT * FROM INFORMATION.OPTIMIZER_TRACE \G;//会输出一个json格式的跟踪文件。


18.1.6.确定问题 并采取响应的优化措施


经过上述步骤，基本可以确定哪里出了问题。此时可以根据响应问题才是措施


18.2 索引问题


索引是数据库优化中最常用也是最重要的手段之一。通过索引可以帮助用户解决大多数sql性能问题


18.2.1 索引的存储分类


概述：
索引是在存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同。mysql 目前提供一下四种索引


B_TREE索引


最常见的索引，大部分引擎都支持。


HASH索引


相对简单，只有memory和heap引擎支持hash索引。hash索引适用于key-VALUES 查询，通过hash索引要比BTREE索引查询更迅速；
hash索引不支持范围查询，例如<, > ,>= <= 这类操作。并且只有 在 = 的条件下才会使用where索引。


只有memory 引擎支持，使用场景简单。


R_TREE索引（空间索引）


空间索引是MyISAM的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍


Full_Text索引（全文索引）


也是MyISAM的一个特殊索引类型，InnoDB从5.6开始支持Full Text 索引。


18.2.2 MySQL 如何使用索引


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


1）匹配全值（match the full value）


对索引中所有的列都指定了具体的值，即对索引中所有列都有等值匹配条件；


2）匹配值的范围查询，（match a range of values）


对索引的值能够进行范围查找。


3）匹配最左前缀（match a left most prefix）


仅仅使用索引中最左边的列进行查找，
比如在col1+col2+col3字段上的联合索引能够被包含 col1、（col1+col2）、（col1+col2+col3）的等值列查询到，可是不能够被col2、（col2+col3）的等值查询列利用到；


4）仅仅对索引进行查询（index only query）


当查询的列都在索引字段中时，查询的效率更高，
查看执行计划
extra ：using index ； 也就是意味着直接访问索引就能够查询到所需要的数据，不需要通过索引回表，using index 也就是平常所说的覆盖索引扫描；


5）匹配列前缀（match a column prefix）


仅仅使用索引中的第一列，并且只包含索引第一列开头一部分进行查找。
例如：select ...from .. where title like  'aa%';


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


7）如果列名是索引 ，那么使用column_name is  null 就会使用索引


例如 a、b、c 是索引
select * from where a is null;


8)5.6 引入了index condition pushdown 的特征。进一步优化查询；pushdown 表示放下，某些情况下条件过滤操作下放到存储引擎；


2 存在索引但不能使用缩印的典型场景


1 以%开头的LIKE查询不能够利用B-tree索引


解决办：
1 全文索引
2.轻量级解决办法，使用二级索引；


2 数据类型出现隐式转换的时候也不会使用索引。


特别是当列的类型是字符串的时候，那么一定要记得在where条件中把字符串常量用引号引起来，否则即便这个列上有索引，MySQL也不会用到。


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


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


5.用or分割开的条件。


如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会用到。
因为or后面的条件中没有索引，那后面的查询肯定要走全表扫描，在存在全表扫描的情况下，就没有必要多一次索引扫描增加io访问。


18.2.3 查看索引的使用情况


语法： 
SHOW STATUS LIKE ‘HANDLE_READ%’；

返回值说明：
如果Handler_read_key 的值很高的话，这个值代表一个行别索引值读的次数，很低的话说明增加索引得到的性能改善不高，因为索引并不是经常用到；
Handler_read_rnd_next 的值很高说明运行效率很低，并且应该建立索引补救。这个值 的含义在数据文件中读下一行的请求数。如果正在运行大量的表扫描 Handler_read_rnd_next将很高，则通常说明表索引不正确 或者写入的查询没有用到索引


18.3 两个简单实用的优化方法


定期分析表和检查表


分析表:


是什么


本语句用于分析和存储表的关键字分布，分析的结果将可以使得系统得到准确的统计信息，使得sql能够生成正确的执行计划。
如果用户感觉执行计划并不是预期的执行计划，执行一次分析表可能会解决问题。这对MYISAM 和 InnoDB 和bdb有作用。


语法：


ANALYZE [LOCAL|  NO_WRITE_BINLOG] TABLE table_name [,.....]


范例


mysql> analyze table sales;
+--------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+---------+----------+----------+
| sakila.sales | analyze | status | OK |
+--------------+---------+----------+----------+
1 row in set (0.00 sec)


检查表


是什么


检查表的作用是检查一个或多个表是否有错误。CHECK TABLE 对 MyISAM 和 InnoDB 表有作用。


语法


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


范例


mysql> check table sales;
+--------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| sakila.sales | check | status | OK |
+--------------+-------+----------+----------+
1 row in set (0.00 sec)


定期优化表：


是什么


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


语法


OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE table_name


18.4 常用 SQL 的优化


18.4.1 大批量插入数据


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


对于MyISAM的表：


可以通过 ,
ALTER TABLE table_name DISABLE KEYS;
loadind the data:
ALTER TABLE table_name ENABLE KEYS;
DISABLE/ENABLE KEYS 用来关闭 或打开 MyISAM表非唯一索引的更新，在导入大量的数据到一个非空的MyISAM表时，通过设置这两个命令可以提高导入的效率。
对于导入一个空的MyISAM表磨人的就是先导入数据再生成索引。因此不用设置。


对于InnoDB表


1.因为InnoDB表是按照主键顺序保存的，所以将导入的顺序按照主键的顺序保存，可以有效的提高导入效率。


2、在导入数据前 先设置 SET UNIQUE_CHECK=0;关闭唯一性校验。导入结束后，设置SET UNIQUE_CHECK=1 开启唯一性校验。


3.如果应用使用自动提交的方式，建议在导入前执行，SET AUTOCOMMIT=0 ;关闭自动提交，导入完成后再开启，SET AUTOCOMMIT=1;


18.4.2 优化 INSERT 语句


1如果同时从同一客户插入很多行，应尽量使用多个值表的INSERT语句，这样大大缩短了客户端与数据库之间连接关闭等消耗。这样比使用单个INSET 语句更快。


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


3.将索引文件和数据文件分在不通的磁盘上进行存储。（创建表是完成）


4. 如果进行匹狼插入，可以通过增加 bulk_insert_buffer_size 变量值的方法来提高速度。


5. 当从一个文件载入表时， 使用LOAD DATA INFILE 比通常使用很多insert 快20倍。


18.4.3 优化 ORDER BY 语句


mysql中两种排序方式：


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


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


fileSort 是通过相应的排序算法，将取得的数据在sort_buffer_size系统变量设置的内存排序区中进行排序，如果内存装载不下，就会将磁盘中的数据进行分块，然后在对各个数据块进行排序，最后将各个数据块合并。sort_buffer_size是每个线程独占的。


了解了以上mysql的排序方式，优化目标就清晰了：


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


filesort 的优化
有些条件限制下 不能让filesort消失。就要想办法加快filesort的操作。
对于filesort 有两种排序算法


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


一次扫描算法：
一次性取出所有满足条件的操作，然后在sort_buffer中排序一次性输出所有结果。
排序的内存开销大，但是效率高。


18.4.4 优化GROUP BY  语句：


默认情况下 MySQL对所有group by col1，col2 ... 的字段进行排序。这与在查询中制定order by col1，col2...类似 ，因此显式的包括一个包含相同列的order by子句相同，对mysql的执行效率没有影响；


18.4.5 优化嵌套查询


有些情况下 嵌套查询能被更有效的连接（join）取代、因为join 不需要使用临时表


18.4.6 MySQL 如何优化 OR 条件


对于含有OR的查询子句，如果要利用索引，则OR之间的每个条件都要用到索引，如果没有索引，则考虑增加索引列。（否则会用到全表扫描，详细 阅读索引优化）


18.4.7优化分页查询


第一种优化思路：
在分页索引上完成排序分页操作，然后在根据返回的数据 回原表查询其他的列


第二种优化思路：
 把limit 换成某个位置的查询。 比如返回最后一条id 取大于这个id的


18.4.8 使用SQL提示（SQL HINT）




是什么


简单来说 就是在SQL语句中加入一些人为的提示来达到优化操作的目的。


1 USE INDEX 


来提示希望mysql去参考的索引列表,就可以让mysql不再考虑其他可用的索引，
例如：
SELECT * FORM tablename USE INDEX(index_name)


2 IGNORE INDEX


 单纯的让MySQL忽略一个或多个索引
例如：
SELECT * FROM table_name IGNORE INDEX(inde_name1)


3 FORCE INDEX 强制mysql使用一个特定的索引


例如：
SELECT * FROM table_name FORCE INDEX（index_name） WHERE id>1;


常用SQL技巧


1正则表达式的使用


利用 REGEXP 进行模糊匹配 （是区分大小写的）
例如：
SELECT 'abc' REGEXP '^a' :
返回1


2巧用RAND 提取随机行


利用 ORDER BY RAND():
能够把数据随机排序。对于随机抽取样本对总体的统计有十分重要的意义。


3. 利用GROUP BY 的WITH ROLLUP 子句


4 用BIT GROUP FUNCTIONS 做统计


利用 GORUP BY、BIT_AND、 BIT_OR完成统计工作。


6  数据库名、表名大小写问题。


数据库名、表名 大小写是否敏感取决于操作系统(因为表名至少对应一个文件)
列名、索引名、存储子程序 和触发器名在任何平台大小写都不敏感。


7 使用外键注意的问题


只对 InnoDB存储引擎有效， 当使用REFERENCES table_name(col)