索引结构
索引都是采用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在找到第一个匹配行后就停止搜索;