索引结构

索引都是采用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　

5.1引入此参数，主要用于控制index merge

 

 

执行计划

使用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 (not using or subqueries)
PRIMARY	Outermost
	Second or later statement in a
DEPENDENT UNION	Second or later statement in a , dependent on outer query
UNION RESULT	Result of a .
SUBQUERY	First in subquery
DEPENDENT SUBQUERY	First in subquery, dependent on outer query
DERIVED	Derived table (subquery in FROM clause)
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 that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

 

 

 

Extra

using index使用覆盖索引；

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

using filesort—order by 引起的排序；

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

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

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

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15480802/viewspace-757967/，如需转载，请注明出处，否则将追究法律责任。