高性能MySQL笔记

高性能MySQL笔记

锁粒度

表锁

表锁是Mysql最基本的锁策略，并且是开销最小的策略。表锁会锁定整张表，一个用户在对表进行写操作前，需要先获得写锁，这会堵塞其他用户对该表的所有读写操作。没有写锁时其他的用户才可以获得读锁，读锁之间是不相互堵塞的。
写锁的优先级也高于读锁，一个写锁的请求可能会被插入到读锁队列的前面，反之则不行。

行锁

行锁可以最大程度的支持并发处理。

四种隔离级别

未提交读

在未提交读级别，事务中的修改即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称为脏读。

提交读/不可重复读

在该隔离级别下，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。两次执行同样的查询，可能会得到不一样的结果。

可重复读

该级别确保了在同一个事务中多次读取同样记录的结果是一致的。但是还是存在幻读的问题。

可串行化

是最高的隔离级别，通过强制事务串行执行，避免了前面说的幻读的问题。可串行化会在读取的每一行数据上都加锁，可能导致大量的超时和锁争用的问题。

幻读

所谓的幻读，指的是当某个实物在读取某个范围的记录时，另一个事务用在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行。

事务日志

存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志采用的是追加的方式，因此写日志的操作是磁盘上的一块区域内的顺序IO，而不用像随机I/O需要在磁盘的多个地方移动磁头。事务日志持久化之后，内存中被修改的数据在后台可以慢慢地刷会到磁盘中。
然后数据的修改已经记录到事务并且持久化，但数据本身还没有写回到磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这部分修改的数据。

MVCC

MVCC的实现，是通过了保存数据在4时间点的快照来实现的。每个事务看到的数据都是一致的，根据事务开始的时间不同，每个事务对同一张表，同一个数据看到数据可能是不一样的。
InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建系统版本号，一个保存行的过期系统版本号。每开始一个新的事务，系统版本号都会递增，系统开始时刻的版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。
MVCC只在可重复读和不可重复读两个隔离级别下工作。因为未提交读总是读取最新的数据号，而不是符合当前事务版本的数据行。而串行化会对所以读取的行都加锁。

• SELECT:InnoDB只会查找版本号小于当前事务版本的数据行，这样可以确保事务读取的行，要么是在事务开始前就已经存在的，要么是事务自身插入或者修改过的。行的删除版本要么未定义，要么大于当前事务的版本号，这可以确保事务读到的行在事务开始之前未被删除。
• INSERT：InnoDB为新插入的每一行保存当前系统版本号作为行版本号。
• DELETE:为删除的每一行保存当前系统版本号作为行删除标识。
• UPDATE：InnoDB为插入一行新纪录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

优化的数据类型

• 更小的通常更好：一般情况下，应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通过更快，因为他们占用更少的磁盘、内存和CPU缓存。
• 简单就好：简单数据类型的操作通常需要更少的CPU周期。例如，整型比字符操作代价更低，因为字符集和校对规则时字符比整型更加复杂。
• 尽量避免NULL：如果查询中包含可为NULL的列，对Mysql来说更难优化，因为可为NULL的列使用索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在Mysql里也需要特殊处理。当可为NULL的列被索引时，每个索引需要一个ewai的自己。

整数类型

TNIYINT,SAMLLINT,MEDIUMINT,INT,BIGINT分别使用8，16，24，32，64位存储空间。
可以使用DECIMAL存储比BIGINT还大的整数。Mysql既支持精确类型，也支持不精确类型。DECIMAL类型用于存储精确的小数。
浮点类型在存储同样范围的值时，通常比DECIMAL使用更少的空间。FLOAT使用4个字节存储。DOUBLE占用8个字节。因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL。但是在数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，将存储的货币单位根据小数的位数乘以对应的倍数即可。
制定浮点数的精度已被遗弃。

字符串类型

VARCHAR

VARCHAR类型用于存储可变长字符串，比定长类型更节省空间。 VARCHAR需要使用1或2个额外的字节存储记录字符串的长度。

CHAR

