Mysql索引优化详细教程

江湖墨年JX

已于 2023-08-18 16:32:55 修改

阅读量259

点赞数

文章标签： mysql 数据库

于 2023-08-07 23:51:06 首次发布

本文链接：https://blog.csdn.net/qq_51867867/article/details/132157346

版权

第五章–逻辑架构

1.服务器处理客户端请求

Mysql是典型的C/S架构，即Client/Server架构，服务器端程序使用mysql.

那服务器进程对客户端进程发送的请求做了什么处理，才能产生最后的处理结果呢？这里以查询请求为例展示：

在这里插入图片描述

下面具体展开看一下（数字标号展示了5.7查询的顺序）：

在这里插入图片描述

Connectors: MySQL服务器之外的客户端程序，和具体编程语言相关的内容
Management Service &Utilities–>基础服务组件： MySQL服务器的基础服务组件
Connection Pool -->连接池：提供了多个用于客户端和服务器端进行交互的线程，这些线程使用完后交还到连接池，供其他客户端使用，从而保证资源不被浪费
SQL Interface–>SQL接口：作用是用来接收SQL指令并返回查询结果
Parser–>解析器：用来解析SQL接口中的SQL，分为语法解析和语义解析。解析后会生成一个语法树，该语法树可用于后续的查询优化。解析器将SQL语句“肢解”为关键字、表名、字段名等内容
Optimlzer–>优化器： 核心组件，对SQL进行优化：分为逻辑上的优化和物理上的优化。物理优化—使用索引
Cache & Buffers–>查询缓存: 在8.0中已经弃用。以key - value的方式缓存查询结果，查询结果作为value，SQL语句作为key。当下一次查询和缓存的查询语句完全一致时查询命中
pluggable Storage Engines–>插件式存储引擎： 与底层的文件系统进行交互
File system–>文件系统
File & Logs–>日志文件

2.MySQL 中的 SQL执行流程

在这里插入图片描述

查询顺序： Connectors–>Connection Pool (连接池)–>SQL Interface(SQL接口)–>Cache & Buffers(查询缓存)–>Parser(解析器)–>Optimlzer(优化器)–>pluggable Storage Engines(插件式存储引擎)–>File system(文件系统)–>Cache & Buffers(查询缓存)–>SQL Interface(SQL接口)

3.小结

MySQL架构图本节开篇所示。下面为了熟悉SQL执行流程方便，我们可以简化如下：

在这里插入图片描述

简化为三层结构：

连接层：客户端和服务器端建立连接，客户端发送 SQL 至服务器端；
SQL 层（服务层）：对 SQL 语句进行查询处理；与数据库文件的存储方式无关；
存储引擎层：与数据库文件打交道，负责数据的存储和读取。

第六章–存储引擎

1.InnoDB引擎是具备外键支持功能的事务存储引擎

优点：

支持事务处理
适用于更新和删除操作：除了增加和查询，InnoDB引擎还适用于更新和删除操作。
处理巨大数据量的最大性能：InnoDB引擎是为处理大量数据的最大性能而设计的，适用于并发量大和数据量大的场景。
支持行级锁：InnoDB引擎支持行级锁，锁粒度低，更适合高并发的场景。

缺点：

1.写的处理效率相对较低，占用更多磁盘空间以保存数据和索引；

2.InnoDB不仅缓存索引还要缓存真实数据，内存要求较高;

3.数据即索引，索引即数据；

2.MyISAM（5.5之前默认的存储引擎）

优点：

擅长增加和查询
优势是访问的速度快 ，对事务完整性没有要求或者以SELECT、INSERT为主的应用
针对数据统计有额外的常数存储。故而 count(*) 的查询效率很高
表和索引是分开存储的，只有非聚簇索引，叶子节点只储存数据的地址，就是需要回表操作。

缺点：

1.但MyISAM 不支持事务、行级锁、外键，有一个毫无疑问的缺陷就是崩溃后无法安全恢复 。

2.应用场景：只读应用或者以读为主的业务

3.MyISAM和InnoDB

对比项	MyISAM	InnoDB
外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁，操作时只锁某一行，不对其它行有影响，适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
自带系统表使用	Y	N
关注点	性能：节省资源、消耗少、简单业务	事务：并发写、事务、更大资源

系统表：是数据库管理系统（DBMS）内部使用的特殊数据库表，用于存储和管理关于数据库本身的元数据信息。它们包含了关于数据库结构、对象、访问权限、索引等方面的信息。系统表记录了数据库的内部状态和配置，以供DBMS进行运行时的管理和控制。

自适应哈希索引：如果检测到某个二级索引不断被使用，二级索引成为热数据，那么InnoDB会根据在二级索引树上的索引值在构建一个哈希索引来加速搜索（只适用于等值比较）；自适应哈希索引还具有动态调整的功能。如果索引不再被频繁查询，系统会逐渐将其从哈希索引中移除，回退到传统的B+树索引结构。这样可以避免冗余的内存占用和维护开销。

第七章–InnoDB数据存储结构（页）

1.磁盘与内存交互基本单位:页（默认为 16KB）

在数据库中，不论读一行，还是读多行，都是将这些行所在的页进行加载。也就是说，数据库管理存储空间的基本单位是页(Page)，数据库I/O操作的最小单位是页。

