破碎细胞的学习笔记

《High Performance MySQL》读书笔记

1.MySQL基础知识

1.1安装包的选择

1.1.1二进制包安装 vs. 源代码编译安装

如没有特殊定制的需求，没必要用源代码编译安装。

比如你想 MySQL被安装在一个目录。

1.1.2.官方二进制安装包 vs. 发行版二进制包

官方的二进制包的文件布局更接近从源代码安装的。各个发行版的二进制包安装后其文件布局可能会很不一样。

如果要使用官方二进制包安装，推荐使用tarball格式的，因为它安装后的文件结构最接近最常见的结构。

1.2.配置文件

1.2.1.文件位置

在类unix操作系统下，搜索顺序为：

/etc/my.cnf

datadir/my.cnf

~/.my.cnf

在windows下，顺序为：

%SystemRoot/my.ini

c:\my.cnf

1.2.2.和配置文件相关的启动参数

--no-defaults 不使用任何配置文件

--defaults-file=/path/to/file 指定配置文件的路径

--defaults-extra-file=/path/to/file 在读取my.cnf文件后额外读取的配置文件

1.2.3.几个示例文件的用处

my-small.cnf

my-medium.cnf

内存为32M到64M的MySQL专用的服务器

内存最多为128M的共享服务器（跑着MySQL、Apache等服务）。

my-large.cnf

my-huge.cnf

1.3.show命令

1.3.1.show variables

显示运行变量

1.3.2.show status

显示状态

1.3.3.show innodb status

显示innodb的状态

2.存储引擎

2.1.MySQL架构

2.2.锁和并发

2.2.1.读/写锁

解决多并发读写的这一经典问题的方法是实现一个包含读锁和写锁的锁系统。

在资源上的读锁是共享的，多个客户端可以同时读一个资源。

写锁是独占的。当资源上有写锁的时候，任何其它的读和写都是不允许的。

2.2.2.锁粒度

多数商业数据库使用行锁定。

2.2.2.1.表锁定

因为MyISAM设计时假想的情况是90%的请求是读，所以MyISAM的表锁定在这种情况下也能获得很好的性能。

换句话说，对于读大量多于写的应用，适合选用MyISAM。

2.2.2.2.页锁定

页锁定的开销比表锁定大。

页锁定中影响并发性的因素是页的大小。

BDB（Berkeley DB）使用页锁定，页大小是8KB。

2.2.2.3.行锁定

行锁定提供最大的并行性，当然也需要最大的额外开销。

InnoDB使用的是行锁定，但不是简单的行锁定，它使用的是和multiversioning schema接合的行锁定。

2.2.3Multi-Version并行控制

Multi-Version Concurrency Control（MVCC）这种并行措施被Oracle、PostgreSQL和MySQL的Innodb存储引擎。

MVCC是行锁定的另一种手段。它允许不上锁的读同时锁定必要的记录以便来写。

MVCC中，所有查询面对的实际上是一个数据的快照。每行又2个额外的隐藏属性。它表示了这行创建和删除的”时间“。这里存储的不是真实的时间，而是一个由数据库系统维护的版本号。每执行一次查询（如果支持事务，则不是查询而是事务），这个版本号就增加一次。

在MCVV的机制下，DBMS的主要任务就是跟踪所有正在运行的查询以及它们的版本号。

SELECT

当记录被select时，DBMS必须检查此行是否符合以下规则：

• 创建版本号必须小于系统当前的版本号。这一点保证了此行是在当前查询开始之前创建的。

• 删除版本号（如果不为空）必须比系统当前版本号大。这保证了此行在的当前查询开始之前没有被删除。

• 创建版本号不能在正在正在运行的所有查询中。这保证了行不是被正在运行的查询添加或改变。

• 所有符合以上标准的行可以被返回为select的结果。

INSERT

当行被添加时，DBMS同时将当前版本号作为行的创建版本号一并插入。

DELETE

DBMS把当前版本号写入记录的删除版本号

UPDATE

写入一个新的原行的副本，将当前版本号作为创建版本号，将记录的删除版本号作为副本的删除版本号。

