Vgbhfive's Blog

MySQL-快速入门和提高

Posted on Edited on

基础

简介

MySQL 是最流行的关系型数据库管理系统，在WEB 应用方面MySQL 是最好的RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。
数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。
每个数据库都有一个或多个不同的API 用于创建、访问、管理、搜索和复制所保存的数据。
也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。
所以，现在使用关系型数据库管理系统（RDBMS）来存储和管理的大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。
RDBMS 即关系数据库管理系统(Relational Database Management System)的特点：

  • 数据以表格的形式出现。
  • 每行为各种记录名称。
  • 每列为记录名称所对应的数据域。
  • 许多的行和列组成一张表单。
  • 若干的表单组成database 。

RDBMS 术语：

  • 数据库: 数据库是一些关联表的集合。
  • 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
  • 表头(header): 每一列的名称。
  • 列: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。
  • 行：一行（=元组或记录）是一组相关的数据，例如一条用户订阅的数据。
  • 值: 行的具体信息, 每个值必须与该列的数据类型相同。
  • 键: 键的值在当前列中具有唯一性。
  • 冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
  • 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
  • 外键：外键用于关联两个表。
  • 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
  • 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
  • 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

安装

Linux

示例以Centos7 系统使用yum 命令安装演示。

1
2
3
4
5
6
7
8
9
wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
rpm -ivh mysql-community-release-el7-5.noarch.rpm
yum update
yum install mysql-server
chown mysql:mysql -R /var/lib/mysql # 权限设置
mysql --initialize # 初始化MySQL
systemctl start mysqld # 启动MySQL 
systemctl status mysqld # 查看MySQL运行状态
mysqladmin --version # 验证MySQL安装
Windows

点击链接https://www.mysql.com/downloads/ 下载你想要的版本。

元数据

MySQL以下三种信息：

  • 查询结果信息： SELECT、UPDATE、DELETE 语句影响的记录数。
  • 数据库和数据表的信息： 包含了数据库及数据表的结构信息。
  • MySQL服务器信息： 包含了数据库服务器的当前状态、版本号等。

命令:

  • SELECT VERSION(): 服务器版本信息
  • SELECT DATABASE(): 当前数据库名 (或者返回空)
  • SELECT USER(): 当前用户名
  • SHOW STATUS: 服务器状态
  • SHOW VARIABLES: 服务器配置变量

基础语法

操作数据库
1
2
3
4
5
6
mysql -h 主机名 -u 用户名 -p # 登录MySQL
create DATABASE vgbh; # 创建数据库
drop database vgbh; # 删除数据库
use vgbh; # 使用数据库
CREATE TABLE table_name (column_name column_type); # 创建数据表
DROP TABLE table_name; # 删除数据表
数据类型

MySQL支持多种类型，大致可以分为三类：

  • 数值。(TINYINT、INTEGER、MEDIUMINT、SMALLINT、DECIMAL、NUMERIC、FLOAT、DOUBLE)
  • 日期/时间。(DATETIME、DATE、TIMESTAMP、TIME、YEAR)
  • 字符串(字符)类型。(CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM、SET)
CURD

Insert:

1
INSERT INTO table_name (field1, field2,...fieldN) VALUES (value1, value2,...valueN);

Select:

1
2
SELECT column_name,column_name FROM table_name [WHERE Clause][LIMIT N][OFFSET M]
SELECT field1, field2,...fieldN FROM table_name WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

Update:

1
UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]

Delete:

1
DELETE FROM table_name [WHERE Clause]

Alter:

1
2
3
4
ALTER TABLE table_name ADD i INT; # 添加字段
ALTER TABLE table_name DROP i; # 删除字段
ALTER TABLE table_name MODIFY c CHAR(10); # 修改字段类型
ALTER TABLE table_name CHANGE i j BIGINT; # 修改字段名称
UNION

MySQL UNION 操作符用于连接两个以上的SELECT 语句的结果组合到一个结果集合中。
多个SELECT 语句会删除重复的数据。

1
2
3
SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions];
分组、排序

Order by:

1
2
SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]

Group by:

1
2
SELECT column_name, function(column_name) FROM table_name WHERE column_name 
GROUP BY column_name;
连接

可以在SELECT、UPDATE、DELETE 语句中使用MySQL 的JOIN 来联合多表查询。
JOIN 按照功能大致分为如下三类：

  • INNER JOIN（内连接或等值连接）：获取两个表中字段匹配关系的记录。
  • LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
  • RIGHT JOIN（右连接）： 与LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
NULL值处理

MySQL 使用SQL SELECT 命令及WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为NULL 时，该命令可能就无法正常工作。
为了处理这种情况，MySQL提供了三大运算符:

  • IS NULL: 当列的值是 NULL,此运算符返回true。
  • IS NOT NULL: 当列的值不为NULL, 运算符返回true。
  • <=>: 比较操作符(不同于=运算符),当比较的的两个值为NULL 时返回true。

关于NULL 的条件比较运算是比较特殊的。你不能使用=NULL 或!=NULL 在列中查找NULL 值 。
在MySQL 中，NULL 值与任何其它值的比较（即使是NULL ）永远返回false，即NULL = NULL 返回false 。

高级

正则表达式

MySQL 同样也支持其他正则表达式的匹配，MySQL 中使用REGEXP 操作符来进行正则表达式匹配。
REGEXP 操作符:

  • ^ :匹配输入字符串的开始位置。如果设置了RegExp 对象的Multiline 属性，^ 也匹配’\n’ 或’\r’ 之后的位置。
  • $ :匹配输入字符串的结束位置。如果设置了RegExp 对象的Multiline 属性，$ 也匹配’\n’ 或’\r’ 之前的位置。
  • . :匹配除”\n” 之外的任何单个字符。要匹配包括’\n’ 在内的任何字符，请使用象’[.\n]’ 的模式。
  • […] :字符集合。匹配所包含的任意一个字符。例如，’[abc]’ 可以匹配”plain” 中的’a’。
  • [^…] :负值字符集合。匹配未包含的任意字符。例如，’[^abc]’ 可以匹配”plain” 中的’p’。
  • p1|p2|p3 :匹配p1 或p2 或p3。例如，’z|food’ 能匹配”z” 或”food”。’(z|f)ood’ 则匹配 “zood” 或 “food”。
  • * :匹配前面的子表达式零次或多次。例如，zo* 能匹配”z” 以及”zoo”。* 等价于{0,}。
  • + :匹配前面的子表达式一次或多次。例如，zo+’ 能匹配”zo” 以及”zoo”，但不能匹配 “z”。+ 等价于 {1,}。
  • {n} :n 是一个非负整数。匹配确定的n 次。例如，’o{2}’ 不能匹配”Bob” 中的’o’，但是能匹配”food” 中的两个o。
  • {n,m} :m 和n 均为非负整数，其中n<=m。最少匹配n 次且最多匹配m 次。

