MySQL 优化SQL语句

1. show processlist

查看线程（可以用kill Id来终止线程，不过可能会损坏数据：比如在delete，alter database时）

2.show profiles

2.1 准备

首先查看数据库是否支持profiling，profiling是否开启

select @@have_profiling #是否支持
# 如果have_profiling 为 YES
select @@profiling  #是否开启
如果profiling=0，就set profiling = 1
则开启profiling

show profiles 查看Query_ID,再通过进一步的show profile for query query_ID(e.g. show profile for query 4)
可以看到执行过程中线程的每个状态和消耗的时间
其中的Sending data 状态表示MySQL线程开始访问数据行并把结果返回给客户端，而不仅仅是返回结果给客户端。由于在Sending data状态下，MySQL线程要做大量的磁盘读取操作，所以经常是整个查询中耗时最长的状态。

2.2 例子

• 1.首先执行下列查询语句

select cpu_use_rate,count(cpu_use_rate)
from terminal_data_file
GROUP BY cpu_use_rate
ORDER BY COUNT(cpu_use_rate) desc

查询时间9.132s

• 2.show profiles
  
  Query ID 为 63

• 3.show profile for query 63
  看到执行过程中线程的每个状态和消耗的时间
  

• 查询information_schema.profiling表并按时间做个DESC排序

SET @query_id := 70;
SELECT  STATE ,
    SUM(DURATION) AS Total_R,
        ROUND(100 * SUM(DURATION) / 
            (SELECT SUM(DURATION)
                FROM information_schema.PROFILING
                where QUERY_ID = @query_id 
            ),2) as Pct_R,
        COUNT(*) as Calls,
        SUM(DURATION) / COUNT(*)as 'R/call'
from information_schema.PROFILING
where QUERY_ID = @query_id
GROUP BY STATE
ORDER BY Total_R DESC

3.两个实用的优化方法

3.1 定期分析表和检查表

analyze table  tb_name
check table tb_name

3.2 定期优化表

optimize table tb_name[,tb_name]...

对于InnoDB引擎，通过设置innodb_file_per_table参数，设置InnoDB为独立表空间模式，这样每个数据库的每个表都会生成一个非独立的ibd文件，用于存储表的数据和索引，这样可以一定程度上减轻InnoDB的表空间回收问题，另外，在删除大量数据后，InnoDB表可以通过alter table 但是不修改引擎的方式来回收不用的空间。

alter table tb_name engine = innodb;

analyze, check, optimize ,alter table执行期间会对表进行锁定，因此要在数据库不繁忙的时候执行相关的操作。

4. 常用SQL优化

4. 1大批量插入数据

• MyISAM

alter table tb_name disable keys
loading the data
alter table tb_name enable keys

disable keys 和enable keys 用来打开或关闭MyISAM表非唯一索引的更新，在导入大量数据到一个非空的MyISAM表时，通过设置这两个命令，可以提高导入效率。
导入到空数据表时，默认先导入数据再创建索引。

alter table film_test2 disable keys;
load data infile '/home/mysql/film_test.txt' into table film_test2;
alter table film_test2 enable keys;

• InnoDB
  
  • 因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效提高导入数据的效率。what’s meaning？？？？
  • 导入数据前执行set unique_checks = 0关闭唯一性校验，在导入后执行set unique_check = 1,恢复唯一性校验
  • 如果应用使用自动提交的方式，在导入前执行set autocommit = 0,关闭自动提交，导入结束后打开set autocommit = 1自动提交。

4.2 优化 insert 语句

• 同时插入很多行时，尽量使用多个值表的insert语句，这样将大大缩减客户端与数据库之间的连接，关闭等消耗。如
  insert into test values(1,2),(1,3),(1,4)...
• 不同用户插入很多行：使用insert delayed语句
• 将索引文件和数据文件分在不同的磁盘上存放（利用建表中的选项）
• 批量插入时，增加bulk_insert_buffer_size变量值的方法来提高速度——只对MyISAM有用。
• 当从文本文件装载一个表时，使用load data infile,这比insert语句快20倍

4.3 优化 order by 语句

MySQL中两种排序方式

• using index（效率较高）

• using filesort

• 优化目标：尽量减少额外的排序，通过索引直接返回有序数据。
  手段：mysql对于排序,使用了两个变量来控制sort_buffer_size和 max_length_for_sort_data。

• 增大sort_buffer_size,让排序在内存中完成。
• 增大 max_length_for_sort_data，让MySQL选择更优化的Filesort排序算法。

show variables like '%sort_buffer_size%'
set sort_buffer_size = xxx,
set max_length_for_sort_data = xxx,
set SESSION sort_buffer_size=7000000  #当前会话退出后参数就还原 
set GLOBAL sort_buffer_size=7000000   #退出mysql再重启后参数改变
这些参数的改变在重启MYSQL服务的时候，都将失效复原，如果想要重启的 时候也载入，则需要修改配置文件

• 尽量不要使用select *

4.4 优化group by 语句

默认情况下，MySQL对所有group by col1, col2 …的字段进行排序，如果想避免排序结果的消耗，则可以指定order by null禁止排序。

4.5 优化嵌套查询

select * from customer 
where customer_id not in
    (
    select customer_id 
    from payment 
    )

使用JOIN来完成

select * 
from customer a left join payment b
on a.customer_id = b.customer_id 
where b.customer_id is null 

MySQL 5.5及以下版本中，子查询效率不如关联查询。JOIN更有效率是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。

4.6 优化 or 条件

4.7 优化分页查询

limit 1000,20 :排出1020条记录后返回1001-1020条，前1000条记录都被抛弃，查询代价非常高。
1. 在索引上完成排序分页的操作，最后根据主键关联回原表查询所需要的其他列内容。

select film_id, sescription 
from film 
order by title 
limit 50,5

改为

select a.film_id, a.description 
from film a inner join 
    (select film_id 
    from film 
    order by title 
    limit 50,5
    )b
on a.film_id = b.film_id