复习使用 MySQL 学习笔记

Question

环境

• MySQL 5.7.13

MySQL 及衍生版本

• MySQL
• MariaDB
• Percona Server

MySQL SQL 基础

• DDL(Data Definition Language) - 数据定义

  • TRUNCATE - 清空
    • TRUNCATE TABLE Syntax - 清空数据表
  • RENAME - 重命名
    • RENAME TABLE Syntax - 重命名数据表
  • CREATE - 创建
    • CREATE VIEW Syntax - 创建视图
    • CREATE EVENT Syntax - 创建事件
    • CREATE INDEX Syntax - 创建索引
    • CREATE TABLE Syntax - 创建数据表
    • CREATE SERVER Syntax - 创建服务器
    • CREATE TRIGGER Syntax - 创建触发器
    • CREATE FUNCTION Syntax - 创建自定义函数
    • CREATE DATABASE Syntax - 创建数据库
    • CREATE PROCEDURE Syntax - 创建存储过程
    • CREATE TABLESPACE Syntax - 创建数据表空间
    • CREATE LOGFILE GROUP Syntax - 创建日志文件组
  • ALTER - 修改
    • ALTER VIEW Syntax - 修改视图
    • ALTER EVENT Syntax - 修改事件
    • ALTER TABLE Syntax - 修改数据表
    • ALTER SERVER Syntax - 修改服务器
    • ALTER DATABASE Syntax - 修改数据库
    • ALTER FUNCTION Syntax - 修改自定义函数
    • ALTER INSTANCE Syntax - 修改实例
    • ALTER PROCEDURE Syntax - 修改存储过程
    • ALTER TABLESPACE Syntax - 修改数据表空间
    • ALTER LOGFILE GROUP Syntax - 修改日志分组
  • DROP - 删除
    • DROP VIEW Syntax - 删除视图
    • DROP EVENT Syntax - 删除事件
    • DROP INDEX Syntax - 删除索引
    • DROP TABLE Syntax - 删除数据表
    • DROP SERVER Syntax - 删除服务器
    • DROP TRIGGER Syntax - 删除触发器
    • DROP DATABASE Syntax - 删除数据库
    • DROP FUNCTION Syntax - 删除自定义函数
    • DROP PROCEDURE Syntax - 删除存储过程
    • DROP TABLESPACE Syntax - 删除数据表空间
    • DROP LOGFILE GROUP Syntax - 删除日志分组

• DML(Data Manipulation Language) - 数据操作

  • DO
  • CALL
  • HANDLER
  • LOAD XML
  • LOAD DATA INFILE
  • INSERT - 数据插入
  • DELETE - 数据删除
  • UPDATE - 数据修改
  • SELECT - 数据查询
    • JOIN - 连接
      • LEFT JOIN - 左连接
      • RIGHT JOIN - 右连接
      • INNER JOIN - 内连接
    • GROUP - 分组
    • UNION - 联合
  • REPLACE
  • Subquery - 子查询
  • LIMIT, OFFSET - 分页

• DCL(Data Control Language) - 数据控制

  • GRANT - 授权
  • REVOKE - 撤销授权

• Transactions and Lock - 事务

  • START TRANSACTION, COMMIT, and ROLLBACK Syntax - 事务开始，事务提交和事务回滚
  • Statements That Cannot Be Rolled Back - 不能执行回滚的语句
  • Statements That Cause an Implicit Commit - 隐式提交
  • SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT Syntax - 保存点，回滚到保存点，释放保存点
  • LOCK TABLES and UNLOCK TABLES Syntax - 锁表与解锁
  • SET TRANSACTION Syntax - 设置事务语法
  • XA Transactions - XA 事务

• MySQL Utility Statements - MySQL 实用语句

  • USE Syntax - 设置当前 SQL 语句默认使用数据库
  • HELP Syntax - 从 MySQL 参考手册返回在线信息
  • EXPLAIN Syntax - 获取 SQL 执行信息
  • DESCRIBE Syntax - 获取表结构信息

