LongLong's Blog

分享IT技术，分享生活感悟，热爱摄影，热爱航天。

MySQL 5.7新特性

21 October 2015

MySQL 5.7终于要发布正式版本了，相比MySQL 5.6，MySQL 5.7除了提升性能外增加了非常多的新功能，具体可以参考官方手册，其中主要有在线DDL（相比5.6增加了索引重命名），空间数据类型索引，原生JSON数据类型，多源复制，多线程复制等。

1. 特性变更

新版本中对老版本的一些特性进行了调整，有些不再支持，有些则不建议再使用，以下只列出一些比较常见的，具体可以参考手册。

1) mysql_install_table

mysql_install_table命令不建议在使用，建议改用mysqld –initialize，运行时会有以下提示

[WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize

使用方式基本没有变化，其命令格式大致如下

mysqld --initialize --user=mysql \
         --basedir=/opt/mysql/mysql \
	     --datadir=/opt/mysql/mysql/data
2) InnoDB不能被禁用

由于系统表已经改用InnoDB，因此不能再禁用InnoDB引擎，–skip-innodb，–disable-innodb等配置将被忽略。

3) YEAR(2)类型将不再支持

YEAR(2)类型不再支持，需要升级为YEAR(4)，如果使用会提示以下错误

ERROR 1818 (HY000): Supports only YEAR or YEAR(4) column.
4) 配置参数变化

参数storage_engine变量替换为default_storage_engine 参数innodb_use_sys_malloc 和innodb_additional_mem_pool_size不再支持

2. 多源复制

多源复制是一个非常好用的特性，可以用在做数据的汇总——将多个MySQL的数据汇总到一个MySQL中，同时也可以利用多源复制提升MySQL复制的性能，在MySQL 5.7之前一只使用Mariadb 10的多源复制特性，其两者在使用和语法上稍有区别，这里只介绍MySQL 5.7中的多源复制语法。具体细节可以参考官方手册

在使用多源复制特性前需要先修改MySQL存储master-info和relay-info的方式，即从文件存储改为表存储，修改配置文件如下

master_info_repository=TABLE
relay_log_info_repository=TABLE

同时也可以在线修改

STOP SLAVE;
SET GLOBAL master_info_repository = 'TABLE';
SET GLOBAL relay_log_info_repository = 'TABLE';

添加一个新的复制源与直接使用CHANGE MASTER TO命令基本相同，只是需要给当前的MASTER使用FOR CHANNEL语句分配一个CHANNEL名字即可，例如以下的CHANGE MASTER TO语句

CHANGE MASTER TO MASTER_HOST='master1', MASTER_USER='rpl', MASTER_PORT=3451, MASTER_PASSWORD='' MASTER_LOG_FILE='master1-bin.000006', MASTER_LOG_POS=628 FOR CHANNEL 'master-1';

#启动全部的复制
START SLAVE;
#启动某一个复制源的复制
START SLAVE FOR CHANNEL 'master-1';

#查看某个复制源的复制状态
SHOW SLAVE STATUS FOR CHANNEL 'master-1';

#通过performance_schema的相关表监控复制状态
SELECT * FROM replication_connection_status;

3. 原生JSON数据类型

MySQL 5.7中提供了对JSON数据类型的支持，并且其不仅是能够对JSON数据的格式进行验证，还可以支持一些类似与MongoDB的操作方式，以及对JSON数据进行索引。

例如建立一个包含JSON数据类型的表

CREATE TABLE JsonData(
	id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
	data json NOT NULL
) ENGINE = InnoDB;

向其中插入几行数据，并进行查询

INSERT INTO JsonData (data) VALUES 
('{"id":1, "name":"Fred"}'), ('{"id":2, "name":"Wilma"}'), 
('{"id":"3", "name":"Barney"}'), ('{"id":"4", "name":"Betty"}');
SELECT * FROM JsonData;

结果如下

+----+-------------------------------+
| id | data                          |
+----+-------------------------------+
|  1 | {"id": 1, "name": "Fred"}     |
|  2 | {"id": 2, "name": "Wilma"}    |
|  3 | {"id": "3", "name": "Barney"} |
|  4 | {"id": "4", "name": "Betty"}  |
+----+-------------------------------+
4 rows in set (0.00 sec)