2.页结构概述

页a、页b、页c …页n这些页可以不在物理结构上相连，只要通过双向链表相关联即可。每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表，每个数据页都会为存储在它里边的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位。

SQL Server中页的大小为 8KB，而在oracle中用术语’‘块’’(Block)来代表"页”，Oralce支持的块大小为2KB，4KB，8KB，16K8，32KB和64KB。

第八章–索引的创建与设计原则

1.索引的分类

MySQL的索引包括**主键索引、唯一索引（unique 可null）、普通索引、全文索引（ElasticSearch取代）、单列索引、多列索引（组合、联合）和空间索引（只能建立在空间数据类型上；引字段不能为空；R-树）**等。

从功能逻辑上说，索引主要有4种，分别是普通索引、唯一索引、主键索引、全文索引
按照物理实现方式，索引可以分为2种:聚簇索引和非聚簇索引
按照作用字段个致进行划分，分成单列索引和联合索引

2.创建索引

2.1.创建表的时候创建索引

使用CREATETABLE创建表时，除了可以定义列的数据类型外，还可以定义主键约束、外键约束或者唯一性约束，而不论创建哪种约束。在定义以上三种约束的同时相当于在指定列上创建了一个索引。(AUTO_INCREASE前面必须要有unique或者primary key)

CREATE TABLE emp(
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20) UNIQUE,
    dept_id INT,
    CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id)
    INDEX(year_publication) //普通索引
    UNIQUE INDEX uk_idx_id(id) //唯一性索引;声明有唯一索引的字段，在添加数据时，要保证唯一性，但是可以添加null
    PRIMARY KEY(id) //主键索引
    INDEX single_idx_name(name(20)) //单列索引，只使用该列的前20个字符进行索引，优化索引的大小和性能。
    INDEX multi_idx(id,name,age) //联合索引
)ENGINE = INNODB ;

INT(10) UNSIGNED指示整数列的值必须是非负数

3.建表后索引的增删改查

3.1.增加索引

#01-create方式增加索引
create index 索引名 on 表名(字段 [asc/desc],) [INVISIBLE];
#例如
create index idx_name on student(id);

#02-Alter方式增加索引
Alter table 表名 add [unique] index idx_name(id) [INVISIBLE];
#例如
ALTER TABLE book5 ADD INDEX idx_cmt(COMMENT);#创建普通索引
ALTER TABLE book5 ADD UNIQUE uk_idx_bname(book_name);#创建唯一性索引

3.2.删除索引

#01-Alter方式删除索引
Alter table 表名 DROP Index 索引名;
#例如
Alter table student DROP Index idx_name;

#02-Alter通过删除主键约束的方式删除主键索引
ALTER TABLE book2 DROP PRIMARY KEY;

#03-DROP INDEX语句删除索引
DROP INDEX index_name ON table_name;

#删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。
#测试：删除联合索引中的相关字段，索引的变化
ALTER TABLE book5
DROP COLUMN book_name;

ALTER TABLE book5
DROP COLUMN book_id;

ALTER TABLE book5
DROP COLUMN info;

添加AUTO_INCREMENT约束字段的唯一索引不能被删除

3.3.修改索引

Alter table 表名 Alter index 索引名 visible/invisible

3.4.查看索引

#方式1：
SHOW CREATE TABLE book;
SHOW CREATE TABLE book\G(在客户端命令行中用\G代替分号;可以以垂直方式显示结果。)
/*
*************************** 1. row ***************************
       Table: book
Create Table: CREATE TABLE `book` (
  `book_id` int(11) DEFAULT NULL,
  `book_name` varchar(100) DEFAULT NULL,
  `AUTHORS` varchar(100) DEFAULT NULL,
  `info` varchar(100) DEFAULT NULL,
  `COMMENT` varchar(100) DEFAULT NULL,
  `year_publication` year(4) DEFAULT NULL,
  KEY `idx_bname` (`book_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
*/

#方式二
SHOW INDEX FROM book1;
/*
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| book1 |          0 | uk_idx_cmt |            1 | COMMENT     | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
*/


#性能分析工具：EXPLAIN
EXPLAIN SELECT * FROM book WHERE book_name = 'mysql高级';

4. MySQL8.0索引新特性

4.1.支持降序索引

降序索引以降序存储键值。虽然在语法上，从MysQL4版本开始就已经支持降序索引的语法了，但实际上该DESC定义是被忽略的，直到MySQL 8.x版本才开始真正支持降序索引〔仅限于InnoDB存储引擎)。
MySQL在8.0版本之前创建的仍然是升序索引，使用时进行反向扫描，这大大降低了数据库的效率。在某些场景下，降序索引意义重大。例如，如果一个查询，需要对多个列进行排序，且顺序要求不一致，那么使用降序索引将会避免数据库使用额外的文件排序操作，从而提高性能

4.2.隐藏索引

