1.常见索引列建议
出现在select,update,delete语句的where从句中的列
包含在order by ,group by ,distinct中的字段
多表的join关联列
2.避免建立冗余和重复索引
重复索引示例:primary key(id)、index(id)、unique index(id)
冗余索引示例:index(a,b,c)、index(a,b)、index(a)
3.优先选择符合存储需要的最小数据类型
4.避免使用TEXT,BLOG数据类型。(如果查询中包含这样的数据,在排序等操作时,就不能使用内存临时表,必须使用磁盘临时表进行,Mysql还要进行二次查询,是sql性能变差)
5.使用TIMESTAMP(4字节)或DATETIME(8字节)存储时间
6.财务相关金额类数据必须使用decimal类型(Decimal类型为精准浮点数,在计算时不会丢失精度)
7.限制每张表上的索引数量,建议但张表索引不超过5个。(索引不是越多越好,索引可以增加查询效率,但同样也会降低插入和更新效率。)
8.冷热数据分离,减小表的宽度
MySQL限制每个表最多存储4096列,并且每一行数据的大小不能超过65535字节 减少磁盘IO,保证热数据的内存缓存命中率(表越宽,把表装载进内存缓冲池时所占用的内存也就越大,也会消耗更多的IO) 更有效的利用缓存,避免读入无用的冷数据 经常一起使用的列放到一个表中(避免更多的关联操作)
9.不要在数据库中存储图片,文件等二进制数据
10.尽量避免使用外键约束。
不建议使用外键约束foregin key,但一定要在表与表之间的关联键上建立索引;
外键可以用于保证数据的参照完整性,但建议在业务端实现;
外键会影响父表和子表的写操作从而降低性能。
11.建议使用预编译语句进行数据库操作
预编译语句可以重复使用这些计划,减少SQL编译所需要的时间,还可以解决动态SQL所带来的SQL注入的问题 只传参数,比传递SQL语句更高效 相同语句可以一次解析,多次使用,提高处理效率。
12.避免使用前%号的查询条件
如a like %123%,(如果无前置%,只有后置%,是可以用到列上的索引的)
在我们查询中经常会用到select id,fnum,fdst from table_name where user_name like '%zhangsan%'; 。这样的语句,普通索引是无法满足查询需求的。庆幸的是在MySQL中,有全文索引来帮助我们。
创建全文索引的sql语法是:
ALTER TABLE `table_name` ADD FULLTEXT INDEX `idx_user_name` (`user_name`);
使用全文索引的sql语句是:
select id,fnum,fdst from table_name where match(user_name) against('zhangsan' in boolean mode);
13.禁止使用SELECT * 必须使用SELECT <字段列表> 查询
原因:
- 消耗更多的CPU和IO以网络带宽资源
- 无法使用覆盖索引
- 可减少表结构变更带来的影响
14.避免使用子查询,可以把子查询优化为join操作
通常子查询在in子句中,且子查询中为简单SQL(不包含union、group by、order by、limit从句)时,才可以把子查询转化为关联查询进行优化。
子查询性能差的原因:
- 子查询的结果集无法使用索引,通常子查询的结果集会被存储到临时表中,不论是内存临时表还是磁盘临时表都不会存在索引,所以查询性能会受到一定的影响;
- 特别是对于返回结果集比较大的子查询,其对查询性能的影响也就越大;
- 由于子查询会产生大量的临时表也没有索引,所以会消耗过多的CPU和IO资源,产生大量的慢查询。
15.避免使用JOIN关联太多的表
对于Mysql来说,是存在关联缓存的,缓存的大小可以由join_buffer_size参数进行设置。
在Mysql中,对于同一个SQL多关联(join)一个表,就会多分配一个关联缓存,如果在一个SQL中关联的表越多,所占用的内存也就越大。
如果程序中大量的使用了多表关联的操作,同时join_buffer_size设置的也不合理的情况下,就容易造成服务器内存溢出的情况,就会影响到服务器数据库性能的稳定性。
同时对于关联操作来说,会产生临时表操作,影响查询效率Mysql最多允许关联61个表,建议不超过5个。
16.在明显不会有重复值时使用UNION ALL而不是UNION
UNION会把两个结果集的所有数据放到临时表中后再进行去重操作
UNION ALL不会再对结果集进行去重操作
17.使用left join或 not exists来优化not in操作
因为not in 也通常会使用索引失效。
18.使用合理的分页方式以提高分页的效率
列:select id,name from table_name limit 88888, 20
使用上述sql语句做分页的时候,可能有人会发现,随着表数据量的增加,直接使用limit分页查询会越来越慢。
优化的方法如下:可以取前一页的最大行数的id,然后根据这个最大的id来限制下一页的起点。比如此列中,上一页最大的id是88888。sql可以采用如下的写法:
select id,name from table_name where id> 88888 limit 20
19.避免在 where 子句中对字段进行 null 值判断
应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:
select id from t where num is null
可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:
select id from t where num=0
20.避免隐式类型转换
隐式转换会导致索引失效
where 子句中出现 column 字段的类型和传入的参数类型不一致的时候发生的类型转换,建议先确定where中的参数类型
21.对于联合索引来说,要遵守最左前缀法则,常用的查询字段放在最前面
22.应尽量避免在 where 子句中使用!=或<>操作符,否则将导致引擎放弃使用索引而进行全表扫描。
23.应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:
select id from t where num=10 or num=20
可以这样查询:
select id from t where num=10
union all
select id from t where num=20
24.in 和 not in 也要慎用,否则会导致全表扫描,如:
select id from t where num in(1,2,3)
对于连续的数值,能用 between 就不要用 in 了:
select id from t where num between 1 and 3
25.应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:
select id from t where num/2=100
应改为:
select id from t where num=100*2
26.用 exists 代替 in
select num from a where num in(select num from b)
用下面的语句替换:
select num from a where exists(select 1 from b where num=a.num)
27.对于复合索引,在查询使用时,最好将条件顺序按找索引的顺序,这样效率最高;
select * from table1 where col1=A AND col2=B AND col3=D 如果使用 where col2=B AND col1=A 或者 where col2=B 将不会使用索引