索引 Index
MySQL中高效获取数据的有序数据结构
以某种方式引用(指向)数据
MySQL 无索引的查询方式
全表扫描
| 优点 | 缺点 |
|---|---|
| 提高数据检索效率，降低数据库IO成本 | 占用空间 |
| 降低数据排序的成本，降低 CPU 的性能消耗 | 降低了 CRD 的效率 |
索引结构
MySQL 的索引实现在存储引擎层实现，不同存储引擎有不同的结构
| 索引结构 | 描述 | 支持情况 |
|---|---|---|
| B+ Tree | 最常见的索引类型，大部分引擎都支持 B+ 树索引 | All |
| Hash | 底层数据结构通过 Hash 表实现，只支持精确匹配索引列的查询才有效，不支持范围查询 | Memory |
| R-tree (空间索引) | MyISAM 引擎的特殊索引类型，主要用于地理空间数据类型，使用较少 | MyISAM |
| Full-text (全文索引) | 通过建立倒排索引，快速匹配文档 | InnoDB >= 5.6、MyISAM |
B+ Tree
二叉树的缺点
- 顺序插入时，会形成一个链表，查询性能大大降低
- 数据量大的情况下，层级深，检索速度慢
解决方法
红黑树(自平衡二叉树)
解决顺序插入问题
B-tree(多路平衡查找树)
B+Tree
- 所有的元素都会存在在叶子节点
- 叶子结点形成一个单向链表
在 MySQL 中索引数据结构对 B+Tree 进行了优化，通过增加一个指向相邻子节点的链表指针，形成了带有顺序指针的 B+Tree，提高了区间访问性能，即叶子结点之间形成双向循环链表
InnoDB 存储引擎选择 B+Tree 索引结构的原因
- 相对于二叉树，层级更少，搜索效率更高
- 对于 B-tree，无论是叶子节点还是非子夜节点都会保存数据，导致一页中存储的键值减少、指针减少，导致在保存大量数据时，只能增加树的高度，从而导致性能降低；而 B+Tree 数据存储在叶子节点，查询效率稳定而且叶子节点形成的双向链表容易实现范围查找
- 相对 Hash 索引，B+Tree 支持范围匹配与排序操作
索引类型
按数据结构
| 类型 | 含义 | 特点 | 关键字 |
|---|---|---|---|
| 主键索引 | 针对表中主键创建的索引 | 默认自动创建且唯一 | PRIMARY |
| 唯一索引 | 避免同一个表中某个数据列的值重复 | 可以有多个 | UNIQUE |
| 常规索引 | 快速定位特定数据 | 可以有多个 | |
| 全文索引 | 全文查找文本中的关键词，而不是比较索引中的值 | 可以有多个 | FULLTEXT |
以下 SQL 语句哪个执行效率高
# id 为主键， name 为索引字段 SELECT * FROM `user` WHERE id = 10; SELECT * FROM `user` WHERE name = 'Arm';主键查询效率最高
建立索引的字段查询后获取行数据需要回表查询(根据主键查询行数据)
按照存储形式
| 分类 | 含义 | 特点 |
|---|---|---|
| 聚集索引 Clustered Index | 将数据存储与索引存储到一起，索引结构的叶子结点保存了行数据 | 必须有，而且只能有一个 |
| 二级索引 | 将数据与索引分开存储，存储结构的叶子结点关联的事对应的主键 | 可以存储多个 |
聚集索引选取规则
- 如果存在主键，主键索引就是聚集索引
- 如果不存在主键，将使用第一个唯一索引作为聚集索引
- 如果表没有主键，或者没有合适的唯一索引，InnoDB会自动生成一个 rowid 作为隐藏的聚集索引
按字段个数
| 分类 | 特点 |
|---|---|
| 单列索引 | 单个字段 |
| 联合索引 | 多个字段 |
索引操作
-
创建
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_column_name,...); -
查看
SHOW INDEX FROM table_name; -
删除
DROP INDEX index_name ON table_name;
索引使用
SQL 性能分析
SQL 执行频率
SHOW GLOBAL STATUS LIKE 'Com_______';慢查询日志
- 慢查询日志记录了所有执行时间超过了指定参数(long_query_time,， 单位: 秒，默认 10 秒)的所有 SQL 语句
- MySQL 的慢查询日志默认没有开启
慢查询日志配置
# /etc/my.cnf # 开启 MySQL 慢日志查询 slow_query_log = 1 # 设置慢日志的时间为 n 秒 long_query_time = n慢查询日志文件
/var/lib/mysql/localhost-slow.logprofile 详情
查看是否支持
SELECT @@have_profiling;设置 profile
SET profiling = 1; // 1 - session 2 - global查看 profile
SHOW profiles; SHOW profile FOR QUERY query_id; SHOW profile CPU for QUERY query_id;explain 执行计划
通过
EXPLAIN或者DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序[EXPLAIN | DESC] SELECT 字段 FROM 表名 WHERE 条件;
列名 含义 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 当前表实际用到的索引 key_len 表示索引中使用的字节数，为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，越短越好 ref rows MySQL 判断的需要执行查询的行数，在 InnoDB 中，是一个估计值 filtered 表示返回结果的行数占需读取行数的百分比，值越大性能越好 extra 额外信息
1. 前缀索引
使用磨个字段中字符串的前几个字符建立索引
使用过前缀索引可以见效索引字段的大小，增加一个索引页中存储的索引值，从而有效提高索引的查询速度
同时，在一些大字符串的字段作为索引是，可以减小索引项的大小
局限性
order by无法使用前缀索引- 前缀索引无法用作覆盖索引
2. 覆盖索引
SQL 中 query 的所有字段，在 B+Tree 的叶子上都可以找到，从而使从二级索引中查询得到记录，而不需要通过聚簇索引查询，避免回表查询的操作
优点
不需要查询包含整行记录的所有信息，大大的节省了 IO 操作
3. 主键索引最好是自增的且长度尽量小
InnoDB 创建的主键索引默认为聚簇索引，数据存放在 B+Tree 的叶子结点上，即同一个叶子结点内的各个数据是按照主键顺序存放的。同时主键字段长度越小，二级索引的叶子结点越小，减小空间开销
优点
- 在使用自增主键时，插入的新数据可以按顺序添加到当前索引的节点，因此每插入一条新纪录都是追加操作，不需要重新移动数据
- 在使用非自增主键时，插入的索引值都是随机的，甚至需要从一个索引页移动到另一个索引页，从而导致也分裂，可能造成大量内存碎片，从而导致索引结构不紧凑，从而影响查询效率
4. 索引最好为 NOT NULL
- NULL 会导致优化器在做索引选择时更加复杂且难以优化
- NULL 没有实际意义但是会占用物理空间
5. 防止索引失效
索引失效的场景
最左前缀法则
如果使用了联合索引，要遵守最左前缀法则(查询从索引最左列开始，并不跳过索引中的列)
如果跳过索引中的列，索引将部分失效
优先使用
>=和<=联合索引中出现范围查询，范围查询右侧的列索引失效
避免在索引列进行运算
索引列上进行运算操作，索引失效
字符串字段加引号
字符串类型字段使用时，不加引号，索引失效
避免头部模糊匹配
尾部模糊匹配，索引不会失效，而头部模糊匹配，索引失效
or 分割开的连接条件都要有索引
如果 or 前的条件中的列有索引，而后面的列中没有索引，索引失效
数据分布影响
如果 MySQL 评估使用索引比全表查询更慢，索引失效
