【2023】MySQL详细 ——SQL优化篇(二)

- 表格关系设

crm系统有多种权限，用户可以分为不同的角色，但是每个用户可以属于多个角色，一个权限授予多种角色，一个角色拥有多种权限1. 先寻找实体表、

1. 先寻找实体表
2. 再寻找他们的关系——1:1，1:M，M:N

一、SQL性能检测：EXPLAIN

1. 在查询语句前面加入 ’EXPLAIN ‘ 关键字

• id：表示查询顺序

数值越大，越先查询，子查询时显示多个；

from后面的sql查询最先执行，select后面from前面的sql查询第二执行，

• select_type：表示select的类型，常见的取值
  

• table：表名

• ⭐type：显示的访问类型，
  

possi

⭐key：索引

key_len：索引长度

⭐rows：扫描的行数

2. 使用have_profiling分析sql

• SELECT @@have_profiling; ：通过该指令查看是否支持
• SELECT @@profiling; ：通过该指令查看是否开启，默认0，表示为开启，
• SET profiling=1; ：开启have_profiling开关，
• SHOW PROFILES; 开启之后可以通过该指令查看前面执行的sql的执行时间

3. 慢查询日志

二、常见sql优化

1. 优化insert语句

start transaction：开启事务

commit：关闭事务

2. order by优化-排序

   mysql排序分为两种：

   a. filesort排序：非索引排序，文件排序；当有需要排序的字段没有索引时，即会使用该方式
   

   b. using index排序：通过有序索引顺序扫描返回有效数据，这种情况不需要额外排序，操作效率高；当查询排序的字段都是有索引时，即会使用该排序方式；所以尽量使用覆盖索引，对需要排序的字段都建立索引

   

   进行多字段排序时，尽量使用同一种排序方式，否则第二种排序会失效
   

   3. group by优化-分组

   

   如果查询group by的时候，取消掉排序则可以执行 order by null禁止排序

   SELECT area_name, COUNT(*) FROM stb_area GROUP BY code //优化前

   SELECT area_name, COUNT(*) FROM stb_area GROUP BY code ORDER BY NULL; //优化后

   4. 嵌套查询-子查询

   使用子查询时，尽量可以改为join查询

   5. LIMIT：分页查询

   在进行分页查询时，越晚后面查询的越慢，所以当查询的数据量较大时，可以采用子查询，先查询出id，在通过id进行条件查询，（十万条以上）、

   优化前

   SELECT * FROM stb_area LIMIT 940,10

   优化后

   SELECT * FROM stb_area s1 JOIN (SELECT id FROM stb_area ORDER BY id LIMIT 940,10) s2 ON s1.id=s2.id

   当查询10万条数据时，可以会不走索引，可以拆分为一百次查询，每次查1000条，查询方案

   在业务层使用多线程执行，然后再使用countDownLatch进行整合

   countdownlatch：同步工具类

   Trace工具