MySQL8.0优化 - 索引的优化与设计原则

---

学习资料

【MySQL数据库教程天花板，mysql安装到mysql高级，强！硬！-哔哩哔哩】
【阿里巴巴Java开发手册】https://www.w3cschool.cn/alibaba_java

索引的优化与设计原则

索引的分类

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。
从功能逻辑上说，索引主要有4种，分别是普通索引、唯一索引、主键索引、全文索引。
按照物理实现方式，索引可以分为2种：聚簇索引和非聚簇索引。
按照作用字段个数进行划分，分成单列索引和联合索引。

普通索引

在创建索引时，不附加任何条件，用于提高查询效率，这类索引可以创建在任何数据类型中，其值是否唯一和非空，要由字段本身的完整性约束条件决定，建立索引后，可以通过索引进行查询。

唯一性索引

使用UNIQUE参数可以设置索引的唯一性索引，在创建唯一性索引时，限制该索引必须是唯一的，但允许有空值，在一张数据表中可以有多个唯一索引。

主键索引

主键索引就是一种特殊的唯一性索引，在唯一性索引的基础上增加了不为空的约束，也就是NOT NULL + UNIQUE，一张表里最多只有一个主键索引。

单列索引

在表中的单个字段上创建索引。单列索引只根据该字段进行索引。单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可，一个表可以有多个单列索引。

多列（组合、联合）索引

多列索引是在表的多个字段组合上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询，但只有查询条件中使用了这些字段的第一个字段时才会被使用。例如，在表中的字段id、name和gender上建立了一个多列索引idx_id_name_gender，只有在查询条件中使用了字段id时该索引才会被使用，使用组合索引时遵循最左前缀集合。

全文索引

全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。他能利用【分词技术】等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。全文索引非常适合大型数据集，对于小的数据集，他的用处比较小。
随着大数据时代的到来，关系型数据库应对全文索引的需求已力不从心，逐渐被solr、ElasticSearch等专门的搜索引擎所替代。

补充：空间索引

使用参数SPATIAL可以设置索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。MySQL中的空间数据类型包括GEOMETRY、POINT、LINSTRING和POLYGON等。目前只有MyISAM存储引擎支持空间检索，而且索引的字段不能为空值，这类索引很少会用到。

索引的设计原则

字段的数值有唯一性限制

索引本身可以起到约束的作用，比如唯一索引、主键索引都是可以起到唯一性约束的，因此在我们数据表中，如果某个字段是唯一性的，就可以直接创建唯一性索引，或者主键索引，这样可以更快速的通过该索引来确定某条记录。
业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba）
说明：不要以为唯一索引影响了insert速度，这个速度损耗可以忽略，但提高查询速度是明显的。

频繁作为WHERE查询条件的字段

某个字段在SELECT语句的WHERE条件中经常被使用到，那么就需要给这个字段创建索引了。尤其是在数据量大的情况下，创建普通索引就可以大 幅提升数据查询的效率。

经常GROUP BY 和 ORDER BY的列

索引就是让数据按照某种顺序进行存储或检索，因此当我们使用GROUP BY对数据进行分组查询，或者使用ORDER BY对数据进行排序的时候，就需要对分组或排序的字段进行索引。如果分组或排序的列有多个，那么可以在这些列上建立组合索引。

UPDATE、DELETE的WHERE条件列

对数据按照某个条件进行查询后在进行UPDATE或DELETE的操作，如果为WHERE字段创建了索引，就能大幅提升效率。原理是因为我们先根据WHERE条件列检索出来这条记录，然后再对它进行更新或删除。如果进行更新的时候，更新的字段是非索引字段，提升效率会更明显，这是因为非索引字段更新不需要对索引进行维护。

DISTINCT字段需要创建索引

有时候我们需要对某个字段进行去重，使用DISTINCT，那么对这个字段创建索引，也会提升查询效率。

多表JOIN连接操作时，创建索引注意事项

