Mysql实战45讲读书笔记——1

第一讲 一条SQL查询语句是如何执行的
①连接器
连接器负责跟客户端建立连接、获取权限、维持和管理连接。

②分析器
③优化器
④执行器
    查询首先会去查找缓存，如果缓存没有才读取存储引擎，增删改操作会清空缓存

第二讲 一条更新语句是如何执行的
执行更新操作时不会立即更新硬盘，
Redolog binlog

第三讲 事务隔离
事务隔离级别：读已提交，读未提交，可重复读，串行化

第四讲 索引
非主键索引叶子节点存储的是主键id，会回表再查找出数据

第五讲 索引
索引覆盖：如果执行的语句是select ID fromTwhere k between3 and5，这时只需要查ID的值，而ID的值已经在k索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引k已经“覆盖了”我们的查询需求，我们称为覆盖索引。(ID为主键，不需要回表)
索引下推：可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。(联合索引，依次判断字段是否满足条件)

第六讲 全局锁和表锁
Mysql里面的锁大致可以分为全局锁、表级锁和行锁三类
加全局读锁：Flush tables with read lock (FTWRL)
MySQL里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta datalock，MDL)。
元数据锁（MDL）会直到事务提交才释放。
表级锁：lock table…read/write

第七讲 行锁
MyIsam不支持行锁，Innodb支持，这也是Innodb取代myisam的重要原因之一
如果出现死锁事务A和事务B在互相等待对方的资源释放，就是进入了死锁状态。当出现死锁以后，
有两种策略：
一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置。
另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。（一般使用第二种策略）

第八讲 快照
InnoDB里面每个事务有一个唯一的事务ID，叫作transactionid。它是在事务开始的时候向InnoDB的事务系统申请的，是按申请顺序严格递增的。
而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把transaction id赋值给这个数据版本的事务ID，记为rowtrx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。
更新数据都是先读后写，而这个读，只能读当前的值，称为‘当前读’。
在没有更新数据的事务里面是’一致读’
select语句加锁:
lock in share mode读锁（S锁，共享锁）
for update 写锁（X锁，排他锁）

读提交的逻辑和可重复读的逻辑类似，它们最主要的区别是： 
    在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询     都共用这个一致性视图； 
    在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图。

对于可重复读，查询只承认在事务启动前就已经提交完成的数据
对于读提交，查询只承认在语句启动前就已经提交完成的数据
如果事务1里面的更新行在事务B里面更新还未提交，会进入等待，等待事务B提交后读取最新数据

第九讲 普通索引和唯一索引的选择
在业务代码已经保证不会写入重复数据的情况下可以使用普通索引，性能会优于唯一索引，如果业务不能保证或者说业务要求用数据库来做约束，那就必须得创建唯一索引。

第十讲 mysql为什么会选错索引
选择索引是优化器的工作，而优化器选择索引的目的就是找到一个最优的执行方案，在数据库里面，扫描行数是影响执行代价的因素之一，扫描的行数越少，以为着访问磁盘的次数越少，消耗的cpu资源越少，
一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。
采样统计的时候，InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。 当变更数据行数超过1/M的时候会自动触发一次索引统计。
在MySQL中，有两种存储索引统计的方式，可以通过设置参数innodb_stats_persistent的值来选择：
设置为on的时候，表示统计信息会持久化存储。这时，默认的N是20，M是10。
设置为off的时候，表示统计信息只存储在内存中。这时，默认的N是8，M是16。

如果你发现explain的结果预估的rows值跟实际情况差距比较大,使用analyze table t命令来修复
索引选错的处理：
    第一种：使用forceindex(*)来强行矫正索引
    第二种：我们可以考虑修改语句，引导mysql使用我们期望的索引
    第三种：在有些场景下，我们可以新建一个更合适的索引，来提供给优化器做选择，或者删掉用错的索引。

mysql如果选错索引可以使用 force index(*)来矫正索引
Select * from t force index(*) where a between 1 and 100 and b between 5000 and 10000

