# 优化SQL
[TOC]
## 1.必须要了解的基础知识
相关阅读：
1. 为什么auto-increment会不连续
https://draveness.me/whys-the-design-mysql-auto-increment/
### 1. 索引
https://www.zhihu.com/question/26113830
索引就是一列可以用来快速查找数据的index。简单理解就是，如果我们把数据拍成一排（数组或者list），那么查找某个数据就会有n的时间复杂度，但是如果我们使用二叉查找树，那么就会把插入查找时间降到log（n）.

#### 1.2进阶：聚簇索引和非聚簇索引
相关阅读：https://cloud.tencent.com/developer/article/1541265
**通俗点讲**
* 聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据
* 非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因
### 2. MySql使用的是InnoDB数据结构（B+）树
https://www.zhihu.com/question/26113830
因为二叉查找树存在极端情况：比如下面这种。该状态下的查找效率低，所以我们想要提高查询效率，我们所生成的二叉树应该又尽量又矮又胖，所以就用到了二叉平衡树。

**二叉平衡树**（是一种二叉排序树，其中每一个节点的左子树和右子树的高度差最多等于1）。
详细理解：https://blog.csdn.net/qq_29542611/article/details/80136574
二叉平衡树依旧有缺陷，因为二叉树的限制，每个根节点至多只有两个子节点，所以一旦数据量过多，所生成的树依旧会非常高（层数多），像我们之前所说的，我们希望我们所存储数据的树“又矮又胖”，那么就需要打破每个根节点至多只有两个子节点的限制。B树就诞生了。

**B树**
理解阅读：https://zhuanlan.zhihu.com/p/27700617
**B+树**
目前还不理解，因为对于B树的缺点没有办法深入理解。

**容易理解阅读（主）**：https://segmentfault.com/a/1190000020416577
B+树相对于B树有一些自己的优势，可以归结为下面几点。

* 单一节点存储的元素更多，使得查询的IO次数更少，所以也就使得它更适合做为数据库MySQL的底层数据结构了。
* 所有的查询都要查找到叶子节点，查询性能是稳定的，而B树，每个节点都可以查找到数据，所以不稳定。
* 所有的叶子节点形成了一个有序链表，更加便于查找。

### 3.MySQL binlog的作用，以及三种不同的binlog格式
原文阅读：https://zhuanlan.zhihu.com/p/33504555
Mysql binlog日志有ROW,Statement,MiXED三种格式；可通过my.cnf配置文件及 == set global binlog_format='ROW/STATEMENT/MIXED'== 进行修改，命令行 == show variables like 'binlog_format'== 命令查看binglog格式；。

* Row level: 仅保存记录被修改细节，不记录sql语句上下文相关信息优点：能非常清晰的记录下每行数据的修改细节，不需要记录上下文相关信息，因此不会发生某些特定情况下的procedure、function、及trigger的调用触发无法被正确复制的问题，任何情况都可以被复制，且能加快从库重放日志的效率，保证从库数据的一致性
**缺点**:由于所有的执行的语句在日志中都将以每行记录的修改细节来记录，因此，可能会产生大量的日志内容，干扰内容也较多；比如一条update语句，如修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中，实际等于重建了表。
**tip**: - row模式生成的sql编码需要解码，不能用常规的办法去生成，需要加上相应的参数(--base64-output=decode-rows -v)才能显示出sql语句; - 新版本binlog默认为ROW level，且5.6新增了一个参数：binlog_row_image；把binlog_row_image设置为minimal以后，binlog记录的就只是影响的列，大大减少了日志内容
* Statement level: 每一条会修改数据的sql都会记录在binlog中优点：只需要记录执行语句的细节和上下文环境，避免了记录每一行的变化，在一些修改记录较多的情况下相比ROW level能大大减少binlog日志量，节约IO，提高性能；还可以用于实时的还原；同时主从版本可以不一样，从服务器版本可以比主服务器版本高
**缺点**：为了保证sql语句能在slave上正确执行，必须记录上下文信息，以保证所有语句能在slave得到和在master端执行时候相同的结果；另外，主从复制时，存在部分函数（如sleep）及存储过程在slave上会出现与master结果不一致的情况，而相比Row level记录每一行的变化细节，绝不会发生这种不一致的情况
* Mixedlevel level: 以上两种level的混合使用经过前面的对比，可以发现ROW level和statement level各有优势，如能根据sql语句取舍可能会有更好地性能和效果；Mixed level便是以上两种leve的结合。不过，新版本的MySQL对row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更；因此，现在一般使用row level即可。
选取规则如果是采用 INSERT，UPDATE，DELETE 直接操作表的情况，则日志格式根据 binlog_format 的设定而记录
如果是采用 GRANT，REVOKE，SET PASSWORD 等管理语句来做的话，那么无论如何都采用statement模式记录

### 4. EXPLAIN关键词
推荐阅读：https://zhuanlan.zhihu.com/p/281517471

## 2.优化Hibernate查询速度

## 3.优化Mysql
### 1. 通过索引
* 覆盖索引：
如果在普通索引树上的查询已经直接提供了结果，不需要回表操作，这样的普通索引叫做覆盖索引。覆盖索引的使用可以显著提高查询效率，是常见的MySQL性能优化手段。

* 索引的最左前缀原则：
在联合索引的情况下，不需要索引的全部定义，只要满足最左前缀，就可以利用索引来加快查询速度。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。最左前缀原则的利用也可以显著提高查询效率，是常见的MySQL性能优化手段。

* 索引下推：
在MySQL5.6之前，只能从根据最左前缀查询到ID开始一个个回表。到主键索引上找出数据行，再对比字段值。MySQL5.6引入的索引下推优化，（联合索引前提）可以在索引遍历过程中，对索引中包含的其余字段先做判断，直接过滤掉不满足条件的记录，减少回表次数，提升查询效率。

## Mysql相关知识
### 1. IFNULL也有不起作用的情况
阅读：https://blog.csdn.net/weixin_40803257/article/details/88691390?utm_medium=distribute.pc_relevant.none-task-blog-2~default~baidujs_title~default-2&spm=1001.2101.3001.4242.1
简单概括就是：如果要使IFNULL起作用，则起码需要有一条reocord返回。如果连record都没有，则返回null

### 2. bit(1)与bit(2)
之前我们知道，bit(1)与bit(2)并无实质区别，区别仅仅只是显示长度，但是就是这个显示长度的问题。bit数据是二进制保存的，如果长度为1，那么DB中只能保存0或1，但是如果长度为2，就能保存4种不同的数据了[b'00', b'01',b'10', b'11']。
在java中的返回值就会发生变化。如果DB中所储存的1>x小于1，那么就会返回1或者0（boolean type返回true或者false），如果大于1(x>1)，那么就会返回二进制代码，这个时候就会报错:
[B cannot be cast to java.lang.Boolean
[B cannot be cast to java.lang.otherType。

相关阅读：What kind of Java type is "[B"?https://stackoverflow.com/questions/4606864/what-kind-of-java-type-is-b