首先，连接表的数量尽量不要超过3张，因为每增加一张表就相当于增加了一次嵌套循环，数量级增长非常快，严重影响查询的效率。
其次，对WHERE条件创建索引，因为WHERE才是对数据条件的过滤。如果在数据量非常大的情况下，没有WHERE条件过滤是非常可怕的。
最后，对于连接的字段创建索引，并且该字段在多张表中类型必须一致。

使用列的类型小的创建索引

我们这里所说的类型大小指的就是该类型表示的数据范围的大小。
我们在定义表结构的时候要显式的指定列的类型，以整数类型为例，有TINYINT、MEDIUMINT、INT、BIGINT等，它们占用的存储空间是依次递增，能表示的整数范围当然也是依次递增。如果我们想对某个整数列建立索引的话，在表示整数范围允许的情况下，尽量让索引列使用较小的类型，比如我们能使用INT就不要使用BIGINT，能使用MEDIUMINT就不要使用INT。
数据类型越小，在查询时进行的比较操作越快。
数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘IO带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中了，从而加快读写效率。
这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中的存储主键值，其他所有二级索引的节点处都会存储一份记录的主键值，如果主键值使用更小的数据类型 ，也就意味着节省了更多的存储空间和更高效的IO。

使用字符串前缀创建索引

假设我们字符串很长，那么存储一个字符串就需要占用很大的存储空间，在我们需要为这个字符串建立索引时，那就意味着在对应的B+树种有这么两个问题：
B+树索引的记录需要把该列完整字符串存储起来，更费时。而且字符串越长，在索引中占用的存储空间越大。
如果B+树索引中索引存储的字符串很长，那在做字符串比较时会占用更多的时间。
我们可以通过截取字段前面一部分内容建立索引，这个就叫前缀索引。这样在查找记录时虽然不能精确的定位到记录的位置，但能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值。既节约空间，又减少了字符串的比较时间，还大体能解决排序的问题。

例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间，如果只检索字段前面若干字符，这样可以提高检索速度。
创建一张商户表，因为地址字段比较长，在地址字段上建立前缀索引：

create table shop(address varchar(120) not null);

alter table shop add index(address(12));

问题是，截取多少呢？截取的多了，达不到节省索引存储空间的目的；截取的少了，重复内容太多，字段的散列度（选择性）会降低。怎么计算不同的长度的选择性呢？
先看一下字段在全部数据中的选择度：

select count(distinct address)/count(*) from shop;

通过不同的长度去计算，与全表的选择性对比：
公式：

select count(distinct left(列名,索引长度))/count(*);

例如：

select count(distinct left(address,10))/count(*) as sub10, -- 截取前10个字符的选择度
count(distinct left(address,15))/count(*) as sub15, -- 截取前15个字符的选择度
count(distinct left(address,20))/count(*) as sub20, -- 截取前20个字符的选择度
count(distinct left(address,25))/count(*) as sub25 -- 截取前25个字符的选择度
from shop;

引申另一个问题：索引列前缀对排序的影响
如果使用了索引列前缀，比方说前边只把address列的前12个字符放到了二级索引中，下边这个查询可能就有点尴尬了：

SELECT * FROM shop
ORDER BY address
LIMIT 12;

因为二级索引不包含完整的address列信息，所以无法对前12个字符相同，后边的字符不同的记录进行排序，也就是使用索引列前缀的方式无法支持使用索引排序，只能使用文件排序。
Alibaba《Java开发手册》
【强制】在varcahr字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分别决定索引长度。
说明：索引的长度与区分长度是一堆矛盾体，一般对字符串类型数据，长度为20的索引，区分度会高达90%以上，可以使用count(distinct left(列名,索引长度))/count(*)的区分度来确定，越靠近1越好。

区分度高（散列性高）的列适合作为索引