第十一讲 怎么给字符串加索引
mysql是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引，如果不指定长度，那就会是整个字符串。
alter table SUser add index index2(email(6));
使用前缀索引后，可能会导致查询语句读取数据的次数变多。
使用前缀索引，定义好长度就能做到既节省空间，又不用增加太多的查询成本。
找出适合长度的方法：
首先使用select count(distinct email) as L from User
然后依次选取不同长度的前缀值来进行比较数量

因为前缀索引只有部分数据，所以使用前缀索引的话就用不上索引覆盖了(需要回表查询出相关信息)
如果我们能够确定业务需求里面只有按照身份证进行等值查询的需求：
    第一种方法：使用倒序存储。比如身份证的后六位就可以使用前缀索引了。
    第二种方法：使用hash字段，在表上再创建一个整数字段，在这个字段上面使用索引，然后每次插入新记录的时候，都同时用crc32()这个函数得到校验码填到这个新字段。

第十二讲 为什么我的mysql会抖一下
MySQL偶尔“抖”一下的那个瞬间，可能就是在刷脏页（flush）
四种情况：
1.Innodb的redilog写满了，这个时候系统会停止更新操作，把checkpoint往前移动，redolog留出空间可以继续写。
2.系统内存不足了，需要新的内存页，就要淘汰一些数据页，空出内存给别的数据页使用，如果淘汰的是脏页，就要先将脏页flush到磁盘。
3.Mysql认为系统空闲的时候会进行flush操作。
4.mysql正常关闭的时候会将所有的脏页刷到磁盘，下次启动的时候就可以直接从磁盘中读取数据。

如何避免：
    合理的设置innodb_io_capacity的值，并且平时要多关注脏数据页的比例，不要让它经常接近75%。

脏页比例是通过过Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total得到的
mysql> select VARIABLE_VALUE into @a from global_statuswhere VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
select VARIABLE_VALUE into @b from global_status whereVARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
select @a/@b;
在InnoDB中，在刷脏页时如果邻居也是脏页也可以一起刷，innodb_flush_neighbors参数就是用来控制这个行为的，值为1的时候会有上述的“连坐”机制，值为0时表示不找邻居，自己刷自己的

第十三讲 为什么表数据删一半，文件大小不变
参数innodb_file_per_table
1.这个参数设置为OFF表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起。
2.这个参数设置为ON表示的是，每个Innodb的数据存储在.ibd的后缀文件中。

将innodb_file_per_table参数设置为on是推荐的做法，在删除表时，系统会直接删除这个文件。
innodb的数据是按页存储的，如果我们删掉了一个数据页上面的所有记录，整个数据页就可以被复用。
如果我们用delete命令将整个表的数据删除，会将所有的数据页标为可复用，所以文件大小不会变。
在经过大量增删改的表都会存在数据空洞，去掉空洞的方法：
    1.重建表，将数据重新插入到新表(可以使用命令alter table A engine=InnoDB来重建表)

第十四讲 count(*)这么慢，怎么办
Innodb是索引组织表，主键索引叶子节点存储的是数据，普通索引存储的是主键值。因此，
MySQL优化器会找到最小的那棵树来遍历，在保证逻辑正确的前提下，尽量减少扫描的数据，是数据库系统设计的通用法则之一。
MyISAM表虽然count()很快，但是不支持事务;
showtable status命令虽然返回很快，但是不准确；
InnoDB表直接count()会遍历全表，虽然结果准确，但会导致性能问题。
对 count( c 主键主 id)，来说来 ，InnoDB引擎会遍历整张表，把每一行的id值都取出来，返回给server层。server层拿到id后，判断是不可能为空的，就按行累加。
对于count(1)来说， ，InnoDB引擎遍历整张表，但不取值。server层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。 单看这两个用法的差别的话，你能对比出来，count(1)执行得要比count(主键id)快。因为从引擎返回id会涉及到解析数据行，以及拷贝字段值的操作。

第十五讲 日志和索引
Mysql的两阶段性提交保证了redolog和binlog都是完整的才会提交。
binlog有着redolog无法替代的功能
归档，redolog是循环写，binlog是追加写
​Mysql系统依赖binlog

