/ db

mysql

MariaDb: https://haobing.wang/mariadb/

GUI 客户端工具

常用查询语句

统计重复字段 identity

SELECT
	id,
	user_id,
	CONCAT(lastname, firstname) AS fullname,
	identity,
	COUNT(*) AS count
FROM
	employee
GROUP BY
	identity
HAVING
	count > 1;

左连接,显示所有用户,及其订单(如果用户不存在订单,则 orderid 为 Nul):

SELECT
	employee.id AS employeeid,
	CONCAT(firstname, lastname) AS fullname,
	identity,
	`order`.id AS orderid
FROM
	employee
LEFT JOIN `order` ON employee.id = `order`.employee_id;

my.cnf

bind-address = 127.0.0.1

默认是 127.0.0.1 ,即无法远程连接。

bind-address = 192.168.1.134

安装

sudo apt-get install mysql-server
# 开发环境:不设置root密码,三次回车。
# 生产环境:root 密码不低于 16 位

sudo mysql_install_db
sudo mysql_secure_installation

# 测试连接库
mysql -u root -p
#开发环境没有设置密码,这里直接enter进入,本机省略参数 -h127.0.0.1
mysql -h10.10.8.8 -uroot -p #连接远程 mysql 服务器,按 enter 后输入密码

卸载 mysql

sudo apt-get purge mysql-server mysql-client mysql-common mysql-server-core-5.5 mysql-client-core-5.5
sudo rm -rf /etc/mysql /var/lib/mysql
sudo apt-get autoremove
sudo apt-get autoclean

创建数据库和用户

# 这里把 ghost 换成你想换成的数据库名,建议和域名保持一致,方便以后维护。
$ create database ghost;

# 这里新建一个用户 ghostusert 密码为123456。
$ create user 'ghostuser'@'localhost' identified by '123456';

# 这里是赋予 ghostuser 这个本地用户所有对数据库 blog的权限,当然这里你可以根据实际需要赋予权限。
$ grant all privileges on ghost.* to 'ghostuser'@'localhost';

# 重新读取权限表中的数据到内存,不用重启mysql就可以让权限生效。
$ FLUSH PRIVILEGES;

设置编码为 utf8mb4

注意: 现在安装的mysql 默认编码应该是 utf8mb4, 如果是 utf8mb4 就不要改动了,utf8mb4utf8完全兼容,但它支持最新的Unicode标准,可以显示emoji字符。

mysql -uroot -p
# mysql -uroot -p  --default-character-set=utf8

mysql> show variables like "%char%"; 
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

MySQL的默认编码是 latin1,不支持中文,支持中文需要把数据库的默认编码设置为utf8。

# mysql5.5
sudo vi /etc/mysql/my.cnf
# [client]下添加:default-character-set = utf8mb4
# [mysqld]下添加:character-set-server = utf8mb4

# 重启 mysql
sudo service mysql restart

mysql -uroot -p

mysql> show variables like "%char%"; 
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

参考: 让应用程序支持emoji字符

补充

mysql> set names 'utf8mb4';  

这条命令会同时修改

  • character_set_client
  • character_set_connection
  • character_set_results

清空数据表

清空单表

DELETE FROM users;
ALTER TABLE users AUTO_INCREMENT = 1;

清空有外键关联的数据表

# 先清空关联表
DELETE FROM posts_tags;
# 再删除数据表
DELETE FROM posts;
DELETE FROM tags;
# 再重置 自增 id
ALTER TABLE posts_tags AUTO_INCREMENT = 1;
ALTER TABLE posts AUTO_INCREMENT = 1;
ALTER TABLE tags AUTO_INCREMENT = 1;

Linux 上的 MySQL 服务器,如果不想开数据库端口(默认3306) 但又希望可以远程访问,这时候可以利用 Navicat 的 SSH 隧道设置,使用方法:

新建数据库连接,输入常规信息,如下图所示:

其中主机名可以直接填写 localhost 或者 127.0.0.1

然后切换 Tab 到 SSH ,如下图所示:

这里改用 ssh 的私钥文件: id_rsa 和密钥的密码登录.

SSH:expected key exchange group packet form server 需要升级 Navicat 到 11.2

5.5 升级到 5.7

wget http://dev.mysql.com/get/mysql-apt-config_0.8.0-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.0-1_all.deb
sudo apt-get update
sudo apt-get install mysql-server

After picking 5.7 choose "apply"

升级后,需要修复数据表结构

sudo mysql_upgrade -u root -p

重启 mysql 服务:

sudo service mysql restart

参考:

备份/还原数据表

CentoOS7 要求数据库的 root 用户,需要以系统的 root 用户登录。所以,我们需要先切换到 root

sudo su
mysqldump -u root -p --all-databases > all_databases.sql
mysqldump -u root -p blog > blog.sql

创建数据库 blog,然后恢复:

mysql -u root -p blog < blog.sql

数据表设计

逻辑设计和物理设计同时进行

物理设计:表名、字段名、字段类型

数据库设计规范:

  • 数据库命名规范:
  • 数据库基本设计规范:字符集
  • 数据库索引设计规范
  • 数据库字段设计规范:选择合适的字段类型
  • 数据库 SQL 开发规范:写出性能更好的 SQL
  • 数据库操作行为规范:减少操作失误

数据库命名规范

  • 库名
  • 表名
  • 列名
  • 索引名

1 . 必须使用小写字母并用下划线分割
2 . 名称禁止包含 MySQL 保留关键字。例如订单表 order 则在 sql 语句中应包含在反引号中,例如 `order`
3. 临时库、临时表必须以 tmp 为前缀,并以日期为后缀
4. 备份库、备份表必须以 bak 为前缀,并以日期为后缀
5. 关联数据列在不同表中的 列名 和 类型 必须一致

数据库基本设计规范

  1. 所有表必须使用 Innodb 存储引擎:
    • MySQL 5.6 以后的默认引擎
    • 支持事务,行级锁,更好的恢复性,高并发下性能更好
  2. 数据库和表的字符集统一使用 UTF-8
    • 库、表、列的字符集必须统一
    • 统一字符集可以避免由于字符集转换产生乱码
    • UTF8 字符集汉字占3个字节,ASCII码占用1个字节
  3. 所有表和一段都要添加注释
    • 从一开始就进行数据字典的维护
  4. 尽量控制单表数据量的大小,建议控制在 500 万以内
    • 500 万并不是 MySQL 数据库的限制
    • 可以用历史数据归档,分库分表等手段来控制数据量的大小
  5. 谨慎使用 MySQL 分区表
    • 分区表在物理上表现为多个文件,在逻辑上表现为一个表
    • 谨慎选择分区表,跨分区查询效率可能更低
  6. 尽量做到冷热数据分离,减小表的宽度
    • 减少磁盘IO,保证热数据的内存缓存命中率
    • 更有效的利用缓存,避免读入无用的冷数据(select * from table_name)
    • 经常使用的列放到一个表中
  7. 禁止在表中建立预留字段
    • 对预留字段类型的修改,会对表进行锁定,严重影响正在运行的系统,增加字段则不会锁定表。
  8. 禁止在数据库中存储图片、文件等二进制数据
  9. 禁止直连生产数据库!!!

数据库索引设计规范

