mysql索引执行计划_mysql索引和执行计划

索引结构

索引都是采用B+树，允许在同1列上重复创建索引；

唯一性索引可以避免下一行额外扫描，普通索引在找到侯选列后，会读取下一行判断其是否依旧满足查询条件，状态变量Handler_read_next记录相应操作；

InnoDB默认页大小16K，默认预留1/16的空闲；如果顺序插入则填充因子为15/16，随机插入则为15/16 – ½，即预留空间比较多；

InnoDB二级索引都包含主键列，且为最后1位；

更确切的说，当二级索引不包含或包含部分主键列时，InnoDB会自动补全所有主键列，但Mysql对此不知晓故无法在sql执行时使用；

索引类型

MyISAM支持fulltext索引且仅限于char/varchar/text；

memory表支持hash索引；

不支持基于函数的索引；

Mysql分区只支持本地索引；

InnoDB快速创建索引

在之前的版本中，创建索引必须创建一张临时表，然后更新临时表后将原表删除，最后重命名临时表，比较耗时；

这个过程很低效，因此5.5(包括采用plugin的5.1)引入一个新功能，叫做fast index creation，仅适用于二级索引；

其直接在原表的基础上创建索引，从而废除了临时表的使用，在创建过程中原表添加共享锁，不阻塞读操作，且索引页的填充率即fill factor更高；

注：如果重建聚集索引，依旧采用重建copy的方式，原表加排他锁，阻塞所有操作；

如果需要创建多个二级索引，可以调用alter table add index一次性执行，避免多次扫描表；

在二级索引快速创建时，需要向$TMPDIR目录写临时文件；

当调用alter table … rename column时，为避免innodb和mysql数据字典不一致，依旧采用重建+copy的方法；

5.5版本暂不对foreign key提供此功能；

此功能仅支持InnoDB，故MyISAM依旧采用创建临时表+ copy的方式创建索引，但可通过调优几个参数加速索引的创建：

Myisam_max_sort_file_size：重建MyISAM索引允许使用的临时文件最大尺寸，如果超出了此规定值，就改用key cache效率就会比较慢；默认2G；

Myisam_sort_buffer_size：用于排序的buffer大小，repair table或创建索引时用到；

key_buffer_size

倘若碰到比较变态的大表，则可采用曲线救国的方法：

案例

为超过1.8亿条数据的表创建索引

需用到memory/merge表，也可使用分区替代；

首先将max_heap_table_size/tmp_table_size调大至4G或更大(视自身服务器而定)，用于存储内存表；

创建内存表，通过insert … select将原表数据装入内存表(假定1次可装载1千万行)，创建索引；如果只有几千万行，可以为内存表添加索引然后通过insert … select将其刷新至目标表，反复几次即可；

创建18个merge表，insert … select将数据加入内存表àinsert … select从内存表刷新至merge表àtruncate内存表反复18次；

创建一个merge表整合18个表，然后insert … select from merge_table order by index1, index2将此表刷新至目标表即可；

索引Hint

Using index–指定所用索引

Straight_join– 强制表连接顺序

Optimizer_switch

执行计划

使用explain可查看sql执行计划

其输出包含多个列，比较重要的如下

Key_len：使用到的索引键的字节长度，其受数据类型,字符集以及not null影响；Null +1字节；varchar + 2字节；

长度

数据类型及约束

4

Int not null

5

Int null

30

Char(30) not null

32

Varchar(30) not null

92

Varchar(30) null charset=utf8

3

Date

4

timestamp

8

datetime

Select type

SIMPLE

Simple SELECT (not using UNION or subqueries)

PRIMARY

Outermost SELECT

Second or later SELECT statement in a UNION

DEPENDENT UNION

Second or later SELECT statement in a UNION, dependent on outer query

UNION RESULT

Result of a UNION.

SUBQUERY

First SELECT in subquery

DEPENDENT SUBQUERY

First SELECT in subquery, dependent on outer query

DERIVED

Derived table SELECT (subquery inFROMclause)

UNCACHEABLE SUBQUERY

A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query

UNCACHEABLE UNION

The second or later select in a UNION that belongs to an uncacheable subquery (seeUNCACHEABLE SUBQUERY)

Extra

using index使用覆盖索引；

using temporary使用了基于内存的临时表，1个query可引用多个，一旦达到限定条件就创建于磁盘上；

using filesort—order by引起的排序；

using join buffer在join时没有使用索引并且需要join buffer存储中间结果；

impossible where--where语句会导致没有符合条件的行；

distinct—mysql在找到第一个匹配行后就停止搜索；