mysql性能优化知识汇总

一、本文内容大纲

  • mysql索引优化
  • mysql事务管理
  • mysql日志机制
  • mysql全局优化
  • 工作中对于mysql使用的一些技巧

本博客会保持更新，将日常工作中涉及到的mysql有用的知识囊括进来

二、mysql索引优化

1、什么是索引？

索引就是一种排好序的数据结构，而选取哪种数据结构最为高效也是mysql底层最核心的原理。

以下将讨论二叉树、红黑树、Hash表、B-Tree、B+树这5种数据结构作为mysql索引数据结构的优劣性
  •  为什么mysql底层不选择二叉树？

　因为二叉树这种数据结构的特点是：左子节点的值小于或等于父节点的值,右子节点的值大于或等于父节点的值。那么，当插入的数据是递增的话，二叉树会变成如下图所示的链表。

树的高度决定了树的查找效率

  •  为什么不选择红黑树？

　红黑树的出现就是为了解决二叉树的缺点（即极端情况下二叉树会变成 一条链表），但是当数据量过大时，其高度不可控，效率就变得很低。

随着数据量的增大，红黑树的高度变得不可控

  • B树和B+树的选择

                                       B树                                                                                                                     B+树

B树是通过在一个节点中放很多索引来控制树的高度。

B+树是对B树的优化，B+树的非叶子节点只存放索引，不存放数据。叶子结点存放所有的索引及数据。且叶子结点间通过指针相连接。

这样B+树就产生了冗余索引，即存放了重复的索引数据，为什么要这么设计呢？

因为一个节点中存放元素的大小是有限制的（mysql限制是16KB），这样，存储相同的元素B+树比B树的高度更低（非叶子结点中只存索引比既存索引又存数据所存放的元素要高很多），此外B+树的叶子结点用指针连接，提高了区间访问的能力。如select * from table where colum > 30 and colum < 40，那么只需要查找到coluum = 30 和 colum = 40 的叶子节点元素，中间部分的元素可以直接通过指针来进行遍历。

  • Hash数据结构的优势和劣势

将索引进行一次hash运算后再存储，每个索引元素都有一个对应的hash值。很多时候hash索引比B+树的效率更高，但是一般不用，因为有很多缺点：

①仅仅能满足"="，"in"，不支持范围查找②hash冲突问题

 2、MyISAM存储引擎和InnoDB存储引擎对于索引的实现

这两个引擎的区别在于聚集索引和非聚集索引：

  • 聚集索引(InnoDB引擎采用的方式)

　B+树的叶子结点存放的是索引所在行其他列的所有数据，即叶子结点包含了完整的数据记录。

  • 非聚集索引（MyISAM引擎采用的方式）
用B+树结构展开查找数据后，叶子结点存放的是数据所在的地址，再通过该地址去相关文件查找数据（数据在另外的一个文件中存储）。

 3、explain工具的使用

  •  id：代表了各个表的查询顺序，id大的先查询。
  • select_type：表示对应行是简单还是复杂的查询
①simple：简单查询，查询不包含子查询和union
②primary：复杂查询中最外层的select
③subquery：包含在select中的子查询（不在from子句中）
④derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）
⑤union：在 union 中的第二个和随后的 select
  • table：表示正在访问哪张表
  • type：表示查询的效率
依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL
一般来说，得保证查询达到range级别，最好达到ref
system和const都是在mysql查询优化阶段能将其转换为一个常量，system是const的特例，当表里只有一条数据的时候就是system.
eq_ref，两张表关联通过主键关联或者唯一索引关联
ref：通过普通索引查询
range：范围查找，如in、between等
index：扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描直接遍历叶子结点，速度还是比较慢的。因为二级索引比主键索引要小，所以、index比ALL要快一些
all：全表扫描，效率最低
  • possible_key：可能会用到的索引，实际如果数据量比较小的话可能不走索引。
  • key：实际采用哪个索引，可以采用force index(索引名)的方式强制走索引。
  • key_len：用到的索引长度，可以计算出来联合索引实际走了哪几个索引。
