mysql索引与sql优化面试题(建议收藏)

概念

说下你对索引的理解

索引是一个排好序的数据结构，它可以帮助我们快速查询/更新/分组/排序/去重数据，索引可以看作是一本新华字典，我们可以根据字典目录，去查找某个字或者偏旁去查找某一类字，但假如没有索引的话，那就相当于我们这本字典没有目录页，如果想要查找某个字需要手动遍历去翻全部的字典页，费时费力。

这个字典目录就相当于索引树中的目录节点，所在的字典内容页就相当于索引树存储真实数据的叶子节点，新华字典就相当于

索引的结构

索引是的结构是b+树，对于主键索引来说目录主要是两部分，索引值 + 叶号，目录叶是辅助检索的。最后一层才是叶子结点，只有叶子节点才会存储数据，非叶子节点只存储键值。叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表，以主键查询为例，第一次磁盘IO会把目录叶会首先被加载到内存中，然后在内存通过二分法定位目标值所在的叶，时间复杂度是log2n，然后第二次磁盘磁盘IO,主键索引B+树图如下

如果是联合索引，假如我们一张表有除了主键id还有a,b,c，对abc三个字段创建联合索引，联合索引结构大体结构如下

叶子节点的存储结构是什么？为何这么设计？

叶子节点是以双向链表的形式存储是因为索引的结构并非稳定的，会随着数据的变化被调整，所以不宜使用连续的磁盘空间，而是采用离散存储的方式，然后通过指针连接形成逻辑连续空间，此外这种结构可以支持范围查询和顺序访问，非常适合数据库的应用

三层B+树能存储多少数据呢？

实际开发中通常B+树的结构是三层，假如目录叶每条数据160bit，叶子结点存储真实数据，每行数据1600bit

三层结构已经可以存储，1000 * 1000 * 100 = 100000000 上亿条数据了，两层b+树也可以存储10万条数据了

B+树这种结构优点是什么

• 减少磁盘IO次数，提升查询速度：综上分析B+树的高度可以相对较低。这样在进行磁盘I/O操作时，能够减少磁盘I/O次数
• 节省内存使用空间，这种设计对内存是非常友好的
• 内存中存在的时间复杂度低
• 叶子节点是以双向链表，如上所述

使用联合索引查询 - 底层原理分析

select * from table where a=13 and b=16 and c=4;

以三层结构的B+树联合索引（abc三个字段）为例，第一次IO会把第一层那一个目录结点加载到内存，在内存中查找的时间复杂度是O(log n)，根据这个目录结点定位到第二层的对应的目录结点，第二次即把第二层的对应的目录结点加载到内存，经过这两次IO就能在第三层精准定位到最终结果所在的叶子结点，总共三次IO，每次IO只会加载一个页到内存，假如我这个查询没有命中任何索引的话，那么实际查找中会用到主键索引，在查到最终结果是在获取到最终结果前主键索引的全部叶子结点都要被加载到内存中，当然这并非是一次性加载的，而是逐步的加载的，为了防止内存被撑爆，这种情况下性能就很差（type=ALL），需要考虑对sql优化

范围查询导致联合索引失效问题底层原理分析

select * from table where a=13 and b > 16 and c=4;

这个地方，字段a和b是有效的，但字段c无效，第一次IO还是把第一层那一个目录结点加载到内存，首先根据a快速定位第二层目录页，我们这里假设a=13的字段在数据库中有5500个，这5500个数据在第二层的目录页上对应着6个连续的目录页，这里比较复杂，我们再分析下上个sql

select * from table where a=13 and b=16 and c=4;

上个sql对应的是b=16这个条件，在已经通过a过滤出的5500条数据，分布在6个连续的目录页，因为a都是相同的，那就意味着在在这5500条数据中b是按序排列的，所以我们b=16我们可以更轻松的过滤数据，假如这里通过(a=13 and b = 16)过滤出500条

回到本sql

本sql查询条件是b > 16,因为此时已经在索引上通过a字段快速过滤出的5500条数据了，在这5500条中b都是有序排列的，b>16我们我依然可以通过索引快速过滤，假如这里通过(a=13 and b > 16)过滤出4000条，但这里的c就不一定是顺序排列的，例如(13, 17,100),(13, 19,1),(13, 20, 180), 这里c分别是100，1，180，非顺序排列的，那这就意味着我们无法通过索引中的目录页去定位最底层数据叶的数据范围了，从高效检索的角度来看索引字段c就是无效的，但c字段在这里就彻底没用了？不一定

如果是这种sql

select a,b,c from table where a=13 and b>16 and c=4;

