MySQL高级应用

前言

提示：本文主要是通过咕泡学院课程学习，本人将记录的笔记进行整理，图片皆来自课程PPT。本文介绍MySQL的一些原理，适用于有一定数据库使用经验的人观看，如有错误欢迎指正。

---

一、MySQL是什么？

MySQL是目前最流行的关系型数据库之一，MySQL因为其速度、可靠性和适应性而备受关注。

二、MySQL常用的存储引擎

    1、ISAM：

          优点：读取操作的速度很快。
          缺点： 没有事务、没有索引、没有字段管理。
    2、MyISAM：

        优点：ISAM扩展格式、提供ISAM里所没有的索引和字段管理、表格锁定的机制、插入数据快，空间和内存使用比较低。
        缺点：重要缺陷就是不能在表损坏后恢复数据、适用于完整性、并发性要求比较低。
    3、HEAP：
        优点：只驻留在内存里的临时表格、速度比ISAM和MyISAM快。
        缺点：管理的数据不稳定、关机前没保存将全部丢失。
    4、InnoDB：
        优点：对数据库ACID事务的支持、实现了SQL标准的四种隔离级别、支持崩溃修复能力和并发控制、提供了行级锁和外键约束、MySQL运行时Innodb会在内存中建立缓冲池，用于          缓冲数据和索引。
        缺点：不支持FULLTEXT类型的索引，而且它没有保存表的行数。
    5、MEMORY：
        优点：数据全部放在内存中、处理速度快。
        缺点：需要足够的内存、内存出现异常就会影响数据、安全性不高、不能建立太大的表。

三、InnoDB内存架构（5.5.5以上默认Engine是Innodb，其他版本默认是MyISAM）

    基于磁盘的存储引擎，数据都是存在磁盘上的。InnoDB将数据从磁盘加载到内存，有一个最小的逻辑单位——页，每一次读取一页的数据到内存。每一页为16KB的大小。
    InnoDB会将一次读取页的数据存放到一块专用的内存中（buffer pool），下一次读取数据的时候会先去buffer pool里查看，是否已经在里面了，如果在，则直接读取，无需再去磁盘读取，以减少IO带来的性能消耗。当要修改数据的时候，就直接修改buffer pool里的数据，此时内存中的数据和磁盘里的数据就不一致了，内存中的页就叫做脏页。InnoDB后台有专门的线程，会专门把脏页的数据刷到磁盘（刷脏）。修改了一批数据之后，一次性把这些数据刷到磁盘，减少了磁盘IO。

架构图：
    

内存结构：
    

异常恢复图示：
        
    Log Buffer保存页中修改的记录，防止刷脏到一半导致数据丢失甚至是损坏。支持crash safe崩溃恢复的功能，redo log的数据缓存在Log Buffer中，redo log有一个固定的大小，超过这个大小会把之前的数据覆盖。

四、InnoDB磁盘结构
    

分为以下几个空间：

系统表空间：默认情况下，所有的表都会公用系统表空间。
独占表空间： 开启之后，一个表占用一个空间，记录数据和索引的信息。
通用表空间
临时表空间
Redo log：持久性。
Undo 表空间：原子性，记录事务发生之前的状态，用于回滚操作，是逻辑日志。

双写缓冲：  

 
当InnoDB往OS写数据的时候，如果写了一半，挂了就会导致部分写失效，会造成数据丢失，数据丢失就可能导致文件损坏，此时拿着损坏的文件做奔溃恢复是没有意义的，所以使用了双写缓冲的技术，分别记录到内存和磁盘里，由于是顺序写入，所以对性能不会带来太大的开销。如果写一半挂了，就可以拿这个页的副本（缓冲）来还原内容，然后再用redo log去恢复数据。
    主从复制原理：
        
    更新语句执行流程：
        

 

