说透MySQL：从数据结构到性能优化，附实际案例和面试题

typora-copy-images-to: img

mysql索引

第一章 MySQL性能(掌握)

1 分析-数据库查询效率低下

我们进入公司进行项目开发往往关注的是业务需求和功能的实现，但是随着项目运行的时间增加，数据量也就增加了，这时会影响到我们数据库的查询性能。所以我们要提高操作数据库的性能，有如下两种方式：

1.硬优化：就是软优化之后性能还很低，只能采取硬优化，最后的步骤了，就是公司花钱购买服务器。在硬件上进行优化。我们在这里不关注。我们关注的软优化。

2.软优化: 在操作和设计数据库方面上进行优化，例如下面讲解的索引。这是我们本课程学习的重点。（重点）

2 分析-执行次数比较多的语句

1.执行次数比较多的语句分类

* 1）查询密集型
	我们使用查询频率较高，8：2 左右
		我们就可以使用索引来进行优化

* 2）修改密集型
	在实际开发中修改密集型一般在订单中使用较多，例如用户增加商品，那么订单中的商品数量增加，或者用户修改商品数量，订单也会修改或者删除等

2.查询累计插入和返回数据条数，即查看当前数据库属于查询密集型还是修改密集型。

-- 查询累计插入和返回数据条数
show global status like 'Innodb_rows%';

说明：Innodb数据一种存储引擎。我们后续会讲解。

3 查看-sql语句的执行效率

我们想学习mysql的性能问题，这里需要准备千万条数据，这样才可以看出效果。

1.插入千万条记录

直接将如下sql语句复制到可视化工具中执行即可。涉及到的存储过程查资料学习。

create database itcast01_noindex;

use itcast01_noindex;

-- 1. 准备表
CREATE TABLE user(
	id INT,
	username VARCHAR(32),
	password VARCHAR(32),
	sex VARCHAR(6),
	email VARCHAR(50)
);

-- 2. 创建存储过程，实现批量插入记录
DELIMITER $$ -- 声明存储过程的结束符号为$$
-- 可以将下面的存储过程理解为java中的一个方法，插入千万条数据之后，在调用存储过程
CREATE PROCEDURE auto_insert()
BEGIN
    DECLARE i INT DEFAULT 1;
	START TRANSACTION; -- 开启事务
    WHILE(i<=10000000)DO
        INSERT INTO user VALUES(i,CONCAT('jack',i),MD5(i),'male',CONCAT('jack',i,'@itcast.cn'));
        SET i=i+1;
    END WHILE;
	COMMIT; -- 提交
END$$ -- 声明结束
DELIMITER ; -- 重新声明分号为结束符号

-- 3. 查看存储过程
SHOW CREATE PROCEDURE auto_insert;

-- 4. 调用存储过程
CALL auto_insert();

锁哥电脑需要预计5分钟左右

说明：由于每个人的电脑配置不一样，所以插入千万条数据的时间也是不一样的，有的人是2分钟，有的人十几分钟或者半个小时。

需求：查询id是22的用户。

大概需要8秒。时间太长了。那么我们需要使用接下来讲解的索引进行优化。

第二章 索引(掌握)

1 什么是索引

索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

将数据进行排序整理的过程就称为索引

我们根据索引去查，提高效率

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

2 MySQL索引分类 面试

* 主键（约束）索引
		主键约束+提高查询效率
		
* 唯一（约束）索引
		唯一约束+提高查询效率

* 普通索引
		仅提高查询效率

* 组合（联合）索引
		多个字段组成索引 name age 

* hash索引
		根据key-value 效率非常高
* 全文索引
		通过查找文本中的关键词，类似于搜索引擎

说明：

1.我们创建表时就会指定主键和唯一约束，那么就相当于给表的字段添加了主键和唯一索引。

3 MySQL索引语法

1 创建索引

① 在已有表的字段上直接创建【了解】

-- 创建普通索引
create index 索引名 on 表名(字段);

-- 创建唯一索引
create unique index 索引名 on 表名(字段);

-- 创建普通组合索引
create index 索引名 on 表名(字段1,字段2,..);

-- 创建唯一组合索引
create unique index 索引名 on 表名(字段1,字段2,..);

