一.良好的表结构设计,字段名称和字段类型,遵循一致性规范,合理的表索引设计
- 1.尽量使用数字型字段,降低查询和连接的性能,降低存储开销(int,bigint,decimal)
- 2.尽可能的使用
varchar/nvarchar代替char/nchar
首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些 - 3.尽量在表中设定一个主键或唯一性约束(推荐使用自增ID,根据具体情况调整,这样会避免一些
尴尬而又不得不处理的数据问题) - 4.垂直拆分,根据你模块的耦合度,将一个大的系统分为多个小的系统,也就是分布式系统
- 5.水平切分,针对数据量大的表,可以根据业务和使用场景进行切分
(可能涉及表结构以及业务代码调整,尽可能早的考虑数据的增长,设计合适的切分类型) - 6.根据数据的流向,对各处理阶段进行分层,每一层处理不同的数据目标,善用中间表
- 7.单个物理数据库实例下表的容量不超过
500万行数据。根据实际情况，可以进行步骤4或进行分库分表操作
二.尽量避免全表扫描
1.考虑在
where及order by涉及的列上建立索引2.尽量避免在
where子句中使用!=或<>操作符,否则将引擎放弃使用索引3.尽量避免在
where子句中对字段进行null值判断,否则将导致引擎放弃使用索引
(建议设置默认值,null值会占用更多的字节,且会在程序中造成很多与预期不符的情况)4.尽量避免在
where子句中使用or来连接条件,否则将导致引擎放弃使用索引(建议尝试使用union all)select id from t where t_no=10 or t_no=20; # 可以这样查询： select id from t where t_no=10 union all select id from t where t_no=20;5.慎用
in和not in,in的条件值不可设置太多,如果条件值是连续的,使用between...and...;
很多时候用exists代替in是一个好的选择select id,name from t where id in (1,2,3); # 可以这样查询 select id,name from t where id between 1 and 3; select t_no from a where t_no in(select t_no from b); # 可以用下面的语句替换： select t_no from a where exists(select 1 from b where t_no=a.t_no);6.如果在
where子句中使用参数，也会导致全表扫描,可以强制使用索引
因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。
然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。select id from t where t_no=@t_no; # 可以改为强制查询使用索引： select id from t with(index(索引名)) where t_no=@t_no;7.尽量避免在where子句中对字段进行函数或表达式操作,导致引擎放弃使用索引
select id from t where substring(name,1,3)='abc' and age/2=30; # 应改为 select id from t where name like 'abc%' and age=2*30;8.善用内部统计信息语句或建立统计表应对常用统计查询,替代直接对表的全表扫描
9.如果不需要
ORDER BY,进行GROUP BY时加ORDER BY NULL,MySQL不会再进行文件排序
三.减少不必要的消耗(CPU、IO、内存、网络带宽)
1.查询时只选择需要的字段和尽可能的使用
limit N2.避免使用
select *3.MySQL内部每秒能扫描内存中上百万行数据,如果增删改查的记录数过于庞大,可以尝试根据数据特征分批次进行切分处理,突破单机瓶颈
4.利用延迟关联或者子查询优化超多分页场景
MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写SELECT b.id,b.name,b.title FROM ( select id from t1 where c1=xxx LIMIT 100000,20 ) a join t1 b on a.id=b.id # t1表中id有索引或主键,避免大数量的全表扫描 ;
四.索引的使用
- 1.使用索引时,遵循最左原则,尽可能保持和索引字段顺序一致,否则,可能不会走索引
- 2.设置索引,不是越多越好,应遵循:
- 一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要
- MySQL优化器认为符合条件的记录在30%以上,它就不会再使用索引,因为mysql认为走索引的代价比不用索引代价大，所以优化器选择了自己认为代价最小的方式; 如性别,如果数据各占一半,那么优化器不会走索引,即使强制走索引,作用也不会很大
- 尽可能的避免更新
clustered索引数据列,如果索引列数据频繁更新,避免使用clustered索引
五.使用临时表,解决一些复杂的查询场景
语法
# 创建临时表
create temporary tmp if not exists {
...
};
insert into tmp as select ... from t;
alter table tmp rename to new_tmp;
truncate table new_tmp;
drop table if exists new_tmp;
使用场景
- 1.如果语句很复杂,连接太多,可以考虑用临时表和表变量分步完成
- 2.如果需要多次用到一个大表的同一部分数据,考虑用临时表和表变量暂存这部分数据
- 3.如果需要综合多个表的数据,形成一个结果,可以考虑用临时表和表变量分步汇总这多个表的数据
使用原则
- 1.当需要重复引用大型表或常用表中的某个数据集时,适当地使用临时表。但是，对于一次性事件，最好使用导出表
- 2.避免频繁创建和删除临时表，以减少系统表资源的消耗
- 3.在新建临时表时，如果一次性插入数据量很大，那么可以使用
select into代替create table，避免造成大量log ，以提高速度;如果数据量不大，为了缓和系统表的资源，应先create table，然后insert - 4.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先
truncate table，然后drop table，这样可以避免系统表的较长时间锁定
自动使用内部临时表的情况
在某些情况下,mysql服务器会自动创建内部临时表:
- 1.该临时表可以是只存在于内存的
memory临时表,或者是存储于硬盘的myisam临时表; - 2.初始创建的
memory临时表由于表的增大,可能会转变为myisam临时表(其转化临界点由max_heap_table_size和tmp_table_size系统变量的较小值决定的！ - 使用explain查看执行计划，检查extra列看是否使用临时表”
using temporary“
注意：max_heap_table_size系统变量应用于所有的memory引擎的表,不管是用户临时表、正常表、或者内部临时表
内部内存临时表的创建条件
- 1.
group by和order by中的列不相同 - 2.
order by的列不是引用from表列表中的第一表 - 3.
group by的列不是引用from表列表中的第一表 - 4.使用了
sql_small_result选项 - 5.含有
distinct的order by语句
内部myisam临时表的创建条件
- 1.表中存在
text、blob列 - 2.在
group by中的 列 有超过512字节 - 3.在
distinct查询中的 列 有超过512字节 - 4.在
union、union all联合查询中，select列列表中的列有超过512字节的
六.注意count的几种用法区别
一般情况下，select count (*)和select count(1)两着返回结果是一样的
假如表沒有主键(Primary key), 那么count(1)比count(*)快，
如果有主键的話，那主键作为count的条件时候count(主键)最快
如果你的表只有一个字段的话那count(*)就是最快的
count(*)跟count(1)的结果一样，都包括对NULL的统计，而count(column)是不包括NULL的统计
七.开启慢查询日志
用于记录执行时间超过某个临界值的SQL日志，用于快速定位慢查询，为我们的优化做参考
# 开启慢查询日志
# 在datadir下产生一个xxx-slow.log的文件
show variables like '%slow_query_log%';
set GLOBAL slow_query_log = on;
# show VARIABLES like 'long_query_time' # 单位秒
# 实操时应该从长时间设置到短的时间，即将最慢的SQL优化掉
# 查看日志,一旦SQL超过了我们设置的临界时间就会被记录到xxx-slow.log中
set long_query_time=0.5;
