mysql索引原理与优化

InnoDB
InnoDB:擅长处理事务
InnoDB存储引擎的内存结构主要有三部分Buffer Pool、Change Buffer、log buffer。

Buffer Pool：缓冲池，用来缓存表数据和索引数据
Page页根据状态可以分为三种类型
free page:空闲page,未被使用
clean page:被使用page，数据没有被修改过
dirty page:脏页，被使用page，数据被修改过，页中数据和磁盘数据产生了不一致。

Page页通过三种链表结构来维护和管理
free list:表示空闲缓冲区，管理free page
flush list:表示需要刷新到磁盘的缓冲区，管理dirty page
lrush list:表示正在使用的缓冲区，管理clean page和dirty page。
LRU优点：最近使用的数据都在链表的头部，最近未使用的数据都在链表的尾部，保证热数据能够最快的被获取到。
LRU缺点：
1.如果发生全表扫描，会把表中的所有页加载到BufferPool中，就会将真正的热数据淘汰掉。因为全表扫描的数据很可能只使用一次，被淘汰的这部分数据才是真正经常被使用的数据。
2.mysql中存在预读机制，很多预读的页都会被放到LRU链表的头部，如果这些预读的页都没有用到的画，这样的话也会导致真正的热数据被淘汰了。
改进型LRU算法
链表分为new和old两部分

冷数据区的数据页什么时候被转到热数据区？
1.该页在LRU链表中存在时间超过1s，将其移动到链表的头部。
2.如果该页在LRU链表中存在的时间小于1s，其位置不变。

redo log和undo log
redo log:重做日志，包括了两部分：一部分是内存中的日志缓冲区(redo log buffer)，另一部分是磁盘上的日志文件(redo log buffer)，我们可以通过redo日志实现事务的持久化。mysql每执行一条DML语句，先将记录写入redo log buffer(redo日志记录的是事务对数据库做了哪些修改)。后续某个时间点再一次性将多个操作记录写到redo log file。
redo log作用：
实现事务的持久性，保证数据的完整性。

undo log：撤销日志，在数据库事务开始之前,Mysql会记录更新前的数据到undo log文件中，如果事务回滚或者数据库崩溃时，可以利用undo log进行回退。
undo log作用：
1.提供回滚操作，实现事务的原子性
2.实现多版本并发控制MVCC(当我们读取某一行数据的时候，如果说这行数据被某个事务锁定了，可以通过undo log读取旧版本的数据，作用是为了实现我们数据库的非阻塞读·)

undo log和redo log的区别？
undo log记录的是事务开始前的数据状态，记录的是更新之前的值。redo log记录的是事务完成后的数据状态，记录的是更新后的值。

索引：是存储引擎用来快速查找记录的一种数据结构

二叉查找树：左子树的键值小于根节点的值，右子树的键值大于根节点的键值。
35 -> 节点中保存着数据字段和指向数据对应记录的物理地址的指针。

二叉查找树的缺点：
存储单边递增有序的数据时，最终的排列结构会形成一个单向链表，会导致对链表尾部的数据读取效率降低。

平衡二叉树：在符合二叉查找树的条件下，还会满足任何节点的两个子树的高度差最大为1。
优点：1.叶子节点的层级减少 2.形态上能够保持平衡 3.查询效率提升了，大量的顺序插入也不会导致性能的降低。
缺点：1.一个节点最多分裂两个子节点，树的高度太高，导致IO次数过多。
2.节点中只保存一个关键字，每次操作获取的目标数据太少。

B-Tree
1.B-Tree是一种平衡的多路查找树,B树允许一个节点存储多个元素
2.B-Tree中的所有节点子树的个数最大值被称为B-Tree的阶，用m表示
m阶的B-Tree需要满足以下条件:
1.每个节点最多拥有m个子树(根节点除外)，每个节点最多有m-1个关键字
2.根节点最少要有两个子树(可以没有,有必须是两个)
3.分支节点至少要有(m/2)颗子树。
4.所有的叶子节点都在同一层，并且以升序排序。
索引存储特点：
B-Tree会定义一条记录为一个键值对[key,value],key就是一条记录的主键，对应的value就是表中除主键外的数据data。