说明：

1.如果在同一张表中创建多个索引，要保证索引名是不能重复的

2.上述创建索引的方式比较麻烦，还需要指定索引名

3.采用上述方式不能添加主键索引

【准备的创建表的SQL语句】

create database day04;

use day04;
-- 创建学生表
CREATE TABLE student(
 id INT,
 name VARCHAR(32),
 telephone VARCHAR(11)
);

【给上述表的字段设置索引】

1.给name字段设置普通索引

CREATE INDEX name_idx ON student(name);

2.给telephone字段设置唯一索引

CREATE UNIQUE INDEX telephone_uni_idx ON student(telephone);

设置好之后可以通过图形化工具查看设置的索引：

② 在已有表的字段上修改表时指定【了解】

-- 添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
alter table 表名 add primary key(字段);  --默认索引名：primary

-- 添加唯一索引（除了NULL外，NULL可能会出现多次）
alter table 表名 add unique(字段); -- 默认索引名：字段名

-- 添加普通索引，索引值可以出现多次。
alter table 表名 add index(字段); -- 默认索引名：字段名

【准备的创建表的SQL语句】

-- 创建学生表
CREATE TABLE student2(
 id INT,
 name VARCHAR(32),
 telephone VARCHAR(11)
);

【给上述表的字段设置索引】

1.指定id为主键索引

ALTER TABLE student2 ADD PRIMARY KEY(id);

2.指定name为普通索引

ALTER TABLE student2 ADD INDEX(name);

3.指定telephone为唯一索引

ALTER TABLE student2 ADD UNIQUE(telephone);

设置好之后可以通过图形化工具查看设置的索引：

③ 创建表时指定【掌握】

-- 创建学生表
CREATE TABLE student3(
 id INT PRIMARY KEY AUTO_INCREMENT, -- 主键索引
 name VARCHAR(32),
 telephone VARCHAR(11) UNIQUE, -- 唯一索引
 sex VARCHAR(5),
 birthday DATE,
 INDEX(name) -- 普通索引
);

2 查看索引

show index from 表名;

【查看student3表的索引信息】

show index from student3;

【结果】

3 删除索引

• 语法

【语法1】直接删除

-- 直接删除
drop index 索引名 on 表名;

【语法2】修改表时删除

-- 修改表时删除
alter table 表名 drop index 索引名;

• 练习
  【1】删除student表的name普通索引

DROP INDEX name_idx ON student;

【2】删除student表的telephone唯一索引

ALTER TABLE student DROP INDEX telephone_uni_idx;

4 千万表记录索引效果演示

使用之前创建好的user数据表中的千万条数据进行测试。注意user表中是没有索引的。

【1.先来测试没有索引情况下查询】

-- 1.指定id查询
select * from user where id = 8888888;
-- 2.指定username精准查询
select * from user  where username = 'jack1234567';

【2.给这2个字段添加索引】

说明：给表的字段添加索引时，底层通过排序方式进行关联组合。所以需要消耗一些时间，并且索引也会占硬盘空间。所以大家使用索引时还需要慎重。

没有添加索引之前，数据占硬盘空间大小：

按照如下方式给以下字段添加索引

-- 指定id为主键索引
ALTER TABLE USER ADD PRIMARY KEY(id);
-- 指定username为普通索引
ALTER TABLE USER ADD INDEX(username);

添加索引之后，数据占硬盘空间大小：

如果在多添加索引，那么占硬盘空间大小还会增加。如果表很复杂，索引加多的话，有可能比原来多几个G也是有可能的。

【3.再测试有索引情况下查询】

-- 1.指定id查询
select * from user where id = 8888888;
-- 2.指定username精准查询
select * from user  where username = 'jack1234567';

说明：通过以上结果可以看出有了索引之后，查询速度比之前快了几十倍。快的飞起。

5 索引的优缺点

优势

1） 类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本。
2） 索引底层就是排序，通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

劣势

• 在数据库建立过程中，需花费较多的时间去建立并维护索引，特别是随着数据总量的增加，所花费的时间将不断递增。
• 在数据库中创建的索引需要占用一定的物理存储空间，这其中就包括数据表所占的数据空间以及所创建的每一个索引所占用的物理空间。
• 在对表中的数据进行修改时，例如对其进行增加、删除或者是修改操作时，索引还需要进行动态的维护，这给数据库的维护速度带来了一定的麻烦。