五、索引

    一、本质：是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中的数据，类似书的目录。
        
    二、innoDB索引类型：
        1、普通索引：没有限制。
        2、唯一索引：字段值不允许重复。
        3、主键索引：是一种特殊的唯一索引，在唯一索引的条件下增加了不允许这个字段的值为null。
        4、全文索引：大文本中匹配字符可以使用全文索引，只有文本类型的字段才可以创建（CHAR VARCHAR text）。

    三、思考：用什么数据结构存储索引是最合适的呢？
    1、平衡二叉树（AVL Tree）存索引

        索引存在磁盘中，访问一次磁盘块就要进行一次IO操作，InnoDB一次读取会读取16K的数据，但是上图展示，一个磁盘块存放的数据远远达不到16K的大小，浪费了大量的空间。并且访问一个磁盘块就要操作一次IO，数据大的时候就要操作很多次，大大降低了性能。
         解决方案： 让一个节点存储更多的数据，让节点的度数增多，就由二叉树变为“多叉树”（多路平衡查找树）。
    2、多路平衡查找树（Balanced Tree，B树）

         如果是无序的插入，会导致不断的分裂与合并，B树的效率已经很高了，但是InnoDB的索引还不是用这种数据结构，而是用了B+树。
    3、加强版多路平衡查找树（B+Tree）

         数据只存储在叶子节点上，叶子节点之间是一条链表，这就保证了当区间检索的时候，无需回到根节点再往下找，而是直接通过这个链表就可以找到所有的数据
         假设一条记录1KB，一个叶子节点存储16条数据，一个bigint为8个字节+指针6个字节=14字节
            
        特点：
            1、B Tree能解决的问题，B+Tree都能解决
            2、扫库、扫表能力更强
            3、磁盘读写能力更强
            4、排序能力更强
            5、效率更加稳定
    InnoDB 索引使用的数据结构：
        1、hash
            
            时间复杂度是O（1）
            不足：
                无序，排序很复杂。
                需要进行hash计算，根据key计算value的方式，只能进行等值查询，不能进行区间查询。
                若重复的值很多，就会导致很多的hash冲突。
                我们并不能自己去创建hash的索引，只用innoDB自己创建的才可以。
        2、B+Tree
            我们在InnoDB引擎下创建的索引就是使用的B+Tree。

    四、InnoDB索引：

        .frm：表结构定义的文件，所有的表都有这个文件。
        只有一个文件（除了.frm）
            .idb：数据和索引都存在这里。
        主键索引：
            
        辅助索引：
            
            除了主键索引之外的索引叫做辅助索引或者二级索引。
            聚集索引：决定数据存放的顺序的索引。
            1、如果有主键索引，根据主键作为聚集索引查找值。
            2、如果没有主键，根据找到的第一个唯一索引且不为null的作为聚集索引查找数据。
            3、没有索引，InnoDB为每一列都设置了一个隐藏的熟悉ROWID，使用ROWID作为聚集索引。
    

    五、MyISAM索引：
        一个表有两个个文件（除了.frm）
            .MYI：索引文件。
            .MYD：数据文件。
        主键索引：

        辅助索引：
            
 

六、索引的创建和使用原则

    一、列的离散度：
        公式：count(distinct(column_name)):count(*)；列的不重复的数比所有行数。
        从上公式可以看出，列的不重复率越高，离散度就越高。在离散度不高的字段上建立索引，由于重复值比较多，所以扫描的行数也会比较多，当重复值很高的时候，查询的时候可能会放弃索引。所以，索引应该尽可能创建在离散多高的列上。
    二、联合索引的最左匹配原则：

        在建立联合索引的时候，要将使用最多的字段放在最左边，而且中间不能中断；比如说你建立了一个联合索引A B C，那么查询的时候，使用A，A B，A B C做查询条件是可以使用到索引的（A B C的顺序没关系，OPTIMIZER优化器会自动调整这个顺序），但是如果用B，C，B C，A C做查询条件，那么将用不到索引。
    三、回表：
        
        当使用主键索引的时候，只需要遍历一棵B+树，但是如果使用了辅助索引，则需要遍历两棵B+树，多扫描一棵B+树的过程就叫做回表。所以回表会带来额外的性能消耗。
        覆盖索引：当除了创建了主键索引还创建了其他的字段的索引的时候，如果查询的字段刚好在这个索引里存在，则可以直接获取字段值，而不需要去回表，这个现象就称为覆盖索引。举个例子：建立了一个联合索引，字段非别是A和B，然后查询：select A,B from xxx where A='xxx' and B='xxx'; 由于A和B就在这个辅助索引树中，所以无需回表。但是如果是select B from xxx where B='xxx';这种情况，优化器去遍历索引的时候，开销比较小的话，也会使用索引。EXPLAIN解析查询语句中出现Using index就说名使用到了覆盖索引
    四、创建索引的技巧：
        1、在用于where判断，order排序和join的（on）字段上创建索引。
        2、索引的个数不要过多，过多索引占用空间，合理创建索引。
        3、区分度（散列度）低的字段，例如性别，不要创建索引。
        4、频繁更新的值，不要作为主键或者索引，更新会导致B+树的调整，带来大量磁盘页的碎片
        5、复合索引把散列值（区分度）高的值放在前面
        6、创建复合索引，而不是修改单列索引
        7、过长字段，怎么建立索引？
             可以使用前缀索引，截取数据的前缀来作为索引。长度、存储空间和区分度之间的关系，取的前缀越长，那么占用的存储空间就越大，取的前缀太短，又会导致区分度过低
        8、为什么不建议用无序的值（例如身份证号、UUID）作为索引？
             无序的值插入，如果当前页写满了，会导致页的分裂，B+树数据结构的分裂。最好使用自增有序的列作为索引
    五、什么时候用不到索引？
        1、索引列上使用函数（replace\SUBSTR\CONCAT\sum count avg）、表达式。
        2、字符串不加引号，出现隐式转换。
        3、like条件中前面带%。
        4、负向查询能用到索引吗？（如：<>， !=， NOT IN）
             不一定，取决于优化器。通过计算公式，计算出的结果，如果使用索引效率高则使用，使用索引效率不高则不使用。

