SQL优化

SQL优化的一般步骤
先查询mysql数据库运行状况。然后定位慢查询，再分析sql的执行过程，然后进行优化

1.使用show status查询数据库的运行状况

//显示数据库运行状态
SHOW STATUS
//显示数据库运行总时间
SHOW STATUS LIKE 'uptime'
//显示连接的次数
SHOW STATUS LIKE 'connections'
//显示执行CRUD的次数
SHOW STATUS LIKE 'com_select'
SHOW STATUS LIKE 'com_insert'
SHOW STATUS LIKE 'com_update'
SHOW STATUS LIKE 'com_delete'

2.开启并记录慢查询

mysql设置的默认的慢查询时间为10s。通常这个数值过大，可改为1s。

//显示慢查询次数
SHOW STATUS LIKE 'slow_queries'
//显示慢查询时间，默认为10s
SHOW VARIABLES LIKE 'long_query_time'

可以修改慢查询时间
①.在my.ini文件中修改(global级别，永久生效)
修改my.ini配置文件中的long_query_time参数即可。修改后需重启，永久生效。
以下是本人机器上安装的percona(基于mysql的开源数据库)有关慢查询的配置信息

//开启慢查询
slow-query-log=1
//慢查询的文件路径
slow_query_log_file="D:/Program Files/MySQL/Log/mysql-slow.log"
//慢查询时间。默认为10秒，可以根据具体需求修改,通常改为1s。
long_query_time=10

②.使用命令修改(session级别，临时生效)

//修改慢查询时间
SET long_query_time = 1(sssion级别，关闭窗口后会还原)

3.使用explain分析sql语句执行过程

mysql会将慢查询记录到慢查询日志中，这时我们就可以针对这些慢查询的sql进行分析和优化。需要用到explain命令。

expain的用法：
只需在select语句前加一个explain即可。

每一个数据项的含义：

数据项 含义

id	SELECT识别符，SELECT的查询序列号；
select_type	SELECT类型。可以是SIMPLE（简单查询）、PRIMARY（最外面的select），UNION(UNION中的第二个或后面的查询语句)，等；
table	用到的表
type	联接类型
possible_keys	可用索引列
key	实际用到的索引列
key_len	键长度
ref	使用哪个列或常数与key一起从表中选择行
rows	检查的行数
Extra	该列包含MySQL解决查询的详细信息

4.使用show profile分析sql

Mysql从5.0.37开始增加了对show profiles和show profile语句的支持。
默认profile是关闭的。可以通过set开启session级别的profiling：

//查看是否支持profile
SELECT @@have_profiling
//开启profile
set profiling=1;

①执行select语句
②show profiles,查询该sql语句的Query ID.
③通过show profile for query语句能看到执行中线程的每个状态和消耗的时间。

show profile for query [上面的query id]

5.sql优化

索引

索引分类：
1.主键索引(primary key)：
创建表时，将某列设为主键，即为主键索引。
也可以在创建表后再单独添加：

alter table 表名 add primary key(列名)

2.唯一索引(unique)
当表的某列被指定为unique约束时，这列就是一个唯一索引。

create table ddd(       
   id int primary key auto_increment ,          
   name varchar(32) unique      
);

3.全文索引(full text)
①.全文索引仅在MyISAM引擎中支持。(mysql5.6版本中已开始支持全文索引了)
②.全文索引只能用于英文，如果出现中文，还是应该使用Lucene等第三方开源框架来处理。

4.普通索引(index)

//创建索引
create index 索引名 on 表名(列名)
//查询索引
show index(es) from 表名

索引的注意点：
1.索引会占用存储空间。
2.适合添加索引的字段。
①频繁作为查询条件的字段应该创建索引。也就是where后面的字段
②唯一性太差的字段不适合单独创建索引

select * from emp where sex='男' 

③频繁变化的字段不应该创建索引

select * from emp where logincount=1

3.能够使用索引的典型场景【未完成】

4.存在索引但不使用索引的典型场景
①以%开头的LIKE查询不能够利用B-tree索引。

select * from emp where last_name like '%NI%'（不会用索引）

这种情况，推荐使用全文索引(fulltext)。
②数据类型中出现隐式转换时不会用到索引。特别是当列类型为字符串时，一定要在where条件中将字符串常量值用引号引起来，否则不会用到索引。

select * from actor where last_name= 1 （不会用索引）
select * from actor where last_name='1'（会用到索引）

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

select * from payment where amount=3.98 and last_update='2006-01-15 22:12:32'（不会用索引）

④用or分割开的条件，如果or前的条件中的列有索引，而后面的列没有索引，那么涉及的索引都不会被用到(前面的索引也不会用到)。
原因：因为or后面的条件没有索引，必然会走全表扫描，在全表扫描情况下，就没有必要多一次索引扫描增加IO访问，一次全表扫描过滤条件就已经足够了。

select * from payment where customer_id=203 or amount = 3.1(不会用到索引。其中customer_id建立了索引)

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

常用的sql优化

上面总结了通过索引来优化查询。对于INSERT、GROUP BY等常用的操作，也有相应的优化方法。

1.大批量插入数据

对于MyISAM表，当用load命令导入数据时，可以有如下几种方式提高导入效率。

ALTER TABLE 表名 DISABLE KEYS;//关闭非唯一索引的更新
……
导入数据
……
ALTER TABLE 表名  ENABLE KEYS;//打开非唯一索引的更新

在导入大量数据到一个非空的MyISAM表时，通过设置这两个命令可提高导入效率。
在导入大量数据到一个空的MyISAM表时，默认就是先导入数据然后才创建索引，所以不用进行设置。

对于Innodb表，有如下几种方式提高导入效率
①.因为Innodb类型的表是按照主键的顺序来保存的，所以将导入的数据按照主键的顺序排序，可以有效的提高导入数据的效率。

SET UNIQUE_CHECKS=0(关闭唯一性校验) 
SET UNIQUE_CHECKS=1(开启唯一性校验)

③.导入数据前，关闭自动提交。导入完成后，再开启。

SET AUTOCOMMIT=0(关闭自动提交) 
SET AUTOCOMMIT=1(开启自动提交)

2.优化INSERT语句

①如果从同一客户端批量插入多条记录，使用以下的插入。

insert into test values(1,2),(1,3),(1,4)……

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

3.优化ORDER BY语句

Mysql中有两种排序方式
1.通过有序的索引顺序扫描直接返回有序数据。不需要额外的排序，操作效率高。
2.通过返回数据进行排序(称为Filesort排序)
Filesort排序是否使用磁盘文件或临时表，取决于Mysql服务器对排序参数的设置和需要排序数据的大小。

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

4.优化GROUP BY语句

5.优化嵌套查询

6.优化OR条件

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

7.优化分页查询

<script type="text/javascript"> $(function () { $('pre.prettyprint code').each(function () { var lines = $(this).text().split('\n').length; var $numbering = $('<ul/>').addClass('pre-numbering').hide(); $(this).addClass('has-numbering').parent().append($numbering); for (i = 1; i <= lines; i++) { $numbering.append($('<li/>').text(i)); }; $numbering.fadeIn(1700); }); }); </script>