6 索引的数据结构

1 概述

我们知道索引是帮助MySQL高效获取排好序的数据结构。

为什么使用索引后查询效率提高很多呢？

肯定和mysql底层的数据结构有关的，接下来我们就分析下mysql中的索引底层的数据结构。

每一行数据都对应一个磁盘地址，假设我要想查找数据89，那么如果没有索引，那么内存读取磁盘会进行6次的磁盘IO。

在没有索引的情况下我们执行一条sql语句，那么是表进行全局遍历，磁盘寻址，上述最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。每个存储数据的磁盘片有可能是分开的。

select  * from user where col2=89;

为了加快的查找效率，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找快速获取到相应数据。

select  * from user where col2=89;

2 索引的数据结构

数据结构学习网站(美国旧金山大学)

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

1. 二叉查找树：左边的子节点比父节点小，右边的子节点比父节点大

说明：类似上述数据比较特殊的情况下，那么如果存储在二叉查找树中就会出现类似链表的情况，那么会大大降低查找效率。

2. 红黑树：平衡二叉树（左旋、右旋、变色）

说明：如果数据结构是红黑树，那么查询1000万条数据，根据计算树的高度大概是23左右，这样确实比之前的方式快了很多，但是如果高并发访问，那么一个用户有可能需要23次磁盘IO.那么100万用户，那么会造成效率极其低下。所以为了减少红黑树的高度，那么就得增加树的宽度，就是不再像红黑树一样每个节点只能保存2个数据，可以引入另外一种数据结构，一个节点可以保存多个数据，这样宽度就会增加从而降低树的高度。这种数据结构例如BTree就满足。

3.BTree：多路平衡搜索树

说明：

1.上述数据结构称为BTree,翻译过来是多路平衡搜索树，B 通常认为是Balance的简称。并不是二叉的。

2.一个节点可以有多个元素，按照上述BTree数据结构，第一个节点即根节点含有3个元素。

3.BTree增加了树的宽度，一个节点可以存储多个元素,每个元素由索引、指针域、数据域组成。

4.一个节点大概有16KB大小，不是节点越大越好。假设一个节点占1G，那么高并发时会占大量的服务器内存，反而会降低效率.

5.内存读取硬盘的内容一次读取单位:页(page 每页大小是4KB)

举例：假设读取一个字符a,那么会读取字符a的前后内容，大小是4KB,然后在读取的内容中找到字符a.读取一个字符是4KB,10个字符也会读取4KB.

6.一个元素=索引(bigint 8字节)+指针域(6字节)+数据域(data 假设是1KB).而一个节点大概有16KB大小，这样计算下来一个节点能存储15个元素。那么log以15为底的1000W的对数大概是6左右，这样就一下将原来红黑树的高度23降为6，宽度增加，高度降低就会大大提高查找效率。

7.BTree有效的控制了树的高度，但是还会有如下问题：

1)树的高度还是有点高

2)范围查询磁盘IO次数较多。例如上述数据查找比15索引大的数据，由于15索引对应的元素具有指针域指定，所以到比15大的数据域中进行查找，那么IO次数还是比较多

4.B+Tree：优化BTree（非叶子节点：索引+指针、叶子节点：索引+数据【地址】）

说明：

1.非叶子节点： 不是树结构最下面的节点.存储的是：索引+指针域。

2.叶子节点 ： 就是最后一层子节点 , 数据存储在叶子节点。存储的是：所有索引+数据或者数据的地址值

注：

1）不同的存储引擎叶子节点存储的内容不一样，有可能是：索引+数据。也有可能是索引+数据的地址值。

2）B+Tree是可以允许有冗余索引出现的，每个节点都有索引，例如上述索引15，明显是冗余的。

3.非叶子节点都是由索引+指针域组成的，一个索引占8字节，一个指针域占6字节，一个元素大概需要14字节。而一个节点总共容量是16KB.那么可以计算出一个节点可以存储的元素个数：

16*1024字节 / (8+6)=1170个元素

