2020-06-28 20:23 已编辑华为_2012实验室_软开

关注

数据库学习笔记（Part 2 MySQL 基础及索引）

前言

本篇依旧是参考了CyC2018以及其他大佬的博客，这里也不一一列举，如有侵权，请联系我，我会标注出处。部分在这里补充一些遗漏的基础知识，再记录MySQL的使用的基础知识点以及索引部分。
知识点会比较乱，因为比较杂。

一、基础知识篇

1.主键和外键

关系型数据库中的一条记录中有若干个属性，若其中某一个属性组(注意是组)能唯一标识一条记录，该属性组就可以成为一个主键。

主键保证的是数据du的唯一性，是能够唯一的标zhi识一组数据的数据元素；比如说dao：学号，姓名，年龄，性别，课程号课程中学号是唯一的，所以可以设它为主键；

外键保证的是数据的完整性。外键：一组数据的主键是另一组数据的元素，那么这个组的主键就是另一个组的外键；主键约束了外键所在表中不能存在主键类之外的值；外键用于与另一张表的关联。是能确定另一张表记录的字段，用于保持数据的一致性。比如，A表中的一个字段，是B表的主键，就可以是A表的外键。

2.数据库存储过程

SQL语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟，它允许控制数据的访问方式。

存储过程的优点：

增强SQL语言的功能和灵活性
标准组件式编程：存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。
较快的执行速度：如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。
减少网络流量：针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织进存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大减少网络流量并降低了网络负载。
作为一种安全机制来充分利用：通过对执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

3.关系型数据库和非关系型数据库区别

一、关系型数据库

1.概念

关系型数据库是指采用了关系模型来组织数据的数据库。简单来说，关系模式就是二维表格模型。
主要代表：SQL Server，Mysql

2.优点

（1）容易理解，二维表的结构非常贴近现实世界，二维表格，容易理解。

（2）使用方便，通用的sql语句使得操作关系型数据库非常方便。

（3）易于维护，数据库的ACID属性，大大降低了数据冗余和数据不一致的概率。

3.瓶颈

海量数据的读写效率。
对于网站的并发量高，往往达到每秒上万次的请求，对于传统关系型数据库来说，硬盘I/o是一个很大的挑战。

高扩展性和可用性。
在基于web的结构中，数据库是最难以横向拓展的，当一个应用系统的用户量和访问量与日俱增的时候，数据库没有办法像web Server那样简单的通过添加更多的硬件和服务节点来拓展性能和负载能力。

二、非关系型

1.概念

NoSQL非关系型数据库，主要指那些非关系型的、分布式的，且一般不保证ACID的数据存储系统，主要代表MongoDB，Redis。

NoSQL提出了另一种理念，以键值来存储，且结构不稳定，每一个元组都可以有不一样的字段，这种就不会局限于固定的结构，可以减少一些时间和空间的开销。使用这种方式，为了获取用户的不同信息，不需要像关系型数据库中，需要进行多表查询。仅仅需要根据key来取出对应的value值即可。

2.缺点

但是由于Nosql约束少，所以也不能够像sql那样提供where字段属性的查询。因此适合存储较为简单的数据。有一些不能够持久化数据，所以需要和关系型数据库结合。

3.目前许多大型互联网都会选用MySql+NoSql的组合方案，因为SQL和NoSql都有各自的优缺点。

关系型数据库适合存储结构化数据，比如：用户的账号、地址：

（1）这些数据通常需要做结构化查询，比如说Join，这个时候，关系型数据库就要胜出一筹。

（2）这些数据的规模、增长的速度通常是可以预期的。

（3）事务性、一致性,适合存储比较复杂的数据。

NoSql适合存储非结构化数据，比如：文章、评论：

（1）这些数据通常用于模糊处理，例如全文搜索、机器学习，适合存储较为简单的数据。

（2）这些数据是海量的，并且增长的速度是难以预期的。

（3）按照key获取数据效率很高，但是对于join或其他结构化查询的支持就比较差。

