概述
引入:为什么要使用索引?为了提高查找的效率,例如:在找一个年龄为10岁的人时,会进行全表扫描,即使找到了,也会全部扫描一遍。这样显然会使查询效率大大降低,此时引入索引的概念。
索引既有优势也会有劣势:
优势 | 劣势 |
提高数据检索的效率,降低数据库的IO成本 | 索引列需要占用空间 |
通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗 | 索引大大提高了查询效率,同时却也降低更新表的速度,如对表进行INSERT、UPDATE、DELETE时,效率降低。 |
结构
MySQL的索引是在存储引擎层实现的,不同的存储引擎有不同的存储结构
如:
索引结构 | 描述 |
B+Tree索引 | 最常见的索引类型,大部分引擎都支持B+树索引 |
Hash索引 | 底层数据结构是用哈希表实现的,只有精准匹配索引列的查询才有效,不支持范围查询 |
R-tree(空间索引) | 空间索引是MyISAM引擎的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少 |
Full-text(全文索引) | 是一种通过建立倒排索引,快速匹配文档的方式,类似于Lucene,Solr,ES |
索引分类
1、主键索引:
含义:针对表中主键创建的索引
特点:默认自动创建,只能有一个
关键字:primary
2、唯一索引:
含义:避免同一个表中某数据列中的值重复
特点:可有多个
关键字:unique
3、常规索引:
含义:快速定位特定数据
特点:可有多个
关键字:无
4、全文索引(用的少):
含义:全文索引查找的是文本中的关键词,而不是比较索引中的值
特点:可有多个
关键字:fulltext
在InnoDB存储引擎中,根据索引的存储形式,又可分为以下两种:
分类 | 含义 | 特点 |
聚集索引 | 将数据存储与索引放到一块,索引结构的叶子节点保存了行数据 | 必须有,而且只有一个 |
二级索引(辅助索引) | 将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
聚集索引的选取规则:
1、如果存在主键,主键索引就是聚集索引
2、如果不存在主键,将使用第一个唯一索引作为聚集索引
3、如果表没有主键,或没有合适的唯一索引,则会自动生成rowid作为隐藏的聚集索引
聚集索引:叶子节点下面挂的是那一行的数据,如查询主键id
二级索引:叶子节点挂的是那一行的id,如查询名字
根据名字查询拿到主键值,再回表查询拿到数据
语法
1、创建索引
格式:create [unique|fulltext] index 索引名 on 表名(表中字段名...);
注意:
unique和fulltext为可选,不写则默认为常规索引
索引名一般遵循idx_tablename_name的格式
表中字段名可以为多个,即一个索引可以关联多个字段
如果只关联一个字段叫单列索引,关联多个叫联合索引
2、查看索引
show index from 表名;
3、删除索引
drop index 索引名 on 表名;