select的字段也被联合索引覆盖，那这样都不需要再回表了，把通过联合索引中a和b字段过滤出的结果，也就是这4000条数据所在的数据页加载到内存，遍历这4000条数据查找出c=4的最终结果。

不满足最左前缀导致索引失效问题底层原理分析

看懂了上面的，那这个知识点就不攻自破了，紧接上述案例，联合索引（a,b,c）但这里查询条件中b缺失了

select * from table where a=13 and c=4;

首先，a字段在联合索引中是有效的，我们还是根据a字段过滤出5500条数据，但因为查询条件没有b，只剩c了，在这个联合索引上，通过a=13过滤出的这5500条数据中，b是有序的，但c无序，代入上面的案例，(13, 17,100),(13, 19,1),(13, 20, 180)，所以这里索引字段c从高效检索的角度来看就会失效

索引对order by语句的提升分析

# 案例sql，表中只有id,city,name,age四个字段
select city,name,age from t where city = '杭州' order by name limit 1000;

没有加索引的话extra这个字段中的就是Using filesort，表示的就是需要进行文件内排序，mysql会给每个线程分配分配一块内存用于排序，称为sort_buffer。

我们对where查询字段city加索引，索引结构如下

首先利用索引city，定位到where查询结果所在的范围，也就ID.X到ID.(X+N)，对其做遍历，遍历的流程如下

• 取ID.X，通过这个id回表主键索引，取name、city、age 三个 字段的值，存入sort_buffer中;
• 重复上述流程，直到最后一个ID.(X+N)，期间累计回表1000次

上述流程执行完后，sort_buffer中已经有1000条数据了，对这些数据按照字段name做快译排序

这种情况下sql执行效率是很低的，上述案例中sort_buffer中需要对1000条数据做排序，如果是数据量大的情况下，那么内存中放的数据就要分成多个临时文件，排序的性能就会很差

# 优化，把排序字段加到索引中
alter table t add idx_city_name(city,name);

我们首先通过索引idx_city_name中的city从索引idx_city_name中直接取出前1000条主键id，因为这有1000条数据的city都是杭州，所以在此基础上，这1000条数据的name也是按序排列的，那相当于通过这1000条的主键id回表主键索引的数据就已经是排好序的了，这样就避免了文件内排序，流程如下

补充：可以通过对city,name,age这三个字段创建联合索引，避免回表，性能更好

索引的优缺点是什么（同时要点明哪些场景适合使用）

索引可以大大提升我们的数据查询速度

• 减少从磁盘IO次数的角度提升数据查询速度，没有索引，就相当于需要全部的数据都要分批次被加载到内存，而有了索引，且是覆盖索引的情况下，只需要把索引加载到内存即可，那这就意味着能显著减少能磁盘IO次数，减少内存使用，而磁盘IO往往是最耗时的项目,所以减少磁盘IO对提升检索速度意义非凡

• 提升检索/排序/去重/分组速度,数据从磁盘加载到内存后

  • 如果我们查询时用不到索引，那么在数据从磁盘加载到内存后需要用遍历数据，也是On的复杂度，如果检索时能用到内存那么时间复杂度就是log2n，大大提升检索速度，且数据量越大提升效果越明显
  • 涉及到order by/group by的时候，可以避免内排序与临时表，节省cpu和内存资源

节省内存资源和减少服务器CPU开销
通过上面例子可知，如果查询没有命中任何索引的话，那么实际查找中会用到主键索引，在查到最终结果前主键索引的全部叶子结点都要被加载到内存中，当然这并非是一次性加载的，而是逐步的加载的，防止内存被撑爆。可见没有命中任何索引的话内存和cpi开销是相当大的。

此外，union/union all/group by/order by等操作会伴随着文件内排序和临时表，如果连表操作没用命中索引则会面临一个O(n的次方)的时间繁杂度，n的次方数就是连表的张数

索引的代价

• 每创建一个索引意味着mysql底层会创建一颗B+树，树的每一个节点都是一页的空间16k，数据量越大占用的磁盘存储空间开销增大
• 索引是一个已排好序的数据结构，聚簇索引B+树上的节点都是按照索引列的值由大到小形成了双向列表，而没和节点中的数据列也是按有小达到形成了单项列表，但执行删除、插入、更新是，索引内部需要重新维护索引的顺序、这就需要耗费一定的性能了，注意，顺序性是为了配合二分查找快速定位的

适合创建索引的情况

** 字段具有唯一性或需要它是唯一的可以考虑建立唯一性索引**，比如email，唯一性索引增删改时索引维护代价比较小但对性能提升很大