索引对数据库的查询性能来说是非常重要的

  1. 限制每张表上的索引量,建议单张表索引不超过 5 个
  • 索引并不是越多越好!索引可以提高效率,同样会降低效率
  • 禁止给表中的每一列都建立索引
  1. 每个 Innodb 表必须有一个主键
  • 不使用更新频繁的列作为主键、不使用多列主键
  • 不使用 UUID,MD5,HASH,字符串列作为主键
  • 建议使用自增ID作为主键
  1. 常见索引列建议
  • SELECT,UPDATE,DELETE 语句的 WHERE 从句中的列
  • 包含在 ORDER BY,GROUP BY,DISTINCT 中的列
  • 多表 JOIN 的关联列
  1. 如何选择索引列的顺序
  • 区分度最高的列(主键)放在联合索引的最左侧
  • 尽量把字段长度小的列放在联合索引的最左侧
  • 使用最频繁的列放在联合索引的左侧
  1. 避免建立冗余索引和重复索引
  • index(a,b,c),index(a,b),index(a) 后面2个索引对于a来说都是冗余索引
  1. 对于频繁的查询优先考虑使用覆盖索引
  2. 尽量避免使用外键
  • 不建议使用外键约束,但一定在表与表之间的关联键上建立索引
  • 外键可用于保证数据的参照完整性,但建议在业务端实现
  • 外键约束会影响父表和子表的写操作从而降低性能

数据库字段设计规范

字段类型的选择,会直接影响数据库的性能

  1. 优先选择符合存储需要的最小的数据类型
  • 将字符串转化为数字类型存储,INET_ATON('255.255.255.255')=429496295,INET_NTOA(429496295)='255.255.255.255'

订单表设计

订单主键应该用什么类型?

int 和 bigint 的选择

主键应该使用整数还是字符串?(用浮点数的请自觉充值智商)

我强烈建议使用字符串。
我们先看使用整数的问题。

使用整数有两个选择:数据库自增和自己生成。

自己生成其实也是自增,无非就是把上次使用的值保存到某个地方,下次使用的时候继续自增。常见的做法是用一个单独的表存储上次用的最大值。这种方式实现复杂,可靠性低,还不如数据库自增。

数据库自增最大的问题还不在于数据库单点造成无法水平切分,因为绝大部分公司还撑不到业务需要分库的情况就倒闭了。

自增主键最大的问题是把公司业务的关键运营数据完全暴露给了竞争对手和VC。举个例子,用户表采用自增主键,只需要每周一早上去注册一个用户,把上周注册的ID和本周注册的ID一比,立刻就知道了该公司一周的新增用户数量。如果网站声称新增了10万用户,但ID却只增加了1千,就只能呵呵了。

因为主键的本质是保证唯一记录,并不要求主键是连续的。实际上不连续的更好,这样既避免了运营数据泄露,也给黑客预测ID制造了障碍,具有更高的安全性。

用字符串主键就不存在这个问题。如果我们用一个UUID作为主键,即varchar(32),除了占用的存储空间较多外,字符串主键具有不可预测性。

有人觉得UUID完全随机,主键本身没有按时间递增,不利于直接主键排序。其实解决这个问题很简单。

方法一,直接用时间戳+UUID构造一个主键,时间戳注意补0,这样生成的主键就是按时间排序的。这个方法简单粗暴,缺点是主键更长了。

方法二,自定义一个算法,时间戳放高位,序列号放低位,还可以保留机器位,然后用base32编码,可以把长度控制在20个字符内。

订单表要冗余 商品名商品价格

参考资料:

关于外键

学生表(student)存学生的信息,成绩表(result)存学生每一次考试的信息,那 result 的 id 就是指向 student 的 foreign key。这个时候你把学生 x 的信息删掉了,他的成绩我还留着!这有两个后果:

  1. 成绩表会有一堆垃圾数据。
  2. 万一新增的学生重用了已删除的学生id,那么他就莫名其妙地拥有了不属于自己的成绩信息,这时候整个数据库就毁了。

所以一定要让你在删除学生的时候给你当头一棒,告诉你要先删成绩,再删人。这是多么合理的做法。

