mysql索引
索引：
是排序的快速查找的特殊数据结构，定义作为查找条件的字段上，又称为键key，索引通过存储引擎实现
类似于书籍的目录作用
优/缺点：
- 索引可以降低服务需要扫描的数据量，减少了IO次数
- 索引可以帮助服务器避免排序和使用临时表
- 索引可以帮助将随机I/O转为顺序I/O
- 占用额外空间，影响插入速度。当一个新索引加入，整个都要更新
索引类型：
- 唯一索引
- 全文索引
- 聚簇索引、非聚簇索引 ， 判断依据是数据和索引是否存放在一起
- 主键索引 ， 主键创建时默认自动创建索引
- 二级(辅助)索引 ， 辅助索引的叶子节点不存放数据，只存放其本身和主键索引的关系，例：id是主键索引，name是辅助索引，name对应那个id
使用辅助索引，回先从辅助索引，找到对应的主键索引，在拿这个结果去主键索引直接找数据
- 稠密索引、稀疏索引， 判断依据是，是否索引每一个数据项
- 简单索引、组合(复合)索引 ， 复合索引时，只能根据索引一字段做条件，不能直接使用索引二，或者and连接
- 左前缀索引 ， 只取前面的字符做索引，从左往右
- 覆盖索引 ，数据能从索引中取出，不必定位行，再读取。也就是被查询的数据能直接被索引覆盖，性能高
索引的结构：
hash索引
基于哈希表实现，只有精确匹配索引中的所有列的查询才有效，索引自身只存储索引列对应的哈希值和数据指针，索引结构紧凑，查询性能好
Memory存储引擎支持显式hash索引，InnoDB和MyISAM存储引擎不支持
适用场景：
只支持等值比较查询，包括=, <=>, IN()
不适合使用hash索引的场景
- 不适用于顺序查询：索引存储顺序的不是值的顺序
- 不支持模糊匹配
- 不支持范围查询
- 不支持部分索引列匹配查找：如A，B列索引，只查询A列索引无效
R树（空间数据索引）
R tree索引是根据地理位置建立
MyISAM支持地理空间索引，可使用任意维度组合查询，使用特有的函数访问，常用于做地理数据存储，使用不多
InnoDB从MySQL5.7之后也开始支持
二叉树
红黑树
平衡类
B tree
平衡类。数据和索引存储在一起
B+ tree
平衡类。根、分枝只放索引，叶子才放数据。所有的IO时间都是一样，一般2次磁盘IU就获取数据
按顺序存储，叶子的数据和索引是同一顺序，每一个叶子节点到根结点的距离是相同的；左前缀索引，适合查询范围类的数据
可以使用B+ Tree索引的查询场景：
| 全值匹配 | 精确所有索引列，如：姓wang，名xiaochun，年龄30 |
| 匹配最左前缀 | 即只使用索引的第一列，如：姓wang |
| 匹配列前缀 | 只匹配一列值开头部分，如：姓以w开头的 |
| 匹配范围值 | 如：姓ma和姓wang之间 |
| 精确匹配某一列并范围匹配另一列 | 如：姓wang,名以x开头的只访问索引的查询 |
说明：
- 一般的索引，只能以开头的索引字符为搜索条件或判断条件，如： “anqi” 为索引字段，做条件只能是where name="a%" 或者name="anqi"。'%q%'和'%i'是无法使用索引的
B+ Tree索引的限制：
- 如果不从最左列开始，则无法使用索引，如，姓名为：xiaohong，查找名为g结尾时就不能使用索引
- 不能跳过索引中的列：如：用两个索引，查找姓wang，年龄30的，只能使用索引第一列，也就是只能使用姓的索引，年龄的索引无效
B+ Tree索引优化的注意点：
- 索引列的顺序和查询语句的写法应相匹配，才能更好的利用索引
- 为优化性能，可能需要针对相同的列但顺序不同创建不同的索引来满足不同类型的查询需求
全文索引(FULLTEXT)
在文本中查找关键词，而不是直接比较索引中的值，类似搜索引擎
InnoDB从MySQL 5.6之后也开始支持
索引的冗余和重复索引：
冗余索引：
已有(a,b)索引，再次创建a索引，就是冗余索引，因为根据左前缀索引，(a,b)包含了a，而创建b索引就不是冗余，因为不满足左前缀规则
重复索引：
已经有索引，再次建立索引，表示一个列或者顺序相同的几个列上建立的多个索引
索引优化：
- 独立地使用列： 尽量避免其参与运算，独立的列指索引列不能是表达式的一部分，也不能是函数的参数，在where条件中，始终将索引列单独放在比较符号的一侧，尽量不要在列上进行运算（函数操作和表达式操作）
- 左前缀索引： 构建指定索引字段的左侧的字符数，要通过索引选择性（不重复的索引值和数据表的记录总数的比值）来评估，尽量使用短索引，如果可以，应该制定一个前缀长度
- 多列索引： AND操作时更适合使用多列索引，而非为每个列创建单独的索引
- 选择合适的索引列顺序：无排序和分组时，将选择性最高放左侧只要列中含有NULL值，就最好不要在此列设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
- 对于经常在where子句使用的列，最好设置索引
- 对于有多个列where或者order by子句，应该建立复合索引
- 对于like语句，以 % 或者 _ 开头的不会使用索引，以 % 结尾会使用索引
- 尽量不要使用not in和<>操作,虽然可能使用索引,但性能不高
- 不要使用RLIKE正则表达式会导致索引失效
- 查询时，能不要就不用，尽量写全字段名，比如:select id,name,age from students;
- 大部分情况连接效率远大于子查询
- 在有大量记录的表分页时使用limit
- 对于经常使用的查询，可以开启查询缓存
- 多使用explain和profile分析查询语句
- 查看慢查询日志，找出执行时间长的sql语句优化
管理索引：
索引相关命令：
#创建索引：
create [unique] index 索引名 on 表(字段名[(字符长度)])
alter table 表 add index 索引名(字段[(字符长度)])
#删除索引：
drop index 索引名 on 表;
alter table 表 drop index 索引(字段[(字符长度)]);
#查看索引：
show indexes from 表;
#优化表空间：
optimize table 表;
#查看索引使用情况：
set global userstat=1; 开启统计索引功能
show index_statistics;
例1：
SET GLOBAL userstat=1;
alter table t1 add index test(id);
select * from t1 where id=2;
show index_statistics;
例2：
alter table t1 add index ts(name);
select * from t1 where name='qq';
show index_statistics;
EXPLAIN 工具
可以通过EXPLAIN来分析索引的有效性,获取查询执行计划信息，用来查看查询优化器如何执行查询
参考资料： https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
语法：
explain select 语句 #显示select语句的查询相关信息
explain extended select 语句 #扩展显示
EXPLAIN输出信息说明：
使用时，最主要查看type、possible_keys、key
| id | 执行编号，标识select所属的行。如果在语句中没子查询或关联查询，只有唯一的select，每行都将显示1。否则，内层的select语句一般会顺序编号，对应于其在原始语句中的位置 |
| select_type | 查询语句的类型 |
| table | 查询的表 |
| type | 关联类型、访问类型，即MySQL决定的如何去查询表中的行的方式 |
| possible_keys | 查询可能会用到的索引 |
| key | 显示mysql决定采用哪个索引来优化查询 |
| key_len | 显示mysql在索引里使用的字节数 |
| ref | 根据索引返回表中匹配某单个值的所有行 |
| rows | 为了找到所需的行而需要读取的行数，估算值，不精确。通过把所有rows列值相乘，可粗略估算整个查询会检查的行数 |
| Extra | 额外信息 |
select_type字段详细解读：
简单查询：
- SIMPLE
复杂查询：
- PRIMARY（最外面的SELECT）
- DERIVED（用于FROM中的子查询）
- UNION（UNION语句的第一个之后的SELECT语句）
- UNION RESUlT（匿名临时表）
- SUBQUERY（简单子查询）
type字段详细解读：
type显示的是访问类型，是较为重要的一个指标，全部的结果值从好到坏依次是：
NULL> system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，要保证查询至少达到range级别，最好能达到ref
常见的10种是：
NULL > system > const > eq_ref > ref > ref_or_null > index_merge > range > index>ALL
| 类型 | 含义 |
|---|---|
| All | 最坏的情况,全表扫描 |
| index | 和全表扫描一样。只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。如果在Extra列看到Using index，说明正在使用覆盖索引，只扫描索引的数据，它比按索引次序全表扫描的开销要小很多 |
| range | 范围扫描，一个有限制的索引扫描。key 列显示使用了哪个索引。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可以使用 range |
| ref | 一种索引访问，它返回所有匹配某个单个值的行。此类索引访问只有当使用非唯一性索引或唯一性索引非唯一性前缀时才会发生。这个类型跟eq_ref不同的是，它用在关联操作只使用了索引的最左前缀，或者索引不是UNIQUE和PRIMARY KEY。ref可以用于使用=或<=>操作符的带索引的列 |
| eq_ref | 最多只返回一条符合条件的记录。使用唯一性索引或主键查找时会发生 （高效） |
| const | 当确定最多只会有一行匹配的时候，MySQL优化器会在查询前读取它而且只读取一次，因此非常快。当主键放入where子句时，mysql把这个查询转为一个常量（高效） |
| system | 这是const连接类型的一种特例，表仅有一行满足条件 |
| Null | 意味着mysql能在优化阶段分解查询语句，在执行阶段甚至用不到访问表或索引（高效） |
Extra字段详细解读：
| Using filesort | 将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的 |
| Using temporary | 使用临时表保存中间结果，在对结果排序时使用临时表，常见于排序order by和分组查询group by |
| Using index condition | 查询的列不完全被索引覆盖，where条件中是一个前导的范围 |
| Using index | 使用覆盖索引，效率很好；如果同时出现using where，表明索引被用来执行索引键值的查找，没有出现，则表明索引直接用来读取数据 |
| Using where | 使用了where条件 |
| Using join buffer | 使用了连接缓存 |
| impossible where | where子句的值总是false，不能用来获取任何元组 |
| distinct | 一旦mysql找到了与行相联合匹配的行，就不在搜索了 |
| select tables optimized away | select操作已经优化到不能再优化了（不用遍历表或索引就能直接返回数据） |
使用explain工具查询索引效率：
例1： 精确匹配索引
explain extended select * from t1 where id=1\G;
例2： 左前缀匹配索引
explain select name from t1 where name like 'q%'\G;
例3： 带入子查询
explain select * from t1 where id = (select max(id) from t1)\G;
