MySQL 优化篇之SQL优化

SQL优化

优化SQL的一般步骤

1.通过show status和应用特点了解各种SQL的执行频率

通过SHOW STATUS可以提供服务器状态信息，也可以使用mysqladmin extended-status命令获得。SHOW STATUS可以根据需要显示session级别的统计结果和global级别的统计结果。
以下几个参数对Myisam和Innodb存储引擎都计数：
1). Com_select 执行select操作的次数，一次查询只累加1；
2). Com_insert 执行insert操作的次数，对于批量插入的insert操作，只累加一次；
3). Com_update 执行update操作的次数；
4). Com_delete 执行delete操作的次数；
以下几个参数是针对Innodb存储引擎计数的，累加的算法也略有不同：
1). Innodb_rows_read select查询返回的行数；
2). Innodb_rows_inserted执行Insert操作插入的行数；
3). Innodb_rows_updated 执行update操作更新的行数；
4). Innodb_rows_deleted 执行delete操作删除的行数；
通过以上几个参数，可以很容易的了解当前数据库的应用是以插入更新为主还是以查询操作为主，以及各种类型的SQL大致的执行比例是多少。对于更新操作的计数，是对执行次数的计数，不论提交还是回滚都会累加。
对于事务型的应用，通过Com_commit和Com_rollback可以了解事务提交和回滚的情况，对于回滚操作非常频繁的数据库，可能意味着应用编写存在问题。
此外，以下几个参数便于我们了解数据库的基本情况：
1). Connections 试图连接Mysql服务器的次数
2). Uptime 服务器工作时间
3). Slow_queries 慢查询的次数
2.定位执行效率较低的SQL语句

可以通过以下两种方式定位执行效率较低的SQL语句：
1).可以通过慢查询日志定位那些执行效率较低的sql语句，用–log-slow-queries[=file_name]选项启动时，mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。可以链接到管理维护中的相关章节。

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

通过以上步骤查询到效率低的SQL后，我们可以通过explain或者desc 获取MySQL如何执行SELECT语句的信息，包括select语句执行过程表如何连接和连接的次序。 explain可以知道什么时候必须为表加入索引以得到一个使用索引来寻找记录的更快的SELECT。
mysql>expain select * from table;
select_type： select 类型
table：输出结果集的表
type：表示表的连接类型, 当表中仅有一行是type的值为system是最佳的连接类型；当select操作中使用索引进行表连接时type的值为ref；当select的表连接没有使用索引时，经常会看到type的值为ALL，表示对该表进行了全表扫描，这时需要考虑通过创建索引来提高表连接的效率。
possible_keys：表示查询时,可以使用的索引列.
key：表示使用的索引
key_len：索引长度
rows：扫描范围
Extra：执行情况的说明和描述
1
2
3
4
5
+----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
| id | select_type | table      | type   | possible_keys | key  | key_len | ref  | rows | Extra               |
+----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
|  1 | SIMPLE      | tab_serial | system | NULL          | NULL | NULL    | NULL |    0 | const row not found |
+----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+

4.确定问题，并采取相应的优化措施

经过以上步骤，基本可以确认问题出现的原因，可以根据情况采取相应的措施，进行优化提高执行的效率。

索引问题

1.索引的存储分类

myisam表的数据文件和索引文件是自动分开的；innodb的数据和索引是存储在同一个表空间里面，但可以有多个文件组成。
索引的存储类型目前只有两种（btree和hash），具体和表的模式相关：
myisam btree
innodb btree
memory/heap hash，btree
mysql目前不支持函数索引，只能对列的前一部分（length）进行索引，例：
create index ind_test on table1(name(5))，
对于char和varchar列，使用前缀索引将大大节省空间。
2.MySQL如何使用索引

索引用于快速找出在某个列中有一特定值的行。对相关列使用索引是提高SELECT操作性能的最佳途径。查询要使用索引最主要的条件是查询条件中需要使用索引关键字，如果是多列索引，那么只有查询条件使用了多列关键字最左边的前缀时，才可以使用索引，否则将不能使用索引。
下列情况下，Mysql不会使用已有的索引：
1). 如果mysql估计使用索引比全表扫描更慢，则不使用索引。
例如：如果key_part1均匀分布在1 和100之间，下列查询中使用索引就不是很好：
SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90
2). 如果使用heap表并且where条件中不用＝索引列，其他> 、<、 >=、 <=均不使用索引；
3). 如果不是索引列的第一部分；
4). 如果like是以％开始；
5). 对where后边条件为字符串的一定要加引号，字符串如果为数字mysql会自动转为字符串，但是不使用索引。
3.查看索引使用情况

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