七、事务

    一、四大特性
        1、原子性A：逻辑单位，一系列操作不可拆分，要么都成功要么都失败。InnoDB通过undo log实现事务回滚
        2、一致性C：数据在操作之前和操作之后是一致的。比如转账，A转1000块给B，A减少了1000而B只增加了500，这就不满足一致性了
        3、隔离性I：多个事务之间互不干扰
        4、持久性D：不会因为数据库重启或者奔溃而导致数据丢失。（InnoDB使用redo log实现奔溃后数据恢复，保证数据的持久性）
    二、并发带来的问题
        脏读：一个事务读取到了另一个事务未提交的数据，导致前后读取的数据不一致。

        不可重复读： 一个事务读到了另一个事务提交的数据，两次读取的数据不一致。

        幻读：一个事务查询数据，另一个事务提交了一条，第一个事务再次查询多出来了数据，像是出现了幻觉一样。

        总结：事务并发的三大问题都是读一致性的问题，必须由数据库提供一定的事务隔离机制来解决。
    三、事务隔离级别（）SQL92标准）
        未提交读：未解决任何并发问题。
        已提交读：解决脏读问题。
        可重复读：解决不可重复读问题。
        串行化：解决所有问题。
    MySQL InnoDB对事务隔离级别的支持程度：
        
    四、事务隔离级别解决方案
        1、在读取数据之前加锁，阻止其他事务对数据进行修改（LBCC）
            InnoDB锁类型：
    行锁级别：
        1、共享锁（读锁）：多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改，修改可能会造成死锁。
              加锁方式：select * from xxx where id=1 LOCK IN SHARE MODE;
              释锁方式：commit/rollback;
        2、排它锁（写锁）：排它锁不能与其他锁并存，如一个事务获取 一个数据行的排它锁，其他事务就不能再获取该行的锁（共享锁、排它锁），只有该获取排它锁的事务是可以对数据行进行读取和修改。
               加锁方式：
                      自动：delete/update/insert 默认加锁
                       手动：select * from xxx where id=1 FOR UPDATE;
               释锁方式：commit/rollback;
        行锁原理：锁住的是索引。
        行锁算法：
               1、区间划分
                            
                   2、记录锁，锁定记录
                            
                    3、间隙锁，锁定范围
                            
                        锁定了一个区间后，在这个区间操作数据是不能成功的，解决了幻读的问题。
                     4、临键锁，锁定范围加记录
                            
                表锁级别：
                    意向共享锁。
                    意向排它锁。
                    意向锁是有存储引擎自己维护的，用户无法手动操作意向锁。
               意向共享锁和意向排它锁是加表锁的一个标志，一旦这个表有这个标志则加表锁一定失败，无需再去检索全表，来查找是否有数据行被别的事务所锁定，提高了性能
        生成一个数据请求时间点的一致性数据快照，并用这个快照来提供一定的级别的一致性读取（MVCC）。
            每次开启一个事务都会记录一个版本号，在我这个事务创建之前的数据可以查看到，在这个事务之后做的删除新增修改的数据在这个事务中查看不到。
    五、事务隔离级别的选择：
        1、RR的间隙锁会导致锁定范围的扩大
        2、条件列未使用到索引，RR锁表，RC锁行
        3、RC的“半一致性”读可以增加update操作的并发性
        4、InnoDB事务隔离级别的实现：

八、mysql优化

