传统数据库
本来准备直接开讲 NoSQL 的 (当时开篇就是说的 NoSQL) 考虑到有些同志可能连 MySQL 系都没接触过, 所以我们 2019 说数据系的时候预计从 MySQL(穿插 MSSQL)开始, 这篇文章就当试水篇, 效果好就继续往下写~(这篇偏理论和运维)
1.1.MariaDB and MySQL
官方文档: https://mariadb.com/kb/zh-cn/mariadb
目前主流: MySQL 5.7 or MariaDB 5.5.x(推荐)
多一句嘴, MySQL 当年被 Oracle 收购后, MySQL 之父觉得靠 Oracle 维护 MySQL 很不靠谱, 然后就跳槽弄了个 MariaDB(很多 Oracle 竞争对手扶持着), 目前 MariaDB 是发展最快的 MySQL 分支版本(PS:MySQL 现在是双协议了, 大部分公司用的版本都是<=5.7)
然后得说下迁移问题: MySQL 5.x 到 MariaDB 5.x 基本上是无缝的, MariaDB 最新稳定版为: MariaDB 5.5
MariaDB 与 MySQL 兼容性可以查看:
https://mariadb.com/kb/en/library/mariadb-vs-mysql-compatibility
PS: 国内比较火的还有阿里的 MySQL 分支: https://github.com/alibaba/AliSQL
不谈其他的, 咱们看看它们开发的积极程度就知道为什么 MariaDB 是主流了
1.2.MariaDB 部署
1. 环境配置和初始化
安装很简单, 以 CentOS 为例:
- systemctl start mariadb.service # 启动 MariaDB
- systemctl enable mariadb.service # 设置开机启动
- systemctl stop mariadb.service # 停止 MariaDB
- systemctl restart mariadb.service # 重启 MariaDB
PS:Win 安装注意这一步:
执行文件简单说明: 有时候我们 ps aux | grep MySQL 的时候, 发现运行的并不是 / usr/bin / 下的 mysqld 而是 mysqld_safe, 那这个 mysqld_safe 是啥呢?==> 线程安全的实例
MariaDB 的程序组成: ls /usr/bin | grep MySQL
Client:
MySQL 命令行客户端
mysqldump 数据库备份用
mysqladmin 远程管理工具
mysqlbinlog 二进制日志管理工具
...
Server:
mysqld_safe 线程安全的实例
mysqld_multi 多实例
- mysqld
- mysql_secure_installation
安全初始化工具(记得先启动数据库哦)
...
MySQL 的账号由两部分组成: username@host,MySQL 客户端连接参数:
-u 用户名: 默认为 root
-h 服务器主机: 默认为 localhost
host 用于限制用户可以通过哪些主机连接
支持通配符:
% 匹配任意长度的任意字符: 172.16.0.0/16 ==> 172.16.%.%
_匹配任意单个字符
-p 密码: 默认为空
安装完成后运行
mysql_secure_installation
来设置密码并初始化
很多人安装完成后是这样设置密码的:(不推荐)
正确打开方式:
mysql_secure_installation
如果允许 root 远程登录:
Disallow root login remotely? [Y/n] n
安全初始化后登录图示:
2. 配置文件
以 MariaDB 5.5.60 为例:
Linux: 配置文件查找顺序(找不到就往下继续)
- /etc/my.cnf -->
- /etc/MySQL/conf.d/*.cnf
- --> ~/.my.cnf
- Windows:
MariaDB 安装目录 / data/my.INI
PS: 一般配置文件都会设置这 3 个
- # 指定数据库文件存放路径
- datadir = /home/dnt/data
- # 独立表空间: 每一个表都有一个. frm 表描述文件, 还有一个. ibd 文件
- innodb_file_per_table=on
- # 不对连接进行 DNS 解析(省时)
- skip_name_resolve=on
其他配置 MariaDB 提供了样本:
- [dnt@localhost ~] ls /usr/share/MySQL/ | grep .cnf
- my-huge.cnf # 超大内存配置参考
- my-innodb-heavy-4G.cnf # 4G 内存配置参考
- my-large.cnf # 大内存配置
- my-medium.cnf # 中等内存配置
- my-small.cnf # 小内存配置
PS:thread_concurrency=CPU 数 * 2 最佳, 修改配置后记得重启数据库
3. 远程访问
1. 之前安全初始化的时候把 root 禁止远程登录了, 现在我们创建一个其他用户
2. 给用户权限
3. 防火墙放行指定端口
4. 远程客户端测试一下
Code 如下:
- # root 账户登录
- MySQL -uroot -p
- # 新增用户
- insert into MySQL.user(user,host,password) values("用户名","%",password("密码"));
- # 刷新设置
- flush privileges;
- # 分配权限
grant all privileges on 数据库.* to 用户名 @"%" identified by "密码";
- # 刷新设置
- flush privileges;
- # 显示服务状态
- systemctl status firewalld
- # 添加 --permanent 永久生效(没有此参数重启后失效)
- firewall-cmd --zone=public --add-port=80/tcp --permanent
- # 重新载入
- firewall-cmd --reload
- # 查看
- firewall-cmd --zone= public --query-port=80/tcp
- # 删除
- firewall-cmd --zone= public --remove-port=80/tcp --permanent
SQLServer 远程连接: https://www.cnblogs.com/dunitian/p/5474501.html
MySQL 军规(58)
文章结尾贴一节 58 的 MySQL 军规:(适用于并发量大, 数据量大的典型互联网业务)
1. 基础规范
表存储引擎必须使用 InnoDB
表字符集默认使用 utf8, 必要时候使用 utf8mb4
utf8 通用, 无乱码风险, 汉字 3 字节, 英文 1 字节
utf8mb4 是 utf8 的超集, 存储 4 字节时使用(eg: 表情符号)
禁止使用存储过程, 视图, 触发器, Event
调试, 排错, 迁移都比较困难, 扩展性较差
对数据库性能影响较大, 互联网业务, 能让站点层和服务层干的事情, 不要交到数据库层
禁止在数据库中存储大文件(eg: 照片)
可以将大文件存储在对象存储系统, 数据库中存储路径
禁止在线上环境做数据库压力测试
测试, 开发, 线上数据库环境必须隔离
2. 命名规范
库名, 表名, 列名必须用小写, 采用下划线分隔
abc,Abc,ABC 都是给自己埋坑
库名, 表名, 列名必须见名知义, 长度不要超过 32 字符
tmp,wushan 谁 TM 知道这些库是干嘛的
库备份必须以 bak 为前缀, 以日期为后缀
从库必须以 - s 为后缀
备库必须以 - ss 为后缀
3. 表设计规范
单实例表个数必须控制在
2000
个以内
单表分表个数必须控制在
1024
个以内
表必须有主键, 推荐使用 unsigned 整数为主键
潜在坑: 删除无主键的表, 如果是 row 模式的主从架构, 从库会挂住
禁止使用外键, 如果要保证完整性, 应由应用程式实现
外键使得表之间相互耦合, 影响 update/delete 等 SQL 性能
有可能造成死锁, 高并发情况下容易成为数据库瓶颈
建议将大字段, 访问频度低的字段拆分到单独的表中存储, 分离冷热数据
垂直拆分的依据, 尽量把长度较短, 访问频率较高的属性放在主表里
流量大数据量大时, 数据访问要有 service 层, 并且 service 层不要通过 join 来获取主表和扩展表的属性
具体可以参考沈剑大牛写的《如何实施数据库垂直拆分》 https://mp.weixin.qq.com/s/ezD0CWHAr0RteC9yrwqyZA
4. 列设计规范
根据业务区分使用 tinyint/int/bigint, 分别会占用 1/
4
/
8
字节
根据业务区分使用 char/varchar(PS: 没有 MSSQL 里的 nvarchar)
字段长度固定, 或者长度近似的业务场景, 适合使用 char, 能够减少碎片, 查询性能高
字段长度相差较大, 或者更新较少的业务场景, 适合使用 varchar, 能够减少空间
根据业务区分使用 datetime/timestamp
datetime 占用 5 个字节, timestamp 占用 4 个字节
存储年使用 year, 存储日期使用 date, 存储时间使用 datetime
必须把字段定义为 NOT NULL 并设默认值
NULL 需要更多的存储空间
NULL 的列使用索引, 索引统计, 值都更加复杂, MySQL 更难优化
NULL 只能采用 IS NULL 或者 IS NOT NULL, 而在 =/!=/in/not in 时有大坑
使用 int unsigned 存储 IPv4, 不要用 char(15)
使用 varchar(20)存储手机号, 不要使用整数
手机号不会用来做数学运算
varchar 可以模糊查询(eg:like '138%')
牵扯到国家代号, 可能出现 +,-,()等字符, eg:
+86
使用 tinyint 来代替 enum
enum 增加新值要进行 DDL 操作
5. 索引规范(常用)
唯一索引使用 uniq_字段名来命名
非唯一索引使用 idx_字段名来命名
单张表索引数量建议控制在 5 个以内
互联网高并发业务, 太多索引会影响写性能
异常复杂的查询需求, 可以选择 ES 等更为适合的方式存储
生成执行计划时, 如果索引太多, 会降低性能, 并可能导致 MySQL 选择不到最优索引
组合索引字段数不建议超过 5 个
如果 5 个字段还不能极大缩小 row 范围, 八成是设计有问题
不建议在频繁更新的字段上建立索引
非必要不要进行 join 查询, 如果要进行 join 查询, 被 join 的字段必须类型相同, 并建立索引
join 字段类型不一致容易导致全表扫描
理解组合索引最左前缀原则, 避免重复建设索引
如果建立了(a,b,c), 相当于建立了(a), (a,b), (a,b,c)
6.SQL 规范(常用)
禁止使用 select *, 只获取必要字段
指定字段能有效利用索引覆盖
select * 会增加
CPU/io / 内存 / 带宽
的消耗
指定字段查询, 在表结构变更时, 能保证对应用程序无影响
insert 必须指定字段, 禁止使用
insert into T values()
指定字段插入, 在表结构变更时, 能保证对应用程序无影响
隐式类型转换会使索引失效, 导致全表扫描(很重要)
禁止在 where 条件列使用函数或者表达式
导致不能命中索引, 全表扫描
禁止负向查询以及 % 开头的模糊查询
导致不能命中索引, 全表扫描
禁止大表 join 和子查询
同一个字段上的 or 必须改写为 in,in 的值必须少于 50 个
应用程序必须捕获 SQL 异常(方便定位线上问题)
课后思考: 为什么 select uid from user where phone=13811223344 不能命中 phone 索引?
来源: https://www.cnblogs.com/dotnetcrazy/p/9887708.html