两个简单实用的优化方法

1.定期分析表

ANALYZE TABLE 语法：
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
本语句用于分析和存储表的关键字分布。在分析期间，使用一个读取锁定对表进行锁定。这对于MyISAM, BDB和InnoDB表有作用。对于MyISAM表，本语句与使用myisamchk -a相当。
CHECK TABLE 语法：
CHECK TABLE tbl_name [, tbl_name] ... [option] ... option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
检查一个或多个表是否有错误。CHECK TABLE对MyISAM和InnoDB表有作用。对于MyISAM表，关键字统计数据被更新。 CHECK TABLE也可以检查视图是否有错误，比如在视图定义中被引用的表已不存在。
CHECKSUM TABLE 语法：
CHECKSUM TABLE tbl_name [, tbl_name] ... [ QUICK | EXTENDED ]
报告一个表校验和。
2.使用optimize table

OPTIMIZE TABLE 语法：
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
如果已经删除了表的一大部分，或者如果您已经对含有可变长度行的表（含有VARCHAR, BLOB或TEXT列的表）进行了很多更改，则应使用OPTIMIZE TABLE。被删除的记录被保持在链接清单中，后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新利用未使用的空间，并整理数据文件的碎片。 OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。

常用SQL的优化

1.大批量插入数据

1). 对于Myisam类型的表，可以通过以下方式快速的导入大量的数据。
ALTER TABLE tblname DISABLE KEYS; loading the data ALTER TABLE tblname ENABLE KEYS;
这两个命令用来打开或者关闭Myisam表非唯一索引的更新。在导入大量的数据到一个非空的Myisam表时，通过设置这两个命令，可以提高导入的效率。对于导入大量数据到一个空的Myisam表，默认就是先导入数据然后才创建索引的，所以不用进行设置。
2). 而对于Innodb类型的表，这种方式并不能提高导入数据的效率。对于Innodb类型的表，我们有以下几种方式可以提高导入的效率：
a. 因为Innodb类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果Innodb表没有主键，那么系统会默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这个优势提高导入数据的效率。
b. 在导入数据前执行SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。
c. 如果应用使用自动提交的方式，建议在导入前执行SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。
2.优化insert语句

1). 如果你同时从同一客户插入很多行，使用多个值表的INSERT语句。这比使用分开INSERT语句快 (在一些情况中几倍)。
Insert into test values(1,2),(1,3),(1,4)„
2). 如果你从不同客户插入很多行，能通过使用INSERT DELAYED语句得到更高的速度。Delayed的含义是让insert 语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘；这比每条语句分别插入要快的多；LOW_PRIORITY刚好相反，在所有其他用户对表的读写完后才进行插入；
3). 将索引文件和数据文件分在不同的磁盘上存放（利用建表中的选项）；
4). 如果进行批量插入，可以增加bulk_insert_buffer_size变量值的方法来提高速度，但是，这只能对myisam表使用；
5). 当从一个文本文件装载一个表时，使用LOAD DATA INFILE。这通常比使用很多INSERT语句快20 倍；
6). 根据应用情况使用replace语句代替insert；
7). 根据应用情况使用ignore关键字忽略重复记录。
3.优化group by语句

默认情况下，MySQL排序所有GROUP BY col1，col2，….。查询的方法如同在查询中指定ORDER BY col1，col2，…。如果显式包括一个包含相同的列的ORDER BY子句，MySQL可以毫不减速地对它进行优化，尽管仍然进行排序。如果查询包括GROUP BY但你想要避免排序结果的消耗，你可以指定ORDER BY NULL禁止排序。
例如：INSERT INTO foo SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;
4.优化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;
但是以下情况不使用索引：
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：
5.优化join语句

Mysql4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接(JOIN).. 替代。假设我们要将所有没有订单记录的用户取出来，可以用下面这个查询完成:
SELECT * FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )
如果使用连接(JOIN).. 来完成这个查询工作，速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好，查询如下:
SELECT * FROM customerinfo LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.CustomerID WHERE salesinfo.CustomerID IS NULL
连接(JOIN).. 之所以更有效率一些，是因为 MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。
6.mysql如何优化or条件
对于or子句，如果要利用索引，则or之间的每个条件列都必须用到索引；如果没有索引，则应该考虑增加索引。
7查询优先还是更新（insert、update、delete）优先