列的基数指的是某一列中不重复数据的个数，比方说某个列包含值2，5，8，2，5，8，2，5，8虽然有9条记录，但该列的基数却是3。也就是说，在记录行数一定的情况下，列的基数越大，该列中的值越分散；列的基数越小，该列的值越集中。这个列的基数指标非常重要，直接影响我们是否能有效的利用索引。最好为列的基数大的列建立索引，为基数太小列的建立索引效果可能不好。

使用最频繁的列放到联合索引的最左侧

这样可以较少的建立一些索引。同时，由于“最左前缀原则”，可以增加索引的使用率。

在多个字段都创建索引的情况下，联合索引优于单值索引

限制索引的数目

在实际工作中，我们也需要注意平衡，索引的数目不是越多越好，我们需要限制每张表上的索引数量，建议单张表索引数量不超过6个。原因：
1、每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
2、索引会影响INSERT、DELETE、UPDATE等语句的性能，因为表中的数据更改的同时，索引也会进行调整和更新，会造成负担。
3、优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成 出一个最好的执行计划，如果同时有很多个索引都可以用于查询，会增加MySQL优化器生成执行计划时间，降低查询性能。

哪些情况不适合创建索引

在WHERE中使用不到的字段，不要设置索引

WHERE条件（包括GROUP BY、ORDER BY）里用不到的字段不需要创建索引，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的。

数据量小的表最好不要使用索引

如果表记录太少了，比如少于1000个，那么是不需要创建索引的。表记录太少，是否创建索引对查询效率的影响并不大。甚至说，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

有大量重复数据的列上不要建立索引

在条件表达式中经常用到的不同值较多的列上建立索引，但字段中如果有大量重复数据，也不用创建索引。比如在学生表的“性别”字段上只有“男”与“女 ”两个不同的值，因此无需创建索引。如果建立索引，不但不会提高查询效率，反而会严重降低数据更新速度。
索引的价值是帮助快速定位，如果定位的数据有很多，那么索引就失去了它的使用价值，当数据重复度大，比如高于10%的时候，也不需要对这个字段使用索引。

避免对经常更新的表创建过多的索引

第一层含义：频繁更新的字段不一定要创建索引。因为更新数据的时候，也需要更新索引，如果索引太多，在更新索引的时候也会造成负担，从而影响效率。
第二层含义：避免对经常更新的表创建过多的索引，并且索引中的列尽可能少。因此，虽然提高了查询速度，同时却会降低更新表的速度。

不建议用无序的值作为索引

例如身份证、UUID（在索引比较时需要转为ASCII，并且插入时可能造成页分裂）、MD5、HASH、无序长字符串等。

删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

不要定义冗余或重复的索引

冗余索引

有时候有意或者无意的就对同一个列创建了多个索引，比如：index(a,b,c)相当于index(a)、index(a,b)、index(a,b,c)。
举例：建表语句如下：

我们知道，通过idx_name_birthday_phone_number索引就可以对name列进行快速搜索，在创建一个专门针对name列的索引就算是一个冗余索引，维护这个索引只会增加维护的成本，并不会对搜索有什么好处。

重复索引

另一种情况，我们可能会对某个列重复建立索引，比方说这样：

我们看到，col1即是主键、又给它定义为一个唯一索引，还给它定义了一个普通索引，可是主键本身就会生成聚簇索引，所以定义的唯一索引和普通索引是重复的，这种情况要避免。

索引优化与查询优化

导致索引失效的情况

SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。

过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段就无法被使用；索引文件具有B-Three的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

计算、函数、类型转换（自动或手动）导致索引失效。

范围条件右边的列索引失效，应用开发范围查询中，例如：金额查询、日期查询往往都是范围查询，应将查询条件放置where语句最后。（创建的联合索引中，务必把范围涉及到的字段写在最后）

不等于（!=或者<>）操作后获取的结果集在总结果集中占据的比例也是关键因素，如果返回的结果集超过全表的10%-30%时，优化器可能会放弃走索引。