这样，读操作就不需要锁表。但附加信息增加了存储空间，DBMS维护版本号又增加了系统开销。

MySQL的锁模型和并行行

锁策略	并行性	额外开销	引擎
表锁定	最低	最低	MyISAM、HEAP、Merge
页锁定	中等	中等	BDB
MVCC	最高	最高	InnoDB

2.3.事务

2.4.选择正确的引擎

2.4.1.考虑因素

2.4.1.1.事务和并发性

如果应用需要事务和高读/写并发，InnoDB是你的最佳选择。

如果应用需要事务但不要求高读/写并发，InnoDB和BDB都是不错的选择。

如果应用不需要事务且查询中读大量大于写或者写大量大于读，则MyISAM是比较好的选择。很多web应用属于这种情况。

2.4.1.2.备份

如果服务器能定期停机做备份，存储引擎比较好选择。如果需要联机备份，则比较麻烦。

混合的存储引擎架构备份比较麻烦，考虑到复杂度，应尽量选择一致的存储引擎。

2.4.1.3.特殊功能

SELECT COUNT(*) FROM mytable

如果应用中对count函数有性能要求，MyISAM比InnoDB要好。MyISAM能时刻知道总的行数，但InnoDB需要每次都重新计算。

如果应用需要定义实体完整性，则需要InnoDB。

如果应用需要全文搜索，则MyISAM。

如果发现需要的功能不能由一个存储引擎满足时，可能需要将一个表分割成多个表。

2.5.存储引擎

属性	MyISAM	Heap	BDB	InnoDB
事务	No	No	Yes	Yes
锁粒度	Table	Table	Page(8KB)	Row
存储	Split files	In-memory	Single file per table	Tablespace
隔离级别	None	None	Read committed	All
可移植格式	Yes	N/A	No	Yes
参照完整性	No	No	No	Yes
有数据的主键	No	No	Yes	Yes
MySQL缓存记录	No	Yes	Yes	Yes
可用性	All versions	All versions	MySQL-Max	All versions

表的定义存储在.frm文件中。

Show table status命令显示表的属性。

例：SHOW TABLE STATUS LIKE 'user'

查看user表的属性。

2.5.1.MyISAM

ISAM= Index Sequential Access Method

2.5.1.1.存储

有2种文件：数据文件（.MYD）和索引文件（.MYI）。

MyISAM的文件可以自由复制。

MyISAM的最大文件尺寸取决于文件系统，但是出于性能考虑，MyISAM默认只操作4GB的数据。MyISAM的索引使用32位的指针。如果要使用操作4GB的数据，需要设置MAX_ROWS和AVG_ROW_LENGTH。

比如：

此表能存储32GB。

CREATE TABLE mytable ( a INTEGER NOT NULL PRIMARY KEY,

b CHAR(18) NOT NULL

) MAX_ROWS = 1000000000 AVG_ROW_LENGTH = 32;

2.5.1.2.其它事宜

锁与并行

MyISAM使用共享的读锁和独占的写锁

自动恢复

启动MySQL时使用—myisam-recover选项可启动对MyISAM表的进行修复

手动修复

用命令CHECK TABLE mytable和REPAIR TABLE mytable检查和修复表。

或者使用命令行myisamchk在服务器离线的时候检查。

索引

MyISAM可对BLOB和TEXT类型进行索引。

延后的键写入（Delayed key writes）

有DELAY_KEY_WRITE属性的表。对于索引的写入，并不是马上写入磁盘，而是先写入内存缓存，当此内容从缓存中除去或者表被关闭的时候，键内容才被写入磁盘。这对负载很重且经常被修改的表有很好的性能提升。

2.5.2.压缩的MyISAM表

如果要把数据库放到光盘或者嵌入式系统中，且数据库不会被修改，可以用使用myisampack工具将MyISAM表压缩，以节约磁盘空间。解压缩所带来的额外开销不会很大，可以接受。

2.5.3.RAID MyISAM表

要使用raid表，需要自己编译mysql以便使用MySQL-Max包。raid表是把数据文件分割，而不一定是要放到不同的磁盘上。