• Database Administration Statements - 数据库管理员语句

  • SET Syntax - 设置
    • SET NAMES Syntax
    • SET CHARACTER SET Syntax
    • SET Syntax for Variable Assignment
  • SHOW Syntax - 查看
    • SHOW BINARY LOGS Syntax - 显示二进制日志
    • SHOW BINLOG EVENTS Syntax - 显示二进制事件
    • SHOW CHARACTER SET Syntax - 显示字符集
    • SHOW COLLATION Syntax - 显示支持的字符
    • SHOW COLUMNS Syntax - 显示字段信息
    • SHOW CREATE DATABASE Syntax - 显示创建指定数据库的 SQL 语句
    • SHOW CREATE EVENT Syntax - 显示创建指定事件的 SQL 语句
    • SHOW CREATE FUNCTION Syntax - 显示创建指定自定义函数的 SQL 语句
    • SHOW CREATE PROCEDURE Syntax - 显示创建指定存储过程的 SQL 语句
    • SHOW CREATE TABLE Syntax - 显示创建指定表的 SQL 语句
    • SHOW CREATE TRIGGER Syntax - 显示创建指定触发器的 SQL 语句
    • SHOW CREATE USER Syntax - 显示创建指定用户的 SQL 语句
    • SHOW CREATE VIEW Syntax - 显示创建指定视图的 SQL 语句
    • SHOW DATABASES Syntax - 显示数据库列表
    • SHOW ENGINE Syntax - 显示引擎的操作信息
    • SHOW ENGINES Syntax - 显示支持的引擎
    • SHOW ERRORS Syntax - 显示错误
    • SHOW EVENTS Syntax - 显示事件
    • SHOW FUNCTION CODE Syntax - 显示自定义函数代码
    • SHOW FUNCTION STATUS Syntax - 显示自定义函数状态
    • SHOW GRANTS Syntax
    • SHOW INDEX Syntax - 显示索引
    • SHOW MASTER STATUS Syntax
    • SHOW OPEN TABLES Syntax
    • SHOW PLUGINS Syntax
    • SHOW PRIVILEGES Syntax - 显示当前用户权限
    • SHOW PROCEDURE CODE Syntax - 显示存储过程代码
    • SHOW PROCEDURE STATUS Syntax - 显示存储过程状态
    • SHOW PROCESSLIST Syntax
    • SHOW PROFILE Syntax - 显示配置文件
    • SHOW PROFILES Syntax - 显示配置文件列表
    • SHOW RELAYLOG EVENTS Syntax
    • SHOW SLAVE HOSTS Syntax - 查看附属主机
    • SHOW SLAVE STATUS Syntax - 查看附属主机状态
    • SHOW STATUS Syntax - 查看各种状态
    • SHOW TABLE STATUS Syntax - 显示指定数据库的所有表状态
    • SHOW TABLES Syntax - 显示指定数据库的所有表
    • SHOW TRIGGERS Syntax - 显示触发器
    • SHOW VARIABLES Syntax - 显示变量
    • SHOW WARNINGS Syntax - 显示警告
  • Table Maintenance Statements - 表维护语句
    • CHECK TABLE Syntax - 检查表
    • REPAIR TABLE Syntax - 修复表
    • ANALYZE TABLE Syntax - 分析表
    • OPTIMIZE TABLE Syntax - 优化表
    • CHECKSUM TABLE Syntax - 校检表
  • Account Management Statements - 账号管理语句
    • GRANT Syntax - 修复
    • REVOKE Syntax - 撤销
    • DROP USER Syntax - 删除用户
    • ALTER USER Syntax - 修改用户
    • CREATE USER Syntax - 创建用户
    • RENAME USER Syntax - 重命名用户
    • SET PASSWORD Syntax - 设置密码
  • Other Administrative Statements - 其他管理语句
  • Plugin and User-Defined Function Statements - 插件和用户定义的函数语句

