MySQL知识汇总

一、索引

作用：提升查询效率

本质：是一种数据结构

索引的特点：需要占用存储空间

1、索引的类型

哈希类型的索引【只查询某一个值时，时间复杂度为：O(1)】
普通的二叉树【左小右大】
平衡二叉树【降低树的高度】
B 树：阶【一个节点拥有最多子节点的个数】，既存储数据，又存储索引，还存储指针
B+树：【只有叶子结点才存数据】，一页默认是 16Kb，假设一页能存 16 条数据，数据最多能存储【阶数的（树高-1）次方*16】
聚合索引和非聚合索引

关于 B+树可以储存的数据：点我查看

2、聚集索引与非聚集索引的区别

一个表中只能拥有一个聚集索引，但能拥有多个非聚集索引
聚集索引中键值的逻辑顺序决定了表中相应行的物理顺序；而非聚集索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，非聚集索引就像新华字典的拼音字典，他结构顺序与实际存放顺序不一定一致。
聚簇索引的叶节点就是数据节点；而非聚簇索引的叶节点仍然是索引节点，只是有一个指针指向对应的数据块
聚集索引：物理存储按照索引排序；非聚集索引：物理存储不按照索引排序；

更多关于两者的介绍：聚簇索引与非聚簇索引

3、为什么选择 B+树？

普通二叉树：普通二叉树存在退化成链表的情况，此时查询就相当于全表扫描了。

平衡二叉树：为什么不是普通二叉树？如果二叉树特殊化为一个链表，相当于全表扫描。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。

为什么不是平衡二叉树呢？我们知道，在内存比在磁盘的数据，查询效率快得多。如果树这种数据结构作为索引，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块，但是平衡二叉树可是每个节点只存储一个键值和数据的，如果是 B 树，可以存储更多的节点数据，树的高度也会降低，因此读取磁盘的次数就降下来啦，查询效率就快啦。

为什么不是 B 树而是 B+ 树呢？B+ 树非叶子节点上是不存储数据的，仅存储键值，而 B 树节点中不仅存储键值，也会存储数据。innodb 中页的默认大小是 16KB，如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的 IO 次数有会再次减少，数据查询的效率也会更快。B+ 树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的，链表连着的。那么 B+ 树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。

二、SQL 优化

1、explain 关键字

作用：仅仅针对 select 查询，可以显示出查询的大概的执行流程和索引的命中情况

核心点:

id
- id 相同: 从上到下顺序执行
- id 不同: 由大到小执行
- id 相同和不同同时存在: 综合以上两个
type
- All：从硬盘全表扫描
- index：从索引全表扫描
- range：检索给定范围
- ref：连接匹配条件
- const：通过索引一次就找到了
- system：表仅有一行记录，是 const 类型的特例
possible_key：可能命中的索引
key：实际使用的索引
key_len：索引使用的字节数（计算）
- 数据的长度
- 数据的类型：int=4
- 是否为空，为空+1
- 字符编码集：utf-8 乘 3，GBK 乘 2
rows: 预估值,不是准确值
Extra：MySQL 解决查询的详细信息
- Using where
- Using temporary
- Using filesort

2、索引命中和失效的情况

假设有一个组合索引：index(a,b,c)，即有有 a，ab，abc 三种组合

2.1、最左法则

2.2、模糊匹配和函数

使用模糊匹配like 时：前缀匹配【比如 abc%】可以触发索引，后缀匹配不能触发索引
where 后面加函数查询：索引完全失效（select 后面可以加）

2.3、范围条件

范围查询右边的列，不能使用索引，所以应将查询条件放置 where 语句的最后面

2.4、is null 和 is not null

更多详见：MySQL 中 SQL 语句和索引的优化

3、关联查询的索引优化

在 sql 优化中，永远是以小表驱动大表。（因为从小表获取数据要比从大表获取数据快）
- 当使用 left join 时，左表是驱动表，右表是被驱动表
- 当使用 right join 时，右表是驱动表，左表是被驱动表
- 当使用 join 时，mysql 会选择数据量比较小的表作为驱动表，大表作为被驱动表
保证被驱动表的join字段被索引
- join 查询在有索引条件下，驱动表有索引不会使用到索引，被驱动表建立索引会使用到索引

更多详见：关联查询优化

4、排序的优化

order by 时不限制索引失效
order by 时顺序错误，索引失效
order by 时规则不一致，可能导致索引失效（默认升序排序，有的字段升序，有的降序，规则不一致）

关于 GROUP BY，group by 使用索引的原则几乎跟 order by 一致：GROUP BY 优化

5、慢查询

如何定位慢查询语句？以及如何优化？

去数据库查询慢查询的日志的配置/去配置文件中看慢查询的日志的配置
分析慢查询日志文件中的内容
优化—->explain—>sql 优化的知识进行优化

慢查询的默认时间是多久: 10s

6、工作中如何优化 SQL？（总结）

进行慢查询分析定位，使用 EXPLAIN 关键字，进行分析

分析语句，是否加载了不必要的字段/数据
分析 SQL 执行句话，是否命中索引等
如果 SQL 很复杂，优化 SQL 结构
如果表数据量太大，考虑分表

三方面：优化表结构（提高查询效率），优化查询语句（避免索引失效）和优化索引