2种使用raid表的目的：1、突破文件系统的单个文件尺寸限制 2、将分开的数据文件放到不同的物理磁盘上以提高性能

下面是创建 raid表的命令：

CREATE TABLE mytable ( a INTEGER NOT NULL PRIMARY KEY,

b CHAR(18) NOT NULL

) RAID_TYPE = STRIPED RAID_CHUNKS = 4 RAID_CHUNKSIZE = 16;

raid_type表示raid的类型，可以是striped或者raid0。raid_chunks表示要将数据文件分割成几个。raid_chunksize表示向一个分割文件中写入多少个KB后再移动到下一个分割文件。

上面的例子中，MySQL将创建4个子目录：00,01,02,03。每个子目录中存储一个文件mytable.MYD。

注意：如果系统中有RAID控制器并有软RAID，raid表就不是太需要了。另外需要注意的是，仅仅是将数据文件分块，而不是索引文件。如果你是为了解决文件系统的尺寸限制，还需要注意索引文件。

2.5.4.MyISAM Merge 表

Merge表是MyISAM最新的功能。raid表是将文件分割，而merge表是将多个相似的表合并成一个虚表。

这对用MySQL来存储日志文件非常有用。每个表存储一段时间的记录，整体分析的时候把表merge成一个虚拟表。

用于merge的子表要符合以下条件：

完全一样的定义

是MyISAM表

在MySQL4.1.1后，支持不同数据库中表的merge

如果要压缩MyISAM表，记得把它从merge中去掉，压缩后再加到merge里面去。

下面是创建merge的示例：

CREATE TABLE mytable0 ( a INTEGER NOT NULL PRIMARY KEY,

b CHAR(18) NOT NULL

);

CREATE TABLE mytable1 (

a INTEGER NOT NULL PRIMARY KEY,

b CHAR(18) NOT NULL

);

CREATE TABLE mytable2 (

a INTEGER NOT NULL PRIMARY KEY,

b CHAR(18) NOT NULL

);

CREATE TABLE mytable (

a INTEGER NOT NULL PRIMARY KEY,

b CHAR(18) NOT NULL

) TYPE = MERGE UNION = (mytable0, mytable1, mytable2) INSERT_METHOD = LAST;

INSERT_METHOD告诉MySQL怎样处理对merge表的插入操作，其值可以为NO、FIRST和LAST。NO表示不允许插入。

3.基准测试

3.1.基准测试的重要性

3.2.基准测试的策略

每次只改变一样东西

迭代测试

重复测试

使用真实数据

不要使用太多的客户端（除非是在进行压力测试，不然不要使用超过50个并发客户端）

不要在服务器上运行测试客户端

3.3

MySQL基准测试的工具

MySQL Benchmark Suit

MySQL super-smack 一个压力测试工具

MyBench 一个用perl写的压力测试工具，比super-smack更容易扩展和定制

4.索引

4.1.基础

4.1.1.基本概念

索引会增加insert update delete的开销，但会提高select的速度。

4.1.1.1.部分索引

对整个字段进行索引

ALTER TABLE mytable1 ADD INDEX (name);

对字段的前4个字节索引

ALTER TABLE mytable ADD INDEX(name(4));

4.1.1.2.多栏索引

对于查询：select * from mytable1 where fname='smith' and lname='john'

理论上创建2个查询：ALTER TABLE mytable1 ADD INDEX (fname,lname)

用户可以这样定义，但MySQL不会同时使用2个查询。实际上，MySQL在每个表每个查询中只会使用一个索引（除了union）。

MySQL处理union是将2个表分别处理，然后再归并的。

4.1.1.3.索引顺序

MySQL没有提供手动指定索引顺序的方法。MySQL自己能优化索引。

4.1.1.4.作为约束的索引

索引不是只能用于加快查询，还可以作为约束使用。 唯一索引（unique index）可以用来保证在某些列中某一值只会出现一次（除了NULL，这始终是特殊的情况）。

建立唯一索引：

ALTER TABLE mytable1 ADD UNIQUE(name);

因为是索引，所以也可以指定部分索引，

如：

ALTER TABLE mytable1 ADD UNIQUE(name(2));