第十六讲 order by是怎么工作的
通过索引查找出主键然后到表中找到对应的字段放在sort_buffer中进行排序之后返回。
如果排序的数量小于sort_buffer_size的话就可以在内存中进行排序，如果数据量过大，内存放不下，就得利用磁盘临时文件排序。
查看排序是否使用了临时排序
打开optimizer_trace，只对本线程有效
SET optimizer_trace=‘enabled=on’;
@a 保存Innodb_rows_read的初始值
select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = ‘Innodb_rows_read’;
执行语句
查看 OPTIMIZER_TRACE 输出
SELECT * FROM information_schema.OPTIMIZER_TRACE\G​
@b 保存Innodb_rows_read的当前值
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = ‘Innodb_rows_read’;
计算Innodb_rows_read差值
select @b-@a;​

内存放不下时，就需要使用外部排序，外部排序一般会使用归并排序算法，将需要排序的数分成n份，每一份单独排序之后再合成一个大的有序文件。
由于使用sort_buffer排序的话如果数据量过大就会生成很多临时文件 不太好
我们可以修改一个参数让mysql使用另外一种算法 rowId排序(rowId排序是只将要排序的字段放入sort_buffer)
SETmax_length_for_sort_data = 16;(返回字段总长度超过16就会使用rowId排序)
    1.初始化sort_buffer，确定需要放入排序的字段
    2.根据where条件找到第一个满足条件的数据的id
    3.再根据主键id到表中取出需要排序的字段 比如name和id
    4.再取出下一个满足条件的id
    5.重复3、4步直到取出所有满足条件的数据
    6.对sort_buffer中的数据按照字段进行排序
    7.遍历排序结果，根据id到原表中取出需要返回的字段

mysql的设计思想：只能内存够，就要多利用内存操作
对于innodb来说，rowId排序会要求回表多造成磁盘读，因此不会被优先选择

第十七讲 如何正确显示随机消息
从表中随机取出三条记录：select * from table order by rand() limit 3
使用这种方法会用到临时表和排序 比如10000条数据，整个执行过程会扫描20003行数据
1.取出10000条数据放到临时表
｜ 临时表使用memory引擎，有两个字段 第一个字段double类型用来存储0~1随机数，第二个字段用来存储返回字段
2.从临时表中取出10000条数据放在sort_buffer中进行排序
3.取出三条结果返回

rowid的实际意义是每个引擎用来唯一标识数据行的信息
｜ 对于有主键的表，rowid就是表的主键，对于没有主键的表，rowid就是系统生成的对这行数据的标识。
优先队列排序
    1.从1000个准备排序的元素中取出前三行，构成一个堆。
    2.取出下一个和堆中最大的进行比较，如果小于则交换。
    3.重复第二步，直到最后一个完成比较。

随机算法1(效率高，但是如果主键不是顺序的可能会存在漏洞)
    首先取得这个表主键的最大值M和最小值N。
    用随机函数生成最大值和最小值之间的数 X=(M-N)*rand()+N
    取不小于X的第一个数
    mysql> select max(id),min(id) into @M,@N from t ; 
    set @X= floor((@M-@N+1)*rand() + @N);
    select * from t where id >= @Xlimit 1;

随机算法2(共需要扫描N+X+1行)
    首先取出表的总行数N
    计算出X=floor(N*rand()) floor函数为取整数部分
    再用limit X, 1取得一行
    mysql> select count(*) into @N from t;
    set @X = floor(@N * rand());
    set @sql = concat("select * from t limit ", @X, ",1");
    prepare stmt from @sql; 
    execute stmt;
    DEALLOCATE prepare stmt;
    select @X 查看变量

第十八讲 为什么这些SQL语句逻辑相同，性能差距却巨大
如果对字段使用了函数，就用不上索引了。（对索引字段做函数操作可能会破坏索引值的有序性，因此优化器就放弃走树搜索功能）
在程序设计语言里面，做自动类型转换的时候，为了避免数据在转换过程中由于截断导致数据错误，也都是“按数据长度增加的方向”进行转换的。