IS NULL可以使用索引，IS NOT NULL无法使用索引。

LIKE以通配符%开头导致索引失效。
【强制】页面搜索严禁左模糊或全模糊，如果需要请走搜索引擎来解决。

OR前后存在非索引的列，索引失效。

数据库和表的字符集统一使用utf8mb4，统一字符集可以避免由于字符集转换产生的乱码。不同的字符集，进行比较前需要进行转换会造成索引失效。

一般性建议

对于单列索引，尽量选择针对当前query过滤性更好的索引。
在选择组合索引的时候，当前query中过滤性最好的字段在索引顺序中，位置越靠前越好。
在选择组合索引的时候，尽量选择能够包含当前query中where子句中更多字段的索引。
在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面。
总之，书写SQL语句时，尽量避免造成索引实效的情况。

关联查询的优化

如果只能添加一条索引的情况下，一定要添加给被驱动表，当然给主驱动表、被驱动表都加上索引是更好的选择，但连接字段的类型一定一致，否则会涉及函数转换导致索引失效。

对于内连接来说，查询优化器可以决定谁作为驱动表，谁作为被驱动表出现。
对于内连接来讲，如果表的连接条件中只能有一个字段作为索引，则有索引的字段所在的表作为被驱动表。
对于内连接来说，在两个表的连接条件都存在索引的情况下，会选择数据量少的表作为驱动表“小表驱动大表”。
对于外连接来说，在特殊情况下也会被查询优化器转换为内连接。

JOIN语句原理

JOIN方式连接多个表，本质上就是哥哥表之间的数据循环匹配。MySQL5.5版本之前，MySQL只支持一种表间关联方式，就是嵌套循环（Nested Loop Join）。如果关联表的数据量很大，则join关联的执行时间会非常长。MySQL5.5以后的版本中，MySQL通过引入BNLJ算法来优化嵌套执行。

驱动表和被驱动表

驱动表就是主表，被驱动表就是从表，费驱动表。

Simple Nested-Loop Join（简单嵌套循环连接）

算法相当简单，从表A中取出一条数据1，遍历表B，将匹配到的数据放到result…以此类推，驱动表A中的每一条记录与被驱动表B的记录进行判断：

可以看到这种方式效率非常低级，以上述表A数据100条，表B数据1000条计算，则A*B=10万次。开销统计如下：

当然MySQL肯定不会这么粗暴的去进行表的连接，所以就出现了后面两种对Nested-Loop Join优化算法。

Index Nested-Loop Join（索引嵌套循环连接）

Index Nested-Loop Join其优化的思路主要是为了减少内层表数据的匹配次数，所以要求在被驱动表上必须有索引才行。通过外层表匹配条件直接与内层表索引进行匹配，避免了内层表的每条记录去进行比较，这样极大的减少了对内层表匹配的次数。

驱动表中的每条记录通过被驱动表的索引进行访问，因为索引查询的成本比较固定，故MySQL优化器都倾向于使用记录数少的表作为驱动表（外表）。

Block Nested-Loop Join（块嵌套循环链接）

如果存在索引，那么会使用index的方式进行join，如果join的列没有索引，被驱动表要扫描的次数太多了。每次访问被驱动表，其表的记录都会被加载到内存中，然后再从被驱动表中取一条与其匹配，匹配结束后清除内存，然后再从驱动表中加载一条记录，然后被驱动表的记录在加载到内存匹配，这样周而复始，大大增加了IO的次数，为了减少被驱动表IO的次数，就出现了Block Nested-Loop Join的方式。

不再是逐条获取驱动表的数据，而是一块一块的获取，引入了join buffer缓冲区，将驱动表join相关的部分数据列（大小受join buffer的限制）缓存到join buffer中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和join buffer中所有驱动表记录进行匹配（内存中操作），将简单嵌套循环中的多次比较合成一次，降低了被驱动表的访问频率。

