关系型数据库
关系型数据库的三范式
第一范式（1NF）: 每一列都是不可分割的原子数据项(基本类型列)
第二范式（2NF）： 要求实体的属性完全依赖于主关键字(无重复行)
第三范式（3NF）: 数据表不包含其它表已有的非主属性(无数据冗余)
关于数据库的规范设计，都会谈到是否符合三范式。但是考虑到数据库的性能优化，也不必都按照三范式来设计，可以做数据的适当冗余。如为了查询效率，在商品表，可以设置个img_url字段放图片的主图。
MySQL本质了解
mysql逻辑架构
1.最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
2.第二层架构主要完成大多的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。
3.存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。
4.数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。
MySQL核心模块
• Server Initialization Module 命令行、配置文件解析、内存分配 • Connection Manager 协议监听和协议转发 • Thread Manager 新建线程处理请求 • Connection Thread 新建新的,或者从线程缓存中去取 • User Authentication Module 验证用户身份 • Access Control Module 访问控制 • Parser 接收请求,解析进入命令分发或者进入查询 • Command Dispatcher 解析器分发给命令分发器 • Query Cache Module 查询缓存检查(SELECT、DELETE、UPDATE) • Optimizer 查询优化器 • Table Manager 打开表,获取锁 • Table Modification Modules 表更新 • Table Maintenance Module 表维护 • Status Reporting Module 状态报告 • Abstracted Storage Engine Interface (Table Handler) 抽象引擎接口 • Storage Engine Implementations (MyISAM, InnoDB...) 存储引擎实现 • Logging Module 日志记录 • Replication Master Module 复制主模块 • Replication Slave Module 复制从模块 • Client/Server Protocol API 客户端、服务器协议 API • Low-Level Network I/O API 底层网络 I/O API • Core API 核心 API
存储引擎的区别和选择
存储引擎是mysql提供的文件访问层的一个抽象接口来定制一种文件访问机制。
mysql的存储引擎包括:MyISAM、InnoDB、BDB、MEMORY、MERGE、
EXAMPLE、NDBCluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等。
MyISAM:不支持事务,支持全文索引,表级锁；数据文件全量备份，可以直接拷贝数据文件进行备份；适合处理读频率远大于写频率的静态表。
InnoDB:支持事务,5.6以后支持全文索引,默认行级锁；数据文件两种形式(单一文件形式和多文件形式,以共享表空间与独占表空间存储)；适用于高并发读写。
MySQL InnoDB的存储文件
参考：http://blog.csdn.net/chenjiayi_yun/article/details/45533909
MySQL插件
NoSql 插件 HandlerSocket
中文全文索引插件 mysqlcft （mysql5.7内置有n-gram parser插件）
InnoDB引擎中的Memcached插件
MySQL中的事务
事务的ACID:
• 原子性(Atomicity ) 全部执行或全部不执行
• 一致性( Consistency ) 事务前后数据都是一致性状态,约束等
• 隔离性或独立性( Isolation) 事务之间是独立的,和级别相关
• 持久性(Durabilily) 事务完成即持久化存储
MysSQL锁表类型
1- 表级锁(MyISAM)
开销小,加锁快;不会出现死锁;锁定粒 度大,发生锁冲突的概率最高,并发度最低
可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺：
mysql> show status like 'table%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Table_locks_immediate | 2979 | | Table_locks_waited | 0 | +-----------------------+-------+ 2 rows in set (0.00 sec))
如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。
MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。锁模式的兼容性如表20-1所示。
MySQL中的表锁兼容性
| 请求锁模式 是否兼容 当前锁模式 | None | 读锁 | 写锁 |
|---|---|---|---|
| 读锁 | 是 | 是 | 否 |
| 写锁 | 是 | 否 | 否 |
- 行级锁(InnoDB)
开销大,加锁慢;会出现死锁;锁定粒度最 小,发生锁冲突的概率最低,并发度也最高
可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：
mysql> show status like 'innodb_row_lock%'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | InnoDB_row_lock_current_waits | 0 | | InnoDB_row_lock_time | 0 | | InnoDB_row_lock_time_avg | 0 | | InnoDB_row_lock_time_max | 0 | | InnoDB_row_lock_waits | 0 | +-------------------------------+-------+ 5 rows in set (0.01 sec)
如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高，还可以通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。
- 页面锁
开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定 粒度界于表锁和行锁之间,并发度一般
使用悲观锁和乐观锁解决并发
悲观锁
在关系数据库管理系统里，悲观并发控制（又名“悲观锁”，Pessimistic Concurrency Control，缩写“PCC”）是一种并发控制的方法。它可以阻止一个事务以影响其他用户的方式来修改数据。如果一个事务执行的操作都某行数据应用了锁，那只有当这个事务把锁释放，其他事务才能够执行与该锁冲突的操作。
悲观并发控制主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。
在DBMS中，悲观锁正是利用数据库本身提供的锁机制来实现的。
乐观锁
乐观锁（ Optimistic Locking ） 相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。
参考：
海量数据的分页优化
方案
- 建立合适的索引
- 使用Redis缓存count,根据访问热度缓存靠后的分页数据
- 查找出limit的开始行id，然后用where拼接，如：
where id>*** limit 0,99 - 产品设计优化
MySQL的安装与配置
MySQL源码安装
下面在CentOS环境下安装、参考Oneinstack
1 |
|
MySQL源码目录
• BUILD 编译脚本 • client 命令行工具代码(mysql,mysqladmin) • cmd-line-utils 增强命令行的第三方库 (libedit 如 readline). • dbug 调试库 • libevent 由于 5.6 支持 某个插件的库 • plugin 插件所在的库 • libmysql MySQL 的库,其他客户端,经如C/PHP 访问MySQL需 要引用这个目录的库 • mysys 核心可移植性或者工具API • regex 正则表达式库 • scripts 脚本库,如 mysqld_safe 所在 • sql MySQL 的核心所在,用C++所写 • sql-common 客户端、服务器能用代码 • strings 字符串库 • storage 存储引擎所在的库 • vio 底层网络I/O操作库 • zlib库
数据库配置优化
基础配置
连接数配置
通过show variables like '%conn%';查看配置
- max_connections 设置整个服务器最大session连接数
- max_user_connections 每个用户的session连接个数,值为0时表示每个用户的连接数不受限制
网络配置
skip-name-resolve：禁止掉DNS的查询
解释：
mysql会在用户登录过程中对客户端IP进行DNS反查，不管你是使用IP登录还是域名登录，这个反查的过程都是在的。所以如果你的mysql所在的服务器的DNS有问题或者质量不好，那么就有可能造成127.0.0.1登录很快，用域名或者IP登录数据库很慢的DNS解析问题。数据库加这个参数速度会变快skip-name-resolve，但是也有注意点，mysql.user 表里面的host不要用localhost之类的，要用127.0.0.1，不然连自己都连不上数据库，会报错。
缓存配置
查询缓存
查询缓存的作用就是当查询接收到一个和之前同样的查询，服务器将会从查询缓存种检索结果，而不是再次分析和执行上次的查询。这样就大大提高了性能，节省时间。当查询很大、更新很少的情况下可以使用查询缓存。
查看缓存设置1
show variables like '%query_cache%';
输出如下：
+------------------------------+---------+ | Variable_name | Value | +------------------------------+---------+ | have_query_cache | YES | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 1048576 | | query_cache_type | OFF | | query_cache_wlock_invalidate | OFF | +------------------------------+---------+
查看缓存的状态
使用show status like '%Qcache%';
输出结果：
+-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 1031832 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 4 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+---------+
日志配置
通用查询日志
通用查询日志：记录建立的客户端连接和执行的语句
1). 查看通用查询日志配置1
show variables like '%general%';
输出结果：
+------------------+------------------------------+ | Variable_name | Value | +------------------+------------------------------+ | general_log | OFF | | general_log_file | /data/mysql/xxxxxx.log | +------------------+------------------------------+通用查询日志默认是关闭的。 2). 查看当前日志输出的格式
1 | MySQL [(none)]> show variables like '%log_output%'; |
+---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_output | FILE | +---------------+-------+ 1 row in set (0.01 sec)
日志格式可以是FILE（存储在数数据库的数据文件中的hostname.log），也可以是TABLE（存储在数据库中的mysql.general_log）
3) 开启通用查询日志的方式
- 通过set命令设置，只对当前mysql生效，重启失效
1 | set global general_log=on; |
- 通过配置文件my.cnf配置
1 | general_log=1 # 为1表示开启通用日志查询，值为0表示关闭通用日志查询 |
慢查询日志
MySQL的慢查询日志是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中（日志可以写入文件或者数据库表，如果对性能要求高的话，建议写文件）。默认情况下，MySQL数据库是不开启慢查询日志的，long_query_time的默认值为10（即10秒，通常设置为1秒），即运行10秒以上的语句是慢查询语句。
1) 查看是否开启慢查询日志
1 | show variables like '%quer%'; |
输出结果中
- slow_query_log值为ON时表示开启
- slow_query_log_file 的值是记录的慢查询日志到文件中（注意：默认名为主机名.log，慢查询日志是否写入指定文件中，需要指定慢查询的输出日志格式为文件，相关命令为：show variables like ‘%log_output%’；去查看输出的格式。当设置为TABLE时，慢查询日志输出至mysql.slow_log中）
2) 在配置文件中配置慢查询
1 | slow_query_log = 1 |
3) 如何制造慢查询，看慢查询设置是否有效
1 | > select sleep(5) |
二进制配置
主要作数据库的恢复和同步使用
1 | log_bin = mysql-bin |
错误日志
1 | log_error = /data/mysql/mysql-error.log |
