高性能MySQL（个人学习笔记）

土豆与红薯

已于 2024-05-17 08:24:50 修改

阅读量1.7k

点赞数 20

文章标签： java mysql 后端数据库 database

于 2024-05-17 08:18:53 首次发布

本文链接：https://blog.csdn.net/yxtudou/article/details/138990597

版权

MySQL

1 架构与基础内容

1.1 逻辑架构

在优化器下有一个执行器，作用为与存储引擎进行交互。

连接：每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行，该线程只能轮流在某个CPU核心或者CPU中运行。

优化与执行：解析器负责解析查询，创建内部解析树，进行重写查询、决定表读取顺序、选择合适的索引等优化。可以通过关键字hint优化器，也可以请求优化器explain优化过程各个因素，便于用户重构查询和schema、修改相关配置，提高运行效率。

SELECT语句解析前，服务器回先检查查询缓存，若能找到其中对应查询，服务器就不会在执行查询解析。

1.2 并发控制

此处讨论两个层面的并发控制：1、服务器层。2、存储引擎层。

读写锁：设置两种类型的锁，共享锁（读锁）和排他锁（写锁）。

读锁是共享的，相互不阻塞。即多个客户在同一时刻可以同时读取一个资源，而不相互干扰。

写锁是排他的，一个写锁会阻塞其他的写锁和读锁。

最终能够保证在给定的时间内，只有一个用户能够写入，方式其他用户读取正在写入的资源。

锁粒度：主要有表锁和行级锁。

表锁（table lock）：MySQL最基本的锁策略，开销最小。会锁定整张表，一个用户在对表进行写操作前，需要获得写锁，这会阻塞其他用户对该表的所有读写操作。没有写锁，其他用户才能获取读锁。读锁不相互阻塞。

READ LOCAL表锁支持某些类型的并发写操作。写锁优先级高于读锁，一个写锁请求可能会被插入到读锁队列前面。

存储引擎可以管理自己的锁，MySQL本身会使用各种有效的表锁来实现不同的目的。如：服务器会为诸如ALTER TABLE之类的语句使用表锁，而忽略存储引擎锁机制。

行级锁（row lock）：最大程度支持并发，带来了最大开销。在InnoDB和XtraDB以及一些其他存储引擎实现了行级锁。行级锁只在存储引擎层实现，MySQL服务器层并没有实现。服务器层不了解存储引擎的锁实现。

1.3 事务

四大特性：ACID，原子性、一致性、隔离性、持久性。

原子性：一个事务被视为一个不可分割的最小工作单元，整个事务所有操作要么全部提交成功，要么失败回滚，不可能只执行一部分。

一致性：**数据库总是从一个一致性状态转换到另外一个一致性状态。**即在执行中间几条语句之间时系统崩溃，因为事务没有提交，所以事务中所做的修改也不会保存到数据库中。

隔离性：一个事务所做的修改在最终提交以前，对其他事务是不可见的。

持久性：事务一旦提交，则其所做的修改就会永久保存到数据库中，即使系统崩溃，修改的数据也不会丢失。

根据业务是否需要事务处理，来选择适合的存储引擎。对于一些不需要事务的查询类应用，选择非事务性的存储引擎，可以获得更高的性能。若存储引擎不支持事务，也可以通过LOCK TABLES语句提供一定程度的保护。

**隔离级别：**SQL定义了四种隔离级别，每个级别都规定了事务所作的修改哪些可见，哪些不可见。较低的隔离通常可以执行更高的并发，开销也越低。

四种隔离级别：

READ UNCOMMITTED（未提交读）：事务中的修改，即使没有提交，对其他事务也是可见的。事务可以读取未提交的数据，称为脏读。对于其他隔离级别来说，性能提升不大，缺乏很多好处。

READ COMMITED（提交读）：一个事务从开始到提交之前，所做的任何修改对其他事务都是不可见的。这个级别也称为不可重复读，即两次执行相同的查询，可能会得到不一样的结果。

REPEATABLE READ（可重复读）：保证了在同一事务中多次读取同样记录的结果是一致的。无法解决幻读问题。幻读：当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入新的记录，之前的事务再次读取该范围的记录会产生幻行。注：InnoDB和XtraDB村粗引擎通过多版本的并发控制（MVCC）解决了幻读问题。

SERIALIZABLE（可串行化）：是最高的隔离级别。通过强制事务串行执行，避免幻读问题。会在每一行数据都加锁，故可能导致大量超时和锁争用问题。实际应用中很少使用该级别，在非常需要保证数据一致性并接受无并发情况下才使用该级别。

死锁：两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。多个事务试图以不同顺序锁定资源就可能会导致死锁。InnoDB目前解决死锁的方式是将持有最少行级排他锁的事务进行回滚。

事务日志：存储引擎在修改表的数据时只需要修改其内存拷贝，在把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。**事务日志采用追加方式，写日志的操作是磁盘一小块区域顺序IO。**事务日志持久之后，内存中被修改的数据在后台可以慢慢刷回磁盘。目前大多数存储引擎都是这样实现，通常称为预写式日志。修改数据需要写两次磁盘。

自动提交：MySQL默认采用自动提交模式，如果不是显示开始一个事务，则每个查询都被当作是一个事务执行提交操作。

可以通过设置AUTOCOMMIT变量来启用或禁用自动提交模式。当AUTOCOMMIT=0时，所有的查询都在一个事务中，直到显式执行COMMIT提交或ROLLBACK回滚。该事务结束时，同时又开始一个新事务。Myisam非事务型存储引擎。

在数据定义语言（DDL）中，若是会导致大量数据改变的操作，这些命令会在执行之前强制执行COMMIT提交当前的活动事务。

MySQL可以执行SET TRANSACTION ISOLATION LEVEL命令来设置隔离级别，新的隔离级别会在下一个事务开始的时候生效。可以在配置文件设置整个数据库的隔离级别，也可以只改变当前会话的隔离级别。

例：SET TRANSACTION ISOLATION LEVEL READ COMMITTED

事务中混合使用存储引擎：MySQL服务器层不管理事务，事务由下层的存储引擎实现，同一个事务中多种存储引擎是不可靠的。

在事务中混合使用事务型和非事务型的表(如：InnoDB和MyISAM)，正常提交没有问题，但是非事务型的表无法回滚。

隐式锁定和显式锁定：InnoDB采用两阶段锁定协议，事务执行时，随时可以锁定。锁只有在执行COMMIT或ROLLBACK才会释放，并且所有锁同时释放。InnoDB会根据隔离级别在需要时自动加锁。

InnoDB支持特定语句进行显式锁定：

SELECT … LOCK IN SHARE MODE

SELECT … FOR UPDATE

InnoDB的行级锁已经工作得很好了

1.4 多版本并发控制（MVCC）

MVCC式行级锁的一个变种，在很多情况下避免了加锁操作，从而降低开销。虽然机制不同，大多实现了非阻塞的读操作，写操作只锁定必要的行。

机制：通过保持数据在某个时间点的快照来实现，即不管执行多长时间，每个事务看到的数据是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

不同存储引擎的MVCC实现不同，典型有乐观并发控制和悲观并发控制。

InnoDB的MVCC实现原理：通过在每行记录后面保存的两个隐藏列实现。这两个列，一个保存行的创建时间。另一个保存行的过期时间。存储的并不是实际的时间值，而是系统版本号。每开启一个事务，系统版本号都会递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

REPEATABLE READ隔离级别下的MVCC：

SELECT：

InnoDB根据以下两个条件检查每行记录，需要都满足：

1.查询版本早于当前事务版本的数据行。确保事务读取的行，要么是事务开始前已经存在的，要么是事务自身插入或修改过的。

2.行的删除版本要么未定义，要么大于当前事务版本号。确保事务读取到的行，在事务开始之前未被删除。

INSERT：

InnoDB为新插入的每一行保存当前系统版本号作为行版本号。

DELETE：

InnoDB为删除的每一行保存当前系统版本号作为行删除标识。

UPDATE：

InnoDB为插入一行新纪录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

优点：无需加锁

缺点：需要额外存储开销

MVCC只支持REPEATABLE和READ COMMITTED 两个隔离级别，其他隔离级别与MVCC不兼容。因为READ UNCOMMITTED总是读取最新的数据行，SERIALIZABLE会对所有读取的数据行进行加锁。

1.5 存储引擎

表的定义是在服务层统一处理，而不同存储引擎保存数据和索引的方式不同。

可以使用SHOW TABLE STATUS命令来显示表的相关信息。

统计信息的含义：

Name：表名

Engine：表的存储引擎类型，在旧版本中名字叫Type

Row_format：行的格式。对于MyISAM，可选的值为Dynamic、Fixed或Compressed。其中，Dynamic的行长度是可变的，一般包含可变长度字段，如：VARCHAR或BLOB。Fixed行长度是固定的，只包含固定长度的列，如CHAR和INEGER。Compressed的行只在压缩表中存在。

Rows：表中的行数。对于MyISAM，该值精确，对于InnoDB，该值是估计值。

Avg_row_length:平均每行包含的字节数。

Data_length:表数据的大小（以字节为单位）

Max_data_length：表数据的最大容量，该值和存储引擎有关

Index_length：索引的大小（以字节为单位）