示例：
查找name字段中以’st’为开头的所有数据：

1
SELECT name FROM person_tbl WHERE name REGEXP '^st';

查找name字段中以元音字符开头或以’ok’ 字符串结尾的所有数据：

1
SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。
事务是必须满足4个条件（ACID）:原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

  • 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
  • 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
  • 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
  • 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
隔离级别

SQL标准中的四种隔离级别:

  • **READ UNCOMMITED(未提交读)**：
    • 事务中的修改，即使没有提交，对其它事务也是可见的。
    • 事务读取未提交的数据，称为脏读(Dirty READ )。
    • 性能并不比其它级别好太多，但确少了很多好处，不推荐。
  • **READ COMMITED(提交读)、不可重复读(nonrepeatable read)**：
    • 大多数数据库的默认级别，但MySQL 不是。
    • 一个事务只能读取已提交的事务所做的修改，换句话说，一个事务从开始直到提交之前，所做的任何修改对其它事务都是不可见的。
    • 可能会导致虚读，如事务A 两次读取数据，事务B在这之间修改了数据，这两次读取会有不一样的结果，即可读取其它事务的增删改。
  • **REPEATABLE READ(可重复读)**：
    • MySQL默认级别。
    • 解决脏读问题，保证在同一个事务中多次读取同样记录的结果是一致的。
    • 可能会导致幻读(Phantom Read )，事务A 读取并修改数据，事务B 也在该范围修改了数据(插入或删除)，事务A 再次读取该范围的数据，发现了幻行(Phantom Row )，即可读取其它事务的增删。
  • **SERIALIZABLE(可串行化)**：
    • 最高级别的隔离级别，强制事务串行执行。
    • 在读取的每一行数据上都加锁，导致大量的超时和锁争用问题。
事务日志

使用事务日志，存储引擎修改表数据，只需修改其内存拷贝，再将该行为记录到持久在硬盘的事务日志中。
大多数存储引擎的实现方案，修改数据需写两次磁盘(第一次为日志记录，第二次为数据)。
优点：

  • 提高事务的效率
  • 速度快。采用追加方式，写日志的操作是磁盘一小块区域的顺序IO ，而不是多区域的随机IO 。

锁

读写锁

共享锁（读锁）： 其他事务可以读，但不能写。
排他锁（写锁）： 其他事务不能读取，也不能写。

锁粒度

MySQL 不同的存储引擎支持不同的锁机制，所有的存储引擎都以自己的方式显现了锁机制，服务器层完全不了解存储引擎中的锁实现：

  • MyISAM 和 MEMORY 存储引擎采用的是表级锁（table-level locking）。
  • BDB 存储引擎采用的是页面锁（page-level locking），但也支持表级锁。
  • InnoDB 存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。
  • 默认情况下，表锁和行锁都是自动获得的， 不需要额外的命令。

但是在有的情况下，用户需要明确地进行锁表或者进行事务的控制，以便确保整个事务的完整性，这样就需要使用事务控制和锁定语句来完成。

索引

MySQL索引的建立对于MySQL 的高效运行是很重要的，索引可以大大提高MySQL 的检索速度。
建立索引会占用磁盘空间的索引文件。
创建最基本的索引：

1
CREATE INDEX indexName ON mytable(username(length));

删除索引：

1
DROP INDEX [indexName] ON mytable;

创建唯一索引：

1
CREATE UNIQUE INDEX indexName ON mytable(username(length))

使用Alter 命令添加和删除索引：

1
2
ALTER TABLE tbl_name ADD INDEX index_name (column_list) # 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list) # 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

显示索引信息：

1
SHOW INDEX FROM table_name;

临时表

MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，MySQL 会自动删除表并释放所有空间。
创建临时表：

1
CREATE TEMPORARY TABLE table_name (column_name column_type);

删除临时表：

1
DROP TABLE table_name;

复制表

需要完全的复制MySQL 的数据表，包括表的结构、索引、默认值等。 如果仅仅使用CREATE TABLE … SELECT 命令，是无法实现的。
如何完整的复制MySQL数据表，步骤如下：

  • 使用SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句，该语句包含了原数据表的结构、索引等。
  • 复制以下命令显示的SQL语句，修改数据表名，并执行SQL语句，通过以上命令 将完全的复制数据表结构。
  • 想复制表的内容，可以使用 INSERT INTO … SELECT 语句来实现。

序列

MySQL 序列是一组整数：1, 2, 3, …，由于一张数据表只能有一个字段自增主键， 如果想实现其他字段也实现自动增加，就可以使用MySQL 序列来实现。

  1. AUTO_INCREMENT
    MySQL 中最简单使用序列的方法就是使用AUTO_INCREMENT 来定义列。

  2. 重值序列

    1
    2
    ALTER TABLE table_name DROP id;
    ALTER TABLE table_name ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id);

  3. 设置序列开始值
    在表创建成功后，通过以下语句来实现：

    1
    ALTER TABLE table_name AUTO_INCREMENT = 100;

SQL 注入

通过网页获取用户输入的数据并将其插入一个MySQL 数据库，那么就有可能发生SQL 注入安全的问题。
所谓SQL 注入，就是通过把SQL 命令插入到Web 表单递交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL 命令。
防止SQL注入，我们需要注意以下几个要点：

  • 永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式或限制长度,对单引号和 双”-“进行转换等。
  • 永远不要使用动态拼装sql ，可以使用参数化的sql 或者直接使用存储过程进行数据查询存取。
  • 永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。
  • 不要把机密信息直接存放，加密或者hash 掉密码和敏感的信息。
  • 应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装
  • SQL 注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用SQL 注入检测工具jsky ，网站平台就有亿思网站安全平台检测工具MDCSOFT SCAN 等。

导入、导出数据

导出语句：

  • 基本导出
    ：MySQL 中你可以使用SELECT … INTO OUTFILE 语句来简单的导出数据到文本文件上。
    1
    2
    SELECT * FROM runoob_tbl INTO OUTFILE '/tmp/vgbh.txt';
    SELECT * FROM passwd INTO OUTFILE '/tmp/runoob.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'; # 导出csv 格式
  • mysqldump 导出数据：
    1
    2
    mysqldump -u root -p \-\-no-create-info \-\-tab=/tmp RUNOOB runoob_tbl # 原始数据
    mysqldump -u root -p vgbh table_name > vgbh.txt # SQL 格式数据

导入语句：

  • MySQL 命令导入：
    1
    mysql -uroot -p123456 < vgbh.sql
  • source 命令导入：
    1
    source /home/abc/vgbh.sql # 导入备份数据库
  • LOAD DATA 导入数据
    1
    LOAD DATA LOCAL INFILE 'vgbh.txt' INTO TABLE table_name;
  • 使用mysqlimport 导入数据
    1
    mysqlimport -u root -p \-\-local database_name vgbh.txt # mysqlimport 使用简介请自查