4.对于根节点中可以存储1170个元素，那么根据每个元素的地址值又会找到下面的子节点，每个子节点也会存储1170个元素，那么第二层即第二次IO的时候就会找到数据大概是：1170*1170=135W。也就是说B+Tree数据结构中只需要经历两次磁盘IO就可以找到135W条数据。

5.对于第二层每个元素有指针域，那么会找到第三层，第三层由索引+数据域组成，假设索引+数据域总大小是1KB.而每个节点一共能存储16KB.所以一个第三层一个节点大概可以存储16个元素即16条记录。那么结合第二层每个元素通过指针域找到第三层的节点，第二层一共是135W个元素，那么第三层总元素大小就是：135W*16结果就是2000W+的元素个数。

6.结合上述讲解B+Tree我们发现有如下优点：

1）千万条数据，B+Tree可以控制在小于等于3的高度。

2）所有的数据都存储在叶子节点上，并且底层已经实现了按照索引进行排序，还可以支持范围查询，叶子节点是一个双向链表，支持从小到大或者从大到小查找。

补充：由于叶子节点按照索引已经排好序，每次查找范围的数据不用再像BTree还得回到根节点查找，直接在叶子节点中查找即可.

小结：

B+Tree好处：

1）降低树的高度

2) 叶子节点按照索引排好序，支持范围查找，速度会很快。

3）还有一点是mysql将根节点都加载到内存中，每张表有一个根节点，大小是16KB.那么这样的好处，按照上述如果是千万条数据，那么只有2次磁盘IO.这就是为什么我们加完索引之后瞬间查到数据的原因了。

3 MySQL中的B+Tree

-- 查看mysql索引节点大小
show global status like 'innodb_page_size';

MySql索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

MySQL中的 B+Tree 索引结构示意图：

7 聚簇索引和非聚簇索引

数据库表的索引从数据存储方式上可以分为聚簇索引和非聚簇索引（又叫二级索引或者辅助索引）两种。Innodb的聚簇索引在同一个BTree中保存了索引列和具体的数据，在聚簇索引中，实际的数据保存在叶子页中，中间的节点页保存指向下一层页面的指针。“聚簇”的意思是数据行被按照一定顺序一个个紧密地排列在一起存储。一个表只能有一个聚簇索引，因为在一个表中数据的存放方式只有一种。

一般来说，将通过主键作为聚簇索引的索引列，也就是通过主键聚集数据。如果你不为表指定一个主键，MySQL会将第一个组成列的not null的唯一索引作为聚簇索引。如果InnoBD表没有主键且没有适合的唯一索引，MySQL将自动创建一个隐藏的名字为“GEN_CLUST_INDEX ”的聚簇索引。

InnoDB（聚簇索引）

MySQL5.5版本之后默认存储引擎

特点：支持事务、支持外键约束

-- 创建 innodb存储引擎表
CREATE TABLE tab_innodb(
 id INT,
 name VARCHAR(32)
)ENGINE = INNODB; -- 这里不指定ENGINE = INNODB默认也是INNODB

上述创建好数据表之后，在对应的mysql安装目录的数据库下面会创建如下两个文件：

说明：

1.InnoDB存储引擎是将数据和索引都存储在一个文件中即.ibd中了。所以InnoDB存储引擎也称为聚集索引，因为将数据和索引放在一个文件中了。

2.InnoDB存储引擎是Mysql的默认存储引擎。InnoDB存储引擎提供了具有提交、回滚、恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些。

3.MySQL数据库存放数据目录里的db.opt文件是MySQL建库过程中自动生成的，记录该库的默认字符集编码和字符集排序规则用的。

也就是说如果你创建数据库指定默认字符集和排序规则，那么后续创建的表如果没有指定字符集和排序规则，那么该新建的表将采用db.opt文件中指定的属性

MyISAM（非聚簇索引）

MySQL5.5版本之前默认存储引擎

特点：不支持事务、不支持外键约束

-- 创建 myisam存储引擎表
CREATE TABLE tab_myisam(
 id INT,
 name VARCHAR(32)
)ENGINE=MYISAM;

说明：

1.MyISAM存储引擎将数据和索引存入两个不同的文件。