这个语句规定name的前2个字母不能相同。

引申而言，对规定unique约束的字段查询可以提高速度。

如果唯一索引仅仅是起到唯一性约束而不提高查询性能的话，等于是浪费了空间，但目前 MySQL没有这样的优化处理。

4.1.1.5.Clustered和secondary索引

MyISAM的索引存储在单独的文件里。这样保证了MySQL能快速地按照索引查询。

如果使用clustered索引，主键和记录是“捆绑”在一起的，并且是按照主键的顺序排列的。InnoDB用的是clustered索引。在Oracle中，clustered索引被称为“index-organized tables”，这能帮助你理解主键和行顺序之间的关系。

如果你仅仅通过主键查找数据，clustered索引将极大地提高性能。标准的MyISAM索引在一次对索引列的查询中需要查找2次：先查找索引文件中的索引项，然后再去数据文件中查找。而clustered索引只需要查找一次。

当使用二级索引的时候，clustered索引可能没那么快。

比如：

SELECT * FROM mytable1 WHERE phone='123';

主键是name，phone上也有索引，这时，使用的实际是phone的索引，而没有使用name索引。

但是在某些情况下，不当地使用clustered索引反而会降低性能。当同时使用clustered索引和二级索引时，需要特别考虑对存储的影响。二级索引指向的是主键而不是具体列。如果对很大的值做索引并且同时有很多二级索引，会造成有很多重复的主键的副本。当clustered索引的值很小的时候，这不会是问题，但当值很大时，这会造成很大存储问题。

也就是说，在InnoDB，Oracle中，不要对拥有很大值的字段做索引，即使有，也尽量不要使用二级索引。这会非常浪费存储空间。

在clustered索引中，对主键的修改的开销比较大。

在选择主键的时候尽量选择不会被修改，且数值小的字段。

4.1.1.6.唯一索引（unique index）VS主键（primary key）

在myISAM，这2个基本没区别，唯一的区别是主键不能含有NULL，主键就是定义了NOT NULL的唯一索引。

InnoDB和BDB中的每个表都需要主键。如果你不指定的话，它们会自己添加一个隐藏的主键，这个主键为一个自增的数字，和AUTO-INCREMENT字段相似。Heap引擎不需要主键，但它会自己建立一个。实际上，你可以创建没有任何索引的heap表。

4.1.1.7.索引NULL值

在一般的数据库中，NULL值可以存在有索引的字段内，但不能存在唯一索引中。但MySQL允许在唯一索引中存NULL。

4.2.索引结构

4.2.1.B-Tree索引

B-Tree是最常见的索引结构。

基于范围的查询在B-Tree中速度很快，比如：

SELECT * FROM phone_book WHERE last_name BETWEEN 'Marten' and 'Mason'

此外还包括min() max()等函数 还有 如下范围查询：

SELECT COUNT(*) FROM phone_book WHERE last_name > 'Zawodny'

MySQL只需要在B-Tree中找到Zawodny这个值，然后仅仅数这个节点的后继节点就可以了。

4.2.2.Hash索引

hash索引使用hash表来存储节点而不是一个平衡二叉树。

Hash索引提供最快的key查找，但是灵活性相比其它的索引类型要弱一些。此时，基于范围的查询不能使用索引来提高速度。

Hash索引对于文本（Text）和数值数据有比较好的效果，因为它能有效地减少索引的尺寸。

4.2.3.R-Tree索引

R-Tree索引用在空间或者N维数据上。这在地图和地理学应用上很有用。当然在基于坐标或者维的查询上很有用。

MySQL的R-Tree实现是基于OpenGIS的规范，在www.opengis.org上可以找到。

空间数据索引对于大多数人可能比较陌生。

创建一个空间数据表：

mysql> create table map_test

-> (

-> name varchar(100) not null primary key,

-> loc geometry,

-> spatial index(loc)

-> );

4.3.索引和表类型

4.3.1.MyISAM表

MyISAM默认的索引类型是B-Tree。MyISAM还提供了两种功能：前缀压缩（prefix compression）和packed keys。

