==================================================================
001-常见存储引擎
一、MySQL中myisam与innodb的区别
InnoDB支持事务，Myisam不支持事务；
InnoDB支持外键，Myisam不支持外键；
InnoDB支持行锁和表锁；Myisam不支持行锁。
Innodb 是索引组织表， myisam 是堆表
InnoDB索引与表在一起，MyISAM索引与表独立。
Innodb主键索引叶节点存数据，Myisam索引叶节点存的地址
InnoDB在内存存储索引和数据，MyISAM 将索引存储到内存中
innodb表索引与表共用文件，myisam索引单独存放
innodb表按主键顺序存放，myisam随机存放
InnoDB适合oltp，myisam适合olap
二、memory存储引擎
以前称作head存储引擎
三、补充：NDB分布式集群存储引擎
1、类似于Oracle的RAC，但它是Share Nothing的架构。
2、数据全部放在内存中，因此通过主键查找非常快。
3、复杂的join操作需要巨大的网络开销，查询速度会很慢。
==================================================================
002-innodb后台线程
| thread/sql/main |
| thread/innodb/io_handler_thread |四读四写(四类change buffer合并io+redo log刷redo+write thread+read thread)
| thread/innodb/srv_master_thread |一个master线程(主线程)
| thread/innodb/srv_purge_thread |Purge thread(删除记录和删除undo)
| thread/innodb/srv_monitor_thread |monitor_thread
| thread/innodb/srv_error_monitor_thread |一个错误监控线程
| thread/innodb/srv_lock_timeout_thread |一个lock监控线程
| thread/innodb/page_cleaner_thread |page_cleaner_thread(刷buffer)
| thread/sql/signal_handler |单独的signal处理线程
| thread/sql/slave_sql |slave_sql
| thread/sql/slave_io |slave_io
一、一个master线程
1、innodb几乎在这个线程上实现有所有功能
每秒1次的操作
每10秒一次的操作
数据库空闲时或数据库关闭时的操作
2、Master thread 内部几个循环(loop)组成：
主循环(loop)，
后台循环(background loop)，
刷新循环(flush loop)，
暂停循环(suspend loop)。
3、主循环有1s循环和10s循环. 1s循环即循环执行一次就sleep 1s后又执行一次又sleep 1 s.
4、各种循环执行的次数，据此判断系统负载高低
5、在循环内可以调用其他线程进行相关的操作。
二、一个lock监控线程
三、一个错误监控线程
四、IO线程
1、innodb_write_io_threads
默认四个，在高并发下可设为8
2、innodb_read_io_threads
默认四个，在高并发下可设为8
3、page clean线程刷新的方式分为三种
===================================================================
003-innodb引擎的4大特性
一、Change buffer
将对二级辅助索引页的操作缓存下来，以此减少辅助索引的随机IO，并达到刷盘操作合并的效果。
二、二次写(防卡写失败造成页断裂)
1、先放入doublewrite buffer
2、接着二次写
(1)写入磁盘共享表空间中(连续存储，顺序写，性能很高)，每次写1MB；
(2)再写入实际的各个表空间文件(离散写)；
3、如果刷盘的时os崩溃，在恢复过程中
(1)从共享表空间的doublewrite中找到该页的一个近的副本，将其复制到表空间文件；
(2)再应用redo log，就完成了恢复过程；
三、自适应哈希索引(ahi)
1、自动根据访问的频率和模式来自动地为某些热点页建立哈希索引。
2、启用AHI后
(1)读取和写入速度可以提高2倍
(2)辅助索引的连接操作性能可提高5倍。
四、预读(read ahead)
1、mysql-5.7中的innodb_buffer_pool_prefetching
(1)一个Extend的64个页面大多页被访问了，Extend中的其它页被访问概率也非常大。
(2)于是将Extend中的其它页面与一并调入内存了，样做可以把多次IO请求合并成一次。
2、read-ahead也可能带来性能问题
==================================================================
004-InnoDB引擎的七种类型的锁
1、共享锁和排他锁（Shared and Exclusive Locks）
不是真正意义上的锁，对其它锁起修饰作用。
2、记录锁（Record Locks）
单个行记录上的锁
3、间隙锁（Gap Locks）
锁定一个范围，但不包括记录本身，即使某些不存在的键值也会被无辜的锁定
4、Next-Key Locks(repeatable-read)
默认的锁算法,锁定一个范围，并且锁定记录本身
5、自增锁（AUTO-INC Locks）
针对AUTO_INCREMENT列的表级锁，一个事务正插入，其他事务的插入必须等待，以便个事务插入的行，是连续的主键值。
6、共享意向锁（Intention Locks）
是特殊的表级别锁，仅仅表明意向
要获得某些行的S锁，须先获得表的IS锁(intention shared)，预示未来有意向对表中某些行加S锁
7、插入意图锁（Insert Intention Locks）
是特殊的表级别锁，仅仅表明意向
要获得某些行的X锁，须先获得表的IX锁(intention exclusive)，预示未来有意向对表中某些行加X锁
8、补充:Intention锁不互斥，它与S/X互斥：
S X
IS 兼容 互斥
IX 互斥 互斥
==================================================================
005-数据库中的几种事务隔离级别
1、未提交读（Read uncommitted）
2、已提交读（Read committed（RC））
Oracle和SQL Server默认事务隔离级别,阿里rds上隔离级别为READ-COMMITTED
3、可重复读（Repeatable read（RR）
在同一个事务内的查询都是事务开始时刻一致的，多会话并发读取时，可能将未提交的记录查询出来，而出现幻读。
4、可串行化（Serializable）。
==================================================================
006-redolog与binlog的区别
1、记录范围：redo只记InnoDB日志，binlog记录所有引擎的日志
2、记录内容：binlog记录SQL语句event，redo记录页更改的物理情况。
3、写入时间：先写redo log，再写binlog，并以binlog写成功为事务提交成功的标志。
4、崩溃恢复：对两者比较，xid在binlog里存在则提交，不存在则回滚。
==================================================================
007-INNODB也采用LRU进行管理检查点
一、Sharp Checkpoint(全量检查点)
发生在数据库关闭时将所有的脏页都刷新回磁盘，这是默认的工作方式，即参数innodb_fast_shutdown=1。
二、FUZZY CHECKPOINT(增量检查点)
在运行时InnoDB存储引擎内部使用Fuzzy Checkpoint只刷新一部分脏页。
三、innodb_io_capacity控制缓冲区刷新脏页时，一次刷新脏页的数量，一般建议如下：
1、100=SATA
2、200=SAS(15000转)
3、600=Raid10（6块SAS)
4、5000=SSD
5、10000-50000=PCI-E
==================================================================
008-online DDL
一、之前版本做ddl，为了避免堵塞DML。
1、采用主从滚动的方式，操作复杂，
2、选择pt-osc工具(pt-online-schema-change)
(1)需要主键或键否则不执行；
(2)触发器在一定情况下会导致死锁，对业务有一定的影响
二、mysql 5.6版本的online ddl则避免了上述限制。
1、首先ADD COLUMN和DROP COLUMN不再会堵塞DML操作，
2、同时建立索引 方面默认使用LOCK=NONE的模式而不会堵塞DML，
==================================================================
009-innodb运行机制补充
一、query cache
1、查询必须大小写、空格、库名、字符集严格一致
2、在mysql8.0中已作废
二、线程池避免高并发让MySQL瘫痪
Thread_Cache被划分为多个group（组），每个组又有对应的工作线程
1、MySQL里面为了提高客户端请求创建连接过程的性能,提供了一个连接池也就是 Thread_Cache池,
2、将空闲的连接线程放在连接池中,而不是立即销毁.
3、这样的好处就是,当又有一个新的请求的时候,mysql不会立即去创建连接线程,
4、而是先去Thread_Cache中去查找空闲的连接线程,如果存在则直接使用,不存在才创建新的连接线程。
5、在短连接的应用中Thread_Cache的功效非常明显,因为在应用中数据库的连接和创建是非常频繁的
三、应不应该开启自动提交？
1、set autocommit = 1;
MySQL在autocommit自动提交模式下，每个SQL都是一个独立的事务
阿里rds上自动提交打开
2、set autocommit = 0:
意味着只执行一个 select 语句也会启动对应的事务，而且不会自动提交。
这个事件会持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。
如果是长连接则会导致长事务，而长事件会占用回滚段和锁资源。
动力小刚于2019年4月 个人邮箱：zcs0237#163.com
