- 回表：
通过普通索引找到主键，然后拿着主键回到主键索引树上搜索的过程。回表影响查询效率。
如何解决回表：
覆盖索引：
-
在一个查询中，使用的索引包含了我们的查询需求，我们称为覆盖索引。
-
覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。
-
联合索引可以实现索引覆盖
联合索引如果执行的语句是select ID from T where kbetween 3 and 5,这时只需要查ID的值, "而ID的值已经在k索引树上了,因此可以直接提供查询结果,不需要回表。也就是说,在这个查询里面,索引k已经"覆盖了"我们的查询需求,我们称为覆盖索引。
演示表的初始化语句：
CREATE TABLE tuser (
id int(11) NOT NULL,
id_card varchar(32) DEFAULT NULL,
name varchar(32) DEFAULT NULL,
age int(11) DEFAULT NULL,
ismale tinyint(1) DEFAULT NULL,
PRIMARY KEY (id),
KEY id_card (id_card),
KEY name_age (name,age)
) ENGINE=InnoDB
最左前缀原则查询时只要满足最左前缀,就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段,也可以是字符串索引的最左M个字符。
当我们执行 select * from tuser where name = “张三” 时可以使用到索引,
select * from tuser where name like “张%” 也能使用到索引
select * from tuser where name like “%三” 则不能使用到索引
select * from tuser where age = 18也不能使用到索引
普通索引与唯一索引 查询过程：select * from tuser where age = 18 and name = “张三” 理论上是不能使用联合索引,但是优化器进行优化后的sql语句是这样的: select * from tuser where name = “张三” and age = 18 这样的话就能使用联合索引了.
普通索引：查询条件满足第一条记录后，需要查找下一个记录直到碰到不满足条件的记录。
唯一索引：由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。
这个不同带来的性能差别是微乎其微的。
更新插入过程：情况一:这个记录要更新的目标页在内存中
唯一索引: 找到要插入的位置,判断插入的数据有没有冲突,没有冲突插入,语句执行结束.
普通索引: 找到位置插入这个值,语句执行结
《一线大厂Java面试题解析+后端开发学习笔记+最新架构讲解视频+实战项目源码讲义》
【docs.qq.com/doc/DSmxTbFJ1cmN1R2dB】 完整内容开源分享
束
这种情况普通索引和唯一索引对更新语句性能影响是微乎其微的.
情况二: 这个记录要更新的目标页不在内存中
唯一索引: 需要将数据页读到内存,判断到没有冲突,插入这个值,语句执行结束
普通索引: 将更新记录在change buffer,语句执行就结束了.
将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。
什么是change buffer
当需要更新一个数据页时,如果数据页在内存中就直接更新,而如果这个数据页还没有在内存中的话,在不影响数据一致性的前提下, InooDB会将这些更新操作缓存在change buffer 中,这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候,将数据页读入内存,然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。
change buffer 在内存中有拷贝，也会被写入到磁盘上。
如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。
将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。
change buffer的使用场景change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。
-
change buffer 只限于用在普通索引的场景下，而不适用于唯一索引。 这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，我建议你尽量选择普通索引。 唯一索引的优势也就是能保证数据的唯一性。在使用机械硬盘时，change buffer 这个机制的收效是非常显著的
-
因为 merge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。 因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。
-
一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用。
环境准备:
CREATE TABLE t (
id int(11) NOT NULL,
a int(11) DEFAULT NULL,
b int(11) DEFAULT NULL,
PRIMARY KEY (id),
KEY a (a),
KEY b (b)
) ENGINE=InnoDB；
然后，我们往表 t 中插入 10 万行记录，取值按整数递增，即：(1,1,1)，(2,2,2)，(3,3,3) 直到 (100000,100000,100000)。：
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into t values(i, i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
哪些情况可能使用不到索引:
查询条件使用函数;
隐式转换(实际上还是使用了函数):
字符型与数字型发生隐式转换时会造成索引失效;
隐式转换会把查询条件转换成数字的判断语句,即: 数字型 = 字符型, 字符型 = 数字型 都会转换成 数字型 = 数字型。
情况一: select * from where a = “11” ; 这种情况sql变成了: select * from where a = int(“11”)a字段没有变化所以可是使用索引
情况二: 假设我们的a字段是varchar型; select * from where a = 11,; 这种情况sql变成了: select * from where int(a) = 11,这个字段已经不是我们创建索引的字段了,所以这种情况下使用不到索引
详细的转换规则: https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html
我们要善于使用explain查看sql的执行情况:
使用explain查看sql语句执行情况
key: 用到的索引
如何给字符段加索引
前缀索引
演示表结构:
mysql> create table SUser(
ID bigint unsigned primary key,
email varchar(64),
…
)engine=innodb;
由于要使用邮箱登录，所以业务代码中一定会出现类似于这样的语句：
mysql> select f1, f2 from SUser where email=‘xxx’;
什么是前缀索引MySQL 是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。
mysql> alter table SUser add index index1(email);#完整索引
或
mysql> alter table SUser add index index2(email(6));#前缀索引
第一个语句创建的 index1 索引里面，包含了每个记录的整个字符串；而第二个语句创建的 index2 索引里面，对于每个记录都是只取前 6 个字节。
定义好合适的长度，就可以做到既节省空间，又不用额外增加太多的查询成本。
前缀索引对覆盖索引的影响select id,name,email from SUser where email=‘zhangssxyz@xxx.com’;
因为前缀索引没有存储字段的完整数据,不得不回表查询这个字段数据.
即使你将 index2 的定义修改为 email(18) 的前缀索引，这时候虽然 index2 已经包含了所有的信息，但 InnoDB 还是要回到 id 索引再查一下，因为系统并不确定前缀索引的定义是否截断了完整信息。