CHAR类型是定长的：MYSQL总是根据定义的字符串长度分配足够的空间。当存储CHAR值时，MYSQL会删除所有的末尾空格。CHAR适合存储很短的字符串，或者所有值都接近同一个长度。

BLOB和TEXT类型

BLOB和TEXT都是为了存储很大的数据而设计的字符串数据类型，分别采用二进制和字符串方式存储。
与其他类型不同，MYSQL会把每个BLOB和TEXT值当做一个独立对象处理，存储引擎在存储时通常会做特殊处理。MYSQL对BLOB和TEXT列进行排序与其他类型是不同的：它只对每个列前面max_sort_length字节做排序。MYSQL不能将BLOB和TEXT列全部长度的字符串进行索引，也不能使用这些索引消除排序。

日期和时间类型

MYSQL可以使用许多类型来保存日期和时间值。MYSQL能存储的最小时间粒度为秒。

DATETIME

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

TIMESTAMP

TIMESTAMP使用4个字节的存储空间，只能表示从1970年到2038年。TIMESTAMP显示的值也依赖于时区。

范式化和反范式化

范式化的优点和缺点

优点：

• 范式化的更新操作通常比反范式化更快
• 当数据较好地范式化，就只有很少或者没有重复数据，所以只需要修改更少的数据
• 范式化的表通常更小，可以更好地放在内存里，所以执行操作更快
• 很少有冗余数据意味着检索列表时更少需要DISTINCT和GROUP BY语句。

缺点：反范式化的缺点通常是需要关联，稍微复杂一点的查询语句在符合范式的schema上都可能需要至少一次关联。

反范式化的优点和缺点

反范式化的schema因为所以的数据都在一张表上，可以很好地避免关联。如果不需要关联表，则对大部分查询最差的情况是全表扫表。当数据量比内存大时这可能比关联要快得多，因为这避免了随机I/O。

ALTER TABLE

ALTER TABLE在大部分情况下，都会锁表并且重建整张表。可以在备机使用ALTER并在完成后把它切换为主库

Mysql索引

B-Tree索引

可以使用b-tree索引的查询类型：

• 全值匹配：全值匹配指的是和索引中的所有列进行匹配。
• 匹配最左前缀：只使用索引的第一列
• 匹配列前缀：只匹配某一列值的开头部分。也只用到了索引第一列。
• 匹配范围值：查找在某一范围内的值，也只使用了索引的第一列。
• 精确匹配某一列并范围匹配另一列
• 只访问索引的查询：查询只需要访问的索引，而无需访问数据行。

B-Tree索引的限制：

• 如果不是按照索引的最左列还是查找，则无法使用索引。
• 不能跳过索引中的列
• 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。

自适应哈希索引

InnoDB注意到某些索引值使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引。

全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文搜索和其他几类索引的匹配方式完全不一样。

索引的选择性

索引的选择是指：不重复的索引值和数据表的记录总数的比值。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时滤掉更多的行，唯一索引选择性是1，这是最好的索引选择性，性能也是最好的。

前缀索引

有的时候需要索引很长的字符列，这会让索引变得大且慢。通常可以索引开始的部分字符，这样可以大大节省索引空间，从而提高索引效率。但是也会降低的索引的选择性。诀窍在于选择足够长的前缀以保证较高的选择性，同时又不能太长。前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。InnoDB的聚簇的索引实际上在统一结构中保存了B-Tree索引和数据行。当表有聚簇索引时，他的数据行实际上存放在索引的叶子。一个表里只有一个聚簇索引。InnoDB通过主键聚集数据。

聚簇索引的优点

• 可以把相关数据保存在一起。
• 数据访问更快。
• 使用覆盖索引扫描的查询可以直接使用页节点的主键值

聚簇索引的缺点

• 提高了I/O密集型应用的性能。
• 插入速度严重依赖于插入顺序
• 更新聚簇索引列的代价很高
• 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行时，可能面临“页分裂”问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳行，这就是一次页分裂。页分裂会导致表占用更多的磁盘空间。
• 聚簇索引可能会导致全表扫描变慢

二级索引（非聚簇索引）

二级索引的叶子节点保存的不是指向行的物理位置而是行的主键值。这意味着通过二级索引查找行，存储引擎需要找到二级索引获得对应的主键值，然后根据这个值去聚簇索引中查找到对应的行。

