MySQL实战45讲

2020/3月更新
https://www.bbsmax.com/A/D854r3rvzE/
这篇自连接的举例不错哦

三大范式：

1. 保证数据原子性。反例：地址信息粒度太粗

2. 保证是完全依赖主键。排除完全无关和部分相关。比如天气出现在用户的信息表，比如订单和产品的联合主键。订单金额和订单时间仅与“订单号”相关，与“产品号”无关
   这样就不满足第二范式的要求

3. 保证是之间完全依赖主键列。没有传递依赖。如订单中，下单者的性别，年龄，确实依赖于订单号。符合第二范式。但明显传递依赖了，直接依赖的是下单者

五大约束

---

语句优化：
如select 1代替select *,exists代替 in
优化limit和offset,MySQL的limit工作原理就是先读取n条记录,然后抛弃前n条,读m条想要的,所以n越大,性能会越差,代码如下:

优化前SQL:SELECT * FROM member ORDER BY last_active LIMIT 50,5

优化后SQL:SELECT * FROM member INNER JOIN (SELECT member_id FROM member ORDER BY last_active LIMIT 50,5) USING (member_id)

分别在于,优化前的SQL需要更多I/O浪费,因为先读索引,再读数据,然后抛弃无需的行,而优化后的SQL(子查询那条)只读索引(Cover index)就可以了,然后通过member_id读取需要的列.

只返回需要的数据

合并一些条件相同的操作

学会用执行计划(explain+语句)和show processlists;

---

一条sql执行慢的原因：

偶尔慢：数据库正在同步数据到磁盘，拿不到表锁或者行锁（用show process list查看当前状态）

一直都慢：

1. 语句写得不好，字段没有索引，只能走全表扫描
2. 字段有索引，但没用上 如where c-1=100就不行，c=101就可以
3. 函数操作导致没用上索引 如where pow(c,2)=100
4. 首字母模糊搜索也会失去索引走全表扫描

主键索引和非主键索引:主键索引存放整行字段的数据，非主键索引存放主键字段的值

一个索引上不同的值越多，也就是说基数越大，那走索引查越有优势

索引系统是通过部分遍历数据，即通过采样的方式，预测索引的基数的。所以可能由于统计失误，所以走了全表扫描

采样：InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。
优化器不止看扫描行数，还看是否回表

可强制走索引：select *from t force index(a) where 也可show index from t查询索引的基数和实际是否符合，不符合则analyze table t重新统计分析

explain可以查看SQL如索引是否满足需求
看慢查询日志可以优化 analyze table

还有可以使用前缀索引alter table add index index(emial(6))
1.从index2索引树找到满足索引值是’zhangs’的记录，找到的第一个是ID1；

2.到主键上查到主键值是ID1的行，判断出email的值不是’zhangssxyz@xxx.com’，这行记录丢弃；

3.取index2上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出ID2，再到ID索引上取整行然后判断，这次值对了，将这行记录加入结果集；

重复上一步，直到在idxe2上取到的值不是’zhangs’时，循环结束
也就是用前缀索引，定义好长度，既可以省空间，又可以和全字符串的索引一个效率。
但选得不好增加扫描行数
select count(distict email)as L from table;

但对身份证这种前序索引不适合的，用倒序存储，因为后几位区分度高。

---

innoDB

1. 不仅有表级锁，还提供行级锁，锁粒度小，写操作不会锁定全表，所以适合并发度高的场景
2. 提供了数据库ACID事务的支持
3. 不支持全文搜索，不保存表的行数
4. 使用聚簇索引

myISAM:

1. 不支持行锁
2. 不支持事务（事务也是在引擎层实现的）
3. 但保存表的行数，读操作远远多于写操作时，此引擎为首选。
4. 使用非聚簇索引

表锁和行锁：前者锁粒度大，锁冲突概率高，并发量低，开销小，加锁快。 后者全相反。

---

第1讲
分为SERVER层和引擎层
用户连接
查询缓存（有更新时，表上的所有缓存会失效，不适用于频繁更新的表）
语法解析器
优化器决定表里多个索引用哪个，表的连接顺序啊这些
权限验证

---

第2讲
一条查询语句怎么执行：
一条更新语句怎么执行：redo log（重做日志）是引擎层的日志，可以保证数据库重启，之前提交的数据不丢失，且是innoDB独有的； binlog（归档日志）是server层的日志，所有引擎可以使用

redolog是物理日志，记录事务操作对变化，记录变化后的值，不管事务是否提交都会记下。会用完，写满了要持久化然后擦除
binlog是逻辑日志，记录数据库执行都所有操作。可以追加

怎么恢复数据库到任意一秒：
找一个全量备份，然后取出那个时间后的binlog，一直放到误操作的那个时刻

---

第3讲
为何不推荐长事务：1长事务使得回滚空间要保留，占大量存储空间。2占用锁资源
事务隔离的实现：每条记录在更新的时候都会同时记录一条回滚操作。同一条记录在系统中可以存在多个版本，这就是数据库的多版本并发控制（MVCC）

存在视图的 2 种隔离级别：

1. 读提交
2. 可重复读
   读提交：在每一条 SQL 开始执行时创建视图，隔离作用域仅限该条 SQL 语句。

可重复读：事务启动时创建视图，因此，在事务任意时刻，对记录读取的值都是一样的。

---

第4讲 索引（也是在引擎层）
索引常见模型：哈希表，有序数组，和搜索树。

主键索引也叫聚簇索引，叶子节点存整行数据，非主键索引叶子节点存的是主键的值，再到主键索引的树去查。这个过程叫回表。
所以我们尽量使用主键查询。

