什么是索引
- MySQL 官方对索引的定义为:索引(Index)是帮助 MySQL 高效获取数据的数据结构。可以得到索引的本质: 索引是数据结构。
- 可以简单理解为:排好序的快速查找数据结构
索引的优缺点
优点
- 提高数据检索的效率,降低数据库的IO成本
- 通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗
缺点
- 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息
- 实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用空间的
索引的分类
基本语法
创建
CREATE [UNIQUE] INDEX [indexName] ON table_name(column);
删除
DROP INDEX [indexName] ON table_name;
查看
SHOW INDEX FROM table_name;
分类
单值索引
- 定义:即一个索引只包含单个列,一个表可以有多个单列索引
- 语法:
--和表一起创建
CREATE TABLE customer (
id INT(10) UNSIGNED AUTO_INCREMENT,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name) --单值索引
);
--单独创建单值索引
CREATE INDEX idx_customer_name ON customer(customer_name);
唯一索引
- 定义:索引列的值必须唯一,但允许有空值
- 语法:
--和表一起创建
CREATE TABLE customer (
id INT(10) UNSIGNED AUTO_INCREMENT,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name), --单值索引
UNIQUE (customer_no) --唯一索引
);
--单独创建唯一索引
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);
主键索引
- 定义:设定为主键后数据库会自动建立索引,innodb为聚簇索引
- 语法:
--和表一起创建
CREATE TABLE customer (
id INT(10) UNSIGNED AUTO_INCREMENT,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id) --主键索引
);
复合索引
- 定义:即一个索引包含多个列
- 语法:
--和表一起创建
CREATE TABLE customer (
id INT(10) UNSIGNED AUTO_INCREMENT,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name), --单值索引
UNIQUE (customer_no), --唯一索引
KEY (customer_no,customer_name) --复合索引
);
--单独创建复合索引
CREATE INDEX idx_no_name ON customer(customer_no,customer_name);
MySQL的索引
B树与B+树: 链接.
为什么说 B+树比 B-树更适合实际应用中操作系统的文件索引和数据库索引?
- B+树的磁盘读写代价更低
B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对 B 树更小。如果把所有同一内部结点 的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说 IO 读写次数也就降低了 - B+树的查询效率更加稳定
由于非终结点并不是最终指向文件内容的结点,而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同,导致每一个数据的查询效率相同
MySQL在创建表时,会根据主键来创建主键索引(如果没有主键,会用一个隐藏值来作为主键)。主键索引所构建的B+树,表中所有的记录都存放在了树的最后一层。且与一般的B+树不同的是:叶子节点间的指针是双向的
索引的使用场景
适合索引的场景
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其它表关联的字段,外键关系建立索引
- 单键/组合索引的选择问题,组合索引性价比更高
- 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
- 查询中统计或者分组字段
不适合索引的场景
- 表记录太少(有无索引差别不大)
- 经常增删改的表或者字段
- Where 条件里用不到的字段不创建索引
- 过滤性不好的不适合建索引(重复性较高,比如国籍、性别之类的字段)