第一章 MYSQL的架构与历史

mysql系统架构

mysql三层逻辑架构：

1层：连接处理、授权认证、安全等
2层：大多数mysql核心功能在此层，包括查询解析、分析、优化、缓存以及内置函数、所有跨存储引擎功能（存储过程、触发器、视图）都在这一层
3层：包括了存储引擎、负责MYSQL中数据存储和提取

连接管理和安全性：

每个客户端连接在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行，该线程只能轮流在某个CPU核心或者CPU中运行，服务器会负责缓存线程，因此不需要为每个新建的连接创建或者销毁线程

优化与执行：

mysql会解析查询，并创建内部数据结构（解析树），然后对其进行各种优化，包括重写查询，决定表的读取顺序，以及选择合适的索引，用户可以根据特殊关键字提示（hint）优化器，影响它的决策过程，也可以用
优化器解释器（explain）优化过程的各个因素，使用户知道服务器如何进行优化决策以及提供一个参考基准。

并发控制：

mysql在两个层面进行并发控制：服务器层和存储引擎层

1.读写锁：读写锁是解决多线程下的脏数据问题，读锁（read lock）是共享的、写锁（write lock）是排他的。 2.锁粒度：给定的资源上，锁定的数据量越小，则系统的并发程度越高。问题是加锁也会消耗资源，锁的各种操作（获得锁、检查锁、释放锁），都会增加系统开销。 3.锁策略：就是在锁的开销和数据安全性直接寻求平衡。大多数商业数据库系统没有提供更多选择，一般都是在表上施加行锁（row-level lock），而mysql则提供多种选择，每种mysql引擎都可以实现自己的锁策略和 锁粒度。mysql主要有两种重要的锁策略：表锁（table lock）、行级锁（row lock）

  表锁：表锁是mysql最基本的锁策略，也是开销最小的策略。它会锁定整张表，一个用户对表进行CUD操作时，需要先获取写锁，这会阻塞其它用户对改表的读写操作。
  行级锁：行级锁可以最大程度支持并发处理（同时也带来了最大的锁开销）.

事务

事务就是一组原子性的SQL查询，或者说一个独立的工作单元。执行一组的语句，要么全成功，要么全失败。

ACID：原子性（atomicity）、一致性(consistency)、隔离性(isolation)、持久性(durability)，一个运行良好的事务处理系统，必须具备这些标准特征

原子性：一个事务必须要被视为一个不可分割的最小工作单元，整个事务操作必须全成功或失败回滚。
一致性：数据库总是从一个一致性的状态转换到另一个一致性的状态。
隔离性：一个事务最终提交前，对其它事务时不可见的。
持久性：一旦事务提交，则所修改的数据会永久保存在数据库中，此时即使系统崩溃，修改的数据也不会丢失。

隔离级别：SQL标准中定义了四种隔离级别，每种级别都规定了一个事务所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的，较低级别的隔离通常可以执行更高的并发，系统开销更小。

READ UNCOMMITTED(未提交读):事务中的修改，即使未提交，对其它事务也是可见的。事务读取未提交的数据，被称为脏读（Dirty read）
READ COMMITTED(提交读)：一个事务从开始直到提交前，只能“看见”已经提交的事务所做的修改。这个级别的可能两次执行同样操作，得到不一样结果，被称为不可重复读（Nonrepeatable read）
REPEATABLE READ(可重复读)：该级别保证了在同一个事务中多次读取通用的记录结果是一致的，但当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新数据，当之前事务再次读取该范围，会出现幻读（Phantom read）
SERIALIZABLE(可串行化)：强制事务串行处理，在读取每一行数据上都加锁，可能会导致大量超时和锁争用的情况。

死锁：死锁是指两个或者多个事务在同一资源上相互占用，并请求锁定对方占有的资源，从而导致恶性循环。

事务日志：使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为持久化到硬盘上，目前大多数存储引擎都是这样实现的，我们称为预写式日志，修改日志需要写两次磁盘。