key_len计算规则如下：
字符串，char(n)和varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字或字母占1个字节，一个汉字占3个字节
char(n)：如果存汉字长度就是 3n 字节
varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为varchar是变长字符串
数值类型
tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节　　
时间类型　
date：3字节
timestamp：4字节
datetime：8字节
如果字段允许为 NULL，需要1字节记录是否为 NULL
索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。
  • 　ref列：这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）
  • rows列：这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。
  • Extra列:有以下几种情况：
1）Using index：使用覆盖索引
覆盖索引定义：mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index；覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值
2）Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖
3）Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围；
4）Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。
5）Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。
6）Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段

 4、like、in、or是否走索引比较特殊

在用in 或者or的时候，当表的数据量较大时，会走索引，表数据量较小时可能就全表扫描不走索引了。
在用like xx%的时候不论表数据量大小都会走索引。like如此特殊的原因是使用了索引下推。
索引下推：正常来说，当使用like后，后面的字段是无序的，走不了索引，但是mysql底层对此做了优化，再遍历索引的过程中会对其他的字段同时做判断，删选出符合条件的主键id，再根据主键进行回表。

like KK%相当于=常量，%KK和%KK% 相当于范围

5、trace工具

因为开启trace工具会影响mysql的性能，所以用完之后要马上关闭
set session optimizer_trace="enabled=on",end_markers_in_json=on; -- 开启trace
然后在查询语句同时查询select * from information_schema.OPTIMIZER_TRACE
详细内容见trace工具解析
set session optimizer_trace="enabled=off",end_markers_in_json=off;-- 关闭trace

 了解即可，基本上用不到