运算符、函数

MySQL 有很多内置函数，主要有以下分类：

  • 字符串函数。
  • 数字函数。
  • 日期函数。

有使用需求的可以自查！
MySQL 的运算符及运算符的优先级，主要有以下几种运算符：

  • 算术运算符。(+、-、*、/、%)
  • 比较运算符。(=、<>、!=、>、<、<=、>=、BETWEEN、NOT BETWEEN、IN、NOTIN、<=>、LIKE、REGEXP、IS NULL、IS NOT NULL)
  • 逻辑运算符。(NOT、AND、OR、XOR)
  • 位运算符。(&、|、^、!、<<、>>)

具体的运算符优先级在使用时可自查！

日志

MySQL 共有六种日志文件，分别是：

  • 重做日志(redo log)
  • 回滚日志(undo log)
  • 二进制日志(bin log)
  • 错误日志(error log)
  • 慢查询日志(slow query log)
  • 一般查询日志(general log)
  • 中继日志(relay log)

其中重做日志和回滚日志与事务操作息息相关，二进制日志也与事务操作有一定的关系。

重做日志
  1. 作用
    确保事务的持久性。
    防止在发生故障的时候，尚有脏页未写入磁盘，在重启MySQL 服务的时候，根据redo log 进行重做，从而达到事务的持久性这一特征。
  2. 内容
    物理格式的日志，记录的是物理数据页面的修改信息，其中redo log 是顺序写入reod log file 的物理文件中去的。
  3. 产生时间
    事务开始之后就产生redo log ，redo log 的落盘并不是随着事物的提交才写入的，而是在事务的执行过程中，便开始写入的redo log 文件中。
  4. 释放时间
    当对应事务的脏页写入到磁盘中，redo log 的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。
  5. 物理文件
    默认情况下，对应的物理文件位于数据库的data 目录下的ib_logfile1 & ib_logfile2 。
    innodb_log_group_home_dir 指定日志文件组所在的路径，默认 / ，表示在数据库的数据目录下。
    innodb_log_files_in_group 指定重做日志文件族中文件的数量，默认2 。
    关于文件的大小和数量，由以下两个参数配置innodb_log_file_size 重做日志文件的大小。
    innodb_mirrored_log_groups 指定了日志镜像文件组的数量，默认1 。
  6. 其他
    即使事务没有提交，Innodb 存储引擎仍然每秒会将重做日志缓存刷新到重做日志文件。
回滚日志
  1. 作用
    保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读(MVVC) ，也即非锁定读。
  2. 内容
    逻辑格式的日志，在执行undo 的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，这一点市不同于redo log 的。
  3. 产生时间
    事务开始之前，简单强势的版本生成undo log ，undo 也会产生redo 来保证undo log 的可靠性。
  4. 释放时间
    当事务提交之后，undo log 并不能立马被删除。
    而是被放入到了待清理的链表中，由purge 线程判断是否由其他事务在使用undo 段中表的上一个事务之前的版本信息，决定是否可以清理undo log 的日志空间。
  5. 物理文件
    MySQL 5.6 之前，undo 表空间位于共享表空间的回滚段中，共享表空间的默认的名称是ibdata ，位于数据文件目录中。
    MySQL 5.6 之后，undo 表空间可以配置为独立的文件，但是提前需要在配置文件中配置，完成数据库初始化后生效且不可改变undo log 文件的个数。
    MySQL 5.7 之后的独立undo 表空间配置参数如下：
  • innodb_undo_directory = /data/undospace/ undo 独立表空间的存放目录
  • innodb_undo_log = 128 回滚字段为128KB
  • innodb_undo_tablespace = 4 指定有四个undo log 文件
  1. 其他
    undo 是在事务开始之前保存的被修改数据的一个版本，产生undo 日志的时候，同样会伴随类似于保护事务持久化机制的redo log 的产生。
    默认情况下undo 文件是保持在共享表空间的，也即ibdatafile 文件中，当数据库中发生一些大的事务性操作时，要生成大量的undo 信息，全部保存在共享表空间中。
    因此共享表空间会很大，默认情况下，被撑大的共享表空间是不会自动收缩的。
二进制日志
  1. 作用
    用于复制，在主从复制中，从库利用主库上的 bin log 进行重播，实现主从同步。
    用于数据库的基于时间点的还原。
  2. 内容
    逻辑格式的日志，可以认为是执行过的事务的sql 语句。但是又不是sql 语句，还包含了执行sql 语句的反向的信息。
    因此可以基于bin log 做到类似与oracle 的闪回功能，其实都是依赖bin log 中的日志记录。
  3. 产生时间
    事务提交的时候，一次性将事务中的sql 语句按照一定的格式记录到bin log 中去。
    因此对于事务的提交，即便是较大的事务，提交都是很快的，但是在开启bin log 的时候，对于较大的事务，可能会变得比较慢一点。
  4. 释放时间
    bin log 的默认保持时间是由参数expire_logs_days 配置，也就是说对于非活动的日志文件，在生成时间超过expire_logs_days 配置的时间后，会被自动删除。
  5. 物理文件
    配置文件的路径为log_bin_basename ，bin log 日志文件按照指定大小，当达到指定的大小时，会进行滚动更新，生成新的日志文件。
  6. 其他
    二进制日志的作用之一是还原数据库的，这与redo log 很类似，但还是有本质的区别：
  • 作用不同。
  • 内容不同。
  • 释放时间不同。
  • 恢复数据的效率也不同。

最后，MySQL 通过两阶段的提交过程来完成事务的一致性，也即 redo log 和 bin log 的一致性，理论上先写 redo log，再写 bin log，两个日志都提交成功，事务才算是真正的完成。

中继日志
  1. 作用
    MySQL 进行主主复制或主从复制的时候会在配置文件制定的目录下面产生相应的 relay log。从服务器 I/O 线程将主服务器的二进制日志（bin log）读取过来记录到从服务器本地文件，然后 SQL 线程会读取 relay log 日志的内容并应用到从服务器。
    31243e7d6299e4448257529b78f82072.jpg

优化

架构

框架

1.jpg
第一层为客户端的连接认证，C/S都有此架构。
第二层为服务器层，包含MySQL的大多数核心服务功能。
第三层包含了存储引擎，服务器通过API 与其通信，API 规避了不同存储引擎的差异，不同存储引擎也不会互相通信。另外存储引擎不会去解析SQL(InnoDB 是例外，它会解析外键定义，因为服务器本身没有实现该功能)。

连接管理和安全

每个客户端在服务器进程中拥有一个线程。
服务器会负责缓存线程，不需要为每一个新建的连接创建或销毁线程(5.5以后版本提供了线程池，可使用少量线程来服务大量连接)。
服务器基于用户名、原始主机信息和密码对客户端进行认证，连接成功后会验证某个特定操作的权限。
​