mysql中的事务：mysql提供了两种事务型存储引擎，InnoDB和NDB Cluster。

自动提交：mysql默认采用自动提交模式，如果不是显式开始一个事务，则每个查询都被当作一个事务提交。可以设置AUTOCOMMIT变量启用或者禁用自动提交模式
在事务中混合使用存储引擎：如果在事务中混合使用事务和非事务型的表（InnoDB和MyISAM）,正常情况下没有问题，如果出现事务回滚，则非事务型的表无法撤销，mysql不会提醒也没有报错，这会导致数据不一致
隐式和显式锁定：InnoDB采用两阶段锁定协议，在事务执行过程中随时可以锁定，锁只有执行了commit或者rollback才会释放，InnoDB会根据事务隔离级别在需要的时候加锁。另外InnoDB也支持显式锁定，如：
		select ...lock in share mode
		select ...for update
		mysql也支持LOCK TABLES 和 UNLOCK TABLES语句，这是服务器层实现的，和存储引擎无关，如果需要应用到事务，还是应该选择事务型存储引擎

多版本并发控制

mysql的大多数事务型存储引擎都不是简单的行级锁，基于并发性能考虑，一般都实现了多版本并发控制（MVCC），其它数据库（oracle、postgreSQL）也实现了MVCC，但各自实现机制不同。

MVCC:可以认为是行级锁的一个变种，但是它很多情况下避免了加锁操作，因此开销更低，实现了非阻塞的读操作，写操作也只锁定必要的行。

MVCC实现：通过保存数据在某个时间点的快照实现的，也就是说，不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不同的。

    InnoDB的MVCC，是通过在每行记录后面保存两个隐藏列来实现的，这两个列，一个保存了行创建时间（版本号），一个保存行过期（删除）时间（版本号）。每开始一个事务，版本号会自动递增，事务开始时刻的
    系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号比较。

MVCC具体操作：

SELECT:InnoDB会根据以下两个条件检查每行记录：
	a.InnoDB只查找行版本号小于或等于当前事务版本的数据行，这样确保事务读取的行，要么是在事务开始前已存在，要么是事务自身插入或修改过的。
	b.行的删除版本要么未定义，要么大于当前事务版本号，这样可以确保事务读取的行，在事务开始前未被删除。

INSERT:InnoDB为新插入每一行保存当前系统版本号为行版本号

DELETE:InnoDB为删除的每一行保存当前系统版本号为行版本号

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

MVCC作用级别：MVCC只在REPEATABLE READ和READ COMMITTED两个隔离级别下工作，其它两个隔离级别和MVCC不兼容，因为READ UNCOMMITTED总是读取最新的数据行，而不符合当前事务版本的数据行，而SERIALIZABLE则对所有读取操作加锁。

MYSQL的存储引擎

InnoDB:mysql默认引擎，它被设计用来处理大量短期事务，短期事务大部分情况都是正常提交的，很少回滚。InnoDB的性能和自动崩溃修复功能，使得它在非事务存储的需求中也很流行。

MyISAM：mysql5.1以前的默认存储引擎，不支持行级锁和事务，崩溃后无法修复。可应用在表创建并导入数据后，不会进行修改的场景。

第五章 创建高性能索引

索引的类型：B-Tree 索引、哈希索引

+ B-Tree 索引：最常用的索引，B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索，根节点的槽中放有指向子节点的指针，存储引擎根据这些指针向下层查找。
	通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限 。最终存储引擎要么找到对应的值，要么值不存在。

可使用B-Tree索引的查询类型： 假设一个user表，索引为姓、名、生日（lastName,firstName,birthday），此索引对如下查询有效
	全值匹配 ：既查询条件为索引的三列
	匹配最左前缀 ：可以查找所有姓‘Allen’的人，即只使用第一列。
	匹配列前缀 ：可以用索引查询以‘J’开头的姓的人
	匹配范围值 ：例如索引可以查找姓在‘Allen’和‘Barry’之间的人。此例也只使用了索引第一列。
	精确匹配某一列并范围匹配另一列 ：索引可以查找姓‘Allen’和名‘J’开头的人，既第一列全匹配，第二列范围匹配
	只访问索引的查询 ：