1.索引值和data数据是分布在整颗树的结构中
2.白色块就是指针，保存着子节点的地址信息。
3.每个节点是可以存储多个索引值和对应的data数据的。
4.树节点的多个索引值是按照升序从左往右排列的。
优点：B树的内部节点中可以存储关键字和相关记录数据，如果把频繁访问的数据放在靠近根节点的位置，就会大大提高热点数据的查询效率。
缺点：B树中每个节点不仅有关键字还有data数据，如果当数据较大时，会导致每个节点存储的key值减少，会导致B树的层数变高，增加查询的I/O次数。
使用场景：主要用户文件系统部分数据库索引，比如MongoDB,大部分关系型数据库的索引是用B+Tree实现的。

B+Tree：B+Tree是在B-Tree的基础上做的一种优化，InnoDB存储引擎就是使用B+Tree来实现其索引结构的。特征：1.非叶子节点只存储索引值 2.数据记录都保存在叶子节点中。3.所有的叶子节点之间有一个链指针。

优点：1.降低树高度，增加节点存储的数据量。
2.B+Tree扫库或者扫表的能力更强，对B-Tree进行扫描时，需要遍历整颗树，B+Tree只需要遍历所有的叶子节点就可以了，而且叶子节点之间还有指针进行连接。
3.B+Tree的磁盘读写能力更强，因为他的根节点和分支节点是不存储数据的，在所有根节点和分支节点同样大小的情况下，保存的关键字要比B-Tree多，所以读写一次磁盘加载的关键字也要比B-Tree更多。
4.B+Tree排序能力更强。
5.B+Tree查询更加稳定，每次查询数据，查询IO次数是相对稳定的。

Hash索引：

聚簇索引(主键索引)：将数据和索引存储在一块，索引结构的叶子节点保存了行数据。

注意：InnoDB的表要求是必须要有聚簇索引：
1.如果表中定义了主键，这个主键索引就是聚簇索引。
2.如果表中没有主键，将第一个非空unique列作为聚簇索引。
3.都没有的话，InnoDB会创建一个隐藏的row-id作为聚簇索引。

非聚簇索引(二级索引)：将数据和索引分开存储，二级索引的索引结构叶子节点中只保存索引列和主键信息。

回表：先根据普通索引找到主键值，然后再根据主键值去聚簇索引中找到完整的数据记录，这就是回表。
覆盖索引：一个索引包含了所需要查询的所有字段的值(不需要回表)，这个索引就是覆盖索引。

explain执行计划

ID字段：select语句的查询序列号，是一组数字表示的是查询或操作表的顺序。
1.ID字段相同，执行的顺序就是从上至下。
EXPLAIN SELECT FROM l1,l2,l3 WHERE l1.id=l2.id AND l2.id=l3.id

2.ID不同，如果有子查询，ID号会递增，ID的值越大，优先级越高，越优先被执行。
EXPLAIN SELECT FROM l2 WHERE id =(SELECT id FROM l1 WHERE id=(SELECT l3.id FROM l3 WHERE l3.title=’ruyuan007’))

select_type:表示查询类型，主要是用来区别普通查询还是子查询。
1.SIMPLE：简单查询
EXPLAIN SELECT FROM L1 WHERE id=1;

2.PRIMARY：在有子查询的情况下，最外层被标记为PRIMARY
3.SUBQUERY：表示在select中或where列表包含了子查询
explain select FROM L2 WHERE id=(SELECT id from l1 WHERE id=(SELECT l3.id FROM l3 WHERE l3.title=’ruyuan008’))

