Explain查询Sql效率

Explain分析Sql性能

前言

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

Explain是什么？有什么用？

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

Explain怎么用？

用法：explain 待分析的Sql语句

Explain返回字段了解一下

!> id

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

!> select_type(查询类型)

• 只列举其中几种

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

!> table

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

!> type(联接类型)

• 可根据type知道sql的性能级别，all性能最差(全表扫描)，

• 我们平时开发写的Sql至少达到range性能级别，最好能达到ref，不然有可能出现性能问题

!> 性能级别天梯如下
> system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all

!> possible_keys

查询时，可能用到的索引

!> key

查询时，真正用到的索引

!> key_len

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

!> rows

扫描了多少行。

!> extra(额外信息)

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

实战小例子

!> 没加索引前

!> 在t_erchang_sanjian_user表的user_name字段加了索引后

!> 又在t_erchang_sanjian_photo表中的create_by字段加了索引后

写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兼容）、支持外键