B-tree树索引的限制：
	1.如果不是按照索引最左列开始查找，则无法使用索引，比如无法使用例子中索引查找名为‘Green’的人。
	2.不能跳过索引的列，索引无法用于查找姓‘Allen’和生日为‘1982-09-15’的人，这是MYSQL只能使用索引的第一列
	3.如果查询的某列是范围查询，则其右边所有列无法使用索引优化查找，既索引无法用于where lastName = 'Allen' and firstName like 'J' and birthday = '1980-09-15',这时使用的是精确匹配某一列并范围匹配另一列查找
总结：索引列的顺序对查询很重要，在优化性能时，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。 

+ 哈希索引 hash index :基于哈希表实现，只有精确匹配所有的列才有效，对于每一行数据，存储引擎都会根据索引列生成每条数据的hash code,哈希索引存储每条数据的hash code，同时保存指向每条数据的指针。

哈希索引的限制：
	1.哈希索引只包含hash code和指针，而不存储字段值，所有不能使用索引中的值来避免读取行。
	2.哈希索引数据不是按照索引值顺序存储的，所有也无法用于排序。
	3.哈希索引不支持部分列匹配查找，需要全部匹配。
	4.哈希索引只支持等值比较查询，包括= 、in()、<=>
	5.访问哈希索引的数据非常快，除非有hash冲突(不同的索引值却有相同的hash code)。
	6.如何哈希索引冲突很多的话，一些索引维护操作代价会很高。例如在某个选择性很低的列建立哈希索引

总结：因为这些限制，哈希索引只适用某些特定场合，而一旦适合哈希索引，则它带来的性能提升非常显著。例如，在数据仓库中有种经典的“星型”schema,需要关联很多查找表，哈希索引就非常适合。除了Memory外，NDB集群引擎也支持唯一哈希索引。


+ 空间数据索引 R-Tree : MyISAM表支持索引，可以用作地理数据存储。

+ 全文索引：是一种特殊的索引，它查找的是文本中的关键词，而不是直接比较索引中的值，全文索引类似搜索引擎做的事情，而不是简单的where条件匹配

索引的优点

1.索引大大减少了服务器需要扫描的数据量
2.索引可以帮助服务器避免排序和临时表
3.索引可以将随机I/O变为顺序I/O

评价一个索引是否适合某个查询的‘三星系统’：索引将相关的记录放在一起则获得一星；如果索引中列顺序和查询中where顺序一致则获得二星；如果索引的列包含了查询中需要的全部列则获得三星。
索引适合中大型表，数据量很小的表全表扫描效率更高，而特大型表，则需要考虑分区技术。

高性能索引策略

1.独立的列：是指索引列不能是表达式的一部分，也不能是函数的参数。（始终将索引列单独放在等式一侧）

2.前缀索引和索引选择性：如果需要索引一个很长的字符列(text,或者很长的varchar)，会让索引变的大且慢，可以考虑创建前缀索引。mysql前缀索引不支持做order by 和group by，也无法使用前缀索引做覆盖扫描。
	前缀索引多少字符，需要考虑选择性是否够高，同时又不能太长（节约空间），决定前缀的合适长度，select count(distinct left(列名,num))/count(*) from table_name;数值越高num越短越好。
	创建前缀索引语法：ALTER TABLE table_name ADD key(列名(num)) 

3.多列索引：很多人对索引理解不够，常见的错误是为每个列建索引 和 错误的顺序创建索引。

4.选择合适的列顺序：创建一个合适列顺序的索引经验法则是：将选择性最高的列放在索引最前列（不考虑排序和分组时）。在一个多列的B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是是第二列。
	索引，索引可以按照升序或者降序进行扫描，以满足符合顺序的查询需求。