优化和执行

MySQL 会解析查询，创建内部数据结构(解析树)，并对其进行各种优化(重写查询、决定表的读取顺序、选择适合的索引)。
用户可以通过特殊的关键字提示(hint )优化器，影响MySQL 的决策过程。也可以请求优化器解释(explain )优化过程的各个因素，便于用户重构查询和schema ，修改相关配置。
优化器不关心表使用的存储引擎，但是存储引擎对优化查询有影响。优化器会请求存储引擎提供容量或某个具体操作的开销信息，已经表数据的统计信息等。
对于SELECT 语句，在解析查询前，服务器会先检查查询缓存(Query Cache)。

存储引擎

MyISAM

MySQL5.1及之前版本的默认存储引擎。支持全文索引、压缩、空间函数(GIS)，不支持事务和行级锁，并且崩溃后无法安全恢复。
对于只读数据，或者表比较小，可以忍受修复(repair )操作，可以考虑MyISAM。
存储：表以.MYD 和.MYI 的数据文件和索引文件存储在文件系统。
特性：

  • 加锁与并发: 对整张表而不是特定行加锁。读取时对读到的表加共享锁，写入时则加排它锁。支持并发插入(CONCURRENT INSERT )，在读取查询的同时，也可以插入新的数据。
  • 修复: 与事务恢复以及崩溃恢复是不同的概念。速度慢，可能会导致数据丢失。通过CHECK TABLE mytable 检查表的错误，REPAIR TABLE mytable 进行修复。
  • 索引特性: 支持全文索引，这是基于分词创建的索引。即使是BOLB 和TEXT 等长字段，也可以基于前500个字符创建索引。
  • 延迟更新索引键(Delayed Key Write): 如指定了DELAY_KEY_WRITE ，每次修改执行完成时，不会将修改的索引数据写入磁盘而是写到内存中的键缓存区(in-memory key buffer)，只有在清理键缓存区或关闭表的时候才会写入磁盘。可极大提升写入性能，但可能在数据库或主机崩溃时造成索引损坏而执行修复操作。

压缩表：只进行读操作可采用压缩表，极大减少磁盘占用空间以及IO ，从而提升查询性能。
性能：设计简单，数据以紧密格式存储，在某些场景下的性能很好。最典型的性能问题为表锁。

InnoDB

MySQL 默认事务型存储引擎，拥有良好的性能和自动崩溃恢复特性。
设计目的：处理大量的短期(short-lived )事务(短期事务大部分情况是正常提交的，很少被回滚)。
概览：

  • 数据存储在表空间(tablespace )中，由InnoDB 管理的黑盒子，有一系列的数据文件组成。
  • 采用MVVC 支持高并发，实现四个标准的隔离级别，默认为REPEATABLE READ ，并且通过间隙锁(next-key locking )策略使得InnoDB 锁定查询涉及的行，还会对索引中的间隙进行锁定，防止幻读出现。
  • 基于聚簇索引建立，对主键查询有很高的性能。但是二级索引(secondary index ，非主键索引)必须包含主键列。如主键索引过大，其它的所有索引都会很大。
  • 从磁盘读取数据采用可预测性预读、自动在内存中创建hash 索引以加速读操作的自适应索引(adaptive hash index )，加速插入操作的插入缓冲区(insert buffer )。
  • 通过一些机制和工具支持真正的热备份。
MyISAM 与InnoDB 对比

MyISAM 更适合读密集的表，而InnoDB更适合写密集的表。
因此在数据库做到主从分离、读写分离之后，为了进一步的提高数据库的性能，可以考虑读取频率较高的库使用MyISAM 作为读取存储引擎，那么写入频率较高的库可以使用InnoDB 作为写入存储引擎。

其他引擎
  1. Archive 引擎：非事务型对告诉插入和压缩做优化的引擎。支持INSERT、SELECT 和索引，每次SELECT 都需要全表扫描，并阻止其它SELECT 执行，以实现一致性读。支持行级锁和专用缓冲区，实现高并发插入。适合日志和数据采集类应用。
  2. Blackhole 引擎：没有实现任何的存储机制，因为它丢弃所有插入的数据，不做保存，但是服务器会记录Blackhole 表的日志，所以可以用于复制数据到备库，或者简单地记录到日志。适合特殊的复制架构和日志审核，但并不推荐。
  3. CSV 引擎：将CSV 文件作为MySQL 表来处理，但不支持索引。适合作为一种数据交换的机制。
  4. Federated 引擎：访问其它MySQL 服务器的代理，创建一个远程MySQL 的客户端连接，将查询传到远程服务器执行，然后提取或发送需要的数据。
  5. Memory 引擎：
  • 所有数据保存在内存中，不需要磁盘IO ，比MyISAM 快一个数量级。
  • 支持Hash索引，但是是表级锁，因此并发写入性能低
  • 不支持BOLB 和TEXT 的列，并且每行的长度是固定的。
  • 适合快速地访问数据，并且这些数据不会修改，重启以后丢失也没关系(数据会丢失，表结构仍保留)。
  1. Merge引擎：弃用。
  2. NDB集群引擎：参加MySQL 集群。

规范

命令规范

所有数据库对象名称必须使用小写字母并用下划线分割。
所有数据库对象名称禁止使用 mysql 保留关键字。（如果表名中包含关键字查询时，需要将其用单引号括起来）
数据库对象的命名要能做到见名识意，并且最后不要超过 32 个字符。
临时库表必须以 tmp_ 为前缀并以日期为后缀，备份表必须以 bak_ 为前缀并以日期(时间戳)为后缀。
所有存储相同数据的列名和列类型必须一致。（一般作为关联列，如果查询时关联列类型不一致会自动进行数据类型隐式转换，会造成列上的索 引失效，导致查询效率降低）

