Mysql学习笔记九，优化篇，SQL优化

• 优化SQL的一般步奏:
• • 查看当前数据库状态

1 show [session|global] status  --显示统计结果

   mysqladmin extended-status --显示统计结果

2 其中session: 当前会话，  global自数据库上次启动至今。 默认session。

3 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	慢查询的次数。

• 定位执行效率低的sql语句

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

2 可以通过show processlist命令查看实时的sql执行情况。

mysql -udb2nor -p -e "show processlist">c.out --可以将查询结果dump到文件中。

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

explain select * from t1 ,t2 where t1.a <> t2.id \G;  --打印执行计划。

select_type	select的类型， 常见的取值有 SIMPLE （简单表， 即不使用表连接 或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或 者后面的查询语句）、SUBQUERY（子查询中的第一个 SELECT）
table	输出结果集的表
type	表的连接类型，性能由好到差: system, 表中仅有一行，即常量表。 const, 单表中最多有一个匹配行，例如 primary key 或者 unique index。 eq_ref,对于前面的每一行，在此表中只查询一条记录，简单来说，就是多表连接 中使用 primary key 或者 unique index。 ref(与 eq_ref 类似， 区别在于不是使用 primary key 或者 unique index，而是使用普通的索引） ref_or_null（与 ref 类似，区别在于 条件中包含对 NULL 的查询） index_merge(索引合并优化) unique_subquery（in 的后面是一个查询主键字段的子查询）  index_subquery （与 unique_subquery 类似， 区别在于 in 的后面是查询非唯一索引字段的子查询）  range （单表中的范围查询） index （对于前面的每一行， 都通过查询索引来得到数据）  all （对于前面的每一行， 都通过全表扫描来得到数据）
possible_keys	表示查询时，可能使用的索引
key	表示实际使用的索引
key_len	索引字段的长度
rows	扫描行的数量
Extra	执行情况的说明和描述

• 确定问题并采取优化。

1. 通过上面的步奏，定位到哪个SQL， 这个SQL出现了什么问题。

• 索引问题

 一. 使用索引:

1. 查询要使用索引最主要的条件是查询条件中需要使用索引关键字，如果是多列索引，那 么只有查询条件使用了多列关键字最左边的前缀时， 才可以使用索引， 否则将不能使用索引。
2. 对于创建的多列索引，只要查询的条件中用到了最左边的列，索引一般就会被使用。
3. 对于使用 like 的查询，后面如果是常量并且只有％号不在第一个字符，索引才可能会 被使用。如果 like 后面跟的是一个列的名字，那么索引也不会被使用。
4. 如果对大的文本进行搜索，使用全文索引而不用使用 like ‘%…%’。
5. 如果列名是索引，使用 column_name  is  null 将使用索引。

二.存在索引但不使用索引

1. 如果 MySQL 估计使用索引比全表扫描更慢，则不使用索引。例如如果列 key_part1 均匀分布在 1 和 100 之间，下列查询中使用索引就不是很好：SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90;
2. 如果使用 MEMORY/HEAP 表并且 where 条件中不使用“=”进行索引列，那么 不会用到索引。heap 表只有在“=”的条件下才会使用索引。
3. 用 or 分割开的条件， 如果 or 前的条件中的列有索引， 而后面的列中没有索引， 那么涉及到的索引都不会被用到。
4. 不是索引列的第一部分。
5. 如果 like 是以％开始。
6. 如果列类型是字符串，那么一定记得在 where 条件中把字符常量值用引号引 起来，否则的话即便这个列上有索引。

三.查看索引使用情况

1. 通过命令： show status like 'Handler_read%' 查看

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

 

• 实用的优化技巧
• • 定期分析表和检查表 : 

分析表	ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...	本语句用于分析和存储表的关键字分布， 分析的结果将可以使得系统得到准确的统计信 息，使得 SQL 能够生成正确的执行计划。如果用户感觉实际执行计划并不是预期的执行计 划， 执行一次分析表可能会解决问题。 在分析期间， 使用一个读取锁定对表进行锁定。 这对 于 MyISAM, BDB 和 InnoDB 表有作用。对于MyISAM 表，本语句与使用 myisamchk -a 相当。
检查表	CHECK TABLE tbl_name [, tbl_name] ... [option] ... option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}	1 检查表的作用是检查一个或多个表是否有错误。 CHECK TABLE 对 MyISAM 和 InnoDB 表有作用。 对于 MyISAM 表，关键字统计数据被更新。 2  可以用于检查视图。

