数据库基础

4.串行化

select也加锁

4.undo & redo

mysql在执行事务时，会把磁盘页面读入缓存

都是innodb产生

redo log

• 作用：实现持久性，防止mysql崩溃引起数据丢失

  • innodb通过force log at commit机制实现事务的持久性，即在事务提交的时候，必须先将该事务的所有事务日志写入到磁盘上的redo log file和undo log file中进行持久化。

• 组成：一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的。

• Mysql数据修改随机写优化为顺序写

• 记录的是数据页的物理修改

undo log

• 实现原子性和MVCC
• 用来回滚未提交的事务
• 每当操作数据前，首先将数据备份到一个地方（这个存储数据备份的地方称为undolog）。然后再修改数据。如果出现了错误或者用户执行了rollback语句，可以利用undolog中的备份将数据恢复到事务开始之前的状态。
• MySQL5.5可以支持128个rollback segment，每个回滚段中有1024个undo log segment。即支持128*1024个undo操作

redo log与binlog

• 二进制日志是在存储引擎的上层产生的，不管是什么存储引擎，对数据库进行了修改都会产生二进制日志。而redo log是innodb层产生的，只记录该存储引擎中表的修改。并且二进制日志先于redo log被记录
• 二进制日志记录操作的方法是逻辑性的语句，而redo log是在物理格式上的日志，它记录的是数据库中每个页的修改。
• 二进制日志只在每次事务提交的时候一次性写入缓存中的日志"文件"。而redo log在数据准备修改前写入缓存中的redo log中，然后才对缓存中的数据执行修改操作；而且保证在发出事务提交指令时，先向缓存中的redo log写入日志，写入完成后才执行提交动作。

Undo+Redo
假设有A、B两个数据，值分别为1,2.
A. 事务开始.
B. 记录A=1到undolog.
C. 修改A=3.
D. 记录A=3到redolog.
E. 记录B=2到undolog.
F. 修改B=4.
G. 记录B=4到redolog.
写入binlog
H. 将redolog写入磁盘。
I. 事务提交

• redo/undo log是innodb层维护的，而binlog是mysql server层维护的

5.sql调优

参考

1. 根据慢日志定位慢sql

   • show variables like '%quer%';
     show status like '%slow_queries%';
     
     set global slow_query_log = on;
     set global long_query_time = 1; 需重启
     

2. 使用explain等工具分析sql

   • 字段
     • 重点关注rows
     • type 表示mysql找到需要的数据行的方式 ；index>all，一般出现这两个，就需要优化了
     • extra
       • Using filesort 无法利用索引完成排序
       • Using temporary 排序使用临时表，orderby groupby

3. 修改sql或者尽量让sql走索引

慢查询优化基本步骤

0. 先运行看看是否真的很慢，注意设置SQL_NO_CACHE
1. where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
2. explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
3. order by limit 形式的sql语句让排序的表优先查
4. 了解业务方使用场景
5. 加索引时参照建索引的几大原则
6. 观察结果，不符合预期继续从0分析

explain

执行计划

这里需要强调rows是核心指标，绝大部分rows小的语句执行一定很快（有例外，下面会讲到）。所以优化语句基本上都是在优化rows

explain select * from class_teacher;

列名	描述
id	在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
select_type	SELECT关键字对应的那个查询的类型，有简单查询、联合查询、子查询等
table	表名
partitions	匹配的分区信息
type	针对单表的访问方法
possible_keys	可能用到的索引
key	实际上使用的索引
key_len	实际使用到的索引长度，越短
ref	当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows	预估的需要读取的记录条数
filtered	某个表经过搜索条件过滤后剩余记录条数的百分比
Extra	一些额外的信息

type：显示连接使用了何种类型
		system	const的特例，仅返回一条数据的时候
		const	查找主键索引，返回的数据至多一条 属于精确查找
		eq_reg：唯一性索引，返回的数据至多一条。属于精确查找
		ref：查找非唯一性索引，返回匹配某一条件的多条数据。属于精确查找、数据返回可能是多条
		range：索引的范围查找
		index：索引的扫描
		all：表扫描
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
rows：mysql认为必须检查的用来返回请求数据的行数；
extra：如果返回值是如下两个就需要进行优化了
		using filesort：mysql需要进行额外的步骤来发现如果对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
		using temporary：MySQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行order by上，而不是group by 上

