MySQL数据库之索引
一、什么是索引
索引是一种用于快速查询到匹配条件的数据的数据结构，是用来加快查询的技术。索引对良好的数据库性能来说，是一个非常重要的指标。当表中的数据量越来越大的时，其索引就越来越重要。
- 基本法则
索引应该构建在被用作《查询条件》的字段上
- 索引类型
1） B+ Tree索引
BTree树的特性：多路平衡树
树中每个节点最多包含m个子节点
每一个叶子节点到根节点的距离是相同的
B+ Tree树的特点：
有n棵子树的B+Tree最多含有n个key，而BTree最多含有n-1个key。
B+Tree的叶节点保存所有的key信息，依key大小顺序排列。
所有的非叶子节点都可以看作是key的索引部分，节点中只含有其子节点的最大（或最小）key。
适合B+Tree索引的查询类型：（全键值、最左前缀、匹配范围）
全值匹配：精确查找某个键的值，如name=“Jinjiao king”
匹配最左前缀： 只精确匹配起头部分 "Jin%"
匹配范围值： 从某个值到另一个值
精确匹配某列并范围匹配另一列
只访问索引的查询
不适合B+Tree索引的场景：
如果不从最左列开始，索引无效，如比如一个索引构建在（name、age）中，如果查询语句中，查询age>20的，这个索引就无效咯
不能跳过索引中的列 （StuID、Name、Age）
如果查询中某个列是范围查询，那边右侧的列都无法再使用索引优化查询
2）hash索引： 基于哈希表
不适用于顺序查询或模糊匹配的查询，只适合精确匹配的查询，只有memory存储引擎支持显示hash索引，其他的都不支持
3）空间索引（R-Tree）
只有MyISAM表支持空间索引
4）全文索引（FULLTEXT）
在文本中查找关键词 （开源搜索引擎：Lucene）
- 索引的优点
索引可以降低服务器需要扫描的数据量，减少IO次数
索引可以帮助服务器避免排序和使用临时表
索引可以帮助将随机I/O转为顺序I/O
- 高性能的索引策略
1）索引的字段，不要做算术运算，如果该列使用算术运算，那么该列就无法使用索引咯
2）左前缀索引；索引构建在字段的左侧的多少个字符，要通过索引选择性来评估
3） 多列索引 （and）
4）选择合适的索引列次序，将选择性最高的放在左侧，范围匹配的放右侧
5) 为用来搜索、分类、分组的数据列构建索引；意思就是说，适合索引的数据列是哪些where子句出现的、order by、group by子句中出现的列
二）通过explain分析索引的有效性
使用方式：
EXPLAIN SELECT 获取查询执行计划信息
输出说明：
id： 当前查询语句中，每个select语句的编号
select_type：
简单查询： SIMPLE
简单子查询： SUBQUERY
用于from中的子查询： DERIVED
UNION语句的第一个之后的select语句： UNION
table： 查询对应的表
type： 即MySQL决定如何去查询表中行的方式
ALL:全表扫描
index：根据索引的次序进行全表扫描
range：范围扫描，有范围限制的根据索引来扫描。扫描位置始于索引中的某一点，结束于另一点
ref：基于等值的方式，根据索引返回表中匹配某单个值得所有行
eq_ref： 仅返回一个行
const、system： 直接返回单个行（最佳的）
possible_keys：查询可能会用到的索引
key： 查询中使用到的索引
key_len： 在索引中使用的字节数
ref： 在利用key字段所表示的索引完成查询时，所用的列或某常量值
rows： MySQL估计为找到所有的目标行而需要读取的行数（而不是最终的行数）
extra： 额外信息
Using index： 使用覆盖索引
Using where： MySQL服务器将在存储引擎层检索后，在进行一次过滤
Using temporary： MySQL对结果排序时会使用临时表
Using filesort：对结果使用一个外部索引排序，基于文件排序 （性能最差）