阅读 1469

关于mysql,我做了个总结!

使用覆盖索引减少IO

mysql的索引类型主要分为聚集索引和非聚集索引,通过聚集索引可以获取到整行数据,而通过非聚集索引只能获得主键id和当前字段。当我们要查询的字段就是非聚集索引叶子含有的字段(primary key + field),那么就不需要回表查询更多的字段,这就是覆盖索引。

# name是索引字段
1. SELECT id,name from user WHERE name='假装懂编程'#不需要回表
2. SELECT id,name,age from user WHERE name='假装懂编程' #需要回表
复制代码

1:因为name索引包含id和name数据,所以通过name索引就能得到所需数据。
2:因为name索引不包含age数据,所以仅通过name索引是得不到age数据的,此时还会去主键索引里获取数据(回表)。

不要用select *

自己用什么字段就查询什么字段,不要使用select *,当我们用了select *

  • 肯定用不了覆盖索引。
  • 多余的字段会造成mysql解析负担。
  • 多余的字段会造成更多的网络开销。

复杂的sql要explain

当我们写下一条复杂的sql时,如果我们通过肉眼已经无法辨别mysql会使用什么索引或者mysql会不会使用到索引,这时候通过explain来查看我们的sql执行计划是个不错的选择。

mysql> explain select id,name from user3 where name="假装懂编程";
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | user3 | NULL       | ref  | name          | name | 403     | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
复制代码

ref是个比较重要的指标(以下按顺序越靠后说明效率越低):

system->const->eq_ref->ref->fulltext->ref_or_null->index_merge->unique_subquery->index_subquery->range->index->all。  
复制代码

特别当我们explain出ref=all的时候要注意,这时你的sql是要进行全表扫描的。

varchar和char的区别

char

固定长度(字符数),如果长度小于定义的长度会用空格补充,会浪费空间。但是因为长度固定所以char的存取速度要比varchar快,char最多能存放255个字符,和编码无关。

varchar

变长(字符数),如果长度小于定义的长度会按照实际的长度来存储,相比char会节约空间,但是因为是变长的,所以存取速度相比char要慢。在存储方面,若列的长度小于等于255字节,那么额外要1个字节记录长度,如果列的长度大于255字节,那么额外要2个字节记录长度。

分页时小心limit陷阱

未用到索引的limit

我们在分页的业务中经常用到limit,比如后台查看用户的留言,由于留言太多,我们不得不分页。假设我们每页展示100条数据,那么每页的查询可能这样。

select * from message limit 0,100 # 第一页
select * from message limit 100,100 #第二页
...
select * from message limit 1000000,100 #第10000复制代码

当我们查到第10000页时,此时要过滤1000000的数据,然后再取100条数据,这个耗时肯定是巨大的。这个问题的本质还是没用到索引,那么我们让分页用到索引就好了,我们可以这样解决:
因为主键id是自增且连续的,这样我们每次通过id来定位到我们的第一条数据,然后向后取100条。这个id就是你上一次获取的分页数据中,最大的那个id。

select * from message where id>=[id] limit 100
复制代码

此场景适用主键是自增连续的,且不能有where条件的,有where条件的话会过滤数据。

需要回表的limit

现在有这样一张表,除了主键索引外还有个user_id的普通索引