• • 定期优化表

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

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

*  注意：ANALYZE、CHECK、OPTIMIZE 执行期间将对表进行锁定，因此一定注意要在数据库不

繁忙的时候执行相关的操作。

• 常用SQL的优化
• • 大批量插入数据 

MyISAM存储引擎的表，可以使用alter table xx disable keys, alter table xx enable keys,打开关闭myisam表非唯一性索引的更新。

innodb优化方式：

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

• • 优化insert语句

使用多个值表的 INSERT 语句	insert into test values(1,2),(1,3),(1,4)…
INSERT DELAYED	如果从不同客户插入很多行，能通过使用 INSERT DELAYED 语句得到更高的速度。 DELAYED 的含义是让 INSERT 语句马上执行，其实数据都被放在内存的队列中，并没有 真正写入磁盘，这比每条语句分别插入要快的多；LOW_PRIORITY 刚好相反，在所有其 他用户对表的读写完后才进行插入；
数据存放	将索引文件和数据文件分在不同的磁盘上存放（利用建表中的选项）
bulk_insert_buffer_size	如果进行批量插入，可以增加 bulk_insert_buffer_size 变量值的方法来提高速度，但是， 这只能对 MyISAM 表使用
LOAD DATA INFILE	当从一个文本文件装载一个表时， 使用 LOAD DATA INFILE。 这通常比使用很多 INSERT 语 句快 20 倍。

• • 优化group by 

默认情况下， MySQL 对所有 GROUP BY col1， col2....的字段进行排序用户想要避免排序结果的消耗，则可以指定 ORDER BY NULL  禁止排序:

select id from t2 group by id order by null;

• • 优化order by

MySQL 可以使用一个索引来满足 ORDER BY 子句， 而不需要额外的排序。

WHERE 条件和 ORDER BY 使用相同的索引，并且 ORDER BY 的顺序和索引顺序相同，并且

ORDER BY 的字段都是升序或者都是降序:

这些SQL的排序可以使用索引	SELECT * FROM t1 ORDER BY key_part1,key_part2,... ; SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC; SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
这些SQL的排序不能使用索引	SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC； --order by 的字段混合 ASC 和 DESC SELECT * FROM t1 WHERE key2=constant ORDER BY key1； --用于查询行的关键字与 ORDER BY 中所使用的不相同 SELECT * FROM t1 ORDER BY key1, key2； --对不同的关键字使用 ORDER BY：

• • 优化or条件

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

explain select * from t1 where t1.var = 'test' or t1.id = 4;--在var上没有索引，在id上有索引。

• • 使用sql提示

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

如： SELECT SQL_BUFFER_RESULTS * FROM... 这个语句将强制 MySQL 生成一个临时结果集。只要临时结果集生成后，所有表上的锁 定均被释放。 这能在遇到表锁定问题时或要花很长时间将结果传给客户端时有所帮助， 因为

可以尽快释放锁资源。

USE INDEX	在查询语句中表名的后面， 添加 USE INDEX 来提供希望 MySQL 去参考的索引列表， 就可 以让 MySQL 不再考虑其他可用的索引。  explain select * from t1 use index(t1_idx) where id =4; --建议mysql使用索引t1_idx
IGNORE INDEX	如果用户只是单纯地想让 MySQL 忽略一个或者多个索引，则可以使用 IGNORE INDEX 作 为 HINT。 explain select * from t1 ignore index(t1_idx) where id =4; --建议mysql忽略索引t1_idx
FORCE INDEX	为强制 MySQL 使用一个特定的索引，可在查询中使用 FORCE INDEX 作为 HINT。 explain select * from t1 force index(t1_idx) where id =4;