基本设计规范

  1. 所有表必须使用 Innodb 存储引擎
    没有特殊要求（即 Innodb 无法满足的功能如：列存储，存储空间数据等）的情况下，所有表必须使用 Innodb 存储引擎（mysql5.5之前默认使用Myisam，5.6以后默认的为Innodb）Innodb 支持事务，支持行级锁，更好的恢复性，高并发下性能更好

  2. 数据库和表的字符集统一使用UTF8
    兼容性更好，统一字符集可以避免由于字符集转换产生的乱码，不同的字符集进行比较前需要进行转换会造成索引失效

  3. 所有表和字段都需要添加注释
    使用comment从句添加表和列的备注 从一开始就进行数据字典的维护

  4. 尽量控制单表数据量的大小，建议控制在500万以内
    500万并不是MySQL数据库的限制，过大会造成修改表结构，备份，恢复都会有很大的问题
    可以用历史数据归档（应用于日志数据），分库分表（应用于业务数据）等手段来控制数据量大小

  5. 谨慎使用MySQL分区表
    分区表在物理上表现为多个文件，在逻辑上表现为一个表 谨慎选择分区键，跨分区查询效率可能更低 建议采用物理分表的方式管理大数据

  6. 尽量做到冷热数据分离，减小表的宽度
    MySQL限制每个表最多存储4096列，并且每一行数据的大小不能超过65535字节 减少磁盘IO,保证热数据的内存缓存命中率（表越宽，把表装载进内存缓冲池时所占用的内存也就越大,也会消耗更多的IO） 更有效的利用缓存，避免读入无用的冷数据 经常一起使用的列放到一个表中（避免更多的关联操作）

  7. 禁止在表中建立预留字段
    预留字段的命名很难做到见名识义 预留字段无法确认存储的数据类型，所以无法选择合适的类型 对预留字段类型的修改，会对表进行锁定

  8. 禁止在数据库中存储图片，文件等大的二进制数据
    通常文件很大，会短时间内造成数据量快速增长，数据库进行数据库读取时，通常会进行大量的随机IO 操作，文件很大时，IO 操作很耗时 通常存储于文件服务器，数据库只存储文件地址信息

  9. 禁止在线上做数据库压力测试

  10. 禁止从开发环境，测试环境直接连接生成环境数据库

字段设计规范

  1. 优先选择符合存储需要的最小的数据类型
    列的字段越大，建立索引时所需要的空间也就越大，这样一页中所能存储的索引节点的数量也就越少也越少，在遍历时所需要的IO 次数也就越多，索引的性能也就越差。

  2. 避免使用TEXT、BLOB数据类型，最常见的TEXT类型可以存储64k的数据

  • 建议把BLOB或是TEXT列分离到单独的扩展表中
    Mysql内存临时表不支持TEXT、BLOB这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行。
    而且对于这种数据，Mysql还是要进行二次查询，会使sql性能变得很差，但是不是说一定不能使用这样的数据类型。
    如果一定要使用，建议把BLOB或是TEXT列分离到单独的扩展表中，查询时一定不要使用select * 而只需要取出必要的列，不需要TEXT列的数据时不要对该列进行查询。
  • TEXT或BLOB类型只能使用前缀索引
    因为MySQL对索引字段长度是有限制的，所以TEXT类型只能使用前缀索引，并且TEXT列上是不能有默认值的。
  1. 避免使用ENUM类型
  • 修改ENUM值需要使用ALTER语句
  • ENUM类型的ORDER BY操作效率低，需要额外操作
  • 禁止使用数值作为ENUM的枚举值
  1. 尽可能把所有列定义为NOT NULL
  • 索引NULL列需要额外的空间来保存，所以要占用更多的空间；
  • 进行比较和计算时要对NULL值做特别的处理
  1. 使用TIMESTAMP（4个字节）或DATETIME类型（8个字节）存储时间
    TIMESTAMP 存储的时间范围 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07。
    TIMESTAMP 占用4字节和INT相同，但比INT可读性高
    超出TIMESTAMP取值范围的使用DATETIME类型存储。
    经常会有人用字符串存储日期型的数据（不正确的做法）：
  • 缺点1：无法用日期函数进行计算和比较
  • 缺点2：用字符串存储日期要占用更多的空间
  1. 同财务相关的金额类数据必须使用decimal类型
  • 非精准浮点：float,double
  • 精准浮点：decimal
    Decimal类型为精准浮点数，在计算时不会丢失精度。占用空间由定义的宽度决定，每4个字节可以存储9位数字，并且小数点要占用一个字节。可用于存储比bigint更大的整型数据。
索引设计规范

  1. 限制每张表上的索引数量，建议单张表索引不超过5个
    索引并不是越多越好！索引可以提高效率同样可以降低效率。
    索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率。
    因为mysql优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加mysql优化器生成执行计划的时间，同样会降低查询性能。

  2. 禁止给表中的每一列都建立单独的索引
    5.6版本之前，一个sql只能使用到一个表中的一个索引，5.6以后，虽然有了合并索引的优化方式，但是还是远远没有使用一个联合索引的查询方式好

  3. 每个Innodb表必须有个主键
    Innodb是一种索引组织表：数据的存储的逻辑顺序和索引的顺序是相同的。
    每个表都可以有多个索引，但是表的存储顺序只能有一种 Innodb是按照主键索引的顺序来组织表的。
    不要使用更新频繁的列作为主键，不适用多列主键（相当于联合索引） 不要使用UUID、MD5、HASH、字符串列作为主键（无法保证数据的顺序增长）。
    主键建议使用自增ID值。

索引列建议

出现在SELECT、UPDATE、DELETE语句的WHERE从句中的列

  • 包含在ORDER BY、GROUP BY、DISTINCT中的字段
    并不要将符合1和2中的字段的列都建立一个索引，通常将1、2中的字段建立联合索引效果更好
  • 多表join的关联列
选择索引列的顺序

建立索引的目的是：希望通过索引进行数据查找，减少随机IO，增加查询性能 ，索引能过滤出越少的数据，则从磁盘中读入的数据也就越少。

  • 区分度最高的放在联合索引的最左侧（区分度=列中不同值的数量/列的总行数）；
  • 尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO性能也就越好）；
  • 使用最频繁的列放到联合索引的左侧（这样可以比较少的建立一些索引）。
建立冗余索引和重复索引

因为这样会增加查询优化器生成执行计划的时间。

  • 重复索引示例：primary key(id)、index(id)、unique index(id)
  • 冗余索引示例：index(a,b,c)、index(a,b)、index(a)
考虑覆盖索引

对于频繁的查询优先考虑使用覆盖索引。
覆盖索引：就是包含了所有查询字段(where、select、ordery by、group by 包含的字段)的索引。
覆盖索引的好处：

  • 避免Innodb表进行索引的二次查询
    Innodb是以聚集索引的顺序来存储的，对于Innodb 来说，二级索引在叶子节点中所保存的是行的主键信息。
    如果是用二级索引查询数据的话，在查找到相应的键值后，还要通过主键进行二次查询才能获取我们真实所需要的数据。而在覆盖索引中，二级索引的键值中可以获取所有的数据，避免了对主键的二次查询 ，减少了IO操作，提升了查询效率。
  • 可以把随机IO变成顺序IO加快查询效率
    由于覆盖索引是按键值的顺序存储的，对于IO密集型的范围查找来说，对比随机从磁盘读取每一行的数据IO要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的IO转变成索引查找的顺序IO。
索引SET 规范

尽量避免使用外键约束

  • 不建议使用外键约束（foreign key），但一定要在表与表之间的关联键上建立索引；
  • 外键可用于保证数据的参照完整性，但建议在业务端实现；
  • 外键会影响父表和子表的写操作从而降低性能。