注意：
这里缓存的不只是关联表的列，select后面的列也会缓存起来。
在一个有N个join关联的sql中会分配N-1个join buffer。所以查询的时候尽量减少不必要的字段，可以让join buffer中可以存放更多的列。

参数设置：block_nested_loop
通过show variables like '%optimizer_switch%'查看block_nested_loop状态。默认是开启的。
join_buffer_size
驱动表能不能一次加载完，要看join buffer能不能存储所有的数据，默认情况下join_buffer_size=256k。
join_buffer_size的最大值在32位系统可以申请4G，而在64位操作系统下可以申请大于4G的Join Buffer空间（64位Windows除外，其大值会被截断为4GB并发出警告）。

Join小结

1、整体效率比较：INLJ > BNLJ > SNLJ
2、永远用小结果集驱动大结果集（其本质就是减少外层循环的数据数量）（小的度量单位指的是 表行数*每行大小）。
3、为了被驱动表匹配的条件增加索引（减少内层表的循环匹配次数）。
4、增大join buffer size的大小（一次缓存的数据越多，那么内层包的扫描次数就越少）。
5、减少驱动表不必要的字段ch挨训（字段越少，join buffer所缓存的数据就越多）。

Hash Join

从MySQL的8.0.20版本开始将废弃BNLJ，因为MySQL8.0.18版本开始就加入了hash join默认都会使用hash join

Nested Loop：
对于被连接的数据子集较少的情况，Nested Loop是个较好的选择。
Hash Join是做大数据集连接时的常用方式，优化器使用两个表中较小（相对较小）的表利用Join key在内存中建立散列表，然后扫描较大的表被探测散列表，找出Hash表匹配的行。
这种方式适用于较小的表可以放入内存，这时优化器将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要求有较大的临时段从而尽量提高I/O的性能。
它能够很好的工作没有索引的大表和并查询的环境中，并提供最好的性能。大多数人都说它是Join的重型升降机。Hash Join只能应用于等值连接（如WHERE A.COL1 = B.COL2），这是由Hash的特点决定的。

子查询优化

MySQL从4.1版本开始支持子查询，使用子查询可以进行SELECT语句的嵌套查询，即一个SELECT语句查询的结果作为另一个SELECT语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。
子查询是MySQL的一项重要的功能，可以帮助我们通过一个SQL语句实现比较复杂的查询。但是子查询的执行效率不高。原因：
1、执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的CPU和IO资源，产生大量的慢查询。
2、子查询的结果集存储的临时表，不论内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。
3、对于返回结果集比较大的子查询，其对查询性能的影响也就越大。
在MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表，其速度比查询要快，如果查询中使用索引的话，性能就会更好。
尽量不要使用NOT IN或者NOT EXISTS，用LEFT JOIN xxx ON xxx WHERE xx IS NULL替代。

排序优化

问题：在WHERE条件字段上加索引，但为什么在ORDER BY字段上还要加索引呢？

回答：
在MySQL中，支持两种排序方式，分别是FileSort和Index排序。
FileSort排序则一般在内存中进行排序，占用CPU较多。如果待排序结果较大，会产生临时文件I/O到磁盘进行排序的情况，效率较低。

优化建议：
1、SQL中， 可以在WHERE子句和ORDER BY子句中使用索引，目的是在WHERE子句中避免全表扫描，在ORDER BY子句避免使用FileSort排序。当然，某些情况下全表扫描，或者FileSort排序不一定比索引慢，但总的来说，我们还是要避免，以提高查询效率。
2、尽量使用Index完成ORDER BY排序。如果WHERE和ORDER BY后面是相同的列就使用单索引列；如果不同就使用联合索引。
3、无法使用Index时，需要对FileSort方式进行调优。
ORDER BY时不LIMIT则索引失效，除非不涉及回表操作，仅查询索引列。
ORDER BY时规则不一致，索引失效（顺序错，不索引；方向反，不索引）。
ORDER BY时，无过滤，不索引，除非使用LIMIT。

