1.sql语句的优化
- explain解析
1.type列,连接类型。一个好的sql语句至少要达到range级别。杜绝出现all级别
常用的类型有: ALL, index, range, ref, eq_ref, const, system, NULL(从左到右,性能从差到好)
2.key列,使用到的索引名。如果没有选择索引,值是NULL。可以采取强制索引方式
key_len列,索引长度
3.rows列,扫描行数。该值是个预估值
4.extra列,详细说明。注意常见的不太友好的值有:Using filesort, Using temporary
- SQL语句中IN包含的值不应过多
- SELECT语句务必指明字段名称
- 当只需要一条数据的时候,使用limit 1
- 如果排序字段没有用到索引,就尽量少排序
- 尽量用union all代替union
union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作,这就会涉及到排序,增加大量的CPU运算,加大资源消耗及延迟。当然,union all的前提条件是两个结果集没有重复数据。
- 不使用ORDER BY RAND()
- 区分in和exists, not in和not exists
- 使用合理的分页方式以提高分页的效率 10.不建议使用%前缀模糊查询,会使索引失效 11.避免在where子句中对字段进行表达式操作
select user_id,user_project from user_base where age*2=36;
中对字段就行了算术运算,这会造成引擎放弃使用索引,建议改成
select user_id,user_project from user_base where age=36/2;
12.避免隐式类型转换
where 子句中出现 column 字段的类型和传入的参数类型不一致的时候发生的类型转换,建议先确定where中的参数类型
13.对于联合索引来说,要遵守最左前缀法则
举列来说索引含有字段id,name,school,可以直接用id字段,也可以id,name这样的顺序,但是name;school都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序,常用的查询字段放在最前面
14.注意范围查询语句
对于联合索引来说,如果存在范围查询,比如between,>,<等条件时,会造成后面的索引字段失效。
15.避免在 where 子句中对字段进行 null 值判断
对于null的判断会导致引擎放弃使用索引而进行全表扫描。
所以字段都设置为not null,减少没必要的null的判断
2.硬件的优化
- 更好的cpu
- 调整内核最大文件数
//查看最大文件数:
sysctl fs.file-max
//临时修改
systcl -w fs.file-max=6553500
//永久生效
echo "fs.file-max=6553500">>/etc/systcl.conf
- 数据库系统配置
//1.并发性能
max_connections:mysql最大的连接数,如果服务器的并发连接请求量比较大,建议适当跳高该值,但不是越大越好,
越高就会使内存开销越大
//2.back_log
mysql能暂存的连接数,当主要的mysql线程在一个很短的时间内得倒非常多的连接请求,这就起作用了,
mysql连接数到达max_connections时,新来的请求会被存在堆栈中,堆栈的数量就是back_log,
如果等待连接的熟练超过back_log,将不被授予连接资源
3.监控
- 慢日志
4.架构优化
- 采用redis或者memcache缓存,减少数据库的调用
- 分库分表分区
- 读写分离
- 热备份以及双活