SQL 优化｜学习笔记

开发者学堂课程【MySQL 实操课程：SQL 优化】学习笔记，与课程紧密联系，让用户快速学习知识。  

课程地址：https://developer.aliyun.com/learning/course/717/detail/12830

SQL 优化

一、SQL 优化建议

1、SQL 诊断优化，会帮您分析当前执行的 SQL，并给到具体的优化建议。

通过 DMS- 数据管理服务平台，比如执行 SELECT*FROM emp；查询这个 emp 表

点击执行就会查询出来了

现在写入：SELECT * FROH emp where enawe LIKE "zhangsan%"; 执行，找到这条语句，对这条语句进行 SQL 诊断，他会帮你去分析：

MySQL 本身自己也提供了一些这样的功能。

诊断会包括索引专断建议和语句优化建议，这里如果没有建索引这个表，他会给你一些建议。

在 MySQL 本身有什么方式可以看到它的指标呢？

在连接 MySQL 的时候，写入 show status like’com ’可以看到 com 开头的一些变量，里面有一个 com select，讲的是当前连接客户端进来的时候总共执行的select 操作的次数，比如每次执行一次 select，当前数值是1，每执行一次就会+1。

Com insert  主要讲的是执行 insert 的次数，比如批量插入的情况，他会给你算一次。

Com update 就是更新的次数，com delete 就是删除的次数。

在这些变量可以大概能够看到当前 MySQL 运行的情况，像 select 讲的是没有被缓存的次数，如果缓存被命中了，就不会给算到 select 里面。

查看指定的存储引擎：show status like ‘inndb 

比如 Inndb rows read 可以看到总共读了多少行，Inndb rows delect  删除了多少行，它会帮你记录下来。

其他还有一些像 com rollback 它的执行次数也会被统计，了解到会不会回滚，如果回滚次数很频繁，应用编写可能存在一定的问题。

也可以看当前总共有多少个连接到数据库，写入：shou status like‘connections’

这个连接次数并不是当前有多少连接，而是总共有多少次连接。

通过这些指标可以看到了，怎么做一些优化呢？

可以通过查看它执行的计划，写入 explain select*from user where name‘zhangsan1’

显示：

Select type 表示 select 类型，如果下面是 simple 代表是单表查询，没有使用到表连接或者只查询。

如果 type 下面是 const 讲得是一行，他解锁的就是一行，如果 type 下面是 all 代表全表扫描，如果是 indest 代表是全索引扫描，如果是 range 代表是范围扫描，就是大于、等于的一些操作。

如果是 ref 讲的是使用唯一索引扫描或者唯一索引前缀扫描，主要用来返回匹配某歌单独值的记录行。

Const 是表里面最多匹配一行，这种查询速度非常的迅速，其实就是根据组件或者唯一索引进行查询的，所以这种效率是最高的。

看一条语句执行的情况可以写入：select @@have profiling 

显示为 yes，代表现在支持查看执行计划的功能。

写入 select @@profiling，显示为0.

写入 set profiling=1，执行就变为了1.

设置为1 后就可以执行刚才的 SQL 语句。

写入 show profiles  它就会把 SQL 语句记录下来。

Show profile for query 2 可以看到详细的执行情况：

这个过程首先是 Starting 开始，然后进行权限的验证 Checking permissions，Init打开表执行初始化，System lock 系统的锁，Optimizing 优化帮你自动生成执行计划，之后做一些准备工作进行执行，执行完毕后发送数据 Sending data，涉及到MySQL server 对存储引擎之间的数据的查询与模仿，比如交给数据引擎，他帮你查询，查询完成后会将数据返回给 MySQL server 层。查询就结束了。

最终把表关了，将资源释放出来，这是整个过程。

举例：

写入 show tables，比如这里有一张表叫 show create table school：

school lCREATE TABLE ‘school‘

‘id‘int (11)DBFA0r UL,

‘name‘Tvarchar (20)DEFA0LT NOLL,

UNIQUE KEY`idx id_name( id  , 'name

ENGINE=InnoDB DEFAOLT CHARSET=latin1 l

可以看到这是 innoDB 的存储引擎，可以把这个表重新复制一份：create table school myisam like school，然后写入 insert into school myisam select * from school; 将数据藏起来。

然后写入 select*from school mysiam：

Show create table school mysiam，然后写入：alter table school myisam engine MYISAM 改为这个存储引擎。

查看：show profiles 会发现很多条语句，执行：select count (1)from:chool myisam;

最后输入 show profiles 可以看到查询 school 总比查询 myisam 的表要多。

写入：show profile for query 13，显示：

这是 innoDB 存储引擎的详细执行计划，会发现这里主要的环节是 Sending data

写入 show profile for query 14，会发现没有 Sending data。

Myisam 和 innoDB 存储引擎在针对 SQL 处理过程当中是存在差异的。

Myisam 在执行统计操作的时候，不需要消耗太多的资源，因为这些数据它是直接缓存起来的，但是 innoDB 没有，所以执行时间会比较长。

如果写入更多数据在表里面，会更加明显看到它们的差异，所以针对 innoDB 和myisam 统计的场景它们的差别。

在 DMS 管理平台也是提供了这样的功能，前面讲到了可以进行 SQL 诊断，也可以看它的执行计划。