前缀压缩（prefix compression）将提取出字符中公共前缀。比如一个字段存储了URL，显然存储重复的前缀“http://”回使B-Tree的每个节点大大增加。

Packed keys是前缀压缩对于整数的的最好方法。数值的高位总是先被存储，对于大规模的数字key，最高位是很少变动的。

要启用packed keys，需要在CREATE TABLE里面设置PACKED_KEY=1。

4.3.1.1.Delayed key writes

有DELAY_KEY_WRITE属性的表。对于索引的写入，并不是马上写入磁盘，而是先写入内存缓存，当此内容从缓存中除去或者表被关闭的时候，键内容才被写入磁盘。这对负载很重且经常被修改的表有很好的性能提升。

4.3.2.Heap表

将B-Tree和Heap表接合，速度将很完美。

4.3.3.BDB表

MySQL的BDB只提供B-Tree索引。

BDB的索引和MyISAM一样提供前缀压缩。和InnoDB一样，BDB也提供 clustered索引。BDB需要主键，如果你没有指定，系统会创建一个隐藏的。这是因为BDB通过主键找到行。索引总是指向主键，而不是指向行的物理位置。这说明，按照二级索引查找比按照主键索引查找要慢。

4.3.4.InnoDB表

InnoDB提供B-Tree索引。但没有提供前缀压缩和打包（packing）。InnoDB需要主键，如果你不指定，系统将自动指定一个64位的值。

索引文件存储在InnoDB的表空间里面。InnoDB使用clustered索引。即是说，主键的值直接影响行和索引节点的物理位置。所以，InnoDB中基于主键的查询会非常快。当记录被找到的时候，也同时被缓存了到缓冲池里面了。

4.3.5.全文索引

MyISAM支持全文索引。全文索引能在文本字段（VARCHAR TEXT等）上做。全文索引是存储在MYI文件中。其具体实现是创建一个有2部分的B-Tree索引，第一部分是varchar，第二部分是float。varchar存储被索引的字符，float存储在其在行中的权重。

全文索引会索引行中的每个单词，由于MySQL的B-Tree很高效，全文索引带来的性能提升绝对值得造成的存储开销。

比如：

select * from articles where body='%database%'

下面的语句会快很多很多。

select * from articles (body) march against ('database')

4.3.6.索引限制

全文索引只是对单词（英文是单词，中文是单字）做索引。比如，搜索中间有“or”的单词，如word,lord，用全文索引是不行的。

4.3.6.2.正则表达式

比如：

select last_name from phone_book where last_name rlike "(son|ith)$"

rlike是MySQL中正则匹配的语句。

但这会很慢。

MySQL的SQL优化器不会优化基于正则表达式的查询。

4.4.索引维护

4.4.1.获得索引信息

获得表的创建信息：SHOW CREATE TABLE mytable1;

获得表的索引信息：SHOW INDEXES FROM mytable1;

4.4.2.刷新索引数据

整理碎片，优化查询 ：OPTIMIZE TABLE mytable1;

MySQL将重新创建索引。

当数据库下线时，可以使用命令行工具：myisamchk

InnoDB和 BDB不是很需要这样的操作。

5.查询性能

5.1.查询处理基础

5.1.1.查询缓存

自MySQL4.0.1之后你可以在my.cnf中设置：

query_cache_type = 1

开启查询缓存

query_cache_type= 2

按需模式

MySQL是将查询hash处理后再存到缓存中的。

基于这样原因，SELECT * FROM table1和select * from table1是不同的缓存项。MySQL不会修饰开始和结尾的空格，所以对于的空格也会造成缓存项不同。

MySQL只会对SELECT做缓存。它通过判断SQL语句的前3个字母是否为sel来判断是否为select查询。所以，对于这样的语句：

/* This is a select */ select * from mytable1

这个语句加上注释后，在执行的时候，可以通过show processlist命令来方便查看。

直到MySQL5.0，都不能判断出是一个select查询，导致不能进行缓存。

有时候我们不希望使用缓存。可以在sql语句里面加入SQL_NO_CACHE就可以。如：

select sql_no_cache * from mytable1

这对防止缓存污染很有用。

另，有的查询不需要缓存（如，某查询一天只会执行一次），可以这样来禁止MySQL缓存它，以便为其它查询留空间。

当my.cnf中的query_cache_type=2时候，只有在sql中加入SQL_CACHE时才启用缓存。

如：

select sql_cache * from mytable1

5.1.2.解析、分析 和 优化

5.1.3.使用 EXPLAIN

首先我们可以用DESC命令查看表结构：desc mytable1;

我们可以在sql语句前加上explain来查看语句的执行情况，如：

explain select * from mytable1 where name='tml';

5.1.4.执行

5.2.优化器的特色和怪脾气

测试查询时，一定记得使用真实数据。

MySQL会自己判断是否使用查询，当需要遍历用where表示的某个数量的数据时候，MySQL会不使用索引。因为读取连续的数据比按索引一个一个读要快。

5.2.2.基于索引的排序

MySQL在一个表的一个查询上只会使用一个索引。

对于含有order by的查询，若没有使用到索引，可以给排序字段添加b-tree索引来提速。

对于已经有索引的情况，则将排序字段添加到已有索引中去。

如：

SELECT * FROM weather WHERE city = 'Toledo' ORDER BY the_date DESC

可以这样添加索引：

ALTER TABLE weather DROP INDEX city, ADD INDEX (city, the_date)

注意：上面删除了原有的(city)索引。

注意，alter中字段的顺序很重要。根据最左前缀原则(leftmost prefix rule)。

我的理解，：索引的选择按照按照最左前缀。如，上面的查询，发现city时候，和索引(city,the_date)的最左前缀“city”匹配，则使用。又如，select * from where the_data='2008-01-01'就不能使用(city,the_city)索引。

5.2.3.无效查询

5.2.4.用全文检索替代LIKE

目前的MySQL的查询优化器对于全文检索的处理还不够聪明。当有一个可用的全文索引时，它会使用它，而不考虑索引能真正消除多少无关行。

5.3.识别慢查询

要找到慢查询，可以启用慢查询日志（slow query log）。该日志记录的仅仅是耗时较长的查询，而不一定是效率低的。

造成查询有时缓慢的原因：

1、表被锁了，日志中的 Lock_time显示了查询等待锁释放的时间。

2、数据或索引没有被缓存进内存。这在MySQL刚启动的时候比较常见。

3、备份进程正在运行

4、CPU负载很大

可以使用mysql自带的perl脚本mysqldumpslow去整理slow log。

5.4.用hint影响mysql

mysql的hint一般出现在select语句的右边，如：

SELECT SQL_CACHE * FROM mytable;

当为多dbms写查询的时候，可以用注释语句来自动屏蔽掉mysql特有的语句，如：

SELECT /*! SQL_CACHE */ * FROM mytable;

5.4.1.join的顺序

如果你觉得mysql对join的顺序处理不好，可以使用STRAIGHT_JOIN代替JOIN或者“.”，如：

SELECT * FROM table1 STRAIGHT_JOIN table2 ON .....;

这样就强迫mysql按照表在查询中出现的顺序来使用表。

5.4.2.索引使用

在表名后面使用USE INDEX(索引列表)让mysql使用查询，如：

SELECT * FROM mytable USE INDEX(mod_time,name) ...;

如果你想让mysql不使用查询，使用IGNORE INDEX(索引列表) ...

5.4.3.结果大小

当结果很大的时候，SQL_BUFFER_RESULT告诉mysql将结果存储到一个临时表里面去，这样能更快地释放锁。

SQL_BIG_RESULT告诉mysql结果集将会很大，让mysql在做决定的时候会更多地考虑基于磁盘的临时表。这样也不大可能为了排序而在临时表上建立索引。

5.4.4.查询缓存

SQL_CACHE让mysql使用缓存。SQL_NO_CACHE让mysql不使用缓存。

如果在配置中将query_cache_type设置为1,就是主动模式，则所有没有指定SQL_NO_CACHE的查询都默认使用缓存。

如果将query_cache_type设置为2，即是按需模式，只有使用了SQL_CACHE的查询才会使用缓存。

5.5.愚蠢的查询策略

5.5.1.两个比一个好