是否采用外键要权衡业务应用场景和开发成本:

  1. 互联网行业不推荐使用外键: 用户量大,并发度高,为此数据库服务器很容易成为性能瓶颈,尤其受IO能力限制,且不能轻易地水平扩展;若是把数据一致性的控制放到事务中,也即让应用服务器承担此部分的压力,而引用服务器一般都是可以做到轻松地水平的伸缩;

2.传统行业推荐使用外键。1>.软件应用的人数有限,换句话说是可控的;2>.数据库服务器的数据量也一般不会超大,且活跃数据有限;

设计数据库时使用外键关联,甚至等到系统部署发布,在生产环境上仍然使用外键……

毕竟,等到你发现用户量增长,数据库写入成为瓶颈的时候,完全可以删掉外键关联嘛……

而且,因为之前的外键约束,也能尽早的发现应用系统的 Bug,从而在之后删掉外键关联的时候不用担心应用系统哪里做得不到位……

当然,如果用户量增长过快,或者是在一个用户量本身就很大的旧系统上新增业务功能,那就只能不用外键,老老实实审查代码了……

外键创建失败的原因

在Mysql中创建外键时,经常会遇到问题而失败,这是因为Mysql中还有很多细节需要我们去留意,我自己总结并查阅资料后列出了以下几种常见原因。

  1. 两个字段的类型或者大小不严格匹配。例如,如果一个是int(10),那么外键也必须设置成int(10),而不是int(11),也不能是tinyint。另外,你还必须确定两个字段是否一个为 signed,而另一个又是unsigned(即:无符号),这两字段必须严格地一致匹配。

1:n 需要中间表么?

n:n 中间表

juction_table

可扩展的登录系统

  • user 表存储 id,name,gender,birth,email,mobile
  • local_auth 表存储 id,user_id,username,password_hash
  • open_auth 表存储 id,user_id,oauth_name,oauth_id,oauth_access_token,oauth_expires
  • api_auth 表存储 id,user_id,api_key,api_secret

每一种 x_auth 表都存储了用户登录认证信息,并通过 user_id 关联到 user 表。这样一个用户可以使用多种方式登录。只要登录成功,拿到了 user_id,再读取 user 表获取用户的 profile,这样读出来的数据也更安全,因为 user 表不包含用户的 password 等敏感信息。

优化查询语句

EXPLAIN SELECT * FROM `order` WHERE active>0;

重点关注 typekey

  • type=const表示通过索引一次就找到了;
  • type=all,表示为全表扫描;
  • key=primary的话,表示使用了主键;

mariadb 10.2 更改默认编码

To change the default character set from latin1 to UTF-8, the following settings should be specified in the my.cnf configuration file.