• 频繁被作为where查询条件的字段，包括select、update、delete，因为无论是更新还是删除的前提都是先找到要操作的目标数据，同时，如果更新的是非索引字段那么提效更明显，因为这种情况下并不需要重新维护
• 需要被group by分组和order by排序的索引
• 需要去重的字段，因为索引已经对这个字段排好序了，去重效率会比常规情况高很多
• 多表连接操作，小表作为驱动表，大表是被当做被驱动表，这样有效减少外层循环的次数，为被驱动表的连接条件增加索引，或者将已有的索引(主外键)当做被驱动表的连接条件，可以有效减少内层的循环次数，此外mysql5版本有join buffer，将驱动表缓冲存储到缓冲池中，避免频繁的从驱动表和被驱动表频繁的IO，尽量用join代替子查询，因为子查询执行效率不高①子查询过程中会生成临时表，浪费cpu资源②子查询生成的结果会存在临时表，且临时表是没有索引，查询效率比较低，尤其是子查询返回较大的结果集，查询效率更低

什么情况要避免索引

• 在where查询中用不到的字段就不要建索引了，及时select中用到的字段也不要再建索引了，因因为索引的目的就是大大提高查询速度,group by和order by字段也同理
• 数据量小的、重复度高的字段尽量避免
• 增删改为主的表不要索引
• 不建议对无序的值做索引，比如uuid、md5字段，这样会大大增加索引维护的开销

索引失效场景与对应的优化建议

# 为a,b,c字段创建索引
CREATE INDEX idx_name ON table_name (a,b,c);
# 案例sql1
select * from table where a=1 and c=3;
# 案例sql2
select * from table where a=1 and b>2 and c=3;

• 联合索引一定要遵循最左前缀法则，一个索引最多16个字段，对于多列索引，过滤条件必须按照索引建立的顺序依次满足，过滤条件中一旦跳过了某个字段，那么后续的索引字段均无法被使用，这是B+树结构决定的，如上述案例sql1，参考上面的联合索引结构图，因为联合索引是按照特定的顺序存储的，这里跳过了b字段，c字段就会失去检索价值，只有a字段是理论上能用的上这个索引的，建立联合索引时最好是包含全部的查询字段，也就是覆盖索引，这样查询性能是最好的，如果不能全部包含，那么优先选择过滤性高的字段，且过滤性越高的字段越靠左，即使无法做到全值匹配也要保证满足最左前缀匹配
• 函数、计算、类型转化会导致索引失效，例如函数left本意是从表中一个个取出email字段取前三个做笔记，尽量避免出现运算、用其它方式代替函数，比如left取左前缀用右模糊查询%代替
• 范围查询后面的索引字段也会失效，上面案例已经把原理讲的很清楚了，这里额外要注意的点是创建索引时，要根据实际情况，将精确查询的字段放在联合索引左边，将范围查询的字段放在联合索引索引最右边某些范围查询运算符会导致索引失效，比如!=、not in，那么索引失效，可以使用between来代替他们
• 模糊查询中左模糊和全模糊匹配导致索引失效，可以用右模糊匹配，右模糊匹配相当于使用前缀去索引中匹配，同理，对应char类型的索引我们建立索引不必对其全字段建立索引，可以只对其前半部分建立索引
• 使用where中用OR关系做多条件查询，导致索引失效，个人理解索引结构中是按字段排序的，and条件满足索引是因为通过满足上一个查询条件根据索引定位到分区范围再用下一个条件，此时可以通过union语法层面代替or，union的原理是将or条件划分为2个独立的sql，对拆分后的两个select的查询条件分别建立索引，这样又可以走索引了，但这样做并没有什么意义，因为union就算能走索引，速度依然很慢
• order by 非覆盖索引，mysql优化前考虑回表代价比较大，所以如果仅对排序字段建立索引就mysql就会弃用，所以一定要加复合索引复合筛选字段和排序字段，排序字段在最右边
• 100%出现的查询字段或者同等概率出现的查询字段，区分度越高的字段越靠左放，原理是通过减少索引判断次数提升索引性能

临时表&文件内排序

Using temporary

表示在执行sql查询时会生成一个临时表存储中间结构、mysql有一个控制临时表大小的参数，超过临时表阈值就会在磁盘上生成，常见于group by、union、select子查询中，这种情况可以通过索引解决，方案就是对分组字段建立索引，建立索引后再次执行该命令可以直接通过走索引完成分组，union开发中通过用来代替or，以为or会导致索引失效，从sql语法层面用union代替or，并对union联合查询的查询条件加字段，出现临时表后性能较差，需要避免

Using filesort

首先，索引本身是个排好序的数据结构，建立索引后即相当于根据xxx字段建立起了已排好序的数据结构，那么在执行order by时直接用索引接口高效完成排序，反之，如果没有索引，就会产生文件内排序，filesort底层用的quicksort算法

