Mysql索引调研
什么是索引
索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。索引是针对表而建立的，它是由数据页面以外的索引页面组成的，每个索引页面中的行都会含有逻辑指针，以便加速检索物理数据
索引的优点(作用)
能快速获取数据
能保证数据记录的唯一性
实现表与表之间的参照完整性
在使用ORDER by、group by子句进行数据检索时，利用索引可以减少排序和分组的时间。
索引的缺点
1.建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件会得膨胀很快。
2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度，因为更新表时，MySQL不仅要保存数据，还要保存一索引文件。 MySQL索引是在存储引擎中实现的，而不是在服务器层中实现的。所以，每种存储引擎的索引都不一定完全相同，并不是所有的存储引擎都支持所有的索引类型。
MySQL支持的存储引擎
MyISAM
InnoDB
MERGE
MEMORY
Archive
各存储引擎对比
| 功能 | MyISAM | MEMORY | InnoDB |
|---|---|---|---|
| 存储限制 | 256TB | RAM | 64TB |
| 支持事务 | No | No | Yes |
| 支持全文索引 | Yes | No | No |
| 支持B+Tree索引 | Yes | Yes | Yes |
| 支持Hash索引 | No | Yes | No |
| 支持es索引 | No | No | Yes |
| 支持数据索引 | No | Yes | Yes |
| 支持数据压缩 | Yes | No | No |
| 空间使用率 | 低 | N/A | 高 |
| 支持外键 | No | No | Yes |
索引的分类：
按数据结构分类可分为：B+tree索引、Hash索引、Fulltext索引、R-Tree索引。
按物理存储分类可分为：聚簇索引、二级索引（辅助索引）。
按字段特性分类可分为：主键索引、普通索引、唯一索引。
按字段个数分类可分为：单列索引、联合索引（复合索引、组合索引）。
按结构分类
1.FULLTEXT
FULLTEXT即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。
全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%”这类针对文本的模糊查询效率较低的问题。
2.HASH
相信大家对hash（哈希）并不陌生。在数据库索引中hash就是一种（key=>value）形式的键值对，如数学中的函数映射，允许多个key对应相同的value，但不允许一个key对应多个value。正是由于这个特性，hash很适合做索引，为某一列或几列建立hash索引，就会利用这一列或几列的值通过一定的算法计算出一个hash值，对应一行或几行数据
Hash虽然大多数时候很高效，当其仍存在着不足之处，在某些情况下并不适用
（1）Hash 索引仅仅能满足”=”,”IN”和”<=>”查询，不能使用范围查询。 由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。
（2）Hash 索引无法被用来避免数据的排序操作。 由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；
（3）Hash 索引不能利用部分索引键查询。 对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。
（4）Hash 索引在任何时候都不能避免表扫描。 前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。
（5）Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。 对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。
3.B+Tree
BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。
BTREE在MyISAM里的形式和Innodb稍有不同
在 Innodb里，有两种形态：一是primary key形态，其leaf node里存放的是数据，而且不仅存放了索引键的数据，还存放了其他字段的数据。二是secondary index，其leaf node和普通的BTREE差不多，只是还存放了指向主键的信息.
而在MyISAM里，主键和其他的并没有太大区别。不过和Innodb不太一样的地方是在MyISAM里，leaf node里存放的不是主键的信息，而是指向数据文件里的对应数据行的信息.
4.R-Tree
RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。相对于BTREE，RTREE的优势在于范围查找。
各种索引的使用情况
（1）对于B+TREE这种Mysql默认的索引类型，具有普遍的适用性
（2）由于FULLTEXT对中文支持不是很好，在没有插件的情况下，最好不要使用。其实，一些小的博客应用，只需要在数据采集时，为其建立关键字列表，通过关键字索引，也是一个不错的方法。
（3）对于一些搜索引擎级别的应用来说，FULLTEXT同样不是一个好的处理方法，Mysql的全文索引建立的文件还是比较大的，而且效率不是很高，即便是使用了中文分词插件，对中文分词支持也只是一般。真要碰到这种问题，Apache的Lucene或许是你的选择。
（4）正是因为hash表在处理较小数据量时具有无可比拟的素的优势，所以hash索引很适合做缓存（内存数据库）。如mysql数据库的内存版本Memsql，使用量很广泛的缓存工具Mencached，NoSql数据库redis等，都使用了hash索引这种形式。当然，不想学习这些东西的话Mysql的MEMORY引擎也是可以满足这种需求的。
（5）至于R-TREE, 是一种处理移动对象数据库历史查询的索引结构，使用情况比较少。
字段特性分类索引
按字段特性来分，Mysql常见索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引
PRIMARY KEY（主键索引）
是一种特殊的索引，不允许有空值
1 | ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) |
UNIQUE(唯一索引)
与”普通索引“类似，不同的就是：索引列的值必须是唯一，但允许有空值
1 | ALTER TABLE `table_name` ADD UNIQUE (`column`) |
INDEX(普通索引)
最基本的索引，没有任何限制
1 | ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) |
FULLTEXT(全文索引)
仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时占空间
1 | ALTER TABLE `table_name` ADD FULLTEXT ( `column` ) |
组合索引（包含多列的索引，与单行索引相对）
为了提高多条件查询效率，可建立组合索引，遵循”最左前缀匹配原则“
1 | ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` ) |