• ==欢迎来访我的个人博客== :Mosey

Explain分析Sql性能

前言

在我们平时开发中,因为系统可能属于初级阶段,数据库的数据还不是很多,所以自我感觉写的Sql语句上运行起来速度还不错,殊不知,随着时间推移,数据量日益增多,系统的查询效率会在打折扣,所以,Sql性能调优手段还得了解一下下。

Explain是什么?有什么用?

  • Explain可以用来分析一条Sql语句的运行效率。根据执行后的结果字段来判断查询有没有用“索引”、“全表扫描还是只扫描一些行”、“当前sql处于哪个性能级别”等等。

Explain怎么用?

  • 用法:explain 待分析的Sql语句

Explain返回字段了解一下

id越大,越先执行(单表Sql除外),id相同,则执行顺序由上而下

simple: 简单select(无union或where子查询)
primary: 最外层select
subquery:子查询中中第一个select

Sql查询对应的数据库表名称

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all

查询时,可能用到的索引

查询时,真正用到的索引

可片面理解为索引长度,不过不是真正的长度,Sql自有一套计算长度规则

扫描了多少行。

是否使用索引 ,是否使用where,是否使用distinct等待
性能级别:useing index>usinh where > using temporary | using filesort

实战小例子

  • 没加索引前
    cRzilD.png
  • 在t_erchang_sanjian_user表的user_name字段加了索引后
    cWSVvF.png
  • 又在t_erchang_sanjian_photo表中的create_by字段加了索引后
    cW9Z79.png

写Sql时要注意点什么

  • where后面的字段的值要加单引号‘’不然如果该字段有索引,则索引失效
  • join连接多表(两到三个表最优)查询时,每个表设置别名,字段也用别名.字段表示
  • 有大量重复数据的字段不适合创建索引,如性别(男,女),是否删除(0,1)这类型字段。
  • 虽然索引能加快查询速度,但是建议单表最多设置5个索引,因为索引太多会影响插入和更新速度
  • 避免同时修改或删除大量数据,这样会造成cpu飙升,从而影响别人对数据库的访问
  • 删除重复索引,如果字段A和B创建了组合索引,那么就不必要单独为A再创建新的索引了(index_A_B相当于创建了A,AB索引,即最左匹配原则)。
  • 如果要插入大量数据,建议分批插入
  • 建议在where和order by涉及的列上建立组合索引
  • 连接查询相关:
    • Inner join 只保留两张表中完全匹配的结果集

    • left join 返回左表所有的行,即使在右表中没有匹配的记录。

    • right join 返回右表所有的行,即使在左表中没有匹配的记录。

  • 避免在索引列上使用mysql的内置函数,如(时间类函数等)
  • 不寻底常用select *,应该要明确要查的具体字段
  • 平时开发中,可日志打印一下完整sql放到数据库客户端运行一下排错
  • 存储引擎中,
    • MyISAM支持表级锁,不支持事务和全文索引
    • InnoDB支持行级锁、支持事务安全(ACID兼容)、支持外键