查询SQL执行时间

set profiling=1;
show profiles;

SQL语句执行得很慢的原因

https://mp.weixin.qq.com/s/pTywDcdg8AVZ8qvR0KZFFQ

偶尔很慢

• 数据库在刷新脏页，同步redolog

数据库更新的字段并不会马上同步持久化到磁盘中去，而是把这些更新的记录写入到 redo log 日记中去，等到空闲的时候，在通过 redo log 里的日记把最新的数据同步到磁盘中去。

• 执行的时候，遇到锁，如表锁、行锁

这个就比较容易想到了，我们要执行的这条语句，刚好这条语句涉及到的表，别人在用，并且加锁了，我们拿不到锁，只能慢慢等待别人释放锁了。或者，表没有加锁，但要使用到的某个一行被加锁了，这个时候，我也没办法啊。

一直都很慢

没用到索引

select * from t where 100 < c and c < 100000;

1. 字段没有索引
2. 字段有索引，但却没有用索引

索引字段做了运算

select * from t where c - 1 = 1000;

3. 函数操作导致没有用上索引

select * from t where pow(c,2) = 1000;

数据库自己选错索引

我们在进行查询操作的时候，例如

select * from t where 100 < c and c < 100000;

有时候就算你在 c 字段上有索引，系统也并不一定会走 c 这个字段上的索引，而是有可能会直接扫描扫描全表，找出所有符合 100 < c and c < 100000 的数据

为什么会这样呢？

系统在执行这条语句的时候，会进行预测：究竟是走 c 索引扫描的行数少，还是直接扫描全表扫描的行数少呢？显然，扫描行数越少当然越好了，因为扫描行数越少，意味着I/O操作的次数越少。

如果是扫描全表的话，那么扫描的次数就是这个表的总行数了，假设为 n；而如果走索引 c 的话，我们通过索引 c 找到主键之后，还得再通过主键索引来找我们整行的数据，也就是说，需要走两次索引。而且，我们也不知道符合 100 c < and c < 10000 这个条件的数据有多少行，万一这个表是全部数据都符合呢？这个时候意味着，走 c 索引不仅扫描的行数是 n，同时还得每行数据走两次索引。

所以呢，系统是有可能走全表扫描而不走索引的。那系统是怎么判断呢？

判断来源于系统的预测，也就是说，如果要走 c 字段索引的话，系统会预测走 c 字段索引大概需要扫描多少行。如果预测到要扫描的行数很多，它可能就不走索引而直接扫描全表了。

系统是怎么预测判断的呢？

系统是通过索引的区分度来判断的，一个索引上不同的值越多，意味着出现相同数值的索引越少，意味着索引的区分度越高。我们也把区分度称之为基数，即区分度越高，基数越大。所以呢，基数越大，意味着符合 100 < c and c < 10000 这个条件的行数越少。

所以呢，一个索引的基数越大，意味着走索引查询越有优势。

索引系统是通过遍历部分数据，也就是通过采样的方式，来预测索引的基数的，会有误差。

由于统计的失误，导致系统没有走索引，而是走了全表扫描

这里我声明一下，系统判断是否走索引，扫描行数的预测其实只是原因之一，这条查询语句是否需要使用使用临时表、是否需要排序等也是会影响系统的选择的。

通过强制走索引的方式来查询，例如

select * from t force index(a) where c < 100 and c < 100000;

我们也可以通过

show index from t;

来查询索引的基数和实际是否符合，如果和实际很不符合的话，我们可以重新来统计索引的基数，可以用这条命令

analyze table t;

来重新统计分析

1)查看mysql是否开启慢查询日志
show variables like 'slow_query_log';

2)设置没有索引的记录到慢查询日志
set global log_queries_not_using_indexes=on;

3)查看超过多长时间的sql进行记录到慢查询日志
show variables like 'long_query_time'

4)开启慢查询日志
set global slow_query_log=on

5)查看慢查询日志文件位置
show variables like 'slow%';


使用MySQL慢查日志对有效率问题的SQL进行监控

show variables like 'slow_query_log';    //查看是否开启慢查日志
set global slow_query_log_file = 'xxx' //设置慢查日志的文件地址
set global log_queries_not_using_indexes=on;    //是否把没有使用sql索引记录到慢查日志中
set global long_query_time=1;    //设置慢查日志的时间，查寻超过多少秒记录(单位：秒)