4.mysql varchar与char的类型

区别一，定长和变长
char 表示定长，长度固定，varchar表示变长，即长度可变。char如果插入的长度小于定义长度时，则用空格填充；varchar小于定义长度时，还是按实际长度存储，插入多长就存多长。

因为其长度固定，char的存取速度还是要比varchar要快得多，方便程序的存储与查找；但是char也为此付出的是空间的代价，因为其长度固定，所以会占据多余的空间，可谓是以空间换取时间效率。varchar则刚好相反，以时间换空间。

区别之二，存储的容量不同
对 char 来说，最多能存放的字符个数 255，和编码无关。
而 varchar 呢，最多能存放 65532 个字符。varchar的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是 65,532字节。且varchar产生的碎片小。

5.mysql中删除数据的几种方式对比

1、drop (删除表)：

删除内容和定义，释放空间。简单来说就是把整个表去掉.以后要新增数据是不可能的,除非新增一个表。

2、truncate (清空表中的数据)：

删除内容、释放空间但不删除定义(保留表的数据结构)。与drop不同的是,只是清空表数据而已。比如现在有5行，删完后只是这5行的数据是空的。

注意:truncate 不能删除行数据,要删就要把表清空。

3、delete (删除表中的数据)：

delete 语句用于删除表中的行。delete语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存

以便进行进行回滚操作。

truncate与不带where的delete ：只删除数据，而不删除表的结构（定义）

4、truncate table

删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用delete。

如果要删除表定义及其数据，请使用 drop table 语句。

6 几个联结的区别

left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行
交叉连接: 交叉连接又叫笛卡尔积，它是指不使用任何条件，直接将一个表的所有记录和另一个表中的所有记录一一匹配。

7.什么叫视图？游标是什么？

视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，视图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。

游标：是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

视图的优点：
(1) 视图能够简化用户的操作
(2) 视图使用户能以多种角度看待同一数据；
(3) 视图为数据库提供了一定程度的逻辑独立性；
(4) 视图能够对机密数据提供安全保护。

8.主键、外键和索引的区别？

定义：
主键--唯一标识一条记录，不能有重复的，不允许为空
外键--表的外键是另一表的主键, 外键可以有重复的, 可以是空值
索引--该字段没有重复值，但可以有一个空值

作用：
主键--用来保证数据完整性
外键--用来和其他表建立联系用的
索引--是提高查询排序的速度

个数：
主键只能有一个
一个表可以有多个外键
一个表可以有多个唯一索引

9.mysql中 in 和 exists 区别

结论：
如果查询的两个表大小相当，那么用in和exists差别不大。

IN查询在内部表和外部表上都可以使用到索引；
Exists查询仅在内部表上可以使用到索引；

当子查询结果集很大，而外部表较小的时候，Exists的Block Nested Loop(Block 嵌套循环)的作用开始显现，并弥补外部表无法用到索引的缺陷，查询效率会优于IN。
当子查询结果集较小，而外部表很大的时候，Exists的Block嵌套循环优化效果不明显，IN 的外表索引优势占主要作用，此时IN的查询效率会优于Exists。IN()查询适合B表数据比A表数据小的情况，IN()查询是从缓存中取数据

但此处存疑，最好能亲手对比一下。

not in 和not exists：如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

10.FLOAT和DOUBLE的区别

FLOAT类型数据在内存中占4字节。
DOUBLE类型数据在内存中占8字节。

11.什么是子查询

条件：一条SQL语句的查询结果做为另一条查询语句的条件或查询结果

嵌套：多条SQL语句嵌套使用，内部的SQL查询语句称为子查询。

二、索引篇

要了解索引必须知道B树和B+树即底层数据结构

索引的数据结构实现

1.B树和B+树