5.聚簇索引：是一种数据存储方式，具体细节依赖其实现方式，但InnoDB的聚簇索引实际上是在同一结构中保存了B-Tree索引和数据行。mySql的主键是聚簇索引。

6.覆盖索引：索引包含了所需要查询的字段的值，我们称为‘覆盖索引’，mysql可以使用索引直接获取列的值，而不需要再读取数据行。

7.冗余和重复的索引：重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引，这会使数据库单独维护重复的索引，影响性能。
	冗余索引和重复索引有所不同，比如你建立了索引（A,B），再创建索引（A）就冗余了。（如果创建索引（B,A）或者（B）则不是冗余）

8.未使用的索引：除了冗余和重复索引，还有一些服务器永远用不到的索引，这些索引完全是累赘，需要删除。需要工具来查找未使用的索引。

9.索引和锁：索引可以让查询锁定更少的行，如果你的查询从不访问这些不需要的行，将会锁定的更少。

第六章 shcema 与数据类型优化

选择优化的数据类型

mysql支持的数据类型很多，选择正确的数据类型对于获取高性能至关重要，几个选择数据类型的原则：

1.更小的通常更好：一般情况下，尽量选择可以存储正确数据的最小数据类型。更小的数据类型占据更小的磁盘、内存和CPU缓存。

2.简单就好：简单数据类型操作通常需要更小的CPU周期，例如，整型比字符串操作代价更低。

3.尽量避免NULL：通常情况下最好指定列为NOT NULL，除非真的需要保存NULL。如果查询包含null的列，对mysql来说更难优化，因为可为null的列使索引，索引统计和值比较都更复杂

整数类型：TINYINT|SMALLINT|MEDIUMINT|INT|BIGINT 分别是8、16、24、32、64为存储空间，可以存储的值范围为-2的(n-1)次方到2的(n-1)次方，其中N为存储空间，整数类型有UNSIGNED可选属性，表示不允许负值，这大致可以使 正数上线提高一倍，比如TINYINT范围-128~127，加上UNSIGNED后则是255。整数计算一般使用64位的BIGINT，MYSQL可为整数指定宽度，例如INT(11),它不会限制值的合法范围，只是规定了MYSQL的交互工具， 用来显示字符串的个数。但是对于存储来说，INT(1)和INT(11)是相同的

实数类型：实数是带有小数部分的数字，然而，它们不只是为了存储小数部分，也可以使用DECIMAL存储比BIGINT还大的整数。MYSQL既支持精确类型，也支持不精确类型。FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似运算。 DECIMAL类型用于存储精确的小数，MYSQL5.0以上，支持精确计算。浮点类型通常比DECIMAL使用更少的空间，所有尽量只在对小数进行精确计算时才使用DECIMAL，在数据量比较大的时候，可以考虑用BIGINT分存储货币，以 解决浮点型不精确和DECIMAL计算代价高的问题。

字符串类型： VARCHAR 和 CHAR类型：VARCHAR类型用于存储可变长字符串，它比定长类型更节省空间，因为它只使用必要的空间；CHAR类型的是定长的，MYSQL总是根据定义的字符串长度分配足够的空间，CHAR值适合存储很短的字符串， 或者所有值都接近同一个长度，对于经常需要变更的值，CHAR也比VARCHAR更好，对于非常短的列，CHAR也比VARCHAR更有效率。

tips:使用VARCHAR(5)和VARCHAR(100) 存储"hello"的空间是一样的，但是更长的列会消耗更多的内存，因为MYSQL通常会分配固定大小的内存来保存内存值，最好的策略是只分配真正需要的空间

BLOB和TEXT类型 BLOB和TEXT都是为了存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。与其它类型不同，MYSQL把每个BOLO和TEXT值当作一个独立对象处理，存储引擎在存储的时候通常会做特殊处理，当BOLO和TEXT 值太大时，InnoDB会使用专门的‘外部’存储区域进行存储，在每个值的行内存储一个指针指向外部存储的实际的值。BLOB和TEXT区别是，BOLB存储二进制，没有排序规则或字符集，而TEXT有字符集和排序。

