MySQL进阶之索引

索引

1.  简介

索引：是帮助MySQL高效获取数据的数据结构（有序的）。在数据之外，数据库系统嗨维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

2. 优缺点

① 优点：提高数据检索的效率，降低数据库的IO成本；通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。
② 缺点：索引列是需要占用磁盘空间的；索引大大提高了查询效率，但同时却也降低了更新表的速度。（即对表进行INSERT、UPDATE、DELETE时效率更低）

3. 索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包括以下几种

索引引擎支持的索引的结构
InnoDB   ：B+tree索引，Full-text（5.6版本之后）
MyISAM ：B+tree索引，R-tree索引，Full-text
Memory  ：B+tree索引，Hash索引

我们常见的索引，如果没有特别指明，都是只B+树结构组织的索引

索引结构

B-Tree（多路平衡查找树）

B+Tree（B+Tree是在B-Tree基础上的一种优化，这种索引结构所有的数据都会出现在叶子节点）

页：Flash存储器中一种区域划分的单元

Hash

1. 简介

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上

2. 特点

Hash索引只能用于对等比较，不支持范围查询；无法利用索引完成排序操作：查询效率高，通知只需要一次检索就可以了，效率通常高于B+tree索引

为什么InnoDB存储引擎选择使用B+tree索引结构

索引的分类

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

聚集索引选取规则： 
如果存在主键，主键索引就是聚集索引。
如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

回表查询：先根据二级索引获取主键值，后再根据二级索引获取其他数据值

索引语法

# 创建索引
CREATE [UNIQE | FULLTEXT] INDEX index_name ON table_name (index_col_name ……);

# 查看索引
SHOW INDEX FROM table_name;

# 删除索引
DROP INDEX index_name ON table_name;

SQL性能分析

1. SQL执行频率

MySQL客户端连接成功后，通过show [session | global] status 命令可以提供服务器状态信息。通过如下指令可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次

# 查看访问频次
SHOW GLOBAL STATUS LIKE 'Com_______';  # 七个下划线

2. 慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。该功能默认没有开启，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

# 开启MySQL慢日志查询开关
slow_query_log = 1

# 设置慢日志的时间为 n 秒，SQL语句执行时间超过2秒，就会视为慢日志，记录慢查询日志
long_query_time = n;

注意：是要在MySQL的配置文件里面输入以上代码！

3. profile详情

show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

通过have_profiling参数，能够看到当前MySQL是否支持profile操作

SELECT @@have_profiling;

# 默认profiling是关闭的，可以通过set语句在session/global级别开启profiling;
SET profiling = 1;

在打开profiling后，执行一系列的业务SQL的操作时，然后通过如下指令查看指令的执行耗时：

# 查看每一条SQL的耗时基本情况
SHOW PROFILES;

# 查看指定query_id的SQL语句各个接断的耗时情况
SHOW PROFILE FOR QUERY QUERY_ID;

# 查看指定query_id的SQL语句CPU的使用情况
SHOW PROFILE CPU for QUERY QUERY_ID;

4. explain执行计划

EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。

EXPLIAIN\DESC SELECT 字段列表 FROM 表名 WHERE 条件;

EXPLAIN执行计划各字段含义：

SQL使用规则 

最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效（后面的字段索引失效）

范围查询

联合索引中，出现范围查询（>,<），范围查询右侧的列索引失效，但是大于等于或者小于等于是不会造成右侧的列索引失效的

索引列运算

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

字符串不加引号

字符串类型数据字段进行查询时，不加引号，建立的索引将会失效

模糊查询

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