CREATE TABLE `message` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL DEFAULT '0',
  `name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB;
复制代码

表的数据量有将近150w:

mysql> select count(*) from message;
+----------+
| count(*) |
+----------+
|  1496067 |
+----------+
复制代码

这时候我要统计user_id=99999的用户数据,并且在所有的数据中只取第70w开始的后面5条,于是我这样执行了:

mysql> select * from message where user_id=99999 limit 700000,5;
+---------+---------+---------+
| id      | user_id | name    |
+---------+---------+---------+
| 1282606 |   99999 | t154458 |
| 1282607 |   99999 | t154459 |
| 1282608 |   99999 | t154460 |
| 1282609 |   99999 | t154461 |
| 1282610 |   99999 | t154462 |
+---------+---------+---------+
5 rows in set (1.17 sec)
复制代码

发现竟然需要1.17s的时间,user_id不是有索引吗,而且我只获取5条数据,我通过explain也看到用了user_id索引。

mysql> explain select * from message where user_id=99999 limit 700000,5;
+----+-------------+---------+------------+------+---------------+---------+---------+-------+--------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key     | key_len | ref   | rows   | filtered | Extra |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+--------+----------+-------+
|  1 | SIMPLE      | message | NULL       | ref  | user_id       | user_id | 4       | const | 745650 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+--------+----------+-------+
复制代码

经过分析发现了一些端倪,于是我又这样执行了一下:

mysql> select a.* from message a join (select id from message where user_id=99999 limit 700000,5) b on a.id=b.id;
+---------+---------+---------+
| id      | user_id | name    |
+---------+---------+---------+
| 1282606 |   99999 | t154458 |
| 1282607 |   99999 | t154459 |
| 1282608 |   99999 | t154460 |
| 1282609 |   99999 | t154461 |
| 1282610 |   99999 | t154462 |
+---------+---------+---------+
5 rows in set (0.14 sec)
复制代码

发现只需要0.14s,比第一种节约了将近1s的时间。

结论:

首先因为你查询的是*,这意味着你要获取所有字段,那么就算你用的是user_id索引,最终也要回表去查。所以对一条数据来说,总的消耗就是user_id索引的查询时间+主键id索引的查询时间,其次因为你用了limit 70000,5,因为user_id=99999的数据非常多,通过limit的话,就要过滤70w的数据,所以(user_id索引的查询时间+主键id索引的查询时间)乘以70w这整个消耗就是浪费的。对于第二条sql来说,它先是用子查询来获取主键id:

select id from message where user_id=99999 limit 700000,5
复制代码

我们知道普通索引除了含有自身的key之外还包含主键id,那么对于这条sql就不用回表,它的总体浪费的消耗就是user_id索引的查询时间乘以70w,最终通过子查询获取到的5个id,只需要消耗5乘以主键id索引的查询时间就可以得到所需数据。整体看下来,第二条sql比第一条sql节约了主键id索引的查询时间乘以70w的消耗,所以第一条要慢很多。

联合索引

多个字段在一起建立个索引叫联合索引,一般联合索引的目的就是通过多个字段可以确定一条数据。比如一个姓名不能定位到一个人,因为重名的有很多,但是姓名+家庭地址就可以定位一个人。这时姓名和家庭地址可以在一起建立一个唯一索引。注意unique key(姓名,家庭地址) 和unique key(家庭地址,姓名)是不一样的。常见的问题就是假设现在有(a,b,c)联合索引,以下查询是否能用到索引:

  • 问题1
select * from xx where a=1 and b=2 and c=3
复制代码

这是最典型的联合索引的最左原则,这个是能用到索引的。

  • 问题2
select * from xx where and b=2 and c=3
复制代码

这个不符合最左原则,所以用不到索引。

  • 问题3
select * from xx where and b=2 and a=1
复制代码

这个是可以用到索引的,联合索引和查询字段的顺序没关系,和建立索引的字段的顺序有关系。

  • 问题4
select * from xx where and a=1 and c=3
复制代码

这个a是可以用到索引的,c用不到索引。

数字隐式转换问题

假设现在有这样一张表:

CREATE TABLE `user` (
  `id` int(1) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB;
复制代码

其中user_id这个字段是个字符串类型且还有索引。这时候要查下user_id是100000的那条数据信息。于是我们写出了以下的sql:

select * from user where user_id=100000;
复制代码

如果不出意外的话,在你的表已经非常大的情况下,你的sql会很慢。

explain select * from user where user_id=100000;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
|  1 | SIMPLE      | user3 | NULL       | index | user_id       | user_id | 1023    | NULL | 315384 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
复制代码

重点:发现rows展示的数据竟然是要全表扫描。明明user_id是有索引的,为什么还会全表扫?造成这个问题的原因是user_id是字符串,而你给的值是整型(user_id没加单引号),在mysql中,字符串和数字做比较的话,是将字符串转换成数字再进行比较的,也就是我们的sql相当于:

select * from user where CAST(user_id AS signed int)=100000; 
复制代码

当mysql的索引字段做了函数操作时,优化器会放弃走索引。因为通过函数的话,索引值的有序性大概率会被破坏,这时候没必须要走索引了。知道原因后,我们把user_id加上单引号再试试:

explain select * from user where user_id='100000';
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | user3 | NULL       | ref  | user_id       | user_id | 1023    | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
复制代码

这时候会发现rows的值是1。

cpu暴涨如何排查

  • 通过top命令确认cpu占用情况。
  • 通过show processlist来查看mysql线程运行情况。
  • 通过show OPEN TABLES where In_use > 0来查看表锁情况。
  • 通过mysql的error log来查看是否有错误。
  • 通过show engine innodb status\G;来查看是否有死锁。
  • 通过慢查询日志检查是否有慢查询。
  • 通过iostat来查看磁盘io情况。

datetime和timestamp区别

  • 空间
    • datetime占用8个字节。
    • timestamp占用4个字节。
  • 表示范围
    • datetime:1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.999999
    • timestamp:1970-01-01 00:00:01.000000 ~ 2038-01-19 03:14:07.999999

总结:

datetime占用更大的空间,但是可以表示更久远的时间,timestamp占用更小的空间,最大只能支持到2038年。

通过limit 1来优化查询

当我们要统计一下学校里是否有来自上海的学生,于是我们这样查询:

select * from student where from="shanghai";
复制代码

这条sql会检索出所有来自上海的学生数据,而我们只需要知道有没有来自上海的学生,所以可以通过limit 1优化:

select * from student where from="shanghai" limit 1;
复制代码

这样的话,在检索出一条数据后就立马停止检索,大大降低开销。 需要注意的是,如果from是唯一索引的话,加不加limit 1是一样的。

分表后的ID怎么保证唯一性?

因为我们主键一般都是自增的,分表后不同表的主键肯定存在相同的,就冲突了,如何解决?

  • 设定步长,比如10张表我们分别设定1-10的基础步长,这样不同的表就不会存在相同的主键id了。
SET auto_increment_offset=1; # 从1开始
SET auto_increment_increment=10; # 每次以10增长
1 11 21 ... # 第一张表
2 12 22 ... # 第二张表
...
10 20 30 ... # 第十张表
复制代码

这种方式适合M-M架构。

  • 分布式ID,现在有很多开源的分布式ID算法,如雪花算法。
  • 分表后不依赖主键ID去查询某些数据,而是根据自己生成的唯一条件去查询,比如订单号。

为什么要用NOT NULL

对于一个字段来说,你可以指定默认值NULL,也可以指定默认值为NOT NULL,而我的建议是最好设置NOT NULL,理由主要是以下:

  • NULL值在mysql中是占用空间的,而空值是不占用空间的。

NULL columns require additional space in the row to record whether their values are NULL.

mysql> select length(NULL), length(''), length('1');
+--------------+------------+-------------+
| length(NULL) | length('') | length('1') |
+--------------+------------+-------------+
|         NULL |          0 |           1 |
+--------------+------------+-------------+
复制代码
  • 对于字段允许null时要注意判断条件:
    1. null值过滤应该是:
select * from xx where name is not null
复制代码
   2. 空值的过滤:
复制代码
select * from xx where name!=""
复制代码
  • count是不会统计null值的列的:
mysql> select * from user;
+----+------+
| id | name |
+----+------+
|  2 | NULL |
|  1 | tom  |
+----+------+
2 rows in set (0.00 sec)

mysql> select count(name) from user;
+-------------+
| count(name) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)
复制代码
  • 排序的时候(从小到大)null值会排在最前面:
mysql> select * from user order by sort asc;
+----+------+
| id | sort |
+----+------+
|  3 | NULL |
|  4 | NULL |
|  1 |    1 |
|  2 |    2 |
+----+------+
复制代码

mysql认为null小于任何值。

唯一索引字段能为NULL吗

是可以的。虽然唯一索引限制了每个值的唯一性,但是对null的话就束手无策,null在mysql中是一个特殊的存在,一般还是推荐NOT NULL。

CREATE TABLE `user` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;
复制代码
insert into user (user_id) values (1),(null),(null);
mysql> select * from user order by user_id;
+----+---------+
| id | user_id |
+----+---------+
|  2 | NULL    |
|  3 | NULL    |
|  1 | 1       |
+----+---------+
复制代码

可以发现两个null值是插入成功了。

线上1亿数据的表加字段怎么处理

  • 尽量选择低峰期处理
  • mysql5.7支持在线DDL,主要的算法有:
    • ALGORITHM=INPLACE:需要rebuild表(注意:添加列需要rebuild,重命名列、添加索引不需要rebuild),期间仍可以DML,且保持良好的并发性能。
    • ALGORITHM=COPY:需要rebuild表,不允许并发DML写操作,可读。

rebuild:涉及表的重建,前提得保证有足够的磁盘空间。rebuild会在原表路径下创建新的.frm和.ibd文件,IO的消耗会较多。并且rebuild期间会申请row log空间记录DDL执行期间的DML操作,这部分操作会在DDL完成后同步到新的表空间中。

no-rebuild:不涉及表的重建,除添加索引,会产生部分二级索引的写入操作外,其余操作均只修改元数据项,即只在原表路径下产生.frm文件,不会申请row log,不会消耗过多的IO,通常来说速度很快。
复制延迟:在主从架构下,主一边执行DDL,一边执行DML,如果slave是单个sql线程按顺序从relay log中取命令执行,这个期间的DML,在slave上必须等待slave的DDL也执行完毕之后,才能同步。所以在IO压力比较的时候,可能会造成复制延迟。

  • 生产环境推荐使用pt-osc/gh-ost等第三方工具进行在线加列。
  • mysql8.0可以通过instant方式完成快速加列,只需要修改metadata信息,代价小,秒级完成。但是得满足不是压缩表不是data dictionary tablespace不是全文索引不是临时表新增列在最后位置

count(1)、count(*)、count(column)的区别

业务中经常要统计某些信息,这离不开mysql的count函数,count(x)中的x可以是多种多样的,其实他们在某些情况下是没区别的,某些情况下又是有区别的。

  • myisam引擎的count(*)最快,因为它不需要实时统计,它是保存起来的,但是前提是不能加where条件。
  • count(1)和count(*)其实没什么区别,count(*)中的*也并不是所想象的那样统计所有的数据,看个例子:
CREATE TABLE `user_info` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL DEFAULT '0',
   PRIMARY KEY (`id`)
) ENGINE=InnoDB;
复制代码

创建一个只有主键索引的表。

#count(*)
mysql> explain select count(*) from user_info;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user_info | NULL       | index | NULL          | PRIMARY | 4       | NULL |    1 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
#count(1)
mysql> explain select count(1) from user_info1;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user_info | NULL       | index | NULL          | PRIMARY | 4       | NULL |    1 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
复制代码

通过explain发现两者都是用的主键索引,没有任何区别。所以count(1)和coun(*)都是通过主键索引来统计的?

  • count(*)和count(1)的覆盖索引优化:
CREATE TABLE `user_info` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY (`user_id`)
) ENGINE=InnoDB;
复制代码

我们加了个user_id索引:

#count(*)
explain select count(*) from user_info;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user_info | NULL       | index | NULL          | user_id | 4       | NULL |    1 |   100.00 | Using index |

#count(1)
explain select count(1) from user_info;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user_info | NULL       | index | NULL          | user_id | 4       | NULL |    1 |   100.00 | Using index |
复制代码

发现竟然用了user_id索引,用了覆盖索引。这其实是因为主键索引是聚集索引(除了KEY之外还有如事务ID、回滚指针等其他信息),单个索引页能存的数量行数肯定是小于user_id这种普通索引,所以同样大小的索引页,user_id可以存下更多的行数,总体来说检索的更快。

  • count(column):如果列的值允许为NULL,那么是不会统计NULL值的,上面介绍过。
mysql> select * from user;
+----+------+---------------------+
| id | name | ctime               |
+----+------+---------------------+
|  1 | tom  | 2021-08-27 08:45:50 |
|  2 | NULL | 2021-08-27 08:45:50 |
|  3 | NULL | 2021-08-27 08:46:18 |
+----+------+---------------------+
3 rows in set (0.00 sec)

mysql> select count(name) from user;
+-------------+
| count(name) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)
复制代码
  • count(1)、count(2)...count(10086)没什么区别。

左连接、右连接、内连接区别

  • 左连接:以左边为主,右边的没数据的补null。
  • 右连接:以右边为主,左边没数据的补null。
  • 内连接:两张表的交集。

注意:使用小表驱动大表。

小心 or 不使用索引

CREATE TABLE `user_info` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11),
  `name` varchar(10),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
复制代码

假设user_info表只有一个主键索引,这时我们要查id=1 或者 user_id=2的数据:

mysql> explain select * from user_info where id=1 or user_id=2;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user_info5 | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |    3 |    66.67 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
复制代码

type竟然是all(全表扫描),解决方式就是给user_id也加个索引:

alter table user_info add index user_id(`user_id`)
mysql> explain select * from user_info where id=1 or user_id=2;
+----+-------------+------------+------------+-------------+-----------------+-----------------+---------+------+------+----------+-------------------------------------------+
| id | select_type | table      | partitions | type        | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                                     |
+----+-------------+------------+------------+-------------+-----------------+-----------------+---------+------+------+----------+-------------------------------------------+
|  1 | SIMPLE      | user_info6 | NULL       | index_merge | PRIMARY,user_id | PRIMARY,user_id | 4,5     | NULL |    2 |   100.00 | Using union(PRIMARY,user_id); Using where |
+----+-------------+------------+------------+-------------+-----------------+-----------------+---------+------+------+----------+-------------------------------------------+
复制代码

为什么像性别这种字段不适合加索引

一般一个字段需不需要建立索引除了看是不是经常在where中使用,还要看它的重复度,重复度越高的字段不适合建立索引,比如性别(只有男女)。我们知道索引分为聚集索引和非聚集索引,一整条行记录是和聚集索引绑定的,非聚集索引除了自身的值外还保存个主键id,这样当我们通过非聚集索引需要获取额外的信息的时候,那就得通过主键id去聚集索引再查一遍,俗称回表。

假设现在表里有100w的数据,男女各一半,现在要获取所有男的姓名,如果走了sex索引,那么首先通过sex就要过滤50w的数据,然后这50w的数据还得回到主键索引里面去找,那么整个IO次数大概等于 (sex树的高度+id树的高度)* 50w,关键是这还不一定有全表扫的效率高,所以不推荐sex字段加索引。

总结:

  • 重复度大的字段就算加了索引,效率也不一定高。
  • 索引还占空间,每次数据变更的时候还得维护索引。

复制那些事

传统的复制(file+pos)

  1. master开启二进制日志记录所有的变更。
  2. slave开启一个IO线程,通过mysql协议,对master发起请求。
  3. master开启一个线程(dump binlog,有几个slave就有几个dump binlog线程),检查自己的binlog,通过slave请求传的位置把对应的位置后的变更发给slave,如果没有带位置,那么就从头开始一个一个发给slave。
  4. slave把收到master传过来的变更记录到自己的中继日志relay log中,并记录对应的binlog的位置。
  5. slave启动另一个线程从relay log中重放变更。

基于GTID的复制

传统的复制缺点:基于file(binlog)+pos(复制偏移量)来进行复制的的模式主要存在主发生故障切换一个从为主的时候,别的从无法通过file+pos来复制新的主。于是GTID模式的复制出现了:

概念:

GTID (Global Transaction ID) 是对于一个已提交事务的编号,MySQL5.6开始支持,它是一个全局唯一的编号。GTID 实际上是由UUID+TID 组成的。其中 UUID是MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量,并且随着事务提交单调递增。 下面是一个GTID的具体形式:3E11FA47-71CA-11E1-9E33-C80AA9429562:23,冒号分割前边为uuid,后边为TID。

工作原理:
  1. 当一个事务在master执行并提交时,产生GTID,并记录到binlog中。
  2. slave收到变更后,读取到GTID后,把它设置到gtid_next变量,即下一个要执行的GTID值。
  3. sql线程从relay log中获取GTID,然后对比slave端的binlog是否有该GTID,如果有记录,说明该GTID的事务已经执行,slave会忽略。如果没有记录,slave就会执行该GTID事务,并记录该GTID到自身的binlog。

复制方式

复制是基于binlog来完成的,binlog的完整性直接关系到数据的完整性。如果你的binlog没有写入到文件系统,且此时数据库正好挂了,那么这块数据是丢失的。如果你每次事务提交的时候立马把binglog刷入到文件系统,IO的压力又很大。于是mysql给出个选项,让binlog的同步策略交给我们自己,根据业务场景决定选择什么样的同步方式。

mysql> show variables like 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 1     |
+---------------+-------+
复制代码
  • sync_binlog=0:表示MySQL不控制binlog的刷新,由文件系统自己根据情况刷入。这时候的性能是最好的,但是风险也是最大的。因为一旦发生故障,在binlog_cache中的所有binlog信息都会被丢失。
  • sync_binlog=1:表示每次事务提交,MySQL都会把binlog刷入文件系统,是最安全但是性能损耗最大的设置,做多丢失一个事务的数据。
  • sync_binlog=N:如果N不等于0或者1,刷新方式同sync_binlog=1类似,只不过此时会延长刷新频率至N次后。

异步复制:

mysql默认的复制模式就是异步方式,即master同步binlog给slave后,不会关心slave是不是收到。

全同步复制:

由于普通异步模式会导致出现slave丢失数据而master不知道的情况,进而引发主从不一致。为了解决这个问题,master得知道slave同步数据的情况,全同步复制就是master收到所有slave的ack,才执行接下来的同步。

半同步复制:

全同步复制的缺点就是慢,万一某台slave的实例因为网络问题延迟回复了ack,那么所有的slave都要等他。为了解决这个问题,于是可以做个折中,只要master至少收到一个slave的ack,那么就认为是成功的。

多线程复制:

slave上的relay log回放处理,之前是在一个线程上处理的,然而master是可以并发的,并发情况下,slave还通过一个IO线程来回放是不是显得力不从心?

  • 在MYSQL5.6版本中,多线程复制是数据库级别的,将多个数据库下的事务按照数据库拆分到多个线程上执行,保证数据库级别的事务一致性。但是实际应用不多,大多数还是一库多表的场景。
  • 在MYSQL5.7版本后,出现了基于逻辑时钟的方式,可以在一个数据库上并发执行relay log的回放(比如update x表和update y表,它们俩没什么事务冲突的情况下就可以并发执行)。

聚集索引与非聚集索引的区别

  • 聚集索引一张表只能有一个,它的叶子节点存储整行数据(叶子节点即为数据页),在很多情况下,优化器倾向于使用聚集索引,因为它的叶子节点存储整行数据,并且是逻辑有序,这对一些排序、范围查找很友好。
  • 非聚集索引也叫辅助索引,一张表可以有多个非聚集索引。辅助索引的叶子节点包含了列的值和主键的值。一般一页的大小为16k,相比聚集索引,同样一页非聚集索引的叶子节点可以存储更多的行。

为什么使用b+树而不使用b树

  • 因为B树不管是叶子节点还是非叶子节点,都会保存数据,那么对于非叶子节点来说,能保存的数量就更少,这样对于同样的数据量,B树高度可能更高,增大磁盘的IO次数,进而影响查询效率,但是b树的优点是关键字的查询也不用每次深入到叶子节点。
  • 因为B+树的所有数据都存储在叶子节点上,所以每次关键字的查询都要深入到叶子节点上,每一个数据的查询效率差不多,查询更稳定。B+树叶子节点是双向链表,所以排序、范围查找更优秀。
文章分类
后端
文章标签