聚簇索引主键三要素

• 不要选择过长的主键，因为无论是聚簇索引还是非主聚簇索引中都会存储主键值，而且一页空间是固定的大小为16k，如果主键过大，首先会使非叶子索引所能存储主键变少 => 然后导致树的层级变高=>增加IO次数，进而会大大增加查询时间
• 从减少索引后续数据变更的维护成本较低考虑，最好选择单调递增的主键，如果用的是非单调的主键，比如uuid，md5会导致也分裂等问题产生
• 必须是唯一和非空的

in和exists的原理与选择

案例

显示所有有员工的部门信息

exists

select * from dept where exists( select * from employee where deptno=dept.deptno);

exists对外表采用遍历方式逐条查询，每次查询都会比较exists的条件语句，当exists里的条件语句返回记录行时则条件为真。此次返回当前遍历到的记录，反之，如果exists里的条件语句不能返回记录行，则丢弃当前遍历到的记录，执行过程为

• 对dept表逐行遍历
• 子查询会使用dept表被遍历到的当前行的数据作为查询条件
• 如果有查找到数据，那么exists返回true，dept表被遍历到的当前行就可以被打印，反正则不被打印

总结：外查询的表相当于是驱动表，需要全表遍历，内查询的表则可以使用索引去提示查找效率，所以外表小内表大适合用exists

in

select * from dept where dept_id  in ( select dept_id from employee where deptno=dept.deptno);

执行流程

• 先执行子查询，从employee取出 dept_id的结果集deptIdList
• 执行外层查询，查询条件是外表dept的字段dept_id 在结果集deptIdList中，因为dept_id是dept表的主键，所以这里是外查询中dept表是可以通过dept的主键索引来查找的，效率较高，所以适合于外表大内表小的情况

Explain分析字段

ref

显示索引的哪一列被使用了，如果可能的话，最好是一个常数。即ref标识哪些列或常量被用于查找索引列上的值

1、这里表示常数100是用来在主键索引中查询，即查找主键id为100的数据

2、这里是d和e通过连表查询，对于e表来是说，它的外键id被用来在它的外键索引引上查找目标值，由于主键索引是union的，所以只需要1次即可定位;

rows

根据表统计信息和索引使用情况，推算出查找出所需记录需要耗费的时间

extra字段

mysql执行架构

• MySQL 服务层：也就是 SERVER 层，用来解析 SQL 的语法、语义、生成查询计划、接管从 MySQL 存储引擎层上推的数据进行二次过滤等等
• MySQL 存储引擎层：按照 MySQL 服务层下发的请求，通过索引或者全表扫描等方式把数据上传到 MySQL 服务层。
• MySQL 索引扫描：根据指定索引过滤条件（比如 where id = 1) ，遍历索引找到索引键对应的主键值后回表过滤剩余过滤条件。
• MySQL 索引过滤：通过索引扫描并且基于索引进行二次条件过滤后再回表

索引下推(ICP)

把以上索引扫描和索引过滤合并在一起处理，过滤后的记录数据下推到存储引擎后的一种索引优化策略,好处是

• 减少了上传到 MySQL SERVER 层的数据。
• 减少了回表的操作次数。

常见Extra字段

• Using filesort即查询过程中，mysql内部进线排序，没有通过索引，这里mysql底层会用xxx排序算法去做排序，相比如索引辅助排序比较耗费性能
• Using temporary表示由于排序没有走索引、使用union、子查询连接查询(构建出虚拟表的情况)，因此创建了一个内部临时表。注意这里的临时表可能是内存上的临时表，Using temporary比using filesort问题更严重，using filesort只是排序操作，而临时表在内存中创建然后再操作再内部操作分组
• Using index: 查询操作中，待查询的字段和查询条件where都已经被索引覆盖，避免了回表，效率不错。
• 只有Using where
• 如果此时type=ALL 意味着没有用到索引或者索引失效，往往意味着全表扫描
• 如果此时type=ref 意味着对查询条件字段创建了索引，但该条件字段由于是非唯一性字段，无法一锤定音，需要整体遍历去一个个对比是否满足查询条件并返回结果集，默认ICP是开启的，即MySQL 索引扫描的同时伴随索引过滤，过滤完毕后再回表查询，效率会有提升
• Using index和Using where同时出现，代表待查询的目标字段已经被索引覆盖，不需要回表取值，只依靠索引就够了，但查询条件是非索引字段或者不满足最左前缀原则导致查询条件没法用到索引，那么这意味着无法直接通过索引来查询到符合条件的数据，也就是说需要遍历并加载整个索引后，将数据上传到mysql Server层，通过mysql Server层通过where去过滤
• Using index condition:常见于复合索引中，由于左列的索引可能用到了范围查询，那么对于它后面的索引字段就意味着失效，因为此时索引要根据这个范围字段的范围去遍历并读取索引，此时开启了icp，从索引中取出的数据会直接在存储引擎侧过滤，而不是上传到Mysql Server层再去通过where过滤，好处如上介绍
• using intersect;分别对and条件用的条件建立索引，那么查询时会用到这些索引，然后对得到的结果求交集，对where条件做覆盖索引可解决
• using union;分别对or条件前后的字段用的条件建立索引，那么查询时会用到这些索引，然后对得到的结果求并集，对where条件做覆盖索引可解决
• using sort_union;分别对or条件前后的字段用的条件建立索引，那么查询时会用到这些索引，但某个的索引可能是聚合查询，但根据条件查询时可能只用到聚合索引的前部分，也就是没有针对这个条件单独创建索引，mysql优化器只能用其它的符合最左前缀匹配的索引，效率会更低一些
• NULL：表示查询条件有命中索引，但查询的目标字段没有被索引覆盖到需要回表

