Mysql总结

Mysql结构：

        客户端，server层，存储引擎层。server层包括：连接器（建立连接），分析器（对SQL语句进行词法分析，语法分析），优化器（做优化，选择索引），执行器（执行语句，从存储引擎层拿到结果）。 存储引擎层：数据库的实现，数据存储的地方，提供读写接口。

Mysql的读写：

        Mysql并不是直接对磁盘进行IO，而是会把磁盘数据分页放入内存，通过对内存的操作来提高效率。缓存机制并不全是优点，如果某个表的结构经常进行修改，那么不建议使用缓存，因为每次结构修改都会删除原有缓存并重新建立。

        select语句：从内存（缓存机制）中通过where条件找数据，如果建索引就走索引，没索引就全表扫描，然后找到该值对应的id（主键）值，如果没有显式的创建主键，MySQL会默认以隐藏字段row_id作为主键，然后通过主键再去表中扫描数据，返回结果。查到主键，再查表的这个过程成为“回表”。为了避免“回表”产生的性能损耗，可以通过建立合适的索引来解决。举个例子:select name from t where age = 20; 可以建立 index(age,name)索引，走age索引后，查到的数据直接就包含了name字段，就可以直接返回，不用“回表”。但同时要考虑建立索引的维护工作。

        update语句：首先从数据库中查询有无该值，查询过程同select，获得该数据后，执行更新操作，数据被更新后，并非直接写入磁盘。首先，将新行数据写入内存中，然后将此次操作写入redo log中，再将此次操作写入bin log中，以上操作完成后，commit提交，此次更新完成。

        insert/delete语句：一个现象：delete删除了表的数据，但是表占用的存储空间并没有变小。在执行删除语句的时候，innodb会把该数据标记为“删除”，我理解为“逻辑删除”。这么做的原因是：如果以后还要在对应位置插入新的元素，就可以复用该位置的空间。同理，删除整个页，就会把整个页标志为”逻辑删除“。insert语句，如果是随机添加，有可能在复用空间中产生”分裂“，同时就可能造成更多的”空洞“。如果把这些”空洞“给清除掉，可以压缩空间。方法：重建表。

Mysql的日志系统：

        redo log：为了防止MySQL的每次写操作都要立刻持久化到磁盘，MySQL使用redo log日志，来暂时的记录每次写操作，在合适的时候，MySQL会将redo log中的操作更新到磁盘中，完成数据的更新，这个过程称为 flush（刷盘）。如果写redo log 的过程中，系统崩溃会丢失数据吗？不会，此时崩溃，事务还未提交，就会回滚，不会影响数据。

                什么时候会进行flush？

                        1.redo log满了，redo log可以理解为一个环形结构，写数据会占用redo log的容量，flush会释放redo log的容量，如果一直写数据，导致redo log满了，就会进行flush。

                        2.内存满了，内存不够用了，需要新的内存页，此时就需要淘汰掉一些旧的内存页，将旧的内存页数据刷盘后，才可以淘汰。

                        3.MySQL认为系统空闲，一段时间内没有新的读写操作，就会进行刷盘。

                        4.关闭MySQL时，会先执行flush操作，防止数据丢失。

        bin log：记录了MySQL对数据库执行更改的所有操作，若操作对数据库本身并无变化，仍然会记录。为什么会有bin log？1.用于归档，因为redo log是循环使用的，只能恢复一段时间内的数据，而bin log使用的是追加写入，不会丢数据。

                bin log的三种格式：

                        statement格式：该模式下bin log中记录的是SQL语句的原文，包括注释，整体上呈现为一个事务的形式。优点：占用空间小 。缺点：主备模式下，一条语句，主数据库使用了A索引，查到了数据，备库同步到这条语句时，可能用B索引，导致查到的数据不一致。

                        row格式：该模式下，bin log里没有SQL语句的原文，替换为了两个event，Table_map 和 XXXX_rows，前者说明操作的是哪个表，后者说明执行某个操作update/delete/insert等。优点:不会出现数据不一致问题。缺点：如果是一个长事务，设计大量数据，这个格式很废空间。

                        mixed格式：两者混合，MySQL自己判断这条语句是否会发生主备不一致，从而选择使用row或statement格式。

                        设置为row格式的好处：恢复数据方便，row记录的是所有修改的数据，如果发现删错/添错数据了，直接把bin log中的delete/insert改为对应的相反操作即可。

                redo log 与 bin log 区别：

                        1.redo log时innodb引擎的东西，而bin log是MySQL的server层实现的，所有引擎通用。

                        2.redo log是物理日志，bin log是逻辑日志。

                        3.redo log是循环写，即会删除早期数据，而bin log是追加写，不会删除。

        提交：MySQL使用两阶段提交的方式，防止数据丢失。即：先对redo log进行第一段提交，进入prepare状态，然后写bin log，最后第二阶段共同提交。如果先写redo log再写bin log，bin log没写完系统崩溃，虽然数据可以恢复，但bin log中没有这条记录，以后在恢复临时库时就会丢失数据。如果先写bin log再写redo log，bin log虽然写完了，但是redo log没写完崩溃了，虽然事务没提交，但是bin log中多了一条数据，之后恢复临时表时就会多一条数据。

        undo log：用于事务回滚时数据恢复操作，事务中每执行一条delete语句时，undo log中就会添加一条相反的insert语句，反之亦然，这样，当事务异常终止后，可以通过执行undo log中的语句将数据恢复到事务未启动时的状态。