2.MyISAM 不支持事务、也不支持外键，其优势是访问的速度快，对事务的完整性没有要求可以使用这个引擎来创建表 。

8 hash索引

哈希索引（hash index）基于哈希表实现，对于hash索引中的所有列，存储引擎都会为每一行的表数据计算一个hash码值，hash索引中存储的就是hash码，索引的结构十分紧凑，这也让hash索引查找的速度非常快只有精确匹配索引所有列的查询才有效。不适合范围查找，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索。

简单地说，哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

select * from user where id=4; -- 假设id使用hash索引存储，查询的时候根据一次哈希算法一次便可以查找到数据

select * from user where id<4; -- 假设id使用hash索引存储，将小于4的值经过哈希算法之后，造成原来存储数据的位置有可能发生了改变，导致没办法再利用索引完成范围查询检索

注意：Hash索引遇到大量Hash值相等的情况后性能并不一定就会比BTree索引高在有大量重复数据情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题

9 覆盖索引

准备素材：

use netclass;
create table `tb_seller` ( 
	`sellerid` varchar (100), 
	`name` varchar (100), 
	`nickname` varchar (50), 
	`password` varchar (60), 
	`status` varchar (1), 
	`address` varchar (100), 
	`createtime` datetime, 
	primary key(`sellerid`) 
)engine=innodb default charset=utf8mb4; 
insert into `tb_seller` 
(`sellerid`,`name`,`nickname`,`password`,`status`,`address`,`createtime`) 
values('alibaba','阿里巴巴','阿里小 店','e10adc3949ba59abbe56e057f20f883e',
'1','北京市','2088-01-01 12:00:00'); 
insert into `tb_seller` (`sellerid`,`name`,`nickname`,`password`,
`status`,`address`, `createtime`) 
values('baidu','百度科技有限公司','百度小店',
'e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00'); 
insert into `tb_seller` (`sellerid`,`name`,`nickname`,`password`,`status`,
`address`, `createtime`) 
values('huawei','华为科技有限公司','华为小店',
'e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00'); 
insert into `tb_seller` (`sellerid`,`name`,`nickname`,`password`,`status`,
`address`, `createtime`) 
values('itcast','传智播客教育科技有限公司','传智播客',
'e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00'); 
insert into `tb_seller` (`sellerid`,`name`,`nickname`,`password`,`status`,
`address`, `createtime`) 
values('xiaomi','小米科技有限公司','小米公司',
'e10adc3949ba59abbe56e057f20f883e','1','上海市','2088-01-01 12:00:00'); 
insert into `tb_seller` (`sellerid`,`name`,`nickname`,`password`,`status`,
`address`, `createtime`) 
values('zijietiaodong','字节跳动科技有限公司','字节跳动公司',
'e10adc3949ba59abbe56e057f20f883e','1','杭州','2088-01-01 12:00:00');

insert into `tb_seller` (`sellerid`,`name`,`nickname`,`password`,`status`,
`address`, `createtime`) 
values('pinduoduo','拼多多科技有限公司','拼多多公司',
'e10adc3949ba59abbe56e057f20f883e','1','上海市','2088-01-01 12:00:00'); 
-- 创建索引
create index idx_seller_name_sta_addr on tb_seller(name,status,address);

【1】覆盖索引：覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。

【2】尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select * 。

1.explain select * from tb_seller where name='科技' and status='0' and address='北京市';
2.explain select name from tb_seller where name='科技' and status='0' and address='北京市';
3.explain select name,status from tb_seller where name='科技' and status='0' and address='北京市';
4.explain select name,status,address from tb_seller where name='科技' and status='0' and address='北京市';

如果查询列，超出索引列，也会降低性能

5.explain select name,status,address,password from tb_seller where name='科技' and status='0' and address='北京市';

TIP :
	using index ：使用覆盖索引的时候就会出现 
	using where：在查找使用索引的情况下，需要回表去查询所需的数据 
	using index condition：查找使用了索引，但是需要回表查询数据
	using index ; using where：查找使用了索引，需要的数据都在索引列中能找到，所以不需要回表 查询数据
	回表查询：先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。

【3】补充

explain表示分析执行计划

我们常常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。所以我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。

10 索引创建原则

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