Data_free：对于MyISAM表，表示已分配但是目前没有使用的空间，该空间包括之前删除的行，以及后续可以被INSERT利用的空间

Auto_increment：下一个AUTO_INCREMENT值

Creat_time：表的创建空间

Update_time:表数据的最后修改时间

Check_time：使用CHECK TABLE命令或myisamchk工具最后一次检查表的时间

Collation：表默认字符集和字符列排序规则

Checksum：如果启用，保存整个表实时校验和

Create_options：创建表指定的其他选项。

Comment：该列包含一些其他的额外信息。对于MyISAM表，保存的是表在创建时带的注释。对于InnoDB表，保存的是InnoDB表空间的剩余空间信息。如果是一个视图，则该列包含VIEW文本字样。

InnoDB所包含的文件是.frm和.ibd

MyISAM所包含的文件是.frm .MYI .MYD

InnoDB存储引擎

MySQL默认事务引擎。被设计为处理大量的短期事务，短期事务大部分情况正常提交，很少会回滚。

数据存储在表空间中。表空间是由InnoDB管理的一个黑盒子，由一系列的数据文件组成。

采用MVCC支持高并发，实现四个标准的隔离级别。默认隔离级别是REPEATABLE READ（可重复读），通过间隙锁策略防止幻读。间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中间隙进行锁定，防止幻影行的插入。

基于聚簇索引，对主键查询有很高的性能，二级索引必须包含主键列，如果主键列很大，则其他所有索引都很大，因此主键应该尽可能缩小。

InnoDB的存储格式是平台独立的，也就是说可以将数据和索引文件从Intel平台复制到PowerPC或Sun SPARC平台。

优化：包括从磁盘读取数据时采用的可预测性预读，能够在内存中创建hash索引以加速读操作的自适应哈希以及能够加速插入操作的插入缓冲区等。

InnoDB支持热备份，而其他不支持热备份的存储引擎需要获取一致性视图停止对所有表的写入，在读写混合的场景中，停止写入可能意味着停止读取。

MyISAM存储引擎

MySQL 5.1以及之前的版本，MyISAM是默认的存储引擎

不支持事务和行级锁，并且有崩溃后无法安全恢复的缺陷。

对于只读的数据，或者表比较小，可以忍受修复操作，则依然可以继续适用MyISAM

存储方式：表存储在两个文件中，数据文件和索引文件，分别以.MYD和.MYI作为扩展名。可以包含动态或静态（长度固定）行。在MySQL5.0中，如果采用变长行，则默认配置只能处理256TB的数据。修改MyISAM表指针长度，可以修改MAX_ROWS和AVG_ROW_LENGTH选项值来实现，两者相乘就是表可能达到的最大大小。

特性：

1.加锁和并发：对整张表加锁，而不是针对行。读取会对需要读到的所有表加共享锁，写入则对表加排他锁。在表有读取查询的同时，也可以往表中插入新的记录（并发插入）。

2.修复：可以手工或自动执行检查和修复操作。通过CHECK TABLEmytable来检查表错误，REPAIR TABLE mytable进行修复。如果MySQL服务器已经关闭，可以通过myisamchk命令行工具进行检查和修复操作。

3.索引特性：即使是BLOB和TEXT等长字段，也可以基于前500个字符创建索引。支持全文索引，这是一种基于分词创建的索引，支持复杂的查询。

4.延迟更新索引键。创建MyISAM表时，如果指定DELAY_KEY_WRITE选项，每次修改执行完成时，不会立刻将修改的索引写入磁盘，而回写到内存的键缓冲区中，清理键缓冲区或关闭表时会将对应索引快写入磁盘。

压缩表：

若创建表并导入数据后，不会进行修改操作，适合采用压缩表。

可以采用myisampack对MyISAM表进行压缩，并且无法修改。

优点：极大减少磁盘空间占用，也可以减少磁盘IO，从而提升查询性能。压缩表也支持索引，但是索引时只读的。

性能：MyISAM引擎设计简单，数据紧密格式存储。某些场景下性能很好，有些服务器级别的性能扩展限制，如：索引键缓冲区的mutex锁，mariaDB基于段的索引键缓冲区来避免该问题。

MySQL内建的其他存储引擎

ARCHIVE引擎

只支持INSERT和SELECT操作，在MySQL5.1之前不支持索引。

特点：会缓存所有的写并利用zlib对插入进行压缩，比MYISAM表磁盘IO更少。

每次SELECT查询都需要执行全表扫描，故更适用于日志和数据采集类工作。

支持行级锁和专用缓冲区，所以可以实现高并发的插入。在一个查询开始知道返回表中存在的所有行数之前，Archive引擎会组织其他的SELECT执行，来实现一致性读。也实现了在批量插入之前对读操作是不可见的。

不是事务型引擎，但是在批量插入之前对读操作时不可见的。

BLACKHOLE引擎

没有实现任何的存储机制，会丢弃所有插入的数据，不做任何保存。

Memory引擎

所有数据存储在内存中，重启后会丢失，并且不需要进行磁盘IO。适用于数据不会被修改的情况，Memory表至少比MyISAM表快一个数量级。

应用场景：

用于查找或映射表
用于缓存周期性聚合数据的结果
用于保存数据分析中产生的中间数据

Memory支持Hash索引，查找速度快，并且采用表级锁，并发写入的性能较低，不支持BLOB或TEXT类型的列，每行的长度是固定的，即使指定了VARCHAR列，实际存储也会转换成CHAR，可能会导致内存浪费。

若MySQL在执行查询时，需要使用临时表来保存中间结果，内部使用临时表就是Memory。若结果超过Memory限制，内部含有BLOB或TEXT字段，临时表会转换为MyISAM表。

Merge引擎

MyISAM的一个变种，由多个MyISAM合并而来的虚拟表。可用于日志或数据仓库类应用。

NDB集群

MySQL服务器、NDB集群存储引擎、以及分布式的、share-nothing的、容灾的、高可用的NDB数据库集合，被称为MySQL集群。

选择合适的引擎

MySQL将InnoDB作为默认存储引擎。除非需要用到某些InnoDB不具备的特性，并且没有其他变法可以替代，否则都应该使用InnoDB引擎。

若要使用全文索引，优先考虑InnoDB加上Sphinx的组合。**建议不要混合使用多种存储引擎。**混合存储对一致性备份和服务器参数配置都带来了一些困难。

应用存储引擎需考虑的几个方面：

事务：需要支持事务，则InnoDB。若不需要，且主要是SELECT或INSERT，则选择MyISAM。
备份：若需要在线热备份，则选择InnoDB是基本要求
崩溃修复：MyISAM崩溃概率高于InnoDB，故建议InnoDB。
特性：如应用依赖聚簇索引，故需要选择InnoDB。

日志型应用

应用对插入速度有很高要求，则适合选取MyISAM和Archive这类开销较低的存储引擎。

若需要对日志做分析报表，有两种解决方案：

利用MySQL内置复制方案复制一份数据到备用库，在备库上执行消耗时间的查询。
在日志记录表的名字中包含年和月的信息，可以在没有插入操作的历史表中进行频繁的查询，无需干扰最新当前表上的插入。

只读或大部分只读的表

例如编制类目或者分列清单（工作岗位、竞拍、不动产等）这类应用场景是典型的读多写少的场景。

选用MyISAM较为适合，有崩溃后无法修复的风险。

MyISAM不一定在所有场景中都比InnoDB快。

故InnoDB也可以进行选择。

订单处理

订单一定包含事务，故选取InnoDB。

电子公告牌和主体讨论论坛

核心表读写压力大，为保证核心表数据一致性，锁称为资源争用主要因素。

CD-ROM应用

考虑MyISAM和MyISAM压缩表，表可以隔离并且在不同介质上拷贝。

大数据量

在巨大数据量下，采用MyISAM，崩溃后数据恢复就是噩梦。

因此需要建立数据仓库，适合Infobright或TokuDB。

转换表的引擎

有三种方法能够将表的存储引擎转换成另外一种引擎。

ALTER TABLE：

这是最简单的方法。可以适用于任何存储引擎，但是有一个问题是，需要执行很长时间。复制期间可能会消耗系统所有的IO能力，同时原表上会加上读锁。

例如：ALTER TABLE mytable ENGINE=InnoDB

将mytable的引擎修改为InnoDB
导出和导入；

可以使用mysqldump将数据导出到文件，然后修改文件中的CREATE TABLE语句的存储引擎选项，同时修改表名。注意mysqldump默认会自动在CREATE TABLE前加上DROP TABLE语句。
创建和查询：

该方法综合了第一种方法和第二种方法的安全，无需导出所有数据，而是先创建一个新的存储引擎的表，然后利用INSERT…SELECT语法来导出数据。

数据量不大的话，比较适合，如果数据量很大，则可以考虑做分批处理，针对每一段数据执行事务提交。pt-online-schema-change工具可以方便执行这个过程。

2 Schema与数据类型优化

2.1 数据类型优化

MySQL选择正确的数据类型对于高性能来说至关重要。几个简要的优化原则：

尽量使用可以正确存储数据的最小数据类型。确保没有低估所需要存储值范围，schema多个地方增加数据类型范围是耗时和痛苦操作。
选取简单的数据类型。因为通常需要更少CPU周期。例如整型比字符操作代价更低。

