对于MySQL的理解

一.理论

1. 一条SQL执行过程
   • 连接器
     负责跟客户端建立连接、获取权限、维持和管理连接。
   • 分析器
     • 词法
       • MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”。
     • 语法
       • 根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。
     • 语义
       • 判断语句是否正确，表是否存在，列是否存在
   • 优化器
     • 决定使用什么索引，和join表的顺序
   • 执行器
     • 判断是否有权限，然后执行语句
     • 执行的过程
2. 索引
   索引的出现其实就是为了提高数据查询的效率，就像书的目录一样
   • 常见索引数据结构(每碰到一个新数据库，我们需要先关注它的数据模型，这样才能从理论上分析出这个数据库的适用场景)
     • 哈希表
       • 适用于只有等值查询的场景，比如 Memcached 及其他一些 NoSQL 引擎
     • 有序数组
       • 适用于等值查询和范围查询场景中，只适用于静态存储引擎
     • 搜索树
     • 跳表
     • LSM 树
   • 常见索引
     基于非主键索引的查询需要多扫描一棵索引树，应用中应该尽量使用主键查询
     • 主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。
     • 非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）配合change buffer使用更佳
     • 唯一索引：唯一索引可以创建多个且能为空(只能一次)，主键索引不能为空
   • 索引优化
     • 覆盖索引(会受到前缀索引影响)
       由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。
     • 最左前缀原则
       第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。
     • 索引下推
   • 优化器选错索引(纠正)
     • 由于索引统计信息不准确，导致判断扫描行数不准确，此种情况可用 analyze table 来解决
     • 由于临时表，排序字段，导致优化器误判，此种情况可用force index来强行指定索引，也可以通过修改语句引导优化器，还可以通过增加或者删除索引来绕过这个问题
   • 索引失效
     总结：对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。(下面三种都是需要函数转换)
     • 条件字段函数操作
       例如 where month(t_modified)=7
     • 隐式类型转换
       例如 select “10” > 9
       如果规则是“将字符串转成数字”，那么就是做数字比较，结果应该是 1；
       如果规则是“将数字转成字符串”，那么就是做字符串比较，结果应该是 0。
     • 隐式字符编码转换
       mysql> select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2; /语句Q1/
       tradeid不是同一个字符集，需要转换CONVERT(traideid USING utf8mb4)，导致索引失效
3. 事务(ACID)
   • 特性
     • 原子性(Atomicity)
     • 一致性(Consistency)
     • 隔离性(Isolation)
       这4种隔离级别，并行性能依次降低，安全性依次提高
       • 读未提交（read uncommitted）
         一个事务还没提交时，它做的变更就能被别的事务看到
       • 读提交（read committed）
         一个事务提交之后，它做的变更才会被其他事务看到
       • 可重复读（repeatable read）
         一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
       • 串行化（serializable ）
         对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。
     • 持久性(Durability)
   • 多事务问题
     • 脏读
     • 不可重复读
     • 幻读
   • 事务启动方式
     • 显式启动事务语句，begin或者start transaction,提交commit，回滚rollback
     • set autocommit=0，该命令会把这个线程的自动提交关掉。这样只要执行一个select语句，事务就启动，并不会自动提交，直到主动执行commit或rollback或断开连接。
   • MVVC(Multi-Version Concurrency Control 多版本并发)
     • 视图是 InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现。
4. 锁
   • 全局锁
     • 命令：
       • Flush tables with read lock (FTWRL)
       • set global readonly=true (不建议使用)
     • 场景：做全库逻辑备份
   • 表锁
     • 一种是表锁，一种是元数据锁（metadata lock)
     • 命令
       • lock tables … read/write(不建议使用 lock tables锁住整个表的影响面太大)
       • MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。
   • 行锁
     • 死锁
       • 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
       • 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。
5. 日志
   • binlog(归档)
     • Server层特有的日志
   • redo log(重做)
     • InnoDB 引擎特有的日志
     • 当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（粉板）里面，并更新内存，空闲时写入磁盘。
   • undo log(回滚)

二.实战

1. Join
   • 什么使用时候Join
     • 如果可以使用 Index Nested-Loop Join 算法，也就是说可以用上被驱动表上的索引，其实是没问题的；
     • 如果使用 Block Nested-Loop Join 算法，扫描行数就会过多。尤其是在大表上的 join 操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种 join 尽量不要用。
   • 如果要使用 join，应该选择大表做驱动表还是小表？
     • 如果是 Index Nested-Loop Join 算法(BNL)，应该选择小表做驱动表；
     • 如果是 Block Nested-Loop Join 算法(ILJ)：
       • 在 join_buffer_size 足够大的时候，是一样的；
       • 在 join_buffer_size 不够大的时候（这种情况更常见），应该选择小表做驱动表。
         （ 小表：在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。）
   • 优化
     • Multi-Range Read 优化
       意为多范围读，表示在回表之前先把需要回表的主键排序，再读取对应的数据（当然，有的并不需要回表），就能够将无序读转化为有序读，性能会大幅提升
     • Batched Key Access(BKA对NLJ 算法的优化,但依赖MRR)
       set optimizer_switch=‘mrr=on,mrr_cost_based=off,batched_key_access=on’;
2. 临时表
   • 特点
     （1）只对当前session可见
     （2）可以与普通表重名
     （3）增删改查用的是临时表
     （4）show tables不显示普通表