结论：
1、两个索引同时存在，MySQL自动选择最优的方案。
2、当【范围条件】和【group by或者order by】的字段出现二选一时，优先观察条件的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上，反之，依然。

GROUP BY优化

GROUP BY使用索引的原则几乎跟ORDER BY一致，GROUP BY即时没有过滤条件用到索引，也可以直接使用索引。
GROUP BY先排序在分组，遵照索引建的最佳左前缀法则。
当无法使用索引列，增大max_length_for_sort_data和sort_buffer_size参数的设置。
WHERE效率高于HAVING，能写在WHERE限定的条件就不要写在HAVING中了。
减少使用ORDER BY，和业务沟通能不排序就不排序，或将排序放到程序端去做。ORDER BY、GROUP BY、DISTINCT这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
包含了ORDER BY、GROUP BY、DISTINCT这些查询的语句，WHERE条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

优化分页查询

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是limit 2000000,10，此时需要MySQL排序前2000010记录，仅仅返回2000000-2000010的记录，其他记录丢弃，查询排序的代价非常大。

优化思路一

在索引上完成排序分页操作，最后根据主键关联回原表查询所需的其他列内容。

EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY LIMIT 2000000,10) a WHERE t.id = a.id;

优化思路二

该方案适用于主键自增的表，可以把LIMIT查询转换成某个位置的查询。

EXPLAIN SELECT * FROM student WHERE id > 2000000 LIMIT 10;

优先考虑覆盖索引

一个索引包含了满足查询结果的数据就叫做覆盖索引，简单说就是索引列+主键包含SELECT到FROM之间查询的列。

覆盖索引的利弊

好处

1、避免Innodb表进行索引的二次查询（回表）
Innodb是以聚集索引的顺序来存储的，对于Innodb来说，二级索引在叶子结点中所保存的是行的主键信息，如果是二级索引查询数据，在查找相应的键值后，还需通过主键进行二次查询才能获取我们真实所需要的数据。
在覆盖索引中，二级索引的键值可以获取所要的数据，避免了对主键的二次查询，减少了IO操作，提升了查询效率。

2、可以把随机IO变成顺序IO加快查询效率
由于覆盖索引是按键值的顺序存储的，对于IO密集型的范围查询来说，对于随机从磁盘读取每一行的数据IO要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的IO转变索引查询的顺序IO。

弊端

索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这是业务DBA，或者成为业务数据架构师的工作。

索引下推

Index Condition Pushdown（ICP）是MySQL5.6中新特性，是一种在存储引擎层使用索引过滤数据的优化方式。
如果没有ICP，存储引擎会遍历索引以定位基表中的行，并将它们返回给MySQL服务器，由MySQL服务器评估WHERE后面的条件是否保留行。

启用ICP后，如果部分WHERE条件可以仅使用索引中的列进行筛选，则MySQL服务器会把这部分WHERE条件放到存储引擎筛选，然后存储引擎通过使用索引条目来筛选数据，并且只有在满足这一条件时才从表中取行。

好处：ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数。
但是，ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据比例。

ICP的开启/关闭

默认情况下启用索引条件下推。可以通过设置系统变量optimizer_switch控制：
index_condition_pushdown

# 打开索引下推
SET optimizer_switch = 'index_condition_pushdown=off';

# 关闭索引下推
SET optimizer_switch = 'index_condition_pushdown=on';

当使用索引下推时，EXPLAIN语句输出结果中Extra列内容显示为Using index condition。

ICP的使用条件

1、如果表访问的类型为range、ref、eq_ref和ref_or_null可以使用ICP。
2、ICP可以用于InnoDB和MyISAM表，包括分区表InnoDB和MyISAM表。
3、对于InnoDB表，ICP仅用于二级索引。ICP的目标是减少全行读取次数，从而减少I/O操作。
4、当SQL使用覆盖索引时，不支持ICP。因为这种情况下使用ICP不会减少I/O。
5、相关子查询的条件不能使用ICP。

