1、事务
事务是MySQL等关系型数据库区别于NoSQL的重要方面，是保证数据一致性的重要手段。
原子性（A）
一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作。
一致性（C）
数据库总是从一个一致性状态转换到另一个一致状态。
隔离性（I）
通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。
持久性（D）
一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。（持久性的安全性与刷新日志级别也存在一定关系，不同的级别对应不同的数据安全级别。）
2、隔离级别
读未提交（Read UnCommit）
会出现脏读
读已提交（Read Commited）
也称为不可重复读，Oracle 默认为该级别
可重复读（Repetable Read）
会出现幻读，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁（悲观锁），其它事务无法修改这些数据，就可以实现可重复读了。但这种方法却无法锁住insert的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据，这就是幻读。
但是MySQL、ORACLE、PostgreSQL等成熟的数据库，出于性能考虑，都是使用了以乐观锁为理论基础的MVCC（多版本并发控制）来实现，通过间隙锁（next-key locking）策略防止幻读的出现
串行化（Serialazable）
读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。
3、存储引擎
InnoDB
InnoDB是基于多版本并发控制的存储引擎。
执行select count(*) from t 会一行行扫描，所以需要建立好索引
-
并发控制
-
锁
如何使用普通锁保证一致性？
普通锁，被使用最多：
(1)操作数据前，锁住，实施互斥，不允许其他的并发任务操作；
(2)操作完成后，释放锁，让其他任务执行；
如此这般，来保证一致性。
普通锁存在什么问题？
简单的锁住太过粗暴，连“读任务”也无法并行，任务执行过程本质上是串行的。
- 共享锁
- 排他锁
-
数据多版本
-
redo日志
数据库事务提交后，必须将更新后的数据刷到磁盘上，以保证ACID特性。磁盘随机写性能较低，如果每次都刷盘，会极大影响数据库的吞吐量。
优化方式是，将修改行为先写到redo日志里（此时变成了顺序写），再定期将数据刷到磁盘上，这样能极大提高性能。
画外音：这里的架构设计方法是，随机写优化为顺序写，思路更重要。
假如某一时刻，数据库崩溃，还没来得及刷盘的数据，在数据库重启后，会重做redo日志里的内容，以保证已提交事务对数据产生的影响都刷到磁盘上。
-
- 事务提交的数据先写到redo日志里（此时变成了顺序写），
再定期将数据刷到磁盘上，即随机写变为顺序写
- 事务提交的数据先写到redo日志里（此时变成了顺序写），
-
- 若数据库崩溃，还没来得及刷盘的数据，在数据库重启后，
会重做redo日志里的内容，以保证已提交事务对数据产生的影响都刷到磁盘上。
- 若数据库崩溃，还没来得及刷盘的数据，在数据库重启后，
-
-
undo日志
-
当事务回滚时，或者数据库奔溃时，可使用undo日志来恢复数据
- 对insert操作，undo日志记录新数据的PK(ROW_ID)，回滚时直接删除
- 对于delete/update操作，undo日志记录旧数据row，回滚时直接恢复；
-
-
回滚端 rollback segment
存储undo日志的地方，是回滚段。
undo日志和回滚段和InnoDB的MVCC密切相关
-
行数据的三个内部属性
- DB_TRX_ID，6字节，记录每一行最近一次修改它的事务ID
- DB_ROLL_PTR，7字节，记录指向回滚段undo日志的指针
- DB_ROW_ID，6字节，单调递增的行ID
-
-
InnoDB为何能够做到这么高的并发
回滚段里的数据，其实是历史数据的快照（snapshot），这些数据是不会被修改，select可以肆无忌惮的并发读取他们。
快照读（Snapshot Read），这种一致性不加锁的读（Consistent Nonlocking Read），就是InnoDB并发如此之高的核心原因之一。
这里的一致性是指，事务读取到的数据，要么是事务开始前就已经存在的数据（当然，是其他已提交事务产生的），要么是事务自身插入或者修改的数据。
-
-
锁类型
-
记录锁 Record Locks
-
间隙锁 Gap Locks
封锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。
-
插入意向锁
间隙锁的一种，它是专门针对insert操作的。
在MySQL，InnoDB，RR下：
t(id unique PK, name);
数据表中有数据：
10, shenjian
20, zhangsan
30, lisi
事务A先执行，在10与20两条记录中插入了一行，还未提交：
insert into t values(11, xxx);
事务B后执行，也在10与20两条记录中插入了一行：
insert into t values(12, ooo);
(1)会使用什么锁？
(2)事务B会不会被阻塞呢？
回答：虽然事务隔离级别是RR，虽然是同一个索引，虽然是同一个区间，但插入的记录并不冲突，故这里：
使用的是插入意向锁
并不会阻塞事务B
-
-
临键锁 Next-Key Locks
临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。
更具体的，临键锁会封锁索引记录本身，以及索引记录之前的区间。
-
自增锁 Auto-inc Locks
自增锁是一种特殊的表级别锁（table-level lock），专门针对事务插入AUTO_INCREMENT类型的列。最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。
-
共享/排它锁(Shared and Exclusive Locks)
-
意向锁(Intention Locks)
InnoDB支持多粒度锁(multiple granularity locking)，它允许行级锁与表级锁共存，实际应用中，InnoDB使用的是意向锁。
意向锁是指，未来的某个时刻，事务可能要加共享/排它锁了，先提前声明一个意向。
-
分类
- 意向排它锁(intention exclusive lock, IX)
- 意向共享锁(intention shared lock, IS)
-
意向锁协议(intention locking protocol)
- 事务要获得某些行的S锁，必须先获得表的IS锁
- 事务要获得某些行的X锁，必须先获得表的IX锁
-
-
插入意向锁
在MySQL，InnoDB，RR下：
t(id unique PK, name);
数据表中有数据：
10, shenjian
20, zhangsan
30, lisi
事务A先执行，在10与20两条记录中插入了一行，还未提交：
insert into t values(11, xxx);
事务B后执行，也在10与20两条记录中插入了一行：
insert into t values(12, ooo);
(1)会使用什么锁？
(2)事务B会不会被阻塞呢？
回答：虽然事务隔离级别是RR，虽然是同一个索引，虽然是同一个区间，但插入的记录并不冲突，故这里：
使用的是插入意向锁
并不会阻塞事务B
-
MyISAM
- 会直接存储总行数，所以select count(*) from t 很快返回，但若加where条件后两种存储引擎处理类似
-
不支持外键
不管哪种存储引擎，在数据量大并发量大的情况下，都不应该使用外键，而建议由应用程序保证完整性。
4、七种连接
5、索引
create index inde_name on table (column)
alter table add index index_name on column
show index from table
定义
索引是帮助数据库高效获取数据的数据结构
目的在于提高查询效率, 可类比字典
优势
- 提高数据检索效率, 降低数据库IO成本
- 通过索引列对数据进行排序, 降低数据排序成本, 降低CPU消耗
劣势
- 索引列也占用空间
- 索引提高了查询效率, 但同时也会降低更新表的速度,如 insert , update , delete. 数据库更新表格除了需要更新数据以外还需要更新索引信息
分类
单值索引
即一个索引只包含单个列，一个表可以有多个单列索引。例如一张表里有很多字段，对name建 一个索引，对number再建一个索引。
唯一索引
索引列的值必须唯一，单允许有空值。
复合索引
一个索引当中包含了两列或者多列。例如可以把一张表里的name和number结合在一起，创建一个复合索引。
索引结构
- B树索引
- Hash索引
- 全文索引
- R-Tree 索引
数据结构
-
B树
- 叶子节点，非叶子节点，都存储数据
- 中序遍历，可以获得所有节点
-
B+树
- 非叶子节点不再存储数据，数据只存储在同一层的叶子节点上
- 叶子之间，增加了链表，获取所有节点，不再需要中序遍历
性能分析
-
mysql 查询优化器
-
常见瓶颈
- CPU
- IO
-
explain
-
能做什么
- 表的读取顺序
- 数据读取操作的操作类型
- 可能用到哪些索引
- 实际用到哪些索引
- 表之间的引用
- 每张表有多少行被优化器查询
-
列名解析
-
id
select 查询的序列号，包含一组数字， 表示查询中执行select 子句或操作表的顺序
-
id相同
执行顺序从上到下
-
id 不同
如果是子查询，ID的序号会递增，ID值越大优先级越高，越先被执行
-
ID相同又不同
序号大的先执行，序号相同的顺序执行
-
-
select_type
查询的类型
-
Simple
简单的select查询，不包含子查询或者union查询
-
Primary
最外层查询称为主查询
-
SubQuery
在select或where列表中包含子查询
-
Derived
在from列表中包含的子查询被标记为derived(衍生)
mysql会递归这些子查询，将结果放到临时表中 -
Union
若第二个select出现在union之后，则被标记为union
若union包含在from子句的子查询中，外层select被标记为 DERIVED(衍生) -
Union result
从union表获取结果的select
-
-
table
-
type
性能从高到低：
system > const > equ_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all-
system
表只有一行记录，是const 类型特例
-
const
表示通过一次索引就能找到，const用于primary key或者unique索引
-
equ_ref
唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
-
ref
非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行
-
range
只检索给定范围的行，使用一个索引来选择行。key列显示使用列哪个索引，一般在where子句中出现 between、> 、<、 in等的查询。
这种范围扫描索引的扫描方式比全表扫描好，因为它只需要开始于索引的某一个点，而结束于某一个点，而不用扫描全部索引 -
index
全索引扫描
-
all
全表扫描
-
-
possible_keys
显示可能应用在这张表的索引，一个或多个。
查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被使用到 -
key
实际使用的索引。
若查询中使用列覆盖索引，则该索引仅出现在key列表中 -
key_len
索引中使用的字节数，可通过该列计算查询中使用的索引长度。在不损失精确性的情况下，长度越短越好。
key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是表定义计算而得，不是通过表内检索出
-
ref
显示索引的哪一列被使用，如果可能的话，是一个常数const
-
rows
根据表统计信息即索引选用情况，大致估算出找到记录所需要读取的行数
-
Extra
-
Using filesort
表示mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
mysql无法利用索引完成的排序操作称为文件排序。
出现该情况时表示查询性能已经很差 -
Using temporary
使用列临时表来保存中间结果，常见与排序 order by 及分组 group by 查询.
性能最差，需要优化 -
Using index
表示相应的查询使用到了覆盖索引（Convering index）,避免访问表的数据行，效率不错。
如果同时出现using where , 表明索引被用来执行索引键值的查找；
如果没有同时出现using where，表明索引用来读取数据而非查询动作 -
using where
-
using join buffer
-
impossible where
如 where name=‘jim’ and name =‘jack’
-
select table optimized away
-
distinct
-
-
-
哪些情况需要创建索引
-
主键自动创建唯一索引
-
频繁作为查询条件的字段应该创建索引
-
查询中与其他表关联的字段，外键关系创建索引
-
频繁更新的字段不合适建索引
-
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
如表t1中存在索引 index_col1_col2_col3
- select col1 from t1 where col1=‘ac’ order by col3;
该查询会使用到文件内排序，导致查询性能下降，因为排序字段与索引不匹配 - select col1 from t1 where col1=‘ac’ order by col2, col3;
该查询性能不错
- select col1 from t1 where col1=‘ac’ order by col3;
-
查询中统计或分组的字段
查询截取分析
-
查询优化
-
永远是小表驱动大表
类似嵌套循环
-
for(int i=5...){
for(int j = 10000...){
....
}
}
for(int i=10000...){
for(int j = 5...){
....
}
}
-
order by
尽量使用索引排序，避免使用文件内排序 file sort。order by 满足两种情况会使用到索引
-
order by 子句使用到索引最左前列
-
使用where子句与order by子句条件列组合满足索引最左前列
- 若排序的字段不在索引列上，
则mysql会启用两种filesort算法在sort_buffer中，单路比多路要占用更多的空间，因为单路是把所有字段都取出，故取出的数据总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据进行排序（创建临时表，多路合并），排序完后再取同样容量大小的数据再排序，如此循环…从而导致多次IO
-
双路排序
mysql4.1之前使用的是双路排序，即两次扫描磁盘，最终得到数据。
读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表读取 -
单路排序
从磁盘读取查询所需要的列，按照order by 列在buffer对他们进行排序，然后扫描排序后的列表对他们进行输出，它的效率更快一些，避免二次读取数据，并且把随机io变为顺序io , 但它会使用更多空间
-
-
-
优化策略
1. 不要使用select *， 这是大忌，它可能导致两种结果： 1.1 当查询的字段大小总和小于max_length_for_sort_data而且排序字段不是text|blob时，会用改进后的算法--单路排序，否则使用多路排序 1.2 两种算法的数据都可能超出sort_buffer的容量，超出后会创建临时表进行合并排序，导致多次io,但是单路算法风险更大，所以要增加 sort_buffer_size参数的值 2. 增大 sort_buffer_size 3. 尝试提高 max_length_for_sort_data 该参数会增加改进算法的概率 - 增大sort_buffer_size参数 - 增大 max_length_for_sort_data 参数-
group by
几乎与order by 原理一致
实质是先排序后分组
-
-
慢查询日志
查看是否已经开启：
show variables like '%slow_query_log%'；
慢查询记录总数：
show variables like '%slow_queries %'；
-
-
开启 set global slow_query_log=1;
只对本次有效，重启数据库后无效
- 阀值 long_query_time
show variables like '%long_query_time%';
// 设置慢查询时间阀值，需要重新登陆会话才可以看到结果
set global long_query_time=3;
-
-
日志分析工具 mysqldumpslow
-
相关参数
- s 按照何种方式排序 - c 访问次数 - l 锁定时间 - r 返回记录 - t 查询时间 - al 平均锁定时间 - ar 平均返回记录数 - at 平均查询时间 - g 后面搭配一个正则表达式 -
show profile
show profile 相关参数：
type：
all ：显示所有信息
block io ： io相关开销
context switches ： 上下文切换相关开销
cpu ： CPU
IPC ： 显示发送和接收相关开销
memory： 内存开销
page faults： 页面错误相关开销
source ：显示和source_function, source_file, source_line 相关开销
swaps ： 显示交换次数相关开销
-
分析步骤
-
- 查看数据库是否支持
show variables like ‘%profiling%’；
set profiling = on;
-
- 运行 sql
-
- show profiles; 查看执行的语句
-
- 诊断 sql , show profile cpu , block io for query N
show profile 相关参数：
type：
all 显示所有信息
block io , io相关开销
context switches 上下文切换相关开销
cpu
IPC 显示发送和接收相关开销
memory 内存开销
page faults 页面错误相关开销
source 显示和source_function, source_file, source_line 相关开销swaps 显示交换次数相关开销
-
-
需要注意的结论
- converting heap to myisam
表示查询结果太大，内存不够用改用磁盘 - creating tmp table
- copying to tmp table on disk
把内存中的临时表复制到磁盘 - locked
- converting heap to myisam
-
6、优化案例
获取大数据表的总行数
当数据达到百万及千万以上时，使用select count(1)会非常的慢（全表扫描），故为来获取数据表的总记录数，可通过以下三种方式获取
-
使用计数器记录，数据表每插入一条记录便加一
-
使用explain 获取一个大概的总数：
explain select * from table;
- 使用系统数据库中的 information_schema中的表 innodb_sys_tablestats 来获取相关数据库下相关表的总行数，仅是一个接近的总数：
7、存储过程
优点
- 减少数据库连接次数
- 无需重新编译，而SQL需要经过解析编译再执行
- 提高代码安全，减少SQL注入
缺点
- 移植性较差
- 开发调试复杂
- SQL本身是一种结构化查询语言，但不是面向对象的的，本质上还是
过程化的语言，面对复杂的业务逻辑，过程化的处理会很吃力
高可用
主从同步
-
主从不一致如何处理（存在时延）
-
- 若业务可接受则可以忽略
-
- 强制读主，高可用主库，
用缓存提高性能
- 强制读主，高可用主库，
-
- 在缓存中记录哪些记录发生过
写请求，来路由读主还是读从
- 在缓存中记录哪些记录发生过
-
读写分离
主从同步延时
如果延迟比较大，就先确认以下几个因素：
-
网络延迟
-
master负载
-
slave负载
1. 分库，将一个主库分为多个主库，每个主库的写并发
压力减少了几倍，此时主从延时可以忽略不计
2. 打开MySQL支持的并行复制，多个库并行复制。
若某个库的写并发很高，单库达到2k/s，则并行复制还是无意义
3. 重写代码，插入数据后立马查询可能查不到
8、监控
主从延迟监控
-
精确做法，判断主从二进制文件的偏移量
- 获取主服务器二进制文件名及便宜了：
show master status\G
- 获取从服务器上对应的主服务二进制文件名及偏移量：
show slave status;
- 简单做法，通过查看从节点中的Seconds_Behind_Master查看
show slave status\G
主从数据一致性检测
工具 pt-table-checksum
TPS/QPS
Innodb线程堵塞
select b.trx_mysql_thread_id '被堵线程ID',b.trx_query '被堵SQL',
c.trx_mysql_thread_id '堵塞线程ID',c.trx_query '堵塞SQL',
(UNIX_TIMESTAMP()-UNIX_TIMESTAMP(c.trx_started)) '堵塞时间'
from information_schema.innodb_lock_waits a
join information_schema.innodb_trx b on a.requesting_trx_id=b.trx_id
join information_schema.innodb_trx c on a.blocking_trx_id = c.trx_id
where (UNIX_TIMESTAMP()-UNIX_TIMESTAMP(c.trx_started))> 60
149
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
