MySQL提升课程 全面讲解MySQL架构设计
select语句 如果从文件中获取我们要查找的数据，这个具体的实现方式则有下面的存储引擎实现
myisam存储引擎
一些系统表和临时排序生成的表还是myisam存储引擎
myisam存储引擎实现的表的存储是有MYD文件和MYI文件 MYD存储的是数据 MYI存储的是索引 还有个frm文件是标识表的数据结构信息
myisam存储引擎所级别是表锁
myisam存储引擎 支持全文索引
myisam存储引擎 支持表数据压缩
myisam存储引擎 不支持事务
=====================innodb系统表空间========================================
1、Innodb使用表空间进行数据存储，根据配置参数innod_file_per_table
的值，如果是ON，则存储在独立表空间--tablename.idb,如果是OFF，则存储
在系统表空间--ibdataX(默认是打开的)--> show variables like 'innodb_file_per_table;'
2、系统表空间和独立表空间要如何选择：
系统表空间无法简单的收缩文件大小
独立表空间可以通过optimize table命令收缩文件系统
系统表空间会产生IO瓶颈
独立表空间可以通过向多个文件刷新数据
3、如果把原来存在系统表空间中的表转移到独立表空间：
使用mysqldump导出所有数据库数据
停止mysql服务，修改参数，并删除innodn相关文件
重启mysql服务，重建innodb系统表空间
重新导入数据
=====================innodb系统表空间========================================
=====================innodb特性========================================
1、Innodb是一种事务性存储引擎
2、完全支持事务的ACID特性
3、实现事务特性的原理：
使用Redo Log和Undo Log，Undo Log用于帮助未提交事务进行回滚，Redo Log记录
已经提交的事务，Undo Log会随机读写，而Redo Log基本是顺序
4、Innodb支持的是行级锁，在进行写操作时需要的资源更少，支持的并发更多
5、行级锁是由存储引擎层实现的
6、锁：
锁的主要租用是管理共享资源的并发访问
锁用于实现事务的隔离性
共享锁（读锁）
独占锁（写锁）
锁的粒度：根据粒度分为表级锁，行级锁，粒度越大，并发就越小
7、阻塞和死锁：
阻塞：一个事务中的锁需要等待另一个事务中的锁释放，形成的是阻塞
死锁：两个或两个以上的事务在执行中相互占用了对方的资源
8、Innodb状态检查：
show engine innodb status;
1：并发量：同一时间处理请求数量，同一时间处理请求数量和连接数量是两个概念，连接数大于处理请求数量，
MySQL参数最大连接数max_connections
这是是查询数据库当前设置的最大连接数 mysql> show variables like '%max_connections%';
2：最好不要在大型活动时候在主库上进行数据库备份会占用很大磁盘io
3:影响数据库性能的因素：服务器硬件，磁盘io,sql查询速度，网卡流量
- 网卡流量控制策越
- 减少从服务器数量
- 多级缓存
- 避免select *
4：老版本不支持多cpu对同一sql的并发处理
5：QPS:每秒钟处理的请求量
6：大表问题：单表超过千万行，表数据文件超过10g,
会导致慢查询很难在一定的时间内查询出所需要的数据，导致大量的磁盘io
建立索引需要更长的时间，5.5版本之前会引发锁表5.5版本之后随便不会锁表，但是会引起主从延迟
修改表结构需要长时间锁表引起主从延迟
7:可以通过分表来解决大表问题，但是分表也面临着两个难点（1）分表主键的选择（2）分表后的跨表查询
8：可以通过数据归档来解决大表问题，但是同样面临着两个问题（1）数据归档时间点的选择（2）以及如果进行归档操作
9：事务：原子性，一致性，隔离性，永久性
10：大事务会导致锁定太多的数据造成大量的阻塞和超时，回滚需要更长的时间，执行时间长，容易造成主从延迟
11:解决大事务有两点（1）避免一次性处理很多数据（2）在事务中移除不必要的语句如select语句
12:数据库类型的选择对数据库的性能影响很大
1 . 数据类型会影响存储空间的开销
2 . 数据类型会影响数据查询性能
所以当一个数据类型可以有多种选择多种类型的时候，应该优先考虑数字类型，其次是日期或二进制类型，最后应该是字符类型。对于相同级别的数据类型，应该优先选择占用空间小的数据类型。
原理：在对数据进行比较（查询条件，JOIN条件及排序）操作时：同样的数据，字符处理往往比数字处理慢，而且在数据库中，数据的处理是以页为单位，列的长度越小，数据类型占用的空间越小，利于性能的提升。
13:int整型
年龄：tinyint(1) 0-255之间
乌龟年龄： smallint(2)
mediumint(3)
int(4)
bigint(8)
14:int(11)代表什么意思
11代表的并不是长度，而是字符的显示宽度，在字段类型为int时，无论你显示宽度设置为多少，int类型能存储的最大值和最小值永远都是固定的
所以无论怎么设置int类型的显示宽度，int所能存储的最大值和最小值是固定的，那么这个显示宽度到底有什么用呢？
当int字段类型设置为无符号且填充零（UNSIGNED ZEROFILL）时，当数值位数未达到设置的显示宽度时，会在数值前面补充零直到满足设定的显示宽度，为什么会有无符号的限制呢，是因为ZEROFILL属性会隐式地将数值转为无符号型，因此不能存储负的数值。
15:如何选择正确的实数类型
列类型 存储空间 是否精确
FLOAT 4个字节 否
DOUBLE 8个字节 否
DECIMAL（与金额有关的） 每4个字节存9个数字，小数点占一个字节 否
DECIMAL(18,9)：表示该字段可存储数字最大的长度是18位，其中小数位最大的长度是9位。由于DECIMAL每4个字节存9个数字，小数点占一个字节，所以该字段占9个字节
---------------------
16：如何选择VARCHAR和CHAR类型
VARCHAR和CHAR类型长度是以字符为定位的而不是字节，如UTF-8编码，每个字符占3个字节，那么CHAR(10)就需要占30个字节
长度：65535字节限制
存储汉字，例如字符集utf8的(每个汉字占据3个字节)，最多可以存储65535/3-2字节
VARCHAR类型的存储特点
- VARCHAR用于存储变长字符串，只占用必要的存储空间。
- 当列的最大长度小于255时，只占用一个额外字节用于记录字符串长度。
- 当列的最大长度大于255，则需要占用两个额外字节用于记录字符串长度。
VARCHAR长度选择问题
- 使用最小的符合需求的长度。
- 系统上线后尽量不要修改VARCHAR的长度，因为在mysql 5.7之前，只要一修改就会发生锁表。
- VARCHAR(5)和VARCHAR(200)存储'AAAAA'这一个字符串时都是使用一个额外的字节来记录字符串的长度，那么他们的性能有什么不同呢？
- VARCHAR(5)优于VARCHAR(200)。因为MySQL为了能够更有效的优化查询，对于VARCHAR字段使用的是其最大的宽度来分配内存，所以如果我们把宽度定的太长就会消耗更多的内存。
VARCHAR类型的适用场景
- 字符串列的最大长度比平均长度大很多
- 字符串列很少被更新
- 使用了多字节字符集存储字符串（如utf8中的中文和英文）
CHAR类型的存储特点
- CHAR类型是定长的
- 字符串存储在CHAR类型的列中会删除末尾的空格（VARCHAR则不会）
- CHAR类型的最大宽度是255个字符，所以如果当我们长度超过255时，则需要使用VARCHAR类型进行存储
CHAR类型的适用场景
- 存储长度近似的值（md5值，如密码）
- 存储短字符串
- 存储经常被更新的字符串列，mysql在运行中会一次性分配足够的内存,多频繁的更新不会造成列分裂,避免超出存储碎片
17：如何选择日期类型
DATATIME类型
DATATIME = yyyy-MM-dd HH:mm:ss
以YYYY-MM-DD HH:MM:SS[.fraction]格式存储日期时间,能支持微秒但是默认还是支持秒
如果我们要支持微秒要定义个长度
datetime(6)=YYYY-MM-DD HH:MM:SS.fraction
与时区无关，占8个字节的存储空间
时间范围：1000-1-1 00:00:00至9999-12-31 23:59:59
TIMESTAMP类型
存储了由格林尼治时间1970-1-1 00:00:00到当前时间的秒数，
以yyyy-MM-dd HH:mm:ss[.fraction]的格式显示，占用4个字节
时间范围：1970-1-1 到 2038-01-19
显示依赖于所指定的时区
在行的数据修改时可以自动修改TIMESTAMP列的值（设置为NOT NULL即可），
默认是第一个为TIMESTAMP类型的列才会自动更新
DATE类型（如生日）
占用3个字节
使用DATE类型可以利用日期函数进行日期之间的计算
范围：1000-01-01到9999-12-31
TIME类型
HH:MM:SS
注意事项：
不要使用字符串类型存储日期时间数据
日期时间类型通常比字符串占用空间小
日期类型在进行查找过滤时可以利用日期来进行对比
可以利用日期函数进行日期之间的计算
使用Int存储日期时间不如使用TIMESTAMP类型
因为TIMESTAMP类型底层其实也是使用INT来存储的，只不过显示的时候格式化了而已，但更方便查看
18：innodb的主键
innodb是基于聚簇索引建立的. 聚簇索引对主键查询有很高的性能. 但它的二级索引(非主键索引)中必须包含主键列, 所以如果主键列很大的话, 其他的所有索引都会很大. 因此, innodb的主键应该尽可能小
这也是为什么InnoDB为什么最好选择自增int型作为主键的原因之一;
另一个原因是使用递增型作为主键, 存储引擎在插入数据时, 不会出现裂页插入的现象, 因为是append添加数据，
同时递增型作为主键可以顺序插入,避免随机io, 增加插入效率不递增,每次插入都会重新排序
19：利用二进制日志增量复制不需要太多的宽带，但是大批量基于行的复制会占用很大宽带,特别是在跨idc的环境下
20：MySQL日志分为
MySQL服务层日志
二进制日志、慢查询日志、通用日志。
MySQL存储引擎层日志
如innodb的重做日志和回滚日志
21：二进制日志：记录了所有对MySQL数据库的修改事件，包括在删改查和对表结构的修改事件。
记载的都是成功执行了的，但对于回滚的语句是不会记载在binlog日志中的。
查看binlog日志格式：
show variables like "binlog_format";
set global binlog_format='MIXED';
set session binlog_format='MIXED';二进制日志类型分为
基于段的格式 binlog_format = STATEMENT （记录sql语句）
binlog_format=statement
优点：日志记录量相对较小，节约磁盘及网络i/o,只对一条记录修改或插入
缺点：必须要记录上下文信息（保证语句在从服务器和主服务器上执行结果一样），
对于特定的函数如uuid(),user()这样的非确定函数还是无法复制，可能造成mysql复制的主备服务器数据不一致
基于行的格式 binlog_format = ROW 推荐
同一sql语句修改10000条数据的情况下，基于段的日志格式只会记录这个sql语句，
基于行的日志格式会有10000条记录分别记录每一行的数据修改
优点：使mysql主从复制更加安全，对每一行数据的修改比基于段的复制高效，误操作而修改了数据库中的数据，
同时又没有备份可以恢复时，我们就可以通过分析二进制日志，对日志记录的数据修改操作做反向处理的方式来达到
恢复数据的目的
缺点：记录日志量较大
版本介绍5.6.2里新增函数binlog_row_image。是动态参数，使用级别session和global。
可选值
full：
默认值，记录所有的行信息，和5.6之前记录的没有区别
minimal:
只记录要修改列的记录
noblob:
记录除了blog和text之外的所有字段
需要注意的是，只有在row格式下，上面的参数才会支持，就是说上面的参数是基于binlog_format的格式的。
binlog_format =Mixed
从 5.1.8 版本开始，MySQL 提供了除 Statement 和 Row 之外的第三种复制模式：Mixed，
实际上就是前两种模式的结合。
在 Mixed 模式下，MySQL 会根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式，
也就是在 statement 和 row 之间选择一种。
22：刷新log日志，自此刻开始产生一个新编号的binlog日志文件
mysql> flush logs;
注：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志；
获取binlog文件列表 mysql> show binary logs;
通过mysqlbinlog命令可以查看binlog的内容
mysqlbinlog /home/mysql/binlog/binlog.000003 |
23：MySQL二进制日志格式对复制的影响
复制的分类
基于SQL语句的复制 - SBR
主库二进制日志格式使用STATEMENT
在MySQL 5.1之前仅存在SBR模式, 又称之为逻辑复制.
主库记录CUD操作的SQL语句, 从库会读取并重放.
优点
生成的日志量少, 节约网络传输IO
当主从的列的顺序不一致时, SBR依然可以正常工作.
如对大表进行结构修改时, 可以先修改从库, 然后再进行主从切换.
缺点
对不确定性函数无法保证主从数据的一致
对于procedure, trigger, function有可能在主从上表现不一致(SBR BUG)
主库上要锁定多少行, 从库上也需要所以多少行, 所以相对于ROW复制时从库上需要更多的行锁
基于行的复制 - RBR
主库二进制日志格式使用ROW
优点
对不确定性函数友好, 如UUID()
减少从库上数据库锁的使用
insert into t_order_cnt(timestr, total, amount)
select date(order_date), count(1), sum(amout)
from t_order group by date(order_date);
1
2
3
1
2
3
上面的SQL在主库执行时会对t_order进行锁表操作, 对于STATEMENT的复制从库上也会对同样的表进行锁定,
但是基于ROW的复制仅需增加t_order对应的行的数据即可.
缺点
要求主从数据库的表的结构一致, 否则可能会中断复制
无法在从库上激活trigger
24：Mysql主从复制的实现原理图大致如下(来源网络)：
MySQL之间数据复制的基础是二进制日志文件（binary log file）。一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中，然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。
实现MySQL主从复制需要进行的配置：
-
- 主服务器：
- 开启二进制日志
- 配置唯一的server-id
- 获得master二进制日志文件名及位置
- 创建一个用于slave和master通信的用户账号
- 从服务器：
- 配置唯一的server-id
- 主服务器：
-
- 使用master分配的用户账号读取master二进制日志
- 启用slave服务
具体实现过程如下：
一、准备工作：
1.主从数据库版本最好一致
2.主从数据库内数据保持一致
主数据库：182.92.172.80 /linux
从数据库：123.57.44.85 /linux
二、主数据库master修改：
1.修改mysql配置
找到主数据库的配置文件my.cnf(或者my.ini)，我的在/etc/mysql/my.cnf,在[mysqld]部分插入如下两行：
[mysqld] bin_log =mysql-bin #开启二进制日志 server-id=1 #设置server-id
2.重启mysql，创建用于同步的用户账号
打开mysql会话shell>mysql -hlocalhost -uname -ppassword
创建用户并授权：用户：rel1密码：slavepass
mysql> CREATE USER 'repl'@'123.57.44.85' IDENTIFIED BY 'slavepass';#创建用户 mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'123.57.44.85';#分配权限 mysql>flush privileges; #刷新权限
3.查看master状态，记录二进制文件名(mysql-bin.000003)和位置(73)：
mysql > SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 73 | test | manual,mysql | +------------------+----------+--------------+------------------+
初始化从服务器数据
对数据库进行备份
mysqldump --single-transaction --master-data=2 --triggers --routines --all-databases -uroot -p >all.sql
将备份拷贝到从库上
scp all.sql root@192.168.3.101:/tmp
在从库上恢复数据
mysql -uroot -p <all.sql
在从库上查看all.sql的内容
more all.sql 并记录change master的内容
二、从服务器slave修改：
1.修改mysql配置
同样找到my.cnf配置文件，添加server-id
[mysqld] server-id=2 #设置server-id，必须唯一
bin_log = mysql-bin
relay_log = mysql-relay-bin
log_slave_update = on (可选)
read_only = on
2.重启mysql，打开mysql会话，执行同步SQL语句(需要主服务器主机名，登陆凭据，二进制文件的名称和位置)：
mysql> CHANGE MASTER TO
-> MASTER_HOST='182.92.172.80',
-> MASTER_USER='rep1',
-> MASTER_PASSWORD='slavepass',
-> MASTER_LOG_FILE='mysql-bin.000003',
-> MASTER_LOG_POS=73;
3.启动slave同步进程：
mysql>start slave;
4.查看slave状态：
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 182.92.172.80
Master_User: rep1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000013
Read_Master_Log_Pos: 11662
Relay_Log_File: mysqld-relay-bin.000022
Relay_Log_Pos: 11765
Relay_Master_Log_File: mysql-bin.000013
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
...
当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了。接下来就可以进行一些验证了，比如在主master数据库的test数据库的一张表中插入一条数据，在slave的test库的相同数据表中查看是否有新增的数据即可验证主从复制功能是否有效，还可以关闭slave（mysql>stop slave;）,然后再修改master，看slave是否也相应修改（停止slave后，master的修改不会同步到slave），就可以完成主从复制功能的验证了。
还可以用到的其他相关参数：
master开启二进制日志后默认记录所有库所有表的操作，可以通过配置来指定只记录指定的数据库甚至指定的表的操作，具体在mysql配置文件的[mysqld]可添加修改如下选项：
# 不同步哪些数据库 binlog-ignore-db = mysql binlog-ignore-db = test binlog-ignore-db = information_schema # 只同步哪些数据库，除此之外，其他不同步 binlog-do-db = game
如之前查看master状态时就可以看到只记录了test库，忽略了manual和mysql库。
25:把中继日志的内容加入到slave的binlog中。也就是说slave的binlog会记录master同步的操作日志。
log-slave-updates=on
26: 基于日志点复制的优点：
1.MySQL最早支持的复制技术，BUG相对较少。
2.对sql查询没有什么限制。
3.故障处理比较容易。
基于日志点复制的缺点：
1.故障转移时重新获取master的日志点信息比较困难。基于日志点复制是从master的binlog的偏移量进行增量同步。如果指定错误会造成遗漏或者重复，造成主从不一致。
27:基于GTID复制
GTID是全局事务ID，其保证为每个在master上提交的事务在复制集群中可以生产一个唯一ID。GTID的生成策略是source_id(也就是server的uuid，在auto.conf文件里面可以看到):transaction_id(自增序列)。
[auto]
server-uuid=67ccaaf1-e4b4-11e7-a07f-c8d3ffc0c026
在基于GTID的复制中，slave会告诉master，slave已经执行事务的 GTID，master也会告诉slave，slave未执行事务的GTID。同一个事务只会在指定的从库执行一次。
基于GTID复制的优点是：
1.可以很方便的进行故障转移，记录master最后事务的GTID值。比如master：A，slave：B,C。当A挂了后，B执行了所有A传过来的事务。当C连接到B后，在自己的binlog找到最后一次A传过来的GTID。然后C将这个GTID发送给B，B获取到这个GTID，就开始从这个GTID的下一个GTID开始发送事务给C。这种自我寻找复制位置的模式减少事务丢失的可能性以及故障恢复的时间。
2.slave不会丢失master的任何修改(开启了log_slave_updates)
基于GTID复制的缺点：
1.不支持非事务引擎。
2.故障处理比较复杂，需要注入空事务。
3.不支持sql_slave_skip_counter(一般用这个来跳过基于binlog主从复制出现的问题。)
4.对执行的sql有一定的限制。
5.为了保证事务的安全性，create table ... select无法使用。不能使用create temporary table创建临时表。不能使用关联更新事务表和非事务表。
28:基于GTID主从复制的步骤：
2.slave通过IO Thread将binlog中变更的数据，写入到slave的relay log中(中继日志)。
3.slave通过sql Thread读取relay log中的GTID，然后对比slave的binlog是否有此记录。
4.如果slave的binlog存在该GTID的记录，那么不好意思，忽略掉了。
5.如果slave的binlog不存在该GTID的记录，那么就执行该GTID事务，并一同记录到slave的binlog中。
主服务器配置
[mysqld] bin_log =mysql-bin #开启二进制日志 server-id=1 #设置server-id
gtid_mode=on
enforce-gtid-consistency=on
log-slave-updates=on
从服务器配置
[mysqld] bin_log =mysql-bin #开启二进制日志 server-id=1 #设置server-id
gtid_mode=on
enforce-gtid-consistency=on
log-slave-updates=on
change master to master_host='127.0.0.1', master_port=3306, master_user='rpl', master_password='123', MASTER_AUTO_POSITION=1;其他和日志点复制一样
影响主从延迟的因素
- 主库执行事务到二进制日志的时间
- 控制主库的事务大小，分割大事务
- 二进制日志传输的时间
- 使用MIXED日志或设置set binlog_row_image=minimal;
- 默认情况下从库只有一个SQL线程，主上并发的修改在从上变成了串行
- 使用多线程复制5.6版本开始
-
在Mysql5.7中可以按照逻辑时钟的方式来分配SQL线程
- 使用多线程复制5.6版本开始
29:分库分表
把一个数据库拆分成多个数据库
把多个数据库分配在不同的数据库实例节点上
把同一个数据库中的某一个表分配在不同的数据库实例上名字一样构相同的表
把同一个数据库中的某一个表分配成多个不同名字的表结构相同的表（水平拆分）
把同一个数据库中的某一个表根据业务不同分配成多个不同名字结构也不相同的表（垂直拆分）
30:数据库分片前的准备
如何选择分区键
分区键要能尽量避免跨分区查询的发生。例如一个博客表，如果采用每一篇博客的主键ID来进行分区，查询某个用户的博客时则要跨多个分区去汇总数据，性能会比分区前更慢。所以可以考虑使用用户ID来进行分区，则只需要在一个分片中进行查询
分区键要能尽量使各个分片中的数据平均。分片就是为了减轻数据库的负载，如果分片后许多查询和更新都集中在一个分片上那分片就意义不大了
如何存储无需分片的表
每个分片中存储一份相同的数据。这种方法适合数据量小的表，可以提升关联查询的效率。
使用额外的节点统一存储。
如何在节点上部署分片
每个分片使用单一数据库，并且数据库名也相同
将多个分片表存储在一个数据库中，并在表名上加入分片号后缀
在一个节点中部署多个数据库，每个数据库包含一个分片
如何分配分片中的数据
按分区键的Hash值取模来分配分片数据
按分区键的范围来分配分区数据
利用分区键和分片的映射表来分配分片数据
如何生成全局唯一ID
使用auto_increment_increment和auto_increment_offset参数
使用全局节点来生成ID
在Redis等缓存服务器中创建全局ID
31：如何获取有性能问题的SQL
1.通过用户反馈获取存在性能问题的SQL.
2.通过慢查日志获取存在性能的SQL.
启动慢查日志
slow_query_log=on
set global slow_query_log=on;
slow_query_log_file 指定慢查日志存储路径及文件
默认情况保存在mysql的数据目录中，最好日志存储和数据存储分开。
long_query_time
指定记录慢查日志SQL执行时间的阀值，单位为秒，默认值为10秒。精确到微秒,
如果为一毫秒这个值为 0.001 。
记录的语句包括
1.查询语句
2.数据修改语句
3.已经回滚的SQL
log_queries_not_using_indexes 是否记录未使用索引的SQL
慢查日志中记录的内容：
第一行记录了：
用户信息，线程ID号 用户信息 sbtest ，线程ID为 17
第二行 ：记录了查询时间
第三行 ：锁的时间
第四行 ： 返回的记录行数
第五行： 扫描的行数
第六行 ： 执行的时间
第七行 ： 执行的语句
常用慢查询日志分析工具
1.mysqldumpslow
汇总除查询条件外其他完全相同的SQL,并将分析结果按照参数中指定的顺序输出。
mysqldumpslow –s r –t 10 slow.log
-s order (c,t,l,r,at,al,ar)
c: 总的次数
t:总的时间
l：锁的时间
r: 总数据行
at,al,ar: t,l,r 的平均数
at 总时间 /总次数
-t top 指定取前几条作为结果输出
2.pt-query-digest
pt-query-digest –explain -h=127.0.0.1,u=root,p=root slow.log>slow.report
可以包括执行计划。
3.实时获取性能的问题的SQL
select id,user,host,db,command ,time,state,info from information_schema.processlist where time>60;
查询服务器中查询时间超过60秒的SQL.
31：查询速度为什么会慢
SQL请求处理步骤
客户端发送SQL请求给MySQL服务器
MySQL服务器会在查询缓存中进行检查，查看是否可以在查询缓存中命中
服务端会对SQL进行解析、预处理再由优化器生成对应的执行计划
根据执行计划，调用存储引擎中的API来查询数据
将查询的数据返回给客户端
32：查询缓存
如果查询缓存开关是打开的会优先对缓存中检查：
这个检查是对大小写敏感的hash查找实现：so，只能进行全值匹配查找。
如果在缓存中命中查询结果，会进行角色的权限认证，然后跳过后面的步骤把数据返回给客户端。
1. 查询的SQL要和缓存中的完全一致，所以命中并不容易。
2. 如果缓存中的数据是正确的，需要每次修改表的时候进行缓存的维护。
3. 而且进行缓存中查找的同时会对表加锁，所以对读写频繁的应用，查询缓存很可能降低查询效率（不建议开启查询缓存）
查询缓存相关参数
query_cache_type：
0代表关闭查询缓存OFF，1代表开启ON，2（DEMAND）代表当sql语句中有SQL_CACHE关键词时才缓存。
例：select SQL_CACHE user_name from users where user_id = ‘100’;
query_cache_size：
表示查询缓存大小，也就是分配内存大小给查询缓存，分配大小为1024整数倍；设置为0表示不缓存
query_cache_limit ：
控制缓存查询结果的最大值
MySql 可以设置一个最大的缓存值，当你查询缓存数结果数据超过这个值就不会
进行缓存。缺省为1M，也就是超过了1M查询结果就不会缓存。
在 query_cache_type 打开的情况下，如果你不想使用缓存，需要使用sql_no_cache关键字
例：select sql_no_cache id,name from tableName;
query_cache_wlock_invalidate：
如果一个表被加锁是否允许直接从缓存中读取结果，默认为FALSE。
query_cache_min_res_unit：
查询缓存中存放的最小内存大小，默认4k；
SQL转变为执行计划
1:解析SQL
语法解析阶段是通过关键字对mysql进行语法解析并生成一颗对应的解析树
此阶段，使用MySQL语法规则验证和解析查询：检查语法是否使用的正确的关键字和关键字的位置是否正确
2:预处理
预处理阶段进一步的验证检查解析树是否合法
此阶段检查查询中所涉及的表和数据列是否存在及名字或者别名是否有存在歧义
3:优化SQL执行计划
查询优化器生成查询计划
影响查询计划生成的因素
- 统计信息不准确
- 执行计划中的成本估算并不等于实际的执行计划成本
- mysql服务器层并不知道哪些页面在内存中，哪些页面在磁盘中，哪些页面顺序读，哪些页面要随机读
- mysql所认为的最优可能和我们所认为的最优并不一致
- mysql基于其成本模型选择最优的执行计划
- mysql不会考虑到其他的并发查询
- 有时候也会基于一些固定的规则来生成执行计划
- mysql不会考虑不受其控制的成本，如：存储过程和用户自定义的函数
MySQL可以优化的SQL类型
- mysql会重新定义标的关联顺序
- 将外链接转换成内连接
- 使用等价变换原则
- 可以利用索引对count(),min(),max()进行优化。如最小值 btree索引第一个数据
- 将表达式转化为一个常数
- 子查询优化：把子查询转换成关系查询
- 会提前终止查询：如发现不成立的条件，如属性设置为正数，查询条件是负数
- 对in()进行优化，会对in中的数据进行排序，然后进行二分查找
如何确定查询各个阶段所消耗的时间
profile
Profiling是从 mysql5.0.3版本以后才开放的。
启动profile之后，所有查询包括错误的语句都会记录在内。
profile是一个session级别的配置，关闭会话或者set profiling=0 就关闭了。（如果将profiling_history_size参数设置为0，同样具有关闭MySQL的profiling效果。）
show profiles
show profile for query N 查询每个阶段所消耗的时间
5.5之后使用performance_schma
---------------------
32:特定sql语句的优化
对大表的数据更新和删除进行批量操作同时连续操作需要中间暂停间隔
如何修改大表的数据结构
对大表的字段和字段的宽度进行变更时候都会锁表，同时无法解决数据库延迟的问题
可以通过以下操作来完成大表的表结构变更
新建一张同样的表结构
对老表进行数据同步
对老表进行存储过程和触发器的同步
对新表重命名
以上三个步骤可以通过一个工具完成
pt-online-schema-change
--alter="modify c varchar(150) not null default''"
--user=root --password=PassWord D=testDataBaseName,t=tesTableName
--charset=utf-8 --execute
如何优化not in和<>查询
#原始的SQL语句
SELECT
customer_id,
first_name,
last_name,
FROM
customer
WHERE
customer_id NOT IN (
SELECT
customer_id
FROM
payment
)
#优化后的SQL语句
SELECT
a.customer_id,
a,
first_name,
a.last_name,
a.email
FROM
customer a
LEFT JOIN payment b ON a.customer_id = b.customer_id
WHERE
b.customer_id IS NULL
使用汇总表的方法进行优化
#统计商品的评论数（若有上亿条记录，执行起来非常慢进行全表扫描）[优化前的SQL]
select count(*) from product_comment where product_id=999;
#汇总表就是提前以要统计的数据进行汇总并记录到数据库中以备后续的查询使用
create table product_comment_cnt(product_id int,cnt int);
#统计商品的评论数[优化后的SQL]
#查询出每个商品截止到前一天的累计评论数+当天的评论数
select sum(cnt) from(
select cnt from product_comment_cnt where product_id=999
union all
select count(*) from product_comment where product_id=999
and timestr>DATE(NOW())
) a
33：B-tree索引
B-tree索引能够加快数据的查询速度（MySQL使用B+Tree）
因为存储引擎不再需要进行全表扫描来获取数据，数据分布在各个节点之中。
B+Tree 索引更适合进行范围查找
是B-Tree的改进版本，同时也是数据库索引索引所采用的存储结构。数据都在叶子节点上，
并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说，
进行范围查找时只需要查找两个节点，进行遍历即可。而B-Tree需要获取所有节点，
相比之下B+Tree效率更高。
什么情况下可以使用B-tree索引
全值匹配的查询
order_sn = “987654321”
匹配最左前缀的查询
匹配列前缀查询
order_sn like “9876”
匹配范围值的查询
order_sn > ‘987654321’ and order_sn < ‘98765444111’
精确匹配左前列并范围匹配另外一列
只访问索引的查询
B-tree索引的使用限制
如果不是按照索引最左列开始查找，则无法使用索引
使用索引是不能跳过索引中的列（三个组合索引的情况）
Not in 和 <> 操作无法使用索引
如果查询中有某个列的范围查询，则其右边所有列都无法使用索引
34:hash索引
基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，
存储引擎都会对所有的索引列的值计算一个哈希码，哈希码是一个较小的值，
并且不同键值的行计算出来的哈希码不一样，哈希索引将所有的哈希码存储在索引中，
同时在哈希表中保存指向每个数据行的指针。
mysql> select * from testhash;
+-------+-----------+
| fname | lname |
+-------+-----------+
| Arjen | Lentz |
| Baron | Schwartz |
| Peter | Zaitsev |
| Vadim | Tkachenko |
+-------+-----------+
4 rows in set (0.00 sec)
假设索引使用假想的哈希函数f(),它返回下面的值：
f('Arjen')=2323
f('Baron')=7437
f('Peter')=8784
f('Vadim')=2458
则哈希索引的数据结构如下：
槽： 值：
2323 指向第1行的指针
2458 指向第4行的指针
7437 指向第2行的指针
8784 指向第3行的指针
哈希索引自身只需要存储对应的哈希值,所以索引的结构十分紧凑,这让哈希索引查找的速度非常快
哈希索引限制
A：hash索引必须进行二次查找,哈希索引只包含哈希值和行指针，而不存储字段值，
所以不能使用索引中的值来避免读取行（即不能使用哈希索引来做覆盖索引扫描），不过，访问内存中的行的速度很快
（因为memory引擎的数据都保存在内存里），所以大部分情况下这一点对性能的影响并不明显。
B：hash索引无法用于排序,哈希索引数据并不是按照索引列的值顺序存储的，所以也就无法用于排序
C：哈希索引也不支持部分索引列匹配查找,因为哈希索引始终是使用索引的全部列值内容来计算哈希值的.如:数据列(a,b)上建立哈希索引,如果只查询数据列a,则无法使用该索引
D：哈希索引只支持等值比较查询，如：=,in(),<=>(注意，<>和<=>是不同的操作)，不支持任何范围查询（必须给定具体的where条件值来计算hash值，所以不支持范围查询）
E：hash索引中hash码的计算可能存在hash冲突
35:为什么要使用索引
索引大大减少了存储引擎需要扫描的数据量
索引可以帮助我们进行排序以避免使用临时表
索引可以把随机I/o变成顺序I/o
36:索引是不是越多越好
- 索引会增加写操作的成本
- 太多的索引会增加查询优化器的选择时间
37:索引细节
索引优化策略
索引列上不能使用表达式或函数
前缀索引和索引列的选择性
索引的选择性是不重复的索引值和表的记录数的比值
联合索引如何使用索引列的顺序
经常会被使用到的列优先
选择性高的列优先
宽度小的列优先
38：覆盖索引
通常开发人员会根据查询的where条件来创建合适的索引，
但是优秀的索引设计应该考虑到整个查询。其实mysql可以使用索引来直接获取列的数据。
如果索引的叶子节点包含了要查询的数据，那么就不用回表查询了，
也就是说这种索引包含（亦称覆盖）
所有需要查询的字段的值，我们称这种索引为覆盖索引。
覆盖索引
优点：
1、可以优化缓存，减少磁盘IO操作
2、可以减少随机IO，使随机IO操作变为顺序IO操作
3、可以避免对Innodb主键索引的二次查询
4、可以避免MyISAM表进行系统调用
无法使用覆盖索引的情况
1、存储引擎不支持覆盖索引
2、查询中使用了太多的列
3、使用了双%号的like查询
覆盖索引
如果一个索引包含（或者覆盖）所有需要查询的字段的值，则称之为覆盖索引，优点如下：
- 索引条目通常远小于数据行大小，所以如果只需要读取索引，mysql就会极大的减少数据访问量
- 因为索引是按照列值顺序存储（至少在单个页内是如此），所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少的多
- 一些存储引擎如MyISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用
由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子结点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询
不是所有类型的索引都可以成为覆盖索引，覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以mysql只能使用B-Tree索引做覆盖索引。另外，不同的存储引擎实现覆盖索引的方式也不同，而且不是所有的引擎都支持覆盖索引。
当发起一个被索引覆盖的查询时，在EXPLAIN的Extra列可以看到Using index信息，示例如下，KEY last_name (first_name) USING BTREE：
mysql> EXPLAIN SELECT * FROM people WHERE first_name = 'm' AND last_name LIKE '%zha%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: people
type: ref
possible_keys: last_name
key: last_name
key_len: 152
ref: const
rows: 2
Extra: Using index condition; Using where
1 row in set (0.00 sec)
在first_name、last_name列上添加一个索引KEY last_name(first_name,last_name) USING BTREE，查询如下所示：
mysql> EXPLAIN SELECT * FROM people WHERE first_name = 'm' AND last_name LIKE '%zha%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: people
type: ref
possible_keys: last_name
key: last_name
key_len: 152
ref: const
rows: 2
Extra: Using index condition
1 row in set (0.00 sec)
在大多数存储引擎中，覆盖索引只能覆盖那些只访问索引中部分列的查询，不过可以更进一步优化InnoDB。InnoDB的二级索引的叶子结点都包含了主键的值，这意味着InnoDB的二级索引可以有效的利用这些“额外”的主键列来覆盖查询，示例如下，KEY last_name(last_name) USING BTREE：
mysql> EXPLAIN SELECT first_name,last_name FROM people WHERE last_name LIKE 'zha
%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: people
type: range
possible_keys: last_name
key: last_name
key_len: 152
ref: NULL
rows: 2
Extra: Using index condition
1 row in set (0.00 sec)
虽然上述示例中索引的列不包含first_name，但也能够用于对first_name做覆盖查询。
39：使用索引优化查询
使用索引扫描来优化排序,通过排序操作,按着索引顺序扫描数据
注意事项：
1、索引的列顺序和order by子句的顺序完全一致
2、索引中所有列的方向(升序，降序)和order by子句完全一样
3、order by中的字段全部在关联表中的第一张表中
利用索引优化🔐
1、索引可以减少锁定的行数
2、索引以加快处理速度，同事也加快了锁的释放
40：
使用索引扫描做排序
mysql有两种方式可以生成有序的结果：通过排序操作，或者按索引顺序扫描。如果EXPLAIN中的type列的值为index，则表明mysql使用了索引扫描来做排序。
扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录就都回表查询一次对应的行。这基本上都是随机I/O，因此按索引顺序读取数据的速度通常要比顺序的全表扫描慢，尤其是在I/O密集型的工作负载时。
mysql可以使用同一个索引既满足排序，又用于查找行，设计索引时应该尽可能的同时满足这两种任务。 只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时，mysql才能使用索引来对结果做排序。如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求，否则，mysql都需要执行排序操作，而无法利用索引排序。
有一种情况下ORDER BY子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候，示例如下，KEY last_name (first_name,last_name) USING BTREE：
mysql> EXPLAIN SELECT dob,address FROM people WHERE first_name = 'm' ORDER BY last_name\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: people
type: ref
possible_keys: last_name
key: last_name
key_len: 152
ref: const
rows: 2
Extra: Using index condition; Using where
1 row in set (0.00 sec)
即时ORDER BY子句不满足索引的最左前缀的要求，也可以用于查询排序，这是因为索引的第一列被指定为一个常数。
冗余和重复索引
mysql允许在相同列上创建多个索引。重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引，应该避免。冗余索引和重复索引有些不同，如果创建了索引(A, B)，再创建索引(A)就是冗余索引，因为这只是前一个索引的前缀索引。 mysql的唯一限制和主键限制都是通过索引实现。
大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新索引。但也有时候出于性能方面的考虑需要冗余索引，因为扩展已有的索引会导致其变的太大，从而影响其他使用该索引的查询的性能。
一般来说，增加新索引将会导致INSERT、UPDATE、DELETE等操作的速度变慢，特别是当新增索引后导致达到了内存瓶颈的时候。
如果判断索引是重复还是冗余，使用工具pt-duplicate-key-checker
关于该工具的使用见pt-duplicate-key-checker检查数据库的重复索引
2.查询未被使用的索引：通过sql语句来查询
3.更新索引统计信息及减少索引碎片
analyze table table_name
# 维护表的碎片
optimize table table_name #使用不当会导致锁表索引和锁
索引可以让查询锁定更少的行。如果查询从不访问那些不需要的行，那么就会锁定更少的行，从两个方面来看这对性能都有好处：
- 首先，虽然InnoDB的行锁效率很高，内存使用也很少，但是锁定行的时候仍然会带来额外开销
- 其次，锁定超过需要的行会增加锁争用并减少并发性
在mysql 5.1和更新的版本中，InnoDB可以在服务器端过滤掉行后就释放锁，但是在早起的mysql版本中，InnoDB只有在事务提交后才能释放锁。 InnoDB在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）。这消除了使用覆盖索引的可能性，并且使得SELECT ... FOR UPDATE比LOCK IN SHARE MODE或非锁定查询要慢的多
41 :mysql 拓扑复制
一主多从复制特点
配置简单
可以做读写分离
多太从库可以做读负载均衡
为不同的业务使用不同的从库
将一个备库放到远程数据中心，用作灾难恢复
42：mha高可用过程
流程如下：
- 从宕机崩溃的master保存二进制日志事件(binlog events)。
- 识别含有最新更新的slave。
- 应用差异的中继日志(relay log)到其它slave。
- 应用从master保存的二进制日志事件(binlog events)。
- 提升一个slave为新master并记录binlog file和position。
- 使其它的slave连接新的master进行复制。
- 完成切换manager主进程OFFLINE
应用层解决
优点：
1、多数据源切换方便，由程序自动完成；
2、不需要引入中间件；
3、理论上支持任何数据库；
缺点：
1、由程序员完成，运维参与不到；
2、不能做到动态增加数据源；
中间件解决(maxscale)
优点：
1、源程序不需要做任何改动就可以实现读写分离；
2、动态添加数据源不需要重启程序；
缺点：
1、程序依赖于中间件，会导致切换数据库变得困难；
2、由中间件做了中转代理，性能有所下降；
3：对延迟要求很高的无法自动切换为主库
44:
