当前位置:Gxlcms > mysql > MySQL高级十三——通过索引优化SQL

MySQL高级十三——通过索引优化SQL

时间:2021-07-01 10:21:17 帮助过:46人阅读

一、概述

二叉树——>索引文件:效率log2N

检索10次:2的10次方。1024条记录。

索引带来的开销

查看数据的文件(安装目录下的data目录),会发现三个文件,

.frm:表示表的结构

.myd:表示数据

.myi:表示索引的文件

索引带来的问题:会导致insert,update,delete的效率

更新频繁的字段不适合创建索引。

唯一性比较差的字段不适合创建索引。比如人的性别只有男和女

满足一下条件才会创建索引

1、肯定在where条件中经常使用到。

2、该字段的变化不会太频繁。

二、索引使用场景

1、快速查找符合where条件的记录。

2、快速确定候选集。若where条件使用了多个索引字段,则MySQL会优先使用能使候选集规模最小的那个索引,以便尽快淘汰不符合条件的记录。

3、如果表中存在几个字段构成的联合索引,则查找记录时,这个联合索引的最左前缀匹配字段也会被自动作为索引来加速查找。

例如:若为某张表创建了3个索引,(c1,c2,c3)构成的联合索引,则(c1),(c1,c2),(c1,c2,c3)均会作为索引,(c2,c3)就不会被作为索引,而(c1,c3)其实只利用到c1索引。

4、多表做join操作时会使用索引(如果参与join的字段在这些表中均建立了索引的话)

5、若某个字段已建立索引,求该字段做sort或group操作时,MySQL会使用索引。

三、通过EXPLAIN分析执行低效SQL的执行计划

如:

mysql> explain select * from taxgrouptaxes\G  
*************************** 1. row ***************************  
           id: 1  
  select_type: SIMPLE  
        table: taxgrouptaxes  
         type: ALL  
possible_keys: NULL  
          key: NULL  
      key_len: NULL  
          ref: NULL  
         rows: 1  
        Extra:

select_type :表示SELECT 的类型,

常见的取值有

SIMPLE:简单表,既不使用表连接或者子查询的

PRIMARY :主查询,既外层的查询

union :UNION 中的第二个或者后面的查询语句

SUBQUERY :子查询中的第一个select .

table :输出结果集的表

type 表示MYSQL 在表中找到所需行的方式。或者叫访问类型。

常见的类型有以下几种;

all

index

range

ref

eq_ref

const,system

null

从上到下呢,性能有最差到最好。

1:type=all 全表扫描。

2:type=index 索引扫面

3:type=tange 索引范围扫描。常见于< <= > >= between 等操作

4:type=ref s使用费唯一索引扫描或唯一索引的前缀扫描。

5:type=eq_ref 类似ref 区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配。

6:type=const/system 单表中最多有一个匹配行,查询起来非常迅速。

7 : type=null MYSQL 不用访问表或者索引。直接就能够得到结果。

possible_keys: 表示查询时可能使用的索引

key: 表示实际使用的索引

key_len: 使用到索引字段的长度

rows : 扫描行的数量

Extra : 执行情况的说明和扫描。包含不适合的其他列中显示单是对执行计划非常重要 。

四、通过show profile分析SQL

1、首先查看MySQL是否支持show profile

mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+
1 row in set (0.00 sec)

2、如果profile是关闭的,可以通过set语句在session级别开启profile

set profiling = 1;

3、执行完毕之后,可以通过show profiles语句,查看当前SQL的queryID。

4、通过show profile for query queryID

以上就是MySQL高级十三——通过索引优化SQL的内容,更多相关内容请关注PHP中文网(www.gxlcms.com)!

人气教程排行