1. 字段内容可识别度不能低于70%，字段内数据唯一值的个数不能低于70%
	例如：一个表数据只有50行，那么性别和年龄哪个字段适合创建索引，明显是年龄，因为年龄的唯一值个数比较多，性别只有两个选项 。性别的识别度是50%。 男 女     

2. 经常使用where条件搜索的字段，例如user表的id name等字段。

3. 经常使用表连接的字段（内连接、外连接），可以加快连接的速度。

4. 经常排序的字段 order by，因为索引已经是排过序的，这样一来可以利用索引的排序，加快排序查询速度。 

* 注意: 那是不是在数据库表字段中尽量多建索引呢?肯定是不是的。因为索引的建立和维护都是需要耗时的 创建表时需要通过数据库去维护索引，添加记录、更新、修改时，也需要更新索引，会间接影响数据库的 效率。

11 避免索引失效

1). 全值匹配 ，对索引中所有列都指定具体值。

该情况下，索引生效，执行效率高。

explain select * from tb_seller where name='小米科技有限公司' and status='1' and address='上海市';

2). 最左前缀法则

如果索引了多列，这里指的是复合索引(联合索引)，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。

注意：如果条件中包含了复合索引的全部字段，那么可以不考虑前后顺序。

匹配最左前缀法则，走索引：

1.explain select * from tb_seller where name='小米科技有限公司'; -- key_len表示索引字段的长度即占字节个数，不同的编码表计算方式不一致
2.explain select * from tb_seller where name='小米科技有限公司' and status='1'; 
3.explain select * from tb_seller where name='小米科技有限公司' and status='1' and address='上海市';

违反最左前缀法则 ， 索引失效：

4.explain select * from tb_seller where status='1';
5.explain select * from tb_seller where status='1' and address='上海市';

如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：

6.explain select * from tb_seller where name='小米科技有限公司' and address='上海市';

注意：上述sql语句跳跃了status这一列，所以上述sql语句只是对索引name生效，key_len的结果403只是name索引的长度，而address索引字段并没有起到所以效果。

注意：如果条件中包含了复合索引的全部字段，那么可以不考虑前后顺序。

explain select * from tb_seller where address='上海市' and status='1' and name='小米科技有限公司' ;

3). 范围查询右边的列，不能使用索引 。

1.explain select * from tb_seller where name='小米科技有限公司' and status='1' and address='上海市';
2.explain select * from tb_seller where name='小米科技有限公司' and status>'1' and address='上海市'; -- 只有name和status索引生效

根据前面的两个字段name ， status 查询是走索引的， 但是最后一个条件address 没有用到索引。

4). 不要在索引列上进行运算操作，索引将失效。

1.-- 3 表示索引 2 表示截取2个字符
select * from tb_seller where substring(name,3,2)='科技';

2.explain select * from tb_seller where substring(name,3,2)='科技';

5).字符串不加单引号，造成索引失效。

1.explain select * from tb_seller where name='小米科技有限公司' and status='1';
2.explain select * from tb_seller where name='小米科技有限公司' and status=1; -- 这里name索引字段生效，status索引字段是无效的

由于，在查询时，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效。

6).用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

示例，name字段是索引列 ， 而createtime不是索引列，中间是or进行连接是不走索引的 ：

1.explain select * from tb_seller where name='传智播客教育科技有限公司' and createtime = '2088-01-01 12:00:00';
2.explain select * from tb_seller where name='传智播客教育科技有限公司' or createtime = '2088-01-01 12:00:00';

7). 以%开头的Like模糊查询，索引失效。

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

1.explain select * from tb_seller where name like '传智播客%';
2.explain select * from tb_seller where name like '%传智播客';
3.explain select * from tb_seller where name like '%传智播客%';

解决方案 ：通过覆盖索引来解决.

1.explain select sellerid from tb_seller where name like '%传智播客%';
2.explain select sellerid,name from tb_seller where name like '%传智播客%';
3.explain select sellerid,name,status,address from tb_seller where name like '%传智播客%';
4.explain select sellerid,name,status,address,password from tb_seller where name like '%传智播客%';

说明：解决上述sql语句索引失效情况需要使用覆盖索引，而password子字段无索引，所以索引失效。