B树每个节点都存储数据，所有节点组成这棵树。B+树只有叶子节点存储数据（B+数中有两个头指针：一个指向根节点，另一个指向关键字最小的叶节点），叶子节点包含了这棵树的所有数据，所有的叶子结点使用链表相连，便于区间查找和遍历，所有非叶节点起到索引作用。
B+树中查找，无论查找是否成功，每次都是一条从根节点到叶节点的路径。

B树的优点

B树的每一个节点都包含key和value，因此经常访问的元素可能离根节点更近，因此访问也更迅速。

B+树的优点:
性能上（也即为什么说B+树比B树更适合实际应用中操作系统的文件索引和数据库索引？）

所有的叶子结点使用链表相连，便于区间查找和遍历。B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。b+树的中间节点不保存数据，能容纳更多节点元素。

B+树的磁盘读写代价更低，因为B+树的所有非叶子节点只会存放索引信息，而真正的数据信息都只存放在叶子节点中，这样一来，每个非叶子节点存放的索引信息就更多，一次磁盘IO就可以读取更多的索引信息到内存中，可以减少磁盘IO的次数。

B+树的查询效率更加稳定，由于非叶子节点只存索引信息，而没有真正的数据信息，所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

B+树更加适合在区间查询的情况，由于B+树的数据都存储在叶子结点中，非叶子结点均为索引，只需要扫一遍叶子结点即可得到所有数据信息，但是B树因为其非叶子结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。

缺点

B+树最大的性能问题在于会产生大量的随机IO，主要存在以下两种情况：

主键不是有序递增的，导致每次插入数据产生大量的数据迁移和空间碎片；插入删除操作会破坏平衡树的平衡性，因此在进行插入删除操作之后，需要对树进行分裂、合并、旋转等操作来维护平衡性。

即使主键是有序递增的，大量写请求的分布仍是随机的；

不使用红黑树是因为红黑树需要频繁的调整

2.MySQL引擎

首先必须强调，索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。因此，即使是MySQL，针对于其不同的引擎如InnoDB和MyISAM也是要分情况讨论的。

InnoDB

是 MySQL 默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎。

实现了四个标准的隔离级别，默认级别是可重复读（REPEATABLE READ）。在可重复读隔离级别下，通过多版本并发控制（MVCC）+ Next-Key Locking 防止幻影读。默认是行级锁。

主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。

内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。

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

MyISAM

设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。

提供了大量的特性，包括压缩表、空间数据索引等。

不支持事务。

不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入（CONCURRENT INSERT）。

可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作是非常慢的。

比较

事务：InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句。因此MyISAM的性能更好
并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。
外键：InnoDB 支持外键。
备份：InnoDB 支持在线热备份。
崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。
其它特性：MyISAM 支持压缩表和空间数据索引。

MYISAM 不支持外键，表锁，插入数据时，锁定整个表，查表总行数时，不需要全表扫描

INNODB 支持外键，行锁，查表总行数时，全表扫描

补充：

1.InnoDB支持MVCC, 而MyISAM不支持

2.InnoDB支持外键，而MyISAM不支持

3.InnoDB不支持全文索引，支持hash索引，而MyISAM支持全文而不支持hash。
InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。！！！！

4.InnoDB按主键插入，MyISAM按顺序插入

MySQL引擎种类

MyISAM、InnoDB、BDB（BerkeleyDB）、Merge、Memory（Heap）、Example、Federated、
Archive、CSV、Blackhole、MaxDB 等等十几个引擎

索引

3.MySQL索引分类之数据结构

索引就是数据结构
按照数据结构分类：FUll TEXT,HASH，BTREE，RTREE。
MyISAM和InnoDB存储引擎：默认使用BTREE，其他结构的索引，在高一点的版本中会实现，如InnoDB在5.6.4支持全文索引。MEMORY/HEAP存储引擎：支持HASH和BTREE索引。

a. B+Tree 索引

是大多数 MySQL 存储引擎的默认索引类型。

因为不再需要进行全表扫描，只需要对树进行搜索即可，所以查找速度快很多。