扩展

Using index condition与 ICP详解

explain select * from employees where salary >10000 and email = "WANG";

不开启icp

• Server 层调用 Engine 查询数据
• Engine 根据联合索引查询salary> 11000 的数据
• 找到每一条满足条件（ salary> 11000 ） 的数据并根据主键 ID 回表，直到找到不符合条件的结果
• 数据返回给 Server 层，Server 层根据条件过滤（email = “WANG”），流程结束

• Server 层调用 Engine 查询数据
• Engine 根据联合索引查询 salary> 11000 的数据，再根据联合索引中已存在的（email = “WANG”）进行过滤，找到符合条件的数据
• 根据找到符合条件的数据，回表查询
• 返回数据给Server层，流程结束

select_type

• simple 简单查询，只有一个简单的select

select email from employees where salary = 11000;

• derive：from表中包含子查询会被标记为derived衍生，mysql会执行这些临时表

select * from (select * from t1 where id = 1);

• primary/subquery子查询，后面的select筛选出的的某个字段的值前一个select的where筛选条件，最外层的select的是primary主查询，外层的select是subquery子查询

explain select email from employees where salary = 11000;

• union：第二个select出现在union后被标记为union，若union包含在from语句的子查询中，外层select会被标记为derived

type执行类型

system

一张表只要一行记录，系统表

constant

常量级，表示通过一次索引就可以找到了，将主键或者unique(唯一)索引作为where语句的查询条件，那么这类sql往往type的类型就是constant

select * from xxxTable where id = 1

eq_ref和ref

唯一性索引扫描，常见于连表查询，连表查询mysql底层会根据最优解来选择驱动表和被驱动表，选择策略参考下文，有三种，这里以索引辅助连表为例，索引辅助连表依然会细分多种策略，驱动表是作为外层的单层循环，通常情况下驱动表的性能级别是all，意味着往往需要全表遍历整改驱动表的聚簇索引做驱动，但存在特殊情况，如果我们目标筛选select相关字段和查询条件where相关字段都被我们创建的复合索引所覆盖，那么此时驱动表的性能级别会是index，即以遍历索引的形式来循环驱动，每次循环的内容即被驱动表通过走索引的形式找到对应的连表匹配值，其中，如果驱动条件走被驱动表的唯一性索引，性能是最佳的，此时被驱动表的性能级别是eq_ref，含义是针对驱动表中的每个连接条件，被驱动表的索引中有且仅有一行数据与之匹配，如果驱动条件走被驱动表的非唯一性普通索引，此时被驱动表的性能级别如果是ref，含义是驱动表中的每个连接条件，被驱动表的索引中有且可能有多行数据与之匹配。mysql内部策略是①判断两表的连表字段，如果一个字段是某个表的主键/唯一键，那么果断选取这个表作为被驱动表②如果该连接字段是两个表的非唯一性字段，那么默认小表驱动大表

eq_ref目前除了连表查询，除了上述连表查询案例情况，其它地方并未见过，使用非唯一性索引字段作为查询条件，案例如下，如果是非唯一性字段作为查询条件，那么type=ref，如果是唯一性字段作为查询条件，那么type=const

range

表示用到了索引的范围查询，扫描部分的索引内容，比全面扫描索引要强

explain select * from  employees where employee_id > 2000;

index

ALL

全表扫描，性能最差，通常是没有用到索引或者索引失效

explain select * from employees where email = "wangzh";

生成环境标准

要求性能要达到range及以上，

possible_keys

可能用到的索引

key_len

索引中使用的字节数

B树和B+数的区别

