18 SQL优化--查询优化

优化SQL语句的一般步骤

1.通过show status 命令了解各种sql的执行频率

在Mysql客户端连接成功后，可以使用show [session | global] status;命令获得服务器状态信息,默认是session。或者在CMD中使用Mysqladmin extended-status;命令获得信息。

例如：show status like 'Com_%'; Com_xxx表示每个xxx语句执行的次数，通常我们关心以下几个参数。

Com_select:执行select的次数，执行一次累加一次。

Com_insert:执行insert的次数，对于批量插入只记为一次。

Con_update:执行update的次数。

Com_delete:执行delete的次数。

上面的这些参数对于所有的表都会进行累加，下面的只针对于InnoDB引擎，累加的算法也略有不同。

Innodb_rows_read:select查询返回的行数。

Innodb_rows_inserted:执行Insert插入的行数。

Innodb_rows_updated:执行update更新的行数。

Innodb_rows_deleted:执行delete删除的行数。

通过以上几个参数，可以很容易的了解当前数据库的应用是以插入为主还是查询为主。

对于事务型的应用，通过Com_commit和Com_rollback可以了解情况，如果回滚操作非常频繁，则意味着需要重新编写。

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

Connections:试图连接Mysql服务器的次数。

Uptime:服务器工作时间。

Slow_queries：慢查询的次数。

 

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

法1:通过查看慢查询日志定位。用--log-slow-queries[=file_name]选项启动时，mysqld写一个包含所有执行时间超过long_query_time秒的sql语句的日志文件。

法2：慢查询日志在查询结束后才可以记录，可以使用show processlist ;查看当前Mysql在进行的线程，包括线程状态，是否锁表等，可以实时查看SQL的执行情况。

 

 

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

Explain的详细用法和explain的列解释在pdf文件mysql_explain中有详细说明，请常看。

 

 

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

一般为添加索引给查询type为all或者extra为fliesort的表。

 

 

 

索引问题

查看某个表中的索引:show index form table \G;

 

1.使用索引

a.Mysql中使用索引，对于多列索引，只有使用最左边的列才能使用索引。

b.对于like查询，后面如果是常量并且%不在第一个字符才能使用索引。另外如果like后面跟的是一个列的名字，也不会使用索引。

c.如果对大文本进行搜索，必须使用全文索引。

d.如果列明是索引，则使用查询...where name is null也是使用了索引。

 

2.存在索引不使用索引

A.如果mysql估计使用索引比全表索引慢，则不会。如列key均分布在1~100之间，则下列查询使用索引就不好.

Select * from table where key > 10 and key <80 ;

B.如果使用Memory/heap表并且where 条件中不使用 = 进行判断，则不会使用索引，heap表只有在 = 条件下才能使用索引。

C.用or分割开的条件，如果or前的条件中的列有索引而后面的列没有索引，则涉及的索引都不会使用。

 

D.组合索引使用的不是最左边的列

F.如果like是以%开始的

E.如果列类型是string类型，那么一定要把where语句中的字符常量用引号引起来。如：explain select * from table where name = '247' \G ; 如果不加引号，因为name的索引为string而常量为int，所以不会使用索引。

 

 

 

查看索引的使用情况

如果索引正在工作，Handler_read_key的值将很高。如果这个值不高，代表增加索引的性能改善不高。

Handle_read_rnd_next的值高意味着查询效率低，并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。

如:show status like 'Handle_read%' ;