因为 B+ Tree 的有序性，所以除了用于查找，还可以用于排序和分组。

可以指定多个列作为索引列，多个索引列共同组成键。

适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找。如果不是按照索引列的顺序进行查找，则无法使用索引。

InnoDB 的 B+Tree 索引分为主索引和辅助索引。主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。这样的操作也被称为回表。

b. 哈希索引

哈希索引能以 O(1) 时间进行查找，但是失去了有序性：

无法用于排序与分组；
只支持精确查找，无法用于部分查找和范围查找。

InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

c. 全文索引

MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。

查找条件使用 MATCH AGAINST，而不是普通的 WHERE。

全文索引使用倒排索引实现，它记录着关键词到其所在文档的映射。

InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。

d. 空间数据索引

MyISAM 存储引擎支持空间数据索引（R-Tree），可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。

4.MySQL索引其他分类方式

主键索引(Primary Key)

数据表的主键列使用的就是主键索引。

一张数据表有只能有一个主键，并且主键不能为null，不能重复。

在mysql的InnoDB的表中，当没有显示的指定表的主键时，InnoDB会自动先检查表中是否有唯一索引的字段，如果有，则选择该字段为默认的主键，否则InnoDB将会自动创建一个6Byte的自增主键。

二级索引(辅助索引)（在MySQL中又或者可以称非主键索引）（也是非聚簇索引）

二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。

唯一索引(Unique Key) ：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为NULL，一张表允许创建多个唯一索引。建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。

普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和NULL。

前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小，因为只取前几个字符。

组合索引：多个列构成的索引。专门用来组合搜索。

5.聚簇索引和非聚簇索引

聚集索引

聚集索引即索引结构和数据一起存放的索引。主键索引是主键作为索引。因此两者本来没有必然联系。但是当聚簇索引所使用的列是主键列时，而事实上InnoDB也是这么做的，因此可以理解InnoDB的主键索引是聚簇索引。

聚集索引的优点

聚集索引的查询速度非常的快，因为整个B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快

聚集索引的缺点

依赖于有序的数据：因为B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或UUID这种又长又难比较的数据，插入或查找的速度肯定比较慢。
更新代价大：如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且况聚集索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的。

非聚集索引

非聚集索引即索引结构和数据分开存放的索引。

MYISAM引擎的表的.MYI文件包含了表的索引，该表的索引(B+树)的每个叶子非叶子节点存储索引，叶子节点存储索引和索引对应数据的指针，指向.MYD文件的数据。

非聚集索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。

非聚集索引的优点

更新代价比聚集索引要小。非聚集索引的更新代价就没有聚集索引那么大了，非聚集索引的叶子节点是不存放数据的

非聚集索引的缺点

跟聚集索引一样，非聚集索引也依赖于有序的数据
可能会二次查询(回表) :这应该是非聚集索引最大的缺点了。当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

二级索引就是非聚集

6.各种索引间的关系

首先标题3按数据结构分类，其次按标题4可以分为主键索引和非主键索引，但是标题4中写的是二级索引，此处稍有争议。因此在标题5中进行阐述，除了按数据结构分，分类比较明确的是聚簇和非聚簇，而对于不同的引擎，索引的实现方式有所不同，因此具体在实现主键索引，唯一索引，普通索引，前缀索引这些索引时，比如InnoDB中的主键索引就是一种聚簇索引，而其他的索引就是非聚簇的。因此下面对比InnoDB和MyISAM的索引。

7.MySQL引擎的索引

MyISAM（非聚集）

使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。
MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。
因此可以看到，任何索引在MyISAM中，其实都是一种非聚集（聚簇）的索引。

InnoDB（聚集索引）

第一个重大区别是InnoDB的数据文件本身就是索引文件，这棵树的叶节点data域保存了完整的数据记录。
但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有）

如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键
如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。（隐含字段）

简单说：
如果我们定义了主键(PRIMARY KEY)，那么InnoDB会选择其作为聚集索引；如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引；否则数据无法形成聚簇的形式。