1、表结构优化（应该和自己关系不大）

（1）尽量使用数字型字段

引擎在处理查询和连接时会逐个比较字符串中每一个字符，而数字型的字段只需要比较一次，这样不仅可以提高查询的性能，还能减少储存开销

（2） varchar 和 char 恰当选择

char 表示定长，长度固定，varchar 表示变长，即长度可变。char 如果插入的长度小于定义长度时，则用空格填充
varchar 小于定义长度时，还是按实际长度存储，插入多长就存多长

因为其长度固定，char 的存取速度还是要比 varchar 要快得多，方便程序的存储与查找；但是 char 也为此付出的是空间的代价，因为其长度固定，所以会占据多余的空间，是以空间换取时间效率。

varchar 则刚好相反，以时间换空间。但是当你的数据库很大时，这种内存和磁盘空间的节省会变得非常重要。

因此当要保存固定长度的字段时用 char，如果字段内容的长度不是固定的常数，那就用 varchar

（3）尽量不要出现大字段

一页默认是 16K，尽量不要出现跨页查询，会降低查询效率，可以使用扩展表的方式，比如 text 字段。

2、优化查询语句

主要是避免索引失效（还有关联查询的优化、排序的优化等），其次是避免加载了不必要的字段/数据

应尽量避免在 where 子句中使对索引列运算，比如!=或<>（不等于），都会造成索引失效
应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描。而改用 union 之后，性能就大大提高了
任何查询也不要出现 select *，而是指定需要的字段

3、索引优化

对作为查询条件和 order by 的字段建立索引
避免建立过多的索引，多使用组合索引

更多索引优化见：索引创建原则

7、sql 的执行顺序

比如如下查询

SELECT
  A.id,
  B.id
FROM
  A
  LEFT JOIN B ON A.id = B.id
WHERE
  A.id = 123
ORDER BY
  A.NAME
  LIMIT 0,
  10;

执行顺序

A【FROM】
ON A.id = B.id【ON】
B【JOIN】
(A+B)C（AB 表合在一起的临时表 C，C 里面的数据量一定是等于 A 里面的数据量，可能有 null）
C 表中满足 where 条件的数据进行筛选【WHERE】
C.aid,C.bid【SELECT】（C 表临时表的 A 表 id 字段和 C 表临时表的 B 表 id 字段）
Order By【ORDER BY】
limit 【LIMIT】

原因：因为 A 表 id 字段肯定不为空，而对应的（A.id = B.id ）B 表 id 字段就可能为空，所以可能有 null，也是因为 A 是驱动表，所以临时表 C 里面的数据量一定是等于 A 里面的数据量。

三、MySQL 的逻辑架构

连接层：负责与外部的服务构建连接
服务层：查询缓存，SQL 语句的验证解析和优化
引擎层：在引擎层确认存储引擎，负责 MySQL 中数据的存储和提取
储存层：数据存储

详细可以看：MySQL 架构概览

一条 SQL 语句在 MySQL 中如何执行的？

权限检查，没有权限直接返回错误信息，有权限优先查询缓存，命中就直接返回结果
没有缓存，Server 层会进行 SQL 解析和预处理
最后 优化器 会确定执行计划并进行权限校验，有权限的话就会调用调用存储引擎来执行查询，并将结果返回给客户端

详细可以看：mysql 的查询流程

1、mysql 集群

1.1、主从模式

数据同步: 默认是异步复制

流程

数据写入主库—>binlog
主库有一个守护线程，通知从库进行同步
从库收到同步内容的请求了以后，使用 slaveI io 从库读取自己上一次同步到哪里了
去主库中获取当前最新的位置到哪里，计算出当前本次同步需要同步的数据有多少
执行同步了，同步完成了以后告诉主库同步完成
不影响并发发送来的 sql 命令

半同步复制

数据写入主库—>binlog
主库有一个守护线程，通知从库进行同步
从库收到同步内容的请求了以后，使用 slaveI io 从库读取自己上一次同步到哪里了
去主库中获取当前最新的位置到哪里，计算出当前本次同步需要同步的数据有多少
执行同步了，同步完成了以后告诉主库同步完成
可以执行下一个命令
超时时间 10s，半同步阻塞超时以后,会变成异步同步
从服务重启或回复正常以后，又变回半同步复制

注意事项：企业中，一般将主从的节点或者服务器放在一个局域网中，这样能提升同步的效率，降低出错的概率.

1.2、主主模式

相互主从

1.3、主从级联模式

一主一从，一从再多从

1.4、主主从级联模式

多主多从，一从多从

2、mycat 中间件

2.1、作用

帮我们将数据写入到不同的表或者不同的数据库去
帮我们从不同的表或者不同的数据库中查询数据

2.2、配置文件

在 Mycat 有核心三个配置文件，分别为：sever.xml、schema.xml、rule.xml

sever.xml：Mycat 服务器参数调整和用户授权的配置文件（应用连接 mycat 的话，也需要设置用户名、密码、被连接数据库信息，要配置这些信息的话，修改 server.xml）
schema.xml：管理着 MyCat 的逻辑库、表、分片规则、 DataNode 以及 DataSource【物理数据库信息】
rule.xml：分片规则的配置文件，分片规则的具体一些参数信息单独存放为文件，也在这个目录下，配置文件修改需要重启