索引结构

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

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

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

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

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

http://www.penglixun.com/tech/database/will_innodb_store_pk_in_index.html

索引类型

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

memory表支持hash 索引；

对blob/text列必须创建prefix索引，即取前N个字节，前缀最大可为1000(InnoDB则为767)；

不支持基于函数的索引；

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

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

案例

http://techdigger.wordpress.com/2009/08/03/creating-indexes-sorting-very-large-tables-fast-mysql/

为超过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 – 强制表连接顺序

优化sql解析

查询涉及的表越多，优化器搜寻最佳执行计划的时间就会越长，可通过如下两个参数控制解析时间：

Optimizer_prune_level: 默认为0，设置为1时优化器估算每个表返回行数进而自动过滤掉一些执行计划，但也有可能漏掉最优的

Optimizer_search_depth: 0..63默认为62，与查询涉及的表数目有关；若大于等于表数，则优化器会遍历每个执行计划以找出最优解，如小于则解析时间会降低，但不保证得到最优解；设置为0则让优化器自主选择；

Optimizer_switch　

5.1引入此参数，主要用于控制index merge http://dev.mysql.com/doc/refman/5.1/en/index-merge-optimization.html

mysql> SELECT @@optimizer_switch\G

*************************** 1. row ***************************

@@optimizer_switch: index_merge=on,index_merge_union=on,

                    index_merge_sort_union=on,

                    index_merge_intersection=on,

                    engine_condition_pushdown=on

执行计划

使用explain可查看sql执行计划

Explain extended—收集额外信息

Explain partitions—用于分区表

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

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

Select type

Ref

The ref column shows which columns or constants are compared to the index named in the key column to select rows from the table.

Filtered

The filtered column indicates an estimated percentage of table rows that will be filtered by the table condition

Join type

由优到劣 system(const) à eq_ref à ref à fulltext à ref or null à index merge à unique subquery/index subquery à range à index à all

Const: 为主键赋予常量，只返回一行

Eq_ref:  inner table使用主键或unique index，而outer table可能返回多行

Ref:  类似eq_ref，不同的是inner table使用普通索引或联合主键的前几列

Ref or null: 多用于自查询，同ref相比多了null检验， select * from table where key_column = expr or key_column is null

Index merge:

Unique_subquery: 在某些in子查询中替代ref  value in (select primary_key from table where expr)

Index_subquery: 同上，只是主键换成普通索引

Range: 索引范围扫描

Index: 全索引扫描

All: 全表扫描

Extra

using index使用覆盖索引；

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

using filesort—order by 引起的排序；

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

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

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