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

一、概述

二叉树——>索引文件：效率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）！