同时还可以查询JSON字段中的某个属性，例如以下查询

SELECT id, JSON_EXTRACT(data, '$.id') FROM JsonData WHERE JSON_EXTRACT(data, '$.name') = 'Fred';

#5.7.9以后的版本JSON_EXTRACT可以简写成
SELECT id, data->'$.id' FROM JsonData WHERE data->'$.name' = 'Fred';

结果如下

+----+----------------------------+
| id | JSON_EXTRACT(data, '$.id') |
+----+----------------------------+
|  1 | 1                          |
+----+----------------------------+
1 row in set (0.00 sec)

另外可以对JSON数据的进行类似MongoDB中修改操作，如进行以下修改

UPDATE JsonData SET data =  JSON_SET(data, '$.name', 'longlong') WHERE id = 1;
SELECT * FROM JsonData WHERE id = 1;

结果如下

+----+-------------------------------+
| id | data                          |
+----+-------------------------------+
|  1 | {"id": 1, "name": "longlong"} |
+----+-------------------------------+
1 row in set (0.00 sec)

此外还有JSON_INSERT，JSON_REPLACE，JSON_APPEND，JSON_REMOVE等（insert replace set的语意与Memcache的add replace set类似）可以对JSON数据进行修改而不需要从客户端修改后覆盖原来的数据，可以节约很多数据传输，提升操作的速度

另外还可以对JSON的某个属性建立索引，进行检索，对表进行以下的修改，注意只有InnoDB支持对于VIRTUAL列增加索引

ALTER TABLE JsonData ADD name varchar(64) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.name'))) VIRTUAL;
ALTER TABLE JsonData ADD INDEX(name);

表结构变为

