sql优化的一些方法

一、正确建立索引

关于索引的建立可以看这一篇

二、sql语句优化

  1. sql 语句中的 in 包含的值不应过多,改用 between

    select id from t where num in (1,2,3,4,5);
    // 改用between
    select id from t between 1 and 5;
    
  2. select 语句指明字段,不要用 *

  3. 只查询一条数据时,避免使用 limit 1

  4. 避免在 where 子句中对字段 null 值判断
    mysql 会对数据中 null 值的数量进行判断,决定使不使用索引

  5. 避免在 where 子句中对字段进行表达式操作

    select uid from user_t where uid*10=40;
    // 算术运算导致引擎放弃使用索引
    select uid from user_t where uid=40/10;
    
  6. 对于联合索引,需要遵守最左前缀法则

  7. 尽量使用 inner join ,避免使用 left join
    如果连接方式是inner join,在没有其他过滤条件的情况下 MySQL 会自动选择小表作为驱动表,但是left join在驱动表的选择上遵循的是左边驱动右边的原则,即left join左边的表名为驱动表。

  8. 注意范围查询语句
    对于联合索引来说,如果存在范围查询,比如between、>、<等条件时,会造成后面的索引字段失效。

    解决办法: 业务允许的情况下,使用 >= 或者<= 这样不影响索引的使用.

    explain select * from  `user_test` where uid=10 and name='张三' and phone='13527748096'; 
    explain select * from  `user_test` where uid between( 1 and 10) and name ='张三' and phone='13527748096';  
    
  9. 不建议使用%前缀模糊查询
    例如 : LIKE“%name" 或者 LIKE“%name%”,这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”

    explain select * from  `user_test` where uid=10 and  uid like  "%1" ; 
    explain select * from  `user_test` where uid=10 and  uid like  "1%" ; 
    
  10. where 子句中使用 or 来连接条件,如果or连接的条件有一方没有索引,将导致引擎放弃使用索引而进行全表扫描

    解决办法: 将or连接的双方都建立索引,就可以使用.

    explain select * from  `user_test` where  uid=10 or name='张三'; 
    
  11. 应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。

    explain select uid from  `user_test` where uid=1 and  substring(phone,1,3)='135'
     
    explain select uid from  `user_test` where uid=1 and   abs(uid)=1;
    
  12. 字符串类型的字段 查询的时候如果不加引号'' ,会导致自动进行隐式转换,然后索引失效

  13. 指定查询的索引

    当 sql 查询的字段有多个索引的时候,mysql 优化器会自动选择一个索引进行查询,我们也可以通过 sql 字段进行自定义,

    • use index(索引): 推荐使用指定的索引 (最终用不用该索引,还需要mysql自己判断)

      select * from  use index(索引A)  
      
    • ignore index(索引) : 忽略掉这个索引

      select * from  ignore index(索引A)  
      
    • force index(索引): 强制使用该索引

      select * from  force index(索引A)  
      
  14. insert 优化

    • 需要插入多条数据的时候 使用批量插入.(多次插入需要频繁的建立连接.浪费资源)

    • 多次插入数据时,采用手动提交事务

      sql 语句在执行的时候如下所示, 会自动开启事务和提交事务. 我们可以手动开启事务,然后执行多条 sql 后,在手动提交事务.减少资源浪费.

    start transaction;   执行sql;   commit;
    
  15. order by 排序优化 (排序时,使用有索引的字段进行排序)
    使用order by排序时,会出现两种情况 (explain查看Extra字段)
    - using fileSort : 全表扫描,读取出数据,然后再排序缓冲区进行排序. (排序字段没有索引)
    - using index: 通过索引直接返回有序的数据. 不需要额外排序(有索引,效率高)

  16. count 优化
    速度: c o u n t ( ∗ ) > c o u n t ( 1 ) > c o u n t ( 字段 ) count(*)>count(1)>count(字段) count()>count(1)>count(字段)
    inndb 引擎的使用如下 (MyIASM默认存了数据总数,所以效率最高)

    • count(字段):遍历整张表 会把每一行的字段值取出来,然后返回
    • count(1): 便利整张表,但不取值,对于返回的数据,放入1进去.然后累加
    • count(*):inndb引擎,特意做了优化,不会取出值,直接服务层进行累加
  17. update优化 (避免出现表锁)

    innodb引擎使用update时,会有行锁/表锁两种模式, 如果where 字段没有索引的时候会升级成表锁,

    update table set xx=1 where  name=xx   (name没有索引,此时是表锁)
    update table set xx=1 where  id=xx  (id有索引,此时是行锁)
    
  18. 创建表时使用同一的编码

    mysql多表联查时,如果表的字符集不一样,会有一个数据类型转换的过程.

    例如 utf8utf8mb4 前者是3字节unicode编码,后者是4字节unicode编码. 此时如果多表查询,则索引会失效