4.DERIVED：在from列表中的子查询被标记为派生表，Mysql会递归执行这些子查询，把结构放入临时表
5.UNION：在使用UNION的时候第一个查询是派生表，之后表的select_type=UNION
UNION RESULT:UNION的结果
EXPLAIN SELECT FROM (SELECT FROM l3 UNION SELECT FROM L4) a

table:被操作表
type:连接类型，为了找到所需的数据而使用的扫描方式，system>const>eq_ref_ref>range>index>All,一般来说保证查询至少要达到range级别，最好能到ref，否则就要进行SQL优化。
1.system:表示表中只有一行数据的时候，很少出现
2.const:表示命中了主键索引或者是唯一索引，表示的是通过索引一次就找到了数据。
命中主键索引：EXPLAIN SELECT FROM L1 WHERE id=3

ALTER TABLE l1 ADD UNIQUE(title); — 添加唯一索引
命中唯一索引：EXPLAIN SELECT FROM L1 WHERE l1.title=’ruyuan001’

3.eq_ref:连接查询中对于前一个表的每一行，后表只有一行被扫描，只有在连接时使用的索引都是主键或者唯一索引时，才会有这种类型出现。
连接时使用的索是主键
EXPLAIN SELECT l1.id,l1.title FROM l1 LEFT JOIN l2 ON l1.id=l2.id

4.ref:表示使用了普通索引，对于前表的每一行，后面可能有多于一行的数据被扫描
ALTER TABLE l1 add INDEX idx_title(title)
EXPLAIN SELECT FROM l1 INNER JOIN l2 ON l1.title=l2.title

5.range:表示的是进行了索引上的范围查询，检索了给定范围的行。
EXPLAIN SELECT FROM l1 WHERE l1.id BETWEEN 1 AND 10

6.index:出现index，表示的是需要扫描索引上的全部数据。
EXPLAIN SELECT FROM l2 GROUP BY id ORDER BY id;

EXPLAIN SELECT count() FROM l2 ;

7.ALL：没有使用任何索引，发生全表扫描
EXPLAIN SELECT FROM l3 INNER JOIN l4 ON l3.title=l4.title

总结各类type类型的特点：

possible_keys:可能应用到表上的索引
key:实际使用的索引，如果为null表示没有使用到索引或者索引失效了。
key_len:表示的是索引中使用的字节数，通过该列可以计算查询中使用索引的长度，长度越长越好，表示索引被充分的利用。
创建表sql
CREATE TABLE L5 ( a INT PRIMARY KEY, b INT NOT NULL, c INT DEFAULT NULL,d CHAR(10) NOT NULL );
key_len=4字节，用到了主键索引是int类型，所以是4字节。
EXPLAIN SELECT FROM l5 WHERE a>1 AND b=1

key_len计算规则如下：
字符串，char(n)和varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字
或字母占1个字节，一个汉字占3个字节
char(n)：如果存汉字长度就是 3n 字节
varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为
varchar是变长字符串
数值类型
tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节　
时间类型
date：3字节
timestamp：4字节
datetime：8字节
如果字段允许为 NULL，需要1字节记录是否为 NULL
索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。
示例数据初始化
CREATE TABLE users (
uid INT PRIMARY KEY AUTO_INCREMENT,
uname VARCHAR(20),
age INT(11)
);
INSERT INTO users VALUES(NULL, ‘lisa’,10);
INSERT INTO users VALUES(NULL, ‘lisa’,10);
INSERT INTO users VALUES(NULL, ‘rose’,11);
INSERT INTO users VALUES(NULL, ‘jack’, 12);
INSERT INTO users VALUES(NULL, ‘sam’, 13);
extra:显示Mysql在查询过程中的一些详细信息。
1.Using filesort：表示对所有的记录进行了文件排序，排序没有用到索引。
explain SELECT FROM users ORDER BY age;

2.Using temporary：表示使用了临时表来存储结果集
EXPLAIN SELECT count() ,uname FROM users GROUP BY uname;

