索引简介
索引是帮助MySQL高效获取数据的数据结构(有序)。在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查法,这种数据结构就是索引
为什么要使用索引?
-
通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
-
可以大大加快 数据的检索速度(大大减少的检索的数据量), 这也是创建索引的最主要的原因。
-
帮助服务器避免排序和临时表。
-
将随机IO变为顺序IO
-
可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。
使用索引的注意事项
-
在经常需要搜索的列上,可以加快搜索的速度;
-
在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。
-
在经常需要排序的列上创 建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间;
-
对于中到大型表索引都是非常有效的,但是特大型表的话维护开销会很大,不适合建索引
-
在经常用在连接的列上,这 些列主要是一些外键,可以加快连接的速度;
-
避免 where 子句中对字段施加函数,这会造成无法命中索引。--哪些操作会引起索引失效
-
在使用InnoDB时使用与业务无关的自增主键作为主键,即使用逻辑主键,而不要使用业务主键。
-
删除长期未使用的索引,不用的索引的存在会造成不必要的性能损耗 MySQL 5.7 可以通过查询 sys 库的 chema_unused_indexes 视图来查询哪些索引从未被使用
-
在使用 limit offset 查询缓慢时,可以借助索引来提高性能
索引结构
MySQL的索引是在存储引擎层实现的,不同的存储引擎有不同的结构,主要包含以下几种:
索引结构 | 描述 |
---|---|
B+Tree索引 | 最常见的索引类型,大部分引擎都支持B+树索引 |
Hash索引 | 底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效,不支持范围查询 |
R-tree(空间索引) | 空间索引是MyISAM引擎的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少 |
Full-text(全文索引) | 是一种通过建立倒排索引,快速匹配文档的方式。类似于ES,Solr |
索引分类
分类 | 含义 | 特点 | 关键字 |
---|---|---|---|
主键索引 | 针对于表中主键创建的索引 | 默认自动创建,只能有一个 | PRIMARY |
唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | UNIQUE |
常规索引 | 快速定位特定数据 | 可以有多个 | |
全文索引 | 全文索引查找的是文本中的关键词,而不是比较索引中的值 | 可以有多个 | FULLTEXT |
在InnoDB存储引擎中,根据索引的存储形式,又可以分为以下两种:
分类 | 含义 | 特点 |
---|---|---|
聚集索引 | 将数据存储于索引放到了一块,索引结构的叶子结点保存了行数据 | 必须有,而且只有一个 |
二级索引(辅助索引或非聚集索引) | 将数据与索引分开存储存储,索引结构的叶子结点关联的是对应的主键 | 可以存在多个 |
聚集索引选取规则
-
如果存在主键,主键索引就是聚集索引
-
如果不存在索引,将使用第一个唯一索引作为聚集索引
-
如果表没有主键,或没有合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索引回表查询:先根据二级索引查询的聚集索引,再根据聚集索引拿到想查询的数据
索引操作
- 添加PRIMARY KEY(主键索引)
ALTER TABLE `table_name` ADD PRIMARY KEY (`column`)
- 添加UNIQUE(唯一索引)
ALTER TABLE `table_name` ADD UNIQUE (`column`)
- 添加INDEX(普通索引)
ALTER TABLE `table_name` ADD INDEX index_name (`column`)
- 添加FULLTEXT(全文索引)
ALTER TABLE `table_name` ADD FULLTEXT ( `column`)
- 添加多列索引
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )