Mysql 杂文记事

物理结构

MySQL物理结构主要由三部分文件组成：日志文件，数据文件，复本文件。

 

 1. 日志文件

ErrorLog：记录了MyQL Server运行过程中所有较为严重的警告和错误信息，以及MySQL

Server每次启动和关闭的详细信息。

BinLog：BinLog是MySQL Server中最为重要的日志之一，它将所有修改数据库数据的query以二进制形式记录到日志文件中，包括执行时间，消耗资源以及相关的事务信息，所以binlog

是事务安全的。

UpdateLog：功能和BinLog基本类似，不过它是以文本格式记录内容。自从 MySQL增加了 BinLog 功能之后，就很少使用更新日志了。

QueryLog：查询日志记录 MySQL 中所有的query，体积比较大，开启后对性能也有较大的影响，所以请大家慎用该功能。

SlowQueryLog：顾名思义，慢查询日志记录的是执行时间较长的query，包括语句执行的时刻，执行所消耗的时间，执行用户，连接主机等相关信息。MySQL提供了mysqlslowdump工具帮助分析SlowQueryLog。

RedoLog：Innodb是一个事务安全的存储引擎，其事务安全性主要就是通过在线redo日志和记录在表空间中的undo信息来保证的。redo日志中记录了Innodb所做的所有物理变更和事务信息，通过redo日志和undo信息，Innodb保证了在任何情况下的事务安全性。

 

 2. 数据文件

不同存储引擎产生的数据文件是不一样的，上图所示为MyISAM与Innodb的数据文件格式。

 .frm：存放与表相关的元数据信息，如表结构定义。不管是什么类型的存储引擎，每一张表都会有一个以表名命名的“.frm”文件。

.MYD：MyISAM存储引擎数据文件，存放MyISAM表数据信息。每一张MyISAM表都有一个“.MYD”文件与之对应。

.MYI：MyISAM存储引擎索引文件，存放MyISAM表的索引信息。对于MyISAM存储来说，可以被cache的内容主要就是来源于“.MYI”文件中。

.ibd：Innodb数据文件(包括索引信息)，在独享表空间时后缀为.ibd，共享表空间时为.ibdata。

  

3. 复本文件（Replication）

master.info：位于Slave端的数据目录下，存放Master的主机地址，连接用户，连接密码，连接端口，当前日志位置，已经读取到的日志位置等信息。

RelayLog：

1. mysql-relay-bin.xxxxxn 文件用于存放Slave端的I/O线程从Master端所读取到

的BinaryLog信息，然后由Slave端的SQL线程从该RelayLog中读取并解析相应的日志信息，转化成Master所执行的SQL语句，然后在 Slave 端应用。

2. relay-log.info存放通过Slave的I/O线程写入到本地的relay log的相关信息。供Slave端的SQL线程以及某些管理操作随时能够获取当前复制的相关信息。

 

逻辑结构

 总的来讲，MySQL可以看成是二层架构，第一层叫做SQL Layer，在MySQL数据库系统处理底层数据之前的所有工作都是在这一层完成的，包括权限判断，sql解析，执行计划优化， query cache的处理等；第二层是存储引擎层Storage Engine Layer，也就是底层数据存取操作实现部分，由多种存储引擎共同组成。

 

 

存储引擎

1. MyISAM

使用MyISAM类型的表将产生表结构定义文件(.frm)，数据文件(.MYD)和索引文件(.MYI)，同一张表不管创建了多少个索引都将放到同一个索引文件中(.MYI)。MyISAM支持以下三种类型的索引：

B-Tree索引：所有索引节点都按照balance tree的数据结构来存储，所有索引数据节点都在叶节点。MyISAM的B-Tree索引有一个较大的限制，那就是参与一个索引的所有字段的长度之和不能超过1000字节。

R-Tree索引：R-Tree索引的存储方式和b-tree索引有一些区别，主要设计用于为存储空间和多维数据的字段做索引。

Full-text索引：Full-text索引就是我们常说的全文索引，他的存储结构也是b-tree。主要是为了解决like查询的低效问题。

 

当出现以下情况时可能导致MyISAM文件损坏：

1、当mysqld正在做写操作的时候被kill掉或者其他情况造成异常终止；

2、主机Crash；

3、磁盘硬件故障；

4、MyISAM存储引擎中的bug；

在运行过程中如果发现某个MyISAM表出问题了，可以在线通过check table校验，并可以通过repair table命令来尝试修复。在数据库关闭状态下，也可以通过myisamchk工具来对数据库中某个（或某些）表进行检测或者修复。不过强烈建议不到万不得已不要轻易对表进行修复操作，修复之前尽量做好可能的备份工作，以免带来不必要的后果。

 

2. Innodb

MySQL最为广泛使用的除了MyISAM之外，就是Innodb了，这款引擎主要有以下特点：

 

事务安全：实现了SQL92标准所定义的所有四个级别（ READ UNCOMMITTED， READ COMMITTED， REPEATABLE READ和SERIALIZABLE）。对事务安全的支持，是使得Innodb成为MySQL最为流行的存储引擎之一的重要原因。

数据多版本读取：Innodb在事务支持的同时，为了保证数据的一致性以及并发时候的性能，通过undo信息，实现数据多版本读取。

锁机制改进：Innodb改变了MyISAM的锁机制，实现了行级锁。虽然 Innodb的行锁机制是通过索引实现的，但毕竟在数据库中99%的 SQL语句都是要使用索引来做检索数据的。所以，行锁定机制也无疑为Innodb在承受高并发压力的环境下增强了不小的竞争力。

实现外键：Innodb实现了外键引用这一数据库的重要特性，使在数据库端控制部分数据的完整性成为可能。

 

由于Innodb是事务安全的存储引擎，所以系统Crash对它来说并不能造成非常严重的损失，有redo日志的存在，有checkpoint机制的保护，Innodb完全可以通过redo日志将数据库Crash时刻已经完成但还没有来得及将数据写入磁盘的事务恢复，也能够将所有部分完成并已经写入磁盘的未完成事务回滚并将数据还原。

 

此外，MySQL还有其它类型的存储引擎，适用于不同场景，如NDB Cluster，Merge，Memory，BDB，Federated，Archive，BlackHole，CSV。

 

授权访问

Mysql存储相关用户权限存储在mysql.User, mysql.db, mysql.Host, mysql.table_priv, mysql.column_priv表中，可以通过show grants for ‘uname’@’host’; 查看某用户授权信息。由于权限信息访问频繁，所以Mysql在启动时会将该类信息加载到内存中，在手动修改相关权限表后，需要执行flush privileges; 将修改重加载到内存才会生效。

 

1. 创建用户

create user 'def'@'localhost' identified by 'pwd';

上面将创建用户名为’def’，密码为’pwd’的用户，该用户只能在本机访问，不支持远程连接。

 

2. 用户授权

Mysql数据库将权限分为五个等级，高级别权限将会覆盖低级别的权限：

 

2.1 Globle Level

grant select,update,delete,insert on *.* to ‘def’@’localhost’;

即授予’def’@’localhost’对所有对象(库,表,字段)的增删查改权限。Globle Level支持以下权限：

 

名称	支持	描述
alter	all	表结构更改
alter routine	5.0.3+	变更procedure,function,trigger等权限
create	all	数据库，表和索引的创建
create routine	5.0.3+	创建procedure,function,trigger等权限
create temporary tables	4.0.2+	临时表创建
create user	5.0.3+	创建用户
create view	5.0.1+	创建视图
delete	all	表删除
drop	all	库删除
execute	5.0.3+	执行procedure,function,trigger等权限
file	all	执行load data infile, select into file的权限
index	all	创建索引
insert	all	数据插入
lock tables	4.0.2+	执行lock tables命令显式给表加锁的权限
process	all	执行show processlist命令的权限
reload	all	执行flush等让数据库reload对象或数据的操作
replication client	4.0.2+	执行show master/slave status命令的权限
replication slave	4.0.2+	复制环境中Slave连接用户所需要的复制权限
select	all	数据查询
show databases	4.0.2+	执行show databases命令权限
show view	5.0.1+	执行show create view的权限
shutdown	all	Mysql Server的shutdown权限
super	4.0.2+	执行kill线程, hange master, purge master logs, set global等命令的权限
update	all	数据更新
usage	all	新创建用户后不授任何权限的时候所拥有的最小权限

 

2.2 Database Level

grant alter on test.* to 'def'@'localhost';

授予’def’@’localhost’对’test’库所有对象的’alter’权限。Database Level支持以下权限：

 

名称	支持	描述
alter	all	表结构更改
alter routine	5.0.3+	变更procedure,function,trigger等权限
create	all	数据库，表和索引的创建
create routine	5.0.3+	创建procedure,function,trigger等权限
create temporary tables	4.0.2+	临时表创建
create view	5.0.1+	创建视图
delete	all	表删除
drop	all	库删除
execute	5.0.3+	执行procedure,function,trigger等权限
index	all	创建索引
insert	all	数据插入
lock tables	4.0.2+	执行lock tables命令显式给表加锁的权限
select	all	数据查询
show view	5.0.1+	执行show create view的权限
update	all	数据更新

 

2.3 Table Level

grant index on test.t1 to ‘def’@’172.16.10.%’;

即授予10网段的机器使用’def’用户，在test.t1表创建索引的权限。可用权限为：

 

名称	支持	描述
alter	all	表结构更改
create	all	数据库，表和索引的创建
delete	all	表删除
drop	all	库删除
index	all	创建索引
insert	all	数据插入
select	all	数据查询
update	all	数据更新

 

2.4 Column Level

grant select(id,value) on test.t2 to 'def'@'%.foo.com';

授予符合通配符域名的主机用户，在test.t2表的id,value列的查询权限。支持的权限为：

 

名称	支持	描述
insert	all	数据插入
select	all	数据查询
update	all	数据更新

 

注意：当某个用户在向某个表插入(insert)数据的时候，如果该用户在该表中某列上面没有insert权限，则该列的数据将以默认值填充。这一点和很多其他的数据库都有一些区别，是MySQL自己在SQL上面所做的扩展。

 

2.5 Routine Level

grant execute on test.p1 to ‘def’@’localhost’;

授予用户执行存储过程p1的权限。该级别支持权限为：

 

名称	支持	描述
alter routine	5.0.3+	变更procedure,function,trigger等权限
execute	5.0.3+	执行procedure,function,trigger等权限

 

此外可以通过 grant all 语句授予某Level全部权限，如：

grant all on test.t1 to ‘def’; (Table Level)。

 

拥有grant权限的用户也可以将自身所拥有的全部权限授予其它用户，所以grant权限是一个非常特殊和危险的权限。通常在执行grant授权语句时在最后加上with grant option子句达到授予grant权限的目的。

 

3. 撤销权限

revoke insert on *.* from ‘def’@’localhost’;

 

4. 无法登录

帐户无法登录的时候，就可以通过前面的知识分析问题了：

4.1 用户是否创建(use mysql; select User,Host from user;)

4.2 主机是否限制，比如Host为%的账户，在使用mysql -ufoo -p登录时，因为没有指定主机，mysql就选择localhost作为缺省主机地址，可该用户并没有locahost的主机(即%不包括localhost)。这时可以通过 mysql -ufoo -p -h yourhost显式指定主机登录，或者添加localhost主机的账户。

 4.3 修改是否更新到内存中(show grants for foo@’%’; flush privileges;)

 

备份与恢复

1. 逻辑备份

1.1 mysqldump备份

生成可重现当前数据库中数据的insert结构备份文件，使用方式(mysqldump --help)：

Usage:  mysqldump [OPTIONS] database [tables]

OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]

OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

如：mysqldump -u username -p dbname > dbname.sql

在生产环境下，这样的操作可能会带来完整性问题，解决方式如下：

1. 暂停Mysql Server服务

2. 事务型存储引擎使用(--single-transaction)参数控制dump操作在一个事务中完成

3. 表锁定，即dump过程中只读不写(--lock-all-tables)

 

mysqldump也可以生成纯文本结构的信息：

mysqldump -u username -T /tmp dbname tablename --fieldsenclosed-by=\" --fields-terminated-by=,

 

cat /tmp/tablename.txt

350021,21,"A","abcd"

350022,22,"B","abcd"

 

或者使用select方式导出纯文本数据：

-> SELECT * INTO OUTFILE '/tmp/tablename.txt'

-> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

-> LINES TERMINATED BY '\n'

-> FROM tablename limit 100;

 

cat /tmp/tablename.txt

350021,21,"A","abcd"

350022,22,"B","abcd"

 

1.2 mysqldump恢复

对于insert结构的备份文件，可以通过以下方式恢复：

1. 使用mysql工具，mysql < /path/to/backup.sql

2. 如果已经连接到mysql中，可以执行 source /path/to/backup.sql

 

纯文本数据恢复主要使用mysql中的load data infile命令或mysqlimport工具恢复。

 

2. 物理备份

 

2.1 MyIsam存储引擎

每一个数据库都会在”datadir”目录下生成一个文件夹，MyIsam引擎的表将生成表结构信息”.frm”，数据文件”.MYD”和索引文件”.MYI”三个文件，所以针对MyIsam引擎的物理备份，只需要备份以上三个文件和Mysql系统共享的物理文件就可以了。

 

虽然MyIsam没有事务相关文件，但Mysql对索引一致性也做出要求，所以在备份时需要加锁，避免备份过程中写操作导致索引不一致的问题。可以通过mysqlhotcopy进行热备：

mysqlhotcopy db_name[./table_regex/] [new_db_name | directory]

 

mysqlhotcopy是一个用perl编写的使用程序，其主要实现原理实际上就是通过先LOCK住表， 然后执行FLUSH TABLES动作，该正常关闭的表正常关闭，将该fsync的数据都fsync，然后通过执行OS级别的复制（cp等）命令，将需要备份的表或者数据库的所有物理文件都复制到指定的备份集位置

 

2.2 Innodb存储引擎

Innodb表空间存储方式分为独立表空间和共享表空间，通过以下参数控制：

innodb_file_per_table：表空间存储方式，1(on)表示启用独享表空间，独享表空间即每张表独享一个.ibd文件；反之共享表空间将共享一个或多个共享文件ibdata文件。

 

2.2.1 共享表空间

共享表空间会将所有innodb表数据/索引信息都写到一个或多个共享文件中，该文件通过以下参数控制文件路径，大小与扩展方式：

innodb_data_home_dir：共享文件根目录

innodb_data_file_path：共享文件名称，可以配置多个文件与文件扩展方式

可以通过show variables like 'innodb_data%';查看配置参数。

 

所以在共享表空间模式下，需要备份innodb_data_file_path相关文件和datadir下的 .frm表结构文件，以及innodb_log_group_home_dir路径下与事务相关的日志文件。

 

2.2.2 独享表空间

由于undo/redo相关信息都是存储在共享表空间文件中的，所以即使是独享表空间模式，也还是需要备份innodb_data_file_path相关文件和datadir下的.frm和.ibd文件，以及innodb_log_group_home_dir路径下与事务相关的日志文件。

  

Innodb在线热备一般都要借助相关工具完成，如ibbackup(Innobase公司的商业版)。

 

优化

1. explain

explain select fa,ba from foo, bar\G 可以看到query语句对索引使用情况和基于统计信息的结果集大小等相关信息。

 

2. profiling

profiling可以收集更详细的统计信息，比如us, sy, io等。

set profiling = 1;

select fa,ba from foo, bar;

show profiles\G;

show profile cpu,block io for query 1;

 

3. schema

范式规则有时并不一定是业务最优结构，比如在某些调用频繁的场景，可以考虑分拆大字段，冗余字段，从而降低无用字段读取或表联接查询。

 

4. 需求优化

需求的合理性与需求实现时的逻辑设计，很大程度上决定了可优化程度。比如一个要求实时更新帖子回复数量的需求，无论如何设计都无法避免频繁统计或读写的问题。

 

通常优化百分比占用是这样的：

需求和业务优化： 55%

Query语句的优化：30%

数据库自身的优化：15%

 

查看系统状态：status;

查看活动连接：show processlist;

 

锁机制

1. 行级锁

优点：行级锁粒度小，锁竞争概率也比较小，所以并发处理能力更强。

缺点：加锁与解锁需要更复杂的操作，占用内存与算法实现更复杂，消耗更大;会发生死锁。

在Mysql中事务型存储引擎通常是行锁实现的，如：Innodb, NDB Cluster。

 

通过以下命令，可以查看innodb行锁占用情况：

show status like 'innodb_row_lock%';

Innodb_row_lock_current_waits  0    #当前正在等待锁的数量

Innodb_row_lock_time          1113 #锁定总时间

Innodb_row_lock_time_avg      6    #平均锁定时间

Innodb_row_lock_time_max     91   #最大锁定时间

Innodb_row_lock_waits         176  #总共等待锁的次数

 

2. 表级锁

优点：逻辑简单，加锁与解锁带来的系统性能消耗较小，由于是表级锁定，可以避免死锁。

缺点：锁粒度粗，锁竞争激烈，并发处理能力较差。

在Mysql中非事务型存储引擎通常是使用表锁实现的，如：MyIsam, Memory, CSV；这更适合于读多写少的应用场景。

 

通过以下命令，可以查看系统表锁占用情况：

show status like 'table_locks%';

Table_locks_immediate 278497 #产生表级锁定的次数

Table_locks_waited    0     #表锁竞争导致的等待次数

 

3. 页级锁

页锁是介于行锁与表锁之间的锁机制，性能也介于两者之间；与行锁一样，页锁也可能导致死锁。在Mysql中采用页锁的存储引擎是BerkeleyDB。

 

4. Innodb锁机制

 

4.1 共享锁/排它锁/意向锁

Innodb的行级锁定分为两种类型，共享锁和排他锁，实现过程中为了让行级锁定和表级锁定共存(提高锁系统性能)，使用了意向锁（表级锁定）的概念。

  

意向锁的作用就是当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个意向排他锁。

	共享锁(S)	排他锁(X)	意向共享锁(IS)	意向排它锁(IX)
共享锁(S)	兼容	冲突	兼容	冲突
排他锁(X)	冲突	冲突	冲突	冲突
意向共享锁(IS)	兼容	冲突	兼容	兼容
意向排它锁(IX)	冲突	冲突	兼容	兼容

 

4.2 MVCC

InnoDB存储引擎是基于多版本的并发控制协议MVCC (Multi-Version Concurrency Control)，耳熟能详的便是：读不加锁，读写不冲突。在MVCC并发控制中，读操作可以分成以下两类：

4.2.1快照读(snapshot read)

简单的select操作，属于快照读，不加锁，可能读到历史版本。如：select * from table where ?;

4.2.2当前读 (current read)

特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。如：

select * from table where ? lock in share mode;

select * from table where ? for update;

insert into table values (…);

update table set ? where ?;

delete from table where ?;

其中，第一句加的是共享锁(S)，其它是排它锁(X)。

 

4.3 锁与隔离级别

Innodb实现了ISO/Ansi SQL92定义的四种隔离级别：

Read UnCommited：可以读取未提交记录(存在脏读)。

Read Commited：快照读可忽略; 当前读保证对读取到的记录加行锁(存在不可重复读)。

Repeatable Read：快照读可忽略; 当前读保证对读取到的记录加行锁并对满足查询条件的记录加间隙锁(存在幻读)。

Serializable：从MVCC并发控制退化为基于锁的并发控制。不区分快照读与当前读，所有的读操作均为当前读，读加共享锁(S)，写加排它锁(X)。Serializable隔离级别下，读写冲突，因此并发度急剧下降，在MySQL/InnoDB下不建议使用。

 

查看系统/会话隔离级别：select @@global.tx_isolation,@@tx_isolation;

更改会话级隔离级别：set session tx_isolation='read-uncommitted';

更改系统级隔离级别：set global tx_isolation='read-uncommitted';

 

索引

Btree索引

Btree是一种优秀的数据检索结构，数据库都喜欢使用Btree或它的变种树提供存储与检索服务。在Innodb存储引擎中，存在两种类型的索引形式：Cluster形式的主键索引与普通的Btee索引(Secondary Index)。

 

在叶子结构层上，主键索引存放了实际数据，而SecondaryIndex则放置了指向主键索引的指针，所以在Innodb上通过主键访问会比普通索引更高效。

 

Hash索引

在理想情况下(静态集合完美哈希)，Hash索引是最优数据结构，目前Memory存储引擎是支持这种索引的，但它也存在以下问题：

1. 不支持范围查找

2. 索引无法排序

3. 哈希冲突导致线性扫描

 

Fulltext

据悉，目前只有MyIsam存储引擎的char,varchar,text三种数据类型可以使用fulltext索引。该类型索引的结构也是基于Btree的，但关键词部分存储的是分词之后的信息，对中文支持并不是很好，需要借助第三方补丁或插件，精准度有待评估。另一方面，fulltext在建立与维护索引时也是需要消耗资源的。

 

R-Tree

R-Tree 索引可能是我们在其他数据库中很少见到的一种索引类型，主要用来解决空间数据检索的问题。在MySQL中，支持一种用来存放空间信息的数据类型GEOMETRY，从 MySQL5.0.16 版本开始，BDB，Innodb，NDBCluster和Archive存储引擎也开始支持该数据类型。当然，虽然多种存储引擎都开始支持GEOMETRY数据类型，但是仅仅之后MyISAM存储引擎支持R-Tree索引。在MySQL中采用了具有二次分裂特性的R-Tree来索引空间数据信息，然后通过几何对象（MRB）信息来创建索引。

 

限制

1. MyISAM存储引擎索引键长度总和不能超过1000字节；

2. BLOB和TEXT类型的列只能创建前缀索引；

3. MySQL 目前不支持函数索引；

4. 使用不等于（ != 或者 <>）的时候 MySQL 无法使用索引；

5. 过滤字段使用了函数运算后（如abs(column)）， MySQL无法使用索引；

6. Join 语句中 Join 条件字段类型不一致的时候 MySQL 无法使用索引；

7. 使用 LIKE 操作的时候如果条件以通配符开始（'%abc...'） MySQL 无法使用索引；

8. 使用非等值查询的时候 MySQL 无法使用 Hash 索引；

 

参考资料

简朝阳：<<MySQL性能调优与架构设计>>

用户手册：http://dev.mysql.com/doc/refman/5.5/en/optimization.html

七月：http://blog.csdn.net/v_JULY_v/article/details/6530142/

Mysql加锁处理分析：http://hedengcheng.com/?p=771