Mysql数据库知识总结（看资料总结出来的）

一、基础知识

二、SQL优化与索引

三、数据库规范建议

四、数据库设计

五、数据库架构

一、基础知识

知识点主要包括：数据类型 常用函数 字符集 事务隔离级别 锁机制

（1）、数据类型

数值类型 -- TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT、 DECIMAL

时间类型 -- DATE、DATETIME、TIMESTAMP

字符串类型 – CHAR、VARCHAR、TEXT

特有类型 -- ENUM、SET

字符串区分大小写 -- VARBINARY

备注：

* 所有变长字符串全部使用 VARCHAR 类型，类似于状态，有限类别的字段，也使用可以比较明显表示出实际意义的字符串，而不应该使用INT之类的数字来代替；

* 固定长度的字符串使用 CHAR 类型，所有单个字符的全部使用 CHAR 类型，而不应该 使用VARCHAR 类型；

* 当字符数量可能超过 20000 个的时候，可以使用 TEXT 类型来存放字符类数据。所有使用 TEXT 类型的字段必须和原表进行分拆，与原表主键单独组成另外一个表进行存放；

* 需要精确到时间（年月日时分秒）的字段可以使用DATETIME 或TIMESTAMP，但请注 意各自能表达的范围，以及是否需要用到TIMESTAMP的特性，尽量使用TIMESTAMP 类型替代DATETIME以减少数据存储空间的占用；

* 只需要精确到天的字段全部使用 DATE 类型，而不应该使用 TIMESTAMP或DATETIME；

* 自增序列特性的字段只能使用 INT 或者 BIGINT，必须明确标识为无符号类型 (关键词：UNSIGNED)，除非确实会出现负数，仅当该字段的数值会超过42亿，才使用 BIGINT 类型，并且自增字段必须作为主键或主键的一部分；

（2）常用函数

包括：数学函数 字符串函数 日期操作函数 类型转换函数

* 数学函数：

ABS(value)：返回value的绝对值，例：ABS(-101)，返回：101；

FLOOR(value)：去掉value的小数，例：FLOOR(2013.8)，返回：2013；

MOD(N,M)：返回N除以M的余数，例：MOD(2013,10)，返回：3；

ROUND(value)：返回value的四舍五入值，例：FLOOR(2013.8)，返回：2014；

ROUND(value,num)：保留num位小数value的四舍五入值，例： FLOOR(2013.867,1)，返回：2013.9；

RAND()：返回随机数值；

* 字符串函数

字符串连接函数：MySQL中字符串连接方法，使用CONCAT() 或CONCAT_ WS()函数，语法如下： CONCAT(string1,string2,...) CONCAT_ WS(separator,string1,string2,..)

* 字符串长度统计

LENGTH(string) #返回string所占的字节数

CHAR_LENGTH(string) #返回string中的字符个数

统计字符个数，就不区分是汉字还是字母或数字，也跟字符集没有关系，若统计的是字节数，则由字符是汉字、字母或数字类型，以及字符集共同决定。

备注：

我们所有的MySQL数据库都将会采用UTF8编码，所以一个汉字占3个字节；英文输入法的字母或数字占一个字节。

* 字符串函数

字符串判断函数

IF(exp1,exp2,exp3)：若是exp1 为真，返回exp2；若是exp1为假，返回exp3；

IFNULL(exp1,exp2)：若是exp1 IS NOT NULL，返回exp1，否则返回exp2；

NULLIF(exp1,exp2)：若是exp1=exp2，返回NULL，否则返回exp1；

* 字符串替换函数

LTRIM(exp1)：去掉exp1中字符串开头的空格

RTRIM(exp1)：去掉exp1中字符串结尾的空格

TRIM(exp1)：去掉exp1中的开头和 结尾的空格

TRIM(exp2,exp1)：去除掉exp1中存在的字符串exp2

* 字符串查找函数

SUBSTRING_INDEX(exp1,delim,count)：exp1为字符串，delim为分割符号，count表示 第几个风格符号，例如： SUBSTRING_INDEX(‘yto56.com.cn’,‘.’,1)，返回：yto56

SUBSTRING(exp1,pos,len)： exp1为字符串，pos为位置，len为长度，例如： SUBSTRING(‘yto56.com.cn’,1,9)，返回：yto56.com