覆盖索引

如果一个索引包含所有需要查询的字段的值，我们就称之为覆盖索引。
优点：

• 索引条目通常远小于数据行大小，所以如果只需要读取索引，那MySQL就会极大地减少数据访问量。
• 因为索引是按照列值顺序存储的，所以对于I/O密集型的范围查找会比随机地从磁盘读取每一行的数据的I/O要少得多。

使用索引扫描来做排序

Mysql有两种方式可以生成有序的结果：通过排序操作；或者按索引顺序扫描。如果EXPLAIN出来的type列的值为“index”，说明Mysql使用了索引扫描来做排序。
如果索引不能覆盖查询所需的全部列，那就不得不扫描每一条索引记录就得回表查询一次对应的行。这基本上是随机I/O，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢，尤其是在I/O密集型的工作负载时。
只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时，Mysql才能使用索引来对结果进行排序。如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。ORDER BY子句的和查找型操作的限制是一样的：需要满足索引的最左前缀的要求。
有一个情况下ORDER BY子句可以不满足最左前缀的要求，就是前导列为常量的时候，如果WHERE或者JOIN子句对这些列置顶了常量，就可以弥补索引的不足。

索引和锁

索引可以让查询锁定更少的行。如果你的查询从不访问那些不需要的行，那么就会索引更少的行。InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的行数，从而减少锁的数量。

避免多个范围条件

对于范围条件查询，Mysql无法再使用范围列后面的其他索引列，但是对于多个等值条件查询则没有这个限制。

查询性能优化

三种方式应用WHERE条件

• 在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎层完成的。
• 使用索引覆盖扫描（Extra列中出现了Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在Mysql服务器层完成的，但无需再回表查询记录。
• 从数据表中返回数据，然后过滤不满足条件的记录（在Extra列中出现Using Where）。这在Mysql服务层完成，Mysql需要先从数据表中读出记录然后过滤。

分解关联查询

用分解关联查询的方式重构查询有如下的优势：

• 让缓存的效率更高。许多应用查询可以方便地缓存单表查询对应的结果对象。
• 将查询分解后，执行单个查询可以减少锁的竞争。
• 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
• 查询本身效率可能会有所提升。
• 更进一步，这样做相当于在应用中实现了哈希关联，而不是使用Mysql的嵌套循环关联。某些场景下哈希关联的效率要高很多。

查询执行的基础

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

1. 客户端发送一条查询给服务器
2. 服务器先检查查询缓存，如果命中了返回就返回存储在缓存中的结果。否则就进入下一阶段。
3. 服务器端进行SQL解析、预处理、再由优化器生成对应的执行计划
4. Mysql根据优化器生成的执行计划，调用存储引擎的API来执行查询
5. 将结果返回给客户端

Mysql如何执行关联查询

在Mysql的概念中，每个查询都是一次关联，所以读取结果临时表也是关联。

Mysql对任何关联都执行嵌套循环关联操作：即Mysql现在一个表中循环取出单条数据，然后再嵌套循环到下一个表中循环，直到找到所有表中匹配的行为止，然后根据各个表匹配的行，返回查询中需要的各个列。Mysql会尝试在最后一个关联表找到所有的匹配行,如果最后一个匹配行无法找到更多的行以后,Mysql返回到上一层次关联表，看是否找到更多的匹配记录，以此类推迭代执行。
Mysql在FROM子句中遇到子查询时，先执行子查询然后将其结果存放在一个临时表中，然后将这个临时表当做个普通表对待。

Mysql查询优化器的局限性

关联子查询

使用In()加子查询，性能经常会非常糟糕，所以通常建议使用EXISTS()等效的改写查询来获得更好的效率。

松散索引扫描

Mysql并不支持松散索引扫描，也就无法按照不连续的方式扫描一个索引。

优化子查询

尽可能使用关联替代子查询

优化LIMIT分页

优化此类分页查询的一个最简单的方法就是尽可能地使用索引覆盖扫描，而不是查询所有的列，然后根据需要做一次关联操作再返回所需的列。

