Sql 优化笔记_相较于“select“关键字,“select distinct“语句执行时间会更快,因为其减少

作者：盐析白兔 | 2024-06-05 00:42:18

踩

相较于“select“关键字,“select distinct“语句执行时间会更快,因为其减少

Sql 优化
- 概述
  - - 优化成本：硬件>系统配置>数据库表结构>SQL及索引。
    - 优化效果：硬件<系统配置<数据库表结构<SQL及索引。
- MySQL层优化原则
  - 减少数据访问：设置合理的字段类型，启用压缩，通过索引访问等减少磁盘IO
  - 返回更少的数据：只返回需要的字段和数据分页处理减少磁盘io及网络io
  - 减少交互次数：批量DML操作，函数存储等减少数据连接次数
    - 扩展
      - SQL语句主要分为哪几类
        数据定义语言DDL（Data Ddefinition Language）CREATE，DROP，ALTER
        主要为以上操作即对逻辑结构等有操作的，其中包括表结构，视图和索引。
        
        数据查询语言DQL（Data Query Language）SELECT
        这个较为好理解即查询操作，以select关键字。各种简单查询，连接查询等都属于DQL。
        
        数据操纵语言DML（Data Manipulation Language）INSERT，UPDATE，DELETE
        主要为以上操作即对数据进行操作的，对应上面所说的查询操作 DQL与DML共同构建了多数初级程序员常用的增删改查操作。
        
        数据控制功能DCL（Data Control Language）GRANT，REVOKE，COMMIT，ROLLBACK
        主要为以上操作即对数据库安全性完整性等有操作的，可以简单的理解为权限控制等。
  - 减少服务器CPU开销：尽量减少数据库排序操作以及全表查询，减少cpu 内存占用
  - 利用更多资源：使用表分区，可以增加并行操作，更大限度利用cpu资源
- SQL优化策略
  核心
  - 最大化利用索引
  - 尽可能避免全表扫描
  - 减少无效数据的查询
- 查询语句的书写顺序
  - select ===> from ===> where ===> group by ===> having ===> order by ===> limit
- 查询语句的执行顺序
  - from ===> where ===> group by ===> having ===> select ===> order by ===> limit
  - from ===> on ===>join ===>where ===> group by ===> 聚合===> having ===> select ===> distinct ===>order by ===> limit
- SQL优化策略
  适用于数据量较大的场景下，如果数据量较小，没必要以此为准，以免画蛇添足。
  - 避免不走索引的场景
    (自己扩展InnoDB引擎)
    - 扩展：
      - 查看索引
        使用explain分析你SQL执行计划
        SQL提供了explain关键字，它可以分析你的SQL执行计划，看它是否最佳。Explain主要看SQL是否使用了索引。
    - 1. 尽量避免在字段开头模糊查询，
      SELECT * FROM t WHERE username LIKE '%陈%'
      - 原因：会导致引擎放弃索引进行全表扫描。
      - 优化方式：尽量在字段后面使用模糊查询。如下：
        SELECT * FROM t WHERE username LIKE '陈%'
      - 当表数据量较少（几千条儿那种），别整花里胡哨的，直接用like '%xx%'。
    - 2. 尽量避免使用in 和not in。如下：
      SELECT * FROM t WHERE id IN (2,3)
      - 原因：会导致引擎走全表扫描
      - 优化方式：
        如果是连续数值，可以用between代替。如下：
        SELECT * FROM t WHERE id BETWEEN 2 AND 3
        
        如果是子查询，可以用exists代替。
        -- 不走索引
        select * from A where A.id in (select id from B);
        -- 走索引
        select * from A where exists (select * from B where B.id = A.id);
    - 3. 尽量避免使用 or，会导致数据库引擎放弃索引进行全表扫描。如下：
      SELECT * FROM t WHERE id = 1 OR id = 3
      - 优化方式：可以用union代替or。如下：
        SELECT * FROM t WHERE id = 1
        UNION
        SELECT * FROM t WHERE id = 3
    - 4. 尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描。如下：
      SELECT * FROM t WHERE score IS NULL
      - 优化方式：可以给字段添加默认值0，对0值进行判断。如下：
        SELECT * FROM t WHERE score = 0
    - 5.尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。
      - 扩展：
        最左前缀匹配原则
        组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
  - SELECT语句其他优化
    - 避免出现select *
      SELECT * FROM student
      - 原因：使用select * 取出全部列，会让优化器无法完成索引覆盖扫描这类优化，会影响优化器对执行计划的选择，也会增加网络带宽消耗，更会带来额外的I/O,内存和CPU消耗。
      - 优化方案：建议提出业务实际需要的列数，将指定列名以取代select *
        SELECT id,NAME FROM student
  - 增删改 DML 语句优化
    - 大批量插入数据代替多条单一数据插入
      - 反例：
      - 正例：
      - 原因：
        减少SQL语句解析的操作，MySQL没有类似Oracle的share pool，只需要解析一次就能进行数据的插入操作；
        
        在特定场景可以减少对DB连接次数
        
        SQL语句较短，可以减少网络传输的IO。
  - 查询条件优化
    - 使用truncate代替delete
      - 当删除全表中记录时，使用delete语句的操作会被记录到undo块中，删除记录也记录binlog，当确认需要删除全表时，会产生很大量的binlog并占用大量的undo数据块，此时既没有很好的效率也占用了大量的资源。
      - 使用truncate替代，不会记录可恢复的信息，数据不能被恢复。也因此使用truncate操作有其极少的资源占用与极快的时间。另外，使用truncate可以回收表的水位，使自增字段值归零。
    - 扩展
      - drop、truncate与delete的区别
        
        在不再需要一张表的时候，用drop；在想删除部分数据行时候，用delete；在保留表而删除所有数据的时候用truncate。
  - 建表优化
    - 使用varchar代替char
      - 原因：因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
      - 反例：`deptname` char(100) DEFAULT NULL COMMENT '部门名称'
      - 正例：`deptname` varchar(100) DEFAULT NULL COMMENT '部门名称'
      - 扩展
        varchar与char的区别
        char的特点
        char表示定长字符串，长度是固定的；
        
        如果插入数据的长度小于char的固定长度时，则用空格填充；
        
        因为长度固定，所以存取速度要比varchar快很多，甚至能快50%，但正因为其长度固定，所以会占据多余的空间，是空间换时间的做法；
        
        对于char来说，最多能存放的字符个数为255，和编码无关
        
        varchar的特点
        varchar表示可变长字符串，长度是可变的；
        
        插入的数据是多长，就按照多长来存储；
        
        varchar在存取方面与char相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间，是时间换空间的做法；
        
        对于varchar来说，最多能存放的字符个数为65532
        
        总之，结合性能角度（char更快）和节省磁盘空间角度（varchar更小），具体情况还需具体来设计数据库才是妥当的做法。
- 更多内容

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/盐析白兔/article/detail/674166