其他查询优化策略

EXISTS和IN的区分

索引是个前提，实际选择与否还是要看表的大小。你可以选择的标准理解为小表驱动大表。在这种方式下效率是最高的。
比如下面这样：

SELECT * FROM A WHERE cc IN (SELECT cc FROM B)

SELECT * FROM A WHERE EXISTS(SELECT cc FROM B WHERE B.cc=A.cc)

当A小于B时，用EXISTS。因为EXISTS的实现，相当于外表循环，实现的逻辑类似于：

for i in A
	for j in B
		if j.cc == i.cc then ...

当B小于A时用IN，因为实现的逻辑类似于：

for i in B
	for j in A
		if j.cc = i.cc then ...

哪个表小就用哪个表来驱动，A表小就用EXISTS，B表小就用IN。

COUNT(*)与COUNT(1)与COUNT(具体字段)效率

问：在MySQL中统计数据表的行数，可以使用三种方式：SELECT COUNT(*)、SELECT COUNT(1)和SELECT (具体字段)，使用这三者之间的查询效率是怎样的？

答：
前提：如果你要统计的是某个字段的非空数据行数，则另当别论，毕竟比较执行效率的前提是结果一样才可以。

环节1：COUNT(*)和COUNT(1)都是对所有结果进行COUNT，COUNT(*)和COUNT(1)本质上并没有区别（二者）执行时间可能略有差别，不过你还是可以把它俩的执行效率看成是相等的。如果有WHERE子句，则是对所有符合筛选条件的数据行进行统计；如果没有WHERE子句，则是对数据表的数据行进行统计。

环节2：如果是MyISAM存储引擎，统计数据表的行数只需要0(1)的复杂度，这是因为每张表MyISAM的数据表都有一个meta信息存储了row_count值，而一致性则由表级锁来保证。

如果是InnoDB存储引擎，因为InnoDB支持事务，采用行级锁和MVCC机制，所以无法像MyISAM一样，维护一个row_count变量，因此需要采用扫描全表，是0(n)的复杂度，进入循环+计数的方式来完成统计。

环节3：在InnoDB引擎中，如果采用COUNT(具体字段)来统计数据行数，要尽量采用二级索引。因为主键采用的索引是聚簇索引，聚簇索引包含的信息多，明显会大于二级索引（非聚簇索引）。对于COUNT(*)和COUNT(1)来说，他们不需要查找具体的行，只是统计行数，系统会自动采用占用空间更小的二级索引来进行统计。

如果有多个二级索引，会使用key_len小的二级索引进行扫描。当没有二级索引的时候，才会采用主键索引来进行统计。

关于SELECT(*)

在表查询中，建议明确字段，不要使用 * 作为查询的字段列表，推荐使用SELECT<字段列表>查询。原因：
1、MySQL在解析的过程中，会通过查询数据字典将“*”按序转换成所有列名，这会大大的耗费资源和时间。
2、无法使用覆盖索引。

LIMIT 1 对优化的影响

针对的是会扫描全表的SQL语句，如果你可以确定结果集只有一条，那么加上LIMIT 1的时候，当找到一条结果的时候就不会继续扫描了，这样会加快查询速度。
如果数据表已经对字段建立了唯一索引，那么可以通过索引进行查询，不会全表扫描的话，就不需要加上LIMIT 1了。

多使用COMMIT

只要有可能，在程序中尽量多使用COMMIT，这样程序的性能得到提高，需求会因为COMMIT所释放的资源而减少。
COMMIT所释放的资源：
回滚段上用于恢复数据的信息
被程序语句获得的锁
redo / undo log buffer 中的空间
管理上述3中资源中的内部花费
MySQL默认是开启自动提交的，即每一条DML（增删改）语句都会被作为一个单独的事务进行隐式提交。