3.Using where：表示使用了全表扫描
EXPLAIN SELECT FROM users WHERE age=10

创建索引：alter table users add INDEX idx_uname(uname)
3.Using index：表示直接访问索引就能获取到所需要的数据。
EXPLAIN SELECT uid,uname FROM users WHERE uname=’lisa’;

4.Using join buffer (hash join)：表示使用了连接缓存，会显示连接查询时使用的算法。
EXPLAIN SELECT FROM users u1 LEFT JOIN(SELECT FROM users WHERE age=1) u2 ON u1.age=u2.age

创建索引：ALTER TABLE test_explain.l5
ADD INDEX idx_c_d(c, d) USING BTREE;
5.Using index condition：表示的是使用了索引，但是只使用了索引的一部分
EXPLAIN SELECT FROM l5 WHERE c>10 AND d=’s’

ref:表示的是索引那一列被使用了，并且是一个常量。
explain SELECT FROM l1 WHERE l1.id=1

rows:表示的是MySQL中为了找到所需要的记录，一共访问了多少行。
filtered:返回结果的行占需要读取的行的百分比。
EXPLAIN SELECT * FROM l3,l4 WHERE l3.id=l4.id AND l3.title like ‘ruyuan007’

索引优化原则

准备数据
create database idx_optimize character set ‘utf8’;

CREATE TABLE users(
id INT PRIMARY KEY AUTO_INCREMENT,
user_name VARCHAR(20) NOT NULL COMMENT ‘姓名’,
user_age INT NOT NULL DEFAULT 0 COMMENT ‘年龄’,
user_level VARCHAR(20) NOT NULL COMMENT ‘用户等级’,
reg_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘注册时间’
);

INSERT INTO users(user_name,user_age,user_level,reg_time)
VALUES(‘tom’,17,’A’,NOW()),(‘jack’,18,’B’,NOW()),(‘lucy’,18,’C’,NOW());
创建联合索引：
ALTER TABLE users ADD INDEX idx_nal (user_name,user_age,user_level) USING BTREE;
1.最佳左前缀法则：如果创建的是联合索引，就必须遵守这个法则。当使用联合索引时,where后面条件需要从索引的最左前列开始使用。

1. 场景1：按照索引字段顺序使用，没有问题。

explain SELECT * FROM users WHERE user_name =’tom’ AND user_age=17 and user_level=’A’

1. 场景2：直接跳过user_name 字段，没有遵守最佳左前缀则导致索引失效。

explain SELECT * FROM users WHERE user_age=17 and user_level=’A’

1. 场景3：查询不按照创建联合索引的顺序，索引是可以使用的(原因是：MySQL底层优化器对其进行了优化。)

explain SELECT * FROM users WHERE user_age=17 and user_level=’A’ AND user_name =’tom’

1. 场景4：只要包含最左侧字段，索引就可以生效。

EXPLAIN SELECT * FROM users WHERE user_name=’tom’

最佳左前缀底层原理
mysql创建联合索引的时候要遵守一个规则：首先会对联合索引最左边的字段进行排序，再在第一个字段的基础之上对第二个字段进行排序。

2.不要在索引列上左任何的运算，比如计算、使用函数、自动或手动的进行类型转换都会导致索引的失效，从而使查询转向全表扫描。
插入数据：INSERT INTO users(user_name,user_age,user_level,reg_time) VALUES(‘11223344’,22,’D’,NOW());

1. 场景1：使用系统函数left()函数，对user_name进行操作

EXPLAIN SELECT * FROM users WHERE LEFT(user_name,6) =’112233’ — 索引失效

1. 字符串不加单引号(隐式类型转换)

EXPLAIN SELECT * FROM users WHERE user_name=11223344

3.范围之后全失效：where条件中如果有范围条件，并且范围条件之后还有其他条件，范围之后的索引列的索引就会失效。
— user_level索引失效了
EXPLAIN SELECT * FROM users WHERE user_name=’tom’ and user_age>17 AND user_level=’A’