设置为隐藏索引，则该不起作用
在MySQL 5.7版本及之前，只能通过显式的方式删除索引。此时，如果发现删除索引后出现错误，又只能通过显式创建索引的方式将删除的索引创建回来。如果数据表中的数据量非常大，或者数据表本身比较大，这种操作就会消耗系统过多的资源，操作成本非常高。
从MySQL 8.x开始支持隐藏索引（invisible indexes），只需要将待删除的索引设置为隐藏索引，使查询优化器不再使用这个索引（即使使用force index（强制使用索引），优化器也不会使用该索引），确认将索引设置为隐藏索引后系统不受任何响应，就可以彻底删除索引。这种通过先将索引设置为隐藏索引，再删除索引的方式就是软删除。

同时，如果你想验证某个索引删除之后的查询性能影响，就可以暂时先隐藏该索引

注意:
主键不能被设置为隐藏索引。当表中没有显式主键时，表中第一个唯一非空索引会成为隐式主键，也不能设置为隐藏索引。

索引默认是可见的，在使用CREATE TABLE，CREATE INDEX或者ALTER TABLE等语句时可以通过VISIBLE或INVISIBLE关键词设置索引的可见性

注意
当索引被隐藏时，它的内容仍然是和正常索引一样实时更新的。如果一个索引需要长期被隐藏，那么可以将其删除，因为索引的存在会影响插入、更新和删除的性能

5.索引的设计原则

5.1.哪些情况适合创建索引

5.1.1.字段的数值有唯一性的限制

索引本身可以起到约束的作用，比如唯一索引、主键索引都是可以起到唯一性约束的，因此在我们的数据表中，如果某个字段是唯一性的，就可以直接创建唯一性索引，或者主键索引。这样可以更快速地通过该索引来确定某条记录。

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引（来源:Alibaba）
说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的

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

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

5.1.3.经常 GROUP BY 和 ORDER BY 的列

结论：
1.如果单独用GROUP BY，就针对对应字段建立索引，若对多个字段进行GROUP BY，可以建立联合索引。
2.如果既有GROUP BY又有ORDER BY可以考虑联合索引，此联合索引中要把GROUP BY 的字段写在前面，ORDER BY的字段写在后面，且8.0中若是降序的话加上DESC后效果更好

5.1.4.UPDATE、DELETE 的 WHERE 条件列

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

5.1.5.DISTINCT 字段需要创建索引

这是因为索引会对数据按照某种顺序进行排序，所以在去重的时候也会快很多

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

对用于连接的字段创建索引，并且该字段在多张表中的 类型必须一致(否则可能存在隐式转换导致索引失效) 。比如 course_id 在student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。

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

这里所说的类型大小指的就是该类型表示的数据范围的大小。
在定义表结构的时候要显式的指定列的类型，以整数类型为例，有TINYINT、MEDIUNINT、INT、BIGINT等，它们占用的存储空间依次递增，能表示的整数范围当然也是依次递增。如果想要对某个整数列建立索引的话。在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如能使用INT就不要使BIGINT，能使用MEDIUMINT就不要使用INT。这是因为:

数据类型越小，在查询时进行的比较操作越快
数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘I/O带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率
这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键使用更小的数据类型，也就意味着节省更多的存储空间和更高效的I/O。

5.1.8.使用字符串前缀创建索引

假设字符串很长，那存储一个字符串就需要占用很大的存储空间。在需要为这个字符串列建立索引时，那就意味若在对应的B+树中有这么两个问题:

1.B+树索引中的记录需要把该列的完整字符串存储起来，更费时。而且字符串越长，在索引中占用的存储空间越大。
2.如果B+树索引中索引列存储的字符串很长，那在做字符串比较时会占用更多的时间。
我们可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值。既节约空间，又减少了字符串的比较时间，还大体能解决排序的问题。
例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间，如果只检索字段前面的若干字符，这样可以提高检索速度。
创建一张商户表，因为地址字段比较长，在地址字段上建立前缀索引

先看一下字段在全部数据中的选择度：

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

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

count(distinct left(列名, 索引长度))/count(*)
#例如
select count(distinct left(address,10)) / count(*) as sub10, -- 截取前10个字符的选择度
count(distinct left(address,15)) / count(*) as sub11, -- 截取前15个字符的选择度
count(distinct left(address,20)) / count(*) as sub12, -- 截取前20个字符的选择度
count(distinct left(address,25)) / count(*) as sub13 -- 截取前25个字符的选择度
from shop;
数值越接近1更好

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

SELECT FROM shop ORDER BY address
LIMIT 12;

因为二级索引中不包含完整的address列信息，所以无法对前12个字符相同、后边的字符不同的记录进行排序，也就是使用索引列前缀的方式无法支持使用索引排序，只能使用文件排序。

拓展：Alibaba《Java开发手册》
[强制]在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。
说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达90% 以上，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。

5.1.9.区分度高(散列性高)的列适合作为索引

列的基数: 指的是某一列中不重复数据的个数，比方说某个列包含值2，5，8，2，5，8，2，5，8，虽然有9条记录，但该列的基数却是3(2，5，8不重复的个数 )。也就是说，在记录行数一定的情况下，列的基数越大，该列中的值越分散;列的基数越小，该列中的值越集中。这个列的基数指标非常重要，直接影响我们是否能有效的利用索引。最好为列的基数大的列建立索引，为基数太小列的建立索引效果可能不好。
可以使用公式 select count(distinct a)/count(*) from t1计算区分度，越接近1越好，一般超过33%就算是比较高效的索引了。
拓展:联合索引把区分度高（散列性高）的列放在前面。

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

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

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