三、 explain关键字

通过explain我们可以获得以下信息:

  • 表的读取顺序
  • 数据读取操作的操作类型
  • 哪些索引可以使用
  • 哪些索引被实际使用
  • 表之间的引用
  • 每张表有多少行被优化器查询

使用方法:explain + sql语句。会出现下方截图.根据下方的字段来进行解析sql的问题所在
例如:

 explain SELECT user_name FROM `user` WHERE id ="1060"

在这里插入图片描述

  1. id 字段
    id 可以认为是查询序列号,每一个 id 代表一个 select,一句 sql 有两个select,就会有两列,两个id.,不同的id代表不同子查询,id越大优先级越高,越先被解析,如果id相同,则按照从上到下的顺序查找.

  2. select_type:表示查询的类型

1. SIMPLE(简单SELECT,不使用UNION或子查询等)  
2. PRIMARY(子查询中最外层查询,查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)
3. UNION(UNION中的第二个或后面的SELECT语句)
4. DEPENDENT UNION(UNION中的第二个或后面的SELECT语句,取决于外面的查询)
5. UNION RESULT(UNION的结果,union语句中第二个select开始后面所有select)
6. SUBQUERY(子查询中的第一个SELECT,结果不依赖于外部查询)
7. DEPENDENT SUBQUERY(子查询中的第一个SELECT,依赖于外部查询)
8. DERIVED(派生表的SELECT, FROM子句的子查询)
9. UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存,必须重新评估外链接的第一行)
  1. table:输出结果集的表

    显示这一行的数据是关于哪张表的,有时不是真实的表名字,可能是简称

  2. type:表示表的连接类型

    对表访问方式,表示MySQL在表中找到所需行的方式,又称“访问类型”。

    常用的类型有: ALL、index、range、 ref、eq_ref、const、system、NULL(从左到右,性能从差到好)

    ALLFull Table Scan, MySQL将遍历全表以找到匹配的行
     
    index: Full Index Scan,indexALL区别为index类型只遍历索引树
     
    range:只检索给定范围的行,使用一个索引来选择行
     
    ref: 表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
     
    eq_ref: 类似ref,区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配,简单来说,就是多表连接中使用primary key或者 unique key作为关联条件
     
    const、system: 当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于where列表中,MySQL就能将该查询转换为一个常量,system是const类型的特例,当查询的表只有一行的情况下,使用system
     
    NULL: MySQL在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成。
    
  3. possible_keys:表示查询时,可能使用的索引

    指出MySQL能使用哪个索引在表中找到记录,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用(该查询可以利用的索引,如果没有任何索引显示 null)

    该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。

    如果该列是NULL,则没有相关的索引。在这种情况下,可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样,创造一个适当的索引并且再次用EXPLAIN检查查询

  4. key:表示实际使用的索引

    key列显示MySQL实际决定使用的键(索引),必然包含在possible_keys

    如果没有选择索引,键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX

  5. key_len:索引字段的长度

    表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度(key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的)

    不损失精确性的情况下,长度越短越好

  6. ref:列与索引的比较

    列与索引的比较,表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值

  7. rows:扫描出的行数(估算的行数)

    估算出结果集行数,表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数

  8. Extra:执行情况的描述和说明

    该列包含MySQL解决查询的详细信息,有以下几种情况:

    Using where:不用读取表中所有信息,仅通过索引就可以获取所需数据,这发生在对表的全部的请求列都是同一个索引的部分的时候,表示mysql服务器将在存储引擎检索行后再进行过滤
     
    Using temporary:表示MySQL需要使用临时表来存储结果集,常见于排序和分组查询,常见 group by ; order by
     
    Using filesort:当Query中包含 order by 操作,而且无法利用索引完成的排序操作称为“文件排序”
     
    -- 测试Extra的filesort
    explain select * from emp order by name;
    Using join buffer:改值强调了在获取连接条件时没有使用索引,并且需要连接缓冲区来存储中间结果。如果出现了这个值,那应该注意,根据查询的具体情况可能需要添加索引来改进能。
     
    Impossible where:这个值强调了where语句会导致没有符合条件的行(通过收集统计信息不可能存在结果)。
     
    Select tables optimized away:这个值意味着仅通过使用索引,优化器可能仅从聚合函数结果中返回一行
     
    No tables used:Query语句中使用from dual 或不含任何from子句
     
    -- explain select now() from dual;
    
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

ZhShy23

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值