MySQL阅读笔记

2.1 MySQL物理文件组成

2.11 日志文件

 1、错误日志：Error Log

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

Server 每次启动和关闭的详细信息。在默认情况下，系统记录错误日志的功能是关闭的。

  2、二进制日志：Binary Log& Binary Log Index

二进制日志，也就是我们常说的binlog，也是MySQL Server 中最为重要的日志之一。

当我们通过“--log-bin[=file_name]”打开了记录的功能之后，MySQL 会将所有修改数据

库数据的query 以二进制形式记录到日志文件中。当然，日志中并不仅限于query 语句这么简单，还包括每一条query 所执行的时间，所消耗的资源，以及相关的事务信息，所以binlog是事务安全的。

   “--binlog-ignore-db=db_name”与“--binlog-do-db=db_name”两个参数有一个共同的概念需要大家理解清楚，参数中的db_name 不是指query 语句更新的数据所在的数据库，而是执行query 的时候当前所处的数据库。不论更新哪个数据库的数据，MySQL 仅仅比较当前连接所处的数据库（通过use db_name 切换后所在的数据库）与参数设置的数据库名，而不会分析query 语句所更新数据所在的数据库。

    mysql-bin.index文件（binary log index）的功能是记录所有Binary Log 的绝对路

径，保证MySQL 各种线程能够顺利的根据它找到所有需要的Binary Log 文件。

4、查询日志：query log

查询日志记录MySQL 中所有的query，通过“--log[=fina_name]”来打开该功能。由

于记录了所有的query，包括所有的select，体积比较大，开启后对性能也有较大的影响，

所以请大家慎用该功能。一般只用于跟踪某些特殊的sql 性能问题才会短暂打开该功能。默认的查询日志文件名为hostname.log。

5、慢查询日志：slow query log

顾名思义，慢查询日志中记录的是执行时间较长的query，也就是我们常说的slow

query，通过设--log-slow-queries[=file_name]来打开该功能并设置记录位置和文件名，

默认文件名为hostname-slow.log，默认目录也是数据目录。

慢查询日志采用的是简单的文本格式，可以通过各种文本编辑器查看其中的内容。其中记录了语句执行的时刻，执行所消耗的时间，执行用户，连接主机等相关信息。MySQL 还提供了专门用来分析满查询日志的工具程序mysqlslowdump，用来帮助数据库管理人员解决可能存在的性能问题。

6、Innodb 的在线redo 日志：innodb redo log

Innodb 是一个事务安全的存储引擎，其事务安全性主要就是通过在线redo 日志和记录

在表空间中的undo 信息来保证的。redo 日志中记录了Innodb 所做的所有物理变更和事务信息，通过redo 日志和undo 信息，Innodb 保证了在任何情况下的事务安全性。Innodb 的redo日志同样默认存放在数据目录下，可以通过innodb_log_group_home_dir 来更改设置日志的存放位置，通过innodb_log_files_in_group设置日志的数量。

2.1.2数据文件

  在MySQL 中每一个数据库都会在定义好（或者默认）的数据目录下存在一个以数据库名字命名的文件夹，用来存放该数据库中各种表数据文件。不同的MySQL 存储引擎有各自不同的数据文件，存放位置也有区别。多数存储引擎的数据文件都存放在和MyISAM 数据文件位置相同的目录下，但是每个数据文件的扩展名却各不一样。如MyISAM“.MYD”作为扩展名，Innodb 用“.ibd”，Archive 用“.arc”，CSV 用“.csv”，等等。

1、“.frm”文件

与表相关的元数据（meta）信息都存放在“.frm”文件中，包括表结构的定义信息等。

不论是什么存储引擎，每一个表都会有一个以表名命名的“.frm”文件。所有的“.frm”文

件都存放在所属数据库的文件夹下面。

   2“.ibd”文件和ibdata 文件

这两种文件都是存放Innodb 数据的文件，之所以有两种文件来存放Innodb 的数据（包

括索引），是因为Innodb 的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是独享表空间存放存储数据。独享表空间存储方式使用“.ibd”文件来存放数据，且每个表一个“.ibd”文件，文件存放在和MyISAM 数据相同的位置。如果选用共享存储表空间来存放数据，则会使用ibdata 文件来存放，所有表共同使用一个（或者多个，可自行配置）ibdata 文件。

 

2.1.3Replication相关文件

1、master.info;

2、relay log 和relay log index

   其中mysql-relay-bin.index记录relay log日志的存放位置的绝对路径。

3、relay-log.info 文件：

类似于master.info，它存放通过Slave 的I/O 线程写入到本地的relaylog 的相关信

息。供Slave 端的SQL 线程以及某些管理操作随时能够获取当前复制的相关信息。

2.1.3其他文件

1 .cnf: 系统配置文件

2 pid file: 存放进程id

3 socket file:socket 文件也是在Unix/Linux 环境下才有的，用户在Unix/Linux 环境下客户端连接可以不通过TCP/IP 网络而直接使用Unix Socket 来连接MySQL。

 

 

3MySQL存储引擎简介

3.2MyISAM存储引擎

MyISAM 存储引擎的表在数据库中，每一个表都被存放为三个以表名命名的物理文件。首先肯定会有任何存储引擎都不可缺少的存放表结构定义信息的.frm 文件，另外还有.MYD和.MYI文件，分别存放了表的数据（.MYD）和索引数据（.MYI）。每个表都有且仅有这样三个文件做为MyISAM 存储类型的表的存储，也就是说不管这个表有多少个索引，都是存放在同一个.MYI文件中。

MyISAM 支持以下三种类型的索引：B-Tree索引、R-Tree索引和Full-text索引。其中B-Tree是最常用的索引，Full-text索引主要是为了解决在我们需要用like查询的低效问题。

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

3.2Innodb存储引擎

Innodb与MyISAM相比，其特点(优势)如下：

1. 支持事物（四个级别全部实现）

2、数据多版本读取

Innodb 在事务支持的同时，为了保证数据的一致性以及并发时候的性能，通过对

undo信息，实现了数据的多版本读取。

3、锁定机制的改进

Innodb 改变了MyISAM 的锁机制，实现了行锁。虽然Innodb 的行锁机制的实现是通

过索引来完成的，但毕竟在数据库中99%的SQL 语句都是要使用索引来做检索数的所

以，行锁定机制也无疑为Innodb 在承受高并发压力的环境下增强了不小的竞争力。

4、实现外键

 

 

在物理存储方面，Innodb 存储引擎也和MyISAM 不太一样，虽然也有.frm文件来存放表结构定义相关的元数据，但是表数据和索引数据是存放在一起的。

虽然我们可以自行设定使用共享表空间还是独享表空间来存放我们的表，但是共享表空间都是必须存在的，因为Innodb 的undo 信息和其他一些元数据信息都是存放在共享表空间里面的。

Innodb 的物理结构分为两大部分：

1. 数据文件（表数据和索引数据）

2. 日志文件

 

 3.5 Memory存储引擎

Memory存储引擎实现页级锁定。

3.5.5 ARCHIVE存储引擎：

ARCHIVE 存储引擎的锁定机制为行级锁定。

 

4 MySQL安全管理

4.2.1 权限系统简介

MySQL 的权限系统在实现上比较简单，相关权限信息主要存储在几个被称为grant tables 的系统表中，即： mysql.User，mysql.db，mysql.Host，mysql.table_priv和mysql.column_priv。由于权限信息数据量比较小，而且访问又非常频繁，所以Mysql 在启动的时候，就会将所有的权限信息都Load 到内存中保存在几个特定的结构中。所以才有我们每次手工修改了权限相关的表之后，都需要执行“FLUSH PRIVILEGES”命令重新加载MySQL的权限信息。当然，如果我们通过GRANT，REVOKE 或者DROPUSER 命令来修改相关权限，则不需要手工执行FLUSH PRIVILEGES 命令，因为通过GRANT，REVOKE 或者DROPUSER 命令所做的权限修改在修改系统表的同时也会更新内存结构中的权限信息。

且由于Column Level 所针对的权限和Routine Level的权限作用域没有重合部分，所以不会有覆盖与被覆盖的关系。针对ColumnLevel 级别的权限仅有INSERT，SELECT 和UPDATE 这三种。Column Level 的权限授权语句语法基本和TableLevel 差不多，只是需要在权限名称后面将需要授权的列名列表通过括号括起来，如下：

root@localhost : test 12:14:46> GRANT SELECT(id,value)ON test.t2 TO 'abc'@'%.jianzhaoyang.com';

Query OK, 0 rows affected(0.00 sec)

 

Routine Level 的权限主要只有EXECUTE 和ALTERROUTINE 两种，主要针对的对象是procedure 和function这两种对象，在授予Routine Level 权限的时候，需要指定数据库和相关对象，如：

root@localhost : test 04:03:26> GRANT EXECUTE ONtest.p1 to

'abc'@'localhost';

 

4.2.4 访问控制实现原理

那内存结构中的权限信息更新之后对已经连接上的用户何时生效呢？（第二次用到该级别的权限时）

对于Global Level 的权限信息的修改，仅仅只有更改之后新建连接才会用到，对于已经连接上的session 并不会受到影响。而对于Database Level 的权限信息的修改，只有当客户端请求执行了“USE database_name”命令之后，才会在重新校验中使用到新的权限信息。所以有些时候如果在做了比较紧急的Global 和Database 这两个Level的权限变更之后，可能需要通过“KILL”命令将已经连接在MySQL中的session 杀掉强迫他们重新连接以使用更新后的权限。对于Table Level 和Column Level 的权限，则会在下一次需要使用到该权限的Query 被请求的时候生效，也就是说，对于应用来讲，这两个Level 的权限，更新之后立刻就生效了，而不会需要执行“KILL”命令。

 

当MySQL 接收到客户端的请求之后，访问控制模块是需要校验该用户是否满足提交的请求所需要的权限。权限校验过程是从最大范围的权限往最小范围的权限开始依次校验所涉及到的每个对象的每个权限。

 

我们自己必须至少保证数据库在备份时候的数据是处于某一个时间点的，这样就要求我们必须做到在备份MyISAM 数据库的物理文件的时候让MyISAM 存储引擎停止写操作，仅仅提供读服务，其根本实质就是给数据库表加锁来阻止写操作。

MySQL 自己提供了一个使用程序mysqlhotcopy，这个程序就是专门用来备份MyISAM 存储引擎的。

mysqlhotcopydb_name[./table_regex/] [new_db_name | directory]

从上面的基本使用方法我们可以看到，mysqlhotcopy出了可以备份整个数据库，指定的某个表，还可以通过正则表达式来匹配某些表名来针对性的备份某些表。备份结果就是指定数据库的文件夹下包括所有指定的表的相应物理文件。

mysqlhotcopy 是一个用perl 编写的使用程序，其主要实现原理实际上就是通过先LOCK住表，然后执行FLUSH TABLES 动作，该正常关闭的表正常关闭，将该fsync 的数据都fsync，

然后通过执行OS 级别的复制（cp 等）命令，将需要备份的表或者数据库的所有物理文件都复制到指定的备份集位置。

 

5.5 备份策略的设计思路

从我个人经验来看，可以根据不同的需求不同的级别通过如下的几个思路来设计出合理

的备份策略：

1、对于较为核心的在线应用系统，需要有在线备用主机通过MySQL 的复制进行相应的备份，复制线程可以一直开启，恢复线程可以每天恢复一次，尽量让备机的数据延后主机在一定的时间段之内。这个延后的时间多长合适主要是根据实际需求决定，一般来说延后一天是一个比较常规的做法。