trace工具用法：
1 mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on; ‐‐开启trace
2 mysql> select * from employees where name > 'a' order by position;
3 mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;
4  
5 查看trace字段：
6 {
7  "steps": [
8  {
9  "join_preparation": { ‐‐第一阶段：SQL准备阶段，格式化sql
10  "select#": 1,
11  "steps": [
12  {
13  "expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`empl
oyees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from
`employees` where (`employees`.`name` > 'a') order by `employees`.`position`"
14  }
15  ] /* steps */
16  } /* join_preparation */
17  },
18  {
19  "join_optimization": { ‐‐第二阶段：SQL优化阶段
20  "select#": 1,
21  "steps": [
22  {
23  "condition_processing": { ‐‐条件处理
24  "condition": "WHERE",
25  "original_condition": "(`employees`.`name` > 'a')",
26  "steps": [
27  {
28  "transformation": "equality_propagation",
29  "resulting_condition": "(`employees`.`name` > 'a')"
30  },
31  {
32  "transformation": "constant_propagation",
33  "resulting_condition": "(`employees`.`name` > 'a')"
34  },35  {
36  "transformation": "trivial_condition_removal",
37  "resulting_condition": "(`employees`.`name` > 'a')"
38  }
39  ] /* steps */
40  } /* condition_processing */
41  },
42  {
43  "substitute_generated_columns": {
44  } /* substitute_generated_columns */
45  },
46  {
47  "table_dependencies": [ ‐‐表依赖详情
48  {
49  "table": "`employees`",
50  "row_may_be_null": false,
51  "map_bit": 0,
52  "depends_on_map_bits": [
53  ] /* depends_on_map_bits */
54  }
55  ] /* table_dependencies */
56  },
57  {
58  "ref_optimizer_key_uses": [
59  ] /* ref_optimizer_key_uses */
60  },
61  {
62  "rows_estimation": [ ‐‐预估表的访问成本
63  {
64  "table": "`employees`",
65  "range_analysis": {
66  "table_scan": { ‐‐全表扫描情况
67  "rows": 10123, ‐‐扫描行数
68  "cost": 2054.7 ‐‐查询成本
69  } /* table_scan */,
70  "potential_range_indexes": [ ‐‐查询可能使用的索引
71  {
72  "index": "PRIMARY", ‐‐主键索引
73  "usable": false,
74  "cause": "not_applicable"
75  },
76  {
77  "index": "idx_name_age_position", ‐‐辅助索引
78  "usable": true,
79  "key_parts": [
80  "name",
81  "age",
82  "position",
83  "id"
84  ] /* key_parts */
85  }
86  ] /* potential_range_indexes */,
87  "setup_range_conditions": [88  ] /* setup_range_conditions */,
89  "group_index_range": {
90  "chosen": false,
91  "cause": "not_group_by_or_distinct"
92  } /* group_index_range */,
93  "analyzing_range_alternatives": { ‐‐分析各个索引使用成本
94  "range_scan_alternatives": [
95  {
96  "index": "idx_name_age_position",
97  "ranges": [
98  "a < name" ‐‐索引使用范围
99  ] /* ranges */,
100  "index_dives_for_eq_ranges": true,
101  "rowid_ordered": false, ‐‐使用该索引获取的记录是否按照主键排序
102  "using_mrr": false,
103  "index_only": false, ‐‐是否使用覆盖索引
104  "rows": 5061, ‐‐索引扫描行数
105  "cost": 6074.2, ‐‐索引使用成本
106  "chosen": false, ‐‐是否选择该索引
107  "cause": "cost"
108  }
109  ] /* range_scan_alternatives */,
110  "analyzing_roworder_intersect": {
111  "usable": false,
112  "cause": "too_few_roworder_scans"
113  } /* analyzing_roworder_intersect */
114  } /* analyzing_range_alternatives */
115  } /* range_analysis */
116  }
117  ] /* rows_estimation */
118  },
119  {
120  "considered_execution_plans": [
121  {
122  "plan_prefix": [
123  ] /* plan_prefix */,
124  "table": "`employees`",
125  "best_access_path": { ‐‐最优访问路径
126  "considered_access_paths": [ ‐‐最终选择的访问路径
127  {
128  "rows_to_scan": 10123,
129  "access_type": "scan", ‐‐访问类型：为scan，全表扫描
130  "resulting_rows": 10123,
131  "cost": 2052.6,
132  "chosen": true, ‐‐确定选择
133  "use_tmp_table": true
134  }
135  ] /* considered_access_paths */
136  } /* best_access_path */,
137  "condition_filtering_pct": 100,
138  "rows_for_plan": 10123,
139  "cost_for_plan": 2052.6,
140  "sort_cost": 10123,141  "new_cost_for_plan": 12176,
142  "chosen": true
143  }
144  ] /* considered_execution_plans */
145  },
146  {
147  "attaching_conditions_to_tables": {
148  "original_condition": "(`employees`.`name` > 'a')",
149  "attached_conditions_computation": [
150  ] /* attached_conditions_computation */,
151  "attached_conditions_summary": [
152  {
153  "table": "`employees`",
154  "attached": "(`employees`.`name` > 'a')"
155  }
156  ] /* attached_conditions_summary */
157  } /* attaching_conditions_to_tables */
158  },
159  {
160  "clause_processing": {
161  "clause": "ORDER BY",
162  "original_clause": "`employees`.`position`",
163  "items": [
164  {
165  "item": "`employees`.`position`"
166  }
167  ] /* items */,
168  "resulting_clause_is_simple": true,
169  "resulting_clause": "`employees`.`position`"
170  } /* clause_processing */
171  },
172  {
173  "reconsidering_access_paths_for_index_ordering": {
174  "clause": "ORDER BY",
175  "steps": [
176  ] /* steps */,
177  "index_order_summary": {
178  "table": "`employees`",
179  "index_provides_order": false,
180  "order_direction": "undefined",
181  "index": "unknown",
182  "plan_changed": false
183  } /* index_order_summary */
184  } /* reconsidering_access_paths_for_index_ordering */
185  },
186  {
187  "refine_plan": [
188  {
189  "table": "`employees`"
190  }
191  ] /* refine_plan */
192  }193  ] /* steps */
194  } /* join_optimization */
195  },
196  {
197  "join_execution": { ‐‐第三阶段：SQL执行阶段
198  "select#": 1,
199  "steps": [
200  ] /* steps */
201  } /* join_execution */
202  }
203  ] /* steps */
204 }
205  
206 结论：全表扫描的成本低于索引扫描，所以mysql最终选择全表扫描
207  
208 mysql> select * from employees where name > 'zzz' order by position;
209 mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;
210  
211 查看trace字段可知索引扫描的成本低于全表扫描，所以mysql最终选择索引扫描
212  
213 mysql> set session optimizer_trace="enabled=off"; ‐‐关闭trace

6、mysql索引优化原则

  •  order by 和 group by 优化

