Category Archive: 数据库

七月 17th, 2018

MySQL默认采用的是MyISAM。
MyISAM不支持事务,而InnoDB支持。InnoDB的AUTOCOMMIT默认是打开的,即每条SQL语句会默认被封装成一个事务,自动提交,这样会影响速度,所以最好是把多条SQL语句显示放在begin和commit之间,组成一个事务去提交。
InnoDB支持数据行锁定,MyISAM不支持行锁定,只支持锁定整个表。即MyISAM同一个表上的读锁和写锁是互斥的,MyISAM并发读写时如果等待队列中既有读请求又有写请求,默认写请求的优先级高,即使读请求先到,所以MyISAM不适合于有大量查询和修改并存的情况,那样查询进程会长时间阻塞。因为MyISAM是锁表,所以某项读操作比较耗时会使其他写进程饿死。
InnoDB支持外键,MyISAM不支持。
InnoDB的主键范围更大,最大是MyISAM的2倍。
InnoDB不支持全文索引,而MyISAM支持。全文索引是指对char、varchar和text中的每个词(停用词除外)建立倒排序索引。MyISAM的全文索引其实没啥用,因为它不支持中文分词,必须由使用者分词后加入空格再写到数据表里,而且少于4个汉字的词会和停用词一样被忽略掉。
MyISAM支持GIS数据,InnoDB不支持。即MyISAM支持以下空间数据对象:Point,Line,Polygon,Surface等。
没有where的count(*)使用MyISAM要比InnoDB快得多。因为MyISAM内置了一个计数器,count(*)时它直接从计数器中读,而InnoDB必须扫描全表。所以在InnoDB上执行count(*)时一般要伴随where,且where中要包含主键以外的索引列。为什么这里特别强调“主键以外”?因为InnoDB中primary index是和raw data存放在一起的,而secondary index则是单独存放,然后有个指针指向primary key。所以只是count(*)的话使用secondary index扫描更快,而primary key则主要在扫描索引同时要返回raw data时的作用较大。

七月 17th, 2018

问题: mysql 语句查询时: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: In aggregated query without GROUP BY, expression #25 of SELECT list contains nonaggregated column ‘nybx.l.PUSH_TIME’; this is incompatible with sql_mode=only_full_group_by错误

解决办法下my.cnf(windows下是my.ini)加以下行
[mysqld]
sql_mode=’STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,
NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’

七月 16th, 2018

前段时间,将线上MySQL数据库升级到了5.7。考虑到可能产生的不兼容性,在升级之前,确实也是战战兢兢,虽然测试环境,开发环境早在半年前就已提前升级。

基于前期的调研和朋友的反馈,与开发相关的主要有两点:

sql_mode

MySQL 5.6中,其默认值为”NO_ENGINE_SU BSTITUTION”,可理解为非严格模式,譬如,对自增主键插入空字符串”,虽然提示warning,但并不影响自增主键的生成。

但在MySQL 5.7中,其就调整为了严格模式,对于上面这个,其不会提示warning,而是直接报错。

分组求最值

分组求最值的某些写法在MySQL5.7中得不到预期结果,这点,相对来说比较隐蔽。

其中,第一点是可控的,毕竟可以调整参数。而第二点,却是不可控的,没有参数与之相关,需要开发Review代码。

下面具体来看看

测试数据

mysql> select * from emp;
+——-+———-+——–+——–+
| empno | ename    | sal    | deptno |
+——-+———-+——–+——–+
|  1001 | emp_1001 | 100.00 |    10 |
|  1002 | emp_1002 | 200.00 |    10 |
|  1003 | emp_1003 | 300.00 |    20 |
|  1004 | emp_1004 | 400.00 |    20 |
|  1005 | emp_1005 | 500.00 |    30 |
|  1006 | emp_1006 | 600.00 |    30 |
+——-+———-+——–+——–+
6 rows in set (0.00 sec)

其中,empno是员工编号,ename是员工姓名,sal是工资,deptno是员工所在部门号。

业务的需求是,求出每个部门中工资最高的员工的相关信息。

在MySQL5.6中,我们可以通过下面这个SQL来实现,

SELECT
deptno,ename,sal
FROM
( SELECT * FROM emp ORDER BY sal DESC ) t
GROUP BY
deptno;

结果如下,可以看到,其确实实现了预期效果。

