目录
怎么解决由这种热点行更新导致的性能问题呢？（即短时间大量更新同一行数据）
普通索引和唯一索引如何选择？（业务可以保证不写入重复数据的情况下）
binlog的三种格式：statement、row、mixed
一、基本架构
1.基础架构
MySQL可以分为Server层和存储引擎层两部分。
Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。
连接器
连接器负责跟客户端建立连接、获取权限、维持和管理连接。
尽量使用长连接：
全部使用长连接后，你可能会发现，有些时候 MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。
解决方案：
1.定期断开长连接。
2.如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。
查询缓存
不要使用查询缓存，因为查询缓存往往弊大于利（命中率低，MySQL 8.0 版本已删除该模块）。
分析器
词法分析和语法分析。
优化器
优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。
执行器
开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限。
执行器的执行流程是这样的（下面是无索引情况，有索引“取满足条件的第一行”）：
1.调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；
2.调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
3.执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。
2.日志系统
redo log（重做日志）
WAL 机制，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志（顺序写磁盘），再写磁盘（随机写磁盘），也就是先写粉板，等不忙的时候再写账本。
InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么这块“粉板”总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示。
write pos 是当前记录的位置，checkpoint 是当前要擦除的位置。write pos 和 checkpoint 之间的是“粉板”上还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。
好处：
1.一条更新语句进行执行的时候，InnoDB引擎会把更新记录写到redo log日志中，然后更新内存，此时算是语句执行完了，然后在空闲的时候或者是按照设定的更新策略将redo log中的内容更新到磁盘中。避免随机写磁盘。
2.有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。
redo log 的写入机制
先写入redo log buffer（内存），再写入文件系统的 page cache（共享的，并行事务可能一起被持久化到磁盘），最后持久化到磁盘。
innodb_flush_log_at_trx_commit=1表示每次事务提交时都将 redo log 直接持久化到磁盘。
InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用fsync持久化到磁盘。
binlog（归档日志）
redo log和binlog有以下三点不同：
1.redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
2.redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2 这一行的 c 字段加 1 ”。
3.redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
binlog 的写入机制
事务执行过程中，先把日志写到 binlog cache（内存），事务提交的时候，再写到 binlog files中（文件系统），每个线程有自己的binlog cache，但是共用同一份 binlog 文件。最后图中的 fsync，才是将数据持久化到磁盘的操作。
write 和 fsync 的时机，是由参数 sync_binlog 控制的。sync_binlog=1表示每次提交事务都会执行 fsync。
MySQL 的“双 1”配置，指的是sync_binlog=1和innodb_flush_log_at_trx_commit=1。
下图是update 语句的执行流程图，图中浅色框表示是在 InnoDB 内部执行的，深色框表示是在执行器中执行的。
3.文件组织架构
1.日志文件：slow.log(慢日志)，error.log(错误日志)，general.log(基本日志)
2.配置文件：my.cnf
3.数据库：performance_schema，mysql，information_schema，sys
4.innodb存储引擎（框中部分）： .frm 文件用来描述表的格式或者说定义； .ibd 文件就是每一个表独有的表空间，文件存储了当前表的数据和相关的索引数据
实际目录：
4.InnoDB的逻辑存储结构
从InnoDB逻辑存储结构看，所有的数据都被逻辑的存放在一个空间中，为表空间（tablespace）。表空间又由段（segment）、区（extend）、页（page）组成，页也可以别成为块（block）。
InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。
5.实战技巧
为什么MySQL有时候会抖动一下？
1. redo log 写满了，要 flush 脏页（脏页：当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。）
2. 内存不够用了，要先将脏页写到磁盘。
InnoDB 刷脏页的控制策略，有几个相关参数。
为什么数据删掉一半，表文件大小不变
因为可能是数据页被复用了。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。删除和插入数据都会造成空洞。
减少表“空洞”方法是重建表：alter table t engine=InnoDB
kill不掉某些语句的原因
线程没有执行到判断线程状态的逻辑（比如IO压力过大）
终止逻辑耗时较长（从 show processlist 结果上看也是 Command=Killed，比如超大事务执行期间被 kill、大查询回滚等）
从全表扫描看MySQL层和InnoDB层对内存的管理
MySQL层
MySQL 是“边读边发的”，服务端并不需要保存一个完整的结果集。具体如下：
1.获取一行，写到 net_buffer 中；
2.net_buffer 写满，调用网络接口发出去；
3.发送成功，就清空 net_buffer；
4.发送函数返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待。直到网络栈 重新可写。
InnoDB层
内存的数据页是在 Buffer Pool (BP) 中管理的。InnoDB 内存管理用的是改进的最近最少使用 (LRU) 算法。
在 InnoDB 实现上，按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域。
新插入的数据页，都被放到 old 区域；若这个数据页在 LRU 链表中存在的时间超过了 1 秒，才把它移动到链表头部。
Java主流数据库连接池
c3p0：开源的JDBC连接池，单线程，性能较差，适用于小型系统。
DBCP：由Apache开发的一个Java数据库连接池项目，单线程，并发量低，性能不好，适用于小型系统。
Druid：Druid是Java语言中最好的数据库连接池。主要特色：为分析监控设计；快速的交互式查询；高可用；可扩展；Druid是一个开源项目，源码托管在github上。
二、事务相关
1.定义
原子性（atomicity）：所有操作要么全部成功，要么全部失败
一致性（consistency）：数据库总是从一个一致性状态转移到另一个一致性状态
隔离性（isolation）：一个事务在最终提交以前，对其他事务不可见
持久性（durability）：一旦提交，则永久保存
2.事务隔离
读未提交（read uncommitted）
一个事务还没提交时，它做的变更就能被别的事务看到。也称为脏读。
读提交（read committed）
一个事务提交之后，它做的变更才会被其他事务看到。两次同样查询得到结果不一样，也称作不可重复读。大多数数据库系统的默认隔离级别。
可重复读（repeatable read）
一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。但会导致幻读。
幻读
幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。
在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。且幻读仅专指“新插入的行”。
如何解决幻读：InnoDB采用间隙锁（数据行是可以加上锁的实体，数据行之间的间隙，也是可以加上锁的实体）防止幻读。但是间隙锁会影响并发度。间隙锁和行锁合称 next-key lock，帮我们解决了幻读的问题。
串行化（serializable）
最高隔离级别，“写”会加“写锁”，“读”会加“读锁”，事务串行执行。
例子：（读未提交：v1=2,v2=2,v3=2；读提交：v1=1,v2=2,v3=2；可重复读：v1=1,v2=1,v3=2；串行化：v1=1,v2=1,v3=2）
mysql> create table T(c int) engine=InnoDB;
insert into T(c) values(1);
事务隔离的实现
在“可重复读”隔离级别下，这个一致性读视图（即快照，区别于普通视图）是在事务启动时创建的，整个事务存在期间都用这个视图。在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。这里需要注意的是，“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；而“串行化”隔离级别下直接用加锁的方式来避免并行访问。
这里我们展开说明“可重复读”。在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。
假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志（undo log）里面就会有类似下面的记录。如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC），以此来支持高并发。
回滚日志总不能一直保留吧，什么时候删除呢？答案是，在不需要的时候才删除。也就是说，系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除。什么时候才不需要了呢？就是当系统里没有比这个回滚日志更早的 read-view 的时候。
MVCC里如何创建快照
InnoDB 的行数据有多个版本，每个数据版本有自己的 row trx_id，每个事务或者语句有自己的一致性视图。
下图就是一个记录被多个事务连续更新后的状态。图 2 中的三个虚线箭头，就是 undo log；而 V1、V2、V3 并不是物理上真实存在的，而是每次需要的时候根据当前版本和 undo log 计算出来的。
在实现上， InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。
这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。InnoDB 利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力。
事务更新和查询区别
事务更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”。即读取最新版本的数据，然后更新。
事务查询数据是一致性读（隔离级别是可重复读情况下）。
读提交的逻辑和可重复读的逻辑类似，它们最主要的区别是：
在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图；
在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图。
避免使用长事务
原因：回滚记录都必须保留，这就会导致大量占用存储空间；长事务还占用锁资源，也可能拖垮整个库。
3.事务的启动方式
MySQL 的事务启动方式有以下几种：
第一种：显式启动事务语句， begin/start transaction。配套的提交语句是 commit，回滚语句是 rollback。
第二种：set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。
建议你总是使用 set autocommit=1, 通过显式语句的方式来启动事务。另外注意，begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。
你可以在information_schema 库的 innodb_trx 这个表中查询长事务：
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60
三、锁
根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表锁和行锁三类。表锁是MySQL层面，行锁是引擎层面。
1.全局锁
全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。
全局锁的典型使用场景是，做全库逻辑备份。官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数-single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。-single-transaction 方法只适用于所有的表使用事务引擎的库。如果有的表使用了不支持事务的引擎，那么备份就只能通过 FTWRL 方法。
2.表锁
分类
MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。
一类是表锁。语法是 lock tables … read/write。与 FTWRL 类似，可以用 unlock tables 主动释放锁。对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。
另一类表级的锁是 MDL锁（元数据锁）。MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。
给一个小表加个字段，导致整个库挂了的原因
原因解释：session C 会被 blocked，是因为 session A 的 MDL 读锁还没有释放，而 session C 需要 MDL 写锁，因此只能被阻塞。session C 之后所有对表的增删改查操作都需要先申请 MDL 读锁，就都被锁住。如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新 session 再请求的话，这个库的线程很快就会爆满。
如何安全地给小表加字段
正常场景首先我们要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。要考虑先暂停 DDL，或者 kill 掉这个长事务。
考虑一下另一场景。如果你要变更的表是一个热点表比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...
3.行锁
两阶段锁
行锁分成读锁和写锁。
在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。
下图事务B会阻塞。如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
死锁和死锁检测
当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。
下图事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态。
MySQL解决死锁办法
死锁超时机制。直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置，默认值是 50s。对于在线服务来说，这个等待时间往往是无法接受的。
死锁检测。发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。innodb_deadlock_detect 的默认值本身就是 on。但是死锁检测要耗费大量的 CPU 资源。
怎么解决由这种热点行更新导致的性能问题呢？（即短时间大量更新同一行数据）
方案一：临时把死锁检测关掉。意味着可能会出现大量的超时，这是业务有损的。
方案二：控制并发度。并发控制要做在数据库服务端。可以修改MySQL源码，对于相同行的更新，在进入引擎之前排队。或者通过中间件实现。或者从业务设计上实现。
3.实战技巧
MyISAM和InnoDB比较
MyISAM读性能好：MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比MyISAM体积庞大不小。
InnoDB优势：InnoDB存储引擎，因为它支持事务，支持行锁，高并发处理性能更好，CPU及内存缓存页优化使得资源利用率更高。
InnoDB玩的是内存，内存越大，它的优势才能发挥出来。
MyISAM玩的是硬盘IO，转速越快，它的优势才能发挥出来。（MyISAM崩溃后损坏概率大，因为其只将数据写内存，定期刷到磁盘）
应用场景：
MyISAM，应用于日志型应用。
InnoDB，大数据量，高并发量的互联网业务场景（行锁，对提高并发帮助很大；事务，对数据一致性帮助很大）。
| MyISAM | InnoDB | |
| 外键 | 不支持 | 支持 |
| 事务 | 不支持 | 支持 |
| 锁表 | 表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作 | 行锁，操作时只锁某一行，不对其它行有影响，适合高并发的操作 |
| 缓存 | 只缓存索引，不缓存真实数据 | 不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响 |
| 占用空间 | 表空间占用小 | 表空间占用大 |
| 关注点 | 性能，查询速度快 | 事务 |
| 存储限制 | 256TB | 64TB |
| 数据压缩 | 支持 | 不支持 |
四、库表相关优化
1.库表结构优化
选择合适的数据类型
例如定长时尽量char而不是varchar，用TimeStamp而不是Datetime
缓存表
建立缓存表的原因则是优化检索和查询语句，这些查询语句经常需要特殊的表和索引，跟普通的OLTP操作表有一些区别。
汇总表
建立汇总表的原因就在于：无论是通过粗略的统计计算还是严格的计数，都比计算原始表的所有行有效的多。（计数器表思想：为了获得更高的并发性能，可将计数器保存在多行中，每次选择一行进行更新。update hit_counter set cnt=cnt+1 where slot=rand()*100，select sum(cnt) from hit_counter）
提高ALTER TABLE 操作的性能
首先，分析以下alter一个超大表低效率的原因：
1. mysql中alter表的机制是：创建一个符合alter目标的表，然后把数据全部插入到新表中，然后删除老表。
2. 在数据迁移时，每插入一行，需要对索引进行一次更新。效率低下且会产生索引碎片。
3. 当没有足够的内存，或者表上的索引非常多时，效率十分的低下。
对常见的场景，能使用的技巧只有两种：一种是先在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换；另外一种技巧是“影子拷贝”。影子拷贝的技巧是用要求的表结构创建一张和源表无关的新表，然后通过重命名和删表操作交换两张表。
当不需要改变表本身时（例如改变默认值），选择后者：
MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5;
ALTER COLUMN rental_duration SET DEFAULT 5;----只会修改.frm文件而不涉及表数据
当需要改变表本身时：
基本的技术是新建新的表产生新的.frm文件，然后替换掉已经存在的那张表的.frm
2.分区表
为什么使用分区表
在数据量超大的时候，B-Tree索引就无法起作用了，查询将产生大量随机IO，响应时间大到无法接受。
分区有利于管理非常大的表，它采用分而治之的逻辑，更多的是从管理的角度出发的。（进而可能产生如下优点：和单个磁盘或者文件系统分区相比，可以存储更多数据；优化查询；对于已经过期或者不需要保存的数据，可以通过删除与这些数据有关的分区来快速删除数据）
分区是MySQL的一个插件Plugin功能，将一张大表的数据在数据库底层分成多个分区文件（.ibd文件），即MySQL层一张表，引擎层多张表。因此，是数据库分的，对应用透明，代码无需修改任何东西。而分库分表则是应用层的处理。
分区表使用场景
分区的话则一般都是放在单机里的，用的比较多的是时间范围分区，方便归档和清理历史数据。
分区表对分区字段的限制
如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分。
访问分区表，尽量在where条件中带入分区列，让优化器过滤掉无需访问的分区。（可通过explain partitions select 。。查看优化器是否执行了分区过滤）
分区类型
range分区：按照给定的连续区间划分数据的归属，典型的就是按照时间维度的月份分区
list分区：按照某个字段上的规则，不同的数据离散地分布在不同的区中
hash分区：Hash分区可以将数据均匀地分不到预先定义的分区中
key分区：hash分区不用的是，key分区使用MySQL自定义的库函数进行分区
分区表的问题
MySQL 在第一次打开分区表的时候，需要访问所有的分区；
在 server 层，认为这是同一张表，因此所有分区共用同一个 MDL 锁。
基于上述缺点，如果团队里分库分表中间件维护的很好，建议使用分库分表，而不是分区表。
3.分库分表
为什么分库分表
分表解决单标海量数据的查询性能问题；分库解决高并发、磁盘IO、网络IO问题。
使用场景
单表达到几千万，每日增量几十万，查询性能很差，这时候考虑分表；
并发量扛不住，或者磁盘IO和网络IO消耗过大时，考虑分库。
垂直分库分表和水平分库分表
1.垂直分库
根据业务耦合性，将关联度低的不同表存储在不同的数据库。
2.垂直分表
基于数据库中的列进行，某个表字段较多，可以新建一张扩展表，将不经常用或者字段长度较大的字段拆出到扩展表中。
3.水平分库
以字段为依据（用户id是最常用的分库分表字段），按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。
4.水平分表
以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中。难以根本上解决问题。
分库分表带来的问题
事务支持；跨库join等问题
分库分表中间件
分为proxy和客户端式架构。如下图。常用的有：TDDL、sharding-jdbc、mycat。
4.实战技巧
临时表的使用
临时表一般用于处理比较复杂的计算逻辑，例如一些复杂的由于临时表是每个线程自己可见的（因为临时表在磁盘的文件名和内存的key都加上了thread_id），所以不需要考虑多个线程执行同一个处理逻辑时，临时表的重名问题。在线程退出的时候，临时表也能自动删除，省去了收尾和异常处理的工作。这是临时表是指磁盘临时表，区别于内存临时表（在union和group by原理中涉及）。
自增主键有时候会不连续，为什么？
情形一：唯一键冲突（插入一条记录时，自增主键先加1，在插入，但插入失败了，自增主键不回退）
情形二：回滚（但自增主键不会回滚）
五、索引
1.索引的常见模型
哈希表
这种结构适用于只有等值查询的场景（因为链表是往后追加，不有序），比如 Memcached 及其他一些 NoSQL 引擎。也是Hashmap原理。
有序数组
有序数组在等值查询和范围查询场景中的性能就都非常优秀。有序数组索引只适用于静态存储引擎，不修改的场景。
二叉搜索树
特点是父节点左子树所有结点的值小于父节点的值，右子树所有结点的值大于父节点的值。
“N 叉”树
为什么不用二叉树？索引不止存在内存中，还要写到磁盘上。树高太高，需访问太多数据块。
以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。
N 叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中了。
“N 叉”树——B-Tree
B树又叫做多叉平衡查找树。
1.所有键值分布在整颗树中（索引值和具体data都在每个节点里）；
2.任何一个关键字出现且只出现在一个结点中；
3.搜索有可能在非叶子结点结束（最好情况O(1)就能找到数据）。
“N 叉”树——B+Tree
1.所有关键字都在叶子节点出现，内部节点(非叶子节点并不存储真正的 data)；
2.为所有叶子结点增加了一个链指针，依关键字的大小自小而大顺序链接的双向链表。
B+树的优点：
1.单一节点存储更多的元素（因为不含有对应的值，仅仅含有键），使得查询的IO次数更少。
2.所有查询都要从根节点查找到叶子节点，查询性能稳定，相对于B树更加稳定，因为B+树只有叶子节点存储了对应的值信息。
3.所有叶子节点形成有序双向链表，对于SQL的范围查询以及排序查询都很方便。
4.B/B+树的共同优点的每个节点有更多的孩子，插入不需要改变树的高度，从而减少重新平衡的次数，非常适合做数据库索引这种需要持久化在磁盘，同时需要大量查询和插入的应用。树中节点存储这指向页的信息，可以快速定位到磁盘对应的页上面。
2.InnoDB索引模型
无索引
没有索引的情况下，不论是以主键还是其他列作为搜索条件，只能沿着页的双链表从左到右依次遍历各个页。
辅助索引（二级索引）
1.找到对应的叶子节点，获取主键值，然后再通过聚簇索引中的B+树检索到对应的叶子节点，然后获取整行数据。（回表，回表是一行行搜索主键索引的，是随机读取磁盘的操作。）
2.InnoDB二级索引的叶子节点存的是主键值，而不是具体数据，这减小了移动数据或者数据页分裂时维护二级索引的开销。
聚簇索引
1.以主键值的大小为页和记录的排序规则，在叶子节点处存储的记录包含了表中所有的列。
2.InnoDB默认创建的主键索引是聚簇索引，其它索引都属于辅助索引；InnoDB必须要有，且只有一个聚簇索引。
示例，先通过辅助索引k找到对应的主键值，再通过主键索引找到对应的列值（数据页内部300,500,600是通过二分法来定位记录）。
（create table T(id int primary key,
k int not null,
name varchar(16),
index (k))engine=InnoDB;）
索引维护
如果插入新的行 ID 值为 700，则只需要在 R5 的记录后面插入一个新记录。如果新插入的 ID 值为 400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。而更糟的情况是，如果 R5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。影响性能和空间利用率。
覆盖索引
如果一个索引包含所有要查询的字段，则称为覆盖索引。遵循最左前缀原则。
从辅助索引中查询得到记录，而不需要通过聚簇索引查询获得。使用覆盖索引的好处很明显，不需要查询出包含整行记录的所有信息，因此可以减少大量的I/O操作。
索引下推
这是MySQL 5.6 引入的内部优化。以联合索引（name, age）为例，select * from tuser where name like '张%' and age=10 and ismale=1;根据前缀索引规则，只能用name索引。可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
下面两个图是这两个过程的执行流程图。（左图为原始流程，右图为引入索引下推的流程）
4.InnoDB和MyISAM索引存储对比
MYISAM存储模式
主键索引和二级索引基本一样，是按列值与行号来组织索引的。它的叶子节点中保存的实际上是指向存放数据的物理块的指针
InnoDB存储模式
1.主键索引中的叶子节点则记录了主键值、事务id、用于事务和MVCC的回流指针以及所有的剩余列
2.二级索引的叶子节点存放的是KEY字段加主键值
注意：索引是在磁盘中的，用到时再加载到内存中的。
5.索引的类型
- B-Tree
- Hash：不支持hash索引时可创建伪hash，例如url很长时。
- FullText
- R-Tree
6.索引的种类
- 普通索引：仅加速查询
- 唯一索引：加速查询 + 列值唯一（可以有null）
- 主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
- 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
- 全文索引：对文本的内容进行分词，进行搜索
7.索引的优点
- 索引大大减少了服务器需要扫描的数据量
- 索引可以帮助服务器避免排序和临时表
- 索引可以将随机IO变为顺序IO
8.高性能的索引策略
索引列不能是表达式一部分
使用前缀索引
通过计算选择性得出截取的长度(因为选择性越高，查询效率越高)---减少索引占用空间
select count(DISTINCT LEFT(column_name,3)) / COUNT(*) from table_name;
select count(DISTINCT LEFT(column_name,4)) / COUNT(*) from table_name;压缩（前缀压缩）索引
MyISAM中使用
多列索引需要考虑选择合适的索引列顺序
经验法则：
1.在不考虑排序和分组的情况下，选择性最高的列优先放在最前面。
2.要考虑WHERE子句中的排序、分组和范围条件等其他因素。
3.查询性能也和值的分布有关，所以要兼顾极端的值分布问题。
使用聚簇索引
使用覆盖索引
9.实战技巧
基本经验
1.对于非常小的表：大部分情况下简单的全表扫描更高效。
2.中到大型表：索引非常高效。
3.特大型表：建立和使用索引的代价非常高，可以使用分库分表或分区技术代替。
哪些场景下应该使用自增主键，而哪些场景下使用业务主键
业务字段做主键的缺陷：
1.业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高
2.由于每个非主键索引的叶子节点上都是主键的值，如果业务字段较长，占用空间会很大。
使用业务字段做主键的场景：只有一个索引，该索引必须是唯一索引。即典型的 KV 场景。
普通索引和唯一索引如何选择？（业务可以保证不写入重复数据的情况下）
查询性能：两者相差无几。
更新性能：普通索引可以使用change buffer（用的是 buffer pool 里的内存，将更新记录在 change buffer，语句执行就结束了。注意与redo log的区别，redo log记录所有物理日志，是带写磁盘的操作），减少了随机磁盘访问，提升了性能。适用于写多读少的业务，例如账单类、日志类的系统。
（大量插入数据慢、内存命中率低的时候，可以考虑是否使用了主键索引，是否可以改为普通索引。）
MySQL内部选错索引怎么办？
通过sql语句执行时间或者explain看rows发现MySQL选错了索引可以采用如下方式解决：
对于由于索引统计信息不准确导致的问题，你可以用 analyze table tablename来解决。而对于其他优化器误判的情况，你可以在应用端用 force index 来强行指定索引，也可以通过修改语句来引导优化器，还可以通过增加或者删除索引来绕过这个问题。
给字符串加索引
直接创建完整索引，这样可能比较占用空间；
创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
创建hash索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。
对索引字段做函数操作，不走索引
对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。
隐式类型转换和隐式字符编码转换都是变相的使用了函数，也可能会导致不走索引。
生产环境增加或改变索引
假设你现在的服务是一主一备，主库 A、备库 B，这个方案的大致流程是这样的：
1.在备库 B 上执行 set sql_log_bin=off，也就是不写 binlog，然后执行 alter table 语句加上索引；
2.执行主备切换；
3.这时候主库是 B，备库是 A。在 A 上执行 set sql_log_bin=off，然后执行 alter table 语句加上索引。
六、查询性能优化
1.为什么查询速度会慢
查询需要在不同的地方花费时间，包括网络，CPU计算，生成统计信息和执行计划、锁等待（互斥等待）等操作，尤其是向底层存储引擎检索数据的调用操作，这些调用需要在内存操作、CPU操作和内存不足时导致的I/O操作上消耗时间，根据引擎不同，可能还会产生大量的上下文切换以及系统调用。
2.慢查询基础：优化数据库访问
是否想服务器请求了不需要的数据
MYSQL是否在扫描额外的记录
查询开销的三个指标：响应时间，响应时间，返回的行数
如果说发现查询需要扫描大量的数据但只返回少数的行，那么通常可以尝试下面的技巧去优化它：
1.使用索引覆盖扫描
2.改变库表结构。例如使用单独的汇总表
3.重写这个复杂的查询，让MySQL优化器能够以更优化的方式执行这个查询
3.重构查询方式
- 切分查询：将大查询切分成小查询
- 分解关联查询：对每一个表进行一次单表查询，然后再应用程序中进行关联
4.查询执行的原理
查询执行步骤
（图详见第一章）
1.客服端发送一条查询给服务器
2.服务器先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一个阶段。
3.服务器端进行SQL解析、预处理，在由优化器生成对应的执行计划。
4.MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询
5.将结果返回给客户端
MySQL能够处理的优化类型
重新定义关联表顺序、列表IN()的比较、提前终止查询、子查询优化、覆盖索引扫描、优化COUNT() 、MIN() 、 MAX()、将外连接转化成内连接
MySQL查询优化器的局限性
关联子查询：MySQL的子查询实现非常糟糕(5.6版本以后有改进)。最糟糕的一类查询是WHERE条件中包含IN()的子查询语句（通常建议使用EXISTS()等效的改写；一般建议使用左外连接(LEFT OUTER JOIN)代替子查询(?)）。
UNION的限制：MySQL无法将限制条件从外层"下推"到内层，这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上。
并行执行：MySQL无法利用多核特性来并行执行查询(貌似5.6以后有改进)
哈希关联：MySQL不支持哈希关联。
5.优化特定类型的查询
count的使用
MyISAM 表虽然 count(*) 很快，但是不支持事务；
show table status 命令虽然返回很快，但是不准确；
InnoDB 表直接 count(*) 会遍历全表，虽然结果准确，但会导致性能问题。
对于 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。count(1) 执行得要比 count(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。
对于 count(字段) 来说，
如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；
如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。
但是 count(*) 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。
所以结论是：按照效率排序的话，count(字段)<count(主键id)<count(1)≈count(*)，所以我建议你，尽量使用 count(*)。
如果自己计数可采用的方案：
用缓存系统保存计数（可能存在Redis中计数和MySQL中记录不一致）
在数据库保存计数（利用事务解决计数表和记录表不一致问题）
关联查询join的使用
MySQL优化器及我们平时主动优化的规则：
1.如果可以使用被驱动表的索引（join 语句执行过程中，驱动表是走全表扫描，而被驱动表是走树搜索），join 语句有优势的；
2.如果不能使用被驱动表的索引，尽量不要使用
3.在使用 join 的时候，应该让小表（结果集小）做驱动表
join的优化步骤：
1.先通过explain查看MySQL优化器是否选对了驱动表（第一行是驱动表，第二行是被驱动表），如果选对了则没问题。
a.第二行被驱动表上key显示有索引使用，则使用了被驱动表索引；
b.第二行被驱动表上key显示null，Extra显示Using join buffer（Blocked Nested Loop），则未使用被驱动表索引。（Blocked Nested Loop算法是在内存中比较的一种算法）。
2.如果优化器选错了驱动表，则用straight_join指定驱动表（在前的是驱动表），参照上述规则。这里注意改变驱动表会改变连接顺序，因此只用于内连接，外连接（左连接和右连接）不能用。
order by工作原理
假设语句select city,name,age from t where city='杭州' order by name limit 1000;
全字段排序：
直接查询得到整行（name、city、age）放入sort_buffer，如果内存够，排序就在内存中（sort_buffer）完成；如果内存放不下，则不得不利用磁盘临时文件辅助排序。
不适用的场景：如果查询要返回的字段很多的话，那么 sort_buffer 里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。
rowid 排序：
查询要排序的字段（name、id）放入sort_buffer进行排序，排序完根据id找到（name、city、age）。
MySQL 认为内存足够大，会优先选择全字段排序；内存太小才会选择rowid 排序。
MySQL 做排序是一个成本比较高的操作，可以通过联合索引（city, name）或者覆盖索引（city, name, age）使其不需要排序（因为索引本身就是排序的）。
union的使用
执行原理：
第一个子查询将查到的结果存入内存临时表，第二个子查询查到的结果往内存临时表插，最终内存临时表中的数据就是结果集。
union all 没有了“去重”的语义，就依次执行子查询，得到的结果直接作为结果集的一部分，发给客户端。
优化：
很多优化策略在UNION查询中都没法很好地使用
group by的使用
原理：
创建内存临时表，将结果存入内存临时表中。再对相关字段做排序（sort_buffer中进行），将结果返回客户端。（结果不需要排序可加上order by null）
优化方法：
select id%100 as m, count(*) as c from t1 group by m；
1.尽量让 group by 过程用上表的索引，确认方法是 explain 结果里没有 Using temporary 和 Using filesort；
2.如果 group by 需要统计的数据量不大，尽量只使用内存临时表；
3.也可以通过适当调大 tmp_table_size 参数，来避免用到磁盘临时表；
4.如果数据量实在太大，使用 SQL_BIG_RESULT 这个提示，来告诉优化器直接使用排序算法得到 group by 的结果。
优化子查询
尽可能使用关联查询代替
优化LIMIT分页
第一种在页面中限制分页数量；
第二种延迟关联（先使用覆盖索引查询到主键，后做一次关联返回所需列）。
另，分页的业务上的技巧：
1.将具体页数换成下一页或获取更多数据按钮（每页10条，返回11条，若11条存在则显示下一页）
2.缓存数据
6.实战技巧
只查一行数据也慢的原因
等待表锁、等待行锁或其他原因。可以通过show processlist找到相应的进程并kill掉。
数据库连接数暴涨如何处理
先处理掉那些占着连接但是不工作的线程，show processlist结合information_schema 库的 innodb_trx先kill掉事务外的空闲线程。
七、MySQL高可用架构
1.主备复制
双 M 结构主备一致性保证
主备同步流程（同时解决循环复制）：
从节点 A 更新的事务，binlog 里面记的都是 A 的 server id；
传到节点 B 执行一次以后，节点 B 生成的 binlog 的 server id 也是 A 的 server id；
再传回给节点 A，A 判断到这个 server id 与自己的相同，就不会再处理这个日志。所以，死循环在这里就断掉了。
一般将备库设置为只读（readonly）模式。
主备库之间的内部流程
备库 B 跟主库 A 之间维持了一个长连接。一个事务日志同步的完整过程是这样的：
1.在备库 B 上通过 change master 命令，设置主库 A 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量。
2.在备库 B 上执行 start slave 命令，这时候备库会启动两个线程，就是图中的 io_thread 和 sql_thread。其中 io_thread 负责与主库建立连接。
3.主库 A 校验完用户名、密码后，开始按照备库 B 传过来的位置，从本地读取 binlog，发给 B。
4.备库 B 拿到 binlog 后，写到本地文件，称为中转日志（relay log）。
5.sql_thread 读取中转日志，解析出日志里的命令，并执行。
binlog的三种格式：statement、row、mixed
有些 statement 格式的 binlog 可能会导致主备不一致（因为statement是原语句可能存在主备选择不同的索引执行等，而row记录了主键ID），所以要使用 row 格式。
因为有些 statement 格式的 binlog 可能会导致主备不一致，所以要使用 row 格式。但 row 格式的缺点是，很占空间。比如你用一个 delete 语句删掉 10 万行数据，用 statement 的话就是一个 SQL 语句被记录到 binlog 中，占用几十个字节的空间。但如果用 row 格式的 binlog，就要把这 10 万条记录都写到 binlog 中。这样做，不仅会占用更大的空间，同时写 binlog 也要耗费 IO 资源，影响执行速度。
MySQL 就取了个折中方案，也就是有了 mixed 格式的 binlog。mixed 格式的意思是，MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式。
MySQL 设置的 binlog格式（binlog_format）的设置不应该是statement，至少应该把 binlog 的格式设置为 mixed，很多也设为row。
2.主备切换
主备延迟
主备延迟最直接的表现是，备库消费中转日志（relay log）的速度，比主库生产 binlog 的速度要慢。
可能原因：
1.备库所在机器的性能差
2.备库的压力大（跑了很多查询，可搞多个从库或借助外部系统而减少使用备库）
3.大事务，比如一次性delete太多数据（要分成多次删除）；大表重建表DDL（使用 gh-ost 方案）
4.官方的 5.6 版本之前，MySQL 只支持单线程复制（即上一张图的sql_thread到data是单线程），由此在主库并发高、TPS 高时就会出现严重的主备延迟问题。后面MySQL推出了各种并行复制策略，如按库并行策略等。
主备切换的策略
由于主备延迟的存在，所以在主备切换的时候，就相应的有不同的策略。
可靠性优先策略：
1.判断备库 B 现在的 seconds_behind_master，如果小于某个值（比如 5 秒）继续下一步，否则持续重试这一步；
2.把主库 A 改成只读状态，即把 readonly 设置为 true；
3.判断备库 B 的 seconds_behind_master 的值，直到这个值变成 0 为止；
4.把备库 B 改成可读写状态，也就是把 readonly 设置为 false；
5.把业务请求切到备库 B。
这个切换流程，一般是由专门的 HA 系统来完成的，我们暂时称之为可靠性优先流程。
可用性优先策略：
如果我强行把步骤 4、5 调整到最开始执行，也就是说不等主备数据同步，直接把连接切到备库 B，并且让备库 B 可以读写。我们把这个切换流程，暂时称作可用性优先流程。这个切换流程的代价，就是可能出现数据不一致的情况。
在实际的应用中，我更建议使用可靠性优先的策略。毕竟保证数据准确，应该是数据库服务的底线。在这个基础上，通过减少主备延迟，提升系统的可用性。
2.一主多从架构
一主多从架构
下图中A 和 A’互为主备， 从库 B、C、D 指向的是主库 A。一主多从的设置，一般用于读写分离，主库负责所有的写入和一部分读，其他的读请求则由从库分担。
切换
一主多从结构在切换完成后，A’会成为新的主库，从库 B、C、D 也要改接到 A’。尽量使用GTID 模式来做一主多从的切换。
读写分离架构和可能的坑
客户端直连和带 proxy 的读写分离架构比较（如上面两图）：
1.客户端直连方案，查询性能稍微好一点儿，并且整体架构简单，排查问题更方便。但是这种方案，由于要了解后端部署细节，所以在出现主备切换、库迁移等操作的时候，客户端都会感知到，并且需要调整数据库连接信息。一般采用这样的架构，一定会伴随一个负责管理后端的组件，比如 Zookeeper，尽量让业务端只专注于业务逻辑开发。
2.proxy 的架构，对客户端比较友好。客户端不需要关注后端细节，但对后端维护团队的要求会更高。而且proxy也需要有高可用架构。
主从可能存在延迟，如果查询选择的是从库的话，可能存在延迟。解决办法：强制走主库方案；sleep 方案；判断主备无延迟方案；配合 semi-sync 方案；等主库位点方案；等 GTID 方案。
在一主一备的双 M 架构里，主备切换只需要把客户端流量切到备库；而在一主多从架构里，主备切换除了要把客户端流量切到备库外，还需要把从库接到新主库上。
主备切换有两种场景，一种是主动切换，一种是被动切换。而其中被动切换，往往是因为主库出问题了，由 HA 系统发起的。
3.实战技巧
如何判断数据库出问题了
select 1判断：只能证明进程存在，如果并发数过高，线程都在等待锁，其实系统不可用，但是检测还是正常。
查表判断：但空间满了，检测不出来
优先考虑 update 系统表，然后再配合增加检测 performance_schema 的信息，此方案较为推荐。
八、备份与恢复
1.备份的分类
按照是否能够继续提供服务
热备份：（在线备份）在数据库运行的过程中进行备份，并且不影响数据库的任何操作
温备份：能读不能写，在数据运行的过程中进行备份，但是对数据有影响，如需要加全局锁保证数据的一致性。
冷备份：（离线备份）在停止数据库的情况下，复制备份数据库的物理文件。
按照备份后文件的内容分类
备份文件是可读的文本文件，比如sql语句，适合数据库的迁移和升级，但是恢复时间比较长。
物理备份：复制数据库的物理文件
按照备份数据库的内容分类
完全备份：对数据库进行一个完整的备份
增量备份：在完全备份的基础上，对数据库的增量进行备份
日志备份：只要是对binlog的备份
2.逻辑备份与恢复
常见的做法是用全量备份加上应用 binlog 来实现的。
备份
每周全量备份，详细如下：
每周日凌晨3点全量备份，使用mysqldump将整个数据库导出，并会删除上周留下的mysq-bin.00000*，然后对mysql的备份操作会保留在bak.log文件中。
mysqldump -uroot -p123456 -q --no-autocommit --flush-logs --single-transaction --master-data=2 > gxt.sql
–single-transaction：能保证导出时数据库的一致性状态
–master-data=2：选项将会在输出SQL中记录下完全备份后新日志文件的名称，用于日后恢复时参考
恢复
如果数据量很大，建议先关闭binlog日志功能。
利用全备的sql文件中记录的CHANGE MASTER语句，binlog文件及其位置点信息，找出binlog文件中增量的那部分。用mysqlbinlog命令将上述的binlog文件导出为sql文件。
1.查看全备之后新增的binlog文件：
2.恢复mysql-bin.000003文件的154行之后的信息：
mysqlbinlog --start-position=154 --database=sysecokit mysql-bin.000003 | mysql -uroot -p -v databasename
3.将其他binlog文件（除去mysql-bin.000003）导出sql文件（这一步可删除00Xbin.sql中之前误操作的语句）：
mysqlbinlog -d sysecokit mysql-bin.00000X > 00Xbin.sql
4.恢复全备数据和增量数据
mysql -uroot -p < XXX.sql
mysql -uroot -p databasename < 00Xbin.sql
注意区别：MySQL自身崩溃恢复是通过redo log和binlog实现的，单binlog无法恢复。上述是人为恢复。
3.物理备份与恢复
MyISAM物理备份
备份内容：每一个MyISAM存储引擎表都会有三个文件存在，分别为记录表结构元数据的“.frm”文件、存储表数据的“.MYD”文件，以及 存储索引数据的“.MYI”文件，只须备份上面的三种文件。
备份工具：MySQL提供了一个实用程序mysqlhotcopy，这个程序就是专门用来热备份MyISAM存储引擎的。（冷备份直接复制文件）
恢复：冷备份直接拷贝文件；热备份借助mysqlhotcopy
InnoDB物理备份
备份内容：InnoDB存放数据位置的配置为“innodb_data_home_dir”、“innodb_data_file_path”和“innodb_log_group_home_dir”这三个 目录位置指定的参数，以及另外一个决定InnoDB的表空间存储方式的参数“innodb_file_per_table”。
备份工具：InnoDB存储引擎的开发者（Innobase公司）开发了一款名为ibbackup的商业备份软件，实现InnoDB数据的在线物理备 份。（冷备份直接复制文件）
恢复：冷备份直接拷贝文件；热备份借助ibbackup
4.实战技巧
误删操作，如何恢复
误删行：可以用 Flashback 工具通过闪回把数据恢复回来。注意不要在主库操作，先在备库或从库操作完确认后，恢复回主库。
误删库 / 表：类似上面利用全量备份+binlog方式恢复（除去误删的语句）
九、SQL问题排查
1.show profile与show profiles
show profile（show profile for query query_id）
显示sql执行过程中各个环节的消耗情况，例如cpu使用情况，打开表、检查权限、执行优化器、返回数据等分别用了多长时间，可以分析语句执行慢的瓶颈在哪。
show profiles
显示最近发给服务器的多条语句（默认是15条）的执行时间
2.explain
explain select * from emp where name = 'Jefabc';
概要描述：
id:选择标识符
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明（Using index表示使用了覆盖索引；Using filesort表示使用了排序）
3.show status
查看MySQL服务器的状态信息
4.show full processlist
1.查看当前 mysql 是否有压力，都在跑什么语句，当前语句耗时多久了，有没有什么慢 SQL 正在执行之类的;
2.可以看到总共有多少链接数，哪些线程有问题(time是执行秒数，时间长的就应该多注意了)，然后可以把有问题的线程 kill 掉，这样可以临时解决一些突发性的问题。
5.慢查询日志
slow_query_log 慢查询开启状态
slow_query_log_file 慢查询日志存放的位置
long_query_time 查询超过多少秒才记录
运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数。当然，如果不是调优需要的话，一般不建议启动该参数。
3356
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