尽量在索引上进行排序，遵循最左前缀原则。group by的实质是先排序后分组，如果不需要排序可以加上order by null 来禁止排序，注意where要高于having，能写在where中的限定条件就不要去having限定了。

  • 索引设计原则
①当主体代码都写完之后再统筹的去建立索引
②可以设计一个或者两三个联合索引（尽量少建单值索引），让每一个联合索引都尽量去包含sql语句中的where、order by、group by的字段，尽量满足最左前缀原则
③尽量在值比较多的字段上建立索引，这样才能发挥出B+树二分查找的优势
④对于varchar(255)的大字段上建立索引，可以取该字段的前20位建立索引，类似 key index(name(20),age,position)。如果采用了这种方式，对于where查询来说是可以的，但是对于order by 和 group by就没有作用了
⑤where与order by冲突时优先where
⑥一般范围查找的字段要建在最后面，因为一旦涉及到范围查找它后面的索引都不生效了。

 

  •  分页优化
①对于select * from table limit 90000,10
这种查询方式效率很低，mysql会查询出从第一条查询到90010条记录，并将前90000条记录舍弃掉
对于主键自增且连续的表（效率极高）
select * from table where id > 90000 limit 10
②根据非主键字段排序并分页
select * from table order by name limit 90000,10
这样查询效率很低，即便name建立了索引也有可能不走索引
select * from table e inner join (select id from table order by bane limit 90000,10) ed  on e.id = ed.id
这样用覆盖索引去优化，会走索引，效率较高
  •  join连接优化
select * from a inner join b on a.id = b.id
mysql底层会选择一张小表作为驱动表，对于小表做全表扫描，然后再拿扫描到的结果去大表中根据关联条件做过滤
对于大表而言，连接条件一定要建立索引
一般默认小表作为驱动表，如果mysql选择驱动表错了，可以手动指定驱动表
select * from a straight_join b on a.id = b.id  代表选择b作为驱动表
  •  in和exists优化
select * from a where id in (select id from b)
这种写法相当于是先查询出b的id，然后拿着这个id循环去a表里面查询，对于b的数据集小于a的数据集的时候，in要优于exists
select * from a where exists (select 1 from b where b.id = a.id)
这种写法相当于是先查询出a的所有结果，然后拿着查询出的全部id循环去b表里面查询，对于a的数据集小于b的数据集的时候，exists要优于in

 

  •  count(*)优化
count(*),count(1),count(id),count(name)这四种写法效率几乎一样，差别可忽略不计，尽量使用count(*)，因为count(*)会统计null值,count(列名)则不会
对于MyISAM引擎来说，count(*)的效率极高，因为底层会在一个地方维护这个表的总数
对于InnoDB引擎来说，如果表数据量很大，count(*)的效率也不会很高，可以用show table status like 表a 来用Rows粗略估计数量，但是不准，慎用。
  •  建表规范
1、单表行数超过500万行或者单表容量超过2GB，才推荐进行分库分表
2、业务上具有唯一特性的字段，必须建成唯一索引
3、超过3张表不允许使用join
4、查询尽量避免使用左模糊或者全模糊查询，如果一定要使用建议走搜索引擎
5、尽量避免使用in，如果非要使用要控制in后面的集合元素要控制在1000个之内
6、所有字符存储要使用utf8字符集，存储表情用utf8mb4
  •  各种类型数据选择

 尽量使用更小的数据类型，更节省空间。尽量把字段定义成NOT NULL，避免使用NULL。

如果确定数字类型的数据没有负数，一定要勾选无符号，可以扩大一倍的数据范围。

对于char()和varchar()的选择，因为char类型是定长的字符串，如果长度不够尾部会补空格（检索的时候自动去掉空格），varchar是变长字符串。如果确定每条数据的长度都是一致的可以用CHAR，否则还是用varchar吧

二、mysql事务管理

 1、什么是事务？

一组操作要么成功，要么失败，目的是保证数据最终的一致性。

  • 原子性：当前事务的操作要么同时成功，要么同时失败。原子性由 undo log日志来实现。
  • 一致性：使用事务的最终目的，由其他3个特性和业务逻辑的正确性保证的。
  • 隔离性：在事务并发执行时，他们内部的操作不能互相干扰。由mysql的各种锁以及MVCC机制来实现。
  • 持久性：一旦提交了事务，它对数据库的改变就应该是永久的。持久性由redo logo日志来实现。

