数据库

sql优化

Posted by JimWang on 2021-02-23

sql优化

SQL语句优化

慢查询日志发现有问题的SQL

通过MySQL慢查询日志对有效率问题的SQL进行监控

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10s以上的语句。通过MySQL的慢查询日志，我们可以查询出执行的次数多占用的时间长的SQL

通过explain查询和分析SQL的执行计划

使用 EXPLAIN 关键字可以知道MySQL是如何处理你的SQL语句的，以便分析查询语句或是表结构的性能瓶颈。通过explain命令可以得到表的读取顺序、数据读取操作的操作类型、哪些索引可以使用、哪些索引被实际使用、表之间的引用以及每张表有多少行被优化器查询等问题。当扩展列extra出现Using filesort和Using temporay，则往往表示SQL需要优化了。

SQL语句的优化

⒈优化insert语句：一次插入多值；

⒉应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描；

⒊应尽量避免在 where 子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描；

⒋优化嵌套查询：子查询可以被更有效率的连接(Join)替代；

⒌很多时候用 exists 代替 in 是一个好的选择。

⒍选择最有效率的表名顺序：数据库的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表将被最先处理

  • 在FROM子句中包含多个表的情况下：
    • 如果三个表是完全无关系的话，将记录和列名最少的表，写在最后，然后依次类推。也就是说：选择记录条数最少的表放在最后。
    • 如果有3个以上的表连接查询：如果三个表是有关系的话，将引用最多的表，放在最后，然后依次类推。也就是说：被其他表所引用的表放在最后。

⒎用IN代替OR：

1
2
select * from emp where sal = 1500 or sal = 3000 or sal = 800;
select * from emp where sal in (1500,3000,800);

⒏SELECT子句中避免使用*号：
我们最开始接触 SQL 的时候，“*” 号是可以获取表中全部的字段数据的，但是它要通过查询数据字典完成，这意味着将消耗更多的时间，而且使用 “*” 号写出来的 SQL 语句也不够直观。

索引优化

建议在经常作查询选择的字段、经常作表连接的字段以及经常出现在 order by、group by、distinct 后面的字段中建立索引。

但是有些情况索引会失效

索引失效的情况

  1. like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。
  2. or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效
  3. 组合索引的情况下，必须满足最左匹配原则(eg：多列索引col1、col2和col3，则索引生效的情形包括col1或col1，col2或col1，col2，col3)。
  4. 数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。
  5. 在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。
  6. 当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。
  7. 对索引字段进行计算操作、字段上使用函数。（索引为 emp(ename,empno,sal)）

表结构优化

  1. 选择合适数据类型
  2. 遵守三大范式
  3. 表的垂直拆分
    • 把不常用的字段单独放在同一个表中；
    • 把大字段独立放入一个表中；
    • 把经常使用的字段放在一起；
  4. 表的水平拆分
    • 表的水平拆分用于解决数据表中数据过大的问题，水平拆分每一个表的结构都是完全一致的
    • 对ID进行hash运算，如果要拆分成5个表，mod(id,5)取出0~4个值；
    • 针对不同的hashID将数据存入不同的表中；

部分转载自：https://www.yuque.com/fanzhengxu/tba6b8/dx0hvw#QHAdi




FRIENDS