+——–+———-+——–+
| deptno | ename    | sal    |
+——–+———-+——–+
|    10 | emp_1002 | 200.00 |
|    20 | emp_1004 | 400.00 |
|    30 | emp_1006 | 600.00 |
+——–+———-+——–+

再来看看MySQL5.7的结果,竟然不一样。

+——–+———-+——–+
| deptno | ename    | sal    |
+——–+———-+——–+
|    10 | emp_1001 | 100.00 |
|    20 | emp_1003 | 300.00 |
|    30 | emp_1005 | 500.00 |
+——–+———-+——–+

实际上,在MySQL5.7中,对该SQL进行了改写,改写后的SQL可通过explain(extended) + show warnings查看。

mysql> explain select deptno,ename,sal from (select * from emp order by sal desc) t group by deptno;
+—-+————-+——-+————+——+—————+——+———+——+——+———-+—————–+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+—-+————-+——-+————+——+—————+——+———+——+——+———-+—————–+
|  1 | SIMPLE      | emp  | NULL      | ALL  | NULL          | NULL | NULL    | NULL |    6 |  100.00 | Using temporary |
+—-+————-+——-+————+——+—————+——+———+——+——+———-+—————–+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `slowtech`.`emp`.`deptno` AS `deptno`,`slowtech`.`emp`.`ename` AS `ename`,`slowtech`.`emp`.`sal` AS `sal` from `slowtech`.`emp` group by `slowtech`.`emp`.`deptno`
1 row in set (0.00 sec)

从改写后的SQL来看,其消除了子查询,导致结果未能实现预期效果,官方也证实了这一点,https://bugs.mysql.com/bug.php?id=80131

很多人可能不以为然,认为没人会这样写,但在大名鼎鼎的stackoverflow中,该实现的点赞数就有116个-由此可见其受众之广,仅次于后面提到的“方法二”(点赞数206个)。
https://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results

需要注意的是,该SQL在5.7中是不能直接运行的,其会提示如下错误:

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘t.ename’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

这个与sql_mode有关,在MySQL 5.7中,sql_mode调整为了

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

其中,ONLY_FULL_GROUP_BY与group by语句有关,其要求select列表里只能出现分组列(即group by后面的列)和聚合函数(sum,avg,max等),这也是SQL92的标准。

但在工作中,却经常看到开发写出下面这种SQL。

mysql> select deptno,ename,max(sal) from emp group by deptno;
+——–+———-+———-+
| deptno | ename    | max(sal) |
+——–+———-+———-+
|    10 | emp_1001 |  200.00 |
|    20 | emp_1003 |  400.00 |
|    30 | emp_1005 |  600.00 |
+——–+———-+———-+
3 rows in set (0.01 sec)

实在不明白,这里的ename在业务层有何意义,毕竟,他并不是工资最高的那位员工。

分组求最值,MySQL的实现方式

其实分组求最值是一个很普遍的需求。在工作中,也经常被开发同事问到。 下面具体来看看,MySQL中有哪些实现方式。

方法1

SELECT
e.deptno,
ename,
sal
FROM
emp e,
( SELECT deptno, max( sal ) maxsal FROM emp GROUP BY deptno ) t
WHERE
e.deptno = t.deptno
AND e.sal = t.maxsal;

方法2

SELECT
a.deptno,
a.ename,
a.sal
FROM
emp a
LEFT JOIN emp b ON a.deptno = b.deptno
AND a.sal < b.sal
WHERE
b.sal IS NULL;

这两种实现方式,其实是通用的,不仅适用于MySQL,也适用于其它主流关系型数据库。

方法3
MySQL 8.0推出了分析函数,其也可实现类似功能。

SELECT
deptno,
ename,
sal
FROM
(
SELECT
deptno,
ename,
sal,
LAST_VALUE ( sal ) OVER ( PARTITION BY deptno ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) maxsal
FROM
emp
) a
WHERE
sal = maxsal;

三种实现方式的性能对比

因上面测试案例的数据量太小,三种实现方式的结果都是秒出,仅凭执行计划很难直观地看出实现方式的优劣。

下面换上数据量更大的测试数据,官方示例数据库employees中的dept_emp表,https://github.com/datacharmer/test_db

表的相关信息如下,其中emp_no是员工编号,dept_no是部门编号,from_date是入职日期。

