优化体系--MySQL 索引优化（full-text、btree、hash、rtree）
概述
目前大部分数据库系统及文件系统都采用B-Tree(B树)或其变种B+Tree(B+树)作为索引结构。B+Tree是数据库系统实现索引的首选数据结构。在MySQL中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的。今天主要简单讲下mysql的几个索引类型，下面一起看看吧~
01
MySQL索引类型
mysql里目前只支持4种索引，分别是：full-text，b-tree，hash，r-tree
b-tree索引应该是mysql里最广泛的索引了，除了archive基本所有的存储引擎都支持它.
1、full-text索引
full-text在mysql里仅有myisam支持它，而且支持full-text的字段只有char、varchar、text数据类型。
full-text主要是用来代替like "%***%"效率低下的问题。
强烈注意：MySql自带的全文索引只能用于数据库引擎为MYISAM的数据表，如果是其他数据引擎，则全文索引不会生效。此外，MySql自带的全文索引只能对英文进行全文检索，目前无法对中文进行全文检索。如果需要对包含中文在内的文本数据进行全文检索，我们需要采用Sphinx（斯芬克斯）/Coreseek技术来处理中文。
ps：目前，使用MySql自带的全文索引时，如果查询字符串的长度过短将无法得到期望的搜索结果。MySql全文索引所能找到的词默认最小长度为4个字符。另外，如果查询的字符串包含停止词，那么该停止词将会被忽略。
如果可能，请尽量先创建表并插入所有数据后再创建全文索引，而不要在创建表时就直接创建全文索引，因为前者比后者的全文索引效率要高。
2、b-tree索引
b-tree在myisam里的形式和innodb稍有不同
在 innodb里，有两种形态：一是primary key形态，其leaf node里存放的是数据，而且不仅存放了索引键的数据，还存放了其他字段的数据。二是secondary index，其leaf node和普通的b-tree差不多，只是还存放了指向主键的信息.
采用InnoDB引擎的数据存储文件有两个，一个定义文件，一个数据文件。若建索引的字段不是主键ID，则对该字段建索引，然后在叶子节点中存储的是该记录的主键，然后通过主键索引来找到对应的记录
innodb
而在myisam里，主键和其他的并没有太大区别。不过和innodb不太一样的地方是在myisam里，leaf node里存放的不是主键的信息，而是指向数据文件里的对应数据行的信息。MyIsam中索引和数据分别存放在不同的文件，所以在索引树中的叶子节点中存放的数据是该索引对应的数据记录的地址，由于数据与索引不在一起，所以MyIsam是非聚簇索引。
3、hash索引
目前应该只有memory和ndb cluster支持这种索引。hash索引由于其结构，所以在每次查询的时候直接一次到位，不像b-tree那样一点点的前进。所以hash索引的效率高于b-tree，简单地说，哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。
但hash也有缺点，主要如下：
(1)由于存放的是hash值，所以仅支持<=>以及in操作.
(2)hash索引无法通过操作索引来排序，这是因为存放的时候经过hash计算，但是计算的hash值和存放的不一定相等，所以无法排序.
(3)在组合所以里，无法对部分使用索引.
(4)不能避免全表扫描，只是由于在memory表里支持非唯一值hash索引，就是不同的索引键，可能存在相同的hash值.
(5)当存在大量相同hash值得时候，hash索引的效率会变低。
4、r-tree索引
myisam支持空间索引，可以用作地理数据存储，R-tree无须前缀索引。空间索引会从所有维度来索引数据。查询时，可以有效地使用任意维度来组合查询。
不过r-tree在mysql很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有myisam、bdb、innodb、ndb、archive几种。
相对于b-tree，r-tree的优势在于范围查找。
02
mysql里sql语句注意点
1. myisam里所有键的长度仅支持1000字节，innodb是767
2. blob和text字段仅支持前缀索引
3. 使用!=以及<>不等于的时候，mysql不使用索引
4. 当在字段时候函数的时候，mysql无法使用索引；在join时条件字段类型不一致的时候，mysql无法使用索引；在组合索引里使用非第一个索引时也不使用索引.
5. 在使用like的时候，以%开头，即"%***"的时候无法使用索引；在使用or的时候，要求or前后字段都有索引
有时候mysql query optimizer会认为使用索引并不是最优计划,所以不使用索引。可以在sql语句里可以用use,force index,当然有时候使用也不会比不用快,所以需要忽略掉index方法是ignore index.
03
几个技巧
1. 强制连接顺序： STRAIGHT_JOIN
SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE …
由上面的SQL语句可知，通过STRAIGHT_JOIN强迫MySQL按TABLE1、TABLE2的顺序连接表。如果你认为按自己的顺序比MySQL推荐的顺序进行连接的效率高的话，就可以通过STRAIGHT_JOIN来确定连接顺序。
2. 强制使用临时表： SQL_BUFFER_RESULT
SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE …
当我们查询的结果集中的数据比较多时，可以通过SQL_BUFFER_RESULT，选项强制将结果集放到临时表中，这样就可以很快地释放MySQL的表锁（这样其它的SQL语句就可以对这些记录进行查询了），并且可以长时间地为客户端提供大记录集。
3. 分组使用临时表 SQL_BIG_RESULT和SQL_SMALL_RESULT
SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM TABLE1 GROUP BY FIELD1;
一般用于分组或DISTINCT关键字，这个选项通知MySQL，如果有必要，就将查询结果放到临时表中，甚至在临时表中进行排序。SQL_SMALL_RESULT比起SQL_BIG_RESULT差不多，很少使用。
后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注一下~