日期和时间类型：MYSQL可以使用多种类型来保存时间或者日期。 DATETIME:这个类型能保存最大范围的值，从1111~9999，精度为秒，它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关，使用8个字节存储空间。 TIMESTAMP：这个类型保存了从1970-01-01以来的秒数，他与UNIX时间戳相同，TIMESTAMP只使用4个字节存储空间，因此它的范围比DATETIME小得多，只能表示1970-2038年。 TIMESTAMP和DATETIME很不一样，前者提供的值与时区相关，后者则保留文本表示日期时间，默认情况下，如果插入时没有指定第一个TIMESTAMP列的值，MYSQL会设置当前时间为这个列的值。TIMESTAMP列默认NOT NULL，这也和 DATETIME不同。

选择标识列：关联操作中，通过标识列查找其他列，或者标识列作为其它表的外键使用，这时要保证关联表中的列数据类型完全一致，包括像UNSIGNED这样的属性。混用可能导致性能问题，或者隐式转换错误。 标识列小技巧：1.整数类型时标识列的最好选择，因为他们很快且可以使用AUTO_INCREMENT 2.SET和ENUM是标识列的糟糕选择 3.字符串类型：如果可能，应该避免字符串类型作为表示列，因为它们消耗空间且通常比数值型慢。尤其MyISAM引擎中，因为对字符串进行压缩，会导致查询慢很多。

特殊类型数据：某些类型数据并不直接与内置类型一致，低于秒精度的时间戳就是一个例子，另一个是IPv4地址，人们通常使用VARCHAR(15)来储存，然而，它们实际上是32位无符号整数，小数点是为了阅读方便。所以应使用无符号整数 存储IP地址，MYSQL提供了INET_ATON()和INET_NTOA()函数在这两种表示方法间转换

Mysql schema设计中的陷阱

太多的列：MYSQL的存储引擎API工作时，需要在服务器层和存储引擎层直接进行通过行缓冲进行拷贝数据，然后再服务器层将缓存内容解析成各个列，从行缓存中将编码过的列转换成行数据结构的操作代价是非常高的。如果使用非常宽的表 (数千个字段)，然而只有一小部分列实际用到，这时转换的代价就会非常高。

太多的关联：MYSQL限制了每个管理操作最多操作61个表，但实际上，不建议关联查询超过12个表以上。

全能的枚举：防止过度使用枚举，如CREATE TABLE ...() country enun('','0','1'...,'31')。这种模式的schema设计非常凌乱，如果枚举需要增加一个新的国家时，就要做一个ALTER TABLE操作。

变相的枚举：枚举允许列中存储一组定义值中的单个值，集合(SET)则允许列中存储一组定义值中的一个或者多个值，这有时候会导致混乱，比如CREATE TABLE ... (sex set('1','0') not null default '1')。如果1和0不会同时出现，那么 毫无疑问应该使用枚举替代集合。

非此发明的NULL：之前我们讲了应避免使用null的好处，并且尽可能考虑替代方案，即使需要存储一个事实上的空值时，可以考虑0、空字符串代替。但是遵循这个原则也不能走极端，当确实需要表示未知值时，不要害怕使用NULL。

范式和反范式

数据库设计三范式：1.第一范式(确保每列保持原子性) 2.第二范式(确保表中的每列都和主键相关) 3.第三范式(确保每列都和主键列直接相关,而不是间接相关)

范式的优点和缺点：当为了性能而寻求帮助时，通常建议对schema进行范式化设计，尤其是写密集的场景， 范式化通常能带来的好处： 1.范式化的更新通常比非范式化的快。 2.当数据范式化较好时，就只有很少或者没有重复数据，update只需要更改最少的代码 3.范式化的表通常更小，所以操作更快 4.很少的冗余意味着检索时效率更高 范式化的缺点：缺点时通常需要关联查询，稍微复杂一点的查询语句在符合范式的schema上可能至少需要一次关联，这不但代价昂贵，还可能使索引无效。