4.避免使用is null、is not null、!=、or

• 使用is null会使索引失效

  EXPLAIN SELECT * FROM users WHERE user_name is null
  Impossible WHERE:表示where条件不成立，不能返回任何的行。
  

• 使用is not null 也会使索引失效

EXPLAIN SELECT * FROM users where user_name is not null

• 使用!=和or会使索引失效(不确定)

EXPLAIN SELECT FROM users WHERE user_name != ‘tom’;
EXPLAIN SELECT FROM users WHERE user_name = ‘tom’ or user_name = ‘jack’;

4.like以%开头会使索引失效，也就是说%出现在左边的时候索引失效，在右边时不影响
EXPLAIN SELECT * FROM users WHERE user_name like ‘%tom’

注意：解决%在左边导致索引失效的方法，使用覆盖索引
EXPLAIN SELECT user_name,user_age,user_level FROM users WHERE user_name like ‘%tom%’

索引优化原则总结
最左前缀法则要遵守
索引列上不计算
范围之后全失效
覆盖索引记住用
不等于、is null、is not null、or导致索引失效。
like百分号加右边，加左边导致索引失效，解决方法:使用覆盖索引。

JOIN优化

驱动表：多表关联查询时，第一个被处理的表就是驱动表，使用驱动表去关联其他的表。
左连接中 左表是驱动表，右表是被驱动表
右连接中 右表是驱动表，左表是被驱动表
内连接中 表数据量较小的表会由mysql自动选择作为驱动表去驱动大表
有一个重点是，如果where条件存在的话 mysql会根据where实际条件进行驱动表的选择
sql优化中，一个比较重要的点就是要用小表驱动大表
JOIN原理：
1.simple Nested-Loop Join(简单的嵌套循环连接)：本质上就是一个双层的for循环，通过循环外层表的行数据，逐个与内层表的所有行数据进行比较来获取结果。SNL的匹配方式：匹配次数=外层表的行数 内层表的行数
2.Index Nested-Loop Join(索引嵌套循环连接):对简单的嵌套循环连接做了优化，主要是为了减少内层表数据的匹配次数，将进行join的字段(被驱动表中的字段)建立索引。 INL的匹配方式：匹配次数=外层表的行数 内层表索引的高度。
3. Block Nested Loop Join (块嵌套循环)：如果join字段没有索引，MySQL也不会使用SNL，而是加入Buffer缓冲区，降低内循环的次数。MySQL中JOIN Buffer的默认大小是256KB，可以通过join_buffer_size来查看。设置JOIN Buffer的大小:set session join_buffer_size=大小值。
JOIN总结：

