MySQL学习笔记

刚接触MySQL，觉得就是CRUD语句，实现起来超级简单，但是总是不知道其所以然，虽然不妨碍使用，但是总是欠缺了那么一点东西，慢慢了解其原理之后，有些东西才恍然大悟。

MySQL的基础知识：除了常用的CRUD语句，就是对于数据类型的选择：

类型 大小 范围（有符号） 范围（无符号） 用途 
TINYINT 1 字节 (-128，127) (0，255) 小整数值 
SMALLINT 2 字节 (-32 768，32 767) (0，65 535) 大整数值 
MEDIUMINT 3 字节 (-8 388 608，8 388 607) (0，16 777 215) 大整数值 
INT或INTEGER 4 字节 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值 
BIGINT 8 字节 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值 
FLOAT 4 字节 (-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度
浮点数值 
DOUBLE 8 字节 (1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度
浮点数值 
DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值

INT 类型
　　在 MySQL 中支持的 5 个主要整数类型是 TINYINT，SMALLINT，MEDIUMINT，INT 和 BIGINT。这些类型在很大程度上是相同的，只有它们存储的值的大小是不相同的。

　　MySQL 以一个可选的显示宽度指示器的形式对 SQL 标准进行扩展，这样当从数据库检索一个值时，可以把这个值加长到指定的长度。例如，指定一个字段的类型为 INT(6)，就可以保证所包含数字少于 6 个的值从数据库中检索出来时能够自动地用空格填充。需要注意的是，使用一个宽度指示器不会影响字段的大小和它可以存储的值的范围。

　　万一我们需要对一个字段存储一个超出许可范围的数字，MySQL 会根据允许范围最接近它的一端截短后再进行存储。还有一个比较特别的地方是，MySQL 会在不合规定的值插入表前自动修改为 0。

　　UNSIGNED 修饰符规定字段只保存正值。因为不需要保存数字的正、负符号，可以在存储时节约一个“位”的空间。从而增大这个字段可以存储的值的范围。

　　ZEROFILL 修饰符规定 0（不是空格）可以用来真补输出的值。使用这个修饰符可以阻止 MySQL 数据库存储负值。

FLOAT、DOUBLE 和 DECIMAL 类型
　　MySQL 支持的三个浮点类型是 FLOAT、DOUBLE 和 DECIMAL 类型。FLOAT 数值类型用于表示单精度浮点数值，而 DOUBLE 数值类型用于表示双精度浮点数值。

　　与整数一样，这些类型也带有附加参数：一个显示宽度指示器和一个小数点指示器。比如语句 FLOAT(7,3) 规定显示的值不会超过 7 位数字，小数点后面带有 3 位数字。

　　对于小数点后面的位数超过允许范围的值，MySQL 会自动将它四舍五入为最接近它的值，再插入它。

　　DECIMAL 数据类型用于精度要求非常高的计算中，这种类型允许指定数值的精度和计数方法作为选择参数。精度在这里指为这个值保存的有效数字的总个数，而计数方法表示小数点后数字的位数。比如语句 DECIMAL(7,3) 规定了存储的值不会超过 7 位数字，并且小数点后不超过 3 位。

　　忽略 DECIMAL 数据类型的精度和计数方法修饰符将会使 MySQL 数据库把所有标识为这个数据类型的字段精度设置为 10，计算方法设置为 0。

　　UNSIGNED 和 ZEROFILL 修饰符也可以被 FLOAT、DOUBLE 和 DECIMAL 数据类型使用。并且效果与 INT 数据类型相同。

字符串类型
　　MySQL 提供了 8 个基本的字符串类型，可以存储的范围从简单的一个字符到巨大的文本块或二进制字符串数据。

类型 大小 用途 
CHAR 0-255字节 定长字符串 
VARCHAR 0-255字节 变长字符串 
TINYBLOB 0-255字节 不超过 255 个字符的二进制字符串 
TINYTEXT 0-255字节 短文本字符串 
BLOB 0-65 535字节 二进制形式的长文本数据 
TEXT 0-65 535字节 长文本数据 
MEDIUMBLOB 0-16 777 215字节 二进制形式的中等长度文本数据 
MEDIUMTEXT 0-16 777 215字节 中等长度文本数据 
LOGNGBLOB 0-4 294 967 295字节 二进制形式的极大文本数据 
LONGTEXT 0-4 294 967 295字节 极大文本数据

CHAR 和 VARCHAR 类型
　　CHAR 类型用于定长字符串，并且必须在圆括号内用一个大小修饰符来定义。这个大小修饰符的范围从 0-255。比指定长度大的值将被截短，而比指定长度小的值将会用空格作填补。

　　CHAR 类型可以使用 BINARY 修饰符。当用于比较运算时，这个修饰符使 CHAR 以二进制方式参于运算，而不是以传统的区分大小写的方式。

　　CHAR 类型的一个变体是 VARCHAR 类型。它是一种可变长度的字符串类型，并且也必须带有一个范围在 0-255 之间的指示器。CHAR 和 VARCHGAR 不同之处在于 MySQL 数据库处理这个指示器的方式：CHAR 把这个大小视为值的大小，不长度不足的情况下就用空格补足。而 VARCHAR 类型把它视为最大值并且只使用存储字符串实际需要的长度（增加一个额外字节来存储字符串本身的长度）来存储值。所以短于指示器长度的 VARCHAR 类型不会被空格填补，但长于指示器的值仍然会被截短。

　　因为 VARCHAR 类型可以根据实际内容动态改变存储值的长度，所以在不能确定字段需要多少字符时使用 VARCHAR 类型可以大大地节约磁盘空间、提高存储效率。

　　VARCHAR 类型在使用 BINARY 修饰符时与 CHAR 类型完全相同。

TEXT 和 BLOB 类型
　　对于字段长度要求超过 255 个的情况下，MySQL 提供了 TEXT 和 BLOB 两种类型。根据存储数据的大小，它们都有不同的子类型。这些大型的数据用于存储文本块或图像、声音文件等二进制数据类型。

　　TEXT 和 BLOB 类型在分类和比较上存在区别。BLOB 类型区分大小写，而 TEXT 不区分大小写。大小修饰符不用于各种 BLOB 和 TEXT 子类型。比指定类型支持的最大范围大的值将被自动截短。

日期和时间类型
　　在处理日期和时间类型的值时，MySQL 带有 5 个不同的数据类型可供选择。它们可以被分成简单的日期、时间类型，和混合日期、时间类型。根据要求的精度，子类型在每个分类型中都可以使用，并且 MySQL 带有内置功能可以把多样化的输入格式变为一个标准格式。

类型 大小
(字节) 范围 格式 用途 
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值 
TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS 时间值或持续时间 
YEAR 1 1901/2155 YYYY 年份值 
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值 
TIMESTAMP 8 1970-01-01 00:00:00/2037 年某时 YYYYMMDD HHMMSS 混合日期和时间值，时间戳

DATE、TIME 和 YEAR 类型
　　MySQL 用 DATE 和 YEAR 类型存储简单的日期值，使用 TIME 类型存储时间值。这些类型可以描述为字符串或不带分隔符的整数序列。如果描述为字符串，DATE 类型的值应该使用连字号作为分隔符分开，而 TIME 类型的值应该使用冒号作为分隔符分开。

　　需要注意的是，没有冒号分隔符的 TIME 类型值，将会被 MySQL 理解为持续的时间，而不是时间戳。

　　MySQL 还对日期的年份中的两个数字的值，或是 SQL 语句中为 TEAR 类型输入的两个数字进行最大限度的通译。因为所有 TEAR 类型的值必须用 4 个数字存储。MySQL 试图将 2 个数字的年份转换为 4 个数字的值。把在 00-69 范围内的值转换到 2000-2069 范围内。把 70-99 范围内的值转换到 1970-1979 之内。如果 MySQL 自动转换后的值并不符合我们的需要，请输入 4 个数字表示的年份。

DATEYIME 和 TIMESTAMP 类型
　　 除了日期和时间数据类型，MySQL 还支持 DATEYIME 和 TIMESTAMP 这两种混合类型。它们可以把日期和时间作为单个的值进行存储。这两种类型通常用于自动存储包含当前日期和时间的时间戳，并可在需要执行大量数据库事务和需要建立一个调试和审查用途的审计跟踪的应用程序中发挥良好作用。

　　如果我们对 TIMESTAMP 类型的字段没有明确赋值，或是被赋与了 null 值。MySQL 会自动使用系统当前的日期和时间来填充它。

复合类型
　　MySQL 还支持两种复合数据类型 ENUM 和 SET，它们扩展了 SQL 规范。虽然这些类型在技术上是字符串类型，但是可以被视为不同的数据类型。一个 ENUM 类型只允许从一个集合中取得一个值；而 SET 类型允许从一个集合中取得任意多个值。

ENUM 类型
　　ENUM 类型因为只允许在集合中取得一个值，有点类似于单选项。在处理相互排拆的数据时容易让人理解，比如人类的性别。ENUM 类型字段可以从集合中取得一个值或使用 null 值，除此之外的输入将会使 MySQL 在这个字段中插入一个空字符串。另外如果插入值的大小写与集合中值的大小写不匹配，MySQL 会自动使用插入值的大小写转换成与集合中大小写一致的值。

　　 ENUM 类型在系统内部可以存储为数字，并且从 1 开始用数字做索引。一个 ENUM 类型最多可以包含 65536 个元素，其中一个元素被 MySQL 保留，用来存储错误信息，这个错误值用索引 0 或者一个空字符串表示。

　　MySQL 认为 ENUM 类型集合中出现的值是合法输入，除此之外其它任何输入都将失败。这说明通过搜索包含空字符串或对应数字索引为 0 的行就可以很容易地找到错误记录的位置。

SET 类型
　　SET 类型与 ENUM 类型相似但不相同。SET 类型可以从预定义的集合中取得任意数量的值。并且与 ENUM 类型相同的是任何试图在 SET 类型字段中插入非预定义的值都会使 MySQL 插入一个空字符串。如果插入一个即有合法的元素又有非法的元素的记录，MySQL 将会保留合法的元素，除去非法的元素。

　　一个 SET 类型最多可以包含 64 项元素。在 SET 元素中值被存储为一个分离的“位”序列，这些“位”表示与它相对应的元素。“位”是创建有序元素集合的一种简单而有效的方式。并且它还去除了重复的元素，所以 SET 类型中不可能包含两个相同的元素。

　　希望从 SET 类型字段中找出非法的记录只需查找包含空字符串或二进制值为 0 的行。

首先一直困扰我的是它的索引原理：

  对于数据库操作最为频繁的就是进行查询，而索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从上往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的，如果我想找到m开头的单词呢？或者ze开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？

生活中随处可见索引的例子，如火车站的车次表、图书的目录等，它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。数据库也是一样，但显然要复杂许多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段......这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的，数据库实现比较复杂，数据保存在磁盘上，而为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。

第一部分：基础知识

索引

官方介绍索引是帮助MySQL高效获取数据的数据结构。笔者理解索引相当于一本书的目录，通过目录就知道要的资料在哪里， 不用一页一页查阅找出需要的资料。

唯一索引(unique index)

强调唯一，就是索引值必须唯一。

创建索引：

create unique index 索引名 on 表名(列名);alter table 表名 add unique index 索引名 (列名);

删除索引：

drop index 索引名 on 表名;alter table 表名 drop index 索引名;

主键

主键就是唯一索引的一种，主键要求建表时指定，一般用auto_increment列，关键字是primary key

主键创建：

creat table test2 (id int not null primary key auto_increment);

全文索引

InnoDB不支持，MyISAM支持性能比较好，一般在 CHAR、VARCHAR 或 TEXT 列上创建。

Create table 表名(     id int not null primary key anto_increment,    title varchar(100),FULLTEXT(title))type=MyISAM;

单列索引与多列索引

索引可以是单列索引也可以是多列索引（也叫复合索引）。按照上面形式创建出来的索引是单列索引，现在先看看创建多列索引：

create table test3 (    id int not null primary key auto_increment,    uname char(8) not null default '',    password char(12) not null,    INDEX(uname,password))type=MyISAM;

注意：INDEX(a, b, c)可以当做a或(a, b)的索引来使用，但不能当作b、c或(b,c)的索引来使用。这是一个最左前缀的 优化方法，在后面会有详细的介绍，你只要知道有这样两个概念。

聚簇索引

一种索引，该索引中键值的逻辑顺序决定了表中相应行的物理顺序。 聚簇索引确定表中数据的物理顺序。Mysql中MyISAM 表是没有聚簇索引的，innodb有（主键就是聚簇索引），聚簇索引在下面介绍innodb结构的时有详细介绍。

查看表的索引

通过命令：Show index from 表名 如：

mysql> show index from test3;  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+----+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+----+| test3 |          0 | PRIMARY  |        1  |    id          |     A     |   0          |     NULL | NULL   |     | BTREE      |         |  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+Table：表名Key_name：什么类型索引（这里是主键）Column_name：索引列的字段名Cardinality：索引基数，很关键的一个参数，平均数值组=索引基数/表总数据行，平均数值组越接近1就越有可能利用索引Index_type：如果索引是全文索引，则是fulltext,这里是b+tree索引，b+tree也是这篇文章研究的重点之一

第二部分：MyISAM和INNODB索引结构

简单介绍B-tree B+ tree树

B-tree结构视图

一棵m阶的B-tree树，则有以下性质

1. Ki表示关键字值，上图中，k1<k2<…<ki<k0<Kn(可以看出，一个节点的左子节点关键字值<该关键字值<右子节点关键字值)
2. Pi表示指向子节点的指针，左指针指向左子节点，右指针指向右子节点。即是：p1[指向值]<k1<p2[指向值]<k2……
3. 所有关键字必须唯一值（这也是创建MyISAM 和innodb表必须要主键的原因），每个节点包含一个说明该节点多少个关键字，如上图第二行的i和n
4. 节点：
   • 每个节点最可以有m个子节点。
   • 根节点若非叶子节点，至少2个子节点，最多m个子节点
   • 每个非根，非叶子节点至少[m/2]子节点或叫子树（[]表示向上取整），最多m个子节点
5. 关键字：
   • 根节点的关键字个数1~m-1
   • 非根非叶子节点的关键字个数[m/2]-1~m-1,如m=3，则该类节点关键字个数：2-1~2
6. 关键字数k和指向子节点个数指针p的关系：
   • k+1=p ，注意根据储存数据的具体需求，左右指针为空时要有标志位表示没有 
   • B+tree结构示意图如下：

B+树是B-树的变体，也是一种多路搜索树： * 非叶子结点的子树指针与关键字个数相同 * 为所有叶子结点增加一个链指针（红点标志的箭头）

MyISAM索引结构

MyISAM索引用的B+ tree来储存数据，MyISAM索引的指针指向的是键值的地址，地址存储的是数据，如下图：

结构讲解：上图3阶树，主键是Col2，Col值就是该行数据保存的物理地址，其中红色部分是说明标注。

• 1标注部分也许会迷惑，前面不是说关键字15右指针的指向键值要大于15，怎么下面还有15关键字？因为B+tree的所有叶子节点 包含所有关键字且是按照升序排列（主键索引唯一，辅助索引可以不唯一），所以等于关键字的数据值在右子树
• 2标注是相应关键字存储对应数据的物理地址，注意这也是之后和InnoDB索引不同的地方之一
• 2标注也是一个所说MyISAM表的索引和数据是分离的，索引保存在”表名.MYI”文件内，而数据保存在“表名.MYD”文件内，2标注 的物理地址就是“表名.MYD”文件内相应数据的物理地址。（InnoDB表的索引文件和数据文件在一起）
• 辅助索引和主键索引没什么大的区别，辅助索引的索引值是可以重复的（但InnoDB辅助索引和主键索引有很明显的区别，这里 先提醒注意一下）

Innode索引结构

（1）首先有一个表，内容和主键索引结构如下两图：

Col1

Col2

Col3

1

15

phpben

2

20

mhycoe

3

23

phpyu

4

25

bearpa

5

40

phpgoo

6

45

phphao

7

48

phpxue

…… 

结构上：由上图可以看出InnoDB的索引结构很MyISAM的有很明显的区别

• MyISAM表的索引和数据是分开的，用指针指向数据的物理地址，而InnoDB表中索引和数据是储存在一起。看红框1可看出一行 数据都保存了。
• 还有一个上图多了三行的隐藏数据列（虚线表），这是因为MyISAM不支持事务，InnoDB处理事务在性能上并发控制上比较好， 看图中的红框2中的DB_TRX_ID是事务ID，自动增长；db_roll_ptr是回滚指针，用于事务出错时数据回滚恢复；db_row_id 是记录行号，这个值其实在主键索引中就是主键值，这里标出重复是为了容易介绍，还有的是若不是主键索引（辅助索引）， db_row_id会找表中unique的列作为值，若没有unique列则系统自动创建一个。关于InnoDB跟多事务MVCC点 此： http://www.phpben.com/?post=72

（2）加入上表中Col1是主键（下图标错），而Col2是辅助索引，则相应的辅助索引结构图：

可以看出InnoDB辅助索引并没有保存相应的所有列数据，而是保存了主键的键值（图中1、2、3….）这样做利弊也是很明显：

• 在已有主键索引，避免数据冗余，同时在修改数据的时候只需修改辅助索引值。
• 但辅助索引查找数据事要检索两次，先找到相应的主键索引值然后在去检索主键索引找到对应的数据。这也是网上很多 mysql性能优化时提到的“主键尽可能简短”的原因，主键越长辅助索引也就越大，当然主键索引也越大。

MyISAM索引与InnoDB索引相比较

• MyISAM支持全文索引（FULLTEXT）、压缩索引，InnoDB不支持
• InnoDB支持事务，MyISAM不支持
• MyISAM顺序储存数据，索引叶子节点保存对应数据行地址，辅助索引很主键索引相差无几；InnoDB主键节点同时保存数据行，其他辅助索引保存的是主键索引的值
• MyISAM键值分离，索引载入内存（key_buffer_size）,数据缓存依赖操作系统；InnoDB键值一起保存，索引与数据一起载入InnoDB缓冲池
• MyISAM主键（唯一）索引按升序来存储存储，InnoDB则不一定
• MyISAM索引的基数值（Cardinality，show index 命令可以看见）是精确的，InnoDB则是估计值。这里涉及到信息统计的知识，MyISAM统计信息是保存磁盘中，在alter表或Analyze table操作更新此信息，而InnoDB则是在表第一次打开的时候估计值保存在缓存区内
• MyISAM处理字符串索引时用增量保存的方式，如第一个索引是‘preform’，第二个是‘preformence’，则第二个保存是‘7，ance‘，这个明显的好处是缩短索引，但是缺陷就是不支持倒序提取索引，必须顺序遍历获取索引

第三部分：MYSQL优化

mysql优化是一个重大课题之一，这里会重点详细的介绍mysql优化，包括表数据类型选择，sql语句优化，系统配置与维护优化三类。

表数据类型选择

1. 能小就用小。表数据类型第一个原则是：使用能正确的表示和存储数据的最短类型。这样可以减少对磁盘空间、内存、cpu缓存的使用。
2. 避免用NULL，这个也是网上优化技术博文传的最多的一个。理由是额外增加字节，还有使索引，索引统计和值更复杂。很多还忽略一 个count(列)的问题，count(列)是不会统计列值为null的行数。更多关于NULL可参考：http://www.phpben.com/?post=71
3. 字符串如何选择char和varchar？一般phper能想到就是char是固定大小，varchar能动态储存数据。这里整理一下这两者的区别：

属性

Char

Varchar

值域大小

最长 字符数 是255（不是字节） ，不管什么编码，超过此值则自动截取255个字符保存并没有报错。

65535 个字节，开始两位存储长度，超过 255 个字符，用 2 位储存长度，否则 1 位，具体字符长度根据编码来确定，如 utf8

则字符最长是 21845 个

如何处理字符串末尾空格

去掉末尾空格，取值出来比较的时候自动加上进行比较

Version<=4.1 ，字符串末尾空格被删掉，version>5.0 则保留

储存空间

固定空间，比喻char(10) 不管字符串是否有 10 个字符都分配 10 个字符的空间

Varchar 内节约空间，但更新可能发生变化，若varchar(10), 开始若储存 5个字符，当 update 成 7 个时有 MyISAM 可能把行拆开， innodb 可能分页，这样开销就增大

适用场合

适用于存储很短或固定或长度相似字符，如 MD5加密的密码 char(33) 、昵称 char(8) 等

当最大长度远大于平均长度并且发生更新的时候。

注意当一些英文或数据的时候，最好用每个字符用字节少的类型，如latin1

1. 整型、整形优先原则

   Tinyint、smallint、mediumint、int、bigint，分别需要8、16、24、32、64。 
   值域范围：-2 (n-1)~ 2 (n-1)-1 
   很多程序员在设计数据表的时候很习惯的用int，压根不考虑这个问题 
   笔者建议：能用tinyint的绝不用smallint 
   误区：int(1) 和int(11)是一样的，唯一区别是mysql客户端显示的时候显示多少位。 
   整形优先原则：能用整形的不用其他类型替换，如ip可以转换成整形保存，如商品价格‘50.00元’则保存成50

2. 精确度与空间的转换。在存储相同数值范围的数据时，浮点数类型通常都会比DECIMAL类型使用更少的空间。FLOAT字段使用4 字节存储 数据。DOUBLE类型需要8 个字节并拥有更高的精确度和更大的数值范围，DECIMAL类型的数据将会转换成DOUBLE类型。

最左前缀原则

定义：最左前缀原则指的的是在sql where 子句中一些条件或表达式中出现的列的顺序要保持和多索引的一致或以多列索引顺序出现，只要 出现非顺序出现、断层都无法利用到多列索引。

举例说明：上面给出一个多列索引(username,password,last_login)，当三列在where中出现的顺序如(username,password,last_login)、 (username,password)、(username)才能用到索引，如下面几个顺序(password,last_login)、(passwrod)、(last_login)---这三者不 从username开始，(username,last_login)---断层，少了password，都无法利用到索引。因为B+tree多列索引保存的顺序是按照索引创 建的顺序，检索索引时按照此顺序检索

为什么需要索引（Why is it needed）?

当数据保存在磁盘类存储介质上时，它是作为数据块存放。这些数据块是被当作一个整体来访问的，这样可以保证操作的原子性。硬盘数据块存储结构类似于链表，都包含数据部分，以及一个指向下一个节点（或数据块）的指针，不需要连续存储。

记录集只能在某个关键字段上进行排序，所以如果需要在一个无序字段上进行搜索，就要执行一个线性搜索（Linear Search）的过程，平均需要访问N/2的数据块，N是表所占据的数据块数目。如果这个字段是一个非主键字段（也就是说，不包含唯一的访问入口），那么需要在N个数据块上搜索整个表格空间。

但是对于一个有序字段，可以运用二分查找（Binary Search），这样只要访问log2 (N)的数据块。这就是为什么性能能得到本质上的提高。

什么是索引（What is indexing）?

索引是对记录集的多个字段进行排序的方法。在一张表中为一个字段创建一个索引，将创建另外一个数据结构，包含字段数值以及指向相关记录的指针，然后对这个索引结构进行排序，允许在该数据上进行二分法排序。

副作用是索引需要额外的磁盘空间，对于MyISAM引擎而言，这些索引是被统一保存在一张表中的，这个文件将很快到达底层文件系统所能够支持的大小限制，如果很多字段都建立了索引的话。

索引如何工作（How does it work?）

首先，我们建立一个示范数据库表：

字段名       数据类型      大小
id (Primary key) Unsigned INT   4 bytes
firstName        Char(50)       50 bytes
lastName         Char(50)       50 bytes
emailAddress     Char(100)      100 bytes
注意：使用char是为了指定准确的磁盘占用大小。这个示范数据库包含500万行，而且没有索引。我们将分析一些查询语句的性能，一个是使用主键id（有序）查询，一个是使用firstName（非关键无序字段）。

例1

我们的示范数据库有r=5,000,000条记录，每条记录长度R=204字节而且使用MyISAM引擎存储（默认数据块大小为B=1024字节），这张表的块因子（blocking factor）会是bfr = (B/R) = 1024/204 = 5 条记录每磁盘数据块。保存这张表所需要的磁盘块为N = (r/bfr) = 5000000/5 = 1,000,000 blocks。

在id字段上的线性搜索平均需要N/2 = 500,000块访问来找到一条记录假设id字段是查询关键值，不过既然id字段是有序的，可以执行一个二分查询，这样平均只需要访问log2 (1000000) = 19.93 = 20 个数据块。我们马上就看到了极大的提高。

现在firstName字段既不是有序的，无法执行二分搜索，数值也不具有唯一性，所以对这张表的查找必须到最后一个记录即全表扫描N = 1,000,000个数据块访问。这就是索引用来改进的地方。

假如索引记录只包含一个索引列以及一个指向原记录数据的指针，那么它显而易见会比原记录（多列）要小。所以索引本身所需要的磁盘块要更少，扫描数目也少。firstName索引表结构如下：

Field name       Data type      Size on disk
firstName        Char(50)       50 bytes
(record pointer) Special        4 bytes
注意: MySQL里的指针按表大小的不同分别可能是 2, 3, 4 或 5 个字节。

例2

假设我们的数据库有r = 5,000,000 条记录，建立了一个长R = 54字节的索引，并且使用默认磁盘块大小为1,024字节。那么该索引的块因子为bfr = (B/R) = 1024/54 = 18 条记录每磁盘块。容纳这个索引表总共需要的磁盘块为N = (r/bfr) = 5000000/18 = 277,778 块。

现在使用FirstName字段来进行搜索就可以利用索引来提高性能。这允许使用一个二分查找，平均log2 (277778) = 18.08 -> 19次数据块访问。找到实际记录的地址，这需要进一步的块读取，这样总数达到19 + 1 = 20次数据块访问，这和非索引表的数据块访问次数有天壤之别。

什么时候使用索引（When should it be used?）

鉴于创建索引需要额外的磁盘空间（上面的例子需要额外的277778个磁盘块），以及太多的索引会导致文件系统大小限制所产生的问题，所以对哪些字段建立索引，什么情况下使用索引，需要审慎考虑。

由于索引只是用来加速数据查询，那么显然对只是用来输出的字段建立索引会浪费磁盘空间以及发生插入、删除操作时的处理时间，所以这种情况下应该尽量避免。此外鉴于二分搜索的特性，数据的基数或独立性是很重要的。在基数为2的字段上建立索引，将把数据分割一半，而基数为1000则将返回大约1000条记录。低基数的二分查找效率将降低为一个线性排序，而且查询优化器可能会在基数小于记录数某个比例时（如30%）的情况下将避免使用索引而直接查询原表，所以这种情况下的索引浪费了空间。

建立索引的几大原则

1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录
4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

再有就是MySQL的存储引擎：

MySQL5.5以后默认使用InnoDB存储引擎，其中InnoDB和BDB提供事务安全表，其它存储引擎都是非事务安全表。

最常使用的2种存储引擎:1.Myisam是Mysql的默认存储引擎，当create创建新表时，未指定新表的存储引擎时，默认使用Myisam。每个MyISAM在磁盘上存储成三个文件。文件名都和表名相同，扩展名分别是.frm（存储表定义）、.MYD(MYData，存储数据)、.MYI(MYIndex，存储索引)。数据文件和索引文件可以放置在不同的目录，平均分布io，获得更快的速度。2.InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比Myisam的存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

各存储引擎之间的区别

为了做出选择哪一个存储引擎的决定，我们首先需要考虑每一个存储引擎提供了哪些不同的核心功能。这种功能使我们能够把不同的存储引擎区别开来。我们一般把这些核心功能分为四类:支持的字段和数据类型、锁定类型、索引和处理。一些引擎具有能过促使你做出决定的独特的功能，我们一会儿再仔细研究这些具体问题。

字段和数据类型

虽然所有这些引擎都支持通用的数据类型，例如整型、实型和字符型等，但是，并不是所有的引擎都支持其它的字段类型，特别是BLOG(二进制大对象)或者TEXT文本类型。其它引擎也许仅支持有限的字符宽度和数据大小。

这些局限性可能直接影响到你可以存储的数据，同时也可能会对你实施的搜索的类型或者你对那些信息创建的索引产生间接的影响。这些区别能够影响你的应用程序的性能和功能，因为你必须要根据你要存储的数据类型选择对需要的存储引擎的功能做出决策。

锁定

数据库引擎中的锁定功能决定了如何管理信息的访问和更新。当数据库中的一个对象为信息更新锁定了，在更新完成之前，其它处理不能修改这个数据(在某些情况下还不允许读这种数据)。

锁定不仅影响许多不同的应用程序如何更新数据库中的信息，而且还影响对那个数据的查询。这是因为查询可能要访问正在被修改或者更新的数据。总的来说，这种延迟是很小的。大多数锁定机制主要是为了防止多个处理更新同一个数据。由于向数据中插入信息和更新信息这两种情况都需要锁定，你可以想象，多个应用程序使用同一个数据库可能会有很大的影响。

不同的存储引擎在不同的对象级别支持锁定，而且这些级别将影响可以同时访问的信息。得到支持的级别有三种:表锁定、块锁定和行锁定。支持最多的是表锁定，这种锁定是在MyISAM中提供的。在数据更新时，它锁定了整个表。这就防止了许多应用程序同时更新一个具体的表。这对应用很多的多用户数据库有很大的影响，因为它延迟了更新的过程。

页级锁定使用Berkeley DB引擎，并且根据上载的信息页(8KB)锁定数据。当在数据库的很多地方进行更新的时候，这种锁定不会出现什么问题。但是，由于增加几行信息就要锁定数据结构的最后8KB，当需要增加大量的行，也别是大量的小型数据，就会带来问题。

行级锁定提供了最佳的并行访问功能，一个表中只有一行数据被锁定。这就意味着很多应用程序能够更新同一个表中的不同行的数据，而不会引起锁定的问题。只有InnoDB存储引擎支持行级锁定。

建立索引

建立索引在搜索和恢复数据库中的数据的时候能够显著提高性能。不同的存储引擎提供不同的制作索引的技术。有些技术也许会更适合你存储的数据类型。

有些存储引擎根本就不支持索引，其原因可能是它们使用基本表索引(如MERGE引擎)或者是因为数据存储的方式不允许索引(例如FEDERATED或者BLACKHOLE引擎)。

事务处理

事务处理功能通过提供在向表中更新和插入信息期间的可靠性。这种可靠性是通过如下方法实现的，它允许你更新表中的数据，但仅当应用的应用程序的所有相关操作完全完成后才接受你对表的更改。例如，在会计处理中每一笔会计分录处理将包括对借方科目和贷方科目数据的更改，你需要要使用事务处理功能保证对借方科目和贷方科目的数据更改都顺利完成，才接受所做的修改。如果任一项操作失败了，你都可以取消这个事务处理，这些修改就不存在了。如果这个事务处理过程完成了，我们可以通过允许这个修改来确认这个操作。