第二十一天：mysql--INDEX 索引

标签：INDEX name index -- 使用 查询 索引 mysql

一、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 #

 

标签：INDEX,name,index,--,使用,查询,索引,mysql
From： https://www.cnblogs.com/dujy/p/18014145