6.哪些情况不适合创建索引

6.1. 在where中使用不到的字段，不要设置索引

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

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

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

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

在条件表达式中经常用到的不同值较多的列上建立索引，但字段中如果有大量重复数据，也不用创建索引。比如在学生表的“性别”字段上只有“男”与"女"两个不同值，因此无须建立索引。如果建立索引，不但不会提高查询效率，反而会严重降低数据更新速度。

索引的价值是帮助快速定位。如果想要定位的数据有很多，那么索引就失去了它的使用价值，比如通常情况下的性别字段。

> 结论：当数据重复度大，比如 高于 10% 的时候，也不需要对这个字段使用索引

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

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

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

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

有时候有意或者无意的就对同一个列创建了多个索引，比如: index(a,b,c)相当于index(a)、index(a,b),indexla,b,c)。

7.小结

索引是一把双刃剑，可提高查询效率，但也会降低插入和更新的速度并占用磁盘空间。
选择索引的最终目的是为了使查询的速度变快，上面给出的原则是最基本的准则，但不能拘泥于上面的准则，大家要在以后的学习和工作中进行不断的实践，根据应用的实际情况进行分析和判断，选择最合适的索引方式。

第九章–性能分析工具的使用

1.分析查询语句：EXPLAIN

1.1基本语法

EXPLAIN SELECT select_options
#或者
DESCRIBE SELECT select_options

输出的上述信息就是所谓的执行计划。在这个执行计划的辅助下，我们需要知道应该怎样改进自己的查询语句以使查询执行起来更高效。其实除了以SELECT开头的查询语句，其余的DELETE、INSERT、REPLACE以及UPDATE语句等都可以加上EXPLAIN，用来查看这些语句的执行计划，只是平时我们对SELECT语句更感兴趣

列名	描述
id	在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
select_type	SELECT关键字对应的那个查询的类型
table	表名
partitions	匹配的分区信息
type	针对单表的访问方法
possible_keys	可能用到的索引
key	实际上使用的索引
key_len	实际使用到的索引长度(单位：字节)
ref	当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows	预估的需要读取的记录条数
filtered	某个表经过搜索条件过滤后剩余记录条数的百分比
Extra	一些额外的信息

注意：

CREATE TABLE s1 (
id INT AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,  #key_len:INT可以为空的情况下一定是4+1（null）个字节，key2 INT（3/8）也是
key3 VARCHAR(100), #因为字符集是utf8，所以一个字符占三个字节，100*3，因为还可能是一个边长的类型，所以再+2，+1（null）=303
#变长字符类型（如VARCHAR）可动态变化
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
INDEX idx_key1 (key1),
UNIQUE INDEX idx_key2 (key2),
INDEX idx_key3 (key3),
INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

小结

EXPLAIN不考虑各种Cache
EXPLAIN不能显示MySQL在执行查询时所作的优化工作
EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
部分统计信息是估算的，并非精确值
使用完EXPLAIN后接着使用show warings\G可以查看实际执行的sql句式

#json模式可以看到更加详细的信息，特别是"cost_info": {
      "query_cost": "1360.07"
    } 查询成本
EXPLAIN FORMAT=JSON SELECT ...

第十章–索引优化与查询优化

1.索引失效案例

1.1最佳做前缀法（联合索引）

结论:
MySQL可以为多个字段创建索引，一个索引可以包括16个字段。对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。如果查询条件中没有使用这些字段中第1个字段时，多列(或联合)索引不会被使用。

1.2主键插入顺序

对于一个使用InnoDB存储引擎的表来说，在没有显式的创建索引时，表中的数据实际上都是存储在聚簇索引的叶子节点的。而记录又是存储在数据页中的，数据页和记录又是按照记录主键值从小到大的顺序进行排序，所以如果插入的记录的主键值是依次增大的话，那每插满一个数据页就换到下一个数据页继续插，而如果插入的主键值忽大忽小的话（一般不让这种情况发生），就比较麻烦了，就可能发生页面分裂和记录移位
意味着：性能损耗！所以如果想尽量避免这样无谓的性能损耗，最好让插入的记录的主键值依次递增 ，这样就不会发生这样的性能损耗了。
所以建议：让主键具有 AUTO_INCREMENT ，让存储引擎自己为表生成主键，而不是手动插入。

1.3.计算、函数、类型转换(自动或手动)导致索引失效

#LEFT(student.name,3) = 'abc'; 中left函数的使用导致索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc'; 

EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;
/*使用“stuno+1 = 900001”算术运算导致索引失效

1.4.类型转换导致索引失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = 123; 
/*没有使用索引：name是字符串类型，和int匹配要类型转换

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = '123'; 
/*使用了索引：

1.5.范围条件右边的列索引失效

范围条件：含(<) (<=) (>) (>=)和between等的条件

EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ; 
/*Using index condition表示：有些搜索条件中虽然出现了索引列，但却不能使用到索引
#使用了索引 idx_age_classId_name但是只用了联合索引的前两个字段
对于优化器来说AND连接的字段先写哪个后写哪个无所谓

启发：

应用开发中范围查询，例如:金额查询，日期查询往往都是范围查询。应将查询条件放置where语句最后，建索引时也放在最后
(创建的联合索引中，务必把范围涉及到的字段写在最后)

1.6不等于(!= 或者<>)索引失效(不一定看情况)

#不等于时用不上B+树，只能一个一个查找
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name <> 'abc' ;
#或
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name != 'abc' ;
/*索引失效

1.7.is null可以使用索引，is not null无法使用索引(不一定看情况)

# is null可以使用索引，is not null无法使用索引
#is null可以使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL; 
#is not null无法使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL; 

#因为一般情况not null值太多还不如直接全表查询，所以具体得看null的数量

最好在设计数据表的时候就将字段设置为 NOT NULL约束，比如可以将INT类型的字段，默认值设置为0。将字符类型的默认值设置为空字符串''。
拓展:同理,在查询中使用not like 也无法使用索引，导致全表扫描

例外情况：

CREATE INDEX idx_age_name ON student (age,NAME);

EXPLAIN SELECT * FROM student WHERE age <> 20; #索引失效

EXPLAIN SELECT age,NAME FROM student WHERE age <> 20; #使用了索引，而且是覆盖索引。
/*使用了索引，打破了前面说的“不等于”的查询索引会失效的原则
原因：查询优化器发现使用索引时，不会回表，开销更小，故使用了索引

1.8 like以通配符%开头索引失效

在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为"%”，索引就不会起作用。只有"%"不在第一个位置，索引才会起作用

# like以通配符%开头索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE 'ab%'; 
/*使用了索引
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | student | NULL       | range | idx_name      | idx_name | 63      | NULL |  711 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
*/
/*未使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE '%ab%';
/*
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 498858 |    11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
*/

Alibaba《Java开发手册》
【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

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

在WHERE子句中，如果在OR前的条件列进行了索引，而在OR后的条件列没有进行索引，那么索引会失效。也就是说，OR前后的两个条件中的列都是索引时，查询中才使用索引
因为OR的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的，只要有条件列没有进行索引，就会进行全表扫描，因此索引的条件列也会失效

#只为age或者classid创建索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;
#没有使用索引

#为前后两个索引都创建索引，则OR连接他们时就可以使用索引
CREATE INDEX idx_cid ON student(classid);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;
#因为age字段和classid字段上都有索引，所以查询中使用了索引

1.10.不同的字符集进行比较前需要进行转换、会造成索引失效

统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的字符集进行比较前需要进行转换会造成索引失效
建议：数据库和表的字符集统一使用utf8mb4

一般性建议:

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

2.关联查询优化（多表查询）

2.1.采用左外连接

#给被驱动表加了索引可以避免全表扫描
ALTER TABLE book ADD INDEX Y (card); #【被驱动表】，可以避免全表扫描
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

#给驱动表加了索引也要全表扫描
ALTER TABLE `type` ADD INDEX X (card); #【驱动表】，无法避免全表扫描
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

2.2.采用内连接

结论：
1.对于内连接来说，查询优化器可以决定谁作为驱动表，谁作为被驱动表出现的
2.对于内连接来讲，如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表出现
3.对于内连接来说，在两个表的连接条件都存在索引的情况下，会选择小表作为驱动表。“小表驱动大表”

2.3.join语句原理

2.3.1.驱动表和被驱动表

驱动表就是主表，被驱动表就是从表、非驱动表
EXPLAIN执行结果的记录中，上面的是驱动表，下面的是被驱动表

对于外连接来说:
```
SELECT * FROM A LEFT JOIN B ON ...
#或者
SELECT * FROM B RIGHT JOIN A ON ...
```
通常，大家会认为A就是驱动表，B就是被驱动表，但也未必

2.3.2.Simple Nested-Loop Join(简单嵌套循环连接)

在这里插入图片描述

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

2.3.3.Index Nested-Loop Join《索引嵌套循环连接)

在这里插入图片描述

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

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

2.3.4.Block Nested-Loop Join(块嵌套循环连接)

在这里插入图片描述

Block Nested-Loop Join 的过程：该方法引入了 join buffer 缓冲区，将驱动表中相关的数据列缓存到 join buffer 中。然后对被驱动表进行全表扫描，每条记录通过一次性与 join buffer 中的所有驱动表记录进行匹配（在内存中操作），从而将多次比较合并为一次，降低了被驱动表的访问频率。

块和页的区别：

在数据库中，块（Block）和页（Page）是两个概念，它们在不同的上下文中具有不同的含义。

在这段话中提到的 Block Nested-Loop Join 中的块（Block）是指引入了 join buffer(默认256k) 缓冲区后，将驱动表中相关的数据列缓存到内存中的一组数据。这里的块并不是操作系统或数据库管理系统中的物理块，而是在算法层面上定义的逻辑单位。

而页（Page ）是数据库管理系统中磁盘和内存之间进行数据交互的最小单位。数据库系统通常会将数据划分成固定大小的页，Mysqlz中Innodb中页一般为 16KB。在查询过程中，数据库引擎以页为单位从磁盘加载数据到内存，进行数据读取和处理。