例如：应该适用MySQL内建的类型而不是字符串来存储日期和时间，另外一个应该是用整型存储IP地址。
尽量避免NULL。NULL会使得索引、索引统计、值比较都更为复杂。可为NULL的列会使用更多的存储空间，在MySQL中也需要进行特殊处理。NULL的列被索引时需要一个额外的字节，MyISAM里面还可能导致固定大小的索引变为可变大小的索引。

整数类型

整数类型包括：TINYINT(8),SMALLINT(16),INT(32),BIGINT(64)

其存储范围为-2^(N-1)到2(N-1)-1。其中N为存储空间的位数。

整数可选UNSIGNED，代表不允许负值，可以使得正数范围提高一倍。

整数计算一般使用64位的BIGINT

实数类型

带有小数的数字。

FLOAT和DOUBLE支持使用标准的浮点运算，FLOUT使用4个字节，DOUBLE使用8个字节。DECIMAL可以指定小数点前后所允许的最大位数。例如，DECIMAL（18，9）小数点两边各存储9个数字，一共使用9个字节，小数点前数字使用4个字节，小数点后数字使用4个字节，小数点本身占1个字节。

MySQL 5.0和更高版本中的DECIMAL类型允许使用最多65个数字。

DECIMAL用于存储精确的小数。在计算中DECIMAL会转化为DOUBLE类型。

MYSQL使用DOUBLE作为内部浮点计算的类型。由于需要额外的空间和计算开销，尽量在对小数进行精确计算使用DECIMAL。（例如存储财务数据）。数据量较大时，考虑使用BIGINT代替，将需要存储的货币单位根据小数的位数乘以相应倍数即可。

字符串类型

主要是VARCHAR和CHAR两种主要的字符串类型。

此处是InnoDB或者MyISAM两种存储引擎的存储方式。

VARCHAR

用于存储可变长字符串，相比于定长更加节省空间，有一种情况例外，即通过ROW_FORMAT=FIXED来规定每一行使用定长存储。

需要额外空间：VARCHAR需要使用1或2个额外字节记录字符串的长度，列最大长度小于或等于255字节，则1个字节表示字符串长度，否则2个字节。UPDATE时行可能变长。存储空间不够时，MyISAM会拆成不同片段存储，InnoDB会分裂页来将行放进页内。InnoDB将过长的VARCHAR存储为BLOB。

适用场景：

字符串列最大长度比平均长度大很多。
列更新少，碎片不是问题。
使用复杂字符集，每个字符都使用不同字节数进行存储。

CHAR

定长字符串，MySQL总是根据定义的字符串长度分配足够的空间，会删除末尾的空格。并且会根据需要采用空格填充以方便比较。

CHAR比较适合定长的值和经常变更的值，因为定长的CHAR不容易产生碎片。例如：CHAR适合存储密码的MD5值，因为这就是一个定长值。对于经常变更的值，使用CHAR也更加不容易产生碎片

比较短的列，CHAR也更加有效率。例如判断正误的Y or N，若使用VARCHAR（1），则需要2个字节，因为还有一个记录长度的字节。

数据如何存储取决于存储引擎，并非所有的存储引擎都会按照相同的方式处理定长和变长字符串。

Memory引擎支支持定长的行，即使有变长字段也会根据最大长度分配最大空间。对于填充和截取空格的行为在不同存储引擎时一样的，因为是在MySQL服务器层进行处理。

BINARY和VARBINARY存储的是二进制字符串，跟常规字符串非常相似，但是存储的是字节码不是字符，填充使用\0，检索时也不会去掉填充值。当需要存储二进制数据，并且MySQL使用字节码而不是字符进行比较时，这些类型比较有用

BLOB和TEXT类型

**为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。**对应的二进制类型是：TINYBLOB、SMALLBLOB、BLOB、MEDIUMBLOB。字符类型是TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT、LONGTEXT。

与其他类型不同，**MySQL把BLOB和TEXT当作一个独立的对象进行处理，**存储引擎在存储时会做特殊处理。当值太大时，InnoDB会使用专门的外部存储区域来进行存储，此时每个值在行内需要1-4个字节存储指针，在外部存储区域存储实际值。

二者区别：BLOB存储二进制数据，TEXT有字符集和排序规则

排序：MySQL对BLOB和TEXT列进行排序与其他类型不同，仅对每个列的前max_sort_length字节而不是整个字符串做排序。

索引：MySQL不能将BLOB和TEXT列全部长度的字符串进行索引，也不能使用这些索引消除排序。

Memory引擎不支持BLOB和TEXT

使用枚举代替字符串

ENUM枚举列可以把一些不重复的字符串存储成一个预定义的集合，MySQL存储枚举时会根据列表值数量压缩到一个或两个字节中。

MySQL会将每个值在列表中的位置保存为整数，并在表.frm文件中保存数字-字符串映射关系的查找表。

枚举字段是按照内部存储的整数而不是定义的字符串来进行排序的。可以使用FIELD（）函数显式指定排序顺序，但是会导致MySQL无法利用索引消除排序。

缺点：**字符串列表是固定的，添加或删除字符串必须使用ALTER TABLE。**因此对于一系列未来可能改变的字符串，不建议使用枚举。

日期和时间类型

MySQL能存储的最小时间粒度为妙，而计算可以使用微秒粒度来进行临时计算。MySQL提供两种类似的日期类型：DATETIME和TIMESTAMP。

DATETIME

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

默认情况下使用ANSI标准定义的日期和时间表示方法。

TIMESTAMP

该类型保存了从1970年1月1日午夜以来的秒数，和UNIX时间戳相同。只使用4个字节的存储空间。只能表示1970年到2038年。TIMESTAMP没有指定第一个列的值，MySQL则设置这个列的值为当前时间。TIMESTAMP列默认为NOT NULL，这和其他类型不同。

可以使用BIGINT类型存储微秒级的时间戳，或者使用DOUBLE存储1秒之后的小数部分。

建议选择TIMESTAMP，空间效率更高。

位数据类型

所有位类型，不管底层存储格式和处理方式如何，从技术上来说都是字符串类型。

BIT

可以使用BIT列存储一个或多个true/false值，BIT列最大长度是64个位。MyISAM会打包存储所有的BIT列，故17个单独的BIT列只需要17个位存储。MySQL把BIT当作字符串类型，而不是数字类型。例如，存储‘00111001’到BIT（8）的列并对其进行检索，得到的内容是字符码为57的字符串，也就是说得到ASCII码为57的字符"9"，但是在数字上下文场景中，得到的是数字57。

**大部分应用应该避免使用这种类型。**若想在一个bit空间内存储true/false值，另一个方法为创建一个可以为空的CHAR(0)列，该列可以保存空值（NULL）或者长度为0的字符串（空字符串）。

SET

如果需要保存很多true/false值，可以考虑合并这些列到一个SET数据类型，在MySQL内部以一系列打包的位集合进行表示。可以使用FIND_IN_SET（）和FIELD()来进行查询。缺点是改变列的定义代价较高，需要ALTER TABLE，一般来说，无法在SET列上通过索引查找。

在整数列上按位操作

可以用一个整数包装一系列的位来替代SET，例如，可以把8个位包装到一个TINYINT中，并按位进行操作。可以在应用中为每个位定义名称常量进而简化。

这种办法好处是可以不使用ALTER TABLE改变字段代表的枚举值，缺点是查询语句更难写。

应用：保存权限的访问控制列表（ACL），每个位或SET元素代表一个值。

标识符

为标识列选取合适的数据结构很重要，常见的用法有：用标识列与其他值进行比较（如关联操作），或者通过标识列寻找其他列，标识列也可以在另外的表中作为外键使用。为标识列选择数据类型时，应该选择跟关联表中对应列一样的类型。

选择标识列的类型，需要考虑存储类型和如何进行对比。

一旦选择了一种类型，要确保在所有关联表中都使用同样的类型，并尽量选择最小的数据类型。

下面是一些可选择的方案：

整数：整数通常是标识列最好的选择，因为效率高且可以AUTO_INCREMENT。

ENUM和SET：不建议。

字符串：不建议。比较消耗空间，效率较低。MyISAM表中使用字符串作为标识列要特别小心，因为其默认对字符串采用压缩索引，会导致查询慢很多。

特殊数据类型

例如：低于秒级的时间戳、IP地址。

应该使用无符号整数来存储IP地址，MySQL提供INET_ATON()和INET_NTOA()函数在这两种方法之间转换。

2.2 MySQL schema设计的陷阱

避免某些在MySQL下发生的特定错误，提供在MySQL特定实现下的更好替代方案。

过多的列

MySQL存储引擎API工作时需要在服务器层和存储引擎层通过行缓冲来拷贝数据，在服务器层将缓冲内容解码为各个列，**从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。**当一个有数千个字段的表只有一小部分列被实际用到时，转换的代价就会很高。

太多的关联

实体-属性-值是一个糟糕的设计模式。MySQL限制每个关联操作只能有61张表，很多EVA数据库最后会超过这个限制。若希望查询执行快速且并发性号，单个查询最好在12个表内做关联。

过度使用枚举

