MySQL优化之索引篇: 索引的基础性了解
1.索引的基础概念
MySQL官方对索引的定义为: 索引(index) 是帮助MySQL高效获取数据的数据结构(有序). 在数据之外, 数据库系统还维护满足特定查找算法的数据结构, 这些数据结构以某种方式引用(指向) 数据结构, 这样就可以在这些数据结构上实现高级查找属于算法, 这种数据结构就是索引, 如下面的示意图所示:
左边是数据表, -共有两列七条记录,最左边的是数据记录的物理地址(注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的)。为了加快Col2的查找,可以维护-一个右边所示的二叉查找树,每个节点分别包含索引键值和一一个指向对应数据记录物理地址的指针 ,这样就可以运用二又查找快速获取到相应数据。
一般来说索引本身也很大 ,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上。索引是数据库中用来提高性能的最常用的工具
2.索引的分类
普通索引：仅加速查询
唯一索引：加速查询 + 列值唯一（可以有null）
主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
全文索引：对文本的内容进行分词，进行搜索
3.索引的基础语法
(1)索引的创建
<直接创建索引>-- 创建普通索引
CREATE INDEX index_name ON table_name(col_name);
-- 创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name(col_name);
-- 创建普通组合索引
CREATE INDEX index_name ON table_name(col_name_1,col_name_2);
-- 创建唯一组合索引
CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);
-- 通过修改表结构创建索引
ALTER TABLE table_name ADD INDEX index_name(col_name);
-- <通过修改表结构创建索引>
ALTER TABLE table_name ADD INDEX index_name(col_name);
(2)索引的删除
-- 直接删除索引
DROP INDEX index_name ON table_name;
-- 修改表结构删除索引
ALTER TABLE table_name DROP INDEX index_name;
(3)索引的查看
#查看:
show index from `表名`;
#或
show keys from `表名`
(4)alter命令
-- 1).该语句添加一个主键，这意味着索引值必须是唯一-的 ，且不能为NULL
alter table tb_ name add pr imary key(column. _list);
-- 2).这条语句创建索引的值必须是唯一-的 (除了NULL外，NULL可能会出现多次)
alter table tb_ name add unique index_ name (column_ list);
-- 3). 添加普通索引，索引值可以出现多次。
alter table tb_ name add index index_ name (column. _list) ;
-- 4). 该语句指定了索引为FULLTEXT，用于全文索引
alter table tb_ name add fulltext index_ name(column_ list);
(5)其他
-- 查看表结构
desc table_name;
-- 查看生成表的SQL
show create table table_name;
4.索引的设计原则
索引的设计可以遵循一些已有的原则 , 创建索引的时候请尽量考虑符合这些原则 , 便于提升索引的使用效率,更高效地使用索引 .
- 对查询频次较高 , 且数据量较大的表建立索引 .
- 索引字段的选择 , 最佳选列 应当从where 子句的条件中提取 , 如果where 子句中的组合比较多 , 那么应挑选最常用 , 过滤效果最好的列的组合 .
- 使用唯一索引 , 区分度越高 , 使用索引的效率就越高
- 索引可以有效的提升查询数据的效率 , 但索引数量并不是多多益善 , 索引越多 , 维护索引的代价自然也就水涨船高 . 对于插入, 更新, 删除 等DML操作比较繁琐的表来说 , 索引过多 , 会引入相当高的维护代价 , 降低DML 操作的效率 , 增加相应操作的时间消耗 , 另外索引过多的话 , MySQL也会犯 选择困难症 , 虽然最终仍然会找到一个可用的索引 , 但无疑提高了索引的代价 .
- 使用段索引 , 索引创建之后也是使用硬盘来存储的 , 因此提高索引访问的 I/O 效率 , 也可以跳高总体的访问效率 . 假如构成索引的字段 总长度比较短 , 那么在给定大小的存储块内 , 可以存储更多的索引值 , 相应的可以有效地提升MySQL访问索引的 I/O 效率.
- 利用最左前缀的原则 , N个列组合而成的组合索引 , 那么相当于是创建了N 个索引 . 如果查询时where 子句使用了组成该索引的前几个字段 , 那么这条查询SQL可以利用组合索引来提升查询效率 .
-- 创建复合索引:
CREATE INDEX idx_ name_ email_ status ON tb_ seller (NAME , email,STATUS);
/*就相当于
对name创建索引;
对name，email 创建了索引;
对name, email, status创建了索引; */
5.索引的优缺点
优点:
1. 类似于书籍的目录索引 , 提高数据检索的效率 , 降低数据库的 I/O 成本 .
2. 通过索引列对数据进行排序 , 降低数据排序的成本 , 降低 CPU 的消耗 .
缺点:
1. 实际上 索引也是一张表 , 该表保存了主键与索引字段 , 并指向实体类的记录 , 所以 索引列 也是要占用空间的
2. 虽然索引大大提高了查询的效率 , 但同时也降低了更新表的速度 , 如对表进行 INSERT , UPDATE , DELETE .
因为更新表时, MySQL不仅要保存数据 , 还要保存 索引文件每次更新添加了索引列的字段 , 都会调整因为更新所带来的键值变化后的索引信息 .
6.索引的结构
MySQL的基本存储结构是页（记录都存在页里面），各个数据页可以组成一个双向链表，每个数据页都会为存储在它里面的记录生成一个页目录，每个数据页中的内容又可以组成一个单向链表。由于B+Tree只有叶子节点保存key信息，查询任何key都要从root走到叶子，所以B+Tree的查询效率比较稳定 . MySQL 中的B+Tree 索引结构示意图 如下所示:
MySQL 索引数据结构对经典的B+Tree 进行了优化, 在原B+Tree 的基础上, 增加一个指向相邻叶子节点的链表指针 , 就形成了带有顺序的B+Tree, 提高了区间访问的性能 .
