前言
为什么要Sql优化?:
随着项目的上线,它的数据是不断累加的,数据越来越多响应的时间相对也越来越久,所以要对它进行优化
优化
1. 硬优化
- 对硬件进行优化(CPU,内存,磁盘)
- 分库分表 + 读写分离
- 对它的参数进行设置
2. 软优化
查询语句优化
1.1 执行计划
我们可以使用执行计划(EXPLAIN) 命令来分析一条Sql语句是否为最优的
其中会显示索引和查询数据读取数据条数等信息
我们一般关注有四列
1.type:查询类型从最好到最差依次是:system>const>eq_ref>ref>range>index>All,一般情况下,得至少保证达到index级别,最好能达到ref。
2.possible_keys:显示这张表中可能用到的索引,但不一定会用到。
3.key:实际使用的索引,如果为null,则没有使用索引,因此会出现possible_keys列有可能被用到的索引,但是key列为null,表示实际没用索引。
4.Extra
1.2 优化子查询
在MySql中,尽量使用JOIN来代替子查询,因为子查询需要嵌套查询,嵌套查询时会创建一张临时表,临时表的建立和删除都会有较大的系统开销,而连接查询不会创建临时表,因此效率比嵌套子查询高。
1.3 使用索引优化
这里就要说说Mysql中引擎对索引的支持了:
InnoDB:它的索引和数据是在一起的,都是保存在同一个文件中(ibd)。
myisam:它的索引和数据是分开来管理的。
frm表结构定义
MYD(MYData)数据文件MYI
(MYIndex)索引文件
优化:
- 尽量避免在字段开头使用模糊查询
-- 避免字段开头使用模糊查询
select * from dept where dname like '%莎%';
-- 会导致数据库引索引失效进行全表扫描,可以使用下面语句查询
select * from dept where dname like '莎%';
- 尽量避免使用null进行判断
select * from dept where dsex is null;
-- 它这样会导致不走索引全表扫描,优化
select * from dept where dsex = 0;
如果可以别把索引设置在有null的字段上
因为可为null的列使的索引、索引统计等都变的更加复杂。
- 尽量避免在where判断左侧做表达式
-- 走全表扫描
select * from dept where dage / 2 > 10;
-- 走索引
select * from dept where dage > 10 / 2;
- 尽量避免查询的时候使用 " * "
-- 使用 * 查询的时候 Mysql需要解析更多字段、对象、权限、属性等 会增加计算负担
-- 而且某些字段数据量过大也会增加I/O负担
-- 也发挥不出索引的效率
select * from dept;
-- 写清楚需要的字段
select dage from dept;
- 使用order by的时候 让它走索引
-- 因为索引本身就是已经排序好了的,如果走了索引的话,它就不需要进行排序了。
-- 但是如果没有过滤条件的话就不会走索引,它会把数据重新排序。
-- 没走
select * from dept order by dage;
-- 走了索引
select * from dept where dage > 0 order by dage;
- 多表查询的时候使用小表在前,大表在后
使用Mysql的时候,from的表关联查询时从左往右执行的,第一张表会全表查询,所以需要讲小表放在前面,先扫描小表,这样扫描快效率较高,再扫描后面大表的时候,没准大表前面几条就符合条件呢? - 用多次插入的情况使用批量插入
insert into dept values("莎");
insert into dept values("莎");
insert into dept values("莎");
insert into dept values("莎"),("莎"),("莎");
1.4 分解表
对于字段较多的表,有些热数据查询次数比较多而冷数据查询少,这个时候就可以把他们抽离分开形成新的表。
1.5 中间表
创建中间表,有些数据量大的表查询优化过后还是查询过慢,所以这个时候可以先把里面需要的数据查询出来存在中间表中,然后只需要查询中间表即可。