如何编写高性能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)

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏PHP技术

MySQL 数据库锁定机制

1. MySQL 锁定机制简介 各存储引擎使用三种类型锁定机制 行级锁定(row-level) 表级锁定(table-level) 页级锁定(page...

37316
来自专栏我和PYTHON有个约会

数据库连接引擎那点事儿

天长,地久。天地之所以能长且久者,以其不自生也,故能长生。是以圣人后其身而身先,外其身而身存,非以其无私邪?故能成其私。——老子

642
来自专栏个人随笔

sql sever[基本] ''增删改'' 随笔

  结构语言分类  DDL(数据定义语言)  create  drop  alter   创建删除以及修改数据库,表,存储过程,触发器,索引....  DML(...

2716
来自专栏jeremy的技术点滴

InnoDB与MyISAM的区别

2525
来自专栏数说戏聊

04-06章 过滤数据第4章 过滤数据第5章 高级数据过滤第6章 用通配符进行过滤

分析 这条语句从 products 表中检索两个列,但不返回所有行,只返回 prod_price 值为 3.49 的行,输出:

431
来自专栏潘昌伟的专栏

用好 mysql 分区表

大数据时代,数据量趋于海量,mysql单表很难满足大数据场景的一些统计需求,用好分区表可以很好的解决很大一部分的问题。

8090
来自专栏butterfly100

InnoDB锁机制

1. 锁类型 锁是数据库区别与文件系统的一个关键特性,锁机制用于管理对共享资源的并发访问。 InnoDB使用的锁类型,分别有: 共享锁(S)和排他锁(X) 意...

3315
来自专栏后端技术探索

分表查询统计的一个具体案例

问题描述 mysql数据库在数据量较大的情况下,对数据表进行水平分表,按照年份,如下:

331
来自专栏性能与架构

为什么SQL优化中建议用UNION来代替OR

在SQL优化相关资料中,通常可以看到一个建议:用UNION来代替OR 举例 采用 OR 语句: SELECT * FROM a, b WHERE a.p...

34310
来自专栏用户画像

mysql 模拟试题一

  3.SQL语言的数据操纵语句包括 SELECT,INSERT,UPDATE和 DELETE, 最重要的,也是使用最频繁的语句是__A__。 

694

扫描关注云+社区