因此，块和页在这段话中的意义不完全相同。块是一种逻辑上的概念，指的是将驱动表中相关的数据列缓存到内存中的一组数据；而页是一个物理上的概念，指的是数据库管理系统中磁盘和内存之间进行数据交互的最小单位。

当执行 Block Nested-Loop Join 时，MySQL 引擎将以页为单位将需要的数据从磁盘加载到内存中，然后将这些数据按照块的概念缓存到 join buffer 中供后续的匹配操作使用。这样可以减少对磁盘的访问次数，提高查询效率。

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

2.3.5.小结

整体效率比较: INLJ > BNLJ > SNLJ

永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数目) (小的度量单位指的是表行数*每行大小；where也会影响表行数)

select t1.b,t2.* from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=100; #推荐
select t1.b,t2.* from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=100; #不推荐
#因为t2作为驱动表需要把所以所有字段加载到Join Buffer中，这样放的条数就少。

为被驱动表匹配的条件增加索引(减少内层表的循环匹配次数)
增大join buffer size的大小(一次缓存的数据越多，那么内层包的扫表次数就越少)
减少驱动表不必要的字段查询（字段越少，join buffer所缓存的数据就越多)
保证被驱动表的J0IN字段已经创建了索引
需要JOIN的字段，数据类型保持绝对一致
LEFT JOlN时，选择小表作为驱动表，大表作为被驱动表。减少外层循环的次数。INNER JOIN时，MySQL会自动将小结果集的表选为驱动表。选择相信MySQL优化策略
能够直接多表关联的尽量直接关联，不用子查询(减少查询的趟数)
不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用JOIN来代替子查询
衍生表建不了索引

2.3.6.Hash Join

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

