Mysql学习笔记 - 索引

2019-12-16

全文 ≈ 752字 | 预计阅读时长 2分钟

索引用于快速查找具有特定列值的行。没有索引，MySQL必须从第一行开始，然后通读整个表以找到相关的行。如果表中有相关列的索引，MySQL可以快速确定要在数据文件中间查找的位置，而不必查看所有数据。这比顺序读取每一行要快得多。
InnoDB和MyIsam只支持Btree，因此默认均是Btree，Memory和Heap支持Hash和Btree，如无明确声明，则默认索引均是Hash（包括主键）。

1.Mysql有哪些常用的索引

主键索引：数据列不允许重复，不允许为null，一个表只能有一个主键。

唯一索引：数据列不允许重复，允许为null，一个表允许多个列创建唯一索引。

普通索引：基本的索引类型，没有唯一性的限制，允许为null。

全文索引：全文索引是目前实现大数据搜索的关键技术。

2.Mysql索引的建立原则

Mysql的索引遵循最左原则，在创建多列索引时，要根据业务需求，where条件中使用最频繁的一列放在最左边。

index(a,b,c)
where a = v // 使用a
where a = v and b = v // 使用a,b
where a = v and b = v and c = v // 使用a,b,c

where b = v // 未使用索引
where a = v and c = v // 使用a
where b = v and c = v // 未使用索引

where a > v // 是否使用索引，取决于查询结果集，如果全表扫描速度比索引速度快，那么不使用索引。

where a like "v%" // 使用a
where a like "%v%" // 不适用索引

3.如何分析SQL语句执行性能

以下是两种分析SQL性能的常用方式，explain、show profiles/show profile。

explain + SQL语句，获取SQL分析数据

select_type：对应SQL语句的查询复杂度。
table：正在访问的表。
partitions：数据所在的分区。
type：表示是否用上索引，以及索引是如何使用的，此字段决定索引的性能。ALL<TYPE<RANGE<REF<CONST
possible_keys：查询条件存在的索引。
key：触发的索引。
key_len：索引字段的长度。
ref：索引访问，返回所有匹配某个单值的行。
rows：执行查询必须检查的行数，在InnoDB中此值不精确。
filtered：条件过滤出的行数的百分比。
extra：查询分析结果的额外信息，很重要 e.g. Using index、Using where …

show profiles获得当前会话中执行的SQL语句，字段为：Query_ID, Duration, Query，show profile all for query {Query_ID}

4.Mysql中索引建立常见问题

为经常需要排序、分组和联合操作的字段建立索引
为常作为查询条件的字段建立索引
索引列值保证唯一性
索引建立的数量不要过多
索引列不要使用函数或者表达式
行锁依赖索引的建立
普通索引的数据重复率过高会导致索引失效
最左原则