2、4种隔离级别

set tx_isolation='read-uncommitted';
set tx_isolation='read-committed';
set tx_isolation='repeatable-read';
set tx_isolation='serializable'；
1、读未提交（RU）   脏读    备注：读到了别的事务中未提交的数据。
脏读最大的问题就是可以读到别的事务中未提交的数据。这时候拿着这个数据去进行修改操作后，如果之前未提交的事务发生了回滚，那么之前查到的数据就是脏数据，如果要解决脏读问题，可以采用“读已提交”
2、读已提交 (RC)  不可重复度    备注：查询结果受其他事务影响
不可重复读最大的问题就是，在用一事务中的同一条查询语句查询。如果两次查询的中间有别的事务提交修改了该数据，那么两次查询的结果就会不同。
3、可重复读(RR)    幻读    备注：在开启事务的时候会开启一个快照，查询过程中不受其他事务影响
可重复读最大的问题就是，当我开启了两个事务a,b。其中b更改了某个表的数据，并提交了事务，当然这时候事务a并查不到b提交的数据，但是a却可以通过update语句修改这条数据，很明显是脏写了。
可重复读会存在脏写的情况，怎么解决呢？
直接在事务中用更新语句更新，这样的话在更新的时候就会用数据库最新的数据来进行更新。如：
update a set b = b*20 where id = 1，这样，b的值就会被用实际数据库的数据*20来运算。
4、串行    解决上面所有问题，包括脏读。但是生产环境一般不用串行，效率太低。
持久性：一旦提交了事务，他对数据库的改变就应该是永久性的。持久性由redo log日志来实现
一致性：使用事务的最终目的，由其他3个特性以及业务代码正确逻辑来实现

3、事务的优化原则

①将查询等数据准备操作放到事务外
②事务避免远程调用，远程调用要设置超时，防止事务等待太久
③事务中避免一次性处理太多数据，可以拆分成多个事务分次处理
④更新等涉及加锁的操作尽可能放在事务靠后的位置
⑤能异步处理的尽量异步处理
⑥应用侧（业务代码）保证数据一致性，非事务执行

4、事务时间过长杀掉进程（锁表、卡ddl）

查询事务执行时间超过10秒的事务
select * from information_schema.innodb_trx
where TIME_TO_SEC(timediff(now(),trx_started)) > 10;
查到线程id,直接kill id

5、查询操作方法需要使用事务吗？

一般是不用的，但是如果是数据修改比较频繁的业务场景，且查询时间较长（报表场景）。最好加一个可重复读的事务，因为可重复读的“快照”特性，使得就算查询时间很长，前后两个sql查的都是快照下的数据，虽然查询的不是最新的数据库中的数据，但是能保证两个数据的时间维度相同。

三、mysql锁机制及MVCC底层原理

1、锁的分类

  • 乐观锁：乐观锁适合读操作较多的地方。如果在写操作较多的场景使用乐观锁会导致对比次数过多，影响性能。 
    • 举个例子：有事务A、B对某个数据同时进行更新操作，update table set colum = 500 where id= 1 and version = 1，当事务A对某个数据做修改时，会对其版本号+1，当事务B再执行此更新操作时，数据库中已经没有版本为1的数据了。
            事务B就会再次执行update table set colum = 500 where id= 1 and version = 2，不断轮询版本号，直到找到数据库中存储的版本号，修改完数据为止。
  • 悲观锁：悲观锁适合写操作较多的地方。
　　　　　　举个例子：事务A、B对某个数据同时进行更新操作，想将colum的值+50，那么事务A先执行了Update table set colum = colum + 50 where id = 1，事务B等待A提交完事务之后再次执行同样的操作。
  • 行锁：开销大、加锁慢、并发高