set global slow_query_log=on;
set global log_queries_not_using_indexes=on;
set global long_query_time=0;
set global slow_query_log_file='/Users/johnson/WordDir/mysql/slow.log'; // 这个文件必须存在，重连

慢查日志分析工具

• mysqldumpslow
• pt-query-digest

需要注意的sql类型：

• 查询次数多且每次查询占用时间长的SQL
  通常为pt-query-digest分析的前几个查询
• IO大的SQL
  • 注意pt-query-digest分析中的Rows examine项
  • 扫描行数越多，io越大
• 未命中索引的SQL
  注意pt-query-digest分析中的Rows examine和Row send 的对比
  当Rows examine>>Row send 基本上是使用了索引扫描或者表扫描的方式来进行查询，需要进行优化

sql 语句中count()有条件的时候为什么要加上or null

如count(release_year = ‘2006’ or NULL) 这部分 为什么要加上or NULL 直接count(release_year=‘2006’)有什么问题吗？不就是要找release_year = '2006’的数据吗，为什么要计算NULL的数据

因为 当 release_year不是 2006时 ，release_year=‘2006’ 结果false 不是 NULL，

Count在 值是NULL是 不统计数， （count(‘任意内容’)都会统计出所有记录数，因为count只有在遇见null时不计数，即count(null)==0，因此前者单引号内不管输入什么值都会统计出所有记录数）至于加上or NULL ， 很像其他编程里的or运算符，第一个表达式是true就是不执行or后面的表达式，第一个表达式是false 执行or后面的表达式 。当release_year不为2006时release_year = ‘2006’ or NULL 的结果是NULL，Count才不会统计上这条记录数

5. Sql语句

/* 统计如下： 学号  姓名	语文	数学	英语	总分	平均分 */
select t1.sid, t2.score, t3.score,t4.score, t5.max_score, t6.avg_score 
from 
	stu_course_score t1, 
	(select sid, score from stu_course_score where cid = 1) t2, 
	(select sid, score from stu_course_score where cid = 2) t3, 
	(select sid, score from stu_course_score where cid = 3) t4, 
	(select sid, max(score) max_score from stu_course_score group by sid) t5,
	(select sid, avg(score) avg_score from stu_course_score group by sid) t6 
where t1.sid = t2.sid and t1.sid = t3.sid and t1.sid = t4.sid and t1.sid = t5.sid and t1.sid = t6.sid;

/*列出各门课程成绩最好的学生(要求显示字段: 学号，姓名,科目，成绩) */
select t1.sid, t1.cid, t1.score 
from stu_course_score t1, 
	(select cid, max(score) max_score from stu_course_score group by cid) t2 
where t2.cid = t1.cid and t2.max_score = t1.score;

/* 求出李四的数学成绩的排名
核心 t1.score > t2.score
*/
select sid, cid, score, 
	(select count(*) from stu_course_score t1 where cid = 1 and t1.score > t2.score) + 1 ranking 
from stu_course_score t2 
where cid = 1 and sid =1;

/*查询第5名*/
SELECT * FROM (SELECT * FROM stu_scores ORDER BY score DESC LIMIT 5) stu_course_score ORDER BY score ASC LIMIT 1;

/*排名*/
SELECT t2.sid, t2.stu_name, t2.score,
	(SELECT count(*) FROM stu_course_score t1 WHERE	t1.score>t2.score) + 1 ranking
FROM stu_course_score t2
ORDER BY ranking;

查询第n名：

select min(grade) from score ORDER BY grade DESC LIMIT 2,1 # 第三名

查询排名：

没有“漏洞”

SELECT s1.id, s1.stu_id, s1.c_name, s1.grade, (SELECT COUNT(DISTINCT s2.grade) FROM score s2 WHERE s2.grade >= s1.grade)  `rank` from score as s1 ORDER BY s1.grade desc;

并列，去掉distinct

SELECT s1.id, s1.stu_id, s1.c_name, s1.grade, (SELECT COUNT(s2.grade) FROM score s2 WHERE s2.grade >= s1.grade)  `rank` from score as s1 ORDER BY s1.grade desc;

6. 三范式

