看《高性能MySQL》（第二版）中文版后的笔记

1.锁有两种：

（1）表级锁。这种消耗资源比较小。相对低级。

（2）行级锁。这种消耗资源比较大。相对高级。

另外还有一种锁：支持MVCC的行级锁。这种资源消耗最大。一般不怎么使用。而且这个也不是Mysql特有的。很多数据库都有。

2.set session transaction isolation level read committed,这个语句是设置隔离级别的。read committed就是一个常用的隔离级别。INno db支持所有四个隔离级别。

3.四个隔离级别：

（1）read uncommitted （读取未提交内容）又称脏读。这个比较少用。性能虽最好但是很多缺点。不推荐使用。

（2）read committed （读取提交内容）大多数数据库系统默认隔离级别。但是Mysql不是这个默认的。

（3）repeatable read （可重复读）Mysql默认隔离级别。这个会导致一个问题：幻读（phantom read）。

（4）serializable (可串行化)最高隔离级别。一般不建议使用。他强制事务排序。它虽然解决了所有并发问题。但是代价却是相当高的。它可能导致大量的超时现象和

锁竞争现象。因为事务都在排队等，直到开始的事务完成为止。而一些update等这些比select操作要高级别的操作会排在select之前进行，不管select是否本来就比

update先请求排队等候，就是说如update，delete这些高级别的操作会插队到select操作之前执行。这样问题可想而知。

4.获取数据库中每个表使用何种引擎以及其他更多信息，可以使用：

show table status;

5.获取单张表使用何种引擎以及其他更多信息，可以使用：

show table status like '表名';

6.MyISAM一般将每张表存储为两个文件：数据文件和索引文件。分别后缀名是：.MYD和.MYI. 它可以有动态行和静态行（固定不变的）。

7.MyISAM用的是表锁，不是行锁。

8.INNO DB引擎：

innodb将所有数据共同存储在一个或几个数据文件中。这种数据文件一般叫表空间。表空间本质是一种黑盒（black box）。在黑盒内，INnodb自我管理一切数据。INnodb用MVCC机制获取高并发性能。实现所有四个标准隔离级别。默认级别是repeatable read。使用了间隙锁防止幻读。

9.不要轻信“MyISAM比INNODB快”：

这个不是绝对正确的。作者已知在很多环境下，INNODB都比MyISAM快的多。特别是对于使用聚集索引。或者将数据装入内存的应用。

10.修改表引擎的方法（常用的3个）：

（1） alter table '表名' engine = Falcon;

这个比较耗费时间。因为操作是执行一个旧表到新表的逐行复制。缺点：速度慢。

（2）转储（dump）和导入（import）：

这个是使用MySqldump工具。这个操作很可能丢失原有数据。因为他会默认在create table时先drop table。缺点：安全性差。

（3）create和select：

折中了前两个方法。他是先创建一个新表，再通过MySql的insert。。。select语法来转移数据。

转移完成后，原表还在，所以要在操作完成后删除原表。注意：如果必要，可以在转换时加锁原表，防止数据不一致。

11.使用benchmark（）函数来测试数据库的性能：

例如：SELECTbenchmark(1000000,md5(product))FROMt_orders

和SELECTbenchmark(1000000,sha1(product))FROMt_orders

这两个语句意思是：从t_orders表中将product字段分别用md5和sha1函数来加密10万遍（每条数据都加密10万遍），从响应的时间看效率情况。但是该函数返回值永远是0.

虽然它使用很方便，但是一般不用它完成一种实际的基准测试。因为它很难合计出真正要测试的结果。它只能很狭隘的用于整体

测试中的一小部分。

12.架构优化和索引

 

下面有简单的优化原则：

 

更小通常更好 ： 更小的数据类型通常更快，因为它们使用了更少的磁盘空间 / 内存和 CPU 缓存，而且需要的 CPU 周期也更少。

 

简单就好 ：越简单的数据类型，需要的 CPU 周期就越少。例如整数的代价比字符小。

 

尽量避免 NULL ：要尽可能地把字段定义为 NOT NULL ，即使应用程序无需保存 NULL 。因为 Mysql 难以优化引用了可空列的查询，它会使索引 / 索引统计和值更加复杂。可空列需要更多的存储空间，还需要在 Mysql 内部进行特殊处理。把 NULL 列改为 NOT NULL 带来的性能提升很小，所以除非确定它引入了问题，否则不要把它当成优先的优化措施。