• B数是一个多叉平衡树，每个叶子节点都会存储数据，Innodb索引一次可读取16k数据，索引每个叶子节点都要尽可能存满16k，一方面提供空间利用率，另一方法是为了减少每次查询中磁盘IO的次数，但B树依然存在很多问题
  • 不支持范围查询，只能通过多次遍历的形式来做范围查询
  • 如果叶子节点上存储的是行数据，也就是…,那么在数据量较大的情况下，磁盘IO次数也会明显变多，影响查询效率

B+数的则是根据这一点做出了改进

• 只在叶子节点上存储数据，并且在叶子节点上使用双向指针链接，这样最底层的叶子形成了一个双向链表
• 非叶子节点存储指针和数值，这样理论上B+树高度要比B树矮，查下效率也更高

myIsam和Innodb的区别

• 存储结构：mysql默认使用Innodb，二者都是B+树结构，Innodb是聚簇索引，即(主键)索引和数据是绑在一起的，对应一个数据文件，myIsam则是数据文件和索引文件分开，索引和数据对应不同的存储文件
• 查询速度对比，Innodb通过主键索引查数据效率比较高，一次即可不需要回表，如果是辅助索引(主键索引之外的索引)，InnoDB的辅助索引只会存储主键值而非磁盘地址，我们为数据表中某一字段建立辅助索引后，按这个字段查询时会

Innodb聚簇索引是基于主键搭建起来的，索引即数据，数据即索引

聚簇索引与非聚簇索引区别

聚簇索引优点

• 单值查询速度上聚簇索引，因为非聚簇索引数据可能出现回表，如果不考虑回表数据类似
• 聚簇索引处理范围查找和排序速度非常快，因为聚簇索引中的叶子节点存储的表数据行都是双向链表结构的，这种结构比较适合做范围查找和排序

聚簇索引限制

• 一张表只能有一个聚簇索引，但可以有多个辅助索引
• 对主键要求是比较严格的，因为聚簇索引本身是基于主键构造的，所以要去主键必须是唯一的，如果我们没有声明主键，那么mysql也会自动选取一个唯一性字段来，如果还没有唯一性字段，mysql也会自动建立一个作为主键索引
• 虽然速度快，但维护困难，比如插入或删除数据时效率低于非聚簇索引，插入或删除数据时聚簇索引必须同步

联合索引

MyIsam与Innodb区别

共同点

• 底层都是B+树

区别

• 速度方面(主键索引和非聚簇索引)：聚簇索引不考虑回表，而使用MyIsam必须要回表，所以速度上聚簇索引快于MyIsam，MyIsam相当于全部都是类似于辅助索引，辅助索引对比：Innodb辅助索引涉及到回表操作速度是慢于MyIsam的回表操作的、因为Innodb辅助索引的回表操作需要去主键索引上去取数据，而MyIsam的回表操作相当于直接从索引上获取的目标的地址值去取数据，Innodb的辅助索引速度慢与它的主键索引，也慢于它的辅助索引
• 事务方面，innodb支持事务，所以计算数据行数的效率低于myisam，myisam因为不支持事务所以会缓存表的行数，而innodb支撑事务，不要的事务对应的表行数无法统计，myisam不支持事务
• 主键与外键： InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败； InnoDB表必须有唯一索引且对主键的要求较为严格，需要同时满足非空、唯一性单调递增、占用存储空间较小的类型比如tinyInt
• 存储方面/数据结构，myisam数据和索引是分开的，实际存储结构方面也是划分为数据、表格、索引三部分文件，innodb数据和索引是存储在一起的，聚簇索引文件是存储在主键索引的叶子节点上的，主键起到索引作用，实际存储中数据和索引是放一起的，用一个文件来存储
• 锁方面：InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁
• 行数查询方面：innodb查询行数需要遍历索引统计节点个数，速度慢，myiasm速度比较快

索引的分类

• 功能逻辑：唯一性索引，主键索引、普通(辅助)索引、全文索引
  • 主键索引即聚簇索引，特点如上，必须是唯一和非空的
  • 普通索引的创建没有任何限制，比较随意，可以对任意的一个或多个查询中高频出现的字段创建相关索引，大大提高查询数据
  • 唯一性索引，使用UNION即可设置为唯一性索引
• 物理实现划分：聚簇索引与非聚簇索引
• 作用字段个数：单列索引、辅助索引

创建索引注意点

• 尽量选择占用存储空间更小的类型来做索引，比如尽量选择TYPEINIT来代替BIGINIT
• 创建索引时尽量不要用完整的char类型字段，而是用字符前缀去完成
• 尽量对重复次数少的字段建立索引，如果是联合索引，那么重复性低的字段适合放到最左侧
• 如果查询语句涉及到多个字段，最后针对这些创建联合作用，避免回表影响效率
• 在where查询中用不到的字段就不要建索引了，及时select中用到的字段也不要再建索引了，但如果where语句涉及到了多个查询字段，那么我们遵循原则全值匹配我最爱，不能做到全值匹配，那么至少要满足最左边的索引最左前缀匹配

