2023-04-03 索引

1.索引

作用：加速查找和约束

在数据库中索引最核心的作用是：加速查找。 例如：在含有300w条数据的表中查询，无索引需要700秒，而利用索引可能仅需1秒。
在开发过程中会为那些 经常会被搜索的列 创建索引，以提高程序的响应速度。例如：查询手机号、邮箱、用户名等。

1.1 索引原理

索引的底层是基于B+Tree的数据结构存储的。
B+Tree结构连接：https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

数据库的索引是基于上述B+Tree的数据结构实现，但在创建数据库表时，如果指定不同的引擎，底层使用的B+Tree结构的原理有些不同。

  • myisam引擎，非聚簇索引（数据 和 索引结构 分开存储）

  • innodb引擎，聚簇索引（数据 和 主键索引结构存储在一起）

1.1.1 非聚簇索引（myisam引擎）

create table 表名(
    id int not null auto_increment primary key, 
    name varchar(32) not null,
    age int
)engine=myisam default charset=utf8;
image.png

image.png

下面存储的是数据的内存地址

1.1.2 聚簇索引（innodb引擎）

create table 表名(
    id int not null auto_increment primary key, 
    name varchar(32) not null,
    age int
)engine=innodb default charset=utf8;
image.png

image.png

在MySQL文件存储中的体现：

root@192 userdb # pwd
/usr/local/mysql/data/userdb
root@192 userdb # ls -l
total 1412928
-rw-r-----  1 _mysql  _mysql       8684 May 15 22:51 big.frm，表结构。
-rw-r-----  1 _mysql  _mysql  717225984 May 15 22:51 big.ibd，数据和索引结构。
-rw-r-----  1 _mysql  _mysql       8588 May 16 11:38 goods.frm
-rw-r-----  1 _mysql  _mysql      98304 May 16 11:39 goods.ibd
-rw-r-----  1 _mysql  _mysql       8586 May 26 10:57 t2.frm，表结构
-rw-r-----  1 _mysql  _mysql          0 May 26 10:57 t2.MYD，数据
-rw-r-----  1 _mysql  _mysql       1024 May 26 10:57 t2.MYI，索引结构

上述 聚簇索引 和 非聚簇索引 底层均利用了B+Tree结构结构，只不过内部数据存储有些不同罢了。

在企业开发中一般都会使用 innodb 引擎（内部支持事务、行级锁、外键等特点），在MySQL5.5版本之后默认引擎也是innodb。

mysql> show create table users \G;
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `password` varchar(64) DEFAULT NULL,
  `ctime` datetime DEFAULT NULL,
  `age` int(11) DEFAULT '5',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> show index from users \G;
*************************** 1. row ***************************
        Table: users
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 3
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE   -- 虽然显示BTree，但底层数据结构基于B+Tree。
      Comment:
Index_comment:
1 row in set (0.00 sec)

ERROR:
No query specified
image.png

1.2 常见索引

在innodb引擎下，索引底层都是基于B+Tree数据结构存储（聚簇索引）。

