关于日常使用MySql数据库

这篇文章主要写一写数据库中几个重要的方面的知识总结，仅仅作为个人的笔记

 

目录

1.字符集与排序规则：

2.主键生成策略：

3.数据类型

4.关于索引

5.关于事务

    事务的隔离性级别

  事务隔离性的实现

6.关于锁

7.关于日志

---

1.字符集与排序规则：

  utf8mb3：阉割过的utf8字符集，只使用1～3个字节表示字符

  utf8mb4：正宗的utf8字符集，使用1～4个字节表示字符。(如果需要存储emoji表情，则需要此字符集)

2.主键生成策略：

       优先使用用户自定义主键作为主键，如果用户没有定义主键，则选取一个Unique键作为主键，如果表中连Unique键都没有定义的话，则InnoDB会为表默认添加一个名为row_id的隐藏列作为主键。

3.数据类型

   关于类型（n）的说明

    如：int（11）：明确两点   1.无论N等于多少，int永远占4个字节      2.N表示的是显示宽度，不足的用0补足，超过的无视长度而直接显示整个数字，但这要整型设置了unsigned zerofill才有效

  数据类型使用建议

•   选小不选大：尽量选择可以正确存储数据的最小的数据类型，越小的数据类型通常算起来越快，占用的cpu,与磁盘就越小
•   浮点类型的建议统一选择decimal:   因为float 与double 会产生精度丢失
•   简单就好：简单的数据类型的操作通常需要更少的CPU周期，例如：整型比字符操作代价要小得多，因为字符集和校对规则(排序  规则)使字符比整型比较更加复杂。
•   记录时间的建议使用int或者bigint类型，将时间转换为时间戳格式，如将时间转换为秒、毫秒，进行存储，方便走索引

4.关于索引

   理论讲解：

    聚簇索引的特征：使用记录主键值的大小进行记录和页的排序，B+树的叶子节点存储的是完整的用户记录

 InnoDB存储引擎会自动的为我们创建聚簇索引。另外有趣的一点是，在InnoDB存储引擎中，聚簇索引就是数据的存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引。

   索引使用：

    如何创建索引呢？

     1.创建索引的列需要判断列的差异性，也就是一个列中数据的不同值。

        比如性别列就不适合建立索引   原因是：字段数据也就是男或者女两种，那么一般情况下比如各占50%，比如100万的数据，50万是男，那么我在性别这个二级索引获取50万的数据后，还要回表去聚簇索引50万次去获取完整的记录。那么这样的IO情况，显然建立索引并没有体现出查询的优势（被mysql的优化器 统计的查询成本之后，也许就不会用到性别索引，所以此索引根本没有什么意义）

    什么情况下会用到索引呢？

   1.全值匹配:如果我们的搜索条件中的列和索引列一致的话，这种情况就称为全值匹配,  就算顺序不一样，也没有关系，也照样用到索引

   2.匹配最左的列：如果我们想使用联合索引中尽可能多的列，搜索条件中的各个列必须是联合索引中从最左边连续的列

   3.匹配列前缀：

   4.范围匹配之后的索引列的字段是用不到的

   5.使用覆盖索引（也就是查询的字段都是包含在复合索引中的），这样可以避免回表，提高效率

   6.对索引的字段进行函数操作可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能

   7.隐式类型转化会导致索引失效而全表扫描   ： 如   select * from tradelog where tradeid=110717;而tradeid是varchar类型的，

      这样写会导致数据类型转化：字符型 转为数字型

5.关于事务

    事务的隔离性级别

    当多个事务一起执行的时候，就会出现各个事务之间的干扰，可能出现的问题有：脏读，不可重读读，幻读等。为了解决这些问题，就有了事务隔离性级别的概念。

   读未提交： 可以读取到其他事务还没有提交的变更          可能发生脏读、不可重复读和幻读问题

   读已提交：可以读取其他事务已经提交的变更    出现的问题有 ：不可重复读

   可重复读 ： 一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。（默认的隔离性级别）    会出现幻读的问题

   串行化：无论是读取还是写入，都要加锁。（大大影响数据库的并发性）

   注意：脏写（一个事务修改另一个事务未提交的数据）是最为严重的错误，在任何隔离性级别中都不允许。数据库中会用锁来保证脏写的不发生。

  事务隔离性的实现

       每次对记录进行改动，都会记录一条undo日志（回滚日志），每条undo日志也都有一个roll_pointer属性，INSERT操作对应的undo日志没有该属性，因为该记录并没有更早的版本），可以将这些undo日志都连起来，串成一个链表。不同的事务会访问这个链表中的不同的记录。

     那么回滚日志什么时候会被删除呢？

    系统会判断当没有事务需要用到这些回滚日志的时候，回滚日志会被删除。

    为什么尽量不要使用长事务？

      长事务意味着系统里面会存在很老的事务视图，在这个事务提交之前，回滚记录都要保留，这会导致大量占用存储空间。除此之外，长事务还占用锁资源，可能会拖垮库。

 

6.关于锁

    1.怎么减少行锁对性能的影响呢？

      在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。所以知道了这个之后，如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

    比如假设一个业务场景，顺序是1.用户买票扣款，  2.电影院扣余票， 3. 记入一条交易日志。这三个步骤是在一个事务里面，那么我们应该怎么调整顺序呢？根据两阶段锁协议，电影院扣余票这个记录是面临很多用户操作的，我们应该将它放在最后，这样就会减少对该记录的锁的时间。

2.死锁与死锁检测

  当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁

   mysql中解决死锁的方式有

   1.直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置。

      这种方式的问题是当出现死锁以后，第一个被锁住的线程要过超时时间后才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。（超时时间你不能设置过短吧，不然一般的操作也会被误认为超时）

   2.发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。

7.关于日志

     redoLog(重做日志)：

     需要原因 我们知道事务是有持久性的，也就是事务提交之后，就会保存到磁盘中，就算宕机了，重启之后数据还是会恢复的。

      那么 在事务提交完成之前把该事务所修改的所有页面都刷新到磁盘。这可以满足持久性的要求，但是有很大的缺点

     第一：刷新一个数据页很浪费，就算你修改了一点点，也要刷新整个所属的数据页到磁盘

     第二：随机IO刷起来比较慢，一个事务中修改的数据可能会分散在多个不相邻的数据页中

      解决方案：当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。

    redo日志刷盘时机 ：1.事务提交时   2.后台线程不停的刷刷刷  3.正常关闭服务器时   4.log buffer空间不足时

  有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe

   binLog(记录修改的二进制日志)

    binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的二进制日志

  binLog与redoLog的比较

•    redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用
•    redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。
•    redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。