防止过度使用枚举。当需要在枚举列表中增加一个新的字段时，就需要一个ALTER TABLE操作。在MySQL 5.0以及更早版本中，ALTER TABLE是一种阻塞操作。即使在之后的更新版本中，若不是在列表末尾增加值也会需要ALTER TABLE。

枚举（ENUM）和集合（SET）混用

ENUM允许在列中存储一组定义值的单个值，SET列允许在列中存储一组定义值中一个或多个值。有时候集合内的情况不会同时出现时，应该使用枚举列代替集合列。

过于极端避免NULL

避免使用NULL有一些好处。即使存储一个空值到列表中时，不一定非得使用NULL，也可以使用0、某个特殊值、空字符串。

但是不要走极端。在一些场景上，使用NULL更好。从特定类型的值域选择不可能的值可能会带来很多问题。

2.3范式与反范式

数据表示方法有范式化、反范式化以及两者折中。

范式化数据库中，事实数据只会出现一次，而反范式化的数据库中，信息是冗余的。

范式化的优点和缺点

优点：

**更新速度：**更新操作更快
**冗余数据：**较少的重复数据，修改次数减少
**内存消耗：**存储负载小，内存消耗小
**更少操作：**检索时更少使用DISTINCT或GROUP BY

缺点：

通常需要较多次数的关联。代价昂贵，并让一些索引策略失效。

反范式化的优点和缺点

优点：

所有数据都在一张表中，可以很好避免关联。即使查询最差的情况，没有使用索引，进行全表扫描，这样避免了随机IO。

缺点：

数据冗余，需要较高的存储代价。

混用范式化和反范式化

在实际生产环境中，经常混用范式化和反范式化。即使用部分范式化的schema、缓存表以及其他技巧。最常见反范式化数据方法是复制或缓存，在不同的表中存储相同的特定列。

缓存衍生值也有用。在表中根据需求来新建列进行更新值。

2.4 缓存表和汇总表

有时提升性能最好方法为在同一张表中保存衍生冗余的数据。有时需要一张完全独立的汇总表或缓存表。

缓存表：存储那些可以比较简单从schema其他表获取数据，但是每次获取数据比较慢的表。（获取速度相对快）。

汇总表：使用GROUP BY语句聚合数据的表。（数据不是逻辑冗余的）。也有人用累积表称呼这些表。（累积表）

例子：一个比较繁忙的网站，可以每小时生成一张汇总表，使用简单查询就能实现，并且比实时维护计数器高效。实时计算统计值是很昂贵的操作，要么需要扫描表中大部分数据，要么查询语句只能在某些特定的索引上才能有效运行，而这类特定索引一般对UPDATE操作有影响，所以并不希望创建这样的索引。

缓存表对优化搜索和检索查询语句很有效，这些查询语句通常需要特殊表和索引结构，和普通OLTP操作的表有区别。

例子：可能需要很多不同的索引组合来加速各种类型查询，有时就需要一种只包含主表中部分列的缓存表。一个技巧为对缓存表使用不同的存储引擎。如：主表使用InnoDB，用MyISAM作为缓存表的引擎会得到更小的索引空间，并全文搜索。

使用缓存表或汇总表时，需要考虑实时维护数据还是定期重建。定期重建可以节省资源，保持表不会有很多碎片，以及有完全顺序组织的索引。

重建汇总表和缓存表：

在进行该项操作时，需要保证数据在操作时依然可用，需要通过影子表来实现**。影子表**是在真实表背后创建的表，完成建表后，通过原子的重命名操作切换影子表和原表。

例如：如果要重建my_summary，则可以先创建my_summary_new，然后填充好数据，最后和真实表做切换。

像上面的例子一样，在将my_summary这个名字分配给新建的表之前将原始的my_summary表重命名为my_summary_old，就可以在下一次重建之前一直保留旧版本的数据，如果新表有问题，则可以很容易地快速回滚操作。

物化视图

实际上是预先计算并且存储在磁盘上的表。可以通过各种各样的策略进行刷新和更新。MySQL不原生支持物化视图，Flexviews支持并简单创建和维护。提供以下功能：

变更数据抓取（CDC），可以读取服务器二进制日志并解析相关行变更。
帮助创建和管理视图定义的存储过程。
应用变更到数据库中物化视图的工具。

对比传统汇总表和缓存表，物化视图不需要通过查询原始数据来更新视图。

Flexviews不仅仅可以获得每行的新值，还可以不需要查找源表就能知道每行数据的旧版本，计算增量数据比从源表中读取数据的效率高很多。

使用方案：先写出一个SELECT语句描述想从已经存在的数据库中得到数据，可能包含关联和聚合。Flexviews有一个辅助工具可以转换SQL语句到Flexviews的API调用，监控数据库的变更并且转换后用于更新存储物化视图的表。最后可以简单查询物化视图来替代需要检索的表。

计数器表

在表中保存计数器，则在更新计数器可能会遇到并发问题。而可以用计数器表这种表缓存一个朋友的朋友数、文件下载次数等。并可以使计数器表小而快，并帮助避免查询缓存失效等问题。

可以设计多行，进行计数时可以随机选取一行进行添加，并希望减少表的行数，以避免表变得太大，可以写一个周期执行的任务，合并所有结果到0号槽，并删除其他的槽。

on duplicaate key update：根据某一字段值查询数据库中是否有记录，有则更新，没有则加入。

2.5 加快ALTER TABLE操作的速度

背景：MySQL的ALTER TABLE操作的性能对于大表来说影响巨大。**执行大部分修改表结构的操作是用新的结构创建空表，从旧表中查出所有数据插入新表，然后删除旧表。**这项操作耗费内存资源巨大，用时较长。如果内存不足而表又很大，并有很多索引的时候更是如此。

大部分ALTER TABLE操作会导致MySQL服务中断。DDL时有两种常见的技巧：

在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换。
影子拷贝：用要求的表结构创建一张与源表无关的新表，然后通过重命名和删表操作交换两张表。可以使用的工具：online schema change、openark toolkit、Percona Toolkit。若使用Flexviews，可以通过CDC工具执行无锁的表结构变更。

可以通过ALTER COLUMN操作来改变列的默认值（只修改.frm文件），并且不用重建表，以节省开销。

只修改.frm文件

以下操作可能无需重建表：

移除一个列的AUTO_INCREMENT属性
增加、移除或更改ENUM和SET常量。如果移除的是已经有行数据用到其值的常量，查询将会返回一个空字符串值。

这种情况的基本方案是**为表结构新建.frm文件并替换掉以前的.frm文件。**流程如下：

创建一张有相同结构的表，并进行相应修改。
执行FLUSH TABLES WITH READ LOCK。会关闭所有正在使用的表，并禁止任何表被打开。
交换.frm文件
执行UNLOCK TABLES来释放第二步的读锁。

例子：给sakila.frm表的ratting列增加一个常量。

首先创建新表，并操作新表。

注意，是在常量列表的末尾增加一个新的值，如果把新增的值放在中间，例如：PG-3

之后，则会导致已经存在的数据的含义被改变：已经存在的R值将变成PG-14，而已经存在的NC-17将会成为R。

用操作系统命令交换.frm文件。

对表进行解锁。

删除为完成这个操作所建立的辅助表。

快速创建MyISAM索引

高效载入数据到MyISAM表中有一个常用技巧是先禁用索引、载入数据，然后重新启用索引。

原理：构建索引的工作被延迟到数据完全载入以后，这时可以通过排序来建立索引。可以提速并索引树的碎片更少、更加紧凑。

该方案对唯一索引无效，DISABLE KEYS只对非唯一索引有效。

一种骇客方法加速步骤。操作步骤：（没看懂）

用需要的表结构创建一张表，但是不包括索引
载入数据到表中以构建.MYD文件
按照需要的结构创建另外一张空表，这次要包含索引，这会创建需要的.frm和.MYI文件
获取读锁并刷新表
重命名第二张表的.frm和.MYI文件，让MySQL认为是第一张表的文件
释放读锁
使用REPAIR TABLE来重建表的索引，该操作会通过排序来构建所有索引，包括唯一索引。

对于InnoDB有一个类似技巧：依赖于InnoDB快速在线索引创建功能。先删除所有非唯一索引，然后增加新的列，最后重新创建删除掉的索引。

2.6 总结

尽可能保持东西小而简单。总结有以下原则：

尽量避免过度设计：例如会导致极其复杂查询的schema设计，或者有很多列的表设计
使用小而简单的合适数据类型，尽量避免NULL
尽量使用相同的数据类型存储相似或相关的值
小心VARCHAR:注意可变长字符串，在临时表和排序可能导致悲观按最大长度分配内存
标识列：尽量使用整形定义标识列
注意更新：避免使用MySQL已经遗弃的特性
枚举和集合：小心使用ENUM和SET

注意范式和反范式的结合使用。

ALTER TABLE是比较消耗资源的操作，大部分时间都会锁表并且重建整张表，大部分场景必须使用其他更常规的方法，例如在备机执行ALTER并在完成后把它切换为主库。

3 索引优化

索引：**存储引擎用于快速找到记录的一种数据结构。索引能轻易将查询性能提高几个数量级。**创建优化的索引需要重写查询，故和查询优化非常紧密。

3.1 索引基础

MySQL先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行返回。