2、对于重要级别稍微低一些的应用，恢复时间要求不是太高的话，为了节约硬件成本，不必要使用在线的备份主机来单独运行备用MySQL，而是通过每一定的时间周期内进行一次物理全备份，同时每小时（或者其他合适的时间段）内将产生的二进制日志进行备份。这样虽然没有第一种备份方法恢复快，但是数据的丢失会比较少。恢复所需要的时间由全备周期长短所决定。

3、而对于恢复基本没有太多时间要求，但是不希望太多数据丢失的应用场景，则可以通过每一定时间周期内进行一次逻辑全备份，同时也备份相应的二进制日志。使用逻辑备份而不使用物理备份的原因是因为逻辑备份实现简单，可以完全在线联机完成，备份过程不会影响应用提供服务。

4、对于一些搭建临时数据库的备份应用场景，则仅仅只需要通过一个逻辑全备份即可满足需求，都不需要用二进制日志来进行恢复，因为这样的需求对数据并没有太苛刻的要求。

 

第6章 影响 MySQL Server 性能的相关因素

6.3Query语句的优化

SQL 语句在数据库中执行并获取所需数据这个过程来做一个大概的分析了:

当MySQL Server 的连接线程接收到Client 端发送过来的SQL 请求之后，会经过一系列的分解Parse，进行相应的分析。然后，MySQL 会通过查询优化器模块（Optimizer）根据该SQL 所设涉及到的数据表的相关统计信息进行计算分析，然后再得出一个MySQL 认为最合理最优化的数据访问方式，也就是我们常说的“执行计划”，然后再根据所得到的执行计划通过调用存储引擎借口来获取相应数据。然后再将存储引擎返回的数据进行相关处理，并以Client 端所要求的格式作为结果集返回给Client 端的应用程序。

 

在数据库管理软件中，最大的性能瓶颈就是在于磁盘IO，也就是数据段额存取操作上面。

 

数据库应用系统的优化真正能带来最大收益的就是商业需求和系统架构及业务实现的优化，然后是数据库Schema 设计的优化，然后才是Query 语句的优化，最后才是数据库管理软件自身的一些优化。通过笔者的经验，在整个系统的性能优化中，如果按照百分比来划分上面几个层面的优化带来的性能收益，可以得出大概如下的数据：

需求和架构及业务实现优化：55%

Query 语句的优化：30%

数据库自身的优化：15%

简单来说，可以通过下面三句话来简单的概括数据库应用系统的性能优化：商业需求合理化，系统架构最优化，逻辑实现精简化，硬件设施理性化。

 

 

7 MySQL 数据库锁定机制

7．1 MySQL锁定机制简介

数据库锁定机制简单来说就是数据库为了保证数据的一致性而使各种共享资源在被并发访问时变得有序所设计的一种规则。

 

在MySQL 数据库中，使用表级锁定的主要是MyISAM，Memory，CSV 等一些非事务性存储引擎，而使用行级锁定的主要是Innodb 存储引擎和NDB Cluster 存储引擎，页级锁定主要是BerkeleyDB 存储引擎的锁定方式。

 

 

8. MySQL 数据库 Query 的优化

8.2 Query 语句优化基本思路和原则

在分析如何优化MySQL Query 之前，我们需要先了解一下Query 语句优化的基本思路和原则。一般来说，Query 语句的优化思路和原则主要提现在以下几个方面：

1. 优化更需要优化的Query；

2. 定位优化对象的性能瓶颈；

3. 明确的优化目标；

4. 从Explain 入手；

5. 多使用profile

6. 永远用小结果集驱动大的结果集；

7. 尽可能在索引中完成排序；

8. 只取出自己需要的Columns；

9. 仅仅使用最有效的过滤条件；

10. 尽可能避免复杂的Join 和子查询；