反范式的优点和缺点：反范式的schema因为数据都在一张表上，可以很好避免关联。如果不需要关联，则对大部分查询最差的情况(即没有使用索引，全表扫描)，当数据比内存大时这可能比关联要快得多，因为这样避免了随机I/O 实际上，完全的范式化或者反范式化都是实验室才有的东西，真实世界里是混合使用。

缓存表和汇总表

有时候提升性能最好的方法是同一张表保存衍生的冗余数据，然而，有时候需要创建一张完全独立的汇总表或者缓存表(特别是为了满足检索时的需求)。如果能容许少量脏数据，这是非常好的方法。

计数器表：如果在表中保存计数器，则更新数据有可能碰到并发问题。计数器表在web应用中很常见，用这种表记录登陆次数、文件下载次数等，创建一个独立的表存储计数器通常是个好主意。问题在于，对于任何想要更新这一行的事务 来说，这条记录上都有一个全局的互斥锁(nutex),这会使得事务只能串行，要获得更好的并发性能，可以考虑先初始化100条数据，每次随机在100条数据中选择更新，获取时需要sum一下。

加快ALTER TABLE操作速度：MYSQL修改表结构操作方法是用新的结构创建一张表，从旧表中插入新表数据，然后删除旧表。如果内存不足且表很大，ALTER操作有可能花费数个小时甚至数天。

总结：良好的schema设计原则是普遍适用的，但MYSQL有它自己实现细节要注意，概括来说，尽可能保持任何东西的小而简单总是好的。 1.避免过度设计，例如会导致极其复杂查询的schema设计，或者有很多列的表设计 2.使用小而简单的合适数据类型，除非真实数据模型中有确切的需要，否则尽可能避免NULL值。 3.尽量使用相同数据类型存储相似或相关的值，尤其在关联条件中使用的列 4.注意可变长字符串，尤其在临时表和排序时会按照最悲观的最大长度分配内存 5.尽量使用整型定义标识列 6.避免使用MYSQL废弃的特性，例如指定浮点数的精度，或者整数显示宽度 7.小心使用ENUM和SET，它们使用很方便，但有时候会有陷阱。最好避免使用BIT

第六章 查询性能优化 ::: tip 最优的表设计+最好的索引+合理的查询设计 是高性能的三个必备条件。 :::

为什么查询会慢

查询的速度快慢，重要的是响应时间，如果把查询看作一个任务，那么它是又很多子任务组成，优化查询就是优化其子任务，要么消除一些子任务、要么减少子任务执行次数、要么让子任务执行更快。
查询的生命周期大致是:从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。其中‘执行’可以认为是生命周期中最重要的阶段，这其中包含了大量为了检索数据到存储引擎的调用，以及调用后的数据处理，包括排序、分组等。
完成这些任务时，查询需要在不同地方花费时间，包括网络、CPU计算、生成统计信息和执行计划、锁等待等操作，尤其是向底层存储引擎检索数据的调用操作，这些操作需要在内存中操作，CPU操作以及内存不足会导致I/O操作上消耗时间，根据引擎不同，还可能是产生大量上下文切换以及系统调用。
每一个消耗大量时间的查询案例中，我们都能看见一些不必要的额外操作，某些操作执行了多次，某些操作执行的太慢。优化查询的目的就是减少和消除这些操作所花费的时间。

慢查询基础：优化数据访问

查询效率低最基本的原因就是访问数据量太多，除了某些不可避免的情况，大部分性能低下的查询都可以通过减少访问数据量的方式进行优化。对于低效查询，可以通过下面两步骤分析很有效：
	1.确认应用程序是否在检索大量超过需要的数据，这通常意味着访问了太多的行，但有时也可能是访问太多的列。
	2.确认MYSQL服务器层是否在分析大量超过需要的数据行。

