@[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.2S2）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优化最佳实践
- 分页优化
采用高效的Limit写法，避免分页查询给数据库带来性能影响
- 子查询优化
子查询在5.1，5.5版本中都存在较大的风险，将子查询改为关联
使用MySQL5.6的版本，可以避免麻烦的子查询改写
- 查询需要的字段
避免用select * 查询所有字段数据，只查询需要的字段数据
三 锁
1）索之表级锁
Innodb 与 Myisam
| 引擎 | 支持事务 | 并发 | 索引损坏 | 锁级别 | 在线备份 |
|---|---|---|---|---|---|
| Myisam | 不支持 | 查询堵塞更新 | 索引损坏 | 表 | 不支持 |
| Innodb | 支持 | 不堵塞 | 不损坏 | 行 | 支持 |
目前比较推荐Innodb，较高版本也将其设置 为默认引擎
典型案例
创建表，引擎为Myisam
create table 't_myisam'(
'id' int(11) default null
)engine=myisam default charset=utf8;查询堵塞更新
select id,sleep(100) from t_myisam| Query | 6 | User sleep | select id ,sleep(50) from t |
| Query | 4 | Waiting for table metadata lock | alter table t add column gmt_create data |
| Query | 1 | Waiting for table metadata lock | select*from twhere id=2 |
| Query | 1 | Waiting for table metadata lock | update t setid_2where.id=1 |
解决：更改引擎
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过程中注意数据库中大长事务，大查询
锁问题最佳实践
设计开发阶段
- 避免使用myisam存储引擎，改用Innodb引擎
- 避免大事务，长事务导致事务在数据库中的运行时间加长
- 选择升级到MySQL5.6版本，支持online ddl
管理运维阶段
- 在业务低峰期执行上述操作，比如创建索引，添加字段；
- 在结构变更前，观察数据库中是否存在长SQL，大事务；
- 结构变更期间，监控数据库的线程状态是否存在lock wait;
- 阿里云ApsaraDB支持在DDL变更中加入 wait timeout；
