一、关系型数据库基础

1. 一个好数据库管理系统要有好的数据缓冲区管理器。哪些数据放哪些块，哪些块放入内存。

2.内存同步到磁盘，物理块上二进制数据如何转化应用程序对应数据，都需要存储引擎管理。存储引擎将磁盘上物理模型(数据块二进制数据)转化为逻辑模型(层次网状关系等)，理解上层组件对数据请求，择优载入部分数据。

3.锁是并发性存在前提。

当A用户读数据时，B用户也读数据，不会堵塞，读锁是共享锁，可以并发。相反写锁是独占锁。

4. 死锁：并发2用户，A用户操作1行和10行先锁定1行等待锁定10行，B用户操作10行和1行先锁定10行等待锁定1行，双方都锁定着对方需求的资源而等待对方先释放资源自给进行下一步操作，形成死锁。

锁管理器必须应用锁策略将占用资源少的用户操作先退出释放资源。

5. httpd-2.4有3种MPM多处理模块( Multi-Processing Module )

prefork一个进程响应一个请求，一个主进程负责创建回收子进程，

worker一个进程生成N个线程，一个线程响应一个请求。可以生成M个进程，生成N个线程。可以应对m*n个请求。由一个主进程管理m个子进程，管理m*n个子线程。

event一个线程响应N个请求，依靠事件回调函数调度。

Web服务器大多是get获取数据请求，post提交数据请求也会写进mysql.

6. Web服务器各种请求在Web层次关联少，不会去管理mysql如何处理2个请求间的竞争。但mysql必须管理多用户请求间的数据同步，避免资源争用。进程间很难进行同步。因此对于mysql数据库，一个专用线程响应一个用户请求，mysql线程有状态。httpd线程无状态，因此mysql线程可能长时间在线只要用户没有退出，但通过程序的API查询，可能瞬间建立瞬间解除。因此mysql内在逻辑复杂，并发性低，必须加速。高并发情况下进程的创建释放都会占用大量时间。进/线程复用，用户退出后数据清除进程不会被销毁，放入线程池（thread pool），线程池管理器决定能处理多少用户请求，保证用户请求与线程复用的建立。

7.事务：一组DML语句

ACID 原子一致隔离持久

7.1原子性Atomicity 一个事务（transaction）中的所有操作DML要么都执行，要么都不执行。不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

7.2一致性Consistency 数据库从一状态转到另一状态，数据总量不变。

7.3隔离性Isolation 当两个或者多个事务并发访问（查询和修改的操作）数据库的同一数据时必须相互隔离，不能相互干扰。事务隔离级别由低到高，包括读未提交（Read uncommitted）、读提交（readcommitted）、可重读（repeatable read）和可串行化（Serializable）。隔离越高，并发越低。Mysql的默认隔离级别在第3级可重读。其他数据库隔离级别在第2级读提交。Mysql是插件式事务引擎，其他数据库未必支持事务。在事务引擎下才有隔离，例如innode。

7.4持久性Durability 在事务完成以后，该事务对数据库所作的更改持久完整地保存在数据库中。事务已提交，数据操作持久保留，事务未提交或因故未完成，数据操作必须可撤回。依靠事务日志保持4性。

二、Mysql日志与语言

8.MySQL日志

8.1错误日志：

1.记录mysql服务器的启动、关闭和运行错误的信息。

2.事件调度器运行一个事件时产生的信息。

3.在从服务器上启动从服务器进程时产生信息

8.2查询日志：记录用户登录和记录查询的信息。

8.3慢查询日志：记录执行时间超过指定时间的操作。

8.4事务日志：将离散随机I/O转化为顺序I/O，为保证持久性，事务一提交，操作立即写入磁盘。可以重复利用操作仍可得到相同结果，即幂等性。对磁盘而言，顺序I/O执行效率高于随机I/O一个数量级，因此DBOS数据库操作系统按一定策略每隔一定时间将数据操作执行到数据库中。事务日志中数据两两或三三轮转，但事务日志包含未执行操作和未更新数据，而查询只能对表做查询操作，数据库引擎必须提供对表对事务日志同时查询能力，二者结果生成视图返回用户，否则返回数值不正确。事务日志越大，写入性能越好，并发性越好。但对其他性能压力极大，因此事务日志一般不大。数据库故障重新启动时，必须对已经提交数据操作写入表，未提交数据操作撤回原态。此数据库自我修复过程必须隔离用户参与，事务日志越大，修复操作则越多，出错概率越大。用户强行中断会引起数据库文件崩溃。如果存放事务日志的磁盘损坏，无法执行数据操作，数据丢失。因此要保存2份事务日志在不同磁盘。为存储安全和I/O效率，事务日志与数据文件不要存于同一磁盘或同一分区。Oracle事务日志又细分重做日志redo.log与撤销日志undo.log