上面所列的几点信息，前面4 点可以理解为Query 优化的一个基本思路，后面部分则是我们优化中的基本原则。

 

8.4 合理设计并利用索引

在MySQL 中，主要有四种类型的索引，分别为：B-Tree 索引，Hash 索引，Fulltext索引和RTree索引。

 

索引的利处

索引“能够提高数据检索的效率，降低数据库的IO成本”。

确实，在数据库中某个表的某个字段创建索引，所带来的最大益处就是将该字段作为检索条件的时候可以极大的提高检索效率，加快检索时间，降低检索过程中所需要读取的数据量。但是索引所给我们带来的收益只是提高表数据的检索效率吗？当然不是，索引还有一个非常重要的用途，那就是降低数据的排序成本。

我们知道，每个索引中索引数据都是按照索引键键值进行排序后存放的，所以，当我们的Query 语句中包含排序分组操作的时候，如果我们的排序字段和索引键字段刚好一致，MySQLQuery Optimizer就会告诉mysqld 在取得数据之后不用排序了，因为根据索引取得的数据已经是满足客户的排序要求。

那如果是分组操作呢？分组操作没办法直接利用索引完成。但是分组操作是需要先进行排序然后才分组的，所以当我们的Query 语句中包含分组操作，而且分组字段也刚好和索引键字段一致，那么mysqld 同样可以利用到索引已经排好序的这个特性而省略掉分组中的排序操作。

排序分组操作主要消耗的是我们的内存和CPU 资源，如果我们能够在进行排序分组操作中利用好索引，将会极大的降低CPU 资源的消耗。

 

索引的弊端

索引的益处我们都已经清楚了，但是我们不能光看到索引给我们带来的这么多益处之后就认为索引是解决Query 优化的圣经，只要发现Query 运行不够快就将WHERE 子句中的条件全部放在索引中。确实，索引能够极大的提高数据检索效率，也能够改善排序分组操作的性能，但是我们不能忽略的一个问题就是索引是完全独立于基础数据之外的一部分数据。假设我们在Table ta 中的Column ca 创建了索引idx_ta_ca，那么任何更新Column ca 的操作，MySQL 都需要在更新表中Column ca 的同时，也更新Column ca 的索引数据，调整因为更新所带来键值变化后的索引信息。而如果我们没有对Columnca 进行索引的话,MySQL 所需要做的仅仅只是更新表中Columnca 的信息。这样，所带来的最明显的资源消耗就是增加了更新所带来的IO 量和调整索引所致的计算量。此外，Column ca 的索引idx_ta_ca 是需要占用存储空间的，而且随着Table ta 数据量的增长，idx_ta_ca 所占用的空间也会不断增长。所以索引还会带来存储空间资源消耗的增长。

 

如何判定是否需要创建索引

◆较频繁的作为查询条件的字段应该创建索引；

◆唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件；

◆更新非常频繁的字段不适合创建索引；

◆不会出现在WHERE 子句中的字段不该创建索引；

 

下面是我对于选择合适索引的几点建议，并不一定在任何场景下都合适，但在大多数场景下还是比较适用的。

1. 对于单键索引，尽量选择针对当前Query 过滤性更好的索引；

2. 在选择组合索引的时候，当前Query 中过滤性最好的字段在索引字段顺序中排列越靠前越好；

3. 在选择组合索引的时候，尽量选择可以能够包含当前Query 的WHERE 子句中更多字段的索引；

4. 尽可能通过分析统计信息和调整Query 的写法来达到选择合适索引的目的而减少通过使用Hint 人为控制索引的选择，因为这会使后期的维护成本增加，同时增加维护所带来的潜在风险。

 

MySQL 中索引的限制

