初探MySQL之索引INDEX

INDEX 索引

1.索引介绍

索引：是排序的快速查找的特殊数据结构，定义作为查找条件的字段上，又称为key，索引通过存储引擎实现
优点：
	索引可以降低服务需要扫描的数据量，减少了IO次数
	索引可以帮助服务器避免排序和使用临时表
	索引可以帮助将随机I/O转为顺序I/O
缺点：
	占用额外空间，影响插入速度

1.1 索引类型

• B+ TREE、HASH、R TREE、FULLTEXT
• 聚簇（集）索引、非聚簇索引：数据和索引是否存储在一起
• 主键索引、二级（辅助）索引
• 稠密索引、稀疏索引：是否索引了每一个数据项
• 简单索引、组合索引
• 左前缀索引：取前面的字符做索引
• 覆盖索引：从索引中即可取出要查询的数据，性能高

1.2 索引分类

• 普通索引index :加速查找
  通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引
• 唯一索引
  • 主键索引：primary key ：加速查找+约束（不为空且唯一）
  • 唯一索引：unique：加速查找+约束 （唯一）
• 联合索引
  • primary key(id,name):联合主键索引
  • unique(id,name):联合唯一索引
  • index(id,name):联合普通索引
    通过ALTER TABLE table_name ADD INDEX index_name(column1,column2, column3);创建组合索引
• 全文索引fulltext :在文本中查找关键词，而不是直接比较索引中的值，类似搜索引擎，用于搜索很长一篇文章的时候，效果最好。

1.3 索引结构

1.3.1 二叉树

• 树叶节点存储数据
• 根节点，树枝节点不存储数据，只存储索引

1.3.2 B-Tree 索引

• B-TREE 每个节点都是一个二元数组: [key, data]，所有节点都可以存储数据。key为索引key,data为除key之外的数据
• 首先从根节点进行二分查找，如果找到则返回对应节点的data，否则对相应区间的指针指向的节点递归进行查找，直到找到节点或未找到节点返回null指针。

1.3.3 B+Tree

• B+Tree索引：按顺序存储，每一个叶子节点到根结点的距离是相同的；左前缀索引，适合查询范围类的数据
• 可以使用B+Tree索引的查询类型：
  全值匹配：精确所有索引列，如：姓wang，名xiaochun，年龄30
  匹配最左前缀：即只使用索引的第一列，如：姓wang
  匹配列前缀：只匹配一列值开头部分，如：姓以w开头的
  匹配范围值：如：姓ma和姓wang之间
  精确匹配某一列并范围匹配另一列：如：姓wang,名以x开头的
  只访问索引的查询
• B+Tree索引的限制：
  如不从最左列开始，则无法使用索引，如：查找名为xiaochun，或姓为g结尾
  不能跳过索引中的列：如：查找姓wang，年龄30的，只能使用索引第一列
  特别提示：
  索引列的顺序和查询语句的写法应相匹配，才能更好的利用索引
  为优化性能，可能需要针对相同的列但顺序不同创建不同的索引来满足不同类型的查询需求

1.3.4 全文索引(FULLTEXT)

在文本中查找关键词，而不是直接比较索引中的值，类似搜索引擎
InnoDB从MySQL 5.6之后也开始支持

1.3.5 聚簇和非聚簇索引，主键和二级索引

1.3.6 冗余和重复索引

冗余索引：（A），（A，B）
重复索引：已经有索引，再次建立索引

1.4 索引优化

1.4.1 索引优化使用

• 独立地使用列：尽量避免其参与运算，独立的列指索引列不能是表达式的一部分，也不能是函数的参数，在where条件中，始终将索引列单独放在比较符号的一侧
• 左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择性来评估
• 索引选择性：不重复的索引值和数据表的记录总数的比值
• 多列索引：AND操作时更适合使用多列索引，而非为每个列创建单独的索引
• 选择合适的索引列顺序：无排序和分组时，将选择性最高放左侧

1.4.2 索引优化建议

• 只要列中含有NULL值，就最好不要在此列设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
• 尽量使用短索引，如果可以，应该制定一个前缀长度
• 对于经常在where子句使用的列，最好设置索引
• 对于有多个列where或者order by子句，应该建立复合索引
• 对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引
• 尽量不要在列上进行运算（函数操作和表达式操作）
• 尽量不要使用not in和<>操作

1.4.3 SQL语句性能优化

• 查询时，能不要就不用，尽量写全字段名
• 大部分情况连接效率远大于子查询
• 多表连接时，尽量小表驱动大表，即小表 join 大表
• 在有大量记录的表分页时使用limit
• 对于经常使用的查询，可以开启缓存
• 多使用explain和profile分析查询语句
• 查看慢查询日志，找出执行时间长的sql语句优化

1.5 管理索引

1.5.1 创建索引

CREATE [UNIQUE] INDEX index_name ON tbl_name (index_col_name[(length)],...);
ALTER TABLE tbl_name ADD INDEX index_name(index_col_name);
help CREATE INDEX;

1.5.2 删除索引

DROP INDEX index_name ON tbl_name;
ALTER TABLE tbl_name DROP INDEX index_name(index_col_name);

1.5.3 查看索引

SHOW INDEXES FROM [db_name.]tbl_name;

1.5.4 优化表空间

OPTIMIZE TABLE tb_name;

1.5.5 查看索引的使用

SET GLOBAL userstat=1;
SHOW INDEX_STATISTICS;

1.5.6 EXPLAIN 工具

功能：可以通过EXPLAIN来分析索引的有效性,获取查询执行计划信息，用来查看查询优化器如何执行查询
语法：EXPLAIN SELECT clause
参考资料： https://dev.mysql.com/doc/refman/5.7/en/explain-output.html