mysql> show create table dept_emp\G
*************************** 1. row ***************************
Table: dept_emp
Create Table: CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
KEY `dept_no` (`dept_no`,`from_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> select count(*) from dept_emp;
+———-+
| count(*) |
+———-+
|  331603 |
+———-+
1 row in set (0.09 sec)

mysql> select * from dept_emp limit 1;
+——–+———+————+————+
| emp_no | dept_no | from_date  | to_date    |
+——–+———+————+————+
|  10001 | d005    | 1986-06-26 | 9999-01-01 |
+——–+———+————+————+
1 row in set (0.00 sec)

方法1

mysql> select d.dept_no,d.emp_no,d.from_date from dept_emp d, (select dept_no,max(from_date) max_hiredate from dept_emp group by dept_no) t where d.dept_no=t.dept_no and d.from_date=t.max_hiredate;

12 rows in set (0.00 sec)

mysql> explain select d.dept_no,d.emp_no,d.from_date from dept_emp d, (select dept_no,max(from_date) max_hiredate from dept_emp group by dept_no) t where d.dept_no=t.dept_no and d.from_date=t.max_hiredate;
+—-+————-+————+————+——-+—————+———+———+————————–+——+———-+———————-
| id | select_type | table      | partitions | type  | possible_keys | key    | key_len | ref                      | rows | filtered | Extra
+—-+————-+————+————+——-+—————+———+———+————————–+——+———-+———————-
|  1 | PRIMARY    | <derived2> | NULL      | ALL  | NULL          | NULL    | NULL    | NULL                    |    9 |  100.00 | Using where
|  1 | PRIMARY    | d          | NULL      | ref  | dept_no      | dept_no | 19      | t.dept_no,t.max_hiredate |    5 |  100.00 | NULL
|  2 | DERIVED    | dept_emp  | NULL      | range | dept_no      | dept_no | 16      | NULL                    |    9 |  100.00 | Using index for group-by
+—-+————-+————+————+——-+—————+———+———+————————–+——+———-+———————-

方法2

mysql> explain select a.dept_no,a.emp_no,a.from_date from dept_emp a left join dept_emp b on a.dept_no=b.dept_no and a.from_date < b.from_date where b.from_date is null;
+—-+————-+——-+————+——+—————+———+———+——————–+——–+———-+————————–+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref                | rows  | filtered | Extra                    |
+—-+————-+——-+————+——+—————+———+———+——————–+——–+———-+————————–+
|  1 | SIMPLE      | a    | NULL      | ALL  | NULL          | NULL    | NULL    | NULL              | 331008 |  100.00 | NULL                    |
|  1 | SIMPLE      | b    | NULL      | ref  | dept_no      | dept_no | 16      | slowtech.a.dept_no |  41376 |    19.00 | Using where; Using index |
+—-+————-+——-+————+——+—————+———+———+——————–+——–+———-+————————–+
2 rows in set, 1 warning (0.00 sec)

方法3

mysql> select dept_no,emp_no,from_date from ( select dept_no,emp_no,from_date,last_value(from_date) over(partition by dept_no order by from_date rows between unbounded preceding and unbounded following) max_hiredate from dept_emp) a where from_date=max_hiredate;

12 rows in set (1.57 sec)

mysql> desc select dept_no,emp_no,from_date from ( select dept_no,emp_no,from_date,last_value(from_date) over(partition by dept_no order by from_date rows between unbounded preceding and unbounded following) max_hiredate from dept_emp) a where from_date=max_hiredate;
+—-+————-+————+————+——+—————+——+———+——+——–+———-+—————-+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra          |
+—-+————-+————+————+——+—————+——+———+——+——–+———-+—————-+
|  1 | PRIMARY    | <derived2> | NULL      | ALL  | NULL          | NULL | NULL    | NULL | 331008 |  100.00 | Using where    |
|  2 | DERIVED    | dept_emp  | NULL      | ALL  | NULL          | NULL | NULL    | NULL | 331008 |  100.00 | Using filesort |
+—-+————-+————+————+——+—————+——+———+——+——–+———-+—————-+
2 rows in set, 2 warnings (0.00 sec)

从执行时间上看,

方法1的时间最短,在有复合索引(deptno, fromdate)的情况下,结果瞬间就出来了,即使在没有索引的情况下,也只消耗了0.75s。

方法2的时间最长,3个小时还是没出结果。同样的数据,同样的SQL,放到Oracle查,也消耗了87分49秒。

方法3的时间比较固定,无论是否存在索引,都维持在1.5s左右,比方法1的耗时要久。

这里,对之前提到的,MySQL 5.7中不再兼容的实现方式也做了个测试,在没有任何索引的情况下,其稳定在0.7s(性能并不弱,怪不得有人使用),而同等情况下,方法1稳定在0.5s(哈,MySQL 5.6竟然比8.0还快)。但与方法1不同的是,其无法通过索引进行优化。

从执行计划上看,

方法1, 先将group by的结果放到临时表中,然后再将该临时表作为驱动表,来和dept_emp表进行关联查询。驱动表小(只有9条记录),关联列又有索引,无怪乎,结果能秒出。

方法2, 两表关联。其犯了SQL优化中的两个大忌。

1. 驱动表太大,其有331603条记录。

2. 被驱动表虽然也有索引,但从执行计划上看,其只使用了复合索引  (dept_no, from_date)中的dept_no,而dept_no的选择率又太低,毕竟只有9个部门。

方法3, 先把分析的结果放到一个临时表中,然后再对该临时表进行处理。其进行了两次全表扫描,一次是针对dept_emp表,一次是针对临时表。

所以,对于分组求最值的需求,建议使用方法1,其不仅符合SQL规范,查询性能上也是最好的,尤其是在联合索引的情况下。

PS:

经大神指点,对之前提到的,MySQL 5.7中不再兼容的实现方式,实际可以通过调整optimizer_switch来加以规避

set optimizer_switch=’derived_merge=off’;

derived_merge是MySQL 5.7引入的,其会试图将Derived Table(派生表,from后面的子查询),视图引用,公用表表达式(Common table expressions)与外层查询进行合并。如,

SELECT *
FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2
ON t1.f2=derived_t2.f1
WHERE t1.f1 > 0;

改写为

SELECT *
FROM t1 JOIN (SELECT DISTINCT f1 FROM t2) AS derived_t2
ON t1.f1=derived_t2.f1;

六月 7th, 2018

MariaDB是一个开源的关系数据库管理系统,向后兼容,二进制替换MySQL。它由MySQL的一些原始开发人员以及社区中的许多人开发。随着CentOS 7的发布,MySQL被MariaDB取代为默认的数据库系统。

如果出于任何原因需要安装MySQL,请查看如何在CentOS 7教程上安装MySQL。如果您的应用程序没有任何特定要求,您应该坚持使用MariaDB,这是CentOS 7中的默认数据库系统。

在本教程中,我们将向您展示如何使用官方MariaDB存储库在CentOS 7上安装MariaDB的最新版本。在默认的CentOS仓库中提供的MariaDB服务器版本是5.5版本,不是最新的MariaDB稳定版本。

安装MariaDB

在撰写本文时,MariaDB的最新版本是10.3版本。

创建一个名为MariaDB.repo的存储库文件并添加以下内容:

/etc/yum.repos.d/MariaDB.repo

# MariaDB 10.3 CentOS repository list – created 2018-05-27 07:02 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.3/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

如果您需要安装任何其他版本的MariaDB,请在此[页面](https://downloads.mariadb.org/mariadb/repositories/)上为您需要的MariaDB版本生成一个存储库。

我们将使用yum安装MariaDB服务器和客户端软件包,与其他CentOS软件包相同,方法是键入以下命令:

sudo yum install MariaDB-server MariaDB-client

Yum可能会提示您导入MariaDB GPG密钥:

Retrieving key from https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
Importing GPG key 0x1BB943DB:
Userid    : “MariaDB Package Signing Key <package-signing-key@mariadb.org>”
Fingerprint: 1993 69e5 404b d5fc 7d2f e43b cbcb 082a 1bb9 43db
From      : https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

键入y并按Enter键。

安装完成后,启用并启动MariaDB服务:

sudo systemctl enable mariadb
sudo systemctl start mariadb

一旦MySQL服务启动,我们可以通过输入以下内容来检查它的状态:

sudo systemctl status mariadb

输出示例:

● mariadb.service – MariaDB 10.3.7 database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─migrated-from-my.cnf-settings.conf
Active: inactive (dead)
Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/

并打印MariaDB服务器版本,其中包含:

mysql -V

mysql  Ver 15.1 Distrib 10.3.7-MariaDB, for Linux (x86_64) using readline 5.1

保护MariaDB安全

运行mysql_secure_installation命令来提高MariaDB安装的安全性:

sudo mysql_secure_installation

脚本会提示您设置root用户密码,删除匿名用户,限制root用户对本地计算机的访问权限,并删除测试数据库。 所有步骤都有详细说明,建议对所有问题回答“是”(是)。

从命令行连接到MariaDB

要通过终端连接到MariaDB服务器,我们将使用MariaDB客户端。

您可以通过键入以root用户身份登录到MariaDB服务器:

mysql -u root -p

系统将提示您输入在运行mysql_secure_installation脚本时以前设置的root密码。

一旦你输入密码,你将会看到MariaDB shell,如下所示:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.7-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement

六月 7th, 2018

自增主键没有持久化是个比较早的bug,这点从其在官方bug网站的id号也可看出(https://bugs.mysql.com/bug.php?id=199)。由Peter Zaitsev(现Percona CEO)于2003年提出。历史悠久且臭名昭著。

首先,直观的重现下。

mysql> create table t1(id int auto_increment primary key);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(null),(null),(null);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+—-+
| id |
+—-+
|  1 |
|  2 |
|  3 |
+—-+
3 rows in set (0.00 sec)

mysql> delete from t1 where id=3;
Query OK, 1 row affected (0.36 sec)

mysql> insert into t1 values(null);
Query OK, 1 row affected (0.35 sec)

mysql> select * from t1;
+—-+
| id |
+—-+
|  1 |
|  2 |
|  4 |
+—-+
3 rows in set (0.01 sec)

虽然id为3的记录删除了,但再次插入null值时,并没有重用被删除的3,而是分配了4。

删除id为4的记录,重启数据库,重新插入一个null值。

mysql> delete from t1 where id=4;
# service mysqld restart
mysql> insert into t1 values(null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+—-+
| id |
+—-+
|  1 |
|  2 |
|  3 |
+—-+
3 rows in set (0.00 sec)

可以看到,新插入的null值分配的是3,按照重启前的操作逻辑,此处应该分配5啊。

这就是自增主键没有持久化的bug。究其原因,在于自增主键的分配,是由InnoDB数据字典内部一个计数器来决定的,而该计数器只在内存中维护,并不会持久化到磁盘中。当数据库重启时,该计数器会通过下面这种方式初始化。

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

MySQL 8.0的解决思路

将自增主键的计数器持久化到redo log中。每次计数器发生改变,都会将其写入到redo log中。如果数据库发生重启,InnoDB会根据redo log中的计数器信息来初始化其内存值。为了尽量减小对系统性能的影响,计数器写入到redo log中,并不会马上刷新。具体可参考:https://dev.mysql.com/worklog/task/?id=6204

因自增主键没有持久化而出现问题的常见场景:

1. 业务将自增主键作为业务主键,同时,业务上又要求主键不能重复。

2. 数据会被归档。在归档的过程中有可能会产生主键冲突。

所以,强烈建议不要使用自增主键作为业务主键。刨除这两个场景,其实,自增主键没有持久化的问题并不是很大,远没有想象中的”臭名昭著“。

最后,给出一个归档场景下的解决方案,

创建一个存储过程,根据table2(归档表)自增主键的最大值来初始化table1(在线表)。这个存储过程可放到init_file参数指定的文件中,该文件中的SQL会在数据库启动时执行。

DELIMITER ;;
CREATE PROCEDURE `auto_increment_fromtable2`(IN table1 VARCHAR(255), IN table2 VARCHAR(255))
BEGIN
set @qry = concat(‘SELECT @max1 := (`id` + 1) FROM `’,table1,’` ORDER BY `id` DESC LIMIT 1;’);
prepare stmt from @qry;
execute stmt;
deallocate prepare stmt;
set @qry = concat(‘SELECT @max2 := (`id` + 1) FROM `’,table2,’` ORDER BY `id` DESC LIMIT 1;’);
prepare stmt from @qry;
execute stmt;
deallocate prepare stmt;
IF @max1 < @max2 THEN
set @qry = concat(‘alter table `’,table1,’` auto_increment=’,@max2);prepare stmt from @qry;execute stmt;deallocate prepare stmt;
SELECT ‘updated’ as `status`;
else
SELECT ‘no update needed’ as `status`;
END IF;
END ;;
DELIMITER ;

TAGS:
六月 6th, 2018

Oracle已经发布了他们的开源关系数据库管理系统MySQL 8。这个版本引入了许多改进,最受关注的可能是基于文档的存储,开发人员可以在同一个数据库中使用传统关系数据和“NoSQL”文档数据。该版本还提升了性能,增强了安全性,并改变了默认字符集以促进“移动优先”开发。

MySQL在MySQL 5.7中引入了对JSON的支持,现在在8.0里带来了MySQL文档存储,开发人员可以将无模式JSON文档集合与关系表放在一起使用。MySQL文档存储由一系列技术组成,一个新的客户端协议、X协议以及让MySQL服务器能够使用X协议的X插件。新的X DevAPI是一组异步API,用于在X协议上执行CRUD和SQL操作。MySQL连接器是一组驱动程序,开发人员可以基于驱动程序使用Java、Python、Node、.Net和C ++等主流开发语言来调用API和文档存储。文档存储中还有一个MySQL Shell,提供了交互式的Javascript、Python或SQL接口来支持MySQL服务器的开发和管理。

Geir Høydalsvik自2008年以来一直在MySQL数据库团队中工作,他告诉InfoQ:“文档存储和关系数据库的结合是一个关键特性。用户现在可以拥有一个坚实的数据库系统,可以同时服务于SQL和NoSQL”。

MySQL并不是开源领域中第一个支持关系数据和基于文档数据的数据库,比如PostgreSQL已经推出该特性一段时间了。

与之前的版本相比,MySQL 8.0还带来了显着的性能改进。发行说明指出,MySQL 8.0速度是5.7的两倍。基准测试数据显示,在以每秒查询数量作为衡量手段的情况下,随着用户数量的增加,性能出现差异。基准测试数据还显示,MySQL 8实现了每秒高达180万次查询的新纪录。数据字典元数据的整合提高了可靠性,这些元数据之前以多种文件格式进行存储,并存储在多个位置,现在则存储在一组SQL表中,并使用默认的InnoDB存储引擎。

MySQL 8带来了多项安全性改进,其中之一引入了SQL角色。与大多数应用程序一样,MySQL 8中的角色代表一个指定的授权集合。现在可以在单个会话中创建、授予、删除和应用MySQL角色。数据库还提供了一个新的SQL函数ROLES_GRAPHML(),它返回一个代表角色子图的graphml文档。另一个安全增强是对密码重用的限制。MySQL已经支持密码过期策略,该策略强制要求用户在一段时间之后更改密码,而新版本可以控制用户设置的密码是否有效。这限制了密码重用,从而强制用户在每次更改密码时提供新的增强密码。

默认字符集已从latin-1改为utf8mb4。在移动世界中,emojis和各种各样的字符集需要共存,Unicode/UTF-8编码已经变得无处不在,所以做出了这一变化。由于性能原因,以前的utf8mb3已被弃用。

移动应用的特征之一是使用用户的位置来提供内容,MySQL 8.0基于现有的GIS支持,引入了地理和空间参考系统(SRS)。

8.0版还带来了一些SQL增强功能。其中之一是引入公共表表达式(CTE),这是ANSI SQL 99(又名“SQL 3”)规范的一部分。它们是带有名称的临时结果集,存在于单个语句的作用域内,可以在语句的其他部分进行引用。还增加了一些窗口函数,用于降低代码复杂度。

发布白皮书中可以找到有关MySQL 8的全部细节。

查看英文原文MySQL Version 8 Adds Document Store, Performance and Security Improvements

六月 3rd, 2018

如果刚接触Oracle的话,教科书上一下用这个用户名登陆,一下子用那个用户名登陆,他们有什么区别呢?很想搞清楚的是我们该怎么选择用哪个用户名登录呢?

答:主要是为了区别权限的作用。!

system 和 sys 是DBA 系统管理员身份,SCOTT是普通身份。
当你想要修改database 参数,调整期性能,查看隐含参数等操作的时候,就需要system 或 sys 身份登录,以便有修改权限。
普通身份登录,就相当于你只能用数据库,而不能用它来调整oracle的性能。普通身份所拥有的权限(例如:Select,Creat 等)都是 system 或 sys 赋予他的权限。

总之,系统用户可以做一切活动,而普通用户只能用它所具有的权限。

所以一般用SCOTT访问SQLPLUS是数据库 以免权限太高 造成误删等情况

四月 28th, 2018

Linux系统查看当前时间的命令:

  一、查看和修改Linux的时区

1. 查看当前时区

命令 : “date -R”

2. 修改设置Linux服务器时区

方法 A

命令 : “tzselect”

方法 B 仅限于RedHat Linux 和 CentOS

命令 : “timeconfig”

方法 C 适用于Debian

命令 : “dpkg-reconfigure tzdata”

3. 复制相应的时区文件,替换系统时区文件;或者创建链接文件

cp /usr/share/zoneinfo/$主时区/$次时区 /etc/localtime

例如:在设置中国时区使用亚洲/上海(+8)

cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime

二、查看和修改Linux的时间

1. 查看时间和日期

命令 : “date”

2.设置时间和日期

例如:将系统日期设定成2009年11月3日的命令

命令 : “date -s 11/03/2009”

将系统时间设定成下午5点55分55秒的命令

命令 : “date -s 17:55:55”

3. 将当前时间和日期写入BIOS,避免重启后失效

命令 : “hwclock -w”

注:

date

不加参数可以直接看到当前日期时间

cal

四月 6th, 2018

1)连接操作命令

  • quit:关闭连接(connection)
  • auth:简单密码认证
  • help cmd: 查看cmd帮助,例如:help quit

2)持久化

  • save:将数据同步保存到磁盘
  • bgsave:将数据异步保存到磁盘
  • lastsave:返回上次成功将数据保存到磁盘的Unix时戳
  • shundown:将数据同步保存到磁盘,然后关闭服务

3)远程服务控制

  • info:提供服务器的信息和统计
  • monitor:实时转储收到的请求
  • slaveof:改变复制策略设置
  • config:在运行时配置Redis服务器

4)对value操作的命令

  • exists(key):确认一个key是否存在
  • del(key):删除一个key
  • type(key):返回值的类型
  • keys(pattern):返回满足给定pattern的所有key
  • randomkey:随机返回key空间的一个
  • keyrename(oldname, newname):重命名key
  • dbsize:返回当前数据库中key的数目
  • expire:设定一个key的活动时间(s)
  • ttl:获得一个key的活动时间
  • select(index):按索引查询
  • move(key, dbindex):移动当前数据库中的key到dbindex数据库
  • flushdb:删除当前选择数据库中的所有key
  • flushall:删除所有数据库中的所有key
三月 27th, 2018

在日常的网站维护和管理中,会用到非常多的SQL语句,
熟练使用对网站管理有很多好处,尤其是站群管理的时候。

下面列一些常用的命令做备记。

1、显示数据库 
show databases
显示表
show tables;

 2、创建用户 
创建root用户密码为123

use mysql; 
grant all on *.* to root@'%' identified by '123' with grant option; 
commit;

 3、修改密码 

grant all on *.* to xing@'localhost' identified by '123456' with grant option; 
update user set password = password('newpwd') where user = 'xing' and host='localhost'; 
flush privileges;

 4、创建数据库testdb:

create database testdb;

 5、预防性创建数据库: 

create database if not testdb;

 6、创建表: 

use testdb; 
create table table1( 
username varchar(12), 
password varchar(20));

 7、预防性创建表aaa: 

create table if not exists aaa(ss varchar(20));

 8、查看表结构: 

describe table1;

 9、插入数据到表table1: 

insert into table1(username,password) values 
('leizhimin','lavasoft'), 
('hellokitty','hahhahah'); 
commit;

 10、查询表table1: 

select * from table1;

 11、更改数据:

update table1 set password='hehe' where username='hellokitty'; 
commit;

 12、删除数据: 

delete from table1 where username='hellokitty'; 
commit;

13、给表添加一列: 

alter table table1 add column( 
 sex varchar(2) comment '性别', 
 age date not null comment '年龄' 
); 
commit;

14、修改表结构

从查询创建一个表table1:

create table tmp as 
select * from table1;

15、删除表table1: 

drop table if exists table1; 
drop table if exists tmp;

16、备份数据库testdb 

mysqldump -h 192.168.3.143 -u root -p pwd -x --default-character-set=gbk >C:\testdb.sql

17、删除数据库testdb 

drop database testdb;

18、恢复testdb数据库 
首先先建立testdb数据库,然后用下面命令进行本地恢复

mysql -u root -pleizhimin testdb <C:\testdb.sql

这18个MYSQL命令都是管理员在日常维护中经常会用到的,熟练使用这些命令会使你的工作非常轻松