本文共 3564 字，大约阅读时间需要 11 分钟。
mysql 索引分为 主键索引（聚簇索引）、普通索引、唯一索引、组合索引、全文索引
ALTER TABLE TABLENAME ADD PRIMARY KEY (`字段名`);
基本的索引类型，值可以为空，没有唯一性的限制
ALTER TABLE TABLE_NAME ADD INDEX (`字段名`);
索引列的所有值都只能出现一次，即必须唯一，值可以为空。
CREATE UNIQUE INDEX `索引名称` ON table_name(`字段名`);
多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
ALTER TABLE TABLE_NAME ADD INDEX `索引名` (`字段名1`,`字段名2`,`字段名3`);
组合索引“最左前缀”原则，把最常用作为检索或排序的列放在最左，依次递减，组合索引相当于建立了(字段名1,字段名1字段名2,字段名1字段名2字段名3)三个索引，而字段2或者字段3是不能使用索引的。
5.6版本之后InnoDB存储引擎开始支持全文索引，5.7版本之后通过使用ngram插件开始支持中文。之前仅支持英文，因为是通过空格作为分词的分隔符，对于中文来说是不合适的
MySQL允许在char、varchar、text类型上建立全文索引ALTER TABLE TABLE_NAME ADD FULLTEXT(`字段名`);
MySQL支持三种模式的全文检索模式
1.自然语言模式：通过MATCH AGAINST 传递某个特定的字符串进行检索 2.布尔模式：可以为检索的字符串增加操作符， “+”表示必须包含 “-”表示必须排除 “>”表示出现该单词时增加相关性 “<”表示出现该单词时降低相关性 “*”表示通配符 “~”允许出现该单词，但是出现时相关性为负 “""”表示短语 no operation表示find word是可选的，如果出现，相关性会更高mysql> show variables like '%ft_boolean_syntax%'; +-------------------+----------------+ | Variable_name | Value | +-------------------+----------------+ | ft_boolean_syntax | + -><()~*:""&| | +-------------------+----------------+
3.查询扩展模式：
应用场景：查询的关键字太短，用户需要implied knowledge(隐含知识)时进行。比如，对于单词database的查询，用户可能希望查询的不仅仅是包含database的文档，还指那些包含mysql、oracle、db2的单词。 该查询会执行两次检索，第一次使用给定的短语进行检索，第二次结合第一次相关性比较高的进行检索Mysql目前主要有以下几种索引类型：FULLTEXT，HASH(不适合做区间搜索)，BTREE，B+Tree
只有memory（内存）存储引擎支持哈希索引，哈希索引用索引列的值计算该值的hashCode，然后在hashCode相应的位置存执该值所在行数据的物理位置，因为使用散列算法，因此访问速度非常快，但是一个值只能对应一个hashCode，而且是散列的分布方式，因此哈希索引不支持范围查找和排序的功能。
BTREE是平衡搜索N叉树，BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中，每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型
在BTree的结构下，就可以使用二分查找的查找方式，查找复杂度为h*log(n)，一般来说树的高度是很小的，一般为3左右，因此BTree是一个非常高效的查找结构
在 MySQL 中，索引是在存储引擎层实现的。以主键顺序存在B+树中。
当插入到索引树最后，只需直接插入。
但当插入到索引树中间，需要逻辑上挪动后面的数据，空出位置，并且当数据页满时，需要申请一个新的数据页，然后挪动部分数据过去（页分裂）。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。自增索引（追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂）
业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。 二级索引的叶子节点为主键，业务字段做主键时会占大量存储空间。B+Tree是BTree的一个变种，设d为树的度数，h为树的高度，B+Tree和BTree的不同主要在于：
B+Tree中的非叶子结点不存储数据，只存储键值；
B+Tree的叶子结点没有指针，所有键值都会出现在叶子结点上，且key存储的键值对应的数据的物理地址
一般来说B+Tree比BTree更适合实现外存的索引结构，因为存储引擎的设计专家巧妙的利用了外存（磁盘）的存储结构，即磁盘的一个扇区是整数倍的page（页），页是存储中的一个单位，通常默认为4K，因此索引结构的节点被设计为一个页的大小，然后利用外存的“预读取”原则，每次读取的时候，把整个节点的数据读取到内存中，然后在内存中查找，已知内存的读取速度是外存读取I/O速度的几百倍，那么提升查找速度的关键就在于尽可能少的磁盘I/O，那么可以知道，每个节点中的key个数越多，那么树的高度越小，需要I/O的次数越少，因此一般来说B+Tree比BTree更快，因为B+Tree的非叶节点中不存储data，就可以存储更多的key。
FULLTEXT（全文）索引，仅可用于MyISAM和InnoDB，针对较大的数据，生成全文索引非常的消耗时间和空间。对于文本的大对象，或者较大的CHAR类型的数据，如果使用普通索引，那么匹配文本前几个字符还是可行的，但是想要匹配文本中间的几个单词，那么就要使用LIKE %word%来匹配，这样需要很长的时间来处理，响应时间会大大增加，这种情况，就可使用时FULLTEXT索引了，在生成FULLTEXT索引时，会为文本生成一份单词的清单，在索引时及根据这个单词的清单来索引。FULLTEXT可以在创建表的时候创建，也可以在需要的时候用ALTER或者CREATE INDEX来添加
实际上，我们在建立索引时关注的是区分度，区分度越高越好（区分度distinction=count(distinct name)/count(name)，distinction越接近1越好）。因为区分度越高，意味着重复的键值越少。在建立字符串索引时我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。
1.主键自动建立唯一索引；
2.要根据查询有针对性的创建，考虑在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描
3.查询中与其他表关联的字段，外键关系建立索引
4.高并发条件下倾向组合索引；
5.字符字段只建前缀索引
6.使用多列索引时主意顺序和查询条件保持一致，同时删除不必要的单列索引
7.单列索引无法储null值，复合索引无法储全为null的值。查询时，采用is null条件时，不能利用到索引，只能全表扫描。
8.业务允许情况下优先使用普通索引而不是唯一索引（唯一索引开销更大磁盘随机IO）
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，不仅要保存数据，还要保存一下索引文件，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚集索引那么需要的空间就会更大。
转载地址：http://eqmdi.baihongyu.com/