MySQL进阶

仅用于个人学习进步，如侵权请联系删除！

一、存储引擎

1.mysql存储引擎（存储数据、建立索引、读写数据的实现方式）

在MySQL5.5版本之后，默认使用的是InnoDB存储引擎。在此之前默认使用的MyISAM存储引擎。

2.InnoDB存储引擎、MyISAM存储引擎、Memory存储引擎的区别？

InnoDB：支持事务、行级锁、外键

MyISAM：不支持事务、不支持外键、不支持行级锁，但支持表锁

Memory：表数据只能存储在内存当中，所以这种表只能用于临时表使用

3.InnoDB、MyISAM存储引擎如何选择？

主流的的还是InnoDB，因为支持事务，行级锁能够保证并发情况下的数据一致性。数据会有并发情况就要考虑InnoDB存储引擎

二、索引

1.索引实质是一种能够实现高效查询的数据结构

使用索引的优点：能够提高数据查询的速度

使用索引的缺点：维护索引是会占用空间的，能够提高查询速率，但是会降低更新表速度

2.索引类型（主要是B+Tree、Hash索引）

3.这部分可以学习下二叉树、红黑树（自平衡二叉树）、B-Tree（多路平衡查找树）、B+-Tree

（1）二叉树：每个节点下只会有两个子节点，按照顺序插入会造成链表情况，

（2）红黑树（自平衡二叉树）：能够解决二叉树形成链表情况，但是如果层级太多，速度也会慢

（3）B-Tree（多路平衡查找树）：至多有5个子节点，根节点有4个key，5个指针。当节点有5个key时，中位数会向上分裂，最终实现一个节点只会有4个key

熟悉B-Tree数据结构：在https://www.cs.usfca.edu/~galles/visualization/BTree.html这个地址操作一下，熟悉一下，感受一些魅力（太妙了）

（4）B+-Tree

与B-Tree的区别：1.如下图，所有的非叶子节点的数据都会出现在叶子节点中（其实就是所有数据都会出现在叶子节点，注意所有的叶子节点并不是都会出现在根节点或中间节点当中），2.并且叶子节点中的数据形成了单向链表。

（5）MySQL对B+Tree的优化

如下图，额外增加指针，使叶子节点本身形成的单项链表 形成了双向链表。

1. 标准 B+ 树原始设计

所有叶子节点本身就带指针，构成单向链表：只存 后继指针，从左往右串起来，只能从头往后遍历。

2. MySQL InnoDB 做的优化

InnoDB 在叶子节点上额外加了前驱指针：

• 原有：下个叶子节点指针（向后）

• 新增：上个叶子节点指针（向前）

直接把叶子节点从 单向链表 → 双向循环链表。

(6)、总结：（注意：前面学习时，B-Tree和B+Tree都可以设置成5个节点，4个key，凭什么说B-Tree一页存储的key会减少。那是因为在B-Tree结构中，每个节点都会保存数据。理论上能够放入4个key，但是实际上会根据数据大小，有可能会小于4个key。B+Tree非叶子节点不放入数据，几乎都能装满理论数量的key）

Hash索引只支持精准查询

三、索引分类

就是说

聚集索引一定得有，依次是主键索引、第一个唯一索引、最后是InnoDB自动生成的一个rowId隐藏的聚集索引。聚集索引特别之处是，叶子节点会放入该行的所有数据

二级索引：叶子节点放入只会设置索引的该字和该行的id

当执行查询sql语句时，流程是什么样的？如下图select * from user where name ='Arm'

会先去二级索引根据name='Arm'找到id，拿到id再去聚集索引找到这一行的所有数据，这一操作叫做回表查询。

如果select * from user where id =10 (意思是直接走聚集索引)，应该就是可以直接拿到数据，不需要回表查询了

MySQL B+Tree结构不同高度能够存储多少数据量？

高度为2时，能够存储1万8千条数据量

高度为3时，能够存储2200万条数据量。就能够满足千万级数据量需要了。

