一、索引管理
1、创建/删除索引
第一种方式：ALTER TABLE XX ADD xx:
ALTER TABLE tb_name |ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,…) [index_option] …
ALTER TABLE tb_name DROP PRIMARY KEY| DROP {key|index} index_name
第二种方式：CREATE/DROP INDEX
CREATE [UNIQUE] INDEX index_name [index_type] ON tb_name(index_col_name,…)
DROP INDEX index_name ON tb_name;
2、查看索引
查看表的索引信息，用SHOW INDEX FROM tb_name;
如查看t3表的索引：
这个表各个字段的含义如下：
• Table：索引所在的表名；
• Non_unique：非唯一索引；
• Key_name：索引名称
• Seq_in_index：索引中该列的位置
• Column_name：索引列的名称
• Collation：列以什么方式存储在索引中。B+树为A，即排序的。如果是Hash索引，为NULL。
• Cardinality：索引中唯一值数目的估算，Cardinality的值应该近可能接近1，如果非常小，可以考虑删除此索引。
• Sub_part：是否是列的部分索引，表示只对列的前N个字符索引，如果索引整个列，值为NULL；
• Packed：关键字如何压缩，如果没有压缩，值为NULL。
• NULL：是否索引的列含有NULL值，没有时为空，否则为yes；
• Index_type：索引的类型。
• Comment：注释。
• Index_comment：索引的注释；
Cardinality的值很关键，优化器会根据此值来判断是否使用索引。这个值不是实时更新的，不是每次索引时都更新此值，代价太大，这只是一个大概的值。如果要更新此值，使用ANALYZE TABLE命令。如analyze table t3;
在一些核心表作这操作，可以使用优化器和索引更好的工作。
二、索引的创建原理
1、Fast Index Create
5.5版本之前，创建索引的操作为：
• 首先创建一张临时表，表结构为通过Alter Table命令新定义的结构；
• 然后把原表的数据导到临时表；
• 接着删除原表；
• 最后把临时表名改为原来的表名；
这会导致数据库表在创建索引时不可用。
InnoDB1.0.x后使用Fast Index Create的方式来创建索引，对于辅助索引的创建，加S锁，不需要重建表。
临时表的目录是tmpdir，如果此目录空间不足，将会创建索引失败；这种方式只能对辅助索引有用，主键索引的创建和删除都需要重新建表。
2、Online Schema Change
FaceBook 通过PHP来实现的方式（略）。
3、Online DDL
Mysql 5.6后支持，在辅助索引创建的同时，允许insert/update/delete等DML操作。
不仅辅助索引，以下操作也可以以在线的方式进行：
• 辅助索引的创建与删除
• 改变自增长值；
• 添加或删除外键约束；
• 列的重命名；
通过新的ALTER TALBE的方式：
ALTER TABLE tb_name |ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,…) [index_option] …
ALOGRITHM[=]{DEFAULT|INPLACE|COPY}
LOCK[=]{DEFAULT|NONE|SHARED|EXCLUSIVE}
ALOGRITHM指定了创建索引时的算法，COPY为5.1之前的临时表方式。INPLAC表示索引创建或删除时，不需要临时表。
LOCK表示创建或删除索引时，对表的加锁情况：
• NONE表示不加锁
• SHARED表示加S锁，读操作不受影响，写操作阻塞；
• EXCLUSIVE表示加X锁，读写事务都不能进行。
• DEFAULT根据事务并发最大性来判断是NONE还是SHARED,EXCLUSIVE.
三、Cardinality值
只有访问表很少部分数据时，加索引才有意见，如性别，地区，类型等字段，选择性较小，加锁引效果就不好。如姓名等少重复的字段，为高选择性，加索引效果才好。
可以通过Cardinality来判断该字段是否属于高选择性字段。
cardinality字段的统计原理：
cardinality字段的更新一般发生在insert/update操作中，InnoDB引擎对cardinality的更新策略为：
• 表中1/16的数据已经发生变化；
• Stat_modify_counter > 2 000 000 000
第二种情况为InnoDB引擎中维护一个Stat_modify_counter来表示变化的次数
参考《MySQL技术内幕 -InnoDB存储引擎》整理，如侵权请联系vinin@163.com。