在使用索引的同时，我们还应该了解在MySQL中索引存在的限制，以便在索引应用中尽可能的避开限制所带来的问题。下面列出了目前MySQL 中索引使用相关的限制。

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 中，只有一种Join 算法，就是大名鼎鼎的Nested Loop Join，他没有其他很多数据库所提供的Hash Join，也没有Sort Merge Join。顾名思义，Nested Loop Join 实际上就是通过驱动表的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。如果还有第三个参与Join，则再通过前两个表的Join 结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，如此往复。

 

OrderBy优化：

在MySQL 中，ORDER BY 的实现有如下两种类型：

◆ 一种是通过有序索引而直接取得有序的数据，这样不用进行任何排序操作即可得到满足客户端要求的有序数据返回给客户端；

◆ 另外一种则需要通过MySQL 的排序算法将存储引擎中返回的数据进行排序然后再将排序后的数据返回给客户端。

在MySQL 第二种排序实现方式中，必须进行相应的排序算法来实现数据的排序。MySQL 目前可以通过两种算法来实现数据的排序操作。

1. 取出满足过滤条件的用于排序条件的字段以及可以直接定位到行数据的行指针信息，在SortBuffer 中进行实际的排序操作，然后利用排好序之后的数据根据行指针信息返回表中取得客户端请求的其他字段的数据，再返回给客户端；

2. 根据过滤条件一次取出排序字段以及客户端请求的所有其他字段的数据，并将不需要排序的字段存放在一块内存区域中，然后在Sort Buffer 中将排序字段和行指针信息进行排序，最后再利用排序后的行指针与存放在内存区域中和其他字段一起的行指针信息进行匹配合并结果集，再按照顺序返回给客户端。

上面第一种排序算法是MySQL 一直以来就有的排序算法，而第二种则是从MySQL4.1 版本才开始增加的改进版排序算法。第二种算法与第一种相比较，主要优势就是减少了数据的二次访问。在排序之后不需要再一次回到表中取数据，节省了IO 操作。当然，第二种算法会消耗更多的内存，正是一种典型的通过内存空间换取时间的优化方式。

 

第 9 章 MySQL数据库Schema设计的性能优化

9.1 高效的模型设计

1、大字段垂直分拆优化：

底什么样的字段适合于从表中拆分出去呢？

首要肯定是大字段。为什么？原因很简单，就是因为他的大。大字段一般都是存放着一些较长的Detail 信息，如文章的内容，帖子的内容，产品的介绍等等。

其次是和表中其他字段相比访问频率明显要少很多。

那我们在移出大字段的同时，是否还需要将其他字段也一并移出呢？其实如果我们已经确定有大字段需要分拆出主表的时候，对于其他的字段，只要满足访问频率和大字段一样相对于表中其他字段要低很多的都可以和大字段同时分拆出来。

实际上，在有些时候，我们甚至都不一定非要大字段才能进行垂直分拆。在有些场景下，有的表中大部分字段平时都很少访问，而其中的某几个字段却是访问频率非常高。对于这种表，也非常适合通过垂直分拆来达到优化性能的目的。

2、“大表水平拆分”

3、统计表- 准实时优化

 

“数据库系统的性能不是优化出来的，更多的是设计出来的”。

 

Innodb 存储引擎默认的事务隔离级别是REPEATABLE READ，但实际上在我们大部分的应用场景下，都只需要READ COMMITED 的事务隔离级别就可以满足需求了。

10 网易MySQL资料

mysql的字符集包括字符集（CHARACTER）和校对规则（COLLATION）两个概念。字符集是用来定义mysql存储字符串的方式，校对规则则是定义了比较字符串的方式。字符集和校对规则是一对多的关系, MySQL支持30多种字符集的70多种校对规则。

 

SQL Injection 原理：（SQL注入攻击）

结构化查询语言(SQL)是一种用来和数据库交互的文本语言。SQL Injection 就是利用某些数据库的外部接口把用户数据插入到实际的数据库操作语言(SQL)当中，从而达到入侵数据库乃至操作系统的目的。它的产生主要是由于程序对用户输入的数据没有进行严格的过滤，导致非法数据库查询语句的执行。