8). 如果MySQL评估使用索引比全表更慢，则不使用索引。

1.show index from tb_seller; -- 查看下索引
2.create index idx_address on tb_seller(address); -- 单独创建 address字段为索引
3.explain select * from tb_seller where address='北京市'; -- 走索引，反而效率更低，全表扫描
4.explain select * from tb_seller where address='上海市';

9). in 走索引， not in 索引失效。

1.explain select * from tb_seller where sellerid in('baidu','huawei','xiaomi');

2.explain select * from tb_seller where sellerid not in('baidu','huawei','xiaomi');

12 批量数据分页查询的优化方案

• 传统查询分析

• 使用limit 随着offffset(索引)增大, 查询的速度会越来越慢, 会把前面的数据都取出,找到对应位置。
  一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题是limit 9900000,10,此时需要MySql排序前9900010记录，仅仅返回9900000-9900010的记录，其他记录丢弃，查询排序的代价非常大。

1.use itcast01_hasindex;
2.SELECT * FROM user LIMIT 0,10; 
3.SELECT * FROM user LIMIT 9900000,10;
4.explain SELECT * FROM user LIMIT 9900000,10;

• 使用子查询优化
  在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

-- SELECT id FROM user LIMIT 9900000,10 查询主键数据，id是索引，基于索引分页查询
1.SELECT * FROM user u,(SELECT id FROM user order by id LIMIT 9900000,10) u1 where u.id = u1.id;
2.explain SELECT * FROM user u,(SELECT id FROM user order by id LIMIT 9900000,10) u1 where u.id = u1.id;

• 使用id限定优化
  这种方式要求数据表的id是连续递增的，可以把limit查询转换成某个位置的查询。

1.SELECT * FROM user WHERE id > 9900000 LIMIT 10;
2.explain SELECT * FROM user WHERE id > 9900000 LIMIT 10;

总结：这种查询方案的前提条件是主键递增且数据有序。其实就是利用B+树的原理进行的，因为在Innodb存储引擎中，数据是通过B+树进行存储，叶子节点存储的是主键id，另外子查询中也用到了覆盖索引。一般查询条件排序条件为按照主键id递增，或者是递减，查询偏移起始位置是经过计算之后，带入到sql中的。如果数据量实在是太大了，如果达到千万级别的话建议还是使用分库分表。

13 mysql索引高频面试题

【1】Hash索引和B+树所有有什么区别或者说优劣呢?

首先要知道Hash索引和B+树索引的底层实现原理:

hash索引底层就是hash表,进行查找时,调用一次hash函数就可以获取到相应的键值,之后进行回表查询获得实际数据.B+树底层实现是多路平衡查找树.对于每一次的查询都是从根节点出发,查找到叶子节点方可以获得所查键值,然后根据查询判断是否需要回表查询数据.

那么可以看出他们有以下的不同:

hash索引进行等值查询更快(一般情况下),但是却无法进行范围查询.
因为在hash索引中经过hash函数建立索引之后,索引的顺序与原顺序无法保持一致,不能支持范围查询.而B+树的的所有节点皆遵循(左节点小于父节点,右节点大于父节点,多叉树也类似),天然支持范围.

hash索引不支持使用索引进行排序,原理同上.
hash索引不支持模糊查询以及多列索引的最左前缀匹配.原理也是因为hash函数的不可预测.
hash索引任何时候都避免不了回表查询数据,而B+树在符合某些条件(聚簇索引,覆盖索引等)的时候可以只通过索引完成查询.
hash索引虽然在等值查询上较快,但是不稳定.性能不可预测,当某个键值存在大量重复的时候,发生hash碰撞,此时效率可能极差.而B+树的查询效率比较稳定,对于所有的查询都是从根节点到叶子节点,且树的高度较低.
因此,在大多数情况下,直接选择B+树索引可以获得稳定且较好的查询速度.而不需要使用hash索引.

【2】在哪些情况下会发生针对该列创建了索引但是在查询的时候并没有使用呢(索引失效)?

1.列参与了运算(例如使用不等于查询!=，列参与了数学运算或者函数)
2.在字符串like时左边是通配符.类似于'%aaa'.
3.当mysql分析全表扫描比使用索引快的时候不使用索引.
4.当使用联合索引,前面一个条件为范围查询,后面的即使符合最左前缀原则,也无法使用索引.