一、MySQL优化思路与工具：
        1、优化层次
            
    2、连接数
        连接数越多，带来服务端上下文切换的额外消耗，所以不是连接数越多越好。一般设置为服务器所在机器的 核心数*2+1。所以要合理配置连接数。（默认是151，最大是10万）
        连接数（too many connections）超过了服务器设置的max connections的值：
                服务端处理：增加连接数。
                客户端处理：释放连接数、使用连接池。
    3、架构优化
        单台服务器：
            服务端：
                1、单台数据库满足不了读写访问的需求，使用数据库集群部署。
                2、集群带来的问题，多个数据库节点的数据如何保持一致？
                    复制技术（replication）：
                        
                        、、
                        
                写的请求都通过master节点，读的请求通过slaver节点
                    基于主从复制的读写分离：

                      
                    一定程度的减轻单台服务器的访问的压力，但是会有主从复制一致性的问题（有一定的延迟）。
            客户端：
                配置多数据源，可以使用一些中间件（如MyCat）帮助选择数据源

        分库分表：
            单表数据过大：
                
                
            垂直分库：
                减少数据库并发访问压力。

             
            水平分库分表：解决存储瓶颈。
                
           带来的问题：数据源的选择。可以使用一些中间件（如MyCat）
    4、慢查询日志
        默认关闭（因为会消耗一些性能）
        mysqldumpslow工具来统计慢查询日志
        profile工具也可以查看查询的相关信息，show profiles;查看所有查询的情况；show profile;查看最后执行的一条的情况；show profile for id;查看查询出来的指定一条的执行情况
        mysql命令：show processlist;查看每一个连接的情况，每一个客户端的连接，对于服务端来说就是一个线程，这个命令就可以查看到每一个线程的状态。
    5、执行计划
        explain关键字分析执行情况，从id大的开始执行，如果id一样则从上往下执行。数据量会影响执行顺序，由笛卡尔积决定执行顺序，是一种小表驱动大表的思想。

        explain关键字分析相关字段说明：
        （1）select_type查询类型：
            SIMPLE：简单查询，不包含子查询的查询。
            PRIMARY：包含子查询的主查询。
            SUBQUERY：内存的子查询。
            DERIVED：衍生查询，用到临时表的查询，会把数据临时存在临时表中的查询。
            UNION：用到了UNION的查询。
            UNION RESULT：代表的是UNION的结果。
        （2）type
            const：使用了主键索引或唯一索引查到一条数据（好的类型）。
            system：查询系统表的时候只有一条数据（用得少）。
            eq_ref：关联查询的时候用到了主键索引或唯一索引，对于前面的每个结果动能匹配到后面的结果的时候，除了const之外最好的访问类型。
            ref：查询用到了非唯一索引。
            range （优化最好能达到range及以上的级别）：对索引的范围访问（between and   ,> ,< ），一定用到了索引。
            index：拿到索引的所有的值。
            all：全表扫描，没有用到索引。
            null：没有访问表，直接得到结果。
        （3）possible_keys：可能用到的索引。
        （4）key：实际用到的索引。
        （5）key_len：使用了索引的长度（了解）。
        （6）ref：执行过滤的时候使用了哪一个常量或者哪一个字段一起去筛选。
        （7）rows：预估要扫描多少行数据（不是精确的值）。
        （8）filtered：存储引擎返回数据给SERVER之后，SERVER还要做过滤，剩下有多少满足条件，是一个百分比，高一点比较好（说明在存储引擎层就做好了过滤）。
        （9）Extra（额外的信息）
            Using INDEX：用到了覆盖索引。
            Using where：返回给SERVER层的时候，不是索所用的记录都符合条件，还要在SERVER过滤。
            Using filesort：不能使用索引排序，还要用到其他额外的做排序（需要优化）。
            Using temporary：用到临时表，需要把结果缓存在内存里。如：对非索引列使用distinct的时候；对非索引列做group by的时候，等等。
    6、存储引擎与表结构的优化
        存储引擎：
            查询插入非常多的用MyIsam。
            对数据的一致性要求高的，有并发的、更新多的操作用InnoDB。
            临时数据可以用memory。
        表结构：
            数据量大的表，可以按日期拆分表。
            字段定义原则：可以存储数据的最小数据类型。
            NOT NULL：一定不允许为null的数据，定义成NOT NULL，或者定义default默认数据。
        外键、触发器、视图开发人员不建议使用，约束尽量在代码里做好约束。
        大文件、图片：存到文件服务器上，数据库只存URI，一般是相对地址。
    总结：
        
        上图所示：从上往下成本增加，收效减小。
        不能忽略业务上的优化：
            1、如支付宝鼓励用余额宝支付，而不是银行卡。因为使用内部的数据库会更快，用外部接口性能消耗更大。
            2、服务降级，高峰期关闭其他业务，已达到降低数据库压力的效果。
            3、双十一提前预售，实现分流。
            4、mysql满足不了需求了可以使用搜索引擎（ES）大数据，NoSQL等。