Nested Loop:
对于被连接的数据子集较小的情况，Nested Loop是个较好的选择。
Hash Join是做大数据集连接时的常用方式，优化器使用两个表中较小(相对较小）的表利用Join Key在内存中建立散列表，然后扫描较大的表并探测散列表，找出与Hash表匹配的行。它能够很好的工作于没有索引的大表和并行查询的环境中，并提供最好的性能。大多数人都说它是Join的重型升降机。Hash Join只能应用于等值连接(如WHERE A.COL1=B.COL2)，这是由Hash的特点决定的

类别	Nested Loop	Hash Join
使用条件	任何条件	等值连接(=)
相关资源	CPU、磁盘IO	内存、临时空间
特点	当有高选择性索引或进行限制性搜索时效率比较高能能够快速返回第一次的搜索结果	当缺乏索引或索引条件模糊时，Hash Join比Nested于率比较高，Loop有效。在数据仓库环境下，如果表的纪录数多，效率高
缺点	当索引丢失或者查询条件限制不够时，效率很低。当表的纪录数多时，效率低	为建立哈希表，需要大量内存。第一次的结果返回较慢

3.子查询优化

子查询的执行效率不高。原因:

执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的CPU和IO资源，产生大量的慢查询
子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响
对于返回结果集比较大的子查询。其对查询性能的影响也就越大

结论：尽量不要使用NOT IN 或者 NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代

4.排序优化（!）

在MySQL中，支持两种排序方式，分别是 FileSort 和Index排序。

Index排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高
FileSort 排序则一般在内存中进行排序，占用CPU 较多。如果待排结果较大，会产生临时文件I/O到磁盘进行排序的情况，效率校低

#创建索引  
CREATE  INDEX idx_age_classid_name ON student (age,classid,NAME);

#不限制,索引失效;因为如果使用了索引还得进行所有的回表操作，还不如 FileSort 。
EXPLAIN  SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid; 
#增加limit过滤条件，使用上索引了。因为回表次数少。
EXPLAIN  SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10;  


#过程四：order by时规则不一致, 索引失效 （顺序错，不索引；方向反，不索引）

EXPLAIN  SELECT * FROM student ORDER BY age DESC, classid ASC LIMIT 10;#失效，没用使用索引

EXPLAIN  SELECT * FROM student ORDER BY classid DESC, NAME DESC LIMIT 10;#失效，没用使用索引

EXPLAIN  SELECT * FROM student ORDER BY age ASC,classid DESC LIMIT 10; #失效，没用使用索引

#！！！！！！！！！！！！！！！！！！！！！！！！！！
EXPLAIN  SELECT * FROM student ORDER BY age DESC, classid DESC LIMIT 10;#都反了反而使用了索引



#过程五：无过滤，不索引
EXPLAIN  SELECT * FROM student WHERE age=45 ORDER BY classid;#使用了索引,仅age字段
/*
+----+-------------+---------+------------+------+--------------------------------------------+-----------------------+---------+-------+-------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys                              | key                   | key_len | ref   | rows  | filtered | Extra |
+----+-------------+---------+------------+------+--------------------------------------------+-----------------------+---------+-------+-------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_age_classid_name,idx_age_classid_stuno | idx_age_classid_stuno | 5       | const | 19184 |   100.00 | NULL  |
+----+-------------+---------+------------+------+--------------------------------------------+-----------------------+---------+-------+-------+----------+-------+
*/
EXPLAIN  SELECT * FROM student WHERE  age=45 ORDER BY classid,NAME; #使用了索引,仅age字段

EXPLAIN  SELECT * FROM student WHERE  classid=45 ORDER BY age;

EXPLAIN  SELECT * FROM student WHERE  classid=45 ORDER BY age LIMIT 10;#使用了索引,用了所有字段

CREATE INDEX idx_cid ON student(classid);
EXPLAIN  SELECT * FROM student WHERE  classid=45 ORDER BY age;

小结：

INDEX a_b_c(a,b,c)
order by 能使用索引最左前缀
- ORDER BY a
- ORDER BY a,b
- ORDER BY a,b,c
- ORDER BY a DESC,b DESC,c DESC
如果WHERE使用索引的最左前缀定义为常量，则order by 能使用索引
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b = const ORDER BY c
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b > const ORDER BY b,c
不能使用索引进行排序
- ORDER BY a ASC,b DESC,c DESC /* 排序不一致 */
- WHERE g = const ORDER BY b,c /*丢失a索引*/
- WHERE a = const ORDER BY c /*丢失b索引*/
- WHERE a = const ORDER BY a,d /*d不是索引的一部分*/
- WHERE a in (...) ORDER BY b,c /*对于排序来说，多个相等条件也是范围查询*/

SQL中，可以在WHERE子句和ORDER BY子句中使用索引，目的是在WHERE子句中避免全表扫描，在ORDER EY子句说免使用 FileSort排序。当然，某些情况下全表扫描，或者FileSort排序不一定比索引慢。但总的来说，还是要避免，以提高查询效率。

5.GROUP BY优化

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

6.分页查询优化(limit优化)

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

在大数据量的分页查询时,limit后的起始位置越靠后，耗时越长

EXPLAIN select * from student limit 2000000,10;

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

EXPLAIN SELECT * FROM student ORDER BY id LIMIT 2000000,10;

优化思路二
该方案适用于主键自增的表，可以把Limit查询转换成某个位置的查询

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

7.优先考虑覆盖索引

覆盖索引： SQL只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据（不需要回表），所有应该减少使用selecl *.

覆盖索引的利弊：

好处：

避免Innodb表进行索引的二次查询（回表）
可以把随机IO（回表时）变成顺序IO加快查询效率
由于覆盖索引是按键值的顺序存储的，对于IO密集型的范围查找来说，对比随机从磁盘读取每一行的数据IO要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的IO转变成索引查找的顺序IO。
由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段

弊端：

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

#6. 覆盖索引
#删除之前的索引
#举例1：
DROP INDEX idx_age_stuno ON student;

CREATE INDEX idx_age_name ON student (age,NAME);

EXPLAIN SELECT * FROM student WHERE age <> 20;

EXPLAIN SELECT age,NAME FROM student WHERE age <> 20;
/*使用了索引，打破了前面说的“不等于”的查询索引会失效的原则
原因：查询优化器发现使用索引时，不会回表，开销更小，故使用了索引

#举例2：
EXPLAIN SELECT * FROM student WHERE NAME LIKE '%abc'; #未使用上索引

EXPLAIN SELECT id,age FROM student WHERE NAME LIKE '%abc'; #使用上了索引

8.如何给字符串添加索引

select col1, col2 from teacher where email='xxx';

前缀索引

MySQL是支持前缀索引的。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串

alter table teacher add index index1(email);
#或
alter table teacher add index index2(email(6))

#email=23343@qq.com,后面是重复的那就更适合创建前缀索引了。

前面已经讲过区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少

前缀索引对覆盖索引的影响

结论：
使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。因为索引中这个字段不是完整的了。

9.索引下推（ICP—Index Condition Pushdown）

9.1.概述

索引条件下推优化（Index Condition Pushdown (ICP) ）是MySQL5.6添加的，用于优化数据查询。

不使用索引条件下推优化时存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件。

当使用索引条件下推优化时，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

9.2.适用条件

需要整表扫描的情况。比如：range, ref, eq_ref, ref_or_null 。适用于InnoDB 引擎和 MyISAM 引擎的查询。（5.6版本不适用分区表查询，5.7版本后可以用于分区表查询）。
对于InnDB引擎只适用于二级索引，因为InnDB的聚簇索引会将整行数据读到InnDB的缓冲区，这样一来索引条件下推的主要目的减少IO次数就失去了意义。因为数据已经在内存中了，不再需要去读取了。
引用子查询的条件不能下推。
调用存储过程的条件不能下推，存储引擎无法调用位于MySQL服务器中的存储过程。
触发条件不能下推。

9.3.不使用索引条件下推优化时的查询过程

获取下一行，首先读取索引信息，然后根据索引将整行数据读取出来。
然后通过where条件判断当前数据是否符合条件，符合返回数据。

9.4.使用索引条件下推优化时的查询过程

获取下一行的索引信息。
检查索引中存储的列信息是否符合索引条件，如果符合将整行数据读取出来，如果不符合跳过读取下一行。
用剩余的判断条件，判断此行数据是否符合要求，符合要求返回数据。

EXPLAN分析

当使用explan进行分析时，如果使用了索引条件下推，Extra会显示Using index condition。并不是Using index因为并不能确定利用索引条件下推查询出的数据就是符合要求的数据，还需要通过其他的查询条件来判断。

图一：不使用ICP技术（过程使用数字符号标示，如①②③等）

在这里插入图片描述

过程解释：
①：MySQL Server发出读取数据的命令，这是在执行器中执行如下代码段，通过函数指针和handle接口调用存储引擎的索引读或全表表读。此处进行的是索引读。

②、③：进入存储引擎，读取索引树，在索引树上查找，把满足条件的（经过查找，红色的满足）从表记录中读出（步骤④，通常有IO），从存储引擎返回⑤标识的结果。此处，不仅要在索引行进行索引读取（通常是内存中，速度快。步骤③），还要进行进行步骤④，通常有IO。
⑥：从存储引擎返回查找到的多条元组给MySQL Server，MySQL Server在⑦得到较多的元组。
⑦–⑧：⑦到⑧依据WHERE子句条件进行过滤，得到满足条件的元组。注意在MySQL Server层得到较多元组，然后才过滤，最终得到的是少量的、符合条件的元组。

图二：使用ICP技术（过程使用数字符号标示，如①②③等）

在这里插入图片描述

过程解释：
①：MySQL Server发出读取数据的命令，过程同图一。
②、③：进入存储引擎，读取索引树，在索引树上查找，把满足已经下推的条件的（经过查找，红色的满足）从表记录中读出（步骤④，通常有IO），从存储引擎返回⑤标识的结果。

此处，不仅要在索引行进行索引读取（通常是内存中，速度快。步骤③），还要在③这个阶段依据下推的条件进行进行判断，不满足条件的，不去读取表中的数据，直接在索引树上进行下一个索引项的判断，直到有满足条件的，才进行步骤④，这样，较没有ICP的方式，IO量减少。
⑥：从存储引擎返回查找到的少量元组给MySQL Server，MySQL Server在⑦得到少量的元组。因此比较图一无ICP的方式，返回给MySQL Server层的即是少量的、符合条件的元组。
另外，图中的部件层次关系，不再进行解释。

9.5.示例

假设有一张people表,包含字段name、address、first_name
索引为(name,address,first_name)
然后我们执行下面的查询

SELECT * FROM person  WHERE `name` = "1" AND `address` LIKE "%222" and first_name LIKE "%222";

如果不使用索引条件下推优化的话，MySQL只能根据索引查询出name=1的所有行，然后再依次比较是否符合全部条件。

当使用了索引条件下推优化技术后，可以通过索引中存储的数据判断当前索引对应的数据是否符合条件，只有符合条件的数据才将整行数据查询出来。查看执行计划时发现extra一栏中有Using index condition信息，说明使用了索引下推。

9.6.思考

索引下推优化技术其实就是充分利用了索引中的数据，尽量在查询出整行数据之前过滤掉无效的数据。

由于需要存储引擎将索引中的数据与条件进行判断，所以这个技术是基于存储引擎的，只有特定引擎可以使用。并且判断条件需要是在存储引擎这个层面可以进行的操作才可以，比如调用存储过程的条件就不可以，因为存储引擎没有调用存储过程的能力。

9.7.其他

Using where：表示优化器需要通过索引回表查询数据；
Using index：表示直接访问索引就足够获取到所需要的数据，不需要通过索引回表；(覆盖索引)
Using index condition：在5.6版本后加入的新特性（Index Condition Pushdown）

10.查询过程

假设，执行查询的语句是 select id from test where k=5。

对于普通索引来说，查找到满足条件的第一个记录(5,500)后，需要查找下一个记录，直到碰到第一个不满足k=5条件的记录
对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索
那么，这个不同带来的性能差距会有多少呢？答案是，微乎其微

11.其它查询优化策略

11.1EXISTS 和 IN 的区分

问题：
不太理解哪种情况下应该使用 EXISTS，哪种情况应该用 IN。选择的标准是看能否使用表的索引吗？

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

#A是大表，B是小表情况
SELECT * FROM A  WHERE cc IN(SELECT cc FRON B) #不相关子查询，从里面查出来给外面用

#A是小表，B是大表情况
SELECT * FROM A WHERE EXISTS (SELECT cc FRON B WHERE B.cc=A.cc) #exists使用在相关子查询中，从外面一条条拿数据到里面用

11.2.关于SELECT(*)

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

11.3.COUNT(*)与COUNT(具体字段)效率

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

环节1:
COUNT(*)和COUNT(1)都是对所有结果进行COUNT,COUNT(*)和COUNT(1)本质上并没有区别

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

如果是InnoDB存储引擎，因为InnoDB支持事务，采用行级锁和MVCC机制，所以无法像 MyISAM—样，维护一个row_count变量，因此需要采用扫描全表，进行循环＋计数的方式来完成统计
环节3:
在InnoDB 引擎中，如果采用COUNT(具体字段)来统计数据行数，要尽量采用二级索引。因为主键采用的索引是聚簇索引，聚簇索引包含的信息多，明显会大于二级索引（非聚簇索引)。对于COUNT(*)和COUNT(1)来说，它们不需要查找具体的行，只是统计行数，系统会自动采用占用空间更小的二级索引来进行统计。
如果有多个二级索引，会使用key_len 小的二级索引进行扫描。当没有二级索引的时候，才会采用主键索引来进行统计