CREATE TABLE `JsonData` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data` json NOT NULL,
  `name` varchar(64) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.name'))) VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB

分析以下语句的执行计划

EXPLAIN SELECT * FROM JsonData WHERE name = 'longlong';

结果如下，看到可以使用到索引

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: JsonData
   partitions: NULL
         type: ref
possible_keys: name
          key: name
      key_len: 67
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

4. 直接增加VARCHAR字段的长度

InnoDB类型的表的VARCHAR字段可以增加其最大长度而不需要重建整个表，但也存在一些限制，最大长度小于255的VARCHAR最多能够直接增加到255，最大长度大于等于256则可以直接增大到更大的值，例如以下表

CREATE TABLE `CodeData` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

如果直接增加code字段的长度到64，例如

ALTER TABLE CodeData MODIFY code varchar(64) NOT NULL;

DDL操作会瞬间完成，并会得到以下提示

Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

如果再将code字段的长度增加到256，例如

ALTER TABLE CodeData MODIFY code varchar(256) NOT NULL;

DDL相比之前需要执行一段时间，并得到以下提示

Query OK, 10000 rows affected (0.67 sec)
Records: 10000  Duplicates: 0  Warnings: 0

说明增加到256后，表进行了重建操作，影响的记录数时10000行，而不再是0，而再继续增大其长度到1024

ALTER TABLE CodeData MODIFY code varchar(1024) NOT NULL;

DDL操作会瞬间完成，并会得到以下提示

Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

说明长度超过256后，增加长度再次不需要重建整个表。因此在MySQL 5.7中，VARCHAR的长度如果接近255则最好设定为256，这样如果以后需要增加长度则不需要重建整个表，而代价只是多用一个字节来存储列的长度而已还是比较划算的。

5. 空间数据类型索引

在MySQL 5.7中InnoDB中使用空间数据类型也可以像MyISAM一样建立索引进行查询了。创建如下表

CREATE TABLE GeomData (
	id int NOT NULL AUTO_INCREMENT PRIMARY KEY, 
	g geometry NOT NULL
) ENGINE = InnoDB;

写入一行数据，并查看写入的数据

INSERT INTO GeomData (g) VALUES (Point(1, 2));
SELECT ST_ASTEXT(g) FROM GeomData;

+--------------+
| ST_ASTEXT(g) |
+--------------+
| POINT(1 2)   |
+--------------+
1 row in set (0.00 sec)

随机导入一些数据后，为空间类型列增加一个索引，并分析以下语句的执行计划

ALTER TABLE GeomData ADD SPATIAL INDEX(g);
EXPLAIN SELECT id FROM GeomData WHERE MBREquals(ST_GeomFROMTEXT('POINT(1 2)'), g);

会得到以下结果

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: GeomData
   partitions: NULL
         type: range
possible_keys: g
          key: g
      key_len: 34
          ref: NULL
         rows: 619
     filtered: 100.00
        Extra: Using where

其它空间相关的函数可以参考官方手册

6. 多线程复制

通常情况下MySQL复制只有两个线程，即IO线程和SQL线程，而在MySQL 5.6中可以支持开启多个Worker线程执行复制过来的binlog，但限制是只有不同数据库的binlog才会在多个Worker线程之间并行执行，而在MySQL 5.7中增加了对于同一个时间点的binlog也可以并行执行（应该是基于Group Commit机制）。相关的配置如下

slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=5

增加配置后，可以在Slave上看到启动的Worker线程

|  3 | system user | Connect |    9 | Waiting for an event from Coordinator |
|  4 | system user | Connect |    9 | Waiting for an event from Coordinator |
|  5 | system user | Connect |    9 | Waiting for an event from Coordinator |
|  6 | system user | Connect |    9 | Waiting for an event from Coordinator |
|  8 | system user | Connect |    9 | Waiting for an event from Coordinator |

在并发较高的情况下可以在一定程度上提高复制的性能。

Nginx配置与使用

15 October 2015

经过多年的积累和完善，Nginx已经是和Apache一样成为了一种最为常见的Web服务器，其因其使用基于事件的io模型能够承受大量的连接数，同时只占用很小的内存，灵活的配置文件以及丰富的第三方模块得到了越来越多的应用。

同时目前Nginx + PHP-FPM已经基本上取代了Apache + Mod-PHP成为了PHP运行环境的主流。但存在一个比较常见的误区，就是Nginx + PHP-FPM的性能要远远高于Apache + Mod-PHP，但事实并不是这样，本质上影响性能的是PHP的执行，而且PHP-FPM的运行模型和Apache基本上是类似的——都是prefork的方式，而这才是性能的瓶颈。个人感觉Nginx + PHP-FPM性能好于Apache主要是由于默认安装的Apache加载了大量无用的模块，同时如果没有做动态静态分离，Nginx在处理静态内容时会有很大的优势。

1. Rewrite配置

作为Web服务器，最为常用的配置就是URL的重写，即Rewrite配置，Nginx的Rewrite相对Apache感觉更加简单，同时使用的是PCRE正则书写起来也更加容易一些。这里主要说几个常见的问题

1) MVC入口Rewrite

一般使用过Apache Rewrite，都习惯写成

location / {
	if (!-e $request_filename) {
		rewrite (.*) /index.php?q=$1 last;
	}
}

而如果使用了新版本的Nginx则建议使用try_files语法

location / {
	try_files $uri $uri/ /index.php?q=$uri&$args;
}
2) 整个域名跳转

经常会遇到将整个域名跳转到另外一个域名并且uri不变的情况，一般较为常见的写法是

location / {
	rewrite ^/(.*)$ http://xxx.com/$1 permanent;
}

而较好的写法是不使用正则表达式

location / {
	return 301 $scheme://xxx.com$request_uri;
}
3) break和last的区别

break是当匹配了当前的Rewrite规则后不再执行后面紧跟的Rewrite规则，但会进行执行当前location中的其它配置，而last则是当匹配了当前的Rewrite规则后不再执行后面紧跟的Rewrite规则同时跳出当前的location，根据重写后的uri再进行一次新的location匹配。

例如

location /app/ {
	rewrite ^/app/new/(.*)$ /newapp/$1 break;
	proxy_pass http://backend;
}

当请求匹配了当前的Rewrite规则后，会继续执行后面的proxy_pass指令，而代理请求的request_uri会使用Rewrite以后的值。而如果像如下改了last

location /app/ {
	rewrite ^/app/new/(.*)$ /newapp/$1 last;
	proxy_pass http://backend;
}

则如果匹配了当前的Rewrite规则后，会跳出当前的location，寻找能匹配/newapp/的location，如果没有则会进入location /，特别注意的是如果rewrite后还到当前的location，则使用last特别容易导致死循环，一定要特别注意，例如以下会造成Rewrite的死循环而导致Nginx出现500错误

location /app/ {
	rewrite ^/app/(.*)$ /app/new/$1 last;
	proxy_pass http://backend;
}

会在uri中无限的增加/new/

4) 嵌套if

Nginx的配置是原生不能支持if的嵌套的，但可以通过变通的方法实现类似的效果——将一个变量进行多次赋值，通过最后的结果再进行判断

set $_v "";
if ($request_method = "POST") {
	set $_v "1";
}
if ($cookie_L = "") {
	set $_v "2$_v";
}
if ($v = "21") {
	return 403;
}

2. 作为后段服务的前端

Nginx还有一个很大的用处就是将一些后段服务通过相应的模块之间暴露给前段使用，即将一种其它的协议HTTP化，最为常见的是原声的Memcached模块，例如以下的配置可以使得/message/?mkey=xxxx直接读取出对应的内容，如果mkey是服务端传给客户端的一个加密的字符串，则也可以保证数据的安全性

location /message/ {
	set $memcached_key $arg_mkey;
	memcached_pass memcache;
	error_page 404 = @cache_miss;
}

location @cache_miss {
	internal;
	proxy_pass http://backend;
}

upstream memcache {
	hash $memcached_key consistent;
	server 127.0.0.1:11211;
	server 127.0.0.1:11212;
	server 127.0.0.1:11213;
}

类似的模块还有很多如支持MySQL的Drizzle模块，支持MongoDB的Mongo模块，支持Redis的Redis2和HTTP Redis模块，都可以将后段服务HTTP化，从而省去PHP的操作过程从能得到极高的性能。

3. 使用Lua进行扩展

Nginx的Lua模块是得Nginx能够通过配置就实现各种定制化的功能，例如需要根据多个条件进行判断的Rewrite，对一些请求参数的分析或过滤，其能够通过简单的Lua代码就能够实现一个C模块才能实现的功能，同时也有万全可以接受的性能。这里需要注意的有两点，首先rewrite_by_lua的优先级要低于Nginx原生的Rewrite，因此如下的配置不能得到想要的结果

location / {
    set $a 12; # create and initialize $a
    set $b ''; # create and initialize $b
    rewrite_by_lua 'ngx.var.b = tonumber(ngx.var.a) + 1';
    if ($b = '13') {
       rewrite ^ /bar redirect;
       break;
    }
}

由于最后的原生Rewrite会优先执行，尽管其写在最后面，因此建议书写配置的使用永远将rewrite_by_lua写在最后，避免对配置文件的错误理解，同时如果一定要实现以上的效果，则可以将最后的原生Rewrite改用Lua书写，如下

location / {
    set $a 12; # create and initialize $a
    set $b ''; # create and initialize $b
    rewrite_by_lua '
		ngx.var.b = tonumber(ngx.var.a) + 1;
		if ngx.var.b == 13 then
			ngx.redirect("/bar");
		end
	';
}

此外set_by_lua的代码中不要运行过于复杂的操作，因为其会阻塞Nginx的事件循环，可能会严重影响Nginx的性能。

About

龙_2

低调，淡定，技术流，忠实的爱国主义者，前宝宝树运维工程师。

最新文章

  • Rapsberry Pico中使用MicroPython
  • 使用Python进行CAN总线通信
  • 使用PyTorch实现强化学习
  • 使用Python构建图形界面
  • 使用PyTorch进行数据拟合
  • 使用Selenium实现浏览器爬取网页
  • 基于稀疏网格的窄带拓扑优化
  • 使用PyTorch解决优化问题
  • Windows下的Linux子系统
  • 使用Open Dynamics Engine实现刚体动力学仿真

    • Elsewhere

    1. GitHub
    2. Weibo
    3. Blog