主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小

B+树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数，让查询过程中少访问数据块。

N叉树的N可以调整吗？ ——5.6以后可以通过page大小来间接控制

---

第5讲
覆盖索引：select的数据列从索引中就能获得，索引覆盖了我们的查询需求，不用回表。
索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据

联合索引：通过身份证（主键）查姓名时，index(id,name)
有个最左前缀原则——如index(a,b,c)。当查询语句分别包含a，b或a，b，c都是可以走索引的，而a，c只有其中的a走索引。

mysql5.6引入了索引下推
筛选索引字段中不符合条件的数据，减少回表次数

为什么可能重建索引——索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。

---

第6讲
全局锁的典型场景是作全库逻辑备份：flush tables with read lock
表级锁：表锁（限定别人的读写，也限定了本线程接下来的操作）和元数据锁(访问表时自动被加上)
读锁间不互斥
MDL在事务提交后才释放

---

第7讲
行锁（引擎层）
锁是需要时加上，事务结束时才释放
如果事务要锁多个行，要把最有可能锁冲突的放在最后

死锁：

1. 等待超时，太长影响业务，太短容易误伤，万一只是锁等待呢
2. 一般用这种：设置检测死锁参数为on，然后主动回滚死锁链中的一条事务。
   但也有致命问题：这是一个O（N）的操作，可能没有死锁但是一直在耗费大量CPU资源检测
   解决办法是，并发控制做在数据库服务端，在更新相同行时，在进入引擎前排队

---

第9讲
InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。

changeBuffer只适用于普通索引，不适合唯一索引。以及适用于写多读少的场景，如账单类日志类。
因为唯一索引要判断key是否唯一，要将数据页读入内存，不如直接更新内存，用不上changebuffer

redo log 主要节省的是随机写磁盘的IO消耗（转成顺序写），而change buffer主要节省的则是随机读磁盘的IO消耗。

---

第11讲
第一种方式是使用倒序存储。如果你存储身份证号的时候把它倒过来存，每次查询的时候，你可以这么写：

mysql> select field_list from t where id_card = reverse(‘input_id_card_string’);
由于身份证号的最后6位没有地址码这样的重复逻辑，所以最后这6位很可能就提供了足够的区分度。当然了，实践中你不要忘记使用count(distinct)方法去做个验证。

第二种方式是使用hash字段。你可以在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。

mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);
然后每次插入新记录的时候，都同时用crc32()这个函数得到校验码填到这个新字段。由于校验码可能存在冲突，也就是说两个不同的身份证号通过crc32()函数得到的结果可能是相同的，所以你的查询语句where部分要判断id_card的值是否精确相同。

mysql> select field_list from t where id_card_crc=crc32(‘input_id_card_string’) and id_card=‘input_id_card_string’
这样，索引的长度变成了4个字节，比原来小了很多。

接下来，我们再一起看看使用倒序存储和使用hash字段这两种方法的异同点。

首先，它们的相同点是，都不支持范围查询。倒序存储的字段上创建的索引是按照倒序字符串的方式排序的，已经没有办法利用索引方式查出身份证号码在[ID_X, ID_Y]的所有市民了。同样地，hash字段的方式也只能支持等值查询。

它们的区别，主要体现在以下三个方面：

从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，而hash字段方法需要增加一个字段。当然，倒序存储方式使用4个字节的前缀长度应该是不够的，如果再长一点，这个消耗跟额外这个hash字段也差不多抵消了。

在CPU消耗方面，倒序方式每次写和读的时候，都需要额外调用一次reverse函数，而hash字段的方式需要额外调用一次crc32()函数。如果只从这两个函数的计算复杂度来看的话，reverse函数额外消耗的CPU资源会更小些。

从查询效率上看，使用hash字段方式的查询性能相对更稳定一些。因为crc32算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。

---

第12讲
脏页比例和read log写入速度
innodb_io_capacity*R%

---

第13讲
除非drop表，一般delete数据并不会减少存储空间。只是把数据页标为了可复用

新增数据，造成页分裂，前一个页的末尾也是会形成数据空挡的。

可以通过重建表收缩表空间。
alter table A engine=InnoDB
Online DDL

但有时候在重建表的时候，InnoDB不会把整张表占满，每个页留了1/16给后续的更新用。也就是说，其实重建表之后不是“最”紧凑的。反而空间会大。

---

第14讲
利用事务的原子性和隔离性保证计数准确。

count(*)、count(主键id)和count(1) 都表示返回满足条件的结果集的总行数；而count(字段），则表示返回满足条件的数据行里面，参数“字段”不为NULL的总个数
对于count(主键id)来说，InnoDB引擎会遍历整张表，把每一行的id值都取出来，返回给server层。server层拿到id后，判断是不可能为空的，就按行累加。

对于count(1)来说，InnoDB引擎遍历整张表，但不取值。server层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
count()做了优化，不取值
count(字段)<count(主键id)<count(1)≈count()，

MySQL系统高可用的基础，就是binlog复制

---

第15讲
order by
全字段排序：开辟buffer，确定放入的列名。找相应的id,然后去主键索引中找到符合条件的select的列，然后放入buffer中，从索引中取下一个主键id。重复前两步，直到不满足条件，最后把order by的字段排序。

单行长度太长时：
rowid排序——buffer里只放排序的字段和主键，最后再通过主键id把最后一个字段找一遍放入buffer中。从索引中取下一个主键id。重复前两步，直到不满足条件，最后把order by的字段排序。

---

group by当聚集函数与非聚集函数在一起时，一定要用group by