LOCAL(substr,str)：查找substr在str中第一个位置，例LCOAL(yto56.com.cn’,‘.’)，返回：6

* 其他字符串函数

UPPER(exp1)：把字符串exp1转换成大写；

LOWER(exp1)：把字符串exp1转换成小写；

* 日期操作函数

日期格式转换函数：

将字符串转换成日期方式，DATE_FORMAT() 或STR_TO_DATE()，

两个函数的格式如下：

DATE_FORMAT(expr1,format)

STR_TO_DATE(expr1, format)

常用的日期格式YYYY-MM-DD HH:MM:SS 对应的format为%Y-%m-%d %H:%i:%S

* 日期运算函数

CURDATE()：返回当天的日期，格式： YYYY-MM-DD

NOW()：返回当下的日期时间，格式： YYYY-MM-DD HH:MM:SS

DATE_ADD(date,INTERVAL expr unit)：expr 表达式，unit为单位，例如： DATE_ADD(‘2013-08-20’,INTERVAL -1 DAY)，返回：2013-08-19

* 日期运算函数

MONTY(date)：返回date的月份数，例如MONTY(‘2013-08-20’)，返回：8

DAY(date)：返回date的天数，例如DAY(‘2013-08-20’)，返回：20

YEAR(date)：返回date的年份数，例如YEAR(‘2013-08-20’)，返回：2013

TO_DAYS(date)：date对应的天数，例如TO_DAYS (‘2008-10-07’)，返回：733687

WEEK(date)：返回date对应周几，例如： WEEK(‘2013-08-20’)，返回：2

DATEDIFF(date1,date2)：返回date1-date2的天数差别，例如： DATEDIFF(‘2013-08-20’,’2013-08-18 13:30:14’)，返回：2

* 类型转换函数

CAST(expr AS type)：expr转换成某类型

CONVERT(expr , type) ： expr转换成某类型

CONVERT(expr USING transcoding_name)：expr字符集转换成 某字符集

* 字符集

对于无线应用用户可输入部分，则推荐使用utf8变种，即utf8mb4，四个字节存储。其他情况，默认统一使用字符集为UTF8，字符集校对规则为utf8_general_ci ：

一个中文汉字占3个字节

中文输入法下的一个符号、字符、数字，则存储空间为3个字节

英文输入法下的一个符号、字符、数字，则存储空间为1个字节

区分大小写不推荐设置校验规则utf8_bin，而是数据类型 VARBINARY

CHAR(N)或VARCHAR(N)中的N解释

MySQL数据库这两类字符串定义时填写的长度N，不是字节数的意思 ，而是字符数的意思，例：CREATE TABEL gl_user(username VARCHAR(40)); 则username最多能存储40个字符。

(3)、事务隔离级别

事务具有四个特性：原子性（Atomiocity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability），这四个特性简称ACID特性。

* 原子性 事务是数据库的逻辑工作单位，事务中包括的所有操作要么都做，要么都不做 一致性 事务执行的结果必须是使数据库从一个一致性的状态变到另外一个一致性状态

* 隔离性 一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对其他 事务是隔离的，并发执行的各个事务之间互相不干扰

* 持久性 一个事务一旦成功提交，对数据库中数据的修改就是持久性的。接下来其他的其他 操作或故障不应该对其执行结果有任何影响

（4）、事务隔离级别

* READ UNCOMMITED SELECT的时候允许脏读，即SELECT会读取其他事务修改而还没有提交的数据

* READ COMMITED SELECT的时候无法重复读，即同一个事务中两次执行同样的查询语句，若在第一次与第二次查询之间时间段，其他事务又刚好修改了其查询的数据且提交了，则两次读到的数据不一致

* REPEATABLE READ SELECT的时候可以重复读，即同一个事务中两次执行同样的查询语句，得到的数据始终都是一致的

* SERIALIZABLE 与可重复读的唯一区别是，默认把普通的SELECT语句改成SELECT …. LOCK IN SHARE MODE。即为查询语句涉及到的数据加上共享琐，阻塞其他事务修改真实数据

（5）锁机制

MySQL数据库中，目前主要是2种事务引擎：NDB分布式存储引擎、InnoDB存储引擎

NDB分布式存储引擎