索引可以包含一个或多个列，并且与其排列的顺序有关，**列的顺序十分重要，**因为MySQL只能高效使用索引的最左前缀列。例如：创建一个包含两个列的索引和创建两个包含一个列的索引不相同。

索引在存储引擎层实现，而不是服务器层实现。不同存储引擎索引工作方式不同，不是所有存储引擎都支持所有类型的索引。

索引类型

B-TREE

大多数MySQL引擎都支持该索引，Archive引擎例外。NDB集群存储引擎内部实际使用T-Tree数据结构，InnoDB使用的是B+Tree。

存储引擎使用B-Tree也以不同的方式，**MyISAM使用前缀压缩计算使索引更小，并通过数据的物理位置引用被索引的行。**而InnoDB按照数据原格式进行存储，并根据主键引用被索引的行。

B-Tree对索引列是顺序组织存储的，所以比较适合查找范围数据。

典型索引方式：

全值匹配：指和索引中所有列进行匹配
匹配最左前缀：只使用索引的第一列
匹配列前缀：只匹配某一列的开头部分
匹配范围值：匹配列的在一个范围内的值
精确匹配某一列并范围匹配另外一列：第一列全值匹配，第二列范围匹配
只访问索引的查询：就是覆盖索引，只用访问索引，无需访问数据行

索引除了按值进行查找外，还可以用于查询中的ORDER BY操作，即排序。

B-Tree索引的限制：

必须按照最左列开始查找
不能跳过索引中的列
某个列进行范围查找后，其右边所有列都无法使用索引优化查找。如果范围查询列1值的数量有限，那么可以通过使用多个等于条件来代替范围条件。

哈希索引

**基于哈希表进行实现，只有精确匹配所有列的查询才有效。**每行数据存储引擎都会对所有索引列计算哈希码，并存在索引中，哈希表保存指向每个数据行指针。

MySQL中，**只有Memory引擎显示支持哈希索引，是非唯一哈希索引，**若多个哈希值相同，索引会以链表方式存放多个记录在同一个哈希条目中，并且为默认类型。Memory也支持B-Tree。NDB集群引擎支持唯一哈希索引，并且在NDB集群引擎中作用特殊。

哈希索引查找速度非常快，但是也有其限制：

只包含哈希值和行指针，不存储字段值，故访存次数增加。
无法应用于排序，因为不是按照索引值顺序存储。
不支持部分索引列匹配查找。
不支持范围查询，只支持等值比较查询。
访问哈希索引数据速度快，除非有很多哈希冲突。
若哈希冲突很多，则索引维护操作代价较高。

InnoDB引擎有一个特殊功能叫做自适应哈希索引。其原理是：当InnoDB注意到某些索引值被使用的很频繁时，会在内存中基于B-Tree索引之上再建立哈希索引，这样也使其具备一些哈希索引的优点。

有一个方案是创建自定义哈希索引。思路是：还是使用B-Tree进行查找，但是是使用哈希值而不是键本身进行索引查找，需要再查询的WHERE子句中手动指定哈希函数。

例如：

这样性能较高，因为MySQL优化器会使用url_crc列的索引来完成查找，只需要根据哈希值做快速整数比较即可找到索引条目。相对于对完整的URL字符串做索引来说非常快。而缺陷是需要维护哈希值。

**处理哈希冲突。**使用哈希索引进行查询时，必须在WHERE子句中包含常量值，否则查询无法正确工作。若只是想统计记录数，则无需带入列值。

空间数据索引（R-Tree）

**MyISAM表支持空间索引。**这类索引无需前缀查询，会从所有维度来索引数据，会从所有维度来索引数据。而查询时可以有效使用任意维度来组合查询。需要使用MySQL的GIS相关函数如MBRCONTAINS（）等来维护数据。MySQL的GIS并不完善，故很少有人会使用该特性。

全文索引

一种特殊的索引，查找文本中的关键词，而不是之间比较索引中的值。全文索引需要注意的细节有：停用词、词干和复数、布尔搜索等。全文索引类似于搜索引擎，而不是简单WHERE匹配。

在相同的列上同时创建全文索引和基于值的B-Tree索引不会有冲突，全文索引适用于MATCH AGAINST操作，而不是普通的WHERE条件查询。

其他索引类型

如TokuDB使用分形树索引。ScaleDB使用Patricia tries。其他一些存储引擎技术如InfiniDB和Infobright则使用一些特殊数据结构来优化某些特殊查询。

3.2 索引优点

索引的作用：

快速定位到表中的指定位置
用于ORDER BY和GROUP BY操作，因为数据是有序的，索引B-Tree就会将相关的列值都存储在一起。最后因为索引中存储了实际的列值，所以某些查询只使用索引就能完成全部查询。

索引的优点：

大大减少服务器需要扫描的数据量
帮助服务器避免排序和临时表
将随机IO改变为顺序IO

适合某个查询的三星系统：

一星：索引将相关记录放在一起

二星：如果索引中数据顺序和查找中排列顺序一致

三星：索引中列包含查询中所需要的全部列

3.3 索引优化策略

独立的列

独立的列指的是：索引列不能是表达式的一部分，也不能是函数的参数。

若不满足独立的列条件，则会使得MySQL无法使用已经存在的索引，也就不会使用索引。

例如：

前缀索引和索引选择性

若索引很长的字符列，会让性能变差。通常解决方案是索引开始的部分字符，大大节约索引空间，从而提高索引效率。但是会降低索引选择性。

选择性：不重复的索引值/数据表的记录总数（T）。范围是1/T到1。

选择性越高，查询效率越高，性能越好。因为选择性高的索引可以让MySQL在查找时过滤更多的行。当索引的选择性是1，这是最好的索引选择性，性能也是最好的。

对于BLOB、TEXT或VARCHAR必须使用前缀索引。注意选择足够长的前缀来保证较高选择性，并且不应该过长，使得前缀索引选择性接近于整个索引列。前缀的“基数”应该接近于完整列的“基数”

如何决定前缀适用长度？找到最常见值和列表，和最常见的前缀列表进行比较。

另一个办法是计算完整列的选择性，使前缀的选择性接近于完整列的选择性。

组合索引

在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能，一定程度上可以使用多个单列索引来指定行。

补充：union合并两个或多个SELECT语句的结果值，不允许重复值。而union all允许重复值

优化方案：

当有多个AND条件时，需要一个包含所有相关列的多列索引，而不是多个独立的单列索引
有多个OR条件时，通常会耗费大量CPU和内存资源在缓存、排序和合并操作上，特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。
这些资源耗费通常不会被计算到查询成本上，可能性能还不如全表查询，故可以通过参数optimizer_switch来关闭索引合并功能，也可以使用IGNORE INDEX提示让优化器忽略掉某些索引。

索引列顺序

当不需要考虑排序和分组时，将选择性最高的列排列在前面时最好的。因为此时能够更好帮助筛选WHERE条件的查找。

也要考虑查询条件的具体值。

还要注意特殊情况的影响，不要假设平均情况下的性能也能代表特殊情况下的性能。

聚簇索引

是一种数据存储方式，InnoDB聚簇索引在同一个结构中保持了B-Tree索引和数据行。聚簇表示数据行和相邻的键值紧凑地存储在一起。由于无法将数据行存放在两个不同的地方，故一个表只有一个聚簇索引。但是覆盖索引可以模拟多个聚簇索引的情况。

数据行实际上存储在索引的叶子页。节点页只包含了索引列。

InnoDB通过主键聚集数据，若没有定义主键，InnoDB会选择一个唯一的非空索引替代。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。

InnoDB只聚集在同一个页面上的记录，包含相邻键值的页面可能会相距甚远。聚簇主键可能对性能有帮助，但是也有可能导致严重的性能问题，因此需要仔细考虑聚簇索引。

优点：

可以把相关数据聚集在一起
数据访问数的快，访存次数少
使用覆盖索引扫描的查询可以直接使用页节点中主键值。

缺点：

**插入速度严重依赖于插入顺序。**按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式，若不是按照主键顺序加载数据，那么加载完成后最好使用OPTIMIZE TABLE命令重新组织表。
更新维护聚簇列代价很高。
基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行时，可能面临页分裂问题。页分裂会导致占用更多空间。
可能导致全表扫描变慢
二级索引可能比想象中更大。
二级索引需要两次访存。

特别注意，二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。这意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值取聚簇索引中查找到对应的行。这里做了很多重复的工作，两次B-Tree查找而不是一次，对于InnoDB,自适应哈希索引能够减少这样的重复工作。

InnoDB和MyISAM数据分布对比

二者对应主键索引和二级索引的数据分布有区别。

MyISAM数据分布：

按照数据插入顺序存储在磁盘中，在行旁边显示了行号，从0开始递增，因为行是定长的，索引MyISAM可以从表的开头跳过所需要的字节找到所需要的行。

图5-5是主键分布图，主键是col1，而5-6是普通键的索引，MyISAM中主键索引和其他索引在结构上没有什么不同，主键索引就是一个名为PRIMARY的唯一非空索引。

InnoDB数据分布：

InnoDB支持聚簇索引，故和MyISAM存储数据的方式不同。

InnoDB的存储方式中，聚簇索引就是表，因此不会像MyISAM那样独立存储。

聚簇索引每个叶子节点都包含主键值、事务ID、用于事务和MVCC的回滚指针以及所有剩余列。若主键是一个列前缀索引，InnoDB也会包含完整的主键列和剩下的其他列。