第一范式

列都是不可再分,确保每列的原子性

中国北京市 -> 中国 北京市

第二范式

• 首先满足第一范式
• 非主键列不存在对主键的部分依赖
• 即第二范式要求每个表只描述一件事情

第三范式

• 满足第二范式
• 表中的列不存在对非主键列的传递依赖。除了主键订单编号外，顾客姓名依赖于非主键顾客编号

7.缓存

https://yq.aliyun.com/articles/647032

MySQL Query Cache(MySQL查询缓存)在MySQL Server中是默认打开的，但是网上各种资料以及有经验的DBA都建议生产环境中把MySQL Query Cache关闭

mysql> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |      --查询缓存是否可用
| query_cache_limit            | 1048576 |      --可缓存具体查询结果的最大值
| query_cache_min_res_unit     | 4096    |      --查询缓存分配的最小块的大小(字节)
| query_cache_size             | 599040  |      --查询缓存的大小
| query_cache_type             | ON      |      --是否支持查询缓存
| query_cache_wlock_invalidate | OFF     |      --控制当有写锁加在表上的时候，是否先让该表相关的 Query Cache失效
+------------------------------+---------+

在MySQL Server中打开Query Cache对数据库的读和写都会带来额外的消耗:

• 1. 读查询开始之前必须检查是否命中缓存。
• 2. 如果读查询可以缓存，那么执行完查询操作后，会查询结果和查询语句写入缓存。
• 3. 当向某个表写入数据的时候，必须将这个表所有的缓存设置为失效，如果缓存空间很大，则消耗也会很大，可能使系统僵死一段时间，因为这个操作是靠全局锁操作来保护的。
• 4. 对InnoDB表，当修改一个表时，设置了缓存失效，但是多版本特性会暂时将这修改对其他事务屏蔽，在这个事务提交之前，所有查询都无法使用缓存，直到这个事务被提交，所以长时间的事务，会大大降低查询缓存的命中

为了提高完全相同的Query语句的响应速度，MySQL Server会对查询语句进行Hash计算后，把得到的hash值与Query查询的结果集对应存放在Query Cache中。

MySQL 的每个Query Cache都是以SQL文本作为key来存储的，在应用Query Cache之前，SQL文本不会做任何处理。也就是说，两个SQL语句，只要相差哪怕一个字符(例如大小写不一样，多一个空格，多注释)，那么这两个SQL将使用不同的Cache地址。查询必须是完全相同(逐字节相同)才能够被认为是相同的。另外，同样的查询字符串由于其它原因可能认为是不同的。即两个SQL必须完全一致才会导致cache命中。即检查查询缓存时，MySQL Server不会对SQL做任何处理，它精确的使用客户端传来的查询，只要字符大小写或注释有点不同，查询缓存就认为是不同的查询；

where条件中如包含任何一个不确定的函数将永远不会被cache, 比如current_date, now等。

太大的result set不会被cache (< query_cache_limit)

MySQL缓存机制简单的说就是缓存sql文本及查询结果，如果运行相同的SQL，服务器直接从缓存中取到结果，而不需要再去解析和执行SQL。如果表更改了，那么使用这个表的所有缓存查询将不再有效，查询缓存中值相关条目被清空。

Query Cache的优点很明显，但是也不能忽略它所带来的一些缺点：

• 查询语句的hash计算和hash查找带来的资源消耗。如果将query_cache_type设置为1（也就是ON），那么MySQL会对每条接收到的SELECT类型的查询进行hash计算，然后查找这个查询的缓存结果是否存在。虽然hash计算和查找的效率已经足够高了，一条查询语句所带来的开销可以忽略，但一旦涉及到高并发，有成千上万条查询语句时，hash计算和查找所带来的开销就必须重视了。
• Query Cache的失效问题。如果表的变更比较频繁，则会造成Query Cache的失效率非常高。表的变更不仅仅指表中的数据发生变化，还包括表结构或者索引的任何变化。
• 查询语句不同，但查询结果相同的查询都会被缓存，这样便会造成内存资源的过度消耗。查询语句的字符大小写、空格或者注释的不同，Query Cache都会认为是不同的查询（因为他们的hash值会不同）。
• 相关系统变量设置不合理会造成大量的内存碎片，这样便会导致Query Cache频繁清理内存。