只支持事务隔离级别：READ COMMITED，提供行锁

InnoDB存储引擎

支持四种隔离级别，其中默认的隔离级别为REPEATABLE READ

锁的实现是通过 索引实现的，无索引可走的UPDATE或DELETE会升级为表锁

隔离级别，也即数据可见性是通过MVCC实现

二、SQL优化与索引

知识点主要包括：索引的原理 索引的作用 优化的顺序 子查询优化 分页优化 左连接的正误写法 索引覆盖技术 索引创建准则

（1）、索引的原理

其实就是类似于一本字典，字典的目录就是就可以类比为索引，通过目录查询具体的内容类比于表里面的记录；

如图：

（2）、索引的作用：

* 去重

* 加速查找定位

* 避免排序

* 覆盖索引

（3）、优化的顺序

业务优化最优先，其次设计和程序优化，最后数据库和SQL优化等

SQL、索引和数据库端优化是需要随数据增长/数据分布而不断重新平衡的过程

（4）、子查询优化

原SQL语句

SELECT ID FROM T_EXP_OP_RECORD_TAKING_SUCCESS WHERE ORDERID NOT IN (SELECT ORDERID FROM T_EXP_OP_RECORD_TAKING_SUCCESS WHERE AUX_OP_CODE IN ('NEW','UPDATE','DELETE') AND EXP_TYPE = 10 AND OP_CODE IN(310,311)) LIMIT 10000;

优化后的SQL语句 SELECT ID FROM T_EXP_OP_RECORD_TAKING_SUCCESS WHERE AUX_OP_CODE NOT IN ('NEW','UPDATE','DELETE') OR EXP_TYPE != 10 OR OP_CODE NOT IN(310,311) LIMIT 10000;

总结 减少一次全表扫描 减少回表读数据10000次 只需要扫描表，满足条件的记录值达到10000条即停止扫描

（5）、左连接的正误写法

正确写法： SELECT M.columnname……,N.* columnname….. FROM left_table M LEFT JOIN right_table N ON M. columnname_join=N. columnname_join AND N. columnname=XXX WHERE M.columnname=XXX AND M.…..

解读： ON子句连接条件中M. columnname_join=N. columnname_join ，是用于把二个表中等值的记录连接在一起，但是不影响记录集的数量。若是表left_table中的某记录，无法在表right_table 找到对应的记录，则此记录依然显示在结果集中，表 right_table需要在查询结果集显示的列的值用NULL替代；

ON子句连接条件中表right_table. columnname=XXX用于控制right_table表是否有符合要求的列值还是用NULL替换的方式显示在查询列中，不影响记录集的数量；

WHERE子句控制记录是否符合查询要求，不符合则过滤掉； 常见错误写法之一： SELECT M.columnname……,N.* columnname….. FROM left_table M LEFT JOIN right_table N ON M. columnname_join=N. columnname_join AND N. columnname=XXX AND M.columnname=XXX ;

（6）、索引覆盖技术

索引覆盖的条件 SQL语句的WHERE 条件 是索引的一部分 SQL语句的ORDER 或GROUP 的字段是索引的一部分 SQL语句返回的值或计算元数据列是索引的一部分 总结 采用索引覆盖后，全部的运算通过索引完成 采用索引覆盖后，不需要访问元数据 示例 idx_uid_gmtcreate (uid,gmt_create) SELECT uid, gmt_create FROM tablename WHERE uid>100;

（7）、索引创建准则

单张表中索引数量不超过5个

单个索引中的字段数不超过5个

对字符串使用前缀索引，前缀索引长度不超过10个字符

索引结构和统计信息维护是需要高昂成本的，尽量提高索引复用率

复合索引中每个字段的筛选率

复合索引字段出现在所有SQL中的顺序信息，主要指WHERE、GROUP BY、ORDER BY等子句

复合索引中每个字段的前后排列顺序极其重要性

三、数据库规范建议

知识点包括：命名规范、 字段定义规范、 应用帐号权限 SELECT * 的使用规范、字段上添加函数使用规范、分页查询规范、数据类型转换规范、DML语句有索引可用、产品研发与上线流程建议

（1）、命名规范

* 索引命名：

