索引是帮助MySQL高效获取数据的数据结构

优缺点：

索引结构

索引实现于存储引擎层，不同存储引擎有不同结构：

B+树索引：大部分支持
Hash索引：底层为哈希表，只有精确匹配索引列的查询才有效
R-tree（空间索引）：MyISAM的索引类型，用于地理空间数据类型
Full-text（全文索引）：通过建立倒排索引快速匹配文档

B+树优化

MySQL中对B+树进行了优化，增加了一个指向相邻叶子节点的链表指针，形成了带有顺序指针的B+树，提高了访问区间的性能

索引分类

InnoDB中，根据索引存储形式，又可以分为下面两种：

聚集索引的选取规则：

若存在主键，主键索引就是聚集索引
如果不存在主键，则使用第一个唯一索引作为聚集索引
如果不存在主键或没有合适的唯一索引，则引擎会自动生成一个rowid作为隐藏的聚集索引

语法

创建索引：CREATE [UNIQUE/FULLTEXT] INDEX 索引名 ON 表名 (字段名,...);
- 不加参数是普通索引
- 字段名为索引关联的字段，若只有一列字段称为单列索引，多列字段称为联合索引
查看索引：SHOW INDEX FROM 表名;
删除索引：DROP INDEX 索引名 ON 表名

性能分析

SQL执行频次

通过SHOW [SESSION|GLOBAL] STATUS;命令可以提供服务器状态信息

如下指令可以查看增删查改等命令的访问频次：

SHOW GLOBAL STATUS LIKE 'Com_______'

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time, 默认十秒）的所有SQL语句的日志

MySQL的慢查询日志默认没有开启，需要在配置文件(/etc/my.cnf)中配置：

show_query_log = 1
long_query_time = 2 // 时间超过的参数

生成的日志存放放在/var/lib/mysql，名为localhost-slow.log

show_profile

可以帮助我们了解时间的耗费情况

通过have_profiling参数可以查看是否支持profile操作：SELECT @@have_profiling;
通过SET语句开启：SET [SESSION|GLOBAL] profiling = 1;
查看SQL语句耗时情况：SHOW profiles;
指定看第几个操作的各个阶段的耗时：SHOW profiles FOR query xx;

explain

执行计划，通过该指令可以获取MySQL是如何执行某SELECT语句的信息，包括再执行过程中表如何连接以及连接顺序等

直接在SELECT前面添加DESC或EXPLAIN即可，查询出的各字段含义如下：

id：select查询的序列号，表示查询中执行select子句或者操作表的顺序（id相同，顺序由上到下，id不同值越大越先执行）
select_type：SELECT的类型，常见取值有：
- SIMPLE：简单表，不使用表连接或子查询
- PRIMARY：主查询，即外层的查询
- UNION：UNION中第二个或者后面的查询语句
- SUBQUERY：SELECT/WHERE之后包含了子查询
type：表示连接类型，性能由好到差为：NULL、system、const、eq_ref、ref、range、index、all
possible_key：可能用在这张表上的索引，一个或多个
key：实际用到的索引，若为NULL表示没用到
key_len：用到的索引字节数，为索引字段最大可能长度，并非实际
rows：MySQL认为必须执行查询的行数，在innoDB表中，是一个估计值
filtered：返回结果的行数占需读取行数的百分比
extra：额外信息

索引使用

最左前缀法则

对于联合索引，要遵循最左前缀法则：查询从索引的最左列开始，且不跳过索引中的列

如果跳过某一列，这一列后的字段索引会失效

且最左前缀法则要求的是最左前缀一定存在，和顺序无关

如，已经建立了一个以profession, age和status为列的联合索引

以下查询可以走索引：

SELECT * FROM user WHERE profession = '软件工程' and age = '18';
SELECT * FROM user WHERE profession = '软件工程';
SELECT * FROM user WHERE age = '18' and status = '0' and profession = '软件工程';

而以下查询是全表扫描：

SELECT * FROM user WHERE status = '0';
SELECT * FROM user WHERE age = '18' and status = '0';

而以下查询部分失效：

SELECT * FROM user WHERE profession = '软件工程' and status = '0';

这里profession走了索引，后面的status失效

索引失效

范围查询

联合索引中，若出现范围查询（>, <），索引该位置右侧的列会失效

e.g.

SELECT * FROM user WHERE profession = '软件工程' and age > '18' and status = '0';

最后的status不走索引

索引列运算

不要在索引列上进行运算操作，否则索引失效

SELECT * FROM user WHERE sbstring(phone, 10, 2)= '15';

模糊查询

若仅是尾部模糊匹配，索引不会失效，如果是从头部模糊匹配，索引会失效

e.g.以下走索引：

SELECT * FROM user WHERE profession '软件%'

以下不走索引：

SELECT * FROM user WHERE profession '%工程'

or连接的条件

用or连接的条件，如果or前的条件中的列有索引，但后面的没有，则涉及的索引都不会被用到，即or两侧的条件必须都要有索引

e.g. 以下不走索引：

SELECT * FROM user WHERE id = 10 or age = 21;

因为这里age不在索引内，需要手动添加

数据分布影响

如果MySQL评估使用索引比全表扫描慢，则不用索引

SQL提示

优化数据库的手段，在SQL语句中添加一些人为提示来达到优化操作的目的

USE INDEX：（建议MySQL）使用某个索引

SELECT * FROM user USE INDEX(idx_user_pro) WHERE profession = '软工';

IGNORE INDEX：不用某个索引

SELECT * FROM user IGNORE INDEX(idx_user_pro) WHERE profession = '软工'

FORCE INDEX：强制使用某个索引

SELECT * FROM user FORCE INDEX(idx_user_pro) WHERE profession = '软工'

覆盖索引

当我们执行SELECT操作时，对其EXPLAIN，注意EXTRA中的内容：

若为using index condition，表明查找使用了索引，但是需要回表查询数据
若为using where; using index，表明查找使用了索引，但是需要的数据在索引列中都能找到，不需要回表查询

后者性能比前者高，因此在实际操作中对于查询操作多的数据要建立合适的索引

前缀索引

字段类型是字符串时，有时索引的字符串很长，索引很大，会影响效率，此时可以取字符串的一部分前缀，可以节约空间，提升效率

语法：CREATE INDEX idx_xxx ON table_name(column(n));

n为存储前n个字符

前缀长度：根据索引选择性来决定（不重复的索引值和数据表的记录总数的比值），选择性越高查询效率越高。唯一索引的选择性是1，是最好的索引选择性

前缀索引必然回表，因为它要根据前缀在辅助索引中找到id，用id回到聚集索引获取完整内容

单列/联合索引

单列索引：索引包含单个列
联合索引：索引包含多个列

业务场景下，若存在多个查询条件，考虑针对查询字段建立索引时，建议建立联合索引

B+树中，联合索引的每一列值组合作为节点使用，按照顺序排序

索引设计原则

针对数据量较大，查询比较频繁的表建立索引：一般对于百万量级的表建立，没有查询需求的表不需要建立索引
要针对常作为查询条件、排序、分组操作的字段建立索引
尽量选择区分度高的列作为索引，尽量用唯一索引：区分度高效率高
如果是字符串类型的字段，字段长度较长，可以建立前缀索引
尽量使用联合索引，减少单列索引：联合索引可以覆盖索引，避免回表，提升效率
控制数量：索引越多，维护的代价越带，影响增删改的效率
若索引列不能存储NULL值，建表时使用NOT NULL约束：优化器知道每列是否包含NULL值时，才能更好的确定哪个索引最有效地用于查询

红魔咖啡馆

【MySQL】索引