【3】你知道mysql有哪些索引

1.Mysql中常用的索引有B+树索引(包括普通索引，唯一索引，主键索引 )、哈希索引，全文索引(通过查找文本中的关键词，类似于搜索引擎)，R-TREE索引(表示空间索引，myisam支持空间索引，空间索引主要用于地理空间数据类型，空间索引会从所有维度来索引数据。查询时，可以有效地使用任意维度来组合查询。很少使用)

2.Mysql 传统意义上的索引为B+ 树索引，B+ 树索引的本质就是B+ 树在数据库中的实现，数据库中的B+ 树的高一般为2-4层，因此查找某一键值的行记录只需2-4次IO，大概0.02~0.04秒。

【4】创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因?

MySQL提供了explain命令来查看语句的执行计划,MySQL在执行某个语句之前,会将该语句过一遍查询优化器,之后会拿到对语句的分析,也就是执行计划,其中包含了许多信息. 可以通过其中和索引有关的信息来分析是否命中了索引,例如possilbe_key,key,key_len等字段,分别说明了此语句可能会使用的索引,实际使用的索引以及使用的索引长度

【5】联合索引是什么?为什么需要注意联合索引中的顺序?

MySQL可以使用多个字段同时建立一个索引,叫做联合索引.在联合索引中,如果想要命中索引,需要按照建立索引时的字段顺序挨个使用,否则无法命中索引.

具体原因为:

MySQL使用索引时需要索引有序,假设现在建立了"name,age,school"的联合索引,那么索引的排序为: 先按照name排序,如果name相同,则按照age排序,如果age的值也相等,则按照school进行排序.

【6】非聚簇索引一定会回表查询吗?

不一定,这涉及到查询语句所要求的字段是否全部命中了索引,如果全部命中了索引,那么就不必再进行回表查询.

举个简单的例子,假设我们在员工表的年龄上建立了索引,那么当进行select age from employee where age < 20的查询时,在索引的叶子节点上,已经包含了age信息,不会再次进行回表查询.

后面的即使符合最左前缀原则,也无法使用索引.

【3】你知道mysql有哪些索引

~~~sql
1.Mysql中常用的索引有B+树索引(包括普通索引，唯一索引，主键索引 )、哈希索引，全文索引(通过查找文本中的关键词，类似于搜索引擎)，R-TREE索引(表示空间索引，myisam支持空间索引，空间索引主要用于地理空间数据类型，空间索引会从所有维度来索引数据。查询时，可以有效地使用任意维度来组合查询。很少使用)

2.Mysql 传统意义上的索引为B+ 树索引，B+ 树索引的本质就是B+ 树在数据库中的实现，数据库中的B+ 树的高一般为2-4层，因此查找某一键值的行记录只需2-4次IO，大概0.02~0.04秒。

【4】创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因?

MySQL提供了explain命令来查看语句的执行计划,MySQL在执行某个语句之前,会将该语句过一遍查询优化器,之后会拿到对语句的分析,也就是执行计划,其中包含了许多信息. 可以通过其中和索引有关的信息来分析是否命中了索引,例如possilbe_key,key,key_len等字段,分别说明了此语句可能会使用的索引,实际使用的索引以及使用的索引长度

【5】联合索引是什么?为什么需要注意联合索引中的顺序?

MySQL可以使用多个字段同时建立一个索引,叫做联合索引.在联合索引中,如果想要命中索引,需要按照建立索引时的字段顺序挨个使用,否则无法命中索引.

具体原因为:

MySQL使用索引时需要索引有序,假设现在建立了"name,age,school"的联合索引,那么索引的排序为: 先按照name排序,如果name相同,则按照age排序,如果age的值也相等,则按照school进行排序.

【6】非聚簇索引一定会回表查询吗?

不一定,这涉及到查询语句所要求的字段是否全部命中了索引,如果全部命中了索引,那么就不必再进行回表查询.

举个简单的例子,假设我们在员工表的年龄上建立了索引,那么当进行select age from employee where age < 20的查询时,在索引的叶子节点上,已经包含了age信息,不会再次进行回表查询.