• 范式 & 反范式

  • 范式
    • 第一范式（1NF）
    • 第二范式（2NF）
    • 第三范式（3NF）
    • 巴斯-科德范式（BCNF）
    • 第四范式(4NF）
    • 第五范式（5NF，又称完美范式）
  • 反范式
    • 没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。

MySQL 主要存储引擎

特性/引擎	MyISAM	Aria	InnoDB	XtraDB	Memory	Archive
数据存储	传统顺序数据储存	传统顺序数据储存	表空间存储方式	表空间存储方式
事务支持	不支持	支持	支持	支持	不支持	不支持
外键	不支持	不支持	支持	支持	不支持	不支持
全文检索	支持	支持	5.6 之后支持	5.6 之后支持
锁级别	表级锁	表级锁	行级锁	行级锁	表级锁	行级锁
Count 速度	快	快	慢	慢
适合业务	读多写少、单表数据量小于 1 KW	读多写少、单表数据量小于 1 KW	读写均衡、数据量不限	读写均衡、数据量不限
可用版本	MySQL、MariaDB、Percona	MariaDB	MySQL、MariaDB、Percona	MariaDB、Percona
其他说明	传统顺序索引数据库，适合读多写少小数据量业务	MyISAM 增强版，性能更好	适合高压力高性能的业务模型	InnoDB 增强版

MySQL 索引定义

MySQL 的索引（Index）是帮助 MySQL 高效获取数据的数据结构

MySQL 索引类型

• Normal
  • 普通索引，对存储的数值没有任何限制
• Unique
  • 唯一索引，不允许数值重复，但允许空值
• Primary
  • 主键索引，是一种特殊的唯一索引，不允许存在数值重复或空值
• Spatial（R-Tree）
  • GIS 相关空间查询使用索引数据结构。
• Fulltext
  • 主要用于全文检索，目前只有 MyISAM 和 MySQL 5.6 + 的 InnoDB 支持；目前只支持英文。

MySQL 索引方法

• B+Tree
  • 可用于排序的索引数据结构，可应用于 =，>，< 等各个范围查询，并且可以排序
  • 时间复杂度：O(log2N)
• Hash
  • 只能用于 =，IN 等操作，无法进行范围操作，Key 冲突严重情况下可能性能比 B-Tree 低下
  • 时间复杂度：O(1)

MyISAM 索引结构：B+ Tree

• MyISAM 主索引和辅助索引
  • 区别在于：主索引 Key 是唯一；辅助索引 Key 可以重复
• MyISAM 为非聚簇索引

InnoDB 索引结构：B+ Tree

• InnoDB 主索引和辅助索引
  • InnoDB 的主索引是聚簇索引，数据和 Key 本身都会存储在 B+Tree 的叶子节点
  • InnoDB 的辅助索引本身主要是记录主索引的 Key,最终查找数据还是从辅助索引再去主索引查找
• InnoDB 为聚簇索引

存储引擎及版本选择

结论：

• 使用 InnoDB 于生产环境
• 使用 MySQL 5.5+ 于生产环境

原因：

• 更稳定可靠的数据存储和索引结构；整个存储引擎设计思想更可靠先进，接近于 Oracle、SQL Server 级别的数据库（有兴趣可以去阅读源码了解细节）
• 更多可靠特性支持，比如事务、外键等支持（支付等关键领域事务非常重要）
• 运行更稳定，不论读写数据的量级，都能够保证比较稳定的性能响应
• 更好地崩溃恢复机制，特别利用一些 Percona 的一些工具，更有效运维 InnoDB
• MySQL 5.5+ 对比 MySQL 5.1.x 总体功能和性能提升太多，改进太多

MySQL 优化：硬件

• CPU：使用多核 CPU，能够充分发挥新版 MySQL 多核下的效果，建议 4 核以上
• 内存：如果数据量比较大，建议使用不要低于 20 G 内存的服务器
• 硬盘：SSD 硬盘，提高 TPS，选择适合的 RAID 方案
• 网卡：保证足够的吞吐量，建议千兆网卡

MySQL 优化：软件

• OS 类型
  • Linux
  • FreeBSD
• Linux 关键配置
  • 文件打开描述符：/etc/security/limits.conf 中 nofile、/proc/sys/fs/nr_open
  • 可分配文件句柄数：/etc/sysctl.conf 中 fs.file-max、/proc/sys/fs/file-max
  • 进程数：/etc/security/limits.conf 中的 nproc
  • 线程数：/proc/sys/kernel/thread-max
  • 其他 TCP 和网络相关选项：/etc/sysctl.conf
  • 这些都可以通过 ulimit 或 直接调整 /proc 中变量进行临时修改
  • 建议关闭 SWAP 分区（内存要足够大才行）；如果数据太大，为了防止夯死主机，可以设置 2G 左右的 SWAP 分区

MySQL 优化：基础配置

基础配置 & MyISAM 配置

max_connections=3000
max_user_connections=2800
max_connect_errors=10
max_allowed_packet=64M
max_heap_table_size=512M
tmp_table_size=512M
max_length_for_sort_data=16k
wait_timeout=172800
interactive_timeout=172800

net_buffer_length=8K
read_buffer_size=4M
read_rnd_buffer_size=1M
sort_buffer_size=256K
join_buffer_size=2M
table_open_cache=512
thread_cache_size=512
query_cache_type=1
query_cache_size=256M

InnoDB 配置

innodb_file_per_table=1
innodb_open_files=7168
innodb_use_sys_malloc=1
innodb_additional_mem_pool_size=64M
innodb_buffer_pool_instances=4
innodb_buffer_pool_size=20G
innodb_data_home_dir=/home/work/data
innodb_data_file_path=ibdata1:1024M:autoextend
innodb_autoextend_increment=128
innodb_thread_concurrency=0
innodb_flush_log_at_trx_commit=1
innodb_fast_shutdown=1
innodb_force_recovery=0

innodb_log_buffer_size=16M
innodb_log_file_size=128M
innodb_log_files_in_group=4
innodb_log_group_home_dir=/home/work/data
innodb_max_dirty_pages_pct=60
innodb_purge_threads=0
innodb_lock_wait_timeout=50
innodb_rollback_on_timeout=1
innodb_commit_concurrency=0
innodb_concurrency_tickets=1024
innodb_autoinc_lock_mode=2
innodb_change_buffering=all

MySQL 优化：表设计原则

• 互联网业务的特点：数据量大、读写操作多、业务模式相对简单
• 单表字段不宜过多，尽量不按照第三范式去设计表结构，
• 尽量减少表关联，适当的冗余保证不联表查询
• 表名和字段尽量采用小写字母+下划线的命名规则，尽量使用英文，例如：user_score
• 每个表一定要有主键，一般建议自增 ID；单表索引不宜过多，一般 5~6 个索引
• 字段尽量使用高效类型，比如数字或时间，IP 地址、手机号等都可以使用数字类型存储，非不得已采用字符

MySQL 优化：查询语句

• 不要写太复杂的 SQL 语句，尽量简单，能够让业务去做的，就不要让数据库区操作
• 尽量避免：子查询、group by、distinct 等操作（子查询可以用 left join 替代）
• where、order by 等关键字段一定要建立索引，where 里多个条件经常使用的可以建立联合索引（注意建立索引的数据必须是尽量分布多并且具备单调性，重复率低）
• 把数据当做神一样来供着，能够缓存的尽量缓存（redis、memcached 等），能够多次查询的就不要关联查询
• 所有的变更操作（update、delete）必须有 where 条件！
• 不适合使用 MySQL 服务的数据功能操作，尽量采用别的第三方服务支持，比如全文检索功能

MySQL 优化：SQL 优化实例

• Like 优化
• Limit 优化
• InnoDB 中 count 优化
• 定期使用 Explain 查看执行计划

PHP 与 MySQL 交互

• 扩展层
• 代码层
  • 为防止 mysql 太慢夯住 PHP，建议设置 SQL 超时，或者设置 execute_time 等超时设置
  • $mysqli->options(MYSQL_OPT_READ_TIMEOUT, 3);
  • $mysqli->options(MYSQL_OPT_WRITE_TIMEOUT, 1);
  • 务必在代码里记录相关 SQL 语句执行性能和时间等信息，方便后续优化
• 超时设置
  • PHP 代码连接后端的超时设置
  • php.ini 中 execute_time 设置
  • php-fpm 中 request_terminate_timeout，request_slowlog_timeout
  • nginx upstream 相关超时设置

PHP 与 MySQL 安全

• SQL 注入
• MySQL 服务端
• 备份机制