MySql笔记
记录极客时间《MySql实战45讲》中的一些笔记。
MySql的逻辑架构图
第一讲 一条SQL查询语句是如何执行的？
1.当一个表进行更新的时候，跟这个表有关的查询缓存就会失效。
2.mysql占用内存快，可以定期断开长连接，这样资源可以释放出来，或者每次进行一个大操作后，通过执行mysql_reset_connection来重新初始化资源。
第二讲 一条SQL更新语句是如何执行的？
1.redo log（重做日志）是InnoDB（Mysql引擎之一）特有的，binlog（归档日志）是Server层的，任何引擎都能用。
2.redo log是物理日志，binlog是逻辑日志。
3.redo log是循环写的，会覆盖。binlog是可以追加写入，不会覆盖。
4.两阶段提交可以保证数据恢复是逻辑的一样的。
本讲问题：在什么场景下，一天一备会比一周一备更有优势，或者说，它影响了这个数据库系统的哪个指标？
第三讲 为什么你改了我还看不见？
1.事务具有原子性，一致性，隔离性，持久性。
2.SQL标准事务隔离级别：读未提交，读提交，可重复读，串行化。
3.当数据库多个事务同时执行时，可能出现脏读，不可重复读，幻读的问题。
4.尽量不要使用长事务，不然占用资源，最终拖垮整个库。误用长事务具体看第三讲。
本讲问题：你是数据库负责人，你会怎么避免或者处理长事务这种情况？
第四讲 深入浅出索引（上）
1.索引分为主键索引和普通索引，在应用中应该尽量使用主键查询。
2.InnoDB选择用B+树结构，可以配合磁盘的读写特性，减少单次查询磁盘访问的次数。
3.本讲最后是如何避免长事务对业务的影响。
本讲问题：对于上面例题的两个重建索引的作法，说出你的理解，或者你觉得有什么不合适的吗？
第五讲 深入浅出索引（下）
1.数据库索引的概念包括：覆盖索引，前缀索引，索引下推。其中覆盖索引可以减少树的搜索次数，显著提示查询性能，是一个常用的优化手段。
2.满足语句需求的情况下，尽量少访问资源是数据库设计的重要原则之一。
3.建立联合索引时，如何安排索引内的字段顺序，第一原则是如果通过调整顺序可以少维护一个索引，那么这个顺序是优先考虑的。
本讲问题：这个例题，两个索引都是否是必须的，为什么？
第六讲 全局锁和表锁：给表加个字段怎么有这么多阻碍？
1.MySql的锁大致分为全局锁，表级锁，行锁三类。
2.表级锁又分为：表锁，元数据锁。
3.全局锁主要用在逻辑备份过程中，表锁一般是在数据库引擎不支持行锁的时候才会被用到。
4.MDL（元数据锁）会直到事务提交才释放，在做表结构变更的时候，一定小心不要导致锁住线上查询和更新。
5.DML:增修改数据 DDL：修改表结构
本讲问题：备份一般在备库上进行，你在用-single-transaction方法做逻辑备份的过程中，如果主库上一个小表做了一个DDL，这时候从备库上会看到什么现象。
第七讲 行锁功过：怎么减少行锁对性能的影响？
1.InnoDB支持行锁，MyISAM不支持，不支持行锁，一个表任何一个时刻只能由一个更新进行，会影响业务并发度。
2.两阶段锁协议：在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不是需要了就立刻释放，而是要等到事务结束时才释放。
3.如果你的事务中需要锁多个行，要把最可能造成锁冲突，最可能影响并发度的锁尽量往后面放。
4.发生死锁的解决方案：1.直接进入等待，直到超时。2.主动发起死锁检测。
5.死锁检测在热点行更新会导致利用大量资源的解决方法：1.临时把死锁检测关掉，但是有风险。2.控制并发度，但是如果客户端很多，也是很耗资源，所以并发控制可以在数据库服务端做，可以考虑用中间件实现，或者有大牛修改MySQL源码，总之基本思路是：对于相同行的更新，在进入引擎之前排队。如果没有大牛，可以将一行改成多行的逻辑，比如电影院的账户总额等于10个记录的值，每次随机给一条记录添加，这样冲突的概率为1/10，但是你还要考虑如果有人发生退票，一部分记录会变成0，你要对代码有特殊处理。
本讲问题：关于删除表前面1000行数据，这三个方法你选择哪个，为什么？
第八讲 事务到底是隔离的还是不隔离的？
这篇文章需要好好消化，举例了一个具体的操作，关于事务隔离时候的情况。
1.InnoDB的行数据有多个版本，每个数据版本都有自己的row trx_id，每个事务或者语句有自己的一致性视图。
2.普通查询语句是一致性读，一致性读会根据row trx_id和一致性视图确定数据版本的可见性。
3.对于可重复读，查询只承认在事务启动前就已经提交完成的数据。
4.对于读提交，查询只承认在语句启动前就已经提交完成的数据。
5.而当前读，总是读取已经提交完成的最新版本。
6.MySQL中，有两个视图的概念，1.一个是view,它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是create view…，而他的查询方法与表一样。2.另一个是InnoDB在实现MVCC时用到的一致性读视图即consistent read view用于支持RC（read committed，读提交）和RR（repeatable read，可重复读）隔离级别的实现。
7.更新数据都是先读后写的，而这个读，只能读当前的值，称为当前读（current read）。
本讲问题：一个具体的业务操作出现的问题，具体看原文。
第九讲 普通索引和唯一索引，应该怎么选择
1.身份证号字段比较大，不建议直接当主键，可以当唯一索引，因为这个业务代码上保证了不会写入重复的身份证号。
2.普通索引和唯一索引其实性能上差距不大，但是他们对更新语句性能有影响，但是非常小，只是在于一个判断。
3.change buffer只限于在普通索引下，不太适合唯一索引。
4.redo log主要是节省随机写磁盘的IO消耗（转成顺序写），而change buffer主要节省随机读磁盘的IO消耗。
本讲问题：change buffer一开始是写内存，如果这个时候机器掉电重启，会不会导致change buffer丢失？
第十讲 MySQL为什么有时候会选错索引？
1.优化器选择索引的目的是为了找到一个最优的方案，但是有时候会判断错误从而选错索引。
2.如何让优化器真正选择好的索引：1.我们可以采用force index强行选择一个索引。2.或者修改语句，引导MySql使用我们期待的索引。3.在有些场景下，可以新建一个更合适的索引，或者删掉误用的索引。
本讲问题：具体看文章，是本文例子的扩展问题。
第十一讲 怎么给字符串字段加索引？
1.使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。
2.使用前缀索引就用不上覆盖索引对查询性能的优化，这一点则需要我们考虑是否使用前缀索引。
3.倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题，比如身份证。
4.创建hash字段索引，查询性能稳定，有额外的存储和计算消耗，和倒序存储一样均不支持范围扫描。
本讲问题：维护一个学生信息库，如何设计登录名的索引，这是算实战题，得好好看。
第十二讲 为什么我的MySQL会抖一下？
1.因为InnoDB在后台刷脏页，也就是将内存写入磁盘，所以无论是因为你的查询语句需要内存的时候可能要求淘汰一个脏页，还是由于刷脏页的逻辑会占用IO资源影响到更新语句，都可能会让MySQL抖一下。
本讲问题：一个内存配置为128GB. innodb io_ capacity设 置为20000的大规格实例，正常会建议你将redolog设置成4个1GB的文件。但如果你在配置的时候不慎将redo log设置成了1个100M的文件，会发生什么情况呢?又为什么会出现这种情况呢？
第十三讲 为什么表数据删掉一半，表文件大小不变？
1.建议设置innodb_file_per_table为on，表示每个InnoDB表数据存储在一个以.ibd为后缀的文件中。
2.如果上面说的参数设置为off，表的数据会放在系统共享表空间，这时候如果我们删掉表，空间是不会回收的，而如果是上面的on，表单独存成一个文件，通过drop table系统会删掉这个文件。
3.单纯delete表不用的数据，表文件大小不变，还要通过alter table重建表才可以把表变小。
4.本文还涉及inplace和online的区别，以及onlineDDL下的重建表的过程。
本讲问题：某人用了alter table后发现空间还大了点，是什么原因？
第十四讲 count(*)这么慢，我该怎么办？
1.MyISAM表虽然count()快，但是不支持事务;show table status命令虽然快，但是不准确;InnoDB直接count()会遍历全表，虽然结果准确，但是有性能问题。
2.用redis来保存计数可能存在丢失更新的问题，也就是可能不一致。
3.把计数值放在MySql中，可以解决这个一致性视图的问题，利用了事务这个特性。
本讲问题：我们用事务来保证计数准确，那么这个事务序列中，应该先插入操作记录，还是先更新计数表呢？