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;
下面存储的是数据的内存地址
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;
在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
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语句;
其中比较重要的是 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;