mysql手册10_SQL优化

桃扇骨 2022-11-29 05:39 167阅读 0赞

**mysql手册10\_SQL优化**

--------------------

**1. 大批量插入数据的优化：**

*  **按照主键顺序插入**
 *  **关闭唯一性校验**

导入数据前执行SET UNIQUE_CHECKS=0，关闭唯一性校验
    导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验

*  **手动提交事务**

导入数据前执行SET AUTOCOMMIT=0，关闭自动提交
    导入结束后执行SET AUTOCOMMIT=1，开启自动提交

--------------------

**2. insert语句的优化：**

*  **整合多个insert语句，减少连接次数：**

原始方案：
    insert into test values(1, 'zhangsan');
    insert into test values(2, 'lisi');
    insert into test values(1, 'wangwu');
    
    优化方案：
    insert into test values(1,'zhangsan'),(2,'lisi'),(3,'wangwu');

*  **使用手动事务提交**

start transaction;
    insert into test values(1,'zhangsan'),(2,'lisi'),(3,'wangwu');
    ...
    commit;

*  **按照主键顺序插入**

--------------------

**3. order by 语句的优化（尽可能使用Using index，避免使用Using filesort）：**

*  **查询的字段应只包含索引字段和主键**

*  **使用多个索引字段作为排序条件，要么全部升序，要么全部降序，且排序顺序要和复合索引的定义顺序一致**

seller表的复合索引：seller(name,status,address);
    
    案例1：
    select id,name,status,address from seller order by name desc, status asc;
    结果：Using index; Using filesort 效率低
    
    案例2：
    select id,name,status,address from seller order by name desc, status desc;
    结果：Using index 效率高
    
    案例3：
    select id,name,status,address from seller order by status desc, name desc;
    结果：Using index; Using filesort 效率低

**Filesort优化：**

**注：Using filesort 是Mysql里一种速度比较慢的外部排序，应尽量避免**  
对于Filesort，Mysql有两种排序算法：

*  **两次扫描算法**： 首先根据条件取出排序字段和行指针。在排序区sort buffer 中排序，在临时表中存储排序结果，完成排序后，根据行指针回表取出结果，该操作会导致大量随机I/O操作，效率低。
 *  **一次扫描算法**：一次性取出满足条件的所有字段，然后在排序区排序后直接输出结果集，该算法内存开销大，但效率比两次扫描算法高。

MYSQL会比较 **系统变量max\_length\_for\_sort\_data** 的大小和Query语句取出的字段总大小来判断用哪种算法，如果max\_length\_for\_sort\_data更大，则会使用一次扫描算法，否则使用两次扫描算法。

可以适当提高 **sort\_buffer\_size** 和 **max\_length\_for\_sort\_data** 来增大排序区的大小以提高排序效率。

--------------------

**4. group by 语句的优化**

**group by 子句在进行分组时一般（如果没有合适的索引可用）是先扫描整个表并创建一个新的临时表，然后按照group by 指定的列进行排序, 在创建的临时表中每个组的所有行应为连续的，然后使用该临时表来找到组并执行聚合函数(如果有)。**

优化方案1是在 group by 语句后加上**order by null**，取消排序行为。

此外，给group by 指定的列创建索引也可以提高效率。

--------------------

**5. 嵌套查询的优化**

使用多表联查替换子查询

原始方案：
    select * from user where id in (select user_id from role);
    优化方案：
    select * from user u, role r where u.id = r.user_id;

--------------------

**6. OR的优化**

**or的每个条件列都必须用到索引，且不能用到复合索引，否则索引失效。**

select * from user where id = 1 or name = 'zhangsan';
    
    优化方案：给name添加单列索引

select * from user where id = 1 or id = 10;
    
    优化方案：使用union替换or
    select * from user where id = 1 union select * from user where id = 10;

**7. 分页查询的优化**

查询第一页，每页10条数据，耗时0.00s：
    select * from testdata limit 10;
    
    查询第二页，每页10条数据，耗时0.00s：
    select * from testdata limit 10,10;
    
    查询第25001页，每页10条数据，耗时0.15s:
    select * from testdata limit 250000,10;
    
    优化思路1，从索引中先查出第25001页的数据id，再回表查询数据，耗时0.11s：
    select * from testdata t,(select id from testdata order by id limit 250000,10) a where t.id = a.id;
    
    优化思路2，耗时0.01s：
    select * from testdata where id > 250000 limit 10;
    注：该方案只适合主键自增的列，且自增主键不能出现断层。

--------------------

**8. 使用SQL提示：**

select * from user where name = 'zhangsan';
    
    使用 use index 指定参考使用索引，mysql将不再考虑其他可用的索引：
    select * from user use index(idx_name_age_address) where name = 'zhangsan';
    
    使用 ignore index 指定忽略索引，mysql将不再考虑使用该索引：
    select * from user ignore index(idx_name) where name = 'zhangsan';
    
    使用 force index 强制使用某个索引，即使该索引查询效率比扫表慢也要用：
    select * from user force index(idx_address) where address = '中国北京';