MySQL实战

mysql数据库本身被堵住了

长时间不返还

rr下，为了数据一致性，有大量回滚

表中数据导致，回表次数庞大

关掉占着连接不工作的线程

减少连接过程的消耗

索引没有设计好

sql语句没写好

mysql选错了索引

如果业务方会下掉业务，从白名单去掉sql

单独数据库用户的话，删掉账户

查询重写成select 1返回，慎用

主备延迟 T3-T1

备库 show slave status

原因

循环复制

可靠性优先策略

可用性优先策略

强制走主库方案

sleep方案

判断主备无延迟方案

配合semi-sync方案

等主库位点方案

等GTID方案

Flashback

全量备份+增量备份

延迟复制备库

账号分离

制定操作规范

小表做驱动表

1. 语句执行过程不可以一边读数据一边直接得到结果，需要额外内存；
2. join_buffer是无序数组，sort_buffer是有序数组，临时表是二维表结构；
3. 如果执行逻辑需要用到二维表特性，就会优先考虑使用临时表

1. 如果对group by的结果没有排序要求，要在语句后面加order by null;
2. 尽量让group by过程用上索引，确认方法是explain结果里没有Using temporary和Using filesort;
3. 如果group by需要统计的数据量不大，尽量只使用内存临时表，可以通过适当调大tmp_table_size参数来避免用到磁盘临时表；
4. 如果数据量大，使用SQL_BIG_RESULT来告诉优化器直接使用排序算法得到group by的结果

唯一键冲突

回滚

批量申请自增id，按照1，2，4，8翻倍递增申请，没用到的就会空了

表的自增id达到上限后，再申请时它的值就不会改变，进而导致继续插入数据时报主键冲突的错误

row_id达到上限后，则会归0再重新递增，如果出现相同的row_id，后写的数据会覆盖之前的数据

Xid只需要不在同一个binlog文件中出现重复值即可。虽然理论上会出现重复值，但是概率极小，可以忽略不计

InnoDB的max_trx_id递增值每次MySQL重启都会被保存起

thread_id达到上限后会归0再递增，底层有代码逻辑去判断不冲突使用

write pos代表当前记录位置，一边写一边后移；checkpoint代表当前要擦除的位置，擦除记录前把记录更新到数据文件，擦除后后移

如果write pos追上checkpoint，就没有记录空间了，需要把checkpoint向后推进擦除一些记录

redo log保证数据库发生异常重启时之前提交的记录不会丢失，这个能力成为crash-safe

物理日志，循环写，记录页做了什么改动

innodb_flush_log_at_trx_commit

事务执行过程中，先把日志写到binlog cache,事务提交的时候，再把binlog cache写到binlog文件中

逻辑日志，追加写

两种模式，statement格式会记录sql语句，row格式记录行的内容，记两条，更新前和更新后

sync_binlog

binlog组提交

prepare 阶段

commit 阶段

如果数据库在记录此事务的binlog之前和过程中发生crash。数据库在恢复后认为此事务并没有成功提交，则会回滚此事务的操作。与此同时，因为在binlog中也没有此事务的记录，所以从库也不会有此事务的数据修改。

如果数据库在记录此事务的binlog之后发生crash。此时，即使是redo log中还没有记录此事务的commit 标签，数据库在恢复后也会认为此事务提交成功（因为在上述两阶段过程中，binlog写入成功就认为事务成功提交了）。它会扫描最后一个binlog文件，并提取其中的事务ID（xid），InnoDB会将那些状态为Prepare的事务（redo log没有记录commit 标签）的xid和Binlog中提取的xid做比较，如果在Binlog中存在，则提交该事务，否则回滚该事务。这也就是说，binlog中记录的事务，在恢复时都会被认为是已提交事务，会在redo log中重新写入commit标志，并完成此事务的重做（主库中有此事务的数据修改）。与此同时，因为在binlog中已经有了此事务的记录，所有从库也会有此事务的数据修改。

设置binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count参数，减少binlog的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。

将sync_binlog设置为大于1的值，比较常见是100-1000。风险是主机掉电时会丢binlog日志

将innodb_flush_log_at_trx_commit设置为2。风险是主机掉电时会丢数据

读未提交

读已提交

可重复读

串行化

读未提交

读已提交

可重复读

串行化

show variables like 'transaction_isolation'

select * from information_schema.innodb_trx where time_to_sec(timediff(now(),trx_started))>60

基于整个库的意思是说一个事务内，整个库的修改对该事务都是不可见的。如果在事务内select t表，另外的事务执行了DDL t表，根据发送的时间，要么锁住要么报错

