第二十一天：mysql--INDEX 索引
一、INDEX 索引介绍
索引：是排序的快速查找的特殊数据结构，定义作为查找条件的字段上，又称为键key，索引通过存储引擎实现
优点：
索引可以降低服务需要扫描的数据量，减少了IO次数
索引可以帮助服务器避免排序和使用临时表
索引可以帮助将随机I/O转为顺序 I/O
缺点：
占用额外空间，影响插入速度
索引类型：
B+ TREE、HASH、R TREE、FULL TEXT
聚簇（集）索引、非聚簇索引：数据和索引是否存储在一起
主键索引、二级（辅助）索引
稠密索引、稀疏索引：是否索引了每一个数据项
简单索引、组合索引: 是否是多个字段的索引
左前缀索引：取前面的字符做索引
覆盖索引：从索引中即可取出要查询的数据，性能高
二、索引结构
参考链接 : https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
二叉树
参考链接: https://www.cs.usfca.edu/~galles/visualization/BST.html
红黑树
参考链接:https://www.cs.usfca.edu/~galles/visualization/RedBlack.html
B-Tree 索引
参考链接: https://www.cs.usfca.edu/~galles/visualization/BTree.html
B+Tree索引
参考链接: https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html
B+Tree索引：按顺序存储，每一个叶子节点到根结点的距离是相同的；左前缀索引，适合查询范围类的数据
可以使用B+Tree索引的查询类型：(假设前提: 姓,名,年龄三个字段建立了一个复合索引)
全值匹配：精确所有索引列，如：姓wang，名xiaochun，年龄30
匹配最左前缀：即只使用索引的第一列，如：姓wang
匹配列前缀：只匹配一列值开头部分，如：姓以w开头的记录
匹配范围值：如：姓ma和姓wang之间
精确匹配某一列并范围匹配另一列：如：姓wang,名以x开头的记录
只访问索引的查询
B+Tree索引的限制：
如不从最左列开始，则无法使用索引，如：查找名为xiaochun，或姓为g结尾
不能跳过索引中的列：如：查找姓wang，年龄30的，只能使用索引第一列
Hash索引
Hash索引：基于哈希表实现，只有精确匹配索引中的所有列的查询才有效，索引自身只存储索引列对应的哈希值和数据指针，索引结构紧凑，查询性能好
地理空间数据索引R-Tree（ Geospatial indexing ）
全文索引(FULLTEXT)
聚簇和非聚簇索引，主键和二级索引
冗余和重复索引：
三、索引优化
独立地使用列：尽量避免其参与运算，独立的列指索引列不能是表达式的一部分，也不能是函数的参数，在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 index_name ON tbl_name (index_col_name[(length)],...); ALTER TABLE tbl_name ADD INDEX index_name(index_col_name[(length)]); help CREATE INDEX;
删除索引：
DROP INDEX index_name ON tbl_name; ALTER TABLE tbl_name DROP INDEX index_name(index_col_name);
查看索引：
SHOW INDEX FROM [db_name.]tbl_name;
优化表空间：
OPTIMIZE TABLE tb_name;
查看索引的使用
SET GLOBAL userstat=1; #MySQL无此变量 SHOW INDEX_STATISTICS;
五、EXPLAIN 工具
可以通过EXPLAIN来分析索引的有效性,获取查询执行计划信息，用来查看查询优化器如何执行查询
参考资料： https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
语法：
EXPLAIN SELECT clause
EXPLAIN输出信息说明：
type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：NULL> system >const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery >range > index > ALL ，一般来说，得保证查询至少达到range级别，最好能达到ref
六、使用 profile 工具
#打开后，会显示语句执行详细的过程 set profiling = ON; #查看语句,注意结果中的query_id值 show profiles ; #显示语句的详细执行步骤和时长 Show profile for query # #显示cpu使用情况 Show profile cpu for query #