InnoDB的行锁是针对索引上加的锁（在对应索引项上做标记），不是在整行记录上加的锁。并且该索引不能失效，否则会从行锁升级为表锁（RR级别会升级为表锁，RC级别不会升级为表锁）。
为什么不走索引的情况下RR级别会从行锁升级为表锁，而RC级别不会呢？
因为在RR隔离级别下，需要解决不可重复读和幻读的问题，所以在遍历扫描聚集索引记录时，为了防止扫描过的索引被其他事务修改（不可重复读问题）或间隙被其它事务插入记录（幻读问题），从而导致数据不一致，
所以mysql的解决方案就是把所有扫描过的索引记录和间隙都锁上。
  • 表锁：开销小、加锁慢、并发低
  • 页锁：Innodb不支持
  • 间隙锁：只要在数据间隙范围内锁了一条不存在的记录，那么就会锁住整个间隙范围（不锁边界记录），这样就能防止其他事务在这个间隙范围内插入数据，就解决了可重复读隔离级别的幻读问题。

2、查询数据库中的事务及锁

show status like 'innodb_row_lock%';
对各个状态量的说明如下：
Innodb_row_lock_current_waits: 当前正在等待锁定的数量
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg: 每次等待所花平均时间
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间
Innodb_row_lock_waits: 系统启动后到现在总共等待的次数

对于这5个状态变量，比较重要的主要是：
Innodb_row_lock_time_avg （等待平均时长）
Innodb_row_lock_waits （等待总次数）
Innodb_row_lock_time（等待总时长）
-- 查看事务
select * from INFORMATION_SCHEMA.INNODB_TRX;
-- 查看锁，8.0之后需要换成这张表performance_schema.data_locks
select * from INFORMATION_SCHEMA.INNODB_LOCKS;
-- 查看锁等待，8.0之后需要换成这张表performance_schema.data_lock_waits
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

-- 释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到
kill trx_mysql_thread_id

3、MVCC底层原理

mysql在读已提交和可重复读这两个隔离级别下都实现了MVCC（多版本并发控制机制）

MVCC的核心实现原理是undo日志（事务历史记录版本链）和read-view（一致性视图），下面举一个例子深入理解这两个东西：

 

 在可重复读隔离级别：当事务开启，执行任何查询sql时都会形成当前事务的一致性视图read-view，该视图在事务结束之前都不会发生变化（如果是读已提交的话每次查询都会重新生成read-view），

