MySQL中group_concat函数详解

函数语法:

group_concat( [DISTINCT]  要连接的字段   [Order BY 排序字段 ASC/DESC]   [Separator ‘分隔符’] )


下面举例说明:

select * from goods;  

+——+——+
| id| price|
+——+——+
|1 | 10|
|1 | 20|
|1 | 20|
|2 | 20|
|3 | 200 |
|3 | 500 |
+——+——+
6 rows in set (0.00 sec)


  1. 以id分组,把price字段的值在同一行打印出来,逗号分隔(默认)

select id, group_concat(price) from goods group by id;  

+——+——————–+
| id| group_concat(price) |
+——+——————–+
|1 | 10,20,20|
|2 | 20 |
|3 | 200,500|
+——+——————–+
3 rows in set (0.00 sec)


2. 以id分组,把price字段的值在一行打印出来,分号分隔 
select id,group_concat(price separator ‘;’) from goods group by id;  

+——+———————————-+
| id| group_concat(price separator ‘;’) |
+——+———————————-+
|1 | 10;20;20 |
|2 | 20|
|3 | 200;500 |
+——+———————————-+
3 rows in set (0.00 sec)


3. 以id分组,把去除重复冗余的price字段的值打印在一行,逗号分隔
select id,group_concat(distinct price) from goods group by id;  

+——+—————————–+
| id| group_concat(distinct price) |
+——+—————————–+
|1 | 10,20|
|2 | 20 |
|3 | 200,500 |
+——+—————————–+
3 rows in set (0.00 sec)


4. 以id分组,把price字段的值打印在一行,逗号分隔,按照price倒序排列
select id,group_concat(price order by price desc) from goods group by id;  

+——+—————————————+
| id| group_concat(price order by price desc) |
+——+—————————————+
|1 | 20,20,10 |
|2 | 20|
|3 | 500,200|
+——+—————————————+
3 rows in set (0.00 sec)

MySQL中order by 排序必知

在开发过程时,我们经常会遇到 order by 排序操作,那么你知道什么时候MySQL才会进行排序操作,什么时候不需要时间排序操作?,下面我们就从一个很小的例子中了解一下排序场景。

表结构如下:

CREATE TABLE t (
   id int(11) unsigned NOT NULL AUTO_INCREMENT,
   city varchar(16) NOT NULL,
   name varchar(16) NOT NULL,
   age int(11) NOT NULL,
   addr varchar(128) DEFAULT NULL,
   PRIMARY KEY (id),
   KEY city (city) USING BTREE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

这里只有一个索引city,我们执行一条SQL语句:

SELECT city, name  FROM t WHERE city='杭州' ORDER BY name LIMIT 1000;

通过Explain命令查看执行情况

发现Extra字段里有”Using filesort”,说明使用了排序,而排序必用到了sort_buffer, 这是由数据库为了专门进行排序操作而分配的一块内存。
这里的Using index conditon是指ICP特性,请参考:https://blog.haohtml.com/archives/18201

下面我们专门来看一下这条语句的执行流程是如何的?在此以前我们需要了解一个概念,就是索引的特性是有序的,系统搜索时,一旦找到最后一条满足条件的记录后就立即停止,后面的记录则不再进行扫描,这样就可以用来解决全表扫描的性能问题。

Continue reading

MySQL中的半同步复制

MySQL当前存在的三种复制模式有:异步模式、半同步模式和组复制模式。注意:MySQL复制模式没有“同步复制”这一项的,文章中只是为了读者方便理解半同步复制的概念才介绍了同步复制概念https://dev.mysql.com/doc/refman/8.0/en/replication-semisync.html

从MySQL5.5开始,MySQL以插件的形式支持半同步复制。

1. 异步复制(Asynchronous replication)

MySQL默认的复制即是异步的,主库在执行完客户端提交的事务后会立即将结果返给给客户端,并不关心从库是否已经接收并处理,这样就会有一个问题,主如果crash掉了,此时主上已经提交的事务可能并没有传到从上,如果此时,强行将从提升为主,可能导致新主上的数据不完整。

异步复制是MySQL最早的也是当前使用最多的复制模式,异步复制提供了一种简单的主-从复制方法,包含一个主库(master)和备库(一个,或者多个) 之间,主库执行并提交了事务,在这之后(因此才称之为异步),这些事务才在从库上重新执行一遍(基于statement)或者变更数据内容(基于 row),主库不检测其从库上的同步情况。在服务器负载高、服务压力大的情况下主从产生延迟一直是其诟病。工作流程简图如下:

而同步复制(Fully synchronous replication,MySQL中没有此复制概念)指当主库执行完一个事务,所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回,所以全同步复制的性能必然会收到严重的影响。

2. 半同步复制(Semisynchronous replication)

介于异步复制和全同步复制之间,主库在执行完客户端提交的事务后不是立刻返回给客户端,而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制,半同步复制提高了数据的安全性,同时它也造成了一定程度的延迟,这个延迟最少是一个TCP/IP往返的时间。所以,半同步复制最好在低延时的网络中使用。

MySQL5.5 的版本在异步同步的基础之上,以插件的形式实现了一个变种的同步方案,称之为半同步复制(semi-sync replication)。这个插件在原生的异步复制上,添加了一个同步的过程:当从库接收到了主库的变更(即事务)时,会通知主库。主库上的操作有两种:接收到这个通知以后才去commit事务接受到之后释放session。这两种方式是由主库上的具体配置决定的。当主库收不到从库的变更通知超时时,由半同步复制自动切换到异步同步,这样就极大了保证了数据的一致性(至少一个从库),但是在性能上有所下降,特别是在网络不稳定的情况下,半同步和同步之间来回切换,对正常的业务是有影响的。其工作流程简图如下:

Continue reading

MySQL中对MVCC的理解总结

一、MVCC简介

MVCC (Multiversion Concurrency Control),即多版本并发控制技术。InnoDB数据库的事务隔离级别就是通过UNDO和MVCC来实现的(ACID特性),旧数据存储在UNDO中,再通过DB_ROLL_PTR 回溯查找历史版本。

二、MVCC原理

1、通过DB_ROLL_PT 回溯查找数据历史版本
2、通过read view判断行记录是否可见

理解这一块之前,我们必须先了解一下row的内部存储格式

字段说明:

  • DB_ROW_ID:长度6个字节。此值由InnoDB自动生成,聚集索引时使用。如果用户未显式指定表主键时,表优先使用第一个非null的唯一索引作为主键.否则使用DB_ROW_ID的值作为主键ID,聚集索引会使用此值。如果指定了表主键的话,则聚集索引使用指定的值。
  • DB_TRX_ID:6个字节的事务ID。标记了最后更新此记录的事务ID,每开起一个新事务,其值自动+1
  • DB_ROLL_PTR:7字节的回滚指针。指向当前记录项的undo log记录,找之前版本的数据需通过此指针。

MySQL中的MVCC原理

首次 insert 记录的DB_ROLL_PTR指针为NULL。修改新值后,记录的 DB_ROLL_PTR 回滚指针指向原始值在Undo Log 日志的位置,也就是说将原值在Unde Log的物理位置存储到原记录的 DB_POLL_PTR 字段。如果事务回滚的话,则从Undo Log 中把原始值读取出来再放到记录中去。如果直接commit的话,则直接保存即可。记录格式参考:https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format.html

InnoDB Undo Log的日志类型
MySQL数据库InnoDB存储引擎的undo log采用了逻辑的日志
InnoDB undo log的格式可以概括为:<操作类型>+<Table ID>+<数据>.

  A. 从表中删除一行记录
TRX_UNDO_DEL_MARK_REC (将主键记入日志)
在删除一条记录时,并不是真正的将数据从数据库中删除,只是标记为已删除.这样做的好处是Undo Log中不用记录整行的信息.在undo时操作也变得很简单.
  B. 向表中插入一行记录
TRX_UNDO_INSERT_REC (仅将主键记入日志)
TRX_UNDO_UPD_DEL_REC (将主键记入日志) 当表中有一条被标记为删除的记录和要插入的数据主键相同时, 实际的操作是更新这个被标记为删除的记录。
  C. 更新表中的一条记录
TRX_UNDO_UPD_EXIST_REC (将主键被更新了的字段内容记入日志)
TRX_UNDO_DEL_MARK_RECTRX_UNDO_INSERT_REC  当更新主键字段时,实际执行的过程是删除旧的记录然后,再插入一条新的记录。

事务隔离级别的区别:

  • RR隔离级别下,在每个事务开始的时候,会将当前系统中的所有的活跃事务拷贝到一个列表中(read view)。
  • RC隔离级别下,在事务中的每个语句开始(select)时,会将当前系统中的所有的活跃事务拷贝到一个列表中(read view)
  • 然后按照以下逻辑判断事务的可见性

MVCC解决了什么问题

  • MVCC使得数据库读不会对数据加锁,普通的SELECT请求不会加锁,提高了数据库的并发处理能力;
  • 借助MVCC,数据库可以实现RC,RR等隔离级别,用户可以查看当前数据的前一个或者前几个历史版本。保证了ACID中的I特性(隔离性)。

查看当前数据库中的活跃事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX

参考

MySQL之ICP、MRR、BKA、BNL

Index Condition Pushdown(ICP)

Index Condition Pushdown (ICP)mysql使用索引从表中检索行数据的一种优化方式。

ICP原理

禁用ICP,存储引擎会通过遍历索引定位基表中的行,然后返回给MySQL Server层,再去为这些数据行进行WHERE后的条件的过滤。

开启ICP,如果部分WHERE条件能使用索引中的字段,MySQL Server 会把这部分下推到存储引擎层,存储引擎通过索引过滤,把满足的行从表中读取出。ICP能减少引擎层访问基表的次数和MySQL Server 访问存储引擎的次数。

ICP的目标是减少从基表中全纪录读取操作的数量,从而降低IO操作

对于InnoDB表,ICP只适用于辅助索引。

ICP标识

当使用ICP优化时,执行计划的Extra列显示 Using index condition 提示

Continue reading

MySQL中的innodb_file_format 配置项解读

一:innodb_file_format参数

在 innodb 1.0.6版本之前,innodb文件格式innodb_file_format只有 Antelope(Antelope 文件格式支持Redundant,Compact两种格式来存放行记录,Redundant是为了兼容之前版本而保留的。在mysql 5.1版本中,默认设置为Compact,用户可以通过 show table status like 'table_name'来查看表使用的行格式row_format)

从innodb 1.0.6开始引入了新的文件格式 Barracuda 在原来的基础上(Antelope)新增了Dynamic和Compressed两种行格式。

二:innodb_file_format如何使用

  一般, innodb_file_format 在配置文件中指定;row_format则在创建数据表时指定:

CREATE TABLE test2 (column1 INT PRIMARY KEY) 
ENGINE=InnoDB ROW_FORMAT=Compressed KEY_BLOCK_SIZE=4; 

三:innodb_file_format = Barracuda

在指定innodb_file_format = Barracuda时,建议配合如下参数一起使用

innodb_file_format_max = Barracuda
innodb_strict_mode = 1
innodb_file_per_table = 1

因为system tablespace使用的是Antelope文件格式,如果不指定innodb_file_per_table = 1(5.6.6是默认开启的),那么innodb表被存储在system tablespace,这时在建表的时候无法使用Dynamic和Compressed两种行格式(如果设置了innodb_strict_mode = 1,那么建表的时候指定Dynamic和Compressed会直接报错。如果没有指定innodb_strict_mode = 1,建表的时候指定Dynamic和Compressed会被忽略,默认设置为Compact)

关于innodb_strict_mode参数介绍:

innodb_strict_mode

Command-Line Format –innodb_strict_mode=#
System Variable Name innodb_strict_mode
Variable Scope Global, Session
Dynamic Variable Yes
Permitted Values Type boolean
Default OFF
When innodb_strict_mode is ON, InnoDB returns errors rather than warnings for certain conditions. The default value is OFF.

Strict mode helps guard against ignored typos and syntax errors in SQL, or other unintended consequences of various combinations of operational modes and SQL statements. When innodb_strict_mode is ON, InnoDB raises error conditions in certain cases, rather than issuing a warning and processing the specified statement (perhaps with unintended behavior). This is analogous to sql_mode in MySQL, which controls what SQL syntax MySQL accepts, and determines whether it silently ignores errors, or validates input syntax and data values.

The innodb_strict_mode setting affects the handling of syntax errors for CREATE TABLE, ALTER TABLE and CREATE INDEX statements.innodb_strict_mode also enables a record size check, so that an INSERT or UPDATE never fails due to the record being too large for the selected page size.

Oracle recommends enabling innodb_strict_mode when using ROW_FORMAT and KEY_BLOCK_SIZE clauses on CREATE TABLE, ALTER TABLE, andCREATE INDEX statements. When innodb_strict_mode is OFF, InnoDB ignores conflicting clauses and creates the table or index, with only a warning in the message log. The resulting table might have different behavior than you intended, such as having no compression when you tried to create a compressed table. When innodb_strict_mode is ON, such problems generate an immediate error and the table or index is not created, avoiding a troubleshooting session later.

You can turn innodb_strict_mode ON or OFF on the command line when you start mysqld, or in the configuration file my.cnf or my.ini. You can also enable or disable innodb_strict_mode at runtime with the statement SET [GLOBAL|SESSION] innodb_strict_mode=mode, where mode is either ONor OFF. Changing the GLOBAL setting requires the SUPER privilege and affects the operation of all clients that subsequently connect. Any client can change theSESSION setting for innodb_strict_mode, and the setting affects only that client.

关于innodb_file_per_table参数介绍:

 innodb_file_per_table

Command-Line Format –innodb_file_per_table
System Variable Name innodb_file_per_table
Variable Scope Global
Dynamic Variable Yes
Permitted Values (<= 5.6.5) Type boolean Default OFF Permitted Values (>= 5.6.6) Type boolean
Default ON
When innodb_file_per_table is enabled (the default in 5.6.6 and higher), InnoDB stores the data and indexes for each newly created table in a separate.ibd file, rather than in the system tablespace. The storage for these InnoDB tables is reclaimed when the tables are dropped or truncated. This setting enables several other InnoDB features, such as table compression. See Section 14.4.4, “InnoDB File-Per-Table Tablespaces” for details about such features as well as advantages and disadvantages of using file-per-table tablespaces.

Be aware that enabling innodb_file_per_table also means that an ALTER TABLE operation will move InnoDB table from the system tablespace to an individual .ibd file in cases where ALTER TABLE recreates the table (ALGORITHM=COPY).

When innodb_file_per_table is disabled, InnoDB stores the data for all tables and indexes in the ibdata files that make up the system tablespace. This setting reduces the performance overhead of filesystem operations for operations such as DROP TABLE or TRUNCATE TABLE. It is most appropriate for a server environment where entire storage devices are devoted to MySQL data. Because the system tablespace never shrinks, and is shared across all databases in an instance, avoid loading huge amounts of temporary data on a space-constrained system when innodb_file_per_table=OFF. Set up a separate instance in such cases, so that you can drop the entire instance to reclaim the space.

By default, innodb_file_per_table is enabled as of MySQL 5.6.6, disabled before that. Consider disabling it if backward compatibility with MySQL 5.5 or 5.1 is a concern. This will prevent ALTER TABLE from moving InnoDB tables from the system tablespace to individual .ibd files.

innodb_file_per_table is dynamic and can be set ON or OFF using SET GLOBAL. You can also set this parameter in the MySQL configuration file (my.cnfor my.ini) but this requires shutting down and restarting the server.

Dynamically changing the value of this parameter requires the SUPER privilege and immediately affects the operation of all connections.

关于各个参数的详细介绍,请参见官方文档

MySQL 5.6新特性MRR

一、什么是MRR 

MMR全称是Multi-Range Read,是MYSQL5.6优化器的一个新特性,在MariaDB5.5也有这个特性。优化的功能在使用二级索引做范围扫描的过程中减少磁盘随机IO和减少主键索引的访问次数。是优化器将随机 IO 转化为顺序 IO 以降低查询过程中 IO 开销的一种手段。(参考:https://blog.csdn.net/caomiao2006/article/details/52205177

二、MRR和没有MRR的区别 

给出一个简单的例子,在innodb表执行下面的查询:

SELECT non_key_column FROM tbl WHERE key_column=x

在没有MRR的情况下,它是这样得到结果的

1.  select key_column, pk_column from tb where key_column=x order  by key_column —>
假设这个结果集是t

2.  for each row in t ;
select non_key_column from tb where pk_column = pk_column_value。(回表)

在有MRR的情况下,它是这样执行的:
1.  select key_column, pk_column from tb where key_column = x  order by key_column —> 假设这个结果集是t
2.  将结果集t放在buffer里面(直到read_rnd_buffer_size这个buffer满了),然后对结果集t按照 pk_column 排序      —> 假设排序好的结果集是t_sort
3.  select non_key_column from tb where pk_column in (select pk_column from t_sort)

两者的区别主要是两点:

1. 没有MRR的情况下,随机IO增加,因为从二级索引里面得到的索引元组是有序,但是他们在主键索引里面却是无序的,所以每次去主键索引里面得到non_key_column的时候都是随机IO。(如果索引覆盖,那也就没必要利用MRR的特性了,直接从索引里面得到所有数据)

2. 没有MRR的情况下,访问主键索引的次数增加。没有MRR的情况下,二级索引里面得到多少行,那么就要去访问多少次主键索引(也不能完全这样说,因为mysql实现了BNL),而有了MRR的时候,次数就大约减少为之前次数t/buffer_size

When MRR is used, the Extra column in EXPLAIN output shows Using MRR.

参考:

https://blog.csdn.net/liang_0609/article/details/44040357

https://dev.mysql.com/doc/refman/8.0/en/mrr-optimization.html

http://www.percona.com/blog/2012/03/21/multi-range-read-mrr-in-mysql-5-6-and-mariadb-5-5/

参考阅读

MYSQL之ICP、MRR、BKA(推荐)

MySQL中select中的for update 的用法

注意: FOR UPDATE 只能用在事务区块(BEGIN/COMMIT)中才有效。

有时候我们会看到一些select语句后面紧跟一句for update,表示手动加锁的意思,这里我们就介绍一下对for update的理解。相对另一种手动加锁方法lock in share mode 的区别见:https://blog.csdn.net/liangzhonglin/article/details/65438777

for update:IX锁(意向排它锁),即在符合条件的rows上都加了排它锁,其他session也就无法在这些记录上添加任何的S锁或X锁。如果不存在一致性非锁定读的话,那么其他session是无法读取和修改这些记录的,但是innodb有非锁定读(快照读并不需要加锁),for update之后并不会阻塞其他session的快照读取操作,除了select …lock in share mode和select … for update这种显示加锁的查询操作。

lock in share mode:是IS锁(意向共享锁),即在符合条件的rows上都加了共享锁,这样的话,其他session可以读取这些记录,也可以继续添加IS锁,但是无法修改这些记录直到你这个加锁的session执行完成(否则直接锁等待超时)。

for update是一个意向排它锁,也就是说对于select … for update 这条语句所在的事务中可以进行任何操作(锁定的是记录,这里指主键id=1的这条记录),但其它事务中只能读取(对这条id=1的记录),不能进行update更新操作。

一般用在并发场景下,如双11的时候商品数量的更新,如果不添加for update的话,则会出现商品数量被多减的bug。

为了更加方便理解,我们举例说明(事务隔离级别为 RR,这里表tb的id为主键)

事务A:

start transaction;
select * from tb where id=1 for update;
update tb set product_num=product_num-1 where id=1;

此时另一个事务B执行同样的程序语句:

start transaction;
// 下面此时会被阻塞,直到事务A提交或者回滚
select * from tb where id=1 for update; 
update tb set product_num=product_num-1 where id=1;

对于其它主键值非1的不存在这种情况,只要两个事务操作的不是同一条记录就可以执行成功。

推荐阅读:https://blog.csdn.net/u011957758/article/details/75212222