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;
