02-MySQL索引
2018-11-04 本文已影响0人
嗷大喵儿
1.索引的作用
- 保持数据完整性
- 优化数据访问性能
- 改进表的连接（join）操作
- 对结果进行排序
- 简化聚合数据操作
1.1数据完整性
MySQL使用主键和唯一键来执行每个表中存储数据的唯一性等级
主键
每个表中只能有1个主键，
不能包含NULL值
通过主键可以获取表中任意特定行
如果定了一AUTO INCREMENT列，那么此列必须是主键的一部分
唯一键
表可以有多个唯一键
唯一键可以包含NULL值，且每个NULL值都是唯一的（即NULL!=NULL）
外键：外键不是索引，是约束。然而通常大部分外间约束的先决条件就是外键所在的表和外键参照的表都必须有索引，这样才能管理外键约束。目前InnoDB支持外键约束且不要求存在对应的索引
1.2优化数据访问
索引可以让优化器在执行查询的时候不必检索表中所有的数据。通过限制需要访问的行的树木，可以显著提高查询速度。
1.3表连接
索引除在给定表上限制需要读取的数据外，索引另一个作用就是快捷高效地在相关表之间做连接操作。
1.4结果排序
优化 order by
1.5聚合操作
索引还可以作为一种更方便的计算聚合结果的工具
2.关于存储引擎
2.1存储引擎作用
- 事务性和非事务性
- 持久性和非持久性
- 表/行级别的锁定
- 不同的索引方式，如B-树，B+树，散列以及 R-树
- 聚簇索引和非聚簇索引
- 主码索引和非主码索引
- 数据压缩
- 全文索引能力
2.2存储引擎
- MyISAM 一种非事务性的存储引擎，是MySQL5.5之前版本默认的存储引擎
- InnoDB 最流行的事务性存储引擎，从5.5版本开始成功MySQL的默认引擎
- Memory 一种基于内存的，非事务性的以及非持久性的存储引擎
3.索引专业术语
索引术语.png
4.MySQL索引类型
MySQL支持在所有的关系型数据库表中创建主键，唯一键，不唯一的非主码索引等多种类型的索引。此外，在一些特定的存储引擎中MySQL还支持纯文本和空间索引类型
4.1索引数据结构理论
- B-树 索引节点和叶子节点。叶子节点保存数据，索引节点则用来告诉用户存储在叶子节点中的数据的顺序，并帮用户找到对应的数据
- B+树 B-树的增强版本，具有B-树所有特定，B+树中底层数据是根据被提及的索引进行排序的，B+树还通过在叶子节点之间的附加索引来优化扫描性能
- 散列 即散列表，参考HashMap
- 通信R-树 支持基于数据类型对几何数据进行管理
- 全文本 也是MySQL采用的基本数据结构，性能局限，没啥实际用途
4.2MySQL实现
MyISAM
基于B-树来实现主码索引，唯一索引和非主码索引。
InnoDB
基于B+ 实现主码索引（聚簇主码），这种结构把所有的数据和对应的主码组织在一起，并且在叶子节点这一层添加额外的向前和向后指针，这样更方便进行范围扫描操作
基于B-树的非主码，非主码索引存储的是主码的实际值。
使用散列进行更高效的主码查找
Memory
基于散列也可以基于B-树构建索引
4.3MySQL分区
- 分区的实现对索引的使用和优化有一些帮助
- 分区后表不在支持全文本索引，空间索引和外键索引
- 分区的优势就是使得执行的SQL语句时启用分区精简
5.创建索引
5.1单列索引
即在一列上创建索引
- ALTER TABLE <tableName> ADD PRIMARY KEY [index-name] (<column>);
- ALTER TABLE <tableName> ADD [UNIQUE] KEY|INDEX [index-name] (<column>);
- 创建非主码索引时，key 和 index关键字可互换，主码只能使用 key
- 唯一索引的目的：提供数据完整性&保证在列中任何值都只出现一次，告知优化器对给定的记录最多只可能有一行结果返回
- 如果没有索引，MySQL会使用内部文件排序算法对返回的行进行排序
5.2 多列索引
即在两列/多列上创建索引，称作混合索引/连接索引
*ALTER TABLE <tableName> ADD PRIMARY KEY [index-name] (<column1>,<column2>...);
- ALTER TABLE <tableName> ADD [UNIQUE] KEY|INDEX [index-name] (<column1>,<column2>...);
- 创建索引是耗时的工作，并且会阻塞其他操作。
- 可使用一条Alert语句将给定表上多个索引创建的语句合并起来。
- 多列索引最左边的列也可以被当作单列索引来高效地使用
- 和并where 和 order by 语句。同时使用一条索引可提升查询效率
5.3 添加索引造成的影响
- 影响写的性能
- 重复索引（同一列被重复添加索引）
- 索引是否被使用
- 增加磁盘空间的使用，主码中尽可能使用小的数据类型