是否向数据库请求了不需要的数据 1.查询不需要的数据：比如用SELECT语句查询大量的结果，然后再获取前面N行数据关闭结果集。解决方式是查询需要条数N，SQL语句用limit N 2.多表关联查询返回全部的列：返回多个表的全部数据列是不好的，正确的是只取需要的列。 3.总是取出全部列：SELECT * 需要考虑是否必要，取出全部列会让优化器无法完成索引覆盖扫描这类优化，还会带来额外I/O、CPU、内存消耗。如果考虑代码复用性，查询全部也是可以考虑的，如果应用程序采用了缓存或别的考虑。可以考虑查询超出需要的数据列。 4.重复查询相同的数据：不断重复的执行相同的查询，然后每次返回结果都相同，可以考虑将数据缓存起来，这样性能更好。

MYSQL是否在扫描额外的记录 对于mysql,最简单的查询开销的三个指标如下：响应时间、扫描的行数、返回的行数。这三个数据都会记录到慢查询中，所以查询慢查询是找出扫描行数过多的查询好方法。

1.响应时间：响应时间是个表面的值，响应时间是：服务时间+排队时间。排队时间是指服务器因为等待资源没有真正执行查询的时间，当看到一个响应时间时，需要问问自己这个值是否是个合理的值。
2.扫描的行数和返回的行数：分析查询时，查看该查询的扫描行数是非常有帮助的，一定程度可以说明查询效率高不高，扫描的行数和查询的行数一般在1:1~10:1之间。
3.扫描的行数和返回的类型：评估查询开销的时候，需要考虑从表中找出一行数据的成本。在EXPLAIN语句中，type列反应了访问类型。访问类型有很多种，从全表扫描到索引扫描、范围扫描、唯一索引扫描、常数引用等，这些访问是从慢到快的。扫描行数也是从大到小。
	如果查询没有办法找到合适的访问类型，那么解决的最好办法是增加一个合适的索引。一般来说，MYSQL能够使用三种方式应用where查询条件，从好到坏依次为：
		Ⅰ.在索引中使用where条件来过滤不匹配的记录，这是在存储引擎层完成的。
		Ⅱ.使用索引覆盖扫描（Extra列中出现Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中结果。这是在MySQL服务器层完成的，但无需再回表查询记录。
		Ⅲ.从数据表中返回数据，然后过滤不满足条件的记录（Extra列中出现Using where），这是在MySQL服务器层完成的，MySQL需要先从数据表读出记录然后过滤。

重构查询的方式

优化查询，可以转换查询写法，但是性能更好。也可以修改应用代码，以另一种方式完成查询。最终目的是一样的。

1.一个复杂查询还是多个简单查询：MySQL从设计上让连接和断开都很轻量级，再返回一个小的查询结果方面很高效，把一个复杂的查询分解为多个简单查询是很有必要的。

2.切分查询：将大查询切分为小查询，每个查询功能一样，只完成一小部分，每次返回一小部分查询结果（分页）

3.分解关联查询：很多高性能应用都会对关联查询进行分解，原本一条关联查询分解为多个简单查询，这种方式有如下优势：
	Ⅰ.让缓存更高效，很多简单查询可能已被缓存，这样查询会跳过访问数据库，关联查询如果其中一个表发生变化，缓存失效概率较大。
	Ⅱ.查询分解后，执行单个查询可以减少锁的竞争
	Ⅲ.应用层做分解，容易对数据库进行拆分，更容易做到高性能和可扩展。
	Ⅳ.可以减少冗余查询，在应用层做关联，意味着某条记录只需查一次，而数据库关联，则可能还要重复访问一部分数据。
	Ⅴ.这样做相当于在引用中做哈希关联，而不是MySQL的嵌套关联。
4.

查询执行的基础

当我们向MySQL发送一条请求，MySQL做了什么：

Ⅰ.客户端发送一条查询给服务器
Ⅱ.服务器先查询缓存，如果命中，则立刻返回存储在缓存中的结果，否则进入下一阶段。
Ⅲ.服务器进行SQL解析、预处理、再由优化器生成对应的执行计划。
Ⅳ.MySQL根据优化器生成的执行计划，调用存储引擎API来执行查询
Ⅴ.将结果返回客户端