sudo vi /etc/my.cnf
[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
character-set-server = utf8mb4

Note that the default-character-set option is a client option, not a server option.

数据表名的困境:单数 还是 复数 ?

Table Naming Dilemma: Singular vs. Plural Names

结论:用单数(mongoose 默认用的是复数形式,NoSQL 有其他考虑?)。

  • customer
  • saler
  • user
  • order
  • order_detail
  • bill
  • trade
  • profile

字段值的选择

  • 总服务期
  • 已用服务期
  • 可用服务期

已用服务期 + 可用服务期 = 总服务期

总服务期 是必须存储的,那么问题是 可用服务期已用服务期 存储哪一个?

答案是 可用服务期,因为业务上我们通常会选择出快到期的用户来执行续费提醒通知,可直接选择 可用服务期 小于 3 的用户提醒续费。

已用服务期 在业务中可以通过计算得到。

MySQL 架构与历史

1.1 MySQL 逻辑架构

1.1.1 连接管理与安全性

每个客户端都会在服务器进程中拥有一个线程,这个连接的查询只会在这个单独的线程中执行,该线程只能轮流在某个 CPU 核心或者 CPU 中运行。服务器会负责缓存线程,因此不需要为每一个新建的连接创建或者销毁线程。

MySQL 5.5 或者更新的版本提供了一个 API,支持线程池(Thread-Pooling)插件,可以使用池中少量的线程来服务大量的连接。

1.1.2 优化与执行

1.2 并发控制

无论何时,只要有多个查询需要在同一时刻修改数据,都会产生并发控制的问题。

普通锁的方案在实际应用环境中虽然工作良好,但并不支持并发处理。因为在任意一个时刻,只有一个进程可以修改数据,这在大容量的数据系统中是个问题。

MySQL 在两个层次的并发控制:服务器层与存储引擎层。

Q:MySQL 如何控制并发读写?

MySQL 通过读写锁来控制并发读写。

解决这类经典问题的方法就是并发控制,其实非常简单。在处理并发读或者写时,可以通过实现一个由两种类型的锁系统来解决问题。这两种类型的锁通常被称为读锁(read lock)和写锁(write lock)。

读锁是共享的,或者说是相互不阻塞的。多个客户在同一时刻可以同时读取同一个资源,而互不干扰。
写锁则是排他的,也就是说一个写锁会阻塞其他的写锁或读锁。这是出于安全策略的考虑,只有这样,才能确保在给定的时间里,只有一个用户能执行写入,并防止其他用户读取正在写入的统一资源。

在实际的数据库系统中,每时每刻都在发生锁定,当某个用户在修改某一部分数据时,MySQL 会通过锁定防止其他用户读取同一数据。大多数时候,MySQL 锁的内部管理都是透明的。

一个用户在对表进行写操作(INSERT DELETE UPDATE 等)前,需要先获取写锁,这回阻塞其他用户对该表的所有读写操作。只有没有写锁时,其他读取的用户才能获得读锁,读锁之间是不相互阻塞的。

另外,写锁也比读锁具有更高的优先级,因此一个写锁请求可能会被插入到读锁队列的前面(写锁可以插入到锁队列中读锁的前面,反之,读锁则不能插入到写锁的前面)。

1.3 事务

1.3.1 事务内和事务间的隔离级别

Repeatable Read(可重复读)解决了脏读的问题。该隔离级别保证了在同一个事务中多次读取同样记录的结果是一致的,但是还是无法避免幻读。InnoDB 存储引擎通过多版本并发控制(MVCC)解决了幻读的问题。

Repeatable Read(可重复读)是 MySQL 的默认事务隔离级别。

1.3.2 事务间的死锁

死锁是指两个或者多个事务在同一资源上相互占用,并请求锁定对方占用的资源,从而导致恶性循环的现象。

  1. 当多个事务试图以不同是顺序锁定资源时,就可能会产生死锁。
  2. 多个事务同时锁定同一个资源时,也会产生死锁。

例如,设想下面两个事务同时处理 StockPrice 表:

事务1:

START TRANSACTION
UPDATE StockPrice SET close=45.50 WHERE stock_id = 4 AND date = '2002-05-01';
UPDATE StockPrice SET close=19.80 WHERE stock_id = 3 AND date = '2002-05-02';
COMMIT;

事务2:

START TRANSACTION
UPDATE StockPrice SET close=20.12 WHERE stock_id = 3 AND date = '2002-05-02';
UPDATE StockPrice SET close=47.20 WHERE stock_id = 4 AND date = '2002-05-01';
COMMIT;

如果凑巧两个事务都执行了第一条 UPDATE 语句,更新了一行数据,同时也锁定了该行的数据,接着每个事务都尝试去执行第二条 UPDATE 语句,却发现该行已经被对方锁定,然后两个事务都等待对方释放锁,同时又持有对方需要的锁,则陷入死循环。除非有外部因素介入才可能解除死锁。

为了解决这个问题,数据库系统实现了各种锁检测和死锁超时机制。InnoDB 目前处理死锁的方法是,将持有最少行级排它锁的事务进行回滚(这是相对比较简单的死锁回滚算法)。

对于事务型的系统,这是无法避免的,所以应用程序在设计时必须考虑如何处理死锁。大多数情况下只需要重新执行因死锁回滚的事务即可。

1.3.3 事务日志

1.3.4 MySQL 中的事务

MySQL 提供了 InnoDB 事务型引擎。

自动提交

MySQL 默认采用自动提交(AUTOCOMMIT)模式。也就是说,如果不是显式地开始一个事务,那么每个查询都被当做一个事务并执行提交操作。

当 AUTOCOMMIT=0 时,所有的查询都是在一个事务中,直到显式地执行 COMMIT 或者 ROLLBACK 回滚,该事务结束,同时又开始了另一个新事务。

隐式和显式锁定

InnoDB 采用的是两阶段锁定协议(two-phase locking protocol)。在事务执行过程中,随时都可以执行锁定,锁只有在执行 COMMIT 或者 ROLLBACK 的时候才会释放,并且所有的锁是在同一时刻被释放。前面描述的锁定都是隐式锁定,InnoDB 会根据隔离级别在需要的时候自动加锁。

另外,InnoDB 也支持通过特定语句进行显式锁定,这些语句不属于 SQL 规范:

  • SELECT ... LOCK IN SHARE MODE
  • SELECT ... FOR UPDATE

1.4 多版本并发控制

MySQL 的大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑,它们一般都同时实现了多版本并发控制(MVCC)。

可以认为 MVCC 是行级锁的一个变种,但是它在很多情况下避免了加锁操作,因此开销更低。虽然实现机制有所不同,但大都实现了非阻塞的读操作,写操作也只锁定必要的行。

MVCC 的实现,是通过保存数据在某个时间点的快照来实现的。也就是说,不管需要执行多长时间,每个事务看到的数据都是一致的。根据事务开始的时间不同,每个事务对同一张表,同一时刻看到的数据可能是不一样的。

不同存储引擎的 MVCC 实现是不同的,典型的有乐观锁并发控制悲观锁并发控制

InnoDB 的 MVCC 是通过在每行记录后面保存两个隐藏的列来实现的。这两个列,一个保存了行的创建时间,一个保存行的过期时间(或删除时间)。当然存储的并不是实际的时间值,而是系统版本号。每开始一个新的事务,系统版本号都会自动自增。事务开始时刻的版本号会作为事务的版本号,用来和查询到的每行记录的版本号进行比较。

SELECT
    InnoDB 会根据以下两个条件检查每行记录:
    a:InnoDB 只查找小于等于当前版本号的数据行。
    b:行的`删除版本`要么未定义,要么大于当前事务版本号。
    只有符合上述两个条件的记录,才能作为查询结果。
INSERT
    InnoDB 为新插入的每一行保存当前系统版本号作为`行版本号`
DELETE
    InnoDB 为删除的每一行保存当前系统版本号作为行的删除标识。
UPDATE
    InnoDB 为插入一行新记录,保存当前系统版本号作为行版本号,同时保存当前系统版本号到原来的行作为行删除标识。

保存这两个额外系统版本号,使大多数读操作都可以不用加锁。

MVCC 只在 Repeatable Read 和 ReadCommited 两个隔离级别下工作。因为 Read Uncommitted 总是读取最新的行,Serializable 则会对所有读取的行都加锁。

1.5 MySQL 的存储引擎

1.5.1 InnoDB 存储引擎

MySQL 5.5 (2010)InnoDB 成为默认的存储引擎。

InnoDB 采用 MVCC 来支持高并发,并且实现了四个标准的隔离级别。其默认级别是 Repeatable Read(可重复读),并且通过间隙所(next-key locking)策略来防止幻读的出现。间隙锁使得 InnoDB 不仅仅锁定查询涉及的行,还会对索引中的间隙进行锁定,以防止幻影行的插入。

订单处理

如果涉及订单处理,那么支持事务就是必要选项。InnoDB 是订单处理类应用的最佳选择。

1.5.2 MyISAM 存储引擎