这个视图由执行查询时所有未提交事务id数组（数组里最小的id为min_id）和已创建的最大事务id（max_id）组成，事务里的任何sql查询结果需要从对应版本链里的最新数据开始逐条跟read-view做比对从而得到最终的快照结果。
版本链比对规则：（了解即可）
1. 如果 row 的 trx_id 落在绿色部分( trx_id<min_id )，表示这个版本是已提交的事务生成的，这个数据是<span="">可见的；
2. 如果 row 的 trx_id 落在红色部分( trx_id>max_id )，表示这个版本是由将来启动的事务生成的，是不可见的(若 row 的 trx_id 就是当前自己的事务是可见的）；
3. 如果 row 的 trx_id 落在黄色部分(min_id <=trx_id<= max_id)，那就包括两种情况
    a. 若 row 的 trx_id 在视图数组中，表示这个版本是由还没提交的事务生成的，不可见(若 row 的 trx_id 就是当前自己的事务是可见的)；
    b. 若 row 的 trx_id 不在视图数组中，表示这个版本是已经提交了的事务生成的，可见。

四、mysql日志机制

1、一条sql执行的全流程

 总结为以下几点：

update table set name = '1' where id = '1'
1、将id为1的记录所在的一整页数据加载进缓存池
2、将更新数据的旧值写入undo日志中，形成版本链，便于回滚
3、将第一步缓存池中的数据更新到内存中
4、写redo日志，将redo日志写到redo log buffer缓存中
5、将redo日志顺序写入磁盘，准备提交事务
6、向binlog日志写入磁盘
7、写入commit标记到redo日志中，提交事务完成，该标记为了保证事务提交后redo与binlog数据一致
8、在系统空闲时将数据随机以page为单位写入到磁盘表文件中

2、redo log日志和binlog日志

redo log 日志的作用的当事务成功提交，但是缓存池中的数据没有及时写入磁盘中，可以用redo日志来进行恢复。
binlog日志的作用是磁盘文件的数据被删除了，想要恢复。
  • redo log日志关键参数
1、innodb_log_buffer_size：设置redo log buffer大小参数，默认16M，最大值是4096M，最小值为1M
show variables like '%innodb_log_buffer_size%'
2、innodb_log_group_home_dir：设置redo log文件存储位置参数，默认为"./"，即innodb数据文件存储位置，其中ib_logfile0 和 ib_logfile1即为redo log文件。
show variables like '%innodb_log_group_home_dir%'
3、innodb_log_files_in_group：设置redo log文件的个数，默认2个，最大100个
show variables like '%innodb_log_files_in_group%'
4、innodb_log_file_size：设置单个redo log文件大小，默认值是48M，最大值为512G，注意最大值指的是整个redo log系列文件之和
show variables like '%innodb_log_file_size%'
5、innodb_flush_log_at_trx_commit:这个参数控制redo log的写入策略，它有三种可能取值：
设置为0：表示每次事务提交时都只是把 redo log 留在 redo log buffer 中，数据库宕机可能会丢失数据。
设置为1(默认值)：表示每次事务提交时都将 redo log 直接持久化到磁盘，数据最安全，不会因为数据库宕机丢失数据，但是效率稍微差一点，线上系统推荐这个设置。
设置为2：表示每次事务提交时都只是把 redo log 写到操作系统的缓存page cache里，这种情况如果数据库宕机是不会丢失数据的，但是操作系统如果宕机了，page cache里的数据还没来得及写入磁盘文件的话就会丢失数据。
InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 操作系统函数 write 写到文件系统的 page cache，然后调用操作系统函数 fsync 持久化到磁盘文件。

# 查看innodb_flush_log_at_trx_commit参数值：
show variables like 'innodb_flush_log_at_trx_commit';
# 设置innodb_flush_log_at_trx_commit参数值(也可以在my.ini或my.cnf文件里配置)：
set global innodb_flush_log_at_trx_commit=1;
  • binlog日志
5.7默认关闭，8.0默认开启
  • 开启binlog日志

需要修改配置文件my.ini(windows)或my.cnf(linux)，然后重启数据库。 在配置文件中的[mysqld]部分增加如下配置：

# log‐bin设置binlog的存放位置，可以是绝对路径，也可以是相对路径，这里写的相对路径，则binlog文件默认会放在data数据目录下
 log‐bin=mysql‐binlog
# Server Id是数据库服务器id，随便写一个数都可以，这个id用来在mysql集群环境中标记唯一mysql服务器，集群环境中每台mysql服务器的id不能一样，不加启动会报错
 server‐id=1
# 其他配置
 binlog_format = row 
# binlog_format可以配三个参数，各参数代表的含义如下所示：
  • STATEMENT：基于SQL语句的复制，每一条会修改数据的sql都会记录到master机器的bin-log中，这种方式日志量小，节约IO开销，提高性能，但是对于一些执行过程中才能确定结果的函数，比如UUID()、SYSDATE()等函数如果随sql同步到slave机器去执行，则结果跟master机器执行的不一样。
  • ROW：基于行的复制，日志中会记录成每一行数据被修改的形式，然后在slave端再对相同的数据进行修改记录下每一行数据修改的细节，可以解决函数、存储过程等在slave机器的复制问题，但这种方式日志量较大，性能不如Statement。举个例子，假设update语句更新10行数据，Statement方式就记录这条update语句，Row方式会记录被修改的10行数据。
  • MIXED：混合模式复制，实际就是前两种模式的结合，在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种，如果sql里有函数或一些在执行时才知道结果的情况，会选择Row，其它情况选择Statement，推荐使用这一种。
#
 expire_logs_days = 15 # 执行自动删除binlog日志文件的天数， 默认为0， 表示不自动删除
 max_binlog_size = 200M # 单个binlog日志文件的大小限制，默认为 1GB

# show variables like '%log_bin%'; # 查看binlog是否开启
# show binary logs; # 查看binlog存放的位置

发生以下任何事件时, binlog日志文件会重新生成：

①服务器启动或重新启动

②服务器刷新日志，执行命令flush logs

③日志文件大小达到 max_binlog_size 值，默认值为 1GB

  • 删除 binlog 日志文件：
删除当前的binlog文件
reset master;
# 删除指定日志文件之前的所有日志文件，下面这个是删除6之前的所有日志文件，当前这个文件不删除
purge master logs to 'mysql-binlog.000006';
# 删除指定日期前的日志索引中binlog日志文件
purge master logs before '2023-01-21 14:00:00';
  • 查看bnlog日志
mysqlbinlog --no-defaults -v --base64-output=decode-rows "binlog文件地址"
  • binlog日志文件恢复数据

 有两种方式，可以通过binlog的开始结束位置恢复，也可以通过起始结束时间来恢复

mysqlbinlog  --no-defaults --start-position=219 --stop-position=701 --database=test D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000009 | mysql -uroot -p123456 -v test
# 找到第一条sql BEGIN前面的时间戳标记 SET TIMESTAMP=1674833544，再找到第二条sql COMMIT后面的时间戳标记 SET TIMESTAMP=1674833663，转成datetime格式
mysqlbinlog  --no-defaults --start-datetime="2023-1-27 23:32:24" --stop-datetime="2023-1-27 23:34:23" --database=test D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000009 | mysql -uroot -p123456 -v test
  • 定期备份数据库

 binlog与定期备份数据库相结合才可以做到恢复数据的功效，以下是几个备份数据库的命令



mysqldump -u 用户名 -p 数据库名 > 备份文件路径.sql; #备份单个数据库


mysqldump -u 用户名 -p --all-databases > 备份文件路径.sql；#备份整个数据库


 










mysqldump -u 用户名 -p 数据库名 表1 表2 > 备份文件路径.sql; #备份指定表


mysql -u root -p test < 备份文件名 #恢复整个数据库，test为数据库名称，需要自己先建一个数据库test












五、mysql全局优化与mysql8.0新特性


 1、mysql全局参数配置（提升的性能不是特别高，一般不是dba不建议自己去优化）


 下面参数都是服务端参数，默认在配置文件的 [mysqld] 标签下


 max_connections=3000   # 最大连接数


max_user_connections=2980   #允许用户最大连接数，剩余的20个连接数作为管理员使用


back_log=300  #MySQL能够暂存的连接数量。如果MySQL的连接数达到max_connections时，新的请求将会被存在堆栈中，等待某一连接释放 资源，该堆栈数量即back_log，如果等待连接的数量超过back_log，将被拒绝。 


wait_timeout=300 # 指的是app应用通过jdbc连接mysql进行操作完毕后，空闲300秒后断开，默认是28800，单位秒，即8个小时。这里基本上得改，默认的太大了。


innodb_buffer_pool_size=40G #innodb存储引擎buffer pool缓存大小，一般为物理内存的60%-70%。


innodb_lock_wait_timeout=10 # 行锁锁定时间，默认50s，根据公司业务定，没有标准值。 


2、mysql 8.0新特性


    

  • 新增降序索引
    • 





create table t1(c1 int,c2 int,index idx_c1_c2(c1,c2 desc))；




这时候如果查询的时候按照c1升序，c2降序（或者相反）的顺序进行排序，那么将不会走文件排序，而是利索索引排序。


    

  • group by 不再隐式排序
    • 



5.7版本的mysql在group by 之后会自动根据group by字段做一个排序，而8.0版本的不再自动排序。


    

  • 增加隐藏索引
    • 





alter table t2 alter index idx_c2 visible;  # 取消设置隐藏索引
alter table t2 alter index idx_c2 invisible; # 设置为隐藏索引




在生产环境中，如果想删掉一个索引，可以先把它设置为隐藏索引，当确定其没有用了之后再删掉就行了。


    

  • 新增函数索引
    • 



基本上用不到，很鸡肋。


    

  • 窗口函数
    • 



在聚合函数后面加上over()就变成分析函数了，后面可以不用再加group by制定分组，因为在over里已经用partition关键字指明 了如何分组计算，这种可以保留原有表数据的结构，不会像分组聚合函数那样每组只返回一条数据。




select name,channel,balance,sum(balance) over(partition by name) as sum_balance from account_channel;




    

  • 默认字符集由latin1变为utf8mb4 
    • 



六、工作中用到的一些不常见的sql语句




-- 查看建表ddl语句
show create table a;
-- 查看表中所有字段信息
describe a

-- 创建1个表b和表a的表结构一样
create table b like a;

-- 创建1个表c和表a的表结构和数据都一样
CREATE TABLE c AS SELECT * FROM a;




 







            

            
posted @ 
2025-06-13 17:17 
小小博客yhw 
阅读(32) 
评论(0) 
 
收藏 
举报