Sql优化

---

前言

为什么要Sql优化?：

随着项目的上线，它的数据是不断累加的，数据越来越多响应的时间相对也越来越久，所以要对它进行优化

---

优化

1. 硬优化

1. 对硬件进行优化（CPU，内存，磁盘）
2. 分库分表 + 读写分离
3. 对它的参数进行设置

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)索引文件

优化：

1. 尽量避免在字段开头使用模糊查询

-- 避免字段开头使用模糊查询
select * from dept where dname like '%莎%';

-- 会导致数据库引索引失效进行全表扫描，可以使用下面语句查询
select * from dept where dname like '莎%';

2. 尽量避免使用null进行判断

select * from dept where dsex is null;

-- 它这样会导致不走索引全表扫描，优化
select * from dept where dsex = 0;

如果可以别把索引设置在有null的字段上
因为可为null的列使的索引、索引统计等都变的更加复杂。

3. 尽量避免在where判断左侧做表达式

-- 走全表扫描
select * from dept where dage / 2 > 10;

-- 走索引
select * from dept where dage > 10 / 2;

4. 尽量避免查询的时候使用 " * "

-- 使用 * 查询的时候 Mysql需要解析更多字段、对象、权限、属性等 会增加计算负担
-- 而且某些字段数据量过大也会增加I/O负担
-- 也发挥不出索引的效率
select * from dept;

-- 写清楚需要的字段
select dage from dept;

5. 使用order by的时候 让它走索引

-- 因为索引本身就是已经排序好了的，如果走了索引的话，它就不需要进行排序了。
-- 但是如果没有过滤条件的话就不会走索引，它会把数据重新排序。

-- 没走
select * from dept order by dage;

-- 走了索引
select * from dept where dage > 0 order by dage;

6. 多表查询的时候使用小表在前，大表在后
   使用Mysql的时候，from的表关联查询时从左往右执行的，第一张表会全表查询，所以需要讲小表放在前面，先扫描小表，这样扫描快效率较高，再扫描后面大表的时候，没准大表前面几条就符合条件呢？
7. 用多次插入的情况使用批量插入

insert into dept values("莎");
insert into dept values("莎");
insert into dept values("莎");

insert into dept values("莎"),("莎"),("莎");

1.4 分解表

对于字段较多的表，有些热数据查询次数比较多而冷数据查询少，这个时候就可以把他们抽离分开形成新的表。

1.5 中间表

创建中间表，有些数据量大的表查询优化过后还是查询过慢，所以这个时候可以先把里面需要的数据查询出来存在中间表中，然后只需要查询中间表即可。