24.建议使用合理的分页方式以提高分页效率。
第一种分页写法：
select *
from t
where thread_id = 771025
and deleted = 0
order by gmt_create asc limit 0, 15;
select * from t
where thread_id = 771025
and deleted = 0
order by gmt_create asc limit 0, 15;
原理：一次性根据过滤条件取出所有字段进行排序返回。
数据访问开销=索引IO+索引全部记录结果对应的表数据IO
缺点：该种写法越翻到后面执行效率越差，时间越长，尤其表数据量很大的时候。
适用场景：当中间结果集很小（10000行以下）或者查询条件复杂（指涉及多个不同查询字段或者多表连接）时适用。
第二种分页写法：
select t.* from (
select id from t
where thread_id = 771025 and deleted = 0 order by gmt_create asc limit 0, 15) a, t
where a.id = t.id;
前提：假设t表主键是id列，且有覆盖索引secondary key:(thread_id, deleted, gmt_create)
原理：先根据过滤条件利用覆盖索引取出主键id进行排序，再进行join操作取出其他字段。
数据访问开销=索引IO+索引分页后结果（例子中是15行）对应的表数据IO。
优点：每次翻页消耗的资源和时间都基本相同，就像翻第一页一样。
适用场景：当查询和排序字段（即where子句和order by子句涉及的字段）有对应覆盖索引时，且中间结果集很大的情况时适用。
25.SELECT只获取必要的字段,禁止使用SELECT *。
减少网络带宽消耗;
能有效利用覆盖索引;
表结构变更对程序基本无影响。
26.SQL中避免出现now()、rand()、sysdate()、current_user()等不确定结果的函数。
语句级复制场景下,引起主从数据不一致;不确定值的函数,产生的SQL语句无法利用QUERY CACHE。
27.采用合适的分库分表策略。例如千库十表、十库百表等。
采用合适的分库分表策略,有利于业务发展后期快速对数据库进行水平拆分,同时分库可以有效利用MySQL的多线程复制特性。
28.减少与数据库交互次数,尽量采用批量SQL语句。
使用下面的语句来减少和db的交互次数:
a)INSERT ... ON DUPLICATE KEY UPDATE
b)REPLACE INTO
c)INSERT IGNORE
d)INSERT INTO VALUES()
29.拆分复杂SQL为多个小SQL,避免大事务。
简单的SQL容易使用到MySQL的QUERY CACHE;减少锁表时间特别是MyISAM;可以使用多核CPU。
30.对同一个表的多次alter操作必须合并为一次操作。
mysql对表的修改绝大部分操作都需要锁表并重建表,而锁表则会对线上业务造成影响。为减少这种影响,必须把对表的多次alter操作合并为一次操作。例如,要给表t增加一个字段b,同时给已有的字段aa建立索引, 通常的做法分为两步:
alter table t add column b varchar(10);
然后增加索引:
alter table t add index idx_aa(aa);
正确的做法是:
alter table t add column b varchar(10),add index idx_aa(aa);
31.避免使用存储过程、触发器、视图、自定义函数等。
这些高级特性有性能问题,以及未知BUG较多。业务逻辑放到数据库会造成数据库的DDL、SCALE OUT、 SHARDING等变得更加困难。
32.禁止有super权限的应用程序账号存在。
安全第一。super权限会导致read only失效,导致较多诡异问题而且很难追踪。
33.提交线上建表改表需求,必须详细注明涉及到的所有SQL语句(包括INSERT、DELETE、UPDATE),便于DBA进行审核和优化。
并不只是SELECT语句需要用到索引。UPDATE、DELETE都需要先定位到数据才能执行变更。因此需要业务提供所有的SQL语句便于DBA审核。
34.不要在MySQL数据库中存放业务逻辑。
数据库是有状态的服务,变更复杂而且速度慢,如果把业务逻辑放到数据库中,将会限制业务的快速发展。建议把业务逻辑提前,放到前端或中间逻辑层,而把数据库作为存储层,实现逻辑与存储的分离。
35.建表语句示例
