MySQL系列(五)-索引优化
在进行数据库查询的时候，索引是非常重要的，当然前提是达到一定的数据量，如果数据量非常小的时候，完全可以把这些数据都加载到内存
中，即使全表扫描，照样很快
索引依赖存储引擎层实现，所以支持的索引类型和存储引擎相关，同一种索引底层实现在不同存储引擎也是不一样的
创建索引语法：
CREATE TABLE table_name[col_name data_type]
[UNIQUE|FULLTEXT|SPATIAL]
[INDEX|KEY]
[index_name](col_name[length])
[ASC|DESC]
参数：
1、UNIQUE、FULLTEXT和SPATIAL为可选参数，分别表示唯一索引、全文索引和空间索引
2、INDEX和KEY为同义词，二者作用相同，用来指定创建索引
3、col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择
4、index_name为指定索引的名称，为可选参数，如果不指定则MySQL默认col_name为索引
5、length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度
6、ASC或DESC指定升序或者降序的索引值存储
B-tree索引：除了archive以外的存储引擎，都支持
使用B+树的数据结构实现来存储数据，能够加快数据的查询速度，从索引的root开始往下搜索
B-tree索引的数据是顺序存储的，所以适合范围查找
使用场景：
1、全值匹配的查询，例如：id=‘1001‘
2、匹配最左前缀的查询，例如现在把id和name建立一个联合索引，这时候查询id=‘1001‘可以使用到联合索引，因为id为这个索引最左字段，
但是如果通过name进行筛选，就无法用到联合索引
3、匹配列前缀查询，例如：id like ‘100%‘也可以用到联合索引
4、匹配范围值的查询，例如id < ‘1001‘ and id > ‘1010‘
5、精确匹配左前列并范围匹配另一列
6、值访问索引的查询(覆盖索引)
使用限制：
1、不使用索引最左列的查询，无法使用到联合索引
2、使用索引时不能跳过索引中的列
3、not in和<>无法使用索引
4、索引中有某个列使用了范围查找，则右边的所有列都无法使用索引
PS：不仅可以在where查询中使用，也可以使用在order by和group by中
Hash索引：
Memory存储引擎默认的索引，InnoDB也有Hash索引，这是InnoDB自动建立
Hash索引时基于Hash表实现的，对于Hash索引中所有列，存储引擎为每一列计算一个hash值，hash索引存储的就是hash码
使用限制：
通过hash索引找到对应的行，然后对行的数据进行读取，进行两次查找
无法用于排序
只有查询条件精确匹配Hash索引的所有列，才能使用Hash索引，不能是范围匹配和模糊匹配
可能产生hash冲突，不适合选择性很差的列，例如性别。
选择性：不重复的索引值和表的记录数的比值
比值越高索引的效率越好，因为选择性高的索引可以在查找时过滤掉更多的行，唯一索引的值是1，这是性能最好的
Innodb也有一个特殊的自适应哈希索引(adaptive hash index)
创建自定义哈希索引：
在B-Tree索引的基础上创建伪哈希索引，使用B-Tree进行查找，但是不是使用键本身而是hash值进行查找，只需要在where条件中手动指定
hash函数，记住不要使用SHA1()/MD5()
空间数据索引(R-Tree)：
MyISAM支持，用于存储地理数据GPS数据
全文索引：
它是查找文本中的关键词，而不是直接比较索引中的值，全文索引和其它索引的匹配方式完全不同，不适用与where条件操作
索引的优点：
大大减少存储引擎要扫描的数据量
索引可以帮助我们进行排序以避免使用临时表，B-tree索引不需要进行数据排序
索引可以把随机I/O变成顺序I/O
索引带来的消耗：
增加写操作的成本，在对数据数据进行修改的时候，需要更新索引，所以索引越多，写入的越慢。所以，InnoDB有一层插入缓存，将多次写入
合并为一次写入
增加查询优化器的选择时间，同一个查询如果有很多索引可以选择，会导致查询优化器选择的时间
PS：过多的索引对写、读的效率都是有影响的
索引优化：
1、索引列不能使用表达式或函数
例如：
where id +1 = 5;
SELECT * FROM temp WHERE TO_DAYS(date1)-TO_DAYS(current_date) > 30
优化：
where date1 > date_add(current_date,interval 30 day)
2、前缀索引和索引列的选择性
create index index_name on table(col_name(n))
索引很长的字符列（很长的varchar、text、blob），必须使用前缀索引（MyISAM 727字节，Innodb 1000字节），因为MySQL不允许索引这些
列的完整长度，使用前面所说的伪哈希索引是不行的，通常是索引开始的部分字符，可以节省索引空间，提高索引效率，但是会降低索引的选择性
所以需要在前缀索引的大小和选择性之间找到平衡
3、联合索引：很多列都建立索引不如建立联合索引
如何选择索引列的顺序：
未完待续。。。
原文：https://www.cnblogs.com/huigelaile/p/11127813.html