在开发过程中常见的索引类型有：(前面是单列，后面是多列）

  • 主键索引：加速查找、不能为空、不能重复。 + 联合主键索引
  • 唯一索引：加速查找、不能重复。 + 联合唯一索引
  • 普通索引：加速查找。 + 联合索引

1.2.1 主键和联合主键索引

create table 表名(
    id int not null auto_increment primary key,   -- 主键
    name varchar(32) not null
);

create table 表名(
    id int not null auto_increment,
    name varchar(32) not null,
    primary key(id)
);

create table 表名(
    id int not null auto_increment,
    name varchar(32) not null,
    primary key(列1,列2)          -- 如果有多列，称为联合主键（不常用且myisam引擎支持）
);

alter table 表名 add primary key(列名);

alter table 表名 drop primary key;

注意：删除索引时可能会报错，自增列必须定义为键。

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

alter table 表 change id id int not null;

create table t7(
    id int not null,
    name varchar(32) not null,
    primary key(id)
);

alter table t6 drop primary key;

1.2.2 唯一和联合唯一索引

create table 表名(
    id int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    unique ix_name (name),
    unique ix_email (email),
);

create table 表名(
    id int not null auto_increment,
    name varchar(32) not null,
    unique (列1,列2)               -- 如果有多列，称为联合唯一索引。
);

create unique index 索引名 on 表名(列名);

drop unique index 索引名 on 表名;

1.2.3 索引和联合索引

create table 表名(
    id int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    index ix_email (email),
    index ix_name (name),
);

create table 表名(
    id int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    index ix_email (name,email)     -- 如果有多列，称为联合索引。
);

create index 索引名 on 表名(列名);

drop index 索引名 on 表名;

在项目开发的设计表结构的环节，大家需要根据业务需求的特点来决定是否创建相应的索引。

一般情况下，一张表只能有一个主键索引，id自增，有多个唯一索引，手机号，邮箱，用户名等，普通索引有多个，比如昵称。当对多列联合起来作为条件时，用联合唯一索引，比如一个用户只能对文章进行踩或赞，则可以将用户id和文章id联合唯一索引；一般情况下，我们需要用户名和密码放在一起查询，则可以将用户名和密码联合索引。

2.命中索引(面试）

操作表
在表中创建索引后，查询时一定要命中索引。
在数据库的表中创建索引之后优缺点如下：

  • 优点：查找速度快、约束（唯一、主键、联合唯一）
  • 缺点：插入、删除、更新速度比较慢，因为每次操作都需要调整整个B+Tree的数据结构关系。

所以，在表中不要无节制的去创建索引啊。。。
在开发中，我们会对表中经常被搜索的列创建索引，从而提高程序的响应速度。

CREATE TABLE `big` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(32) DEFAULT NULL,
    `email` varchar(64) DEFAULT NULL,
    `password` varchar(64) DEFAULT NULL,
    `age` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),                       -- 主键索引
    UNIQUE KEY `big_unique_email` (`email`),  -- 唯一索引
    index `ix_name_pwd` (`name`,`password`)     -- 联合索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8

一般情况下，我们针对只要通过索引列去搜搜都可以 命中 索引（通过索引结构加速查找）。

select * from big where id = 5;
select * from big where id > 5;
select * from big where email = "wupeiqi@live.com";
select * from big where name = "武沛齐";
select * from big where name = "kelly" and password="ffsijfs";
...

但是，还是会有一些特殊的情况，让我们无法命中索引（即使创建了索引），这也是需要大家在开发中要注意的。

  • 类型不一致(表格中name是字符串，查询时name是整型）

    select * from big where name = 123;       -- 未命中
select * from big where email = 123;  -- 未命中

特殊的主键：
  select * from big where id = "123"; -- 命中

  • 使用不等于

    select * from big where name != "武沛齐";                -- 未命中
select * from big where email != "wupeiqi@live.com";  -- 未命中

特殊的主键：
  select * from big where id != 123;  -- 命中

  • or，当or条件中有未建立索引的列才失效。

    select * from big where id = 123 or password="xx";            -- 未命中，password未建立索引
select * from big where name = "wupeiqi" or password="xx";    -- 未命中
特别的：
  select * from big where id = 10 or password="xx" and name="xx"; -- 命中

  • 排序，当根据索引排序时候，选择的映射如果不是索引，则不走索引。

    select * from big order by name asc;     -- 未命中（select name 命中）
select * from big order by name desc;    -- 未命中

特别的主键：
  select * from big order by id desc;  -- 命中

  • like，模糊匹配时。

    select * from big where name like "%u-12-19999";  -- 未命中
select * from big where name like "_u-12-19999";  -- 未命中
select * from big where name like "wu-%-10";      -- 未命中

特别的：
  select * from big where name like "wu-1111-%";  -- 命中
  select * from big where name like "wuw-%";      -- 命中

  • 使用函数

    select * from big where reverse(name) = "wupeiqi";  -- 未命中

特别的：
  select * from big where name = reverse("wupeiqi");  -- 命中

  • 最左前缀，如果是联合索引，要遵循最左前缀原则。

--如果联合索引为：(name,password)
     name and password       -- 命中
     name                  -- 命中
     password                -- 未命中
     name or password          -- 未命中

常见的无法命中索引的情况就是上述的示例。

对于大家来说会现在的最大的问题是，记不住，怎么办呢？接下来看执行计划。

3.执行计划

MySQL中提供了执行计划，让你能够预判SQL的执行（只能给到一定的参考，不一定完全能预判准确）。

explain + SQL语句;
image.png

其中比较重要的是 type，他他SQL性能比较重要的标志，性能从低到高依次：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const

  • ALL，全表扫描，数据表从头到尾找一遍。(一般未命中索引，都是会执行全表扫描)

    select * from big;

特别的：如果有limit，则找到之后就不在继续向下扫描.
  select * from big limit 1;

  • INDEX，全索引扫描，对索引从头到尾找一遍

    explain select id from big;
explain select name from big;

  • RANGE，对索引列进行范围查找

    explain select * from big where id > 10;
explain select * from big where id in (11,22,33);
explain select * from big where id between 10 and 20;
explain select * from big where name > "wupeiqi" ;

  • INDEX_MERGE，合并索引，使用多个单列索引搜索

    explain select * from big where id = 10 or name="武沛齐";

  • REF，根据 索引 直接去查找（非键）。

    select *  from big where name = '武沛齐';

  • EQ_REF，连表操作时常见。

    explain select big.name,users.id from big left join users on big.age = users.id;

  • CONST，常量，表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快。

    explain select * from big where id=11;                    -- 主键
explain select * from big where email="w-11-0@qq.com";    -- 唯一索引

  • SYSTEM，系统，表仅有一行(=系统表)。这是const联接类型的一个特例。

     explain select * from (select * from big where id=1 limit 1) as A;
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
【社区内容提示】社区部分内容疑似由AI辅助生成，浏览时请结合常识与多方信息审慎甄别。
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

相关阅读更多精彩内容

友情链接更多精彩内容