非聚集索引和聚集索引的区别在于，通过聚集索引可以查到需要查找的数据，而通过非聚集索引可以查到记录对应的主键值，再使用主键的值通过聚集索引查找到需要的数据
也就是说，聚集的非叶子是索引，叶子就是数据。而非聚集的索引单独是一个树，然后里面对应的是真正数据的树的主键，然后通过索引找主键，然后再去找数据。这里的说法不准确，上述说法是真对InnoDB的，对于MyISAM，他的叶子是存储的对应数据的地址。

学习了索引的分类，自然要清楚索引的优缺点

8.索引的优缺点

优点

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

缺点：

当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度
索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

索引最大的好处是提高查询速度，缺点是更新数据时效率低，因为要同时更新索引。对数据进行频繁查询进建立索引，如果要频繁更改数据不建议使用索引。

9.两个引擎适用场景：

MyISAM适合：插入不频繁，查询非常频繁，如果执行大量的SELECT，MyISAM是更好的选择，没有事务。

InnoDB适合：可靠性要求比较高，或者要求事务；表更新和查询都相当的频繁，大量的INSERT或UPDATE

了解索引后，必然要了解索引的使用及相关注意事项

10.几个专有名词的含义

a.回表

注意之前的InnoDB和MyISAM，一个聚簇一个非聚簇。对于非聚簇的MyISAM，叶子节点是数据的地址，因此直接就找过去了，所以对于MyISAM，个人理解是没有回表一说。（两个文件，一个是数据表，一个是索引表）
而对于InnoDB，如果你使用的是普通的索引，那么就还会去主键索引去找一遍，然后通过主键的索引表查到数据。这样就扫描了两遍。（两个文件，一个是主键索引，这个表带数据，一个是普通索引的表，叶子记录主键值）

b.索引覆盖

说白了，就是通过索引直接查到数据，暂时有2个理解。
比如InnoDB的主键索引，我查你的主键就能查到数据，这样就不用回表，也称为索引覆盖。或者你本来就是想查一下主键，那么这样即使是辅助索引，也不用回表，而是查到主键就直接返回了。

c.索引下推

索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询。索引下推在非主键索引上的优化，可以有效减少回表的次数，大大提升了查询的效率。

使用了联合索引（name，age）
　SELECT * from user where  name like '陈%' and age=20

则老版本会忽略age这个字段，直接通过name进行查询，在(name,age)这课树上查找到了两个结果，然后拿着取到的id值一次次的回表查询，因此这个过程需要回表两次。

新版本：InnoDB并没有忽略age这个字段，而是在索引内部就判断了age是否等于20，对于不等于20的记录直接跳过，因此在(name,age)这棵索引树中只匹配到了一个记录，此时拿着这个id去主键索引树中回表查询全部数据，这个过程只需要回表一次。

11.索引什么时候该建立

最左前缀原则

选择合适的字段

a.不为NULL的字段
索引字段的数据应该尽量不为NULL，因为对于数据为NULL的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为NULL，建议使用0,1,true,false这样语义较为清晰的短值或短字符作为替代。

b.被频繁查询的字段
我们创建索引的字段应该是查询操作非常频繁的字段。

c.被作为条件查询的字段
被作为WHERE条件查询的字段，应该被考虑建立索引。

d.被经常频繁用于连接的字段
经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

e.索引尽量要求数据是唯一的，重复的数据列用作索引效率低。

不合适创建索引的字段

a.被频繁更新的字段应该慎重建立索引
虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

b.不被经常查询的字段没有必要建立索引

c.尽可能的考虑建立联合索引而不是单列索引
因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

d.注意避免冗余索引
冗余索引指的是索引的功能相同，能够命中就肯定能命中，那么就是冗余索引如（name,city ）和（name ）这两个索引就是冗余索引，能够命中后者的查询肯定是能够命中前者的在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

使用索引一定能提高查询性能吗?

大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。