sql优化

子查询用法总结

• sc中多类数据耦合/比如sc成绩表中，一个sid平均出现3次，代表一个学生就大约有3门成绩的记录，对应三条数据，如果有一个需求是查询语文成绩大约数学成绩的同学，但我们需要把每名学生的sid、mathScore、chinaScore罗列出来，并在where中使用条件mathScore，chinaScore

select * from
(select sid,score from sc where cid = 1) china,
(select sid,score from sc where cid = 2) math where china.sid = math.sid and china.score > math.score;

• 需要横跨三个有关联的表中取数据，本来需要做三表连接，但可以分别两两相连

-- explain select sid,
-- (select cname from course c where c.cid = sc.cid) as cname,
-- (select sname  from student s where s.sid = sc.sid) as sname, 
--  score from sc;

• 需要查的数据表中有普通字段还有聚合字段

方案1

连表查询注意点

• 方法一：粗暴双重循环法，通常用两个非索引建进线连接
• 方法二：缓存辅助双重循环法，通常用两个非索引建进线连接
• 方法三：索引辅助完成连表
  • 用两个唯一性主键做连接，默认小表做驱动表，驱动大表
    
  • 开发中更常见是主外键连接查询，这种情况下并不是 单纯按大小划分驱动表，而是以外键所在表e做为外层循环，每次循环会从外键值去主键表d所对应的主键索引取出唯一对应值，这样效率是最高的，反之，如果是主键所在表做外层循环，每次循环会从外键表d所对应的外键索引取对应值，因为外键索引可能是非唯一性，可能对应范围查找，最重要的是还需要汇报。性能不一定会更好。
    
    唯一性索引扫描，常见于连表查询，连表查询mysql底层会根据最优解来选择驱动表和被驱动表，选择策略参考下文，有三种，这里以索引辅助连表为例，索引辅助连表依然会细分多种策略，驱动表是作为外层的单层循环，通常情况下驱动表的性能级别是all，意味着往往需要全表遍历整改驱动表的聚簇索引做驱动，但存在特殊情况，如果我们目标筛选select相关字段和查询条件where相关字段都被我们创建的复合索引所覆盖，那么此时驱动表的性能级别会是index，即以遍历索引的形式来循环驱动，每次循环的内容即被驱动表通过走索引的形式找到对应的连表匹配值，其中，如果驱动条件走被驱动表的唯一性索引，性能是最佳的，此时被驱动表的性能级别是eq_ref，含义是针对驱动表中的每个连接条件，被驱动表的索引中有且仅有一行数据与之匹配，如果驱动条件走被驱动表的非唯一性普通索引，此时被驱动表的性能级别如果是ref，含义是驱动表中的每个连接条件，被驱动表的索引中有且可能有多行数据与之匹配。mysql内部策略是①判断两表的连表字段，如果一个字段是某个表的主键/唯一键，那么果断选取这个表作为被驱动表②如果该连接字段是两个表的非唯一性字段，那么默认小表驱动大表

实测

字段个数&字段大小对速度的影响

SELECT black_list_value from tb_black_list;

SELECT black_list_name from tb_black_list;

这里虽然都是分别只提取一个字段，但是black_list_value是一个json，很大的字段，速度已经趋近于select * 了，而black_list_name只是一个普通小字段，仅仅select该字段速度远小于select大字段，目前来看应该是速度差在网络传输上，需要用自己计算机0网络传输的情况下再试一试

limit原理

原理

# 案例sql
SELECT  * from tb_template limit m, n;

以上述sql为例，这里没有命中其它索引，所以实际使用的是主键索引，这里是直接从主键索引上取出前m+n条数据，然后丢弃掉前m条数据，只保留最后的n条。

验证过程

测试使用的tb_template共60000条数据

# sql1
SELECT  * from tb_template ;

耗时1秒左右

# sql2
SELECT  * from tb_template limit 0, 10;

耗时0.02s左右

# sql3
SELECT  * from tb_template limit 59991, 10;

耗时0.09s左右

# sql4
SELECT  * from tb_template where id >= 59901;

耗时0.02s左右

只看返回值结果的话，sql3和sql4是等价的，但执行速度却差距很大，他们的差距在于底层原理，sql3需要从主键索引上取出前m+n条数据，然后丢弃掉前m条数据，只保留最后的n条，sql4则是可以通过主键索引的目录页快速定位到id >= 59901的叶子节点，只取这部分数据，所以执行速度自然是比sql3快很大