SQL 开发规范

  1. 建议使用预编译语句进行数据库操作
    预编译语句可以重复使用这些计划，减少SQL编译所需要的时间，还可以解决动态SQL所带来的SQL注入的问题 只传参数，比传递SQL语句更高效 相同语句可以一次解析，多次使用，提高处理效率。

  2. 避免数据类型的隐式转换
    隐式转换会导致索引失效。如：select name,phone from customer where id = ‘111’;

  3. 充分利用表上已经存在的索引

  • 避免使用双%号的查询条件。
  • 一个SQL只能利用到复合索引中的一列进行范围查询。
  • 使用left join或 not exists来优化not in 操作，因为not in 也通常会使用索引失效。

  1. 数据库设计时，应该要对以后扩展进行考虑

  2. 程序连接不同的数据库使用不同的账号，进制跨库查询

  • 为数据库迁移和分库分表留出余地
  • 降低业务耦合度
  • 避免权限过大而产生的安全风险
  1. 禁止使用SELECT * 必须使用SELECT <字段列表> 查询
  • 消耗更多的CPU和IO以网络带宽资源
  • 无法使用覆盖索引
  • 可减少表结构变更带来的影响

  1. 禁止使用不含字段列表的INSERT 语句
    例如：

    1
    insert into values ('a','b','c');

    应使用:

    1
    insert into t(c1,c2,c3) values ('a','b','c');

  2. 避免使用子查询，可以把子查询优化为join 操作
    通常子查询在in子句中，且子查询中为简单SQL(不包含union、group by、order by、limit 从句)时，才可以把子查询转化为关联查询进行优化。
    子查询性能差的原因：

  • 子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能 会受到一定的影响；
  • 特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大；
  • 由于子查询会产生大量的临时表也没有索引，所以会消耗过多的CPU和IO资源，产生大量的慢查询。

  1. 避免使用JOIN关联太多的表
    对于Mysql来说，是存在关联缓存的，缓存的大小可以由join_buffer_size参数进行设置。
    在Mysql中，对于同一个SQL多关联（join）一个表，就会多分配一个关联缓存，如果在一个SQL中关联的表越多，所占用的内存也就越大。
    如果程序中大量的使用了多表关联的操作，同时join_buffer_size设置的也不合理的情况下，就容易造成服务器内存溢出的情况，就会影响到服务器数据库性能的稳定性。
    同时对于关联操作来说，会产生临时表操作，影响查询效率Mysql最多允许关联61个表，建议不超过5个。

  2. 减少同数据库的交互次数
    数据库更适合处理批量操作 合并多个相同的操作到一起，可以提高处理效率

  3. 对应同一列进行or 判断时，使用in 代替or
    in 的值不要超过500个in 操作可以更有效的利用索引，or 大多数情况下很少能利用到索引。

  4. 禁止使用order by rand() 进行随机排序
    会把表中所有符合条件的数据装载到内存中，然后在内存中对所有数据根据随机生成的值进行排序，并且可能会对每一行都生成一个随机值，如果满足条件的数据集非常大，就会消耗大量的CPU和IO及内存资源。
    推荐在程序中获取一个随机值，然后从数据库中获取数据的方式。

  5. WHERE从句中禁止对列进行函数转换和计算
    对列进行函数转换或计算时会导致无法使用索引。

  • 不推荐：
    1
    where data(create_time) = '20190806'
  • 推荐：
    1
    where create_time >= '20190806' and create_time < '20190807'
  1. 在明显不会有重复值时使用UNION ALL而不是UNION
  • UNION会把两个结果集的所有数据放到临时表中后再进行去重操作
  • UNION ALL不会再对结果集进行去重操作
  1. 拆分复杂的大SQL为多个小SQL
  • 大SQL：逻辑上比较复杂，需要占用大量CPU进行计算的SQL
  • MySQL：一个SQL只能使用一个CPU进行计算
  • SQL拆分后可以通过并行执行来提高处理效率
操作行为规范
  1. 超100万行的批量写（UPDATE、DELETE、INSERT）操作，要分批多次进行操作
  • 大批量操作可能会造成严重的主从延迟
    主从环境中，大批量操作可能会造成严重的主从延迟，大批量的写操作一般都需要执行一定长的时间，而只有当主库上执行完成后，才会在其他从库上执行，所以会造成主库与从库长时间的延迟情况
  • binlog日志为row格式时会产生大量的日志
    大批量写操作会产生大量日志，特别是对于row格式二进制数据而言，由于在row格式中会记录每一行数据的修改，我们一次修改的数据越多，产生的日志量也就会越多，日志的传输和恢复所需要的时间也就越长，这也是造成主从延迟的一个原因。
  • 避免产生大事务操作
    大批量修改数据，一定是在一个事务中进行的，这就会造成表中大批量数据进行锁定，从而导致大量的阻塞，阻塞会对MySQL的性能产生非常大的影响。
    特别是长时间的阻塞会占满所有数据库的可用连接，这会使生产环境中的其他应用无法连接到数据库，因此一定要注意大批量写操作要进行分批。
  1. 对于大表使用pt-online-schema-change修改表结构
  • 避免大表修改产生的主从延迟
  • 避免在对表字段进行修改时进行锁表
    对大表数据结构的修改一定要谨慎，会造成严重的锁表操作，尤其是生产环境，是不能容忍的。
    pt-online-schema-change它会首先建立一个与原表结构相同的新表，并且在新表上进行表结构的修改，然后再把原表中的数据复制到新表中，并在原表中增加一些触发器。
    把原表中新增的数据也复制到新表中，在行所有数据复制完成之后，把新表命名成原表，并把原来的表删除掉。
    把原来一个DDL操作，分解成多个小的批次进行。

  1. 禁止为程序使用的账号赋予super权限
    当达到最大连接数限制时，还运行1个有super权限的用户连接super权限只能留给DBA处理问题的账号使用。

  2. 对于程序连接数据库账号，遵循权限最小原则
    程序使用数据库账号只能在一个DB下使用，不准跨库 程序使用的账号原则上不准有 drop 权限。