1. 永远使用小结果驱动大结果集(本质上就是减少外层循环的数据数量)
2. 为匹配的条件增加索引(减少内层表的循环匹配次数)
3. 增大join buffer size的大小(一次缓存的数据越多，内存表的扫描次数就越少)
4. 减少不必要的字段查询(字段越少,join buffer中所缓存的数据就越多)

   in和exists函数

   in函数使用场景:如果子查询得出的结果集记录比较少，主查询中的表比较大的时候，应该使用in函数。
   exists特点：exists子句返回的是一个布尔值，如果有返回数据，则返回值是true，反之是false。如果结果为true；外层的查询语句会进行匹配，否则外层查询语句将不进行查询或者查不出任何记录
   
   in函数和exists的区别:
   如果子查询得出的结果集记录比较少，主查询的表比较大而且有索引，此时应该用in。
   如果主查询得出的结果集记录比较少，子查询中表的数据比较多，这时应该用exists。
   总结:in后面跟小表,exists后面跟的是大表。

   order by优化

   mysql两种排序方式：
   索引排序:通过有序索引进行顺序扫描直接返回有序的数据。
   文件排序(额外排序):指的是所有的不是通过索引直接返回排序结果的操作都是文件排序。
   order by优化的核心标准：尽量减少文件排序，通过索引直接返回有序的数据。
   1.只查询用于排序的索引字段，可以利用索引进行排序，最左原则。
   2.排序字段在多个索引中，无法使用索引排序。
   创建索引 KEY idx_name_age (NAME,age), KEY idx_salary (salary)
   EXPLAIN SELECT e.NAME ,e.name FROM employee e ORDER BY e.NAME,e.salary
   
   3.只查询用于排序的索引字段和主键，可以利用索引进行排序。
   4.查询主键之外的没有添加索引的字段，不会利用索引排序。
   5.排序字段顺序和索引列顺序不一致，无法利用索引排序。
   6.where条件是范围查询时，会使order by索引失效。
   注意：order by子句不要求必须有索引第一列，没有仍然可以利用索引排序。但是有个前提条件，只有在等值过来时才可以。
   7.升降序不一致，无法利用索引排序(order by排序字段要么全部正序排序，要么全部倒叙排序，否则无法利用索引排序。)

   Mysql事务与锁的优化

   ACID特性

   原子性：事务是一个原子的操作单元，其对数据的修改，要么全部执行，要么全部不执行。
   持久性：指的是一个事务一旦提交，他对数据库中的数据的改变就是永久性的，后续的操作或者是故障都不应该对其有任何的影响，不会丢失。
   MYSQL持久化的保证:redo log在系统进行崩溃恢复的时候，可以修复数据，从而保证事务数据的持久性。
   隔离性：指的是一个事务的执行不能被其他事务干扰，也就是说一个事务内部的操作和使用数据对其他的事务隔离。
   不考虑隔离性会引发的问题：
   1.脏读：一个事务读取到了另一个事务没有提交的数据(使用到了脏数据)
   2.不可重复读：一个事务多次读取同一行记录的结果不一样，后面跟前面读取的不一致。
   3.幻读：一个事务中多次按相同的条件查询，结果不一致。

InnoDB支持隔离性有4种，从低到高，读未提交，读已提交，可重复读，可串行化。
一致性：指的是事务开始之前和事务结束之后，数据的完整性限制没有被破坏。
一致性包括两方面的内容：约束一致性(创建表结构时所指定的外键，唯一索引等约束。)、数据一致性(是一个综合性的规定，它是由原子性、持久性、隔离性共同保证的结果，不是单独依赖于某一项技术)

MVCC:多版本并发控制，指的是在数据库中为了实现高并发的数据访问，对数据进行多版本处理(每一条数据都有多个版本)，通过事务的可见性保证事务能够看到自己应该看到的数据版本。
MVCC好处：读不加锁，读写不冲突。
MVCC实现原理：每一条数据有多个版本，版本之间通过undo log链条进行连接，通过这样的设计方式可以保证每一个事务提交的时候，一旦需要回滚操作，可以保证同一个事务只能读取比当前版本更早提交的值，不能看到更晚提交的值。

每条数据都有两个隐藏字段：
trx_id:记录最近一次更新这条数据的事务id。
roll_pointer：回滚指针，指向之前生成的undo log

事务隔离级别

读未提交(RU)：一个事务可能会读取到另一个事务修改但是没有提交的数据。
读已提交(RC)：只能读取其他会话中已经提交的数据，解决了脏读，但是会发生不可重复读的现象，也就是可能在一个事务中两次查询的结果不一样。
可重复读(RR)：解决了不可重复读的问题，它确保了同一事务的多个实例在并发读取数据时，会看到相同的数据行。会出现幻读，幻读：当用户读取某一范围内的数据行时，另一个事务又在该范围上插入了新的数据行，当用户在读取该范围的数据时，会发现有新的幻影行。
可串行化：指的是所有的增删改操作串行化，通过强制的事务排序，解决互相冲突的问题，从而解决了幻读的问题。但是这个级别，可能会导致大量的超时现象发生，大量的锁冲突，效率低下。