所以sql3为何不能通过主键索引的目录页快速定位到id >= 59901的叶子节点呢？因为主键id不一定是顺序的，虽然实际开发中主键id通常是顺序自增的，但如果这个表存在删除操作，那么主键id就不是连续的，mysql只能是一点点的加载叶子节点到内存，取出叶子节点的数据，计数，然后把这部分数据从内存中丢弃，直到第59991，此时前面加载的59990条数据已经全部丢弃了，从59991开始读取10条数据，即为最终结果

SQL命令执行顺序

• from t1：针对表t1生成虚拟表 vt1

  • from后面除了根特定的表名外，也完全可以把子查询语句返回的结果集作为虚拟表，多个子查询的表会得到按笛卡尔积获取到的表，并且可以通过where根据两表主外键或其他连接字段的条件来执行

• right/full/left/inner join t2：确定要进行连接的表t2,选择连表策略与拼接策略

• on t1.column = t2.colum：做连表操作，连表方案(三种连接方法，索引辅助连表又分两份)参考下文，以左连接为例，会把筛选后满足条件的数据筛选出，右边做条件过滤后及时没有和左表对应的，也会用null值填充后和左表数据拼接，总之，以左边全部的数据作为基准表，右边符合条件的作拼接

• where：

  • 应用where筛选器后跟字段作为筛选条件，对上一步生产的虚拟表引用where筛选器，生成虚拟表vt4
  • 如果where用到了范围查询in，例如 select * from A where A.x in (select x from B where y>0 );，对于in来说是先执行子查询即通过B表的字段y大于0来从B表中做筛选生成虚拟表t1，在从A表查询出满足条件A.x位于虚拟表t1中的数据
  • 如果where用到了范围查询exists，例如 select * from A where exists (select * from B where y = A.x );，对于exists来说是先一行行的遍历表A，每遍历一行表A的数据就会把遍历到的那行数据的A.x字段传给子查询中的A.x然后执行子查询当前行有结果返回即为true，为true则外表的当前行即进行打印，否则为false，不打印

• group by ：子句将中的唯一的值组合成为一组，得到虚拟表vt5。group by的特殊点在于它会以排序字段作为基准，排序字段可以不止一个，(group by a,b) a和b中存在非唯一性字段那么就会有一个重复,vt5的类似大致如下，生成的虚拟表其实是不符合关系型表达式的，如果没有相关的索引可用，就会生成临时表来做存储中间结果，对性能影响较大
  

  • 排序字段如果是非唯一性的，那无论重复出现多少次都会被整合为一行，而其他行的字段都会被压缩为一个字段，基本只有该字段可以被用来做直接筛选了，其它字段只能在聚合后被查询
  • 排序字段如果是唯一性的，比如根据主键排序，那么就不会出现字段合并现象，可以在任意字段查询的基础上满足聚合查询的要求

  被合并的行这是通过聚合sum/avg/count查询，例如查询满足xxx条件的字段的占比(统计总数/总行数）,保留前两位

round(sum(if((end_time-start_time-b.duration) >=0,1,0) / count(*),2);

多条件判断，类似于java中的switch-case
round((sum(case when if_follow = 1 then 1 when if_follow = 2 then -1 else 0 end) / count((uvl.uid))),3) fans_growth_rat

from数据源

from 
tb_user_video_log uvl join tb_video_info vi
on uvl.video_id = vi.video_id
group by vi.author,month

根据年份/月份这种时间做分组，如果求单个月份的累计数目
  sum(case when if_follow = 1 then 1 when if_follow = 2 then -1 else 0 end) total_fans

根据年份/月份这种时间做分组，如果求当月截止到目前为止的数目
  sum(sum(case when if_follow=1 then 1
             when if_follow=2 then -1
             else 0 end)) over(partition by author order by date_format(start_time,'%Y-%m')) total_fans

• avg/sum/count：执行聚合函数，为vt5生成超组，生成vt6
• having筛选器，生成vt7。having筛选器是第一个也是为唯一一个应用到已分组数据的筛选器
• select：处理select子句。将vt7中的在select中出现的列筛选出来。生成vt8
  • select筛选的字段可以通过子查询的方式返回，子查询中的筛选条件用到外层查询大约是表vt1中的字段作为查询条件，筛选出的数据必须是和外表vt1一一对应的。不能出现多对1的关系
• distinct：应用distinct子句，对vt8进行去重，生成vt9。
• order by：应用子句。按照order_by_condition排序vt9，此时返回的一个游标，而不是虚拟表
• limit：应用limit选项。生成vt10返回结果给请求者即用户