MySQL还允许改变语句调度的优先级，它可以使来自多个客户端的查询更好地协作，这样单个客户端就不会由于锁定而等待很长时间。改变优先级还可以确保特定类型的查询被处理得更快。我们首先应该确定应用的类型，判断应用是以查询为主还是以更新为主的，是确保查询效率还是确保更新的效率，决定是查询优先还是更新优先。下面我们提到的改变调度策略的方法主要是针对Myisam存储引擎的，对于Innodb存储引擎，语句的执行是由获得行锁的顺序决定的。 MySQL的默认的调度策略可用总结如下：
1). 写入操作优先于读取操作。
2). 对某张数据表的写入操作某一时刻只能发生一次，写入请求按照它们到达的次序来处理。
3). 对某张数据表的多个读取操作可以同时地进行。 MySQL提供了几个语句调节符，允许你修改它的调度策略：
a. LOW_PRIORITY关键字应用于DELETE、INSERT、LOAD DATA、REPLACE和UPDATE。
b. HIGH_PRIORITY关键字应用于SELECT和INSERT语句。
c. DELAYED关键字应用于INSERT和REPLACE语句。
如果写入操作是一个LOW_PRIORITY（低优先级）请求，那么系统就不会认为它的优先级高于读取操作。在这种情况下，如果写入者在等待的时候，第二个读取者到达了，那么就允许第二个读取者插到写入者之前。只有在没有其它的读取者的时候，才允许写入者开始操作。这种调度修改可能存在LOW_PRIORITY写入操作永远被阻塞的情况。 SELECT查询的HIGH_PRIORITY（高优先级）关键字也类似。它允许SELECT插入正在等待的写入操作之前，即使在正常情况下写入操作的优先级更高。另外一种影响是，高优先级的SELECT在正常的SELECT语句之前执行，因为这些语句会被写入操作阻塞。如果你希望所有支持LOW_PRIORITY选项的语句都默认地按照低优先级来处理，那么请使用–low-priority-updates选项来启动服务器。通过使用INSERT HIGH_PRIORITY来把INSERT语句提高到正常的写入优先级，可以消除该选项对单个INSERT语句的影响。
8.使用SQL提示
SELECT SQL_BUFFER_RESULTS ...

将强制MySQL生成一个临时结果集。只要所有临时结果集生成后，所有表上的锁定均被释放。这能在遇到表锁定问题时或要花很长时间将结果传给客户端时有所帮助。当处理一个会让客户端耗费点时间才能处理的大结果集时，可以考虑使用SQL_BUFFER_RESULT 提示字。这样可以告诉MySQL将结果集保存在一个临时表中，这样可以尽早的释放各种锁。
USE INDEX
在你查询语句中表名的后面，添加USE INDEX 来提供你希望MySQ去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。
Eg:SELECT * FROM mytable USE INDEX (mod_time, name) ...
IGNORE INDEX
如果你只是单纯的想让MySQL忽略一个或者多个索引，可以使用IGNORE INDEX作为Hint。
Eg:SELECT * FROM mytale IGNORE INDEX (priority) ...
FORCE INDEX
为强制MySQL使用一个特定的索引，可在查询中使用FORCE INDEX作为Hint。
Eg:SELECT * FROM mytable FORCE INDEX (mod_time) ...

其他优化措施

使用持久的连接数据库以避免连接开销。
经常检查所有查询确实使用了必要的索引。
避免在频繁更新的表上执行复杂的SELECT查询，以避免与锁定表有关的由于读、写冲突发生的问题。
对于没有删除的行操作的MyISAM表，插入操作和查询操作可以并行进行，因为没有删除操作的表查询期间不会阻塞插入操作．对于确实需要执行删除操作的表，尽量在空闲时间进行批量删除操作，避免阻塞其他操作。
充分利用列有默认值的事实。只有当插入的值不同于默认值时，才明确地插入值。这减少MySQL需要做的语法分析从而提高插入速度。
对经常访问的可以重构的数据使用内存表，可以显著提高访问的效率。
通过复制可以提高某些操作的性能。可以在复制服务器中分布客户的检索以均分负载。为了防止备份期间对应用的影响，可以在复制服务器上执行备份操作。
表的字段尽量不使用自增长变量，在高并发情况下该字段的自增可能对效率有比较大的影响，推荐通过应用来实现字段的自增长。