InnoDB的二级索引和聚簇索引以及MyISAM的索引也不同。InnoDB二级索引的叶子节点存储的是主键值，MyISAM所对应存储的是行号。如此策略会让二级索引占用更多空间，而在InnoDB在移动行时无需更新二级索引的该主键值。

在InnoDB表中按主键顺序插入行

**若InnoDB表中没有什么数据需要聚集，可以定义一个代理键作为主键，使用AUTO_INCREMENT自增列，**这样保证数据行顺序写入，对于根据主键做关联操作的性能也会更好。

避免不连续且值的分布范围非常大的聚簇索引，特别是对于那些IO密集型的应用。

例如，从性能的角度考虑，使用UUID作为聚簇索引会很糟糕，它使得聚簇索引的插入变得完全随机，使得数据毫无任何聚集特性，这是最坏的情况。下面是二者的对比：

这是userinfo_uuid表，除了主键改为UUID，其余和前面的userinfo表完全相同。

注意到UUID主键插入行不仅花费时间更长，而且索引占用的空间也更大。一方面是因为主键字段更长；另一方面是由于页分裂和碎片导致。

顺序插入：

随机插入：

随机插入的部分缺点：

大量的随机IO。写入的目标页可能已经刷到磁盘上，并从缓存中移除，或者还未加载到缓存中，因此InnoDB在插入前需要从磁盘读取目标页到内存中。
频繁分页操作而导致大量数据移动。由于写入是乱序的，为了分配新的行空间，频繁页分裂会导致大量移动数据，一次插入最少需要修改三个页而不是一个。
数据碎片。由于频繁页分裂，页会变得稀疏并不规则填充。

将这些随机值载入聚簇索引中后，需要OPTIMIZE TABLE来重建表并优化页填充。

顺序的主键什么时候会造成更坏的后果？

在高并发的情况下，InnoDB按主键顺序插入可能会造成明显争用，因为插入都发生在同一个地方。

AUTO_CREMENT锁机制，需要考虑重新设计表或应用，或修改INNODB_AUTOINC_LOCK_MODE配置。

覆盖索引

定义：如果一个索引包含所有需要查询的字段的值，那么称之为覆盖索引。（包括WHERE条件、SELECT列和排序字段等）

覆盖索引只需扫描索引而无需回表。

GPT所写：如果查询涉及到的字段在索引中（满足覆盖索引），但不是最左前缀，MySQL可能会执行索引扫描，仍然利用索引中的数据减少回表查询，但性能可能不如最左匹配。

优点：

极大减少数据访问量。索引条目远小于数据行，对IO密集型应用也有帮助，索引更容易全部放入内存中，对缓存的负载非常重要，这种情况下响应时间将大部分花费在数据拷贝中。
有助于范围查询。索引按照列值顺序存储，对于IO密集型范围查询会比随机从磁盘读每一行数据IO少很多。可以用OPTIMIZE命令让索引完全顺序排列，让简单的范围查询能够使用完全顺序的索引访问。
减少OS系统调用。如MyISAM在内存中只缓存索引，数据依赖OS来缓存，因此访问数据就需要系统调用，这可能会导致严重的性能问题。
避免二次查询。覆盖索引对InnoDB特别有用，InnoDB的二级索引在叶子节点中保存了行的主键值，如果二级主键能够覆盖查询，避免对主键索引的二次查询。

覆盖索引的必要条件是要存储索引列的值，故MySQL只能使用B-Tree索引做覆盖索引，而哈希索引、空间索引和全文索引都不存储索引列的值，不同存储引擎实现覆盖索引也不同，并不是所有引擎都支持覆盖索引。

发起一个被索引覆盖的查询时，在EXPLAIN的Extra可以看到Using index的信息

InnoDB的二级索引的叶子节点都包含了主键的值，意味着InnoDB的二级索引可以有效利用这些额外主键列来覆盖查询。

使用索引扫描进行排序

MySQL有两种生成有序结果的方式：

通过排序操作
按索引顺序扫描。若EXPLAIN出来的type列值为index，则说明MySQL使用了索引扫描来做排序。（注意区别Extra列的Using index）

若索引不能覆盖查询所需全部列，每扫描一条索引记录就需要回表查询，这属于随机IO，因此按索引顺序读取数据速度比顺序全表查询慢。

只有当索引列的顺序和ORDER BY子句的顺序完全一致时，且所有列排序方向相同，MySQL才能使用索引来对结果排序。并且只有ORDER BY子句所引用字段全部为第一个表时，才能使用索引排序。ORDER BY子句后面必须满足最左匹配原则

有一种情况ORDER BY子句可以不满足索引最左前缀的要求，就是前导列为常量的时候，即WHERE子句或JOIN子句对这些列指定了常量。

Sakila示例数据库表rental在列有（rental_date,inventory_id,customer_id)上名为rental_date索引。

此处通过索引进行排序。即使ORDER BY子句不满足索引的最左前缀，也可以用于查询排序，因为索引的第一列被指定为一个常数。

举例一些不能使用索引做排序的查询：

查询使用了两种不同的排序方向，但是索引列都是正序排序：

WHERE rental_date='2005-05-25' ORDER BY inventory_id DESC,customer_id ASC;

查询的ORDER BY子句引用了一个不在索引中的的列

查询到WHERE和ORDER BY中的列无法组成最左前缀

压缩（前缀压缩）索引

MyISAM使用前缀压缩可以减少索引的大小，从而让更多索引可以放入内存中。

压缩方法：先完全保存索引块中第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，把这部分存储起来。

压缩块使用更少的空间，代价是某些操作可能更慢。每个值的压缩前缀都依赖前面的值，MyISAM查找时无法在索引块中进行二分查找，只能从头开始扫描。

测试表明，对于CPU密集型应用，因为扫描需要随机查找，压缩索引使得MyISAM在索引查找上要慢好几倍。压缩索引的倒序扫描就更慢了。压缩索引需要在CPU内存资源和磁盘之间做权衡。压缩索引需要十分之一大小的磁盘空间，如果是IO密集型应用，对某些查询带来的好处会比成本多得多。

可以在CREATE TABLE中指定PACK_KEYS参数来控制索引压缩的方式。

冗余和重复索引

MySQL允许在相同列上创建多个索引，并且需要单独维护重复的索引，优化时也需要逐个进行考虑。

重复索引是在相同列上按照相同顺序创建的相同类型索引，应避免。

例如：

一个经验不足的用户可能是想创建一个主键，先加上唯一限制，然后再加上索引以供查询使用。然而MySQL唯一和索引限制都是通过索引实现的，因此上面的写法实际上再相同的列上创建了三个重复的索引。

冗余索引通常发生在为表添加新索引的时候，例如可能会增加一个新索引（A，B），而不是扩展已经存在的索引（A）。大多数情况下都不需要冗余索引，应该尽量扩展已经存在的索引而不是创建新索引。有时候出于性能考虑应该需要冗余索引。

表中索引越多，插入速度也就越慢。增加新索引会导致INSERT、UPDATE、DELETE等操作的速度变慢，特别是当新增索引后导致达到内存瓶颈的时候。

解决重复索引和冗余索引的方案：删除这些索引。

可以使用common_schema的一些视图来定位，也可以使用pt-duplicate-key-checker

未使用的索引

这些索引是累赘，建议删除。

方案是：Percona Server或者MariaDB中打开userstates服务器变量，然后让服务器正常运行一段时间，再通过INFORMATION_SCHEMA.INDEX_STATISTICS就能查到每个索引的使用频率。

还可以使用pt-index-usage，该工具用于查询日志，并对日志的每一条数据进行EXPLAIN操作1，并打印出关于索引和查询的报告。

索引和锁

InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的行数，从而减少锁的数量。注意，只有当InnoDB在存储引擎层能够过滤掉所有不需要的行才有效。

即使使用了索引，InnoDB也有可能锁住一些不需要的数据。若不能使用索引查找和锁定行可能更糟糕，MySQL做全表扫描回锁住所有的行。

InnoDB、索引和锁有一些其他细节，InnoDB在二级索引上使用共享锁（读锁），而访问主键索引时使用排他锁（写锁）。这消除了使用覆盖索引的可能性，并且使得SELECT FOR UPDATE比LOCK IN SHARE MODE或非锁定查询要慢很多。

3.4 索引案例

支持多种过滤条件

通过IN（）的方式覆盖那些不在WHERE子句中的列这种技巧不能滥用。每额外增加一个IN（）条件，优化器需要做的组合都将以指数形式增加，最终可能会极大降低查询性能。

避免多个范围条件

IN（）代表多个等值条件查询。

优化排序

对于选择性很低的列，可以增加一些特殊的索引来1做排序。

优化这类索引的另一个比较好的方法时延迟关联，通过使用覆盖索引查询返回需要的主键，在根据这些主键关联原表获取到需要的行。可以减少MySQL扫描那些需要丢弃的行数。

3.5 维护索引和表

维护表有三个主要目的：

找到并修复损坏的表
维护准确的索引统计信息
减少碎片

找到并修复损坏的表

对于MyISAM存储引擎，表损坏通常是系统崩溃所导致，其他引擎也会由于1硬件问题，MySQL本身缺陷或者OS的问题导致索引损坏。