Mysql索引优化：前面提到了覆盖索引，用来防止回表查询，即创建where判断字段和返回结果字段组成的联合索引，查询之后直接返回。

        通过最左前缀原则设计更合理的索引，主要判断依据：索引的复用能力。如果通过改变索引顺序可以少维护一个索引，那就改变。

        占用空间方面的思考，如果两个字段作为索引区别不大，可以优先考虑占用空间小的字段。如果是个长字段需要添加索引，可以考虑前缀索引：取该字段的前几个字符作为索引，主要判断依据：字段区分度，如身份证后4位，在添加数据时可以逆序添加，然后使用前缀索引；或者在表上额外加个hash字段，用来存储长字符串的hash值作为索引。

        普通索引和唯一索引的选择：唯一索引的优点：查到数据后因为是唯一的所以就不用继续向下查，而普通索引可能有多个符合条件的值，会继续向下查询判断。会有效率问题吗？几乎不会，因为MySQL是按页查询的，一页会有很多数据，而该页很可能就包含了普通索引的所有值。同时，普通索引通过change buffer机制，提高了自己update数据的效率。

        change buffer：MySQL更新数据时，如果数据在内存中，就直接更新，否则会在change buffer中记录这个更新操作，而不是去进行磁盘IO，只有等到下次需要读取该数据时，会执行change buffer中的多个更新操作，达到减少IO次数的效果。读多写少的场景，不建议用。

        change buffer 和 redo log 区别：前者降低读消耗，后者降低写消耗。

       MySQL什么时候不走索引：

                1.查询时在索引列上进行了函数计算。

                2.数据类型发生隐式转换，底层还是调用了类型转换函数，回归情况1.

                3.前导模糊查询，如 like "%刘"，因为第一个字符就是模糊查询，没法走。

        MySQL选错索引的问题：回到最开始MySQL的整体结构，优化器是选择索引并执行优化的组件。而“扫描行数”是优化器判断的重要条件，扫描行数越少，优化器就认为该索引越高效。即：如果走某个索引，还要去主索引回表查询，这个过程优化器也会计算进去。而如果直接走主键索引，即使查询的行数较多，也有可能效率更高。优化器选择第二种也合理。可以使用 force index 来强迫innodb来选择该索引。

COUNT * 函数问题：

        MyISAM引擎，会专门记录一个总行数，执行count函数时可以直接返回，而Innodb引擎会遍历整张表，每查到一条记录就将计数器加一，最终返回值。当然，如果加了where条件判断语句，MyISAM也不会直接返回。Innodb这么设计的理由：默认可重复读的事务等级，每个记录都要判断自己是否对当前会话可见，所以要一行行的判断并返回。一些解决方案：专门记录一个计数值。

        count 1 * id 的区别:

                count(id) 取出每一行的id，然后返回给server，由server判断是否为空，（id）一定不是空，所以直接累加计数。

                count(1)：遍历整张表，但不取值，对于每行记录，server层都放一个“1”进去，然后累加。

                count(字段)：判断是否定义为not null，如果是not null，同id，否则要取值判断，不是null才进行累加。

Order By 排序：sort_buffer_size 字段控制了MySQL分给缓冲区的内存大小，如果返回的数据集小于这个大小，就在内存中执行排序，否则需要开辟磁盘额外空间辅助排序。

        全字段排序：即正常排序逻辑，根据where条件查询到行的所有需要的字段，存入sort_buffer，最终对sort_buffer中的数据按照排序字段来排序。

        row_id排序：如果查询要返回的字段太多，全字段排序可能会导致内存放不下，从而分很多的临时文件，性能下降。而row_id排序通过where查询条件只返回排序字段(order by xx)和id的值，排序之后通过id值回表二次查询客户端需要的那些字段。节约了空间，但是多了一轮查询。

        除非空间确实不够，不然都选择全字段排序。