语法优化

  1. 在查询时，避免全表查询，应当使用where或者order by 上所涉及的列建立索引。
    索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。
    索引的一个主要目的就是加快检索表中数据的方法，亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。

  2. 在查询时，避免使用!=或者<>，这类的运算符会避开索引对全表进行查找。

  3. 避免使用null值进行判断查找数据，这样会遍历整个表。

    1
    select * from user where age = null;

    为避免这种情况，可以设置默认值为0。

  4. 避免在sql查询语句中添加or。

    1
    select * from user where id = 1 or 1 = 1;

  5. 下面的查询也将导致全表扫描：

    1
    select id from t where name like '%abc%'

    若要提高效率，可以考虑全文检索。

  6. in和not in 也要慎用，否则会导致全表扫描，如：

    1
    select id from t where num in(1,2,3)

    对于连续的数值，能用between 就不要用in 。

    1
    select id from t where num between 1 and 3

  7. 如果在where 子句中使用参数，也会导致全表扫描。
    因为SQL 只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时，它必须在编译时进行选择。
    然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。
    如下面语句将进行全表扫描：

    1
    select id from t where num=@num

    可以改为强制查询使用索引：

    1
    select id from t with(index(索引名)) where num=@num

  8. 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

    1
    select id from t where num/2=100

    应改为:

    1
    select id from t where num=100\*2

  9. 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

    1
    2
    select id from t where substring(name,1,3)='abc'--name以abc开头的id
    select id from t where datediff(day,createdate,'2005-11-30')=0--'2005-11-30'生成的id

    应改为:

    1
    2
    select id from t where name like 'abc%'
    select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'

  10. 不要在where 子句中的 = 左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

  11. 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

  12. 不要写一些没有意义的查询，如需要生成一个空表结构：

    1
    select col1,col2 into t from t where 1=0

    这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：

    1
    create table #t(...)

  13. 很多时候用 exists 代替 in 是一个好的选择：

    1
    select num from a where num in(select num from b)

    用下面的语句替换：

    1
    select num from a where exists(select 1 from b where num=a.num)

  14. 使用SQL 代码前，可以先使用Explain 检测一下SQL 代码性能。

结构优化

  1. 并不是所有索引对查询都有效，SQL 是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL 查询可能不会去利用索引，如一表中有字段sex、male、female 几乎各一半，那么即使在sex 上建了索引也对查询效率起不了作用。

  2. 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

  3. 应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。
    若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

  4. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
    这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

  5. 尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

  6. 任何地方都不要使用 select * from t ，用具体的字段列表代替 * ，不要返回用不到的任何字段。

  7. 尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

  8. 避免频繁创建和删除临时表，以减少系统表资源的消耗。

  9. 临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

  10. 在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度，如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

  11. 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

  12. 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

  13. 使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

  14. 与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。
    在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

  15. 在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

  16. 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

  17. 尽量避免大事务操作，提高系统并发能力。

索引原理

B+Tree
资料引用

关于B-Tree 和 B+Tree 的资料简介：
https://vgbhfive.cn/B-Tree%E5%92%8CB-Tree%E7%9A%84%E6%8E%A2%E7%A7%98%E4%B9%8B%E6%97%85/

主从分库

在高并发环境下，如何保证数据库能抗住高并发的读和写操作，因此绝大部分都会基于MySQL 的主从复制（replication）来设计，包括常见的一主一从、双主模式、半同步复制（semi-sync replication）。

主从复制

常见的数据主从同步可以说成是同步的，但是这个过程上确实异步的，大概流程是这样的：

  • 在master 上提交事务后，写入binlog，返回事务成功标记。
  • 将binlog 发送到slave ，转储成relay log。
  • 在slave 上再将relay log 读取出来应用。

这其中的步骤1 和步骤3 之间是异步进行的，无需等待确认各自的状态，因此可以说MySQL 的主从复制是异步的。

半同步复制

MySQL semi-sync replication 在之前的基础上做了加强，整体流程变成了这样:

  • 首先，master 和slave 都需要启动semi-sync replication 模式。
  • 当某一个slave 连接上master 之后，会主动告知自己当前是否处于semi-sync replication 状态。
  • 在master 上提交事务，写入binlog，此时需要通知一个slave 来接收此事务，等待写入relay log成功并刷新到磁盘后，slave 向master 发送已完成此事务标记。
  • master 收到slave 的完成事务通知后，此时再返回事务成功标记。

所以，上述的流程就存在了一个关键点，rpl_semi_sync_master_timeout即salve 向master 发送通知时间限定值，如果超过此限定值，就会从半同步复制转化为主从复制。

配置

上面主要说了主从复制和半同步复制，那么在MySQL 中，一次事务的提交，需要写入undo、redo、binlog、写数据文件等等，那么在这个过程中，如果出现了crash，就有可能导致主从的数据不一致。

master 配置：

1
2
innodb_flush_log_at_trx_commit = 1     # 每个事务提交时候，把事务日志从缓存区写到日志文件中，并且刷新日志文件的数据到磁盘上
sync_binlog = 100                      # 将binlog 刷新到磁盘上

slave 配置：

1
2
3
master_info_repository = "TABLE"       # 确保在slave上和复制相关的元数据表也采用InnoDB引擎，受到InnoDB事务安全的保护
relay_log_info_repository = "TABLE"    # 确保在slave上和复制相关的元数据表也采用InnoDB引擎，受到InnoDB事务安全的保护
relay_log_recovery = 1                 # 开启relay log自动修复机制，发生crash时，会自动判断哪些relay log需要重新从master上抓取回来再次应用，以此避免部分数据丢失的可能性

经过上述的配置，就可以确保主从配置不会将数据丢失，但是这又不能保证主从数据库的数据一致性了。
但是，别灰心，毕竟有舍才有得嘛！

面试

SQL 攻击

由于浏览器的同源策略并不允许其他网站去访问别的网站的cookie，但并不限制</img>标签去别的网站下载图片，所以此时可以创建一个不可见的img ，将用户cookie 利用img 发送到本机服务器。
1.jpg
应对方法，采用HttpOnly 拒绝使用JavaScript 读取即可防止。

Explain

Explain 可以使用在SELECT、DELETE、INSERT、REPLACE、UPDATE 等语句中，执行的结果会在每一行显示用到的每一个表的详细信息。
简单的语句可能结果就一句，但是复杂的查询语句会有很多行。

使用

在SQL 语句前加上explain ，如：

1
explain SELECT * FROM users;

1.jpg

Explain 输出分析
  1. id: 是用来顺序标识整个查询中select 语句的，在嵌套查询中id越大的语句越先执行。
  2. select_type:查询语句类型。
  • simple: 简单的SELECT（不使用UNION或子查询）。
  • primary: 最外面的SELECT 。
  • union: UNION 中的第二个或更高的SELECT 语句
  • dependent union: UNION 中的第二个或更高的SELECT 语句，取决于外部查询。
  • union result: UNION 的结果。
  • subquery: 在子查询中首先选择SELECT 。
  • dependent subquery: 子查询中的第一个SELECT ，取决于外部查询。
  • derived: 派生表——该临时表是从子查询派生出来的，位于from 中的子查询。
  • uncacheable subquery: 无法缓存结果的子查询，必须为外部查询的每一行重新计算。
  • uncacheable union: 在UNION 中的第二个或更晚的选择属于不可缓存的子查询。
  1. table: 每一行引用的表名。
  2. partitions: 匹配的分区。
  3. type: 访问类型。从上到下效果依次降低：
  • system: const 的一种特例，表中只有一行数据。
  • const: 当确定最多只会有一行匹配的时候，MySQL 优化器会在查询前读取它而且只读取一次，因此非常快。使用主键查询往往就是const 级别的，非常高效。
  • eq_ref: 最多只返回一条符合条件的记录，通过使用在两个表有关联字段的时候。
  • ref: 通过普通索引查询匹配的很多行时的类型。
  • fulltext: 全文索引。
  • ref_or_null: 跟ref 类似的效果，不过多一个列不能null 的条件。
  • index_merge: 此连接类型表示使用了索引合并优化。在这种情况下，输出行中的key 列包含使用的索引列表，key_len 包含所用索引的最长key 部分列表。
  • unique_subquery: 在使用in 查询的情况下会取代eq_ref 。
  • range: 范围扫描，一个有限制的索引扫描。key 列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者IN 操作符，用常量比较关键字列时,可以使用range 。
  • index: 类似全表扫描，只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。
    如在Extra 列看到Using index，说明正在使用覆盖索引，只扫描索引的数据，它比按索引次序全表扫描的开销要小很多。
  • ALL: 全表扫描。
  1. possible_keys: MySQL 可能采用的索引，但是并不一定使用。
  2. key: MySQL 正真使用的索引名称。
  3. key_lens: 索引key 的长度。
  4. ref: 显示之前的表在key 列记录的索引中查找值所用的列或常量。
  5. rows: 预估的扫描行数，只能参考不准确。
  6. filtered: 查询的表行占表的百分比。
  7. extra: 该列包含了很多额外的信息，包括是否文件排序，是否有临时表等，很多时候这个字段很有用能提供很多信息。