注：传统机械磁盘最大的问题在于读写磁头，随机I/O需要花费昂贵的磁头旋转和定位来查找，顺序IO访问的速度远远快于随机IO。

Mysql在修改数据时只需要修改内存，再把修改行为记录到事务日志中(顺序IO)，不用每次将数据修改本身持久化到硬盘(随机IO)，大大提高性能。

8.5二进制日志，用户执行的DML。必须独占执行二进制日志。以二进制文件形式记录数据库中的操作，但不记录查询语句。二进制日志大小上限1GB 

8.6中继日志：MySQL主从复制架构中，从服务器远程复制主服务器二进制日志到本地的中继日志。从服务器按中继日志执行一遍以还原数据库。中继日志的本体主二进制日志已经备份，所以它就无需备份。

9.SQL四种语言

9.1DML（Data Manipulation Language）数据操纵语言：

原先CRUD现在SIUD(Select查询,Insert插入,Update更新,Delete删除)

对数据操作的语句

由DBMS提供，用于让用户或程序员使用，实现对数据库中数据的操作。

DML分成交互型DML和嵌入型DML两类。

依据语言的级别，DML又可分成过程性DML和非过程性DML两种。

需要commit.

SELECT

INSERT

UPDATE

DELETE

MERGE

CALL

EXPLAINPLAN

LOCKTABLE

9.2DDL（Data Definition Language）数据库定义语言：CREATE创建,DROP删除,ALTER修改

操作数据库对象：表，索引，视图，存储过程，存储函数，触发器，游标，事件调度器

用于定义数据库的三级结构，包括外模式、概念模式、内模式及其相互之间的映像，定义数据的完整性、安全控制等约束

DDL不需要commit.

CREATE

ALTER

DROP

TRUNCATE

COMMENT

RENAME

9.3 DCL（Data Control Language）数据库控制语言：授权，角色控制等

GRANT授权,REVOKE撤权

9.4.TCL（Transaction Control Language）事务控制语言

SAVEPOINT设置保存点

ROLLBACK回滚

SETTRANSACTION

10. 顺序文件组织:

聚簇索引：把索引值直接保存在索引(所在数据块)上，索引有序，记录有序。树状索引有序

顺序文件记录本身按顺序存储，此索引即为聚簇索引。顺序文件与索引按索引顺序存一起即为顺序文件。聚簇索引，找到索引，即找到数据。

三、存储引擎

11.Mysql存储引擎

MyISAM

表：

数据文件：表名.MYD

索引文件：表名.MYI

表定义：表名.frm

InnoDB

支持事务和聚簇索引(数据和索引存放在一起)

表：

表空间: 多张表可放置于同一个表空间；表空间多个数据库可共享；ibdata110G后裂变

MySQL支持单表使用独立的表空间文件；建议使用，默认不开。innodb_file_per_table

表定义文件：每张表的表定义文件在数据库目录中;test.frm

数据和索引：test.ibd

存储引擎：也称作表类型，一个服务数据库就是一个物理目录

MyISAM表：

tbname.MYD 数据

tbname.MYI 索引

tbname.frm 格式

[root@station253~]# ls /mydata/data/mydb

db.opttb1.frmtb1.MYDtb1.MYI

InnoDB表：

mysql>CREATE TABLE tb2(id INT,name VARCHAR(30),Info MEDIUMTEXT) ENGINE InnoDB;

mysql>SHOW TABLE STATUS\G;

mysql>show table status LIKE 'tb2'\G;

***************************1. row ***************************

Name: tb2

Engine: InnoDB

修改存储引擎从InnoDB到MyISAM

mysql --default_storage_engine='InnoDB'手工改成MyISAM

缺省修改在会话

mysql> SHOW GLOBAL VARIABLES LIKE'%engine%';

+---------------------------+--------+

| Variable_name| Value|

+---------------------------+--------+

| default_storage_engine|InnoDB |

| engine_condition_pushdown | ON|

| storage_engine| InnoDB |

+---------------------------+--------+

动态修改,重启服务失效

mysql>SET default_storage_engine='MyISAM';

修改配置文件

[root@station253 mysql]# vim /etc/my.cnf

在[mysqld]端最后加

default_storage_engine= MyISAM

重启mysql

[root@station253 mysql]# service mysqldrestart

mysql> SHOW GLOBAL VARIABLES LIKE'%engine%';

+---------------------------+--------+

| Variable_name| Value|

+---------------------------+--------+

| default_storage_engine| MyISAM |

| engine_condition_pushdown | ON|

| storage_engine| MyISAM |

