mysql_组件之索引

1. 索引

索引：是特殊数据结构，定义在查找时作为查找条件的字段，在 MySQL 又称为键 key，索引通过存储引擎实现。

优点：

• 索引可以降低服务需要扫描的数据量，减少了 IO 次数。
• 索引可以帮助服务器避免排序和使用临时表。
• 索引可以帮助将随机 I/O 转为顺序 I/O。

缺点：

• 占用额外空间，影响插入速度索引。

2. 索引类型：

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

二叉树
红黑树
B-TREE索引
B+TREE索引
B+TREE索引
此处有图

2.1. B+Tree索引

B+Tree索引：顺序存储，每一个叶子节点到根结点的距离是相同的；左前缀索引，适合查询范围类的数据。根节点、分支节点放索引，叶子节点放数据。

2.1.1. 可以使用B+Tree索引的查询类型

• 全值匹配：精确所有索引列。如：姓wang，名xiaochun，年龄30。
• 匹配最左前缀：即只使用索引的第一列。如：姓wang。
• 匹配列前缀：只匹配一列值开头部分。如：姓以w开头的。
• 匹配范围值：如：姓ma和姓wang之间。
• 精确匹配某一列并范围匹配另一列：如：姓wang,名以x开头的。
• 只访问索引的查询

2.1.2. B+Tree索引的限制

• 如不从最左列开始，则无法使用索引。
  例如：查找名为xiaochun，或姓为g结尾。
• 不能跳过索引中的列。
  例如：查找姓wang，年龄30的，只能使用索引第一列。
• 如果查询中某个列是为范围查询，那么其右侧的列都无法再使用索引。
  例如：姓　wang，名　x%，年龄30，只能利用姓和名上面的索引。

2.1.3. 特别提示

• 索引列的顺序和查询语句的写法应相匹配，才能更好的利用索引。
• 为优化性能，可能需要针对相同的列但顺序不同创建不同的索引来满足不同类型的查询需求。

2.2. Hash索引

Hash索引：基于哈希表实现，只有精确匹配索引中的所有列的查询才有效，索引自身只存储索引列对应的哈希值和数据指针，索引结构紧凑，查询性能好。

Memory 存储引擎支持显式 hash 索引，InnoDB 和 MyISAM 存储引擎不支持。

适用场景：只支持等值比较查询，包括=, <=>, IN()

不适合使用hash索引的场景

• 不适用于顺序查询：索引存储顺序的不是值的顺序。
• 不支持模糊匹配
• 不支持范围查询
• 不支持部分索引列匹配查找：如A，B列索引，只查询A列索引无效。

2.3. 空间数据索引 R-Tree（ Geospatial indexing ）

MyISAM 支持地理空间索引，可以使用任意维度组合查询，使用特有的函数访问，常用于做地理数据存储，使用不多。
InnoDB 从 MySQL5.7之后也开始支持。

2.4. 全文索引(FULLTEXT)

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

聚簇和非聚簇索引聚簇和非聚簇索引，主键和二级索引
此处有图

2.5. 聚集索引

数据与索引放在同一文件。

2.6. 主键

索引与数据在一起，主键有序，数据也是有序的存放。

2.7. 二级索引

索引+主键，查两次。

2.8. 冗余和重复索引

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

3. 优化

3.1. 索引优化策略

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

3.2. 索引优化建议

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

3.3. SQL 语句性能优化

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

4. 管理索引

4.1. 创建索引

CREATE INDEX index_name ON tbl_name (index_col_name[(length)],...);
help CREATE INDEX;

## 创建索引
create index index_name_age on testlog(name(10));

## 复合索引
create index index_name_age on testlog(name,age);

## 唯一键索引
create unique index uni_name on students(name);

4.2. 删除索引

DROP INDEX index_name ON tbl_name;

4.3. 查看索引

SHOW INDEXES FROM [db_name.]tbl_name;

## 查看索引
show indexs from testlog;
show indexs from testlog;\G

4.4. 优化表空间

OPTIMIZE TABLE tb_name;

4.5. 查看索引的使用

## 前提
[mysqld]
userstat = 1
## 或者
SET GLOBAL userstat=1;


SHOW INDEX_STATISTICS;

5. EXPLAIN

通过EXPLAIN来分析索引的有效性。

5.1. 语法

EXPLAIN SELECT clause

获取查询执行计划信息，用来查看查询优化器如何执行查询。

5.2. 输出信息说明

参考：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

5.2.1. id

当前查询语句中，每个 SELECT 语句的编号。

复杂类型的查询有三种：

• 简单子查询
• 用于 FROM 中的子查询
• 联合查询：UNION

注意：UNION 查询的分析结果会出现一个额外匿名临时表。

5.2.2. select_type

select_type	描述
SIMPLE	简单查询
SUBQUERY	简单子查询
PRIMARY	最外面的 SELECT
DERIVED	用于 FROM 中的子查询
UNION	UNION 语句的第一个之后的 SELECT 语句
UNION RESULT	匿名临时表

5.2.3. table

SELECT 语句关联到的表。

5.2.4. type

关联类型或访问类型，即 MySQL 决定的如何去查询表中的行的方式，以下顺序，性能从低到高。

type	描述
ALL	全表扫描
index	根据索引的次序进行全表扫描；如果在 Extra 列出现 “ Using index ” 表示了使用覆盖索引，而非全表扫描。
range	有范围限制的根据索引实现范围扫描；扫描位置始于索引中的某一点，结束于另一点。
ref	根据索引返回表中匹配某单个值的所有行。
eq_ref	仅返回一个行，但与需要额外与某个参考值做比较。
const，system	直接返回单个行。

5.2.5. possible_keys

查询可能会用到的索引。

5.2.6. key

查询中使用到的索引。

5.2.7. key_len

在索引使用的字节数。

5.2.8. ref

在利用 key 字段所表示的索引完成查询时所用的列或某常量值。

5.2.9. rows

MySQL 估计为找所有的目标行而需要读取的行数。

5.2.10. Extra

额外信息

Extra	描述
Using index	MySQL 将会使用覆盖索引，以避免访问表。
Using where	MySQL 服务器将在存储引擎检索后，再进行一次过滤。
Using temporary	MySQL 对结果排序时会使用临时表。
Using filesort	对结果使用一个外部索引排序。