再往上就该考虑分库分表了。

四、索引语法

创建普通索引：create index idx_user_name（给索引起个名字） on table（表名）（name）

创建联合索引：create index idx_user_name_age_sex（给索引起个名字） on table（表名）（name，age，sex）

创建唯一索引：create unique index idx_user_name（给索引起个名字） on table（表名）（name） 如果不加unique，默认的就是普通索引

查看表的索引 show index from table

删除索引 drop idx_user_name（索引名） on table

五、SQL性能分析

1.通过show global status like ‘Com_______’ ，能够看到查询、增、删、改执行操作的次数

2.慢查询日志（找到耗时过长的sql）

通过在配置文件开启，默认时间10秒，会生成一个slow_query_log慢查询日志，该日志会记录耗时过长的sql

3.profile（即使没达到慢查询阈值的 SQL，也能精准定位它慢在哪里）

4.explain

优化sql时，尽量把type类型向前推进

重点关注的是：以下方框和红线部分

type字段是判断查询性能的核心指标，type有明确的性能优先级，从最优到最差大致为：system > const > eq_ref > ref > range > index > ALL，优化目标是：让type尽量往左边靠

possible_keys：哪些索引可能能用上

key：MySQL 实际用了哪个索引

key_len：告诉你用了索引的多少个字节。越长 = 用的索引字段越多 = 索引越精确

六、索引使用

1.索引的使用原则

（1）最左前缀法则：在联合索引中，必须有最左侧字段，如果没有最左侧字段，该联合索引失效，可以没有右侧的字段。如果跳过的话，则后面的字段索引失效。（show index from table查看联合索引字段的顺序，也就是从左到右的顺序。）

注意：最左侧法则并不是说最左侧字段一定要在左边，而是最左侧字段一定要存在，即使不放在最左侧，只要存在也是能够走索引。

（2）范围查询会导致索引失效：在联合索引，如果业务允许的话，范围查询的字段最好能够带上等于号，使范围查询右侧的列也能走索引

（3）函数运算会导致索引失效：如substring截取字段，就会导致索引失效

（4）字符串字段不加单引号，索引会失效

（5）模糊查询：%放在后面，索引不会失效。%放在前面，索引会失效。

如 like 'AAA%' 不会失效，like '%AAA' 索引会失效

(6)or：只有or两边都是索引字段，才会走索引，如果一侧是索引字段，一侧不是，索引会失效

（7）MySQL会自己评估要不要走索引

（8）is null、is not null 是可能会不走索引的，具体根据数据表中数据

2.sql提示

use index: 建议 MySQL 使用某个索引（只是建议，不是强制）

ignore index: 强制 MySQL 不使用某些索引

force index: 强制 MySQL 必须使用指定索引（最常用！）

覆盖索引以及回表查询：我的理解就是select 查询的字段 会不会再走一遍回表查询操作

假如，有个联合索引关联name、age两个字段，select id , name ,age from table where xxx时，此时走二级索引时，是能够查到所需要的id name age值；假如，只有name 单列索引，age字段没有索引，此时也是走二级索引。但是二级索引是没有age字段值的，只能取id再回表到聚集索引根据id查，也就是说多了一步操作，但其实也很快的。

3.索引的使用

更加推荐联合索引，因为联合索引能把select查询的字段 走一次索引就能查出。如果是单列索引可能还需要回表查询。

4.索引的设计原则

总结：

MySQL 存储引擎的区别

B+Tree 和 Hash 索引结构的区别？

B+Tree：叶子节点包含所有数据；叶子节点形成双向链表

Hash：hash表（数组+链表）

七、SQL优化

1.插入数据

insert优化：批量插入。

手动提交事务：因为每次操作，MySQL都会默认提交。

主键顺序插入

大批量插入数据：MySQL提供的load指令，

2.主键优化

3.order by优化

4.group by优化

5.limit优化

6.count优化

7.update优化