mysql 索引笔记

MySQL 查看执行计划

MySQL 使用 explain + sql 语句查看 执行计划

EXPLAIN SELECT * FROM user WHERE nid = 3;

select_type	说明
SIMPLE	简单查询
PRIMARY	最外层查询
SUBQUERY	映射为子查询
DERIVED	子查询
UNION	联合
UNION RESULT	使用联合的结果

---

table : 正在访问的表名

---

type	说明
ALL	全数据表扫描
index	全索引表扫描
RANGE	对索引列进行范围查找
INDEX_MERGE	合并索引，使用多个单列索引搜索
REF	根据索引查找一个或多个值
EQ_REF	搜索时使用primary key 或 unique类型
CONST	常量，表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。
SYSTEM	系统，表仅有一行(=系统表)。这是const联接类型的一个特例。
性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
性能在 range 之下基本都可以进行调优

---

possible_keys : 可能使用的索引

---

key : 真实使用的

---

key_len : MySQL中使用索引字节长度

---

rows : mysql 预估为了找到所需的行而要读取的行数

---

extra	说明
Using index	此值表示mysql将使用覆盖索引，以避免访问表。
Using where	mysql 将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当(并且如果)它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。
Using temporary	mysql 对查询结果排序时会使用临时表。
Using filesort	mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。
Range checked for each record(index map: N)	没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的

limit

limit 匹配后就不会继续进行扫描

组合索引

建立索引 create index (索引名称) on table(字段1,字段2);

如上组合索引遵循最左字段匹配原则

创建组合索引，那个列在最左能使得效率最高，那么得根据实际数据进行以下语句测试，哪个字段的选择性更接近1就把哪个字段索引前面就好。

select count(distinct 字段1)/count(*) as  字段1_selectivity,

       count(distinct 字段2)/count(*) as  字段2_selectivity,

       count(*) from table

MYSQL 复杂语句执行顺序

(7) SELECT (8) DISTINCT <list>(1) FROM <table>(3) <join_type> JOIN <talbe>(2) ON <join_condition>(4) WHERE <where_condition>(5) GROUP BY <group_by_list>(6) HAVING <having_condition>(9) ORDER BY <order_by_condition>(10) LIMIT <limit_number>

1：from

第一步就是选择出from关键词后面跟的表,这也是sql执行的第一步:表示要从数据库中执行哪张表。

实例说明:在这个例子中就是首先从数据库中找到表T

2:join on

join是表示要关联的表，on是连接的条件。通过from和join on选择出需要执行的数据库表T和S,产生笛卡尔积,生成T和S合并的临时中间表Temp1。on:确定表的绑定关系,通过on产生临时中间表Temp2.

3:where

where表示筛选,根据where后面的条件进行过滤,按照指定的字段的值(如果有and连接符会进行联合筛选)从临时中间表Temp2中筛选需要的数据,注意如果在此阶段找不到数据，会直接返回客户端,不会往下进行.这个过程会生成一个临时中间表Temp3。注意在where中不可以使用聚合函数，聚合函数主要是(min\max\count\sum等函数)

4:group by

group by是进行分组，对where条件过滤后的临时表Temp3按照固定的字段进行分组,产生临时中间表Temp4，这个过程只是数据的顺序发生改变,而数据总量不会变化,表中的数据以组的形式存在

5:Having

对临时中间表Temp4进行聚合,这里可以为count等计数，然后产生中间表Temp5，在此阶段可以使用select中的别名

6:select

对分组聚合完的表挑选出需要查询的数据,如果为*会解析为所有数据,此时会产生中间表Temp6

7:Distinct

distinct对所有的数据进行去重,此时如果有min、max函数会执行字段函数计算，然后产生临时表Temp7

8:order by

会根据Temp7进行顺序排列或者逆序排列，然后插入临时中间表Temp8，这个过程比较耗费资源

9:limit

limit对中间表Temp8进行分页,产生临时中间表Temp9,返回给客户端