rand() 随机查询问题：

        mysql> select word from words order by rand() limit 3; 示例代码

        rand函数的执行流程十分复杂：1.创建一个临时表，字段R用来记录rand产生的随机小数，W用来记录查询的值。2.从目的表中按主键顺序查出所有值，每个值都rand生成一个0-1的随机小数，放入临时表。3.对临时表通过R的值排序。4.初始化sort_buffer，存入临时表中的R和位置信息（用来找到数据）5.对sort_buffer排序，排序完成后根据limit的值返回前x个数据。

        不论是使用哪种类型的临时表，order by rand()这种写法都会让计算过程非常复杂，需要 大量的扫描行数，因此排序过程的资源消耗也会很大。

        优化算法1：随机排序法：取主键id最大和最小两个值，生成一个最大最小中间的随机数，取第一个不小于这个数的id。

        优化算法2：算法1基础上优化：如果id不是有序递增，那这就不是个可靠的随机函数。取整个表的行数，产生不大于行数的随机数，执行limit(x,1)返回不大于随机数的第一个id。

        优化算法3：算法2优化：limit函数算法是通过顺序取值后，丢到前x个，取后边n个，查询行数比较多。取行数，产生3个不大于行数的随机数，执行三个limit(x,1)得到三行数据

查询慢问题：查询长时间不返回：可能表被锁了，等MDL锁。可能正在flush刷盘，等刷盘结束。可能行被锁了，等行锁。情况2：查询行数太多且没加索引，建议加索引。情况3：查询一行，但仍然慢：事务A启动并插入100000条数据，产生了一个巨长的版本链（undo log），事务B读数据，当前读还好，直接找到最新的，而快照读就需要遍历整个版本链。

主从架构模式：为了实现读写分离，开发环境下大多使用主从架构模式。提高效率的同时也会引发一些问题。

        M-S结构：优点有两个，读写分离和实时备份。缺点也有两个，数据一致性难以保障和切换麻烦。MS结构非常适合读多写少的应用场景。

        M-M结构：MM结构中的某一个库加了read-only参数，用来确保一个时间点只存在一个可写的数据库。优点是切换迅速，在应用程序足够强壮的前提下，甚至可以做到数据库宕机时应用不停止服务。缺点也很明显，拓展性不如MS结构强。

        循环复制的问题：MM结构下，A更新完数据后写bin log然后发给B，B更新完后也生成了bin log，由于两者都是M，B又发送给A，导致AB之间一直发送bin log。

                1. 规定两个库的server id必须不同，如果相同，则它们之间不能设定为主备关系；

                 2. 一个备库接到binlog并在重放的过程中，生成与原binlog的server id相同的新的binlog；

                3. 每个库在收到从自己的主库发过来的日志后，先判断server id，如果跟自己的相同，表示这 个日志是自己生成的，就直接丢弃这个日志。

        主备延迟：备用服务器对主服务器的复制是需要时间的，而在这段时间内查询，有可能查询到过期数据。常见原因：1.备库性能差于主库性能；通过对称部署解决。2.备库读压力大：把大量读任务都交给备库，导致负载过大；设置一主多从，负载均衡解决。3.大事务：一个事务执行10分钟，就会导致备库延迟复制10分钟；分成小事务分批执行。

        读写分离的问题：无论怎么优化，总是可能会出现延迟的情况，讨论如何解决。1.强走主库，对于需要实时数据的请求，强制发送给主库查询。2.sleep方案：前端直接返回，后台先sleep一段时间，等延迟结束。3.判断主备无延迟之后再执行：每次查询前先请求判断 ；对比位点；对比GTID集合。

MySql自增id用完了怎么办？

        表结构定义的自增id： 如果定义的时int型的数据，则再插入第2^32个数据时仍会使用2^32 -1 作为自增id，就会出现主键冲突，没有什么好的解决方案，建议修改结构，改成unsigned int。

        每行数据自带的row_id：代码实现是8字节，InnoDB在数据库设计时只保留了后6字节，即当插入数据达到2^48个时，再插入新的数据会导致后6字节变为0，覆盖原始数据的row_id。

        Xid：在事务创建并执行第一条语句时会给xid赋值，长度为8字节，global_query_id 是一个纯内存变量，重启后即清零，超过了会从零开始覆盖，想要用完必须是如下场景：开启事务A，xid是1，在事务内进行2^64次查询，导致global_query_id覆盖旧值，写回xid，然后开启事务B，导致冲突。但2 64次方太大，基本不会发生

        trx_id：InnoDB内部维护了一个max_trx_id，当需要申请新的trx_id时，会拿max_trx_id的值加一作为trx_id值，但只读事务不会使用trx_id，理论上不会出现。

        thread_id：每建立一个数据库连接就会生成一个thread_id，大小为4个字节，当达到最大值时会从0开始重新赋值。