1 MySQL索引
1.1 简介
1.1.1 索引创建
1.1.2 索引类型
MySQL索引种类有：普通索引、唯一索引(主键索引、唯一索引)、联合索引、全文索引、空间索引
MySQL中索引类型 :
-
FULLTEXT：即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX使用，不过目前只有CHAR、VARCHAR ，TEXT列上可以创建全文索引。 -
HASH：由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。
只有Memory存储引擎显式支持哈希索引 -
BTREE：BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。 -
RTREE：RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。相对于BTREE，RTREE的优势在于范围查找
1.1.3 索引优缺点
索引有哪些优缺点：
- 索引的优点
可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。 - 索引的缺点
时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
空间方面：索引需要占物理空间
1.2 SQL优化
1.2.1 查看执行计划
explain或desc这个命令来查看一个这些SQL语句的执行计划，就是为了分析耗时，是否走索引
查看SQL是否使用索引，前面加上explain或desc即可，在Oracle中是explain plan for命令查看索引执行计划，还得紧接着查询表才有结果select plan_table_output from TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
explain select * from emp where name = 'Jefabc'
expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra：
概要描述：
-
id:选择标识符 -
select_type:表示查询的类型。 -
table:输出结果集的表 -
partitions:匹配的分区 -
type:表示表的连接类型 -
possible_keys:表示查询时，可能使用的索引 -
key:表示实际使用的索引 -
key_len:索引字段的长度 -
ref:列与索引的比较 -
rows:扫描出的行数(估算的行数) -
filtered:按表条件过滤的行百分比 -
Extra:执行情况的描述和说明
type表示表的连接类型，由上至下，效率越来越高
-
ALL: 全表扫描 -
index: 索引全扫描 -
range: 索引范围扫描，常用语<,<=,>=,between,in等操作 -
ref使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中 -
eq_ref: 类似ref，区别在于使用的是唯一索引，使用主键的关联查询 -
const/system: 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询 -
null:MySQL不访问任何表或索引，直接返回结果
虽然上至下，效率越来越高，但是根据cost模型，假设有两个索引idx1(a, b, c),idx2(a, c)，SQL为"select * from t where a = 1 and b in (1, 2) order by c";如果走idx1，那么是type为range，如果走idx2，那么type是ref；当需要扫描的行数，使用idx2大约是idx1的5倍以上时，会用idx1，否则会用idx2
Extra:执行情况的描述和说明
-
Using filesort：MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。 -
Using temporary：使用了临时表保存中间结果，性能特别差，需要重点优化 -
Using index：表示相应的select操作中使用了覆盖索引（Coveing Index）,避免访问了表的数据行，效率不错！如果同时出现 using where，意味着无法直接通过索引查找来查询到符合条件的数据。 -
Using index condition：MySQL5.6之后新增的ICP，using index condtion就是使用了ICP（索引下推），在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据。
1.2.2 show profile分析
了解SQL执行的线程的状态及消耗的时。
默认是关闭的，开启语句set profiling = 1;
SHOW PROFILES ;
SHOW PROFILE FOR QUERY #{id};
1.2.3 trace
trace分析优化器如何选择执行计划，通过trace文件能够进一步了解为什么优惠券选择A执行计划而不选择B执行计划。
set optimizer_trace="enabled=on";
set optimizer_trace_max_mem_size=1000000;
select * from information_schema.optimizer_trace;
2 索引失效
2.1 问题引入
今天我来聊聊索引的相关问题，因为索引是大家都比较关心的公共话题，确实有很多坑。
不知道你在实际工作中，有没有遇到过下面的这两种情况：
- 明明在某个字段上加了索引，但实际上并没有生效
- 索引有时候生效了，有时候没有生效
在这里插入图片描述
2.2 准备工作
2.2.1 创建user表
创建一张user表，表中包含：id、code、age、name和height字段
CREATE TABLE `user` (
`id` int NOT NULL AUTO_INCREMENT,
`code` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
`age` int DEFAULT '0',
`name` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
`height` int DEFAULT '0',
`address` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_code_age_name` (`code`,`age`,`name`),
KEY `idx_height` (`height`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
此外，还创建了三个索引：
- id：数据库的主键
- idx_code_age_name：由code、age和name三个字段组成的联合索引。
- idx_height：普通索引
2.2.2 插入数据
为了方便给大家做演示，我特意向user表中插入了3条数据
INSERT INTO `user` (id, CODE, age, NAME, height,address) VALUES (1, '101', 21, '周星驰', 175,'香港');
INSERT INTO `user` (id, CODE, age, NAME, height,address) VALUES (2, '102', 18, '周杰伦', 173,'台湾');
INSERT INTO `user` (id, CODE, age, NAME, height,address) VALUES (3, '103', 23, '苏三', 174,'成都');
2.2.3 查看数据库版本
为了防止以后出现不必要的误会，在这里有必要查一下当前数据库的版本。
select version();
查出当前的mysql版本号为：8.0.21
2.3 联合索引
之前我已经给code、age和name这3个字段建好联合索引：idx_code_age_name。
该索引字段的顺序是：
- code
- age
- name
如果在使用联合索引时，没注意最左前缀原则，很有可能导致索引失效
2.3.1 联合索引中索引有效
explain select * from user where code='101';
explain select * from user where code='101' and age=21
explain select * from user where code='101' and age=21 and name='周星驰';
上面三种情况，sql都能正常走索引，但还有一种比较特殊的场景
explain select * from user where code = '101' and name='周星驰';
在这里插入图片描述
查询条件原本的顺序是：
code、age、name，但这里只有code和name中间断层了，掉了age字段，这种情况也能走code字段上的索引这4条sql中都有
code字段，它是索引字段中的第一个字段，也就是最左边的字段。只要有这个字段在，该sql已经就能走索引。这就是我们所说的
最左匹配原则
2.3.2 联合索引中索引失效
前面我已经介绍过，建立了联合索引后，在查询条件中有哪些情况索引是有效的。
接下来，我们重点看看哪些情况下索引会失效
explain select * from user where age=21;
explain select * from user where name='周星驰';
explain select * from user where age=21 and name='周星驰'
在这里插入图片描述
从图中看出这3种情况下索引确实失效了。
说明以上3种情况不满足最左匹配原则，说白了是因为查询条件中，没有包含给定字段最左边的索引字段，即字段code
2.4 select *
在《阿里巴巴开发手册》中明确说过，查询sql中禁止使用select *
那么，你知道为什么吗？
explain select * from user where name='苏三';
执行结果：
在这里插入图片描述
在该sql中用了
select *，从执行结果看，走了全表扫描，没有用到任何索引，查询效率是非常低的。如果查询的时候，只查我们真正需要的列，而不查所有列，结果会怎么样？
explain select code,name from user where name='苏三';
执行结果：
在这里插入图片描述
从图中执行结果不难看出，该
sql语句这次走了全索引扫描，比全表扫描效率更高。
其实这里用到了：覆盖索引
如果select语句中的查询列，都是索引列，那么这些列被称为覆盖索引。这种情况下，查询的相关字段都能走索引，索引查询效率相对来说更高一些。
而使用select *查询所有列的数据，大概率会查询非索引列的数据，非索引列不会走索引，查询效率非常低。
2.5 索引列上有计算
介绍本章节内容前，先跟大家一起回顾一下，根据id查询数据的sql语句：
explain select * from user where id=1;
执行结果：
在这里插入图片描述
从图中可以看出，由于
id字段是主键，该sql语句用到了主键索引。但如果id列上面有计算，比如：
explain select * from user where id+1=2;
执行结果：
在这里插入图片描述
从上图中的执行结果，能够非常清楚的看出，该id字段的主键索引，在有计算的情况下失效了。
2.6 索引列用了函数
有时候我们在某条sql语句的查询条件中，需要使用函数，比如：截取某个字段的长度。
假如现在有个需求：想查出所有身高是17开头的人，如果sql语句写成这样：
explain select * from user where height=17;
该sql语句确实用到了普通索引：
在这里插入图片描述
但该
sql语句肯定是有问题的，因为它只能查出身高正好等于17的，但对于174这种情况，它没办法查出来。
为了满足上面的要求，我们需要把sql语句稍稍改造了一下：
explain select * from user where SUBSTR(height,1,2)=17;
这时需要用到SUBSTR函数，用它截取了height字段的前面两位字符，从第一个字符开始。
执行结果：
在这里插入图片描述
在使用该函数之后，该sql语句竟然走了全表扫描，索引失效了。
2.7 字段类型不同
在sql语句中因为字段类型不同，而导致索引失效的问题，很容易遇到，可能是我们日常工作中最容易忽略的问题。
注意观察一下user表中的code字段，它是varchar字符类型的。
在sql语句中查询数据时，查询条件我们可以写成这样：
explain select * from user where code="101";
执行结果：
在这里插入图片描述
从上图中看到，该
code字段走了索引。温馨提醒一下，查询字符字段时，用双引号
“和单引号'都可以。MySQL中单双引号分析
但如果你在写sql时，不小心把引号弄掉了，把sql语句变成了：
explain select * from user where code=101;
执行结果：
在这里插入图片描述
你会发现，该
sql语句竟然变成了全表扫描。因为少写了引号，这种小小的失误，竟然让code字段上的索引失效了。
为什么索引会失效呢？
因为code字段的类型是varchar，而传参的类型是int，两种类型不同。
此外，还有一个有趣的现象，如果int类型的height字段，在查询时加了引号条件，却还可以走索引：
explain select * from user where height='175';
执行结果：
在这里插入图片描述
从图中看出该sql语句确实走了索引。int类型的参数，不管在查询时加没加引号，都能走索引。
mysql发现如果是int类型字段作为查询条件时，它会自动将该字段的传参进行隐式转换，把字符串转换成int类型。
mysql会把上面列子中的字符串175，转换成数字175，所以仍然能走索引。
接下来，看一个更有趣的sql语句：
select 1 + '1';
结果是2。
mysql自动把字符串1，转换成了int类型的1，然后变成了：1+1=2
但如果你确实想拼接字符串该怎么办？可以使用concat关键字。
具体拼接sql如下：
select concat(1,'1');
接下来，关键问题来了：为什么字符串类型的字段，传入了int类型的参数时索引会失效呢？
根据mysql官网上解释，字符串'1'、' 1 '、'1a'都能转换成int类型的1，也就是说可能会出现多个字符串，对应一个int类型参数的情况。那么，mysql怎么知道该把int类型的1转换成哪种字符串，用哪个索引快速查值?
感兴趣的小伙伴可以再看看官方文档：https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html
2.8 like左边包含%
模糊查询，在我们日常的工作中，使用频率还是比较高的。
比如现在有个需求：想查询姓李的同学有哪些?
使用like语句可以很快的实现：
select * from user where name like '李%';
但如果like用的不好，就可能会出现性能问题，因为有时候它的索引会失效。
目前like查询主要有三种情况：
like '%a'
like 'a%'
like '%a%'
假如现在有个需求：想查出所有code是10开头的用户。sql语句如下：
explain select * from user where code like '10%';
执行结果：
在这里插入图片描述
图中看出这种
%在10右边时走了索引。而如果把需求改了：想出现出所有code是1结尾的用户。
查询sql语句改为：
explain select * from user where code like '%1';
执行结果：
在这里插入图片描述
从图中看出这种
%在1左边时，code字段上索引失效了，该sql变成了全表扫描。
此外，如果出现以下sql：
explain select * from user where code like '%1%';
该sql语句的索引也会失效。
下面用一句话总结一下规律：当like语句中的%，出现在查询条件的左边时，索引会失效。
为什么会出现这种现象呢？
其实很好理解，索引就像字典中的目录。一般目录是按字母或者拼音从小到大，从左到右排序，是有顺序的。
我们在查目录时，通常会先从左边第一个字母进行匹对，如果相同，再匹对左边第二个字母，如果再相同匹对其他的字母，以此类推。通过这种方式我们能快速锁定一个具体的目录，或者缩小目录的范围。
2.9 列对比
上面的内容都是常规需求，接下来，来点不一样的。
假如我们现在有这样一个需求：过滤出表中某两列值相同的记录。比如user表中id字段和height字段，查询出这两个字段中值相同的记录。
这个需求很简单，sql可以这样写：
explain select * from user where id=height
执行结果：
在这里插入图片描述
为什么会出现索引失效这种结果
id字段本身是有主键索引的，同时height字段也建了普通索引的，并且两个字段都是int类型，类型是一样的。
但如果把两个单独建了索引的列，用来做列对比时索引会失效
2.10 使用or关键字
我们平时在写查询sql时，使用or关键字的场景非常多，但如果你稍不注意，就可能让已有的索引失效。
某天你遇到这样一个需求：想查一下id=1或者height=175的用户。
explain select * from user where id=1 or height='175';
执行结果：
在这里插入图片描述
没错，这次确实走了索引，因为刚好id和height字段都建了索引。
假如需求改了：除了前面的查询条件之后，还想加一个address='成都'。
explain select * from user where id=1 or height='175' or address='成都';
执行结果：
在这里插入图片描述
结果悲剧了，之前的索引都失效了。
因为你最后加的
address字段没有加索引，从而导致其他字段的索引都失效了。
注意：如果使用了or关键字，那么它前面和后面的字段都要加索引，不然所有的索引都会失效，这是一个大坑。
2.11 not in和not exists
在我们日常工作中用得也比较多的，还有范围查询，常见的有：
- in
- exists
- not in
- not exists
- between and
今天重点聊聊前面四种。
2.11.1 in关键字
假如我们想查出height在某些范围之内的用户，这时sql语句可以这样写：
explain select * from user where height in (173,174,175,176);
执行结果：
在这里插入图片描述
从图中可以看出，
sql语句中用in关键字是走了索引的。
如果使用了in，即使后面的条件加了索引，还是要注意in后面的元素不要过多哈。in元素一般建议不要超过500个，如果超过了，建议分组，每次500一组进行
2.11.1.1 delete + in子查询不走索引
之前见到过一个生产慢SQL问题，当delete遇到in子查询时，即使有索引，也是不走索引的。而对应的select + in子查询，却可以走索引。
当时的MySQL版本是5.7，假设当前有两张表account和old_account,表结构如下
CREATE TABLE `old_account` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
`name` varchar(255) DEFAULT NULL COMMENT '账户名',
`balance` int(11) DEFAULT NULL COMMENT '余额',
`create_time` datetime NOT NULL COMMENT '创建时间',
`update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='老的账户表';
CREATE TABLE `account` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
`name` varchar(255) DEFAULT NULL COMMENT '账户名',
`balance` int(11) DEFAULT NULL COMMENT '余额',
`create_time` datetime NOT NULL COMMENT '创建时间',
`update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';
执行的SQL如下：
delete from account where name in (select name from old_account);
查看执行计划，发现不走索引：
image.png
但是如果把delete换成select，就会走索引。如下：
image.png
为什么select + in子查询会走索引，delete + in子查询却不会走索引呢？
我们执行以下SQL看看：
explain select * from account where name in (select name from old_account);
show WARNINGS; //可以查看优化后,最终执行的sql
结果如下：
select `test2`.`account`.`id` AS `id`,`test2`.`account`.`name` AS `name`,`test2`.`account`.`balance` AS `balance`,`test2`.`account`.`create_time` AS `create_time`,`test2`.`account`.`update_time` AS `update_time` from `test2`.`account`
semi join (`test2`.`old_account`)
where (`test2`.`account`.`name` = `test2`.`old_account`.`name`)
可以发现，实际执行的时候，MySQL对select in子查询做了优化，把子查询改成join的方式，所以可以走索引。但是很遗憾，对于delete in子查询，MySQL却没有对它做这个优化
2.11.2 exists关键字
有时候使用in关键字时性能不好，这时就能用exists关键字优化sql了，该关键字能达到in关键字相同的效果：
explain select * from user t1
where exists (select 1 from user t2 where t2.height=173 and t1.id=t2.id)
执行结果：
在这里插入图片描述
从图中可以看出，用
exists关键字同样走了索引。
2.11.3 not in关键字
上面演示的两个例子是正向的范围，即在某些范围之内。
那么反向的范围，即不在某些范围之内，能走索引不？
explain select * from user where height not in (173,174,175,176);
执行结果：
在这里插入图片描述
索引失效了。
看如果现在需求改了：想查一下
id不等于1、2、3的用户有哪些，这时sql语句可以改成这样：
explain select * from user where id not in (173,174,175,176);
执行结果：
在这里插入图片描述
可能会惊奇的发现，主键字段中使用
not in关键字查询数据范围，任然可以走索引。而普通索引字段使用了not in关键字查询数据范围，索引会失效。
2.11.4 not exists关键字
除此之外，如果sql语句中使用not exists时，索引也会失效。具体sql语句如下：
explain select * from user t1
where not exists (select 1 from user t2 where t2.height=173 and t1.id=t2.id)
执行结果：
在这里插入图片描述
从图中看出
sql语句中使用not exists关键后，t1表走了全表扫描，并没有走索引。
2.12 order by的坑
在sql语句中，对查询结果进行排序是非常常见的需求，一般情况下我们用关键字：order by就能搞定。
但order by挺难用的，它跟where或者limit关键字有很多千丝万缕的联系，一不小心就会出问题。
2.12.1 哪些情况走索引
一起看看order by的哪些情况可以走索引。
我之前说过，在code、age和name这3个字段上，已经建了联合索引：idx_code_age_name
2.12.1.1 配合where一起使用
order by还能配合where一起遵循最左匹配原则。
explain select * from user where code='101' order by age;
执行结果：
在这里插入图片描述
code是联合索引的第一个字段，在where中使用了，而age是联合索引的第二个字段，在order by中接着使用。
假如中间断层了，sql语句变成这样，执行结果会是什么呢？
explain select * from user where code='101' order by name;
执行结果：
在这里插入图片描述
虽说
name是联合索引的第三个字段，但根据最左匹配原则，该sql语句依然能走索引，因为最左边的第一个字段code，在where中使用了。只不过order by的时候，排序效率比较低，需要走一次filesort排序罢了。
2.12.1.2 两者都有
如果某个联合索引字段，在where和order by中都有，结果会怎么样？
explain select * from user where code='101' order by code, name;
执行结果：
在这里插入图片描述
code字段在where和order by中都有，对于这种情况，从图中的结果看出，还是能走了索引的。
2.12.2 哪些情况不走索引
前面介绍的都是正面的用法，是为了让大家更容易接受下面反面的用法。
好了，接下来，重点聊聊order by的哪些情况下不走索引？
2.12.2.1 没加where
如果order by语句中没有加where关键字，该sql语句将不会走索引。
explain select * from user order by code, name;
执行结果：
在这里插入图片描述
从图中看出索引真的失效了。
2.12.2.2 只用limit
只是用limit也不走索引
order by后面的条件，也要遵循联合索引的最左匹配原则。具体有以下sql：
explain select * from user order by code limit 100;
explain select * from user order by code,age limit 100;
explain select * from user order by code,age,name limit 100;
执行结果：
在这里插入图片描述
从图中看出这3条sql都不能正常走索引
转载于：https://mp.weixin.qq.com/s/rjIT8LJo_ie8YWceoWBySA
2.12.3 order by文件排序效率很低
如果查看explain执行计划的时候，可以看到Extra这一列，有一个Using filesort，它表示用到文件排序
select name,age,city from staff where city = '深圳' order by age limit 10;
查询前10个，来自深圳员工的姓名、年龄、城市，并且按照年龄小到大排序。
image.png
order by用到文件排序时，为什么查询效率会相对低呢
image.png
order by排序，分为全字段排序和rowid排序。它是拿max_length_for_sort_data和结果行数据长度对比，如果结果行数据长度超过max_length_for_sort_data这个值，就会走rowid排序，相反，则走全字段排序
2.12.3.1 rowid排序
rowid排序，一般需要回表去找满足条件的数据，所以效率会慢一点。以下这个SQL，使用rowid排序，执行过程是这样
select name,age,city from staff where city = '深圳' order by age limit 10;
-
MySQL为对应的线程初始化sort_buffer，放入需要排序的age字段，以及主键id； - 从索引树
idx_city， 找到第一个满足city='深圳’条件的主键id - 到主键id索引树拿到id=9的这一行数据， 取age和主键id的值，存到
sort_buffer； - 从索引树
idx_city拿到下一个记录的主键id，即图中的id=13； - 重复步骤 3、4 直到city的值不等于深圳为止；
- 前面5步已经查找到了所有city为深圳的数据，在
sort_buffer中，将所有数据根据age进行排序； -
遍历排序结果，取前10行，并按照id的值回到原表中，取出city、name 和 age三个字段返回给客户端
image.png
2.12.3.2 全字段排序
同样的SQL，如果是走全字段排序是这样的
select name,age,city from staff where city = '深圳' order by age limit 10;
-
MySQL为对应的线程初始化sort_buffer，放入需要查询的name、age、city字段； - 从索引树idx_city， 找到第一个满足 city='深圳’条件的主键 id
- 到主键id索引树拿到id=9的这一行数据， 取name、age、city三个字段的值，存到
sort_buffer； - 从索引树idx_city 拿到下一个记录的主键id，即图中的id=13；
- 重复步骤 3、4 直到city的值不等于深圳为止；
- 前面5步已经查找到了所有city为深圳的数据，在sort_buffer中，将所有数据根据age进行排序；
-
按照排序结果取前10行返回给客户端
image.png
sort_buffer的大小是由一个参数控制的：sort_buffer_size
- 如果要排序的数据小于
sort_buffer_size，排序在sort_buffer内存中完成 - 如果要排序的数据大于
sort_buffer_size，则借助磁盘文件来进行排序。
借助磁盘文件排序的话，效率就更慢一点。因为先把数据放入sort_buffer，当快要满时。会排一下序，然后把sort_buffer中的数据，放到临时磁盘文件，等到所有满足条件数据都查完排完，再用归并算法把磁盘的临时排好序的小文件，合并成一个有序的大文件。
2.13 左右连接问题
2.13.1 join或者子查询过多
一般来说，不建议使用子查询，可以把子查询改成join来优化。而数据库有个规范约定就是：尽量不要有超过3个以上的表连接。为什么要这么建议呢?
MySQL中，join的执行算法，分别是：Index Nested-Loop Join和Block Nested-Loop Join
-
Index Nested-Loop Join：这个join算法，跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引。 -
Block Nested-Loop Join：这种join算法，被驱动表上没有可用的索引,它会先把驱动表的数据读入线程内存join_buffer中，再扫描被驱动表，把被驱动表的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回。
join过多的问题：
一方面，过多的表连接，会大大增加
SQL复杂度。另外一方面，如果可以使用被驱动表的索引那还好，并且使用小表来做驱动表，查询效率更佳。如果被驱动表没有可用的索引，join是在join_buffer内存做的，如果匹配的数据量比较小或者join_buffer设置的比较大，速度也不会太慢。但是，如果join的数据量比较大时，mysql会采用在硬盘上创建临时表的方式进行多张表的关联匹配，这种显然效率就极低，本来磁盘的 IO 就不快，还要关联。
一般情况下，如果业务需要的话，关联2~3个表是可以接受的，但是关联的字段需要加索引。如果需要关联更多的表，建议从代码层面进行拆分，在业务层先查询一张表的数据，然后以关联字段作为条件查询关联表形成map，然后在业务层进行数据的拼装
2.13.2 关联的字段编码格式不一样
新建两个表，一个user，一个user_job
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
CREATE TABLE `user_job` (
`id` int(11) NOT NULL,
`userId` int(11) NOT NULL,
`job` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
user表的name字段编码是utf8mb4，而user_job表的name字段编码为utf8
image.png
image.png
执行左外连接查询,user_job表还是走全表扫描，如下：
image.png
如果把它们的name字段改为编码一致，相同的SQL，还是会走索引
image.png
在做表关联时，注意一下关联字段的编码问题
2.14 优化器选错了索引
MySQL 中一张表是可以支持多个索引的。你写SQL语句的时候，没有主动指定使用哪个索引的话，用哪个索引是由MySQL来确定的。
我们日常开发中，不断地删除历史数据和新增数据的场景，有可能会导致MySQL选错索引。那么有哪些解决方案呢：
- 使用
force index强行选择某个索引 - 修改你的
SQl，引导它使用我们期望的索引 - 优化你的业务逻辑
- 优化你的索引，新建一个更合适的索引，或者删除误用的索引
3 索引数据结构
3.1 索引数据结构介绍
索引是一种数据结构，可以帮助我们快速的进行数据的查找
索引的数据结构和具体存储引擎的实现有关，在 MySQL 中使用较多的索引有 Hash 索引，B+ 树索引等，而我们经常使用的 InnoDB 存储引擎的默认索引实现为：B+ 树索引
那么为什么使用索引：
- 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
- 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
- 帮助服务器避免排序和临时表。
- 将随机IO变为顺序IO。
- 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义
3.2 索引底层实现
3.2.1 Hash索引
基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），并且Hash索引将所有的哈希码存储在索引中，同时在索引表中保存指向每个数据行的指针
image.png
3.2.2 B-Tree索引(MySQL使用B+Tree)
B-Tree能加快数据的访问速度，因为存储引擎不再需要进行全表扫描来获取数据，数据分布在各个节点之中。
image.png
3.2.3 B+Tree索引
B+Tree索引是B-Tree的改进版本，同时也是数据库索引所采用的存储结构。数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说，进行范围查找时只需要查找两个节点，进行遍历即可。而B-Tree需要获取所有节点，相比之下B+Tree效率更高
3.2.3.1 B+Tree性质
n棵子tree的节点包含n个关键字，不用来保存数据而是保存数据的索引。
所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
所有的非终端结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字。
B+ 树中，数据对象的插入和删除仅在叶节点上进行。
B+树有2个头指针，一个是树的根节点，一个是最小关键码的叶节点。
image.png
3.2.3.2 一棵B+树可以存多少数据量
大家是否还记得，一个B+树大概可以存放多少数据量呢？
InnoDB存储引擎最小储存单元是页，一页大小就是16k。
B+树叶子存的是数据，内部节点存的是键值+指针。索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而再去数据页中找到需要的数据；
image.png
假设B+树的高度为2的话，即有一个根结点和若干个叶子结点。这棵B+树的存放总记录数=根结点指针数*单个叶子节点记录行数。
如果一行记录的数据大小为1k，那么单个叶子节点可以存的记录数 =16k/1k =16
非叶子节点内存放多少指针呢？我们假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，所以就是8+6=14字节，16k/14B =16*1024B/14B = 1170
因此，一棵高度为2的B+树，能存放1170 * 16=18720条这样的数据记录。同理一棵高度为3的B+树，能存放1170 *1170 *16 =21902400，也就是说，可以存放两千万左右的记录。B+树高度一般为1-3层，已经满足千万级别的数据存储。
如果B+树想存储更多的数据，那树结构层级就会更高，查询一条数据时，需要经历的磁盘IO变多，因此查询性能变慢。
3.2.4 Hash索引和B+树索引区别
首先要知道 Hash 索引和 B+ 树索引的底层实现原理：
-
hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。 -
B+ 树底层实现是多路平衡查找树。对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。
那么可以看出他们有以下的不同：
-
hash索引进行等值查询更快(一般情况下)，但是却无法进行范围查询。
因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围。 -
hash索引不支持使用索引进行排序，原理同上。 -
hash索引不支持模糊查询以及多列索引的最左前缀匹配。原理也是因为hash函数的不可预测。 -
hash索引任何时候都避免不了回表查询数据,而B+树在符合某些条件(聚簇索引,覆盖索引等)的时候可以只通过索引完成查询。 -
hash索引虽然在等值查询上较快，但是不稳定。性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。
因此，在大多数情况下，直接选择 B+ 树索引可以获得稳定且较好的查询速度。而不需要使用 hash索引
3.2.5 MyISAM和InnoDB实现B+树索引方式区别
MyISAM，B+Tree叶节点的data域存放的是数据记录的地址，在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的key存在，则取出其data域的值，然后以data域的值为地址读取相应的数据记录，这被称为非聚簇索引
InnoDB，其数据文件本身就是索引文件，相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的节点data域保存了完整的数据记录，这个索引的key是数据表的主键。
因此，InnoDB表数据文件本身就是主索引，这被称为聚簇索引或者聚集索引，而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。
在根据主键索引搜索时，直接找到key所在的节点即可取出数据；根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。
因此，在设计表的时候，不建议使用过长的字段为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。
总结：
InnoDB 主键索引使用的是聚簇索引，MyISAM 不管是主键索引，还是二级索引使用的都是非聚簇索引
3.2.6 Mysql用B+树做索引而不用B-树或红黑树、二叉树
主要原因：B+树只要遍历叶子节点就可以实现整棵树的遍历，而且在数据库中基于范围的查询是非常频繁的，而B树只能中序遍历所有节点，效率太低。
-
B+树
B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B(B-)树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。
由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。 -
Hash
虽然可以快速定位，但是没有顺序，IO复杂度高；
基于Hash表实现，只有Memory存储引擎显式支持哈希索引 ；
适合等值查询，如=、in()、<=>，不支持范围查询；
因为不是按照索引值顺序存储的，就不能像B+Tree索引一样利用索引完成排序 ；
Hash索引在查询等值时非常快 ；
因为Hash索引始终索引的所有列的全部内容，所以不支持部分索引列的匹配查找 ；
如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题 -
二叉树
树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高 -
红黑树
树的高度随着数据量增加而增加，IO代价高
3.3 聚簇索引和非聚簇索引
3.3.1 聚簇索引
一种索引，该索引中键值的逻辑顺序决定了表中相应的物理顺序。
聚集索引确定表中数据的物理顺序。由于聚集索引规定数据在表中的物理存储顺序，因此 一个表只能包含一个聚集索引 。但该索引可以包含多个列（组合索引）
聚簇索引就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的就是整张表的行记录数据。
在 InnoDB 中，只有 主键索引是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引。如果没有唯一键，则MySQL自动为InnoDB表生成一个隐含字段来建立聚簇索引，这个字段长度为6个字节，类型为长整形。
当查询使用聚簇索引时，在对应的叶子节点，可以获取到整行数据，因此 不用再次进行回表查询
聚集索引图示
聚集索引的叶节点就是数据节点
image.png
3.3.2 非聚簇索引
一种索引，该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。
索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块
image.png
聚簇索引和非聚簇索引的区别：
-
聚簇索引的叶子节点存放的是主键值和数据行，支持覆盖索引。 -
非聚簇索引的叶子节点存放的是主键值或数据记录的地址(InnoDB辅助索引的data域存储相应记录主键的值，MyISAM辅助索引的data域保存数据记录的地址)
3.3.4 非聚簇索引一定会回表查询吗
非聚簇索引一定会回表查询吗
不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。
举个简单的例子，假设我们在员工表的年龄上建立了索引，那么当进行select age from employee where age < 20的查询时，在索引的叶子节点上，已经包含了age信息，不会再次进行回表查询
3.4 覆盖索引
如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称 之为覆盖索引
我们知道在InnoDB存储引 擎中，如果不是主键索引，叶子节点存储的是主键值。最终还是要回表，也就是要通过主键再查找一次,这样就 会比较慢。覆盖索引就是把要查询出的列和索引是对应的，不做回表操作
网友评论