@[toc]

二 SQL优化

1）SQL优化之分页优化

普通写法

select * from buyer where sellerid=100 limit 100000, 5000

普通limit M,N 的翻页写法，在越往后翻页的过程中，速度越慢，原因mysql会读取表中的前M+N条数据，M越大，性能就越差

优化写法

select t1.* from buyer t1,
       (select id from buyer where sellerid = 100 limit 100000,5000) t2
where  t1.id = t2.id

注意：需要在t表的sellerid字段上创建索引，id为表的主键

create index ind_sellerid on buyer(sellerid);

原始语句与优化后语句的查询时间对比

-- 原始语句： 
select id,... from buyer where sellerId=765922982 and gmt_modified >='1970-01-01 08:00:00 and gmt_modified<='2013-06-05 17:11:31 limit 255000,5000; 
-- 优化后语句： 
select t2.* from (select id from buyer where sellerId= 765922982 and gmt_modified >='1970-01-01 08:00:00 and gmt_modified <='2013-06-05 17:11:31 limit 255000,5000）t1 buyer t2 where t1.id=t2.id 
-- 查询时间：60S→0.2S

2）SQL优化之子查询优化

典型子查询

select first_name
from employees
where emp_no in
(select emp_no from salaries_2000 
where salary = 5000);

MySQL的处理逻辑是遍历employees表中的每一条记录，代入到子查询中去

改写子查询

select first_name
from employees emp,
(select emp_no from salaries_2000 
where salary = 5000) sal
where emp.emp_no = sal.emp_no;

SQL优化最佳实践

1. 分页优化

采用高效的Limit写法，避免分页查询给数据库带来性能影响

1. 子查询优化

子查询在5.1，5.5版本中都存在较大的风险，将子查询改为关联
使用MySQL5.6的版本，可以避免麻烦的子查询改写

1. 查询需要的字段

避免用select * 查询所有字段数据，只查询需要的字段数据

三 锁

1）索之表级锁

Innodb 与 Myisam

目前比较推荐Innodb，较高版本也将其设置 为默认引擎

典型案例

创建表，引擎为Myisam

create table 't_myisam'(
'id' int(11) default null
)engine=myisam default charset=utf8;

查询堵塞更新

select id,sleep(100) from t_myisam

解决：更改引擎

alter table t_myisam engine = innodb;

2）锁之Metadata lock

DDL操作

alter table t add column gmt_create datetime

数据库连接状态

select id , sleep(50) from t 
-- 在操作下面DDL语句之前，先执行查询状态的语句
alter table t add column gmt_create datetime

可以看到出现：Waiting for table metadata lock,导致后面所有操作都出现堵塞，因为DDL语句会破坏元数据结构，导致拿不到锁。
Tips：DDL过程中注意数据库中大长事务，大查询

锁问题最佳实践

1. 设计开发阶段

   1. 避免使用myisam存储引擎，改用Innodb引擎
   2. 避免大事务，长事务导致事务在数据库中的运行时间加长
   3. 选择升级到MySQL5.6版本，支持online ddl

2. 管理运维阶段

   1. 在业务低峰期执行上述操作，比如创建索引，添加字段；
   2. 在结构变更前，观察数据库中是否存在长SQL，大事务；
   3. 结构变更期间，监控数据库的线程状态是否存在lock wait;
   4. 阿里云ApsaraDB支持在DDL变更中加入 wait timeout；