可以通过CHECK TABLE来检查是否发生表损坏，通常都能找出大多数表和索引的错误。

可以使用REPAIR TABLE命令来修复损坏的表，但同样不是所有的存储引擎都支持。存储引擎不支持，可以通过一个不做任何操作的ALTER操作来重建表。例如修改表的存储引擎为当前存储引擎。

ALTER TABLE innodb_tbl ENGINE=INNODB;

此外，还可以使用存储引擎相关的离线工具，如myisamchk。或者将数据导出一份，然后再重新导入。如果损坏的是系统区域或表的行数据区域，此办法无用。

若InnoDB损坏，有几种常见可能：

数据库硬盘问题：例如内存或磁盘问题
数据库管理员的错误：如再MySQL外部操作了数据文件
InnoDB本身的缺陷

通常是由于尝试使用rsync备份InnoDB导致的。

更新索引统计信息

MySQL的查询优化器通过两个API来了解存储引擎的索引值分布信息，以决定如何使用索引。

第一个是recoeds_in_range()，通过向存储引擎传入两个边界值获取再这个范围大概有多少条记录。MyISAM返回精确值，InnoDB返回估计值。

第二个是info（），该接口返回各种类型的数据，包括索引的基数（每个键值有多少条记录）。

Memory引擎根本不存储索引统计信息
MyISAM将索引统计信息存储在磁盘中，ANALYZE TABLE需要进行一次全索引扫描来计算索引基数，在整个过程中需要锁表。
直到MySQL5.5版本，InnoDB也不在磁盘存储索引统计信息，而是通过随机索引访问进行评估并将其存储在内存中。

可以使用SHOW INDEX FROM命令来查看索引的基数。

基数显示了存储引擎估算索引列有多少个不同的取值。需要注意的是，如果服务器上的库表非常多，则从这里获取元数据的速度可能会非常慢，并且会给MySQL带来额外的压力。

**InnoDB引擎通过抽样的方式来计算统计信息。**首先随机地读取少量的索引页面，然后以此为样本计算索引的统计信息。通过参数innodb_stats_sample_pages来设置样本页的数量。

InnoDB会在首次打开，或者执行ANALYZE TABLE或者表大小发生非常大的变化的时候计算索引信息。在打开某些INFORMATION_SCHEMA表，或者使用SHOW TABLE STATUS和SHOW INDEX、在MySQL客户端开启自动补全功能都会触发索引统计信息的更新。

减少索引和数据的碎片

B-Tree索引可能会碎片化，这会降低查询的效率。碎片化的索引会以很差或无序的方式存储在硬盘。

B-Tree需要随机磁盘访问才能定位到叶子页，所以随机访问是不可避免的。如果叶子页在物理分布是顺序并且紧密的，那么查询的性能就会更好。否则对于范围查询，索引覆盖扫描等操作速度会很慢。

有三种类型的数据碎片：

行碎片：数据行被存储为多个地方多个片段中，即使查询只从索引种访问一行记录，行碎片也会导致性能下降。
行间碎片：行间碎片是指**逻辑上顺序的页，或者行在磁盘上不是顺序存储的。**行间碎片对诸如全表扫描和聚簇索引扫描之类的操作有很大影响。
剩余空间碎片：页中有大量空余空间，导致服务器读取大量不需要的数据造成浪费。

MyISAM这三种碎片都可能出现，但是InnoDB不会出现短小的行碎片。

可以通过执行OPTIMIZE TABLE或者导出再导入的方式来重新整理数据。

MyISAM还可以通过排序算法重建索引来消除碎片。

对于不支持OPTIMIZE TABLE的存储引擎，可以做一个不做任何操作的ALTER TABLE操作来重建表。

对于开启了expand_fast_index_creation参数的Percona Server，按这种方式建表，同时会消除表和索引的碎片化。对于标准版本的MySQL只会消除表的碎片化。

3.6 总结

再选择索引和编写利用这些索引的查询时，有三个原则：

单行访问很慢。若从存储中读取一个数据库只为获取一行，则浪费很多工作。
按顺序访问范围很快。顺序IO不需要多次磁盘寻道。且不需要额外排序操作和将行按组进行聚合计算
索引覆盖查询很快。覆盖查询无需回表，避免了大量单行访问。

如何判断一个系统建立的索引是合理的？

建议按照响应时间来进行分析。找出消耗时间最长的查询或者给服务器带来最大压力的查询，检查这些查询的schema、SQL和索引结构。判断是否有查询扫描过多行，是否做了很多额外排序、是否使用了临时表、是否使用随机IO访问数据、是否有太多回表。

4 查询优化

4.1 查询优化器的提示（hint）

如果对优化器选择的执行计划不满意，可以使用优化器提供的几个提示（hint）来控制最终的执行计划。以下是一些常见提示：

**HIGH_PRIORITY和LOW_PRIORITY。**告诉MySQL，当多个语句同时访问某一个表时，那些语句优先级高或低（仅影响MySQL访问某个数据表的队列顺序）。HIGH_PRIORITY会让SELECT语句重新调度到所有正在等待表锁以便修改数据的语句之前，即放到表队列最前面。高优先级用于INSERT语句时，抵消全局LOW_PRIORITY设置对该语句的影响。LOW_PRIORITY会让该语句一直处于等待状态。这两个提示只对使用表锁的存储引擎有效，不能在InnoDB或其他细粒度的多和并发控制引擎中使用。
DELAYED。对INSERT和REPLACE有效。**使用该提示的语句立刻返回给客户端，并将插入的行数据放入缓冲区，然后在表空闲时批量将数据写入。**日志常用。所收到限制是：不是所有存储引擎都支持这样的左边，该提示会都在函数LAST_INSERT_ID()无法正常工作。
**STRAIGHT_JOIN。**放置SELECT关键字之后，让查询中所有表按照在语句中出现的顺序进行关联。也可放置任何两个关联表名字之间，固定其前后两个表的关联顺序。
SQL_SMALL_RESULT和SQL_BIG_RESULT。仅对SELECT语句有效。告诉优化器对GROUP BY或 DISTINCT查询如何使用临时表和排序。SQL_SMALL_RESULT告诉优化器结果集会很小，可以将结果集放在内存中索引临时表，避免排序。SQL_BIG_RESULT告诉优化器结果集很大，建议使用磁盘临时表做排序操作。
SQL_BUFFER_RESULT。告诉优化器将查询结果放入到一个临时表，然后尽可能释放表锁。当没法使用客户端缓存时，使用服务器缓存很有效。代价是服务器端会消耗更多内存。
SQL_CHACHE和SQL_NO_CACHE。告诉MySQL这个结果集是否应该缓存在查询缓存中。
SQL_CALC_FOUND_ROWS。会让MySQL返回的结果集包含更多的信息。
FOR UPDATE和LOCK IN SHARE MODE。主要控制SELECT的锁机制，只对实现了行级锁的存储引擎有效。会对符合查询条件的数据行加锁。唯一内置的支持这两个提示的引擎时InnoDB，这两个提示会让某些优化无法使用，如索引覆盖扫描。
USE INDEX、IGNORE INDEX、FORCE INDEX。告诉优化器使用或不使用哪些索引来查询记录。FORCE和USE基本相同，除了FORCE会告诉优化器全表扫描成本远远高于索引扫描。
optimizer_search_depth。该参数**控制优化器在穷举执行计划时的限度。**查询长时间处于Statistics状态时，可以考虑调低该参数。
optimizer_prune_level。该参数默认打开，让优化器根据需要扫描的行数来决定是否跳过某些执行计划。
optimizer_switch。包含一些开启/关闭优化器特性的标志位。MySQL5.1中通过该参数来控制禁用索引合并的特性。

使用Percona Toolkit中的pt-upgrade工具，可以检查在新版本中运行的SQL是否与老版本一样，返回相同的结果。

4.2 优化特定类型的查询

多数优化技巧都是和特定版本相关，对于未来SQL版本未必适用。

优化COUNT（）查询

count（）是一个特殊函数，有两种作用：

统计某个列值的数量。在括号中指定列或列的表达式，统计合格表达式有值的结果数。
统计行数。当确认括号内表达式不可能为空时，实际上就是在统计行数。最简单的情况即为COUNT(*)，它会忽略所有的列而直接统计所有行数。

MyISAM的COUNT（*）非常快，可以利用存储引擎的特性直接获取这个值。

而带WHERE的COUNT（）速度和其他存储引擎没有过多区别。

简单的优化：可以使用MyISAM中COUNT（*）非常快的特性，用以加速一些特定条件下COUNT（）的查询。例如：将条件反转一下，然后用总数来进行相减即可获得同样的结果。

使用近似值：有时候业务场景不要求完全精确的COUNT值，可以用近似值来进行代替。EXPLAIN出来的优化器估算的行数就是一个不错的近似值。

更复杂的优化：除了前面的方法，MySQL层面还能做的即索引覆盖扫描。更深一步，考虑修改应用的架构，可以增加汇总表，或者增加类似Memcached类似的外部缓存系统。在“快速，精确和实现简单”三方面，必须舍弃一个。

优化关联查询