13.VARCHAR和CHAR类型：VARCHAR 保存了可变长度的字符串，是使用得最多的字符串类型。它能比固定长度类型占用更少的存储空间，因为它只占用了自己需要的空间 ( 也就是说较短的值占用的空间就较少 ) 。例外情况是使用 ROW-FORMAT=FIXED 创建的 MyISAM 表，它为每行使用固定长度的空间，可能会造成浪费。

VARCHAR使用额外的1到2字节来存储值的长度。如果列的最大长度小于或等于255，则使用1字节，否则就使用2字节。假设使用latin 1字符集，VARCHAR(10)将会占用11字节的存储空间。VARCHAR(1000)则会占用1002字节，因为需要2个字节来保存长度信息。

VARCHAR能节约空间，所以对性能有帮助。然而，由于行的长度是可变的，它们在更新的时候可能会发生变化，这会引起额外的工作。如果行的长度增加并不再适合于原始的位置时，具体的行为则会和存储引擎相关。例如，MyISAM会把行拆开，InnoDB则可能进行分页。另外的存储引擎还可能不会在合适的位置更新数据。

当最大长度远大于平均长度，并且很少发生更新的时候，通常适合使用VARCHAR。这时候碎片就不会成为问题。还有当你使用复杂的字符集，比如UTF-8时，它的每个字符都可能会占用不同的存储空间。

在5.0及以上版本，无论是保存还是取值，MySQL都会保留字符串末尾的空格。但是在4.1及之前的版本，这些空格会被去掉。

CHAR是固定长度的。MySQL总是为特定数量的字符分配足够的空间。当保存CHAR值的时候，MySQL会去掉任何末尾的空格。(在MySQL4.1及之前版本，VARCHAR也是如此。CHAR和VARCHAR在逻辑上是一样的，只是存储格式不同。)进行比较的时候，空格会被填充到字符串末尾。

14.BLOB和TEXT类型：BLOB和TEXT分别以二进制和字符形式保存大量数据。事实上，它们各自有自己的数据类型家族:字符类型有TINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT和二进制类型有TINYBLOB, SMALLBLOB, BLOB, MEDIUMBLOB和LONGBLOB。BLOB等同于SMALLBLOB,TEXT等同于SMALLTEXT。

BLOB和TEXT唯一的区别就是BLOB保存的是二进制数据，没有字符集和排序规则，但是TEXT有字符集和排序规则。

15.使用ENUM代替字符串类型：有时可以使用ENUM列来代替传统的字符串类型。ENUM列可以存储65 535个不同的字符串。MySQL以非常紧凑的方式保存了它们，根据列表中值的数量，MySQL会把它们压缩到1到2个字节中。MySQL在内部把每个值都保存为整数，以表示值在列表中的位置，并且还保留了一份“查找表(Lookup Table)”来表示整数和字符串在表的.frm文件中的映射关系。

另外一个让人吃惊的事情是ENUM字段在内部是按数字顺序进行排序的，而不是按字符串排序。

绕过这个问题的方法就是按照想要的顺序来定义ENUM。还可以使用FIELD()显式地规定排序顺序，但这会造成排序无法使用索引。具体方法是：

SELECT*
FROM `enum_test`
ORDER BY field(e,'apple','dog','fish');

ENUM最不好的一面是字符串列表是固定的，并且添加或删除字符串须使用ALTER TABLE。因此，对于一系列未来可能会改变的字符串，使用ENUM就不是一个好主意。

 

16.日期和时间类型：MySQL提供了两种相似的数据类型:DATETIME和TIMESTAMP。对于很多应用程序，它们都能工作，但是在某些情况下，一种会好于另外一种。

DATETIME

这个类型能保存大范围的值，从1001年到9999年，精度为秒。它把日期和时间封装到一个格式为YYYYMMDDHHMMSS的整数中，与时区无关。它使用了8字节存储空间。

在默认情况下，MySQL以一种可排序的、清楚的格式显示DATETIME值，例如2008-01-16 22:37:08。这种表示日期和时间的方式符合ANSI标准。

TIMESTAMP