第十九讲 为什么我只查询一行的语句 执行这么慢？
通过show processlist命令可以查看全部线程以及情况
可以通过kill connection|query id来杀死进程

带lock in share mode的sql语句是当前读，可以读到当前事务里面修改后的值,普通的select是一致性读，需要执行完undolog之后才返回正确结果。

第二十讲 幻读是什么幻读有什么问题
幻读:
1.在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。
2.上面 session B 的修改结果，被 session A 之后的 select 语句用“当前读”看到，不能称为幻读。幻读仅专指“新插入的行”。

第二十一讲 为什么我只改一行的语句，锁这么多
加锁规则两个"原则"，两个"优化"，一个"bug"
1.原则1：加锁的基本单位是next-key，是前开后闭的
2.原则2：查找的过程中访问到对象才会加锁
3.优化1：索引上的等值查询，给唯一索引加锁时，next-key会退化成行锁
4.优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件时，next-key会退化成间隙锁
5.一个bug：唯一索引上的范围查询 会访问到不满足条件的第一个值为止

第二十二讲 Mysql有哪些饮鸩止渴的性能优化方法
短连接风暴
数据库出现too many connections
第一种方法：先处理掉占着连接不工作的进程
查看所有进程:show processlist
杀死进程:kill connection + id

        第二种方法：减少连接过程的损耗
            重启数据库,跳过权限认证,使用参数–skip-grant-tables启动。慎用



慢查询性能问题
    引发性能问题的慢查询可能为以下三种情况：
        1.索引没设计好
            紧急创建索引来修复

        2.SQL语句没有写好
        3.Mysql选错了索引
            给语句加上force index(索引名)强制使用索引



QPS突增问题
    断开数据库连接或者修改sql语句

第二十三讲 Mysql是怎么保证数据不丢失的
Binlog的写入机制
binlog写入逻辑：在事务执行的过程中，先把日志写到binlog cache事务提交的时候再把binlog cache写到binlog文件中
系统给binlog cache分配了一片内存，每个线程一个，参数binlog_cache_size用于控制单个线程内binlog cache所占的内存大小。如果超过了这个大小就要暂存在磁盘中。
事务提交的时候，执行器会把binlog_cache里的完整事务写入binlog中并清空binlog_cache
每个线程都自己的binlog_cache，但是共用一个 binlog文件
写入机制：
sync_binlog=0，表示落盘不受MySQL控制，由文件系统去控制它的落盘；
sync_binlog=1，这是最安全的方式，在系统故障时最多只丢失一个事务的binlog日志，但是没次提交事务都落盘会对性能有所影响。
sync_binlog=n，则表示每n次事务，MySQL调用文件系统的刷新操作落盘；

Redolog的写入机制
    事务的执行过程中，redolog是会先写到redologbuffer
    redolog buffer里面内容不需要每次生成后都持久化到磁盘
    redelog可能存在的三种状态
        存在redolog buffer中，物理上是在mysql执行的过程中。
        写到磁盘，但还没有持久化，物理上是在文件系统的page cache中
        持久化到磁盘，也就是硬盘中

    为了控制redolog的写入策略，innodb提供了innodb_flush_log_at_trx_commit参数
        0：表示每次事务提交都只把redolog留在redolog buffer
        1：每次事务提交都把redolog持久化到磁盘
        2：每次事务提交都只是把redolog写到page cache

    innodb后台有一个线程，每隔一秒就会把redolog buffer中的日志调用write写到文件系统中的page cache，然后调用fsync持久化到磁盘
        因此可能有还没提交的事务被持久化到磁盘

    另外两种会让还没提交的事务持久化到磁盘的操作
        一种是redolog buffer的占用空间即将达到innodb_log_buffer_size一半的时候，后台线程会主动写盘，只是写到page cache并未同步到磁盘
        另一种是并行的事务提交时，可能会顺带将这个事务的redolog buffer一起持久化到磁盘(例如innodb_flush_log_at_trx_commit为1的时候)

    Mysql双'1'配置：sync_binlog和innodb_flush_log_at_trx_commit都设为1，也就是说在一个事务完整提交之前需要等待两次刷盘，一次是redolog，一次是binlog