InnoDB 默认会将所有的数据库InnoDB引擎的表数据存储在一个共享空间中ibdata1，但增删数据库的时候，ibdata1文件不会自动收缩，单个数据库的备份也将成为问题。通常只能将数据使用mysqldump导出，然后再导入解决这个问题。

可以修改InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间。

mysql> SET GLOBALinnodb_file_per_table=1;

独立表空间：

优点：

1．每个表都有自已独立的表空间。

2．每个表的数据和索引都会存在自已的表空间中。

3．可以实现单表在不同的数据库中移动。

4．空间可以回收（除drop table操作处，表空不能自已回收）

缺点：

单表增加比共享空间方式更大。

结论：

共享表空间在Insert操作上有一些优势，但在其它都没独立表空间表现好。

当启用独立表空间时，合理调整innodb_open_files参数。

mysql>SHOW SESSION VARIABLES LIKE 'innodb_file_per_table';

+-----------------------+-------+

|Variable_name| Value |

+-----------------------+-------+

|innodb_file_per_table | OFF|

+-----------------------+-------+

开启InnoDB为独立表空间模式

mysql>SET GLOBAL innodb_file_per_table=1; 只有全局没有会话

QueryOK, 0 rows affected (0.00 sec)

mysql>SHOW SESSION VARIABLES LIKE 'innodb_file_per_table'; 惊奇的是会话级的变量也改变了

+-----------------------+-------+

|Variable_name| Value |

+-----------------------+-------+

|innodb_file_per_table | ON|

+-----------------------+-------+

mysql>CREATE TABLE tb3(id INT,name VARCHAR(30),Info MEDIUMTEXT) ENGINE InnoDB;

mysql>SHOW TABLE STATUSWHERE Name='tb3'\G

***************************1. row ***************************

Name: tb3

Engine: InnoDB

建议：同一个库中的表最好使用相同的存储引擎; MyISAM不支持事务回滚，InnoDB支持事务回滚

12.当前服务器运行参数全局/当前用户会话

mysql> SHOW {global|session}VARIABLES;

动态调整VARIABLES

当前服务器统计状态参数

mysql> SHOW{global|session}STATUS; 

四、授权

13.不要使用root直接对应用程序授权，防止被盗用

在需要时，为应用程序提供授权账户，不要一直用root登录mysql

不要把管理员和密码直接填入某个配置文件，***攻破Web能通过apache账户看mysql配置文件，看mysql SA和密码,远程连接mysql,为所欲为。

3306端口，在三层交换机里面，用访问控制列表设定只有服务器IP，管理员IP登录mysql server，然后ROOT只能127.0.0.1登录。其他程序访问mysql库，只能用服务器上对应低权限用户登陆，不能借用root。不允许创建，不允许drop.创建用户的时候分配权限就好了

只授权CEARTE,ALTER,CREATETEMPORARY TABLES,DELETE,INSERT,LOCK TABLES,SELECT,UPDATE,SHOW DATABASES,SHOWVIEW,PROCESS

生产环境下相应的权限控制是必不可少的，假如***通过sql非法注入攻破前端应用程序，拿到后台管理员权限，再结合提权，服务器就沦陷了。

不到万不得已，不要授权mysql用户远程访问权限。

grant all privileges on *.* to root@'%' 这种做法相当危险

不要授权root用户远程访问mysql权限

非要使用Mysql SA账户，先登录到mysql 服务器，再本地连接mysql。

14.mysql上权限有转移功能，WITH GRANT OPTION, SA授权给某一用户，他还可转而授权给其他人。转移有风险。注意不用WITH GRANT OPTION。网上很多文档不经过仔细考虑权限风险都有此句。

五．Mysql优化

15.一条语句自动提交一个事务

mysql> SHOW GLOBAL VARIABLES LIKE'autocommit';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| autocommit| ON|

+---------------+-------+

表示：自动提交，都会引起IO,Innodb建议关闭，关闭自动提交功能；提高性能

mysql>set global autocommit='OFF';

QueryOK, 0 rows affected (0.00 sec)

修改后全局变量"自动提交" 关闭态

mysql>show global variables like 'autocommit';

+---------------+-------+

|Variable_name | Value |

+---------------+-------+

|autocommit| OFF|

+---------------+-------+

1 row inset (0.00 sec)

重启服务后动态设置全局变量失效

在初始化参数文件中设置

[mysqld]

init_connect=’SET autocommit=0′

六、MySQL逻辑架构

wKiom1L-1dSiQrFBAAMZp3p7boU782.jpg

MySQL逻辑架构图

1.最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

2.第二层架构主要完成大多少的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

3.存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。

4.数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

 

 

注：最后部分出自兰心hui性的blog:MySQL运行原理与基础架构 

http://xiongkun.blog.51cto.com/7513022/1351370