MySQL客户端/服务器通信协议 在任意时刻，要么是服务器向客户端发送消息，要么是客户端向服务器发送消息，两个动作不能同时执行。这种通信协议简单快速，但是也有限制，一个明显的限制就是没法进行流量控制。 查询缓存 在解析一个查询语句前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中缓存中的数据。如果查询恰好命中了缓存，那么在返回结果之前MySQL会检查一次用户权限，如果没问题，返回结果给客户端。 查询优化处理 查询优化器执行一个计划，有下面几个步骤：

Ⅰ.语法解析和预处理：语法解析器先解析语法关键字正确性以及关键字顺序是否正确。预处理器进一步检查解析树是否合法，比如检查表或者列名是否存在,是否歧义。
Ⅱ.查询优化器：一条查询语句可能有多个执行方式，查询优化器作用是找到最优解。
Ⅲ.数据和索引的统计信息：MySQL系统架构中，服务器层有查询优化器，却没有保存数据和索引的统计信息。统计信息由存储引擎实现。因为服务层没有统计信息，所以MySQL查询优化器在生成查询的执行计划时，需要向存储引擎获取相应信息。
	包括：每个表或者每个索引有多少个页面、每个表每个索引基数是多少，数据行和索引长度、索引的分布信息等。优化器根据这些信息来选择一个最优的执行计划。
Ⅳ.执行计划：MySQL不会如其它数据库一样生成查询字节码来执行，而是生成指令树，通过查询引擎执行指令树并返回结果。

查询执行引擎 在解析和优化阶段，MySQL将生成查询对应的执行计划，MySQL的查询引擎则根据这个计划来完成整个查询 返回结果给客户端 查询的最后一步是返回结果，即使不需要返回结果集给客户端，MySQL仍然会返回这个查询的一些信息，如查询影响的行数。如果查询可被缓存，这一步还会将结果放在查询缓存中。

MYSQL查询优化器的局限性

关联子查询：MySQL的子查询实现很糟，尤其是where条件包含in()的子查询。
UNION的限制：UNION关键字的实现不好。
索引合并优化：
等值传递：某些时候，等值传递会带来额外消耗。
并行执行：MySQL无法利用多核特性来执行并行查询。
哈希关联：MySQL不支持哈希关联。
松散索引扫描：MySQL不支持松散索引扫描，也就是无法按照不连续的方式扫描一个索引。
最大值最小值优化：对于MIN()、MAX()优化做的并不好
在同一张表上查询和更新：MySQL不支持对一张表同时查询和更新。

优化特定类型的查询

优化COUNT()查询：COUNT可以统计某个列值的数量，也可以统计结果集的行数。统计列值数量时，要求列值是非空的（不统计NULL）。统计结果集行数，使用count(*),它会忽略列直接统计行数。 优化关联查询：1.确保ON或者USING字句的列上有索引。2.确保任何GROUP BY和ORDER BY中的表达式只涉及一个表中的列。3.升级MySQL时需要注意，关联语法、运算符优先级等可能发生变化的地方。 优化子查询：尽量用关联查询代替子查询，如果MySQL5.6以后的版本，可以忽略子查询的建议。 优化GROUP BY 和 DISTINCT:这两种查询都可以使用索引优化，这是也最有效的优化方案。

总结：理解查询是如何执行以及时间都消耗到哪些地方，再加上一些诸如解析和优化过程的知识，可以进一步理解上一章讨论的MySQL如何访问表和索引的内容，从另一个维度帮助读者理解MySQL在访问表和索引时查询和索引的关系。 优化通常需要三管齐下：不做、少做、快速的做。除了这些基础手段，包括查询、表结构、索引等，MySQL还有一些高级特性可以帮助优化应用，例如分区、分区和索引类似但是原理不同。MySQL还支持查询缓存，它可以帮 你缓存查询结果，当执行完全相同的查询时，直接使用缓存技术。下一章会介绍这些特性。