什么是索引？
提到索引， 我们想到的是查询慢了 设置索引呗！但是索引为什么起作用？设置了索引为什么还是慢我们其实不是很清楚。
在关系数据库中，索引是一种数据结构，他将数据提前按照一定的规则进行排序和组织， 能够帮助快速定位到数据记录的数据，加快数据库表中数据的查找和访问速度。
像书籍的目录、文件夹、标签 、房号.... 都可以帮助我们快速定位，都可以视为索引。
能实现快速定位数据的一种存储结构，其设计思想是以空间换时间。
在MySQL中索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。常见的索引分类如下：
特点：每个节点最多有两个子节,大在左，小在右 ，数据随机性情况下树杈越明显。
如果数据是按顺序依次进入：
树的高度则会很高（就是一个链表结构）， 此时元素的查找效率就等于链表查询O(n)，数据检索效率将极为低下。
极端的情况下 就是一个链表结构（如下图），此时元素的查找效率就等于链表查询O(n)。
虽通过自旋平衡，子节点会自动分叉为2个分支，从而减少树的高度，当数据有序插入时比二叉树数据检索性能更佳.
但是如果 数据量过大，节点个数就越多，树高度也会增高（也就是树的深度越深），增加磁盘I/O次数，影响查询效率。
B树的出现可以解决树高度的问题。之所以是B树，而并不是名称中"xxx二叉树"，就是它不再限制一个父节点中只能有两个子节点，而是允许 M 个子节点（M > 2）。不仅如此，B树的一个节点可以存储多个元素，相比较于前面的那些二叉树数据结构又将整体的树高度降低了。
B 树的节点可以包含有多个字节点，所以 B树是一棵多叉树，它的每一个节点包含的最多子节点数量的称为B树的阶。如下图是一颗3阶的B树。
当一颗3阶的B树查找 7这个的元素时的流程是怎么样的？
先从根节点出发 ，判断 7在4和8之间，根据P2存储指针6的节点，判断7大于6 最后指针找到叶子节点。也就找到有匹配7的键值。
可以发现一颗3阶的B树在查找叶子节点时，由于树高度只有 3，所以查找过程最多只需要3次的磁盘I/O操作。
数据量不大时可能不太真切。但当数据量大时，节点也会随着增多；此时如果还是前面的自平衡二叉树的场景下，由于二叉树只能最多2个叶子节点的约束，也只能纵向去的去扩展子节点，树的高度会很高，意味着需要更多的操作磁盘I/O次数。而B树则可以通过横向扩展节点从而降低树的高度，所以效率自然要比二叉树效率更高。（直白说就是变矮胖了）
看到这，相信你也知道如果B树这么适合，也就没有接下来B+树的什么事了。
接着，那为什么不用B树，而用了B+树呢？
你看啊，B树其实已经满足了我们最前面所要满足的条件，减少磁盘I/O操作，同时支持按区间查找。但注意，虽然B树支持按区间查找，但并不高效。例如上面的例子中，B树能高效的通过等值查询 15 这个值，但不方便查询出一个区间内3 ~ 10区间内所有数的结果。因为当B树做范围查询时需要使用中序遍历，那么父节点和子节点也就需要不断的来回切换涉及了多个节点会给磁盘I/O带来很多负担。
在MySQL中为什么会选用B+tree做索引结构呢？
B+tree 是在B树基础上的一种优化，其更适合做存储索引结构。在 B+tree 中，非叶子节点上仅存储键值，不存储数据；而所有数据记录均存储在叶子节点上，并且数据是按照顺序排列的。此外在 B+tree 中各个数据页之间是通过双向链表连接的。B+tree 的结构图如下：
B+tree 结构实现数据索引具有如下优点：
a. 非叶子节点上可以存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树也就会变得更矮更胖。这样一来我们查找数据进行磁盘I/O的次数就会大大减少，数据查询的效率也会更快。
b. 所有数据记录都有序存储在叶子节点上，就会使得范围查找，排序查找，分组查找以及去重查找变得异常简单。
c. 数据页之间、数据记录之间都是通过链表链接的，有了这个结构的支持就可以方便的在数据查询后进行升序或者降序操作。
Hash索引其实用的不多，最主要是因为最常见的存储引擎InnoDB不支持显示地创建Hash索引，只支持自适应Hash索引。
虽然可以使用sql语句在InnoDB显示声明Hash索引，但是其实是不生效的
对name字段建立Hash索引，但是通过show index from 表名就会发现实际还是B+树
在存储引擎中，Memory引擎支持Hash索引
Hash索引其实有点像JAVA中的HashMap底层的数据结构，他也有很多的槽，存的也是键值对，键值为索引列，值为数据的这条数据的行指针，通过行指针就可以找到数据
假设现在user表用Memory存储引擎，对name字段建立Hash索引，表中插入三条数据
Hash索引会对索引列name的值进行Hash计算，然后找到对应的槽下面，如下图所示
当遇到name字段的Hash值相同时，也就是Hash冲突，就会形成一个链表，比如有name=张三有两条数据，就会形成一个链表。
之后如果要查name=李四的数据，只需要对李四进行Hash计算，找到对应的槽，遍历链表，取出name=李四对应的行指针，然后根据行指针去查找对应的数据。
Hash索引优缺点
按物理存储分类：InnoDB的存储方式是聚集索引，MyISAM的存储方式是非聚集索引。
在MySQL中，创建一张表时会默认为主键创建聚簇索引，B+树将表中所有的数据组织起来，即数据就是索引主键所以在InnoDB里，主键索引也被称为聚簇索引，索引的叶子节点存的是整行数据。而除了聚簇索引以外的所有索引都称为二级索引，二级索引的叶子节点内容是主键的值。
例如创建如下一张表:
CREATE TABLE users(
id INT NOT NULL,
name VARCHAR(20) NOT NULL,
age INT NOT NULL,
PRIMARY KEY(id)
);
新建一个以age字段的二级索引:
ALTER TABLE users ADD INDEX index_age(age);
MySQL会分别创建主键id的聚簇索引和age的二级索引:
在MySQL中主键索引的叶子节点存的是整行数据，而二级索引叶子节点内容是主键的值.
讲完二级索引，接下来讲一讲如何使用二级索引查找数据。
这里假设对name字段创建了一个索引，并且表里就存了上面示例中的几条数据，这里我再把图拿过来
例如执行下面这条sql 则需要进行回表:
SELECT * FROM users WHERE age=35;
由于查询条件是name = '赵六'，所以会走name索引
整个过程大致分为以下几个步骤：
上一节说当执行select * from `user` where age = 35;这条sql的时候，会先从索引页中查出来age = 35;对应的主键id，之后再回表，到聚簇索引中查询其它字段的值。
那么当执行下面这条sql，又会怎样呢？
select id from `user` where age = 35;
这次查询字段从select *变成select id，查询条件不变，所以也会走age索引
所以还是跟前面一样了，先从索引页中查出来age = 35;对应的主键id之后，惊讶的发现，sql中需要查询字段的id值已经查到了，那次此时压根就不需要回表了，已经查到id了，还回什么表。
而这种需要查询的字段都在索引列中的情况就被称为覆盖索引，索引列覆盖了查询字段的意思。
当使用覆盖索引时会减少回表的次数，这样查询速度更快，性能更高。
所以，在日常开发中，尽量不要select * ，需要什么查什么，如果出现覆盖索引的情况，查询会快很多。
ALTER TABLE `test`.`user` ADD INDEX(`name`);
假设，我们现在对name字段加了一个普通非唯一索引，那么name就是索引列，同时name这个索引也就是单列索引
此时如果往表中插入三条数据，那么name索引的叶子节点存的数据就如下图所示
mysql会根据name字段的值进行排序，这里我假设张三排在李四前面，当索引列的值相同时，就会根据id排序，所以索引实际上已经根据索引列的值排好序了。
这里肯定有小伙伴疑问，name字段存储的中文也可以排序么？
答案是可以的，并且mysql支持很多种排序规则，我们在建数据库或者是建表的时候等都可以指定排序规则，并且后面文章涉及到的字符串排序都是我随便排的，实际情况可能不一样。
对于单个索引列数据查找也是跟前面说的聚簇索引一样，也会对数据分组，之后可以根据二分查找在单个索引列来查找数据。
当数据不断增多，一个索引页存储不下数据的时候，也会用多个索引页来存储，并且索引页直接也会形成双向链表
当索引页不断增多是，为了方便在不同索引页中查找数据，也就会抽取一个索引页，除了存页中id，同时也会存储这个id对应的索引列的值
当数据越来越多越来越多，还会抽取，也会形成三层的一个B+树，这里我就不画了。
ALTER TABLE `test`.`user` ADD INDEX(`name`, `age`, `id`);
除了单列索引，联合索引其实也是一样的，只不过索引页存的数据就多了一些索引列
比如，在name和age上建立一个联合索引，此时单个索引页就如图所示
先以name排序，name相同时再以age排序，如果再有其它列，依次类推，最后再以id排序。
相比于只有name一个字段的索引来说，索引页就多存了一个索引列。
最后形成的B+树简化为如下图
顾名思义是最左优先，以最左边的为起点任何连续的索引都能匹配上。
如果没有第一列的话，直接访问第二列，那第二列肯定是无序的，直接访问后面的列就用不到索引了
当创建(a,b,c)复合索引时，想要索引生效的话，只能使用 a和ab、ac和abc三种组合！
建一个联合索引(a,b,c),实际相当于建了(a),(a,b),(a,b,c)三个索引.每多一个索引,都会增加写操作的开销和磁盘空间的开销.对于大量数据的表,使用联合索引会大大的减少开销!
对联合索引(a,b,c),如果有如下sql的
select a,b,c from table where a='xxx' and b='xx';
那么mysql可以直接通过遍历索引取得数据,而无需回表,这减少了很多的随机io操作.减少io操作,特别是随机io其实DBA主要的优化策略.所以,在真正的实际应用中,覆盖索引是主要的提升性能的优化手段之一.
索引列多,通过联合索引筛选出的数据越少.比如有1000w条数据的表,有如下sql:
select col1,col2,col3 from table where col1=1 and col2=2 and col3=3;
假设:假设每个条件可以筛选出10%的数据
A:如果只有单列索引,那么通过该索引能筛选出1000w*10%=100w条数据,然后再回表从100w调数据中找到符合col2=2 and col3=3的数据,然后再排序,再分页,以此类推(递归);
B:如果是(col1,col2,col3)联合索引,通过三列索引筛选出1000w*10%*10%*10%=1w,效率提升可想
索引下推（INDEX CONDITION PUSHDOWN，简称 ICP）是在 MySQL 5.6 针对扫描二级索引的一项优化改进。 用来在范围查询时减少回表的次数 。ICP 适用于 MYISAM 和 INNODB。
ALTER TABLE `test`.`user` ADD INDEX (`name`,`age`)
不使用索引下推实现
ExplAIn SELECT * FROM user1 WHERE name LIKE 'A%' and age = 40;
使用索引下推实现
Explain SELECT * FROM user1 WHERE name LIKE 'A%' and age = 40;
接下来要执行如下的sql
select * from `user` where name > '王五' and age > 22;
在MySQL5.6(不包括5.6)之前，整个sql大致执行步骤如下：
所以对于图上所示，整个搜索过程会经历5次回表操作，两个赵六，两个刘七，一个王九，最后符合条件的也就是id=6的赵六那条数据，其余age不符和。
虽然这么执行没什么问题，但是不知有没有发现其实没必要进行那么多次回表，因为光从上面的索引图示就可以看出，符合name > '王五' and age > 22的数据就id=6的赵六那条数据
所以在MySQL5.6之后，对上面的age > 22判断逻辑进行了优化
前面还是一样，定位查找到id=4的那个赵六，之后就不回表来判断age了，因为索引列有age的值了，那么直接根据索引中age判断是否大于22，如果大于的话，再回表查询剩余的字段数据（因为是select *），然后再顺序链表遍历，直至结束
所以这样优化之后，回表次数就成1了，相比于前面的5次，大大减少了回表的次数。
而这个优化，就被称为索引下推，就是为了减少回表的次数。
之所以这个优化叫索引下推，其实是跟判断age > 22逻辑执行的地方有关，这里就不过多赘述了。
索引合并（index merge）是从MySQL5.1开始引入的索引优化机制，在之前的MySQL版本中，一条sql多个查询条件只能使用一个索引，但是引入了索引合并机制之后，MySQL在某些特殊的情况下会扫描多个索引，然后将扫描结果进行合并
结果合并会为下面三种情况：
为了不耽误演示，删除之前所有的索引，然后为name和age各自分别创建一个二级索引idx_name和idx_age
当执行下面这条sql就会出现取交集的情况
select * from `user` where name = '赵六' and age= 22;
查看执行计划
type是index_merge，并且possible_key和key都是idx_name和idx_age，说明使用了索引合并，并且Extra有Using intersect(idx_age,idx_name)，intersect就是交集的意思。
整个过程大致是这样的，分别根据idx_name和idx_age取出对应的主键id，之后将主键id取交集，那么这部分交集的id一定同时满足查询name = '赵六' and age= 22的查询条件（仔细想想），之后再根据交集的id回表
不过要想使用取交集的联合索引，需要满足各自索引查出来的主键id是排好序的，这是为了方便可以快速的取交集
比如下面这条sql就无法使用联合索引
select * from `user` where name = '赵六' and age > 22;
只能用name这个索引，因为age > 22查出来的id是无序的，前面在讲索引的时候有说过索引列的排序规则
由此可以看出，使用联合索引条件还是比较苛刻的。
取并集就是将前面例子中的and换成or
select * from `user` where name = '赵六' or age = 22;
前面执行的情况都一样，根据条件到各自的索引上去查，之后对查询的id取并集去重，之后再回表
同样地，取并集也要求各自索引查出来的主键id是排好序的，如果查询条件换成age > 22时就无法使用取并集的索引合并
select * from `user` where name = '赵六' or age > 22;
虽然取并集要求各自索引查出来的主键id是排好序的，但是如果遇到没排好序的情况，mysql会自动对这种情况进行优化，会先对主键id排序，然后再取并集，这种情况就叫 排序后取并集（sort-union）。
比如上面提到的无法直接取并集的sql就符合排序后取并集（sort-union）这种情况
select * from `user` where name = '赵六' or age > 22;
MyISAM 与 InnoDB
众所周知，MySQL 有两种常见的存储引擎。一种是 MyISAM，一种是 InnoDB。
一、它们是什么？
先来看看官网对 MyISAM 的描述，只有一句话，看来官方也不想多加解释。
MyISAM is based on the older (and no longer available) ISAM storage engine but has many useful extensions.
大意：MyISAM 是一款青出于蓝而胜于蓝的存储引擎，它在 ISAM 基础上作了一些扩展和加工。关于 ISAM ，我只告诉你它是 Indexed Sequential Access Method 的缩写，翻译为“有索引的顺序访问方法”。
而对 InnoDB 的描述，就更 professional 一些了。
InnoDB is a general-purpose storage engine that balances high reliability and high performance. In MySQL 8.0, InnoDB is the default MySQL storage engine. Unless you have configured a different default storage engine, issuing a CREATE TABLE statement without an ENGINE= clause creates an InnoDB table.
大意：InnoDB 是一种通用的存储引擎，在高可靠和高性能上作了均衡。MySQL 8.0 中，它是默认的存储引擎（其实在5.5之后的版本就是了），当你执行 CREATE TABLE 建表语句并且不带 “ENGINE = ”子句时，默认帮你创建的就是 InnoDB 表了。
二、两者有什么区别？
拿官网两者的 Features 来作一个分析对比吧：
1、InnoDB 是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高，但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，否则其他索引也会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的。
2、InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败。
3、InnoDB 在 MySQL 5.6 之前不支持全文索引，而 MyISAM 一直都支持，如果你用的是老版本，查询效率上 MyISAM 要高。
4、InnoDB 锁粒度是行锁，而 MyISAM 是表锁。
5、InnoDB 支持事务，MyISAM 不支持，对于 InnoDB 每一条 SQL 语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条 SQL 语言放在 begin 和 commit 之间，组成一个事务。
6、InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而 MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快，但如果上述语句还包含了 where 子句，那么两者执行效率是一样的。
https://blogs.oracle.com/mysql/post/still-using-myisam-it-is-time-to-switch-to-innodb
https://tecadmin.NET/choosing-between-innodb-myisam-and-memory-storage-engines/
http://dimitrik.free.fr/blog/archives/2015/12/mysql-performance-revisiting-innodb-vs-myisam-with-mysql-57.html
答案是会的！！！
InnoDB是MySQL中的一种存储引擎，它会为每个表创建一个主键索引。如果表没有明确的主键索引，InnoDB会使用一个隐藏的、自动生成的主键来创建索引。这个隐藏的主键索引使用的就是B+树结构。因此，在InnoDB中，即使表没有明确的主键索引，也会创建一个B+树索引。
优点：
1. 提高检索效率
2. 降低排序成本，索引对应的字段是会有一个自动排序功能的，默认是升序asc。
它缺点是
适合：
1. 较频繁的作为查询条件的字段应该创建索引
不适合：
1. 字段值的唯一性太差不适合单独做索引
2. 更新非常频繁的字段不适合
3. 不会出现在where句中的字段不适合。
关于SQL优化方法，包括5点
1）创建索引减少扫描量；
2）调整索引减少计算量；
3）索引覆盖（减少不必访问的列，避免回表查询）；
4）干预执行计划；
5）SQL改写；
Explain是 SQL 分析工具中非常重要的一个功能，它可以模拟优化器执行查询语句，帮助我们理解查询是如何执行的；分析查询执行计划可以帮助我们发现查询瓶颈，优化查询性能。
drop table orders;
drop table products;
drop table users;
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
password VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
price FLOAT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
order_date DATETIME NOT NULL,
total_price FLOAT NOT NULL,
product_id INT NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
alter table users add index index_name_email (name,email);
INSERT INTO users (name, email, password)
VALUES ('张三', 'zhangsan@example.com', 'password123'),
('李四', 'lisi@example.com', 'password123'),
('王五', 'wangwu@example.com', 'password123'),
('赵六', 'zhaoli@example.com', 'password123'),
('钱七', 'qianqi@example.com', 'password123');
INSERT INTO products (name, price)
VALUES ('产品 1', 10.00),
('产品 2', 15.00),
('产品 3', 20.00),
('产品 4', 12.00),
('产品 5', 18.00);
INSERT INTO orders (user_id, order_date, total_price, product_id)
VALUES (1, '2023-02-18 10:00:00', 100.00, 1),
(2, '2023-02-18 11:00:00', 50.00, 2),
(3, '2023-02-18 12:00:00', 20.00, 3),
(4, '2023-02-18 13:00:00', 15.00, 4),
(5, '2023-02-18 14:00:00', 25.00, 5);
MySQL5.7版本之前，使用Explain Extended在Explain的基础上额外多返回filtered列与extra列；
Explain Extended select * from users;
MySQL5.7版本之前，使用Explain Partitions在Explain的基础上额外多返回partitions列；
Explain Partitions select * from users;
MySQL5.7版本引入了这两个特性，直接使用Explain关键字可以将partitions列、filtered列、extra列直接查询出来。
Explain select * from users;
Explain语句返回列的各列含义：
|
列名 |
含义 |
|
id |
每个select都有一个对应的id号，并且是从1开始自增的 |
|
select_type |
查询语句执行的查询操作类型 |
|
table |
表名 |
|
partitions |
表分区情况 |
|
type |
查询所用的访问类型 |
|
possible_keys |
可能用到的索引 |
|
key |
实际查询用到的索引 |
|
key_len |
所使用到的索引长度 |
|
ref |
使用到索引时，与索引进行等值匹配的列或者常量 |
|
rows |
预计扫描的行数（索引行数或者表记录行数） |
|
filtered |
表示符合查询条件的数据百分比 |
|
Extra |
SQL执行的额外信息 |
这些查询列大家先留一个印象，后续会详细讲解。
接下来我们将展示Explain中每个列的信息
explain
SELECT users.name, orders.total_price, products.price
FROM users
INNER JOIN orders ON users.id = orders.user_id
INNER JOIN products ON orders.product_id = products.id;
explain
select * from orders where product_id = (select id from products where products.price = 10);
set session optimizer_switch='derived_merge=off'; #关闭MySQL5.7对衍生表合并优化
explain
select orders.*
from (select id from products) as temp inner join orders on temp.id = orders.product_id;
set session optimizer_switch='derived_merge=on'; #还原配置
explain
select id from users
union
select id from products;
优化器会针对子查询进行一定的优化重写SQL：
EXPLAIN select * from users WHERE id in (select user_id from orders where id = 1);
show WARNINGS;
Explain select * from users;
Explain select * from users inner join orders on users.id = orders.user_id;
explain
select id from users
union
select id from products;
explain
select orders.*,(select name from products where id = 1) from orders;
explain
select orders.*,(select name from products where products.id = orders.user_id) from orders;
set session optimizer_switch='derived_merge=off'; #关闭MySQL5.7对衍生表合并优化
explain
select * from (select user_id from orders where id = 1) as temp;
set session optimizer_switch='derived_merge=on'; #还原配置
explain
select * from (
select id from products where price = 10
union
select id from orders where user_id in (1,2)
union
select id from users where name = '张三' ) as temp;
explain
select * from (
select id from products where price = 10
union all
select id from orders where user_id in (1,2)
union all
select id from users where name = '张三' ) as temp;
explain
select * from orders where id in (
select id from products where price = 10
union
select id from orders where user_id = 2
union
select id from users where name = '张三' );
explain
select id from users
union
select id from products;
查询语句所涉及的表的分区情况。具体来说，它会显示出查询语句在哪些分区上执行，以及是否使用了分区裁剪等信息。如果没有分区，该项为NULL。
效率从高到低分别为：system > const > eq_ref > ref > fulltext > ref_or_null > range > index > ALL，一般来说保证range级别，最好能达到ref级别。
InnoDb存储引擎的统计数据不是精确的，虽然只有一条数据但是type类型为ALL；
DROP TABLE t;
CREATE TABLE t(i INT) ENGINE=InnoDb;
INSERT INTO t VALUES(1);
explain select * from t;
Memory存储引擎的统计数据是精确的，所以当只有一条记录的时候type类型为system。
DROP TABLE tt;
CREATE TABLE tt(i INT) ENGINE=memory;
INSERT INTO tt VALUES(1);
explain select * from tt;
explain
select * from orders where id = 1;
explain
select users.* from users inner join orders on users.id = orders.id;
1.关联查询，使用非唯一索引进行匹配。
explain
select users.* from users inner join orders on users.id = orders.user_id;
2.简单查询，使用二级索引列匹配。
explain
select * from orders where user_id = 1;
explain
select * from orders where user_id > 3;
explain
select user_id from orders;
explain
select * from users;
explain
select min(id) from users;
联合索引可以通过这个值算出具体使用了索引中的哪些列。
使用单例索引：
explain
select * from users where id = 1;
使用联合索引：
explain
select * from users where name = '张三' and email = 'zhangsan@example.com';
计算规则：
char(n)：n个字节
varchar(n)：如果是uft-8：3n+2字节，加的2个字节存储字符串长度。如果是utf8mb4：4n+2字节。
tinyint：1字节
smaillint：2字节
int：4字节
bigint：8字节
date：3字节
timestamp：4字节
datetime：8字节
字段如果为NULL，需要1个字节记录是否为NULL
当使用索引列等值查询时，与索引列进行等值匹配的对象信息。
1.常量：
explain
select * from users where name = '张三' and email = 'zhangsan@example.com';
2.字段：
explain
select users.* from users inner join orders on users.id = orders.id;
3.函数
explain
select users.* from users inner join orders on users.id = trim(orders.id);
1.全表扫描
explain
select * from orders where user_id >= 3 and total_price = 25;
2.索引扫描
explain
select * from orders where user_id > 3;
前一个表指 explain 中的id值比当前表id值小的表，id相同的时候指后执行的表。
explain
select users.* from users inner join orders on users.id = orders.id;
explain
select user_id,id from orders where user_id = 1;
explain
select * from orders where total_price = 100;
explain
select * from orders where user_id = 1 and total_price = 100;
explain
select * from orders where user_id > 3;
1.total_price列无索引，需要创建一张临时表进行去重
explain
select distinct total_price from orders;
2.name列有联合索引
explain
select distinct name from users;
1.total_price列无索引，无法通过索引进行排序。需要先保存total_price与对应的主键id，然后在排序total_price查找数据。
explain
select total_price from orders order by total_price;
2.name列有索引，因索引已经是排好序的所以直接读取就可以了。
explain
select name from users order by name;
explain
select min(id) from users;
explain
select min(password) from users;
示例表：
CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
`remark` varchar(255) DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='员工记录表';
INSERT INTO `test`.`employees`(`id`, `name`, `age`, `position`, `hire_time`, `remark`) VALUES (2, '张三', 18, 'beijing', '2023-06-11 20:51:35', '测试');
INSERT INTO `test`.`employees`(`id`, `name`, `age`, `position`, `hire_time`, `remark`) VALUES (3, '张三', 23, 'shanghai', '2023-06-11 20:51:35', '测试2');
INSERT INTO `test`.`employees`(`id`, `name`, `age`, `position`, `hire_time`, `remark`) VALUES (1, '李四', 20, 'shanghai', '2023-06-11 20:51:35', '测试3');
INSERT INTO `test`.`employees`(`id`, `name`, `age`, `position`, `hire_time`, `remark`) VALUES (5, '王五', 19, 'beijing', '2023-06-12 14:32:15', NULL);
INSERT INTO `test`.`employees`(`id`, `name`, `age`, `position`, `hire_time`, `remark`) VALUES (4, '赵六', 22, 'shenzheng', '2023-06-12 14:33:00', NULL);
INSERT INTO `test`.`employees`(`id`, `name`, `age`, `position`, `hire_time`, `remark`) VALUES (6, '赵六', 24, 'beijing', '2023-06-12 14:37:50', NULL);
INSERT INTO `test`.`employees`(`id`, `name`, `age`, `position`, `hire_time`, `remark`) VALUES (7, '刘七', 20, 'shanghai', '2023-06-12 14:38:27', NULL);
INSERT INTO `test`.`employees`(`id`, `name`, `age`, `position`, `hire_time`, `remark`) VALUES (8, '刘七', 22, 'beijing', '2023-06-12 14:38:41', NULL);
INSERT INTO `test`.`employees`(`id`, `name`, `age`, `position`, `hire_time`, `remark`) VALUES (9, '王九', 9, 'shanghai ', '2023-06-12 14:40:17', NULL);
MySQL全值匹配是指在使用复合索引时，查询条件要包含索引的所有列，才能最大程度地利用索引。
EXPLAIN SELECT * FROM employees WHERE name= '张三';
查看索引长度是74=(3*24+2),可以算出联合索引中只使用了name前缀索引.
EXPLAIN SELECT * FROM employees WHERE name= '张三' AND age = 18;
查看索引长度是78=(3*24+2)+4,可以算出联合索引中只使用了name和age前缀索引.
EXPLAIN SELECT * FROM employees WHERE name= '张三' AND age = 18 AND position ='beijing';
查看索引长度是140=(3*24+2)+4+(3*20+2),可以算出联合索引中只使用了完整的联合索引
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
——带头大哥不能死，中间兄弟不能断；
-- name 74 +age 78 + position 140
EXPLAIN SELECT * FROM employees WHERE name= '张三' and age = 18 AND position = 'beijing' ;
-- 带头大哥不能死
EXPLAIN SELECT * FROM employees WHERE age = 18 AND position = 'beijing';
-- 中间兄弟不能断
EXPLAIN SELECT * FROM employees WHERE name= '张三' AND position = 'beijing';
EXPLAIN SELECT * FROM employees WHERE name = '张三';
EXPLAIN SELECT * FROM employees WHERE left(name,3) = '张三';
给hire_time增加一个普通索引：
ALTER TABLE `employees` ADD INDEX `idx_hire_time` (`hire_time`) USING BTREE ;
EXPLAIN select * from employees where date(hire_time) ='2018-09-30';
转化为日期范围查询，有可能会走索引：
EXPLAIN select * from employees where hire_time >='2018-09-30 00:00:00' and hire_time <='2018-09-30 23:59:59';
还原最初索引状态
ALTER TABLE `employees` DROP INDEX `idx_hire_time`;
范围查询会使后面字段无序，造成部分索引失效。
——范围之后全失效；
EXPLAIN SELECT * FROM employees WHERE name= '张三' AND age = 18 AND position ='beijing';
EXPLAIN SELECT * FROM employees WHERE name= '张三' AND age > 18 AND position ='beijing';
——覆盖索引不写星；
EXPLAIN SELECT name,age FROM employees WHERE name= '张三' AND age = 18 AND position ='beijing';
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager';
mysql在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描
< 小于、 > 大于、 <=、>= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引
EXPLAIN SELECT * FROM employees WHERE name != '张三';
is null,is not null 一般情况下也无法使用索引
EXPLAIN SELECT * FROM employees WHERE name is null
少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引，详见范围查询优化
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' or name = 'HanMeimei';
——Like百分写最右
EXPLAIN SELECT * FROM employees WHERE name like '%三'
EXPLAIN SELECT * FROM employees WHERE name like '张%'
问题：解决like'%字符串%'索引不被使用的方法？
a）使用覆盖索引，查询字段必须是建立覆盖索引字段
EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei%';
b）如果不能使用覆盖索引则可能需要借助搜索引擎
——VAR引号不可丢
EXPLAIN SELECT * FROM employees WHERE name = '1000';
EXPLAIN SELECT * FROM employees WHERE name = 1000;
给年龄添加单值索引
ALTER TABLE `employees` ADD INDEX `idx_age` (`age`) USING BTREE ;
explain select * from employees where age >=1 and age <=2000;
没走索引原因：mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。比如这个例子，可能是由于单次数据量查询过大导致优化器最终选择不走索引
优化方法：可以将大的范围拆分成多个小范围
explain select * from employees where age >=1 and age <=1000;
explain select * from employees where age >=1001 and age <=2000;
还原最初索引状态
ALTER TABLE `employees` DROP INDEX `idx_age`;
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
Like百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用；
VAR引号不可丢，SQL高级也不难！
trace工具用法：
mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on; --开启trace
mysql> select * from employees where name > 'a' order by position;
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;
查看trace字段：
{
"steps": [
{
"join_preparation": { --第一阶段：SQL准备阶段，格式化sql
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from `employees` where (`employees`.`name` > 'a') order by `employees`.`position`"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": { --第二阶段：SQL优化阶段
"select#": 1,
"steps": [
{
"condition_processing": { --条件处理
"condition": "WHERE",
"original_condition": "(`employees`.`name` > 'a')",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`employees`.`name` > 'a')"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`employees`.`name` > 'a')"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`employees`.`name` > 'a')"
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */
},
{
"table_dependencies": [ --表依赖详情
{
"table": "`employees`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [ --预估表的访问成本
{
"table": "`employees`",
"range_analysis": {
"table_scan": { --全表扫描情况
"rows": 10123, --扫描行数
"cost": 2054.7 --查询成本
} /* table_scan */,
"potential_range_indexes": [ --查询可能使用的索引
{
"index": "PRIMARY", --主键索引
"usable": false,
"cause": "not_Applicable"
},
{
"index": "idx_name_age_position", --辅助索引
"usable": true,
"key_parts": [
"name",
"age",
"position",
"id"
] /* key_parts */
}
] /* potential_range_indexes */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */,
"analyzing_range_alternatives": { --分析各个索引使用成本
"range_scan_alternatives": [
{
"index": "idx_name_age_position",
"ranges": [
"a < name" --索引使用范围
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false, --使用该索引获取的记录是否按照主键排序
"using_mrr": false,
"index_only": false, --是否使用覆盖索引
"rows": 5061, --索引扫描行数
"cost": 6074.2, --索引使用成本
"chosen": false, --是否选择该索引
"cause": "cost"
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`employees`",
"best_access_path": { --最优访问路径
"considered_access_paths": [ --最终选择的访问路径
{
"rows_to_scan": 10123,
"access_type": "scan", --访问类型：为scan，全表扫描
"resulting_rows": 10123,
"cost": 2052.6,
"chosen": true, --确定选择
"use_tmp_table": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 10123,
"cost_for_plan": 2052.6,
"sort_cost": 10123,
"new_cost_for_plan": 12176,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`employees`.`name` > 'a')",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`employees`",
"attached": "(`employees`.`name` > 'a')"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`employees`.`position`",
"items": [
{
"item": "`employees`.`position`"
}
] /* items */,
"resulting_clause_is_simple": true,
"resulting_clause": "`employees`.`position`"
} /* clause_processing */
},
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"steps": [
] /* steps */,
"index_order_summary": {
"table": "`employees`",
"index_provides_order": false,
"order_direction": "undefined",
"index": "unknown",
"plan_changed": false
} /* index_order_summary */
} /* reconsidering_access_paths_for_index_ordering */
},
{
"refine_plan": [
{
"table": "`employees`"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": { --第三阶段：SQL执行阶段
"select#": 1,
"steps": [
] /* steps */
} /* join_execution */
}
] /* steps */
}
结论：全表扫描的成本低于索引扫描，所以mysql最终选择全表扫描
mysql> select * from employees where name > 'zzz' order by position;
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;
查看trace字段可知索引扫描的成本低于全表扫描，所以mysql最终选择索引扫描
mysql> set session optimizer_trace="enabled=off"; --关闭trace
DROP TABLE IF EXISTS student;
CREATE TABLE student (
id int(10) NOT NULL AUTO_INCREMENT COMMENT '序号',
student_id INT NOT NULL COMMENT '学号',
name varchar(20) COMMENT '姓名',
department varchar(20) COMMENT '院系',
remarks varchar(400) COMMENT '备注',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
DROP TABLE IF EXISTS scores;
CREATE TABLE scores (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '序号',
student_id INT NOT NULL COMMENT '学号',
course_name VARCHAR(50) NOT NULL COMMENT '课程名称',
score INT NOT NULL COMMENT '分数',
remarks varchar(400) COMMENT '备注'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE student ADD index idx_name_department (name, department);
INSERT INTO `student` (`name`,`student_id`,`department`,`remarks`) values ('刘零',1,'美术','备注0');
INSERT INTO `student` (`name`,`student_id`,`department`,`remarks`) values ('郑一',2,'土木','备注1');
INSERT INTO `student` (`name`,`student_id`,`department`,`remarks`) values ('吴二',3,'数学','备注2');
INSERT INTO `student` (`name`,`student_id`,`department`,`remarks`) values ('张三',4,'中文','备注3');
INSERT INTO `student` (`name`,`student_id`,`department`,`remarks`) values ('李四',5,'英语','备注4');
INSERT INTO `student` (`name`,`student_id`,`department`,`remarks`) values ('王五',6,'美术','备注5');
INSERT INTO `student` (`name`,`student_id`,`department`,`remarks`) values ('钱六',7,'土木','备注6');
INSERT INTO `student` (`name`,`student_id`,`department`,`remarks`) values ('孙七',8,'数学','备注7');
INSERT INTO `student` (`name`,`student_id`,`department`,`remarks`) values ('赵八',9,'英语','备注8');
INSERT INTO `student` (`name`,`student_id`,`department`,`remarks`) values ('周九',10,'数学','备注9');
BEGIN
DECLARE v_name VARCHAR(20);
DECLARE v_department VARCHAR(200);
DECLARE i INT DEFAULT 0;
DECLARE n INT DEFAULT 100000;
DECLARE v_max_id INT DEFAULT 1;
set autocommit = 0;
select max(id) into v_max_id from student;
REPEAT
set i = i + 1;
set v_max_id = v_max_id + 1;
set v_name = CONCAT('mock_name',i);
set v_department = CONCAT('mock_department',i);
INSERT INTO `student` (`student_id`,`name`,`department`,`remarks`) values (v_max_id,v_name,v_department,'mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks');
INSERT INTO `scores` (`student_id`,`course_name`,`score`,`remarks`) values (v_max_id,CONCAT('mock_Chinese',i),RAND()*(100-50)+50,'mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks');
INSERT INTO `scores` (`student_id`,`course_name`,`score`,`remarks`) values (v_max_id,CONCAT('mock_Math',i),RAND()*(100-50)+50,'mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks');
INSERT INTO `scores` (`student_id`,`course_name`,`score`,`remarks`) values (v_max_id,CONCAT('mock_English',i),RAND()*(100-50)+50,'mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks_mock_remarks');
UNTIL i = n
END REPEAT;
COMMIT;
set autocommit = 1;
END
阿里规范：
分析：
查看执行计划，select * 走全表扫描，没有用到任何索引，查询效率非常低；查询列都是索引列那么这些列被称为覆盖索引。这种情况下查询的相关字段都能走索引，索引查询的效率相对较高。
EXPLAIN select * from student where name like 'mock_name%';--不走索引
EXPLAIN select name, department from student where name like 'mock_name%';--走索引
通过show warnings语句查看查询列*号替换成表所有字段。
EXPLAIN select * from student where name like 'mock_name%';--替换成表对应的字段
SHOW WARNINGS;
总结：
小表驱动大表就是指用数据量较小、索引比较完备的表，然后使用其索引和条件对大表进行数据筛选，从而减少数据计算量，提高查询效率。比如说student表有30条数据，scores表有80w条数据。
EXPLAIN
select * from student left join scores on student.id = scores.student_id;
EXPLAIN
select * from scores left join student on student.id = scores.student_id;
Join Buffer（连接缓冲区）是优化器用于处理连接查询操作时的临时缓冲区。简单来说当我们需要比较两个或多个表的数据进行Join操作时，Join Buffer可以帮助MySQL临时存储结果，以减少磁盘读取和CPU负担，提高查询效率。需要注意的是每个join都有一个单独的缓冲区。
Block nested-loop join（BNL算法）会将驱动表数据加载到join buffer里面，然后再批量与非驱动表进行匹配；如果驱动表数据量较大，join buffer无法一次性装载驱动表的结果集，将会分阶段与被驱动表进行批量数据匹配，会增加被驱动表的扫描次数，从而降低查询效率。所以开发中要遵守小表驱动大表的原则。
分阶段匹配过程如下：
1、先把student表前15条数据读到join buffer中。
2、然后用scores表去匹配join buffer中的前15条。
3、记录下匹配结果。
4、清空join buffer。
5、再把student表后15条读取join buffer中。
6、然后用scores表去匹配join buffer中的后15条。
7、记录下匹配结果。
mysql需要在两张表以上获取数据的方式有两种：第一种通过连表查询获取，第二种通过子查询获取。
模拟一个真实场景，同样student表有30条数据，scores表有80w条数据，我们想查看学号小于15的学员各科分数信息：
ALTER TABLE scores ADD index idx_student_id (student_id);
EXPLAIN
SELECT
(SELECT student.NAME FROM student WHERE student.id = scores.student_id),
scores.course_name,
scores.score
FROM
scores;
因为子查询需要执行两次数据库查询，一次是外部查询，一次是嵌套子查询。因此，使用连接查询可以减少数据库查询的次数，提高查询的效率。
连接查询可以更好地利用数据库索引，提高查询的性能。子查询通常会使用临时表或内存表，而连接查询可以直接利用表上的索引。这意味着连接查询可以更快地访问表中的数据，减少查询的资源消耗。
对于大型数据集，使用连接查询通常比使用子查询更高效。子查询通常需要扫描整个表，而连接查询可以利用索引加速读取操作。
EXPLAIN
SELECT
student.NAME,
scores.course_name,
scores.score
FROM
student inner JOIN scores ON student.id = scores.student_id;
使用连接查询可以更快地执行查询操作，减少数据库的负载，提高查询的性能和效率。
select remarks from scores group by remarks;
添加索引前：
添加索引后：
批量插入或批量删除数据，比如说现在需要将1w+数据插入到数据库，大家是一条一条处理还是批量操作呢？建议是批量操作，逐个处理会频繁的与数据库交互，损耗性能。
反例：
for(Order order: list){
orderMapper.insert(order):
}
在循环中逐条插入数据。
insert into order(id,code,user_id) values(123,'001',100)
该操作需要多次请求数据库，才能完成这批数据的插入。
但众所周知，我们在代码中，每次远程请求数据库，是会消耗一定性能的。而如果我们的代码需要请求多次数据库，才能完成本次业务功能，势必会消耗更多的性能。
那么如何优化呢？
正例：
orderMapper.insertBatch(List<User> users):
提供一个批量插入数据的方法。
insert into order(id,code,user_id) values(123,'001',100),(124,'002',100),(125,'003',101);
这样只需要远程请求一次数据库，sql性能会得到提升，数据量越多，提升越大。
但需要注意的是，不建议一次批量操作太多的数据，如果数据太多数据库响应也会很慢。批量操作需要把握一个度，建议每批数据尽量控制在500以内。如果数据多于500，则分多批次处理。
限制行数非常有用，因为它可以提高查询性能、减少处理需要的时间，并且只返回我们关心的列。
百万级表Limit翻页越往后越慢咋办
为什么 offset 偏大之后 limit 查找会变慢？这需要了解 limit 操作是如何运作的，以下面这句查询为例：
select * from table_name limit 10000,10
这句 SQL 的执行逻辑是
第一次优化
根据数据库这种查找的特性，就有了一种想当然的方法，利用自增索引（假设为id）：
select * from table_name where (id >= 10000) limit 10
第二次优化
说起数据库查询优化，第一时间想到的就是索引，所以便有了第二次优化：先查找出需要数据的索引列（假设为 id），再通过索引列查找出需要的数据。
Select * From table_name Where id in (Select id From table_name where ( user = xxx )) limit 10000, 10;
select * from table_name where( user = xxx ) limit 10000,10
相比较结果是（500w条数据）：第一条花费平均耗时约为第二条的 1/3 左右。
同样是较大的 offset，第一条的查询更为复杂，为什么性能反而得到了提升？
这涉及到 mysql 主索引的数据结构 b+Tree ，这里不展开，基本原理就是：
第三次优化
在数据量大的时候 in 操作的效率就不怎么样了，我们需要把 in 操作替换掉，使用 join 就是一个不错的选择。
select * from table_name inner join ( select id from table_name where (user = xxx) limit 10000,10) b using (id)
select id,name,department from student
union all
select id,student_id,score from scores
select id,name,department from student
union
select id,student_id,score from scores
那么union all与union如果当然它业务数据容许出现重复的记录，我们更推荐使用union all，因为union去重数据需要遍历、排序和比较，它更耗时，更消耗cpu资源，但是数据结果最完整。
因此，在数据库设计时，应该尽量减少JOIN操作的使用频率，并且简化表之间的关系，以提高查询效率和系统的性能。
除上述优化之外，通常在建表还需要注意以下内容：
SQL优化是提高数据库性能的重要方法，在实际开发中我们的SQL要尽量遵守以下几点原则，避免留下技术债：
京公网安备 11010802035086号