确保ON或USING子句中的列有索引。一般来说，只需要在关联顺序中第二个表的相应列上创建索引。
确保任何GROUP BY和ORDER BY的表达式只涉及一个表中的列。这样才有可能MySQL用这个索引来优化这个过程。
升级MySQL时注意关联语法、运算符优先级等其他可能会发生变化的地方。不同类型的关联可能会生成不同的结果。

优化子查询

尽量使用关联查询代替。

优化GROUP BY和DISTINCT

很多场景下，MySQL使用同样的办法优化这两种查询。MySQL优化器会在内部处理时相互转化这两类查询。都可以使用索引进行优化。

无法使用索引时：GROUP BY通过两种策略来完成，即使用临时表或文件排序做分组。可以使用SQL_BIG_RESULT和SQL_SMALL_RESULT来让优化器按照你希望的方式运行。

对关联查询做分组（GROUP BY），按照查找某个列进行分组，通常采用查找表的标识列分组效率会比其他列更高。

如果没有通过ORDER BY 子句显式指定排序列，查询使用GROUP BY子句时，结果集会自动按照分组的字段进行排序。

优化GROUP BY WITH ROLLUP

分组查询一个变种就是要求MySQL对返回的分组结果再做一次超级聚合。可以通过WITH ROLLUP子句来实现该逻辑。

优化LIMIT分页

系统需要进行分页操作时，通常使用LIMIT加上偏移量的办法实现，并加上ORDER BY子句，若没有索引，则需要大量文件排序操作。

要优化比较靠后的分页查询，要么在页面中限制分页数量，要么优化大偏移量的性能。

最简单的办法是：尽可能减少扫描数量，而不是查询所有的列。根据需要做一次关联操作再返回所需列。

延迟关联也将大大提升查询效率，让MySQL扫描尽可能少的页面。如：

LIMIT和OFFSET的问题，主要在于OFFSET，会导致MySQL扫描大量不需要的行然后抛弃掉。**如果可以使用书签记录上次取数据的位置，那么下次就可以直接从该书签的位置开始扫描。**这样可以避免使用OFFSET。若主键是单调的即可实现。

其他优化办法：包括预先计算的汇总表，或者关联到一个冗余表，冗余表只包含主键列和需要做排序的数据列。

优化UNION查询

MySQL总是通过创建并填充临时表来执行UNION查询。很多优化策略无法很好使用。

若是在需要服务器消除重复的行，否则一定要使用UNION ALL，若没有ALL，MySQL会给临时表加上DISTINCT选项，导致整个临时表做数据的唯一性检查，代价很高。

MySQL总是将数据放入临时表，然后取出。

静态查询分析

Percona Toolkit中的pt-query-advisor能够解析日志、分析查询模式，并给出所有可能存在潜在问题的查询，并给出足够详细的建议。

使用用户自定义的变量

在查询中混合使用过程化和关系化逻辑的时候，自定义变量可能很有用。

用户自定义变量是用来存储内容的临时容器。可以在任何可以使用表达式的地方使用这些自定义变量。

以下场景不能使用用户自定义变量：

使用自定义变量的查询，无法使用查询缓存。
不能在使用常量或标识符的地方使用自定义变量。如表名、列名和LIMIT子句。
生命周期是在一个连接中有效，所以不能用它们来做连接间的通信。
使用连接池或持久化链接时，自定义常量可能让毫无关系的代码发生交互。
5.0以前大小写敏感
不能显式声明自定义变量的类型。
MySQL优化器某些场景下可能会将这些变量优化掉。
赋值顺序和时间点不固定。
：=优先级很低，主要明确使用括号
使用未定义变量不会产生任何语法错误。

优化排名语句

使用用户自定义变量重要特性为：可以在给一个变量赋值同时使用这个变量。

避免重复查询刚刚更新的数据

统计更新和插入的数量

当每次由于冲突导致更新时对变量@x自增一次，然后通过对这个表达式乘0来让其不影响要更新的内容。MySQL协议会返回被更改的总行数，故无需单独统计该值。

确定取值的顺序

最大的一个问题可能是没有注意到在赋值和读取变量的时候可能是在查询的不同阶段。例如在SELECT子句中进行赋值然后在WHERE子句中读取变量可能变量取值并不如所想那样。

编写偷懒的UNION

假设需要编写一个UNION查询，第一个子查询作为分支条件先执行，若找到了匹配的行，则跳过第二个分支。

用户自定义变量的其它用处

不仅在SELECT语句中，其他任何类型的SQL语句都可以对变量进行赋值。例如：可以像前面使用子查询的方式改进排名语句一样来改进UPDATE。

可以使用的以下场景：

查询运行时计算总数和平均值
模拟GROUP语句的函数FIRST（）和LAST（）
对大量数据进行数据计算
计算一个大表的MD5散列值
编写样本处理函数，当样本中数值超过某个边界值时变成0
模拟读\写游标
在SHOW语句的WHERE子句中加入变量值

5 重点面试题和概念

存储引擎：数据文件在磁盘上不同的组织形式。

回表：通常在使用二级索引时，如果创建索引的键是其他字段，那么在叶子节点中存储的是该记录的主键，然后再通过主键索引找到对应记录的过程叫做回表。

索引覆盖：所需要查询的字段都在访问B+树的叶节点中，无需回表。

**索引下推（ICP）：**将部分擦汗寻条件下推至存储引擎层面进行处理，从而减少需要返回给MySQL服务器层面的数据量，减少IO次数。

最左匹配原则：在联合索引中，若SQL语句用到了联合索引中最左边的索引，那么这条SQL语句可以利用这个联合索引去进行匹配。

MRR（mult_range read）：把随机磁盘读，转化为顺序磁盘读，从而提高了索引查询的性能。

FIC（Fast Index Create）：

先创建临时表，将数据导入临时表
把原始表删除
修改临时表的名字

给当前表添加一个共享锁，不会有创建临时文件的资源消耗，还是在源文件中，但是此时如果有人发起DML操作，很明显数据会不一致，所以添加共享锁，读取时没有问题的，但是DML时会有问题。

一致性非锁定读（CNR）：指InnoDB存储引擎通过MVCC的方式来读取当前执行时间上数据库中运行的数据，如果读取的行正在执行delete、update操作，这时读取操作不会因此而取等待行上锁的释放，而去读取行的一个快照。快照数据指该行之前版本的数据。

MVCC：是数据库提供的一种提供并发访问控制的技术。它让读写操作互不阻塞，每个写操作都会创建一个新版本的数据，读操作会从有限多个版本的数据中挑选一个合适的结果进行返回。

InnoDB实现MVCC：核心理念是数据快照，不同事务访问不同版本的数据快照（无需拷贝数据浪费空间），从而实现不同的事务隔离级别。实现原理是通过事务的undo log进行回滚操作。

根据行为的不同undo log分为两种：insert undo log和update undo log。

insert undo log是在insert操作中产生的undo log，因为insert操作的记录只对事务本身可见，对其他事务此纪录是不可见的，所以insert undo log可以在事务提交后直接删除而不需要进行purge操作

update undo log操作是update或delete操作中产生的undo log，因为会对已经存在的记录产生影响，为了提供MVCC机制，因此update undo log不能在事务提交时就进行删除，而是将事务提交时放到history list上，等待purge线程进行最后的删除操作。

InnoDB是否支持hash索引？

支持自适应hash，即人为无法干预的hash索引，由MySQL自动判断转换的。

索引是什么？

索引是帮助MySQL高效获取数据的数据结构，存储在文件系统中，索引的文件存储形式与村粗引擎有关，索引文件的结构一般有：hash表、B+树。

为什么不选择哈希表？

利用hash存储的话需要将所有的数据文件添加到内存中，比较耗费内存空间
如果所有查询都是等值查询，那么hash确实很快，但是实际工作环境中范围查找的数据更多，而不是等值查询，因此hash不太合适。

为什么不选择二叉树或红黑树？

主要是在IO层面考虑，无论是二叉树还是红黑树，都会因为树的深度过深而造成IO次数变多，影响数据读取的效率。

InnoDB构建聚簇索引所使用键的顺序？

通过B+Tree的主键创建索引，若没有其次选择唯一键，若都没有则会生成一个6字节的row_id来作为主键。

优化小细节

当使用索引列进行查询时尽量不要使用表达式，把计算放到业务层而不是数据库层。例如：

建议：select actor_id from actor where actor_id=4

不建议：select actor_id from actor where actor_id+1=5

尽量使用主键查询，而不是其他索引，因为主键查询不会触发回表查询（代理主键：与业务无关的列作为主键。自然主键：与业务相关的列作为主键）
使用前缀索引
使用索引扫描来排序
union all，in，or都可以使用索引，但是推荐使用in
范围列可以使用索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列
强制类型转换会全表扫描
更新十分频繁，数据区分度不高的字段上不宜建立索引。更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能。类似于这类区分度不大的属性，建立索引是没有意义的，不能有效过滤数据。
创建索引的列，不允许为null，可能会得到不符合预期的结果
当需要进行表连接时，最好不要超过三张表，因为需要join的字段，数据类型必须一致
能使用limit的时候尽量使用limit
单表索引尽量控制在5个以内（现在没有太多限制）
组合索引字段数不允许超过五个
创建索引时避免认为：1.索引越多越好。2.过早优化，在不了解系统的情况下进行优化。