MySQL

mysql相关

事务

特性（ACID）

  1. 原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；

  2. 一致性： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；

  3. 隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；

  4. 持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

并发事务带来的问题

  1. 脏读（Dirty read）: （一个事务执行修改还未提交被另一个事务拿去用了）当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

  2. 丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。

  3. 不可重复读（Unrepeatableread）: （修改）指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

  4. 幻读（Phantom read）: （新增或删除）幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

SQL标准定义的事务隔离级别（由低到高）

  1. READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

  2. READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

  3. REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

  4. SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

mysql使用可重复读，并使用next-key lock算法来避免幻读发生

InnoDB 存储引擎在 分布式事务 的情况下一般会用到SERIALIZABLE(可串行化)隔离级别

索引

  1. B+tree索引

  2. hash索引

  3. 全文索引

hash索引

  1. 优点

    1. 一次到位，效率很高

  2. 缺点

    1. 仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。

    2. 无法被用来避免数据的排序操作。由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；

    3. 不能利用部分索引键查询。对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

    4. 在任何时候都不能避免表扫描。前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

    5. Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下

注意

  1. 在MySQL中，只有HEAP/MEMORY引擎表才能显式支持哈希索引（NDB也支持，但这个不常用），InnoDB引擎的自适应哈希索引（adaptive hash index）不在此列，因为这不是创建索引时可指定的。

  2. HEAP/MEMORY引擎表在mysql实例重启后，数据会丢失。

应用场景

通常，B+树索引结构适用于绝大多数场景，像下面这种场景用哈希索引才更有优势：在HEAP表中，如果存储的数据重复度很低（也就是说基数很大），对该列数据以等值查询为主，没有范围查询、没有排序的时候，特别适合采用哈希索引。

B+tree索引

MySQL的基本存储结构是页(记录都存在页里边)，一页的大小为16KB

  1. 各个数据页可以组成一个双向链表

  2. 每个数据页中的记录又可以组成一个单向链表

    1. 每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录

    2. 以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。

所以说，如果我们写select * from user where indexname = 'xxx'这样没有进行任何优化的sql语句，默认会这样做：

  1. 定位到记录所在的页：需要遍历双向链表，找到所在的页

  2. 从所在的页内中查找相应的记录：由于不是根据主键查询，只能遍历所在页的单链表了

这样的时间复杂度为O（n）

索引通过将无序的数据变成有序（相对），数据结构为B+树

没有用索引我们是需要遍历双向链表来定位对应的页，现在通过 目录 就可以很快地定位到对应的页上了！（二分查找，时间复杂度近似为O(logn)）

查询语句

  1. EXPLAIN

  2. SHOW PROFILES

最左前缀原则

在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDER BY子句也遵循此规则。

避免冗余索引

冗余索引指的是索引的功能相同，能够命中就肯定能命中 ，那么 就是冗余索引如（name,city）和（name）这两个索引就是冗余索引，能够命中后者的查询肯定是能够命中前者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

MySQLS.7 版本后，可以通过查询 sys 库的 schema_redundant_indexes 表来查看冗余索引

避免查询条件中含有函数或表达式

SELECT * FROM employees.titles WHERE emp_no='10001' AND left(title, 6)='Senior';

SELECT * FROM employees.titles WHERE emp_no - 1='10000';

LIKE条件

SELECT * FROM employees.titles WHERE emp_no='10001' AND title LIKE 'Senior%';

如果通配符%不出现在开头，则可以用到索引，但根据具体情况不同可能只会用其中一个前缀

范围查询

范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引。同时，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。

覆盖索引与回表

MySQL的覆盖索引与回表

存储引擎

myisam和innodb

  1. 行级锁：前者只有表级锁，后者支持行级锁和表级锁，默认行级锁

  2. 事务和崩溃后的安全恢复:前者不支持

  3. 外键：前者不支持

  4. MVCC：前者不支持

锁

  1. 悲观锁：每次拿数据都加锁

    1. 适用：多写

  2. 乐观锁：总是假设最好的情况，每次拿数据都认为别人不会修改

    1. 适用：多读

    2. 实现

      1. MVCC：版本号机制。一般是在数据表中加上一个数据版本号version字段，表示数据被修改的次数，当数据被修改时，version值会加一。当线程A要更新数据值时，在读取数据的同时也会读取version值，在提交更新时，若刚才读取到的version值为当前数据库中的version值相等时才更新，否则重试更新操作，直到更新成功。

      2. CAS：compare and swap，一种著名的无锁算法。

    3. 问题

      1. ABA问题

      2. 循环时间长开销大

      3. 只能保证一个共享变量的原子操作

ABA问题

如果一个变量V初次读取的时候是A值，并且在准备赋值的时候检查到它仍然是A值，那我们就能说明它的值没有被其他线程修改过了吗？很明显是不能的，因为在这段时间它的值可能被改为其他值，然后又改回A，那CAS操作就会误认为它从来没有被修改过。这个问题被称为CAS操作的 "ABA"问题。

JDK 1.5 以后的 AtomicStampedReference 类就提供了此种能力，其中的 compareAndSet 方法就是首先检查当前引用是否等于预期引用，并且当前标志是否等于预期标志，如果全部相等，则以原子方式将该引用和该标志的值设置为给定的更新值。

锁算法

  1. Record local：单个行记录上的锁

  2. Gap lock：间隙锁，锁定一个范围，不包括记录本身

  3. Next-key lock（innodb适用可重复读事务级别+next-key lock解决幻读）：锁定一个范围，包含记录本身

相关知识点：

  1. innodb对于行的查询使用next-key lock

  2. Next-locking keying为了解决Phantom Problem幻读问题

  3. 当查询的索引含有唯一属性时，将next-key lock降级为record key

  4. Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生

  5. 有两种方式显式关闭gap锁：（除了外键约束和唯一性检查外，其余情况仅使用record lock）

    1. 将事务隔离级别设置为RC

    2. 将参数innodb_locks_unsafe_for_binlog设置为1

大表优化

  1. 限定数据的范围（where limit..）

  2. 读写分离（主写、从读）

  3. 垂直分区（按列拆分）

    1. 优点：可以使得列数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。

    2. 缺点：主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂。

  4. 水平分区（按行拆分）：分库

    1. 客户端代理：分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。 当当网的 Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。

    2. 中间件代理： 在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。 我们现在谈的 Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。

一条SQL执行很慢怎么分析

  1. 大多数情况下正常，偶尔很慢

    1. 数据库在刷新脏页（flush），例如redo log 写满了需要同步到磁盘

    2. 执行的时候遇到锁，如表级锁、行级锁（show processlist 查看当前状态）

  2. 一直很慢

    1. 没有用上索引：例如该字段没有索引；由于对字段进行运算、函数操作导致无法用索引

    2. 数据库选错了索引

一条SQL语句在MySQL中如何执行的

生产环境大表加索引

  1. 在业务低峰期进行

  2. 使用pt-online-schema-change

    1. 首先它会新建一张一模一样的表，表名一般是_new后缀

    2. 然后在这个新表执行更改字段操作

    3. 然后在原表上加三个触发器，DELETE/UPDATE/INSERT，将原表中要执行的语句也在新表中执行

    4. 最后将原表的数据拷贝到新表中，然后替换掉原表

Previous存储Next数据中台

Last updated