MySQL索引名称大小写会造成优化器进行索引选择时出现性能问题，索引命名规则为全 部大写或全部小写，索引名称以idx_或IDX_开头，结尾为_u（或_U）或 _n(或_N)，其中： _U或_u表示唯一性索引，_n或_N表示普通索引，默认推荐索引名称小写的格式

* 对象命名大小写：

提高开发人员的效率，降低研发成本，规避程序的故障发生点，数据库对象：数据库名称 、表名称、存储过程名称、自定义函数名称、触发器名称、视图名称和任务调度名称都会默 认设置为不区分大小写，但建议统一使用小写

库名、表名、字段名建议使用小写字母，并采用下划线分割

库名、表名、字段名禁止超过40个字符。须见名知意

库名、表名、字段名禁止使用MySQL保留字 临时库、表名必须以tmp为前缀，并以日期为后缀

备份库、表必须以bak为前缀，并以日期为后缀

* 字段定义规范

字段属性强制定义为NOT NULL

字符串类型的字段属性默认值为空格，也即‘’

数字类型的字段属性默认值为0 时间类型的字段属性默认值为’1970-01-01 08:00:01’

若是出生年月日用处的字段，默认值为空格，也即‘’ 字段的数据约束除唯一性外，其他约束由应用程序保证

（2）、应用帐号权限

正常权限 为了减少数据安全方面的风险，故对应用帐号只授予四种数据库权限： SELECT、INSERT、UPDATE、DELETE 对于只读类型的应用，应授予只读帐号的权限为：SELECT；

特殊权限 鉴于我们有EVENT、PROCEDURE，可能需要授权相关的权限，则 授予的权限为：EXECUTE；

（3）、SELECT * 的使用规范

基本原则： 在不必要查询中使用“*”列出所有字段，且存在GROUP BY或ORDER BY的时候，禁止使用SELECT * 一次取出所有的字段。对于表连接的 JOIN 语句，禁止使用 SELECT * 来进行查询。含有 text 字段的表，当不需要取出 TEXT 字段的时候，也禁止使用SELECT * 进行查询.

详细说明：

1> 进行GROUP BY或ORDER BY的时候不允许使用 SELECT * 是为了确保 MySQL 能够使用最新的优化排序算法

2> JOIN 语句不允许使用 SELECT * 是为了防止仅仅只需要索引即可完成的查询需要回表取数

3> 存有 TEXT 字段表,在不需要取出TEXT字段的时候，不允许使用 SELECT * ,因为 TEXT 字段是存放在和普通记录不一样的物理位置，会造成大量的io操作

4> SELECT * 会增加CPU、IO开销和占用更多的网络带宽，也影响性能

5> 避免因增删字段而没有修改相关SQL及相关程序代码导致程序BUG，而禁用SELECT *

（4）、字段上添加函数使用规范

基本原则： 禁止在 WHERE 条件中出现的过滤字段上，使用任何函数进行类型或格式的转换；正确的做法是把传入比较的值转换为列类型所需要的。

错误的写法： SELECT uid , gmt_create FROM gl_user WHERE UPPER(username)=UPPER (‘YTO56.com ‘);

正确的写法： SELECT uid , gmt_create FROM gl_user WHERE username= UPPER (‘YTO56.com ‘);

（5）、分页查询规范

基本原则： 分页查询语句全部都需要带有排序条件，除非商业方明确要求不要使用任何排序来随机展示数据。详细说明：

1> 常规分页语句写法(start：起始记录数，page_offset：每页记录数)： SELECT ID,username FROM gl_user WHERE username like '%@163.com' ORDER BY M.gmt_create LIMIT start, page_offset;

2> 多表 Join 的分页语句，如果过滤条件在单个表上，需要先分页，再 JOIN 低性能写法： SELECT M.username,P.rolename FROM gl_user M INNER JOIN gl_role P ON M.ID=P.userid WHERE username like '%@163.com' ORDER BY M.gmt_create LIMIT start, page_offset; 高性能写法： SELECT M.username,P.rolename FROM (SELECT ID,username FROM gl_user WHERE username like '%@163.com' ORDER BY M.gmt_create LIMIT start, page_offset)M,gl_role P WHERE M.ID=P.userid;

这样写前提是关联表之间记录一一对应，否则可能会返回的记录数目少于或多于page_offset的值。

（6）、数据类型转换规范