锁粒度

表锁

表锁是Mysql最基本的锁策略，并且是开销最小的策略。表锁会锁定整张表，一个用户在对表进行写操作前，需要先获得写锁，这会堵塞其他用户对该表的所有读写操作。没有写锁时其他的用户才可以获得读锁，读锁之间是不相互堵塞的。
写锁的优先级也高于读锁，一个写锁的请求可能会被插入到读锁队列的前面，反之则不行。

行锁

行锁可以最大程度的支持并发处理。

四种隔离级别

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2RilgF8U-1630730020566)(en-resource://database/831:1)]

未提交读

在未提交读级别，事务中的修改即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称为脏读。

提交读/不可重复读

在该隔离级别下，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。两次执行同样的查询，可能会得到不一样的结果。

可重复读

该级别确保了在同一个事务中多次读取同样记录的结果是一致的。但是还是存在幻读的问题。

可串行化

是最高的隔离级别，通过强制事务串行执行，避免了前面说的幻读的问题。可串行化会在读取的每一行数据上都加锁，可能导致大量的超时和锁争用的问题。

幻读

所谓的幻读，指的是当某个实物在读取某个范围的记录时，另一个事务用在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行。

事务日志

存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志采用的是追加的方式，因此写日志的操作是磁盘上的一块区域内的顺序IO，而不用像随机I/O需要在磁盘的多个地方移动磁头。事务日志持久化之后，内存中被修改的数据在后台可以慢慢地刷会到磁盘中。
然后数据的修改已经记录到事务并且持久化，但数据本身还没有写回到磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这部分修改的数据。

MVCC

MVCC的实现，是通过了保存数据在4时间点的快照来实现的。每个事务看到的数据都是一致的，根据事务开始的时间不同，每个事务对同一张表，同一个数据看到数据可能是不一样的。
InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建系统版本号，一个保存行的过期系统版本号。每开始一个新的事务，系统版本号都会递增，系统开始时刻的版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。
MVCC只在可重复读和不可重复读两个隔离级别下工作。因为未提交读总是读取最新的数据号，而不是符合当前事务版本的数据行。而串行化会对所以读取的行都加锁。

• SELECT:InnoDB只会查找版本号小于当前事务版本的数据行，这样可以确保事务读取的行，要么是在事务开始前就已经存在的，要么是事务自身插入或者修改过的。行的删除版本要么未定义，要么大于当前事务的版本号，这可以确保事务读到的行在事务开始之前未被删除。
• INSERT：InnoDB为新插入的每一行保存当前系统版本号作为行版本号。
• DELETE:为删除的每一行保存当前系统版本号作为行删除标识。
• UPDATE：InnoDB为插入一行新纪录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

优化的数据类型

• 更小的通常更好：一般情况下，应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通过更快，因为他们占用更少的磁盘、内存和CPU缓存。
• 简单就好：简单数据类型的操作通常需要更少的CPU周期。例如，整型比字符操作代价更低，因为字符集和校对规则时字符比整型更加复杂。
• 尽量避免NULL：如果查询中包含可为NULL的列，对Mysql来说更难优化，因为可为NULL的列使用索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在Mysql里也需要特殊处理。当可为NULL的列被索引时，每个索引需要一个ewai的自己。

整数类型

TNIYINT,SAMLLINT,MEDIUMINT,INT,BIGINT分别使用8，16，24，32，64位存储空间。
可以使用DECIMAL存储比BIGINT还大的整数。Mysql既支持精确类型，也支持不精确类型。DECIMAL类型用于存储精确的小数。
浮点类型在存储同样范围的值时，通常比DECIMAL使用更少的空间。FLOAT使用4个字节存储。DOUBLE占用8个字节。因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL。但是在数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，将存储的货币单位根据小数的位数乘以对应的倍数即可。
制定浮点数的精度已被遗弃。

字符串类型

VARCHAR

VARCHAR类型用于存储可变长字符串，比定长类型更节省空间。 VARCHAR需要使用1或2个额外的字节存储记录字符串的长度。

CHAR

CHAR类型是定长的：MYSQL总是根据定义的字符串长度分配足够的空间。当存储CHAR值时，MYSQL会删除所有的末尾空格。CHAR适合存储很短的字符串，或者所有值都接近同一个长度。

BLOB和TEXT类型

BLOB和TEXT都是为了存储很大的数据而设计的字符串数据类型，分别采用二进制和字符串方式存储。
与其他类型不同，MYSQL会把每个BLOB和TEXT值当做一个独立对象处理，存储引擎在存储时通常会做特殊处理。MYSQL对BLOB和TEXT列进行排序与其他类型是不同的：它只对每个列前面max_sort_length字节做排序。MYSQL不能将BLOB和TEXT列全部长度的字符串进行索引，也不能使用这些索引消除排序。

日期和时间类型

MYSQL可以使用许多类型来保存日期和时间值。MYSQL能存储的最小时间粒度为秒。

DATETIME

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

TIMESTAMP

TIMESTAMP使用4个字节的存储空间，只能表示从1970年到2038年。TIMESTAMP显示的值也依赖于时区。

范式化和反范式化

范式化的优点和缺点

优点：

• 范式化的更新操作通常比反范式化更快
• 当数据较好地范式化，就只有很少或者没有重复数据，所以只需要修改更少的数据
• 范式化的表通常更小，可以更好地放在内存里，所以执行操作更快
• 很少有冗余数据意味着检索列表时更少需要DISTINCT和GROUP BY语句。

缺点：反范式化的缺点通常是需要关联，稍微复杂一点的查询语句在符合范式的schema上都可能需要至少一次关联。

反范式化的优点和缺点

反范式化的schema因为所以的数据都在一张表上，可以很好地避免关联。如果不需要关联表，则对大部分查询最差的情况是全表扫表。当数据量比内存大时这可能比关联要快得多，因为这避免了随机I/O。

ALTER TABLE

ALTER TABLE在大部分情况下，都会锁表并且重建整张表。可以在备机使用ALTER并在完成后把它切换为主库

Mysql索引

B-Tree索引

可以使用b-tree索引的查询类型：

• 全值匹配：全值匹配指的是和索引中的所有列进行匹配。
• 匹配最左前缀：只使用索引的第一列
• 匹配列前缀：只匹配某一列值的开头部分。也只用到了索引第一列。
• 匹配范围值：查找在某一范围内的值，也只使用了索引的第一列。
• 精确匹配某一列并范围匹配另一列
• 只访问索引的查询：查询只需要访问的索引，而无需访问数据行。

B-Tree索引的限制：

• 如果不是按照索引的最左列还是查找，则无法使用索引。
• 不能跳过索引中的列
• 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。

自适应哈希索引

InnoDB注意到某些索引值使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引。

全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文搜索和其他几类索引的匹配方式完全不一样。

索引的选择性

索引的选择是指：不重复的索引值和数据表的记录总数的比值。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时滤掉更多的行，唯一索引选择性是1，这是最好的索引选择性，性能也是最好的。

前缀索引

有的时候需要索引很长的字符列，这会让索引变得大且慢。通常可以索引开始的部分字符，这样可以大大节省索引空间，从而提高索引效率。但是也会降低的索引的选择性。诀窍在于选择足够长的前缀以保证较高的选择性，同时又不能太长。前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。InnoDB的聚簇的索引实际上在统一结构中保存了B-Tree索引和数据行。当表有聚簇索引时，他的数据行实际上存放在索引的叶子。一个表里只有一个聚簇索引。InnoDB通过主键聚集数据。

聚簇索引的优点

• 可以把相关数据保存在一起。
• 数据访问更快。
• 使用覆盖索引扫描的查询可以直接使用页节点的主键值

聚簇索引的缺点

• 提高了I/O密集型应用的性能。
• 插入速度严重依赖于插入顺序
• 更新聚簇索引列的代价很高
• 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行时，可能面临“页分裂”问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳行，这就是一次页分裂。页分裂会导致表占用更多的磁盘空间。
• 聚簇索引可能会导致全表扫描变慢

二级索引（非聚簇索引）

二级索引的叶子节点保存的不是指向行的物理位置而是行的主键值。这意味着通过二级索引查找行，存储引擎需要找到二级索引获得对应的主键值，然后根据这个值去聚簇索引中查找到对应的行。

覆盖索引

如果一个索引包含所有需要查询的字段的值，我们就称之为覆盖索引。
优点：

• 索引条目通常远小于数据行大小，所以如果只需要读取索引，那MySQL就会极大地减少数据访问量。
• 因为索引是按照列值顺序存储的，所以对于I/O密集型的范围查找会比随机地从磁盘读取每一行的数据的I/O要少得多。

使用索引扫描来做排序

Mysql有两种方式可以生成有序的结果：通过排序操作；或者按索引顺序扫描。如果EXPLAIN出来的type列的值为“index”，说明Mysql使用了索引扫描来做排序。
如果索引不能覆盖查询所需的全部列，那就不得不扫描每一条索引记录就得回表查询一次对应的行。这基本上是随机I/O，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢，尤其是在I/O密集型的工作负载时。
只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时，Mysql才能使用索引来对结果进行排序。如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。ORDER BY子句的和查找型操作的限制是一样的：需要满足索引的最左前缀的要求。
有一个情况下ORDER BY子句可以不满足最左前缀的要求，就是前导列为常量的时候，如果WHERE或者JOIN子句对这些列置顶了常量，就可以弥补索引的不足。

索引和锁

索引可以让查询锁定更少的行。如果你的查询从不访问那些不需要的行，那么就会索引更少的行。InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的行数，从而减少锁的数量。

避免多个范围条件

对于范围条件查询，Mysql无法再使用范围列后面的其他索引列，但是对于多个等值条件查询则没有这个限制。

查询性能优化

三种方式应用WHERE条件

• 在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎层完成的。
• 使用索引覆盖扫描（Extra列中出现了Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在Mysql服务器层完成的，但无需再回表查询记录。
• 从数据表中返回数据，然后过滤不满足条件的记录（在Extra列中出现Using Where）。这在Mysql服务层完成，Mysql需要先从数据表中读出记录然后过滤。

分解关联查询

用分解关联查询的方式重构查询有如下的优势：

• 让缓存的效率更高。许多应用查询可以方便地缓存单表查询对应的结果对象。
• 将查询分解后，执行单个查询可以减少锁的竞争。
• 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
• 查询本身效率可能会有所提升。
• 更进一步，这样做相当于在应用中实现了哈希关联，而不是使用Mysql的嵌套循环关联。某些场景下哈希关联的效率要高很多。

查询执行的基础

1. 客户端发送一条查询给服务器
2. 服务器先检查查询缓存，如果命中了返回就返回存储在缓存中的结果。否则就进入下一阶段。
3. 服务器端进行SQL解析、预处理、再由优化器生成对应的执行计划
4. Mysql根据优化器生成的执行计划，调用存储引擎的API来执行查询
5. 将结果返回给客户端

Mysql如何执行关联查询

在Mysql的概念中，每个查询都是一次关联，所以读取结果临时表也是关联。

Mysql对任何关联都执行嵌套循环关联操作：即Mysql现在一个表中循环取出单条数据，然后再嵌套循环到下一个表中循环，直到找到所有表中匹配的行为止，然后根据各个表匹配的行，返回查询中需要的各个列。Mysql会尝试在最后一个关联表找到所有的匹配行,如果最后一个匹配行无法找到更多的行以后,Mysql返回到上一层次关联表，看是否找到更多的匹配记录，以此类推迭代执行。
Mysql在FROM子句中遇到子查询时，先执行子查询然后将其结果存放在一个临时表中，然后将这个临时表当做个普通表对待。

Mysql查询优化器的局限性

关联子查询

使用In()加子查询，性能经常会非常糟糕，所以通常建议使用EXISTS()等效的改写查询来获得更好的效率。

松散索引扫描

Mysql并不支持松散索引扫描，也就无法按照不连续的方式扫描一个索引。

优化子查询

尽可能使用关联替代子查询

优化LIMIT分页

优化此类分页查询的一个最简单的方法就是尽可能地使用索引覆盖扫描，而不是查询所有的列，然后根据需要做一次关联操作再返回所需的列。