技巧和案例

SQL SSM 实战

  1. 根据多条件随机条件查询

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    select * from t_user_info where 1 = 1
    <if test="userId != null and userId != ''">
       and userId = #{userId}
    </if>
    <if test="userName != null and userName != ''">
       and userName = #{userName}
    </if>
    <if test="userId != null and userId != ''">
       and userId IN
       <foreach item="userId" collection="userIds" index="index" open="(" separator="," close=")">
          #{userId}
       </foreach>
    </if>
    <if test="beginDate != null and beginDate != '' and endDate != null and endDate != ''">
       and date(CREATE_DATE) BETWEEN #{beginDate} AND #{endDate}
    </if>
    <if test="startRows != null and pageSize !=''">
       LIMIT #{startRows},#{pageSize}
    </if>

  2. 根据条件计数

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    select count(*) from t_user_info where 1 = 1
    <if test="userId != null and userId != ''">
       and userId = #{userId}
    </if>
    <if test="userName != null and userName != ''">
       and userName = #{userName}
    </if>
    <if test="startRows != null and pageSize !=''">
       LIMIT #{startRows},#{pageSize}
    </if>

  3. 多表联合查询

    1
    2
    3
    4
    SELECT a.*, b.* FROM (
       SELECT * FROM t_user_info WHERE id = #{id} 
    ) a,
    t_user_home_info b WHERE a.id = b.apply_id

读写分离

https://github.com/chenlei2/spring-boot-mybatis-rw

数据库减负

海量数据，光用数据库肯定是没法搞定的，所以可以从一下几个方面着手解决。

缓存

第一种解决方案就是缓存。
缓存，我们可以将数据直接缓存在内从中。例如Map 也可以使用缓存框架，如Redis 等，将一些需要频繁使用的热点数据保存在缓存中，每当用户来访问的时候，就可以直接将缓存中的数据返回给用户，这样可以有效降低服务器的压力。
另外可以缓存起来使用的数据，一般都不能对实时性要求太高。

页面静态化

页面静态化其实可以算作是缓存的另外一种形式，相当于直接将相关的页面渲染结果缓存起来。
首先大家知道，在我们的 Web 项目中，资源分为两大类：

  • 静态资源
  • 动态资源

静态资源就是我们常见的HTML、CSS、JavaScript 图片等资源，这些资源可以不经过服务端处理，就可以直接返回给前端浏览器，浏览器就可以直接显示出来。
动态资源则是指我们项目中的Servlet 接口、Jsp 文件、Freemarker 等，这些需要经过服务端渲染之后，才可以返回前端的资源。
在实际项目中，静态资源的访问速度要远远高于动态资源，动态资源往往很容易遇到服务器瓶颈、数据库瓶颈。
因此，对于一些不经常更新的页面，或者说更新比较缓慢的页面，我们可以通过页面静态化，将一个动态资源保存为静态资源，这样当服务端需要访问的时候，直接将静态资源返回，就可以避免去操作数据库了，降低数据库的压力。
一般来说，Freemarker、Velocity 等都有相关的方法可以帮助我们快速将动态页面生成静态页面。

数据库优化

很多时候程序跑得慢，不是因为设备落后，而是因为数据库SQL 写的太差劲。
要解决海量数据的问题，数据库优化肯定也是不可避免的。一般来说，我们可以从 SQL 优化、表结构优化、以及数据库分区分表等多个方面来对数据库进行优化。
数据库优化其实也是一门巨大的学问，以后有时间可以仔细琢磨琢磨。

热点数据分离

数据库中的数据，虽然是海量数据，但是这些数据并不见得所有数据都是活跃数据。例如用户注册，有的用户注册完就消失的无影无踪了，而有的用户则在不停的登录。
因此，对于这两种不同的用户，我们可以将活跃用户分离出来，在主要操作的数据表中只保存活跃用户数据。
每次用户登录，先去主表中查看有没有记录，有的话，直接登录，没有的话，再去查看其他表。

通过判断用户在某一段时间内的登录次数，就可以很快分离出热点数据。

合并数据库操作

这个方案的宗旨其实是减少数据库操作的次数。例如多次插入操作，我们可以合并成一条SQL 搞定。
多个不同条件的查询，如果条件允许的话，也可以合并成为一个查询，尽量减少数据库的操作，减少在网络上消耗，同时也降低数据库的压力。

数据库读写分离

数据库的读写分离一方面可以提高数据库的操作效率，另一方面也算是对数据库的一个备份。
具体的操作可以去看我下边的技巧与案例模块。

分布式数据库

数据库读写分离之后，无形中增大了代码的复杂度，所以一般还需要借助分布式数据库中间件，这样可以有效提高数据库的弹性，可以方便的随时为数据库扩容，同时也降低代码的耦合度。

NoSQL 和Hadoop

引入NoSQL 和Hadoop 也是解决方案之一。
NoSQL 突破了关系型数据库中对表结构、字段等定义的条条框框，使用户可以非常灵活方便的操作，另外NoSQL 通过多个存储块存储数据的特点，使得天然具备操作大数据的优势（快）。
不过，老实说，NoSQL 目前还是在互联网项目中比较常见，在传统的企业级应用中还是比较少见。
Hadoop 就不必说了，大数据处理利器。

引用

https://mp.weixin.qq.com/s/OZ0Uqv7UCiyhhRurNdVi4A
https://juejin.im/user/57cc22b87db2a2007898d792/posts -MySQL系列

个人备注

此博客内容均为作者学习所做笔记，侵删！
若转作其他用途，请注明来源！