基本原则： 在所有 Query 的 Where 条件中必须使用和过滤字段完全一致的数据类型，杜绝任何隐式类型转换，避免造成因为数据类型不匹配而导致 Query 执行计划的出错，造成性能问题.

详细说明：

1> 所有 Where 条件的字段上不允许使用函数做类型转换，如有需要转换类型，只能转换过滤值,而不是转换字段.

2> 最为常见的隐式类型转换常见于时间类型与字符串类型之间，建议所有时间类型字段在iBatis中均以时间类型传入，或者以字符串传入然后通过时间函数转换字符串为合法的时间格式 ，如下： SELECT * FROM member WHERE gmt_create=DATE_FORMATE('2009010101:02:03','%Y-%m-%d %H:%i:%s');

3> 在表连接 Query 中，如果连接条件两端的数据类型不一致，必须保证将驱动表的连接条件数据类型转换为与被驱动表一致的数据类型.

（7）、DML语句有索引可用

*DML语句是指：INSERT、UPDATE和DELETE，本文指后2种操作

（2）InnoDB存储引擎通过索引实现加锁，无索引会导致升级为表锁

（3）DML升级 为表级锁，且加的是排他锁，会堵塞同表的其他业务

（8）、产品研发与上线流程建议

四、数据库设计

知识点包括：字段类型 存储引擎 主键选型 主副表设计 大字段分离 冷热数据分离

（1）、字段类型

字段的值尽量数字化，数据类型尽量为整型

字段的存储空间长度，尽量使用短且满足要求的数据类型

时间类型尽量使用TIMESTAMP，除出生年月日使用DATETIME外

尽量不使用数据类型：TEXT 和 BLOB

字符串类型尽量使用VARCHAR，而不使用CHAR

用DECIMAL类型 替代 FLOAT和 DOUBLE类型

（2）、存储引擎

* InnoDB存储引擎：

支持事务 支持行锁和表锁 数据安全性强 BP支持同时缓存元数据和索引数据 支持MVCC 支持B+树，仅自适应索引为HASH 支持全文检索；

* MyISAM存储引擎：

KB只缓存索引数据 仅支持表锁，不支持事务 支持地理数据存储 支持全文检索；

* MEMORY存储引擎：

元数据和索引都缓存内存中 不支持事务，仅支持表锁 不支持数据持久化 支持B+树和HASH索引；

* Sphinx 支持全文索引：

支持中文分词 支持创建全量索引和增量索引 支持分布式部署；

（3）、主键选型

（4）、主副表设计

前置数据库的签收数据为例

t_exp_op_record_sgnature

t_exp_op_record_sgnature_deal

t_exp_op_record_sgnature_fail

t_exp_op_record_sgnature_success

t_exp_op_record_sgnature_success_02

t_exp_op_record_sgnature_success_03

t_exp_op_record_sgnature_unsuccess

t_exp_op_record_sgnature_success1

t_exp_op_record_sgnature_update

总结 字段数量最多为56个，最小为54个字段，相同字段数为54个 每张表字段值相同数目：52个 相同的字段值存在一张表，副表记录8种类型展开的数据

（5）、大字段分离

VARCHAR类型字段的长度小于255个字节，1个字节标记

VARCHAR类型字段的长度超过255个字节，2个字节标记

TEXT类型字段 ，1-4个字节标记，8个字节作为指针

TEXT类型字段的值是存储在隐形列中

总结： 大字段一般单独存储到一张表中，以及尽量用VARCHAR替代TEXT

（6）、冷热数据分离

适合冷热分离的场景：

表的数据字段存在大量不频繁访问 和 部分频繁访问的字段

表的数据字段存在频繁更改的字段 和 大量极少修改的字段

表的数据字段同时一起展示的量小或频率低

如何分离 ：把频繁访问和访问热度低的数据存储在2张表中，以某表的主键ID关联

把频繁修改的字段 和 极少修改的字段存储到不同表中

提高数据的访问速度和数据修改速度，减少IO操作

备注：冷热分离的场景一定要结合业务特点和实际的数据操纵情况

五、数据库架构

知识点包括：双主复制架构 一主多从架构 分布式数据库 伪分布式数据库

（1）、双主复制架构

（2）、一主多从架构

（3）、分布式数据库

（4）、伪分布式数据库架构