MySQL学习笔记1.0

1.两种日志（binlog 和 redo log）

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

为了保证数据库的一致性，必须要保证2份日志一致，因此使用的两阶段式提交。

2.事务隔离

    SQL 标准的事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。

• 读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
• 读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
• 可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
• 串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

3.索引

    在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为前面我们提到的，InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。
    根据叶子节点的内容，索引类型分为主键索引和非主键索引。主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

覆盖索引、最左前缀原则、索引下推

4.表级锁

    MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。
    MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

• 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
• 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

5.普通索引和唯一索引

    普通索引和唯一索引应该怎么选择？
    其实，这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。实际使用中，普通索引和 change buffer 的配合使用，对于数据量大的表的更新优化还是很明显的。

6.索引选择异常

索引选择异常和处理：

    对于由于索引统计信息不准确导致的问题，可以用 analyze table 来解决。

    而对于其他优化器误判的情况:
    1.采用 force index 强行选择一个索引。
    2.考虑修改语句，引导 MySQL 使用我们期望的索引。
    3.在有些场景下，可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。

7.给字符串字段加索引

    1.使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。但使用前缀索引就无法使用覆盖索引对查询性能的优化，这也是在选择是否使用前缀索引时需要考虑的一个因素。

    2.总结：

• 直接创建完整索引，这样可能比较占用空间；
• 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
• 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
• 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

8.数据库表的空间回收

    一个 InnoDB 表包含两部分，即：表结构定义和数据。表结构定义占用的空间很小，主要考虑表数据的空间回收。

    表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数 innodb_file_per_table 控制的：
    这个参数设置为 OFF 表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；
    这个参数设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。
    从 MySQL 5.6.6 版本开始，它的默认值就是 ON 了。我建议你不论使用 MySQL 的哪个版本，都将这个值设置为 ON。因为，一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过 drop table 命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。
    如果要收缩一个表，只是 delete 掉表里面不用的数据的话，表文件的大小是不会变的，还要通过 alter table 命令重建表，才能达到表文件变小的目的。

9.count()函数

    count(*)、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数；而 count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。

• 对于 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
• 对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。单看这两个用法的差别的话，count(1) 执行得要比 count(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。
• 对于 count(字段) 来说：如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。
• 但是 count( * ) 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count ( * ) 肯定不是 null，按行累加。
      按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count( * )，所以建议尽量使用 count( * )。

10.随机查询数据

    如果直接使用 order by rand()，这个语句需要 Using temporary 和 Using filesort，查询的执行代价往往是比较大的。所以，在设计的时候你要尽量避开这种写法。在实际应用的过程中，比较规范的用法就是：尽量将业务逻辑写在业务代码中，让数据库只做“读写数据”的事情。

11.函数操作

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。同时，隐式类型转换，隐式字符编码转换，也会要求在索引字段上做函数操作而导致了全索引扫描。

12.binlog 的三种格式

    一种是 statement，一种是 row。还有一种 mixed，其实它就是前两种格式的混合。
    当 binlog_format=statement 时，binlog 里面记录的就是 SQL 语句的原文。
    当 binlog_format 使用 row 格式的时候，binlog 里面记录了真实修改行的数据。
    因为有些 statement 格式的 binlog 可能会导致主备不一致，所以要使用 row 格式。但 row 格式的缺点是，很占空间。比如你用一个 delete 语句删掉 10 万行数据，用 statement 的话就是一个 SQL 语句被记录到 binlog 中，占用几十个字节的空间。但如果用 row 格式的 binlog，就要把这 10 万条记录都写到 binlog 中。这样做，不仅会占用更大的空间，同时写 binlog 也要耗费 IO 资源，影响执行速度。所以，MySQL 就取了个折中方案，也就是有了 mixed 格式的 binlog。mixed 格式的意思是，MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式。也就是说，mixed 格式可以利用 statment 格式的优点，同时又避免了数据不一致的风险。