每个事务都有一个事务ID，叫做transaction id，严格递增

事务在启动时，找到已提交的最大事务ID记为up_limit_id

事务在更新一条语句时，比如id=1改为id=2，会把id=1和该行之前的row trx_id写到undo log里，并且在数据页上把id的值改为2，并且把修改这条语句的transaction id记到该行行头

再定一个个规矩，一个事务要查看一条数据时，必须先用该事务的up_limit_id<transaction id，则只能去undo log去取，去undo log查找数据的时候，也需要做比对，必须up_limit_id>transaction id，才返回数据

当前读都是先读后写，只能读当前值，会更新事务内的up_limit_id为该事务的transaction id

快照读的情况下，RR不能更新事务内的up_limit_id，而RC每次都会把up_limit_id更新为快照读之前最新已提交事务的transaction id，则RC不能可重复读

当前读的情况下，RR是利用record + gap lock 来实现的，而RC没有gap，所以RC不能可重复读

哈希表

有序数组

搜索树

主键索引

非主键索引

覆盖索引

前缀索引

索引下推

唯一索引与普通索引的选择

增大区分度

alter table T engine=InnoDB

条件字段函数操作

隐式类型转换

隐式字符编码转换

数据量太少，系统觉得没必要走索引

走索引不比全表扫描代价低

in()中的数量>全表总行数x9.3%

全局读锁 Flush Tables with read lock(FTWRL)

场景：全库逻辑备份

表锁

元数据锁MDL

间隙锁+行锁

前开后闭

出现的原因

mysql5.6开始引入了page cleaner线程，专门负责刷脏

innodb_io_capacity

刷盘速度

刷脏算法

某一个数据库的所有的表数据，索引文件全部放在一个文件中，默认这个共享表空间的文件路径在data目录下。 默认的文件名为:ibdata1  初始化为10M。

优点：可以将表空间分成多个文件存放到各个磁盘上（表空间文件大小不受表大小的限制，如一个表可以分布在不同的文件上）。数据和文件放在一起方便管理。

缺点：所有的数据和索引存放到一个文件中，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，这样对于一个表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，日值系统这类应用最不适合用共享表空间。

每一个表都将会生成以独立的文件方式来进行存储，每一个表都有一个.frm表描述文件，还有一个.ibd文件。 其中这个文件包括了单独一个表的数据内容以及索引内容，默认情况下它的存储位置也是在表的位置之中。

优点：
    1.每个表都有自已独立的表空间。
    2.每个表的数据和索引都会存在自已的表空间中。
    3.可以实现单表在不同的数据库中移动。
    4.空间可以回收（除drop table操作处，表空不能自已回收）
        a.Drop table操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。
        b.对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。
        c.对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。

缺点：单表增加过大，如超过100个G。

先逻辑备份，然后修改配置文件my.cnf中的参数innodb_file_per_table参数为1，重启服务后将逻辑备份导入即可。

修改配置文件my.cnf中的参数innodb_file_per_table参数为1，重启服务后将需要修改的所有innodb表都执行一遍：alter table table_name engine=innodb;

 trx_id as 事务ID,
 trx_state as 事务状态,
 trx_started as 事务开始时间,
 trx_requested_lock_id as 事务需要等待的资源,
 trx_wait_started as 事务开始等待时间,
 trx_mysql_thread_id as 事务线程id,
 trx_query as 具体SQL语句,
 trx_operation_state as 事务当前操作状态,
 trx_tables_in_use as 使用表数量,
 trx_tables_locked as 拥有锁数量,
 trx_lock_memory_bytes as 锁住内存大小,
 trx_rows_locked as 事务锁住的行数,
 trx_rows_modified as 事务更改的行数,
 trx_concurrency_tickets as 事务并发票数,
 trx_unique_checks as 是否唯一性检查,
 trx_foreign_key_checks as 是否外键检查,
 trx_last_foreign_key_error as 最后的外键错误

lock_id as 锁ID,
 lock_trx_id as 拥有锁的事务ID,
 lock_mode as 锁模式,
 lock_type as 锁类型,
 lock_table as 被锁的表,
 lock_index as 被锁的索引,
 lock_space as 被锁的表空间号,
 lock_page as 被锁的页号,
 lock_rec as 被锁的记录号,
 lock_data as 被锁的数据

requesting_trx_id as 请求锁的事务ID,
 requested_lock_id as 请求锁的锁ID,
 blocking_trx_id as 当前拥有锁的事务ID,
 blocking_lock_id as 当前拥有锁的锁ID