MySQL之SQL优化详解(二)

时间:2024-02-01 22:47:30

MySQL之SQL优化详解(二)

1. SQL的执行顺序

1.1 手写顺序

 

1.2 机读顺序

 

 

2. 七种join

 

3. 索引

3.1 索引初探

  • 是什么: 排好序的快速查找数据结构

  • 两个主要的索引结构: B+tree 索引和哈希索引。

  • 如何建: 1. alter table table_name add index index_name; 2. create index index_name on table_name;

 

优点: 类似大学图书馆建书目索引,提高了检索效率,降低了数据库IO,同时还可以通过索引进行排序,降低数据排序的成本,降低了CPU的消耗

缺点: 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行 insertupdatedelete,因为更新表时不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段。

 

3.2 索引分类

1.主键索引:主键是一种唯一性索引,但它必须指定为PRIMARY KEY,每个表只能有一个主键

alter table tablename add primary key (`字段名`)

2.唯一索引:索引列的所有值都只能出现一次,即必须 唯一,值可以为 空

alter table tablename add unique (`字段名`)

3.普通索引:基本的索引类型,值可以为空,没有唯一性的限制

alter table tablename add index (`字段名`);

4.全文索引: 全文索引的索引类型为 FULLTEXT,全文索引只能创建在CHAR、VARCHAR、TEXT类型的字段上。查询数据量较大的字符串类型字段时,使用全文索引可以提高查询速度

alter table tablename add fulltext(`字段名`);

 

3.3 建与不建

对于MySQL的索引创建,我们经常有疑虑,那么什么时候该建什么时候不该建呢?

哪些情况需要创建索引

  1. 主键自动创建唯一索引

  2. 频繁作为查询条件的字段应该创建索引

  3. 查询中与其它表关联的字段,外键关系建立索引

  4. 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度

  5. 查询中统计或者分组字段

 

哪些情况不需要建索引

  1. 频繁更新的字段不适合创建索引
  2. where 条件用不到的字段不适合创建索引
  3. 注意,如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果

 

4. 性能分析Explain

Explain简称执行计划,使用Explain关键字可以模拟优化器执行SQL查询语句

用法:explain + SQL


 

1. id:select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序

① id 相同执行顺序由上至下

② id 不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行

③ id相同不相同,不相同

注:id如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行

 

2. select_type:查询的类型,主要是用于区分 普通查询、联合查询、子查询等的复杂查询

  • simple:简单的select 查询,查询中不包含子查询或者 union
  • primary:查询中若包含任何复杂的子部分,最外层查询则被标记为
  • subquery:在selectwhere列表中包含了子查询
  • derived:在from 列表中包含的子查询被标记为 derived(衍生)
  • union:若第二个select出现在之后,则被标记为 union(若union 包含from 子句的子查询中,外层select将被标记为:derived)
  • union result:从union 表获取结果的 select

 

3. table:显示这一行的数据是关于哪张表的

4. type:访问类型排列,显示查询使用了何种类型

从好到坏,system > const > eq_ref > ref > range > index > all

  • system:表只有一行记录(等于系统表),这是const 类型的特列,平时不会出现,这个也可以忽略不计
  • const:表示通过索引一次就找到了,const 用于比较 primary key或者unique索引
  • eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描
  • ref:非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问
  • range:只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引,一般就是在你的where语句中出现了 between、<、>、in等的查询
  • index:Full Index Scan,index与 all 区别为 index 类型只遍历索引树
  • all:Full Table Scan,将遍历全表以找到匹配的行

 

5. possible_key:显示可能应用在这张表的索引,一个或多个。(但不一定被实际应用)

6. key:实际使用的索引,如果为null,则没有使用索引。

查询中若使用了覆盖索引,则该索引与查询的select字段重叠

 

7. key_len:表示索引中使用的字节数

8. ref:显示索引的哪一列被使用,如果可能的话,是一个常数,哪些列或常量被用于查找索引列上的值

注:由key_len可知t1表的idx_col1_col2被充分使用,col1匹配t2表的col1col2匹配了一个常量,即 'ac'

 

9. rows:根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数

10. extra:包含不适合在其他列中显示但十分重要的额外信息

  • Using filesort (劣): mysql 会对数据使用一个外部的索引排序(文件排序),而不是照表内的索引顺序进行读取

 

  • Using temporary (劣):使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by

 

  • Using index (优):表示相应的select操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错!
  • Using where:表明使用了where 过滤
  • Using join buffer:表明使用了连接缓存
  • impossible where:where子句的值总是false,不能用来获取任何数据

 

  • select tables optimized away:select操作已经优化到不能再优化了(MySQL根本没有遍历表或索引就返回数据了
  • distinct:在select部分使用了distinc关键字

心法:

针对explain命令生成的执行计划,这里有一个查看心法。我们可以先从查询类型type列开始查看,如果出现all关键字,后面的内容就都可以不用看了,代表全表扫描。再看key列,看是否使用了索引,null代表没有使用索引。然后看rows列,该列用来表示在SQL执行过程中被扫描的行数,该数值越大,意味着需要扫描的行数越多,相应的耗时越长,最后看Extra列,在这列中要观察是否有Using filesort 或者Using temporary 这样的关键字出现,这些是很影响数据库性能的。