6.MySQL索引(二)

MySQL索引分类

  • BTree索引
  • Hash索引
  • full-text全文索引
  • R-tree索引

MySQL官方对索引的定义为:索引(Inex)是帮助MySQL高效获取数据的数据结构。

索引的目的在于提高查询效率, 可以类比字典。

索引可以影响 查询条件和排序

单值索引

即一个索引只包含单个列,一个表可以有多个单列索引

唯一索引

索引列的值必须唯一,但允许有空值

复合索引

即一个索引包含多个列

基本语法

创建

CREATE [UNIQUE] INDEX indexName on myTable(columnName(length))

删除

DROP INDEX [indexName] ON myTable;

查看

SHOW INDEX FROM table_name\g

修改索引

有四种方式来添加数据表的索引

ALTER TABLE tbl_name ADD PRIMARY KEY(column_list); 该语句添加一个主键, 这意味着索引值必须是唯一的,且不能为NULL

ALTER TABLE tbl_name ADD UNIQUE index_name(column_list); 创建索引的值是唯一的(除NULL外,NULL 可能出现多次)

ALTER TABLE tbl_name ADD INDEX index_name(column_list); 添加普通索引,索引值可出现多次

ALTER TABLE tbl_name ADD FULLTEXT index_name(column_list);创建全文索引

需要建索引的情况

  • 1.主键自动建立唯一索引
  • 2.频繁作为查询条件的字段应该创建索
  • 3.查询中与其它表关联的字段,外键关系建立索引
  • 4.频繁更新的字段不适合创建索引
  • 5.Where 条件里用不到的字段不创建索引
  • 6.单键/组合索引的选择问题,who?(在高并发下倾向创建组合索引)
  • 7.查询中排序的字段,排序字段若通索引去访问将大提高排序速度。
  • 8.查询中统计或者分组字段

哪引动情况不需要建索引

  • 1.表记录太少
  • 2.经常增删改的表
  • 3.重复且分布平均的表字段。比如性别

性能分析

Explain

使用Explain关键字可以模拟优化器执行SQL查询语句,从而知道 MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

语法

explain + SQL

mysql> explain select * from  tbl_emp;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | tbl_emp | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

作用

  • 表的读取顺序
  • 数据读取操作的操作类型
  • 哪些索引可以使用
  • 哪些索引被实际使用
  • 表之间的引用
  • 每张表有多少行被优化器查询
id

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

三种情况:

  • id 相同,执行顺序由上至下
explain select t2.* from t1, t2, t3 where t1.id = t2.id and t1.id = t3.id and t1.other_column='';
  • id不同,如果是子查询,id 序号会递增,id值越大优先级越高,越先被执行
  • id即有相同,又有不同。 id 值越大,越先被执行,然后相同的顺序执行。

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

select_type

  • SIMPLE 简单的select 查询,查询中不包含子查询或者union
  • PRIMARY 查询中若包含任何复杂的子查询,最外层查询则被标记为PRIMARY
  • SUBQUERY 在SELECT或WHERE列表中包含了子查询
  • DERIVED 在FROM列表中包含的子查询被标记为DERIVED(衍生)表,MySQL会递归执行这些子查询,把结果放在临时表里。
  • UNION 若第个SELECT出现在UNION 之后,则被标记为UNION;若UNION包含在FROM子句的子查询中,外层将被标记为:DERIVED
  • UNION RESULT 从UNION表获取结的SELECT, 也就是联合查询后的结果

table 显示这一行的数是关于哪张表的

type 访问类型

显示查询使用了何种类型

  • ALL

  • index

  • range

  • ref

  • eq_ref

  • const, system

  • null

  • system 表只有一行记录(等同于系统表),这是const类型的特例,平不会出现,这个也可以忽略不计

  • const 表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据,所以很快。如果将主键置于where列表中,MySQL就能将该查询转换为一个常量。

  • eq_ref 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描 。

  • ref 非唯一性索引扫描,返回匹配某人单独值的所有行。本质上也是一种索引访问,它返回所有匹配某个单独值的行。然而,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体

  • range 只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引。一般就是在你的 where语句中出现了between < > in 等的查询。这种范围索引扫描比全表扫描要好,因为它只需要开始于索引的某一点,而结束于另一点,不用扫描全部索引。

  • index Full Index Scan, index与ALL区别为index类型只遍历索引树。这通常比ALL快,因为索引文件通常比数据文件小。也就是说虽然all和Index都是读全表,但index是从索引中读取的,而all是从硬盘中读的。

  • ALL 全表抛扫描 常见的从最好到最差依次是:

System > const > eq_ref > ref >range > index > ALL

一般来说,得保证查询至少达到 range和ref级别

key 实际使用的索引。如果为NULL ,则没有使用索引。查询中若使用了覆盖索引,则该索引仅出现在key列表中。

覆盖索引:

使查询的字段,和建索引的字段和顺序相同。

  • possible_keys 显示可能应用到这张表中的索引,一个或多个。查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用。

  • key_len 表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下,长度越短越好。 key_len显示的值为索引字估的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的。

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

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

  • Extra 包含 不适合在其他列中显示,但十分重要的额外信息。

  • Using filesort 说明MySQL会对数据使用一个外部的索引排序, 而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作称为“文件排序”。

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

  • Using index 表示相应的SELECT操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错!如果同时出现Using where,表明索引被用来执行索引键值的查找;如果没有同时出现Using where,表明索引用来读取数据而非执行查找动作。直接从索引上读取到了数据,并没有执行查找表的数据。

覆盖索引(Covering Index), 一说索引覆盖。

就是select的数据列只用从索引中就能够取得,不必读取数据行,MySQL可以利用索引返回Select列表中的字段,而不必根据索引再次读取数据文 件,换句话说查询列要被所建的索引覆盖。

注意: 如果要使用覆盖索引,一定要注意select列表中只取出需要的列,不可select *,因为如果将所有字段一起做索引会导致索引文件过大,查询性能下降。

  • using where 使用了where过滤
  • using join buffer 使用了连接缓存
  • impossible where where 子句的值总是false, 不能用来获取任何元组。
  • select tables optimized away 在没有GROUPBY 子句的情况下,基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作,不必等到执行阶段再进行计算,查询执行计划生成的除段即完成优化。
  • distinct 优化DISTINCK 操作,在找到第一个匹配的元组后即停止找同样值的动作。