如何编写高性能sql语句

一、什么是执行计划？

1）执行计划

执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案，这个方案是由查询优化器自动分析产生的，比如一条SQL语句如果用来从一个 10万条记录的表中查1条记录，那查询优化器会选择“索引查找”方式，如果该表进行了归档，当前只剩下5000条记录了，那查询优化器就会改变方案，采用 “全表扫描”方式。 

可见，执行计划并不是固定的，它是“个性化的”。产生一个正确的“执行计划”有两点很重要：    

a、SQL语句是否清晰地告诉查询优化器它想干什么？  

b、查询优化器得到的数据库统计信息是否是最新的、正确的？

2）定期归档

上文中提到了表归档，那什么是归档？其实就是做一个数据库的存档。

例如：数据库有一张表数据量很大，真正WEB项目只用到一个月内的数据，因此把一个月前的旧数据定期归档，该怎么做？

1 - 创建一个新表，表结构和索引与旧表一模一样

create table table_new like table_old;

2 - 新建存储过程，查询30天的数据并归档进新数据库，然后把30天前的旧数据从旧表里删除

delimiter $
create procedure sp()
begin
insert into tb_new select * from table_old where rectime < NOW()  -  INTERVAL 30 DAY;
delete from db_smc.table_old where rectime < NOW() - INTERVAL 30 DAY;
end

3 - 创建EVENT，每天晚上凌晨00：00定时执行上面的存储过程

create event if not exists event_temp 
on schedule every 1 day
on completion preserve
do call sp();

备注：

第一次执行存储过程的时候因为历史数据过大, 可能发生意外让该次执行没有成功。重新执行时会遇到报错ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction，应急解决方案如下：

1、执行show full processlist;查看所有MySQL线程

2、执行SELECT * FROM information_schema.INNODB_TRX\G; 查看是否有错误线程，线程id在show full processlist;的结果中状态为sleep

3、kill 进程id

二、 统一SQL语句的写法

对于以下两句SQL语句，程序员认为是相同的，数据库查询优化器认为是不同的。      

select * from dual
select * From dual

其实就是大小写不同，查询分析器就认为是两句不同的SQL语句，必须进行两次解析。生成2个执行计划。所以作为程序员，应该保证相同的查询语句在任何地方都一致，多一个空格都不行！

三、SQL语句采用绑定变量   

select * from orderheader where changetime > '2010-10-20 00:00:01'
select * from orderheader where changetime > '2010-09-22 00:00:01'

以上两句语句，查询优化器认为是不同的SQL语句，需要解析两次。如果采用绑定变量

select * from orderheader where changetime > @chgtime

@chgtime变量可以传入任何值，这样大量的类似查询可以重用该执行计划了，这可以大大降低数据库解析SQL语句的负担。一次解析，多次重用，是提高数据库效率的原则。  

四、绑定变量窥测  

事物都存在两面性，绑定变量对大多数OLTP处理是适用的，但是也有例外。比如在where条件中的字段是“倾斜字段”的时候。

“倾斜字段”指该列中的绝大多数的值都是相同的，比如一张人口调查表，其中“民族”这列，90%以上都是汉族。那么如果一个SQL语句要查询30岁的汉族人口有多少，那“民族”这列必然要被放在where条件中。这个时候如果采用绑定变量@nation会存在很大问题。   

试想如果@nation传入的第一个值是“汉族”，那整个执行计划必然会选择表扫描。然后，第二个值传入的是“布依族”，按理说“布依族”占的比例可能只有万分之一，应该采用索引查找。但是，由于重用了第一次解析的“汉族”的那个执行计划，那么第二次也将采用表扫描方式。这个问题就是著名的“绑定变量窥测”，建议对于“倾斜字段”不要采用绑定变量。 

五、mysql分区表

分区表是一种粗粒度,简易的索引策略,适用于大数据的过滤场景.最适合的场景是,没有合适的索引时,对其中几个分区表进行全表扫描.或者只有一个分区表和索引是热点,而且这个分区和索引能够全部存储在内存中.限制单表分区数不要超过150个,并且注意某些导致无法做分区过滤的细节,分区表对于单条记录的查询没有优势,需要注意这类查询的性能.

分区表分为RANGE,LIST,HASH,KEY四种类型,并且分区表的索引是可以局部针对分区表建立的

CREATE TABLE sales (
    id INT AUTO_INCREMENT,
    amount DOUBLE NOT NULL,
    order_day DATETIME NOT NULL,
    PRIMARY KEY(id, order_day)
) ENGINE=Innodb PARTITION BY RANGE(YEAR(order_day)) (
    PARTITION p_2010 VALUES LESS THAN (2010),
    PARTITION p_2011 VALUES LESS THAN (2011),
    PARTITION p_2012 VALUES LESS THAN (2012),
    PARTITION p_catchall VALUES LESS THAN MAXVALUE);

这段语句表示将表内数据按照order_dy的年份范围进行分区,2010年一个区,2011一个,2012一个,剩下的一个.

要注意如果这么做,则order_day必须包含在主键中,且会产生一个问题,就是当年份超过阈值,到了2013,2014时,需要手动创建这些分区

替代方法就是使用HASH

CREATE TABLE sales (
    id INT PRIMARY KEY AUTO_INCREMENT,
    amount DOUBLE NOT NULL,
    order_day DATETIME NOT NULL
) ENGINE=Innodb PARTITION BY HASH(id DIV 1000000);

这种分区表示每100W条数据建立一个分区,且没有阈值范围的影响.

分区表的查询语句如下（查询p_2010分区）：

SELECT * FROM `sales` partition(p_2010)