就像它的名字一样，TIMESTAMP类型保持了自1970年1月1日午夜(格林尼治标准时间)以来的秒数，它和Unix的时间戳相同。TIMESTAMP只使用了4字节的存储空间，因此它的范围比DATETIME小得多。它表示只能从1970年到2038年。MySQL提供了FROM_ UNIXTIME()函数把Unix时间戳转换为日期，并提供了UNIX TIMESTAMP()函数，把日期转换为Unix时间戳。

TIMESTAMP也有DATETIME没有的特殊性质。在默认情况下，如果插入的行没有定义TIMESTAMP列的值，MySQL就会把它设置为当前时间(注3)。在更新的时候，如果没有显式地定义TIMESTAMP列的值，MySQL也会自动更新它。可以配置T工MESTAMP列的插入和更新行为。最后，TIMESTAMP列默认是NOT NULL，这和其他的豹据举型都不一样。

除了这些特殊行为之外，通常应该使用TIMESTAMP，因为它比DATETIME更节约空间。有时人们把Unix的时间戳保存为整数值，但是这通常没有任何好处。因为这种格式处理起来不太方便，我们并不推荐它。

17.添加主键会自动创建主键索引， 普通索引需要自己手动去添加和指定，表中效率最高的索引就是主键索引

18.清理数据库碎片的问题：

碎片分两种：行碎片和内部行碎片两种。

清理方法是：运行optimize table 表名（注意这里的表名不要加''符号。如果要加符号就加``号。），或者转储并重新加载数据也可以。

不支持optimize的，可以用alter方法：

alter table enum_test engine = innodb;

这个就是将表enum_test修改下数据引擎。这样一更新数据引擎（不一定要更新，一般都不更新，这里的意思是重新生成一下的意思。）碎片就去掉了很多。

19.myisam不支持事务。不支持动态行（即使是varchar(500)也会默认变为char(500)）。

20.Inno DB是唯一支持外键的引擎。其他引擎接受外键但不强制执行。并按主键聚集。索引按照主键引用行。索引没有像Myisam一样使用前缀压缩，所以可能会索引大很多。

数据装载缓慢。没有缓存count(*)值（和MyISAM不同，innodb不会把表的行数保存到表中）

21.show full processlist 显示在这个表上的所有进程
23.select sql_no_cache * from t_products 不用缓存。
24.show status like 'last_query_cost' 显示最后一次查询的相关信息。

25.straight_join 强制执行引擎按照查询中表出现的顺序来进行连接操作。

26.可以通过ignore index命令禁止一些索引。

27.mysql不能在多个cpu上面执行一个查询。其他数据库有这个特性，但mysql没有。不要花大量时间视图搞清楚mysql如何并行执行查询。

28.mysql从来都不支持松散型索引，即是说不连续的索引。

29.假设一个查询：
select ... from tbl where b between 2 and 3;
假设表的列（a,b）上有索引，那么执行这个上面的查询是不是性能不好？因为首先数据库会先执行a的索引，这样就导致数据库对全表扫描了。怎么优化呢？
用ignore index(a) 来禁止a的索引？正确的做法是单独对b做索引。

30.mysql不能很好的优化min（）和max（）。

31.对count的误解：对count的误解可以排在“mysql十大被误解主题”的头名。

COUNT 会干什么（What COUNT( ) Does）
COUNT 是一个特殊的函数，它有两种不同工作方式：统计值的数量和统计行的数量。值是一个非空（Non-Null）的表达式（Null 意味着没有值）。如果在 COUNT( ) 的括号中定义了列名或其他表达式。COUNT 就会统计这个表达式有值的次数。很多人对这感到费解，部分原因是因为他们弄不清楚值和 NULL 的区别。

COUNT 的另外一种形式就是统计结果中行的数量。当 MySQL 知道括号中的表达式永远都不会为 NULL 的时候，它就会按这种方式工作。最明显的例子就是 COUNT(*)，它是 COUNT 的一种特例，正如我们所想的那样，它不会把通配符 * 展开成所有的列，而是忽略所有的列并统计行数。

一个最常见的错误就是在想统计行数的时候，在 COUNT 的括号中放入列名。如果想知道结果的行数，应该总是使用 COUNT(*)，这样可以清晰地说明意图，并且得到好的性能。