sql优化和索引常见的面试题

sql优化和索引常见的面试题

什么是索引？

索引就像是图书的目录，根据目录中的页码快速找到所需内容，帮助我们快速检索数据库中的数据。
索引储存在文件系统中。
索引的文件存储形式和数据库引擎有关。

索引文件的结构：

hash
B+树

数据库查询过程

1.sql由客户端发送到MySQL Service。
2.进行权限校验，备份判断。
3.解析语法，进行优化。
4.到缓存中找，找到直接返回，没找到就进入下一阶段。
5.服务器进行SQL解析，预处理，再由优化器生成执行计划。
6.根据执行计划，调用存储引擎来执行查询。
7.将结果返回给客户端，同时缓存查询结果。

缓存组件
查询缓存：SQL语句的缓存，把经常使用的SQL缓存起来。
数据缓存：从磁盘中加载数据到缓存中，然后对数据进行过滤。
日志缓存：通过日志恢复记录。**

什么是Explain？

Explain被称为执行计划，在语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，模拟MySQL优化器来执行SQL语句，执行查询时，会返回执行计划的信息，并不执行这条SQL。

Explain可以用来分析SQL语句和表结构的性能瓶颈。通过explain的结果，可以了解到如数据表的查询顺序、数据查询操作的操作类型、哪些索引可以被命中、哪些索引实际会命中、每个数据表有多少行记录被查询等信息。

mysql索引的数据结构类型：

1.平衡二叉树:

2.B树：

3.B+树：

4.Hash索引：

索引类型有哪些？

主键索引：是一种特殊的唯一索引，不允许有空值。
唯一索引：索引列中的值必须是唯一的，但是允许为空值。
普通索引：给表中某一个字段添加索引。
组合索引：给表中多个字段添加索引，主要针对多条件查询。
全文索引：只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引。

那么索引具体采用的哪种数据结构呢?

常见的MySQL主要有两种结构：Hash索引和B+ Tree索引，我们使用的是InnoDB引擎，默认的是B+树。（二叉树如果深度太深，会造成IO次数变多，影响读取的速度）

既然你提到InnoDB使用的B+ 树的索引模型，那么你知道为什么采用B+ 树吗？这和Hash索引比较起来有什么优缺点吗？

因为Hash索引底层的数据结构是hash表，hash表是以key-value键值对的结构存值，所以多个数据在存储关系上是完全没有任何顺序关系的。而B+ 树是一种多路平衡查询树，所以他的节点是天然有序的。

区别就是：

hash索引适合等值查询，不适合范围查询。
hash索引没办法利用索引完成排序。
如果有大量重复键值的情况下，hash索引查询的速度会很低，因为存在hash碰撞的问题。

什么是储存引擎？

根据不同的储存方式对数据进行磁盘的储存。

常用的储存引擎有哪些？有什么区别？

MySQL中常用的四种存储引擎分别是：

① MyISAM存储引擎
② innoDB存储引擎
③ MEMORY存储引擎
④ ARCHIVE存储引擎。

区别

Innodb（OA系统）:

支持事务，外键。
磁盘的存储文件只有一个，需要的内存更大。
支持表和行级的锁。

MylSAM（适用于查询较多的系统）:

不支持事务和外键。
磁盘存储文件有三个，可被压缩，储存空间小。
只支持表锁。

MySQL优化专题

硬件层面

提供更大的内存，减少磁盘的IO。
采用多核CPU，提高MySQL执行速度。
采用固态硬盘。

软件层面

选择合适的存储引擎
采用一个高效的MySQL逻辑架构

建表

数据类型选择的原则：

1.更小的通常更好

2.简单

3.尽量避免null

一、sql索引的基础

1.接口在1s内，很多高性能的保持在200ms

2.在无法使用索引的时候会进行IO读写，进行全表扫描造成较大的性能开销

3.查索引的过程： 先查索引（索引结构B+树），不用进行全表扫描——>索引没有会进行全表扫描

4.索引分类：

①主键索引：主键自带索引效果
②普通索引：为普通列创建索引
③唯一索引：就像唯一列，列中的数据是唯一的，比普通索引性能要好（前提：列中数据唯一）。
④联合索引（组合索引）：一次性为表中多个字段创建索引，联合索引不建议超过五个。
⑤全文索引：MyISAM存储引擎支持全文索引，一般不会用MyISAM，经常用Es来做。

二、索引使用的数据结构

​ 1.线性表 ：

• 链表底层是采用数组，通过索引下标快速查找，增删快。（时间复杂度高）

		1.1线性顺序表

​		1.2线性链式表

​		1.3单向链表：一个头节点指向下一个节点，有一个数据域有一个指针域，指针域指向下一个节点。 例如：1——>2

​		1.4双向链表：一个头节点指向下一个节点，有一个数据域两个指针域。 例如：1<——>2

​		

​

​ 2.栈：先进后出

​ 3.队列：先进先出

​ 4.串：

String定长串，StringBuffer ，StringBudlier

​ 5.广义表：

更灵活的多维数组

​ 6.树：

​ 6.1：多叉树，

非二叉树

​ 6.2：二叉树：

一个节点最多只能有两个子节点，节点数值必须是连在一起的。

​ 6.3：二叉查找树：

查询性能和高度有关，与插入数据的顺序也有关，二叉查找对根子节点数值要比左子节点的数值要大，比右子节点数小

​ 6.4：平衡二叉树：

左子树和右子树深度差不超过1，通过自旋进行平衡（左右旋）

​ 6.5：红黑树：

红黑树在查询性能上做了优化，索引没有采用它，因为它一层存放的数据是有限的，数据量大，树的深度就越大，性能越差

​ 6.6：B树：

允许一个节点存放多个数据，但B树一个节点存储多个数据是由树的深度决定，B树一个节点存放16KB，最多存放15个数据

​ **6.7：B+树：

①非叶子节点冗余了叶子节点中的键。
②叶子节点是从小到大，从左到右排列。
③叶子节点之间提供了指针，提高了区间的访问性能。
④只有叶子节点存放数据，非叶子节点不放数据放键。
⑤B+ 树中各个页之间是通过双向链表连接的，叶子节点中的数据是通过单向链

​ 6.8：哈希表： 使用他查询最快，但是不支持区间访问

1. MySQL的优化

1.添加索引
2.优化SQL语句
3.尽量不要使用子查询，用小表驱动大表。
4.结构的优化 ：读写分离，主从复制，Mycat

sql执行的顺序：
from > on > where > group by > having > select > distinct > order by > limit

2.分库分表

1.水平拆分 (比如说数据特别多的时候偶,可以按照时间戳,按照一定的时间划分一次)
2.垂直拆分 (当一个表中 字段过多时,把经常用的经常不用的字段进行垂直拆封)

3.分库的规则：

1.后四位 % 库的数量
2.（后四位 / 库的数量）% 表的数量
3.为什么要用这种算法分库分表，有什么好处？
答：当数据库需要扩容的时候，将数据库按照两倍进行扩容，这样结合算法就能够让挪动的数据非常的少。

4. 在mybatis中#和$的主要区别是：

传入的参数在SQL中显示为字符串，#方式能够很大程度防止sql注入；
$传入的参数在SqL中直接显示为传入的值，
$方式无法防止Sql注入。

Explain的sql优化字段解析

Explain 的每个字段

几个重要字段 为：id、type、key、rows、Extra

一、id(必记住)

id列的编号就是select 的序列号，也可以理解为SQL执行顺序的标识，有几个select 就有几个id。

• id值不同：只查询，id的序号会递增，id值越大优先级越高，优先执行
• id值相同：从上向下执行：
• id值为null ： 表示是一个结果集，不需要使用它来查询。

二、select_type

查询的类型，主要是用于区分普通查询、联合查询、子查询等复杂的查询

• SIMPLE：简单的select查询，查询中不包含子查询或者union
• PRIMARY：查询中包含任何复杂的子部分，最外层查询则被标记为primary （且只有一个）
• SUBQUERY：在select 或 where列表中包含了子查询
• DERIVED：在from列表中包含的子查询被标记为derived（衍生），mysql或递归执行这些子查询，把结果放在零时表里
• UNION：若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived
• UNION RESULT：从union表获取结果的select （没有参与查询，id值此时为null）

三、table

表示explain 的一行正在访问哪个表。

• 如果查询使用啦别名。那么这里显示的是别名
• 如果不涉及对数据库表的操作。那么显示null
• 如果显示为尖括号控起来 就表示这是个临时表。后面的N 就是执行计划中的id。表示结果来自于这个查询产生
• 如果是尖括号 括起来的<union M,N> 与之类似 ，这也是一个临时表，表示这个结果来自与union查询的id为M,N的结果集

四、type（必记住）

type显示查询使用了何种查询范围，主要有以下几种，从最坏到最好依次是：

ALL < index < range ~ index_merge < ref < eq_ref < const < system

ALL，说明效率是很差，需要优化了。

• system: 表中只有一条数据， 这个类型是特殊的 const 类型。
• const: 针对主键或唯一索引的等值查询扫描，表示通过索引一次就找到了，最多只返回一行数据。
  const 查询速度非常快， 因为它仅仅读取一次即可。例如下面的这个查询，它使用了主键索引，因此 type 就是 const 类型的： explain select * from user_info where id = 2；
• eq_ref: 唯一性索引扫描此类型通常出现在多表的 join 查询，表示对于前表的每一个结果，都只能匹配到后表的一行结果。并且查询的比较操作通常是 =，查询效率较高。例如：explain select * from user_info, order_info where user_info.id = order_info.user_id;
• ref: 此类型通常出现在多表的 join 查询，针对于非唯一或非主键索引，或者是使用了 最左前缀 规则索引的查询。例如下面这个例子中， 就使用到了 ref 类型的查询：explain select * from user_info, order_info where user_info.id = order_info.user_id AND order_info.user_id = 5
• range: 表示使用索引范围查询，通过索引字段范围获取表中部分数据记录。这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中。例如下面的例子就是一个范围查询：explain select * from user_info where id between 2 and 8；
• index: 表示全索引扫描(full index scan)，和 ALL 类型类似，只不过 ALL 类型是全表扫描，而 index 类型则仅仅扫描所有的索引， 而不扫描数据。index 类型通常出现在：所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据。当是这种情况时，Extra 字段 会显示 Using index。
• ALL: 表示全表扫描，这个类型的查询是性能最差的查询之一。通常来说， 我们的查询不应该出现 ALL 类型的查询，因为这样的查询在数据量大的情况下，对数据库的性能是巨大的灾难。 如一个查询是 ALL 类型查询， 那么一般来说可以对相应的字段添加索引来避免。
  NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引

一般来说，好的sql查询至少达到range级别，最好能达到ref

五、possible_keys

它表示 mysql 在查询时，可能使用到的索引。 注意，即使有些索引在 possible_keys 中出现，但是并不表示此索引会真正地被 mysql 使用到。 mysql 在查询时具体使用了哪些索引，由 key 字段决定。

六、key

显示查询实际使用哪个索引来优化对该表的访问；
select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。

七、key_len

用于处理查询的索引长度，表示索引中使用的字节数。通过这个值，可以得出一个多列索引里实际使用了哪一部分。

注：key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。另外，key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。

八、ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值 ,
如果可能，是一个常量const。

九、rows（记）

表示MySQL根据表统计信息及索引选用情况，大致估算的找到所需的目标记录所需要读取的行数，不是精确值。这个值非常直观的显示 sql 效率好坏， 原则上 rows 越少越好

十、filtered

当你的索引统计直接获取了需要的所有数据时，就会显示100

因此一个比较低filtered值表示需要有一个更好的索引，假如type=all，表示以全表扫描的方式得到1000条记录，且filtered=0.1%，表示只有1条记录是符合搜索条件的。

十一、extra（记）

Using filesort

MySQL有两种方式可以生成有序的结果，通过排序操作或者使用索引，当Extra中出现了Using filesort 说明MySQL使用了后者，但注意虽然叫filesort但并不是说明就是用了文件来进行排序，只要可能排序都是在内存里完成的。大部分情况下利用索引排序更快，所以一般这时也要考虑优化查询了。使用文件完成排序操作，这是可能是ordery by，group by语句的结果，这可能是一个CPU密集型的过程，可以通过选择合适的索引来改进性能，用索引来为查询结果排序。

Using temporary

用临时表保存中间结果，常用于GROUP BY 和 ORDER BY操作中，一般看到它说明查询需要优化了，就算避免不了临时表的使用也要尽量避免硬盘临时表的使用。

Not exists

MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行， 就不再搜索了。

Using index

说明查询是覆盖了索引的，不需要读取数据文件，从索引树（索引文件）中即可获得信息。如果同时出现using where，表明索引被用来执行索引键值的查找，没有using where，表明索引用来读取数据而非执行查找动作。这是MySQL服务层完成的，但无需再回表查询记录。

Using index condition

这是MySQL 5.6出来的新特性，叫做“索引条件推送”。简单说一点就是MySQL原来在索引上是不能执行如like这样的操作的，但是现在可以了，这样减少了不必要的IO操作，但是只能用在二级索引上。

Using where

使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。注意：Extra列出现Using where表示MySQL服务器将存储引擎返回服务层以后再应用WHERE条件过滤。

Using join buffer

使用了连接缓存：Block Nested Loop，连接算法是块嵌套循环连接;Batched Key Access，连接算法是批量索引连接

impossible where

where子句的值总是false，不能用来获取任何元组

select tables optimized away

在没有GROUP BY子句的情况下，基于索引优化MIN/MAX操作，或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

distinct

优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

MySQL执行计划（explain）的局限性

（1）EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况；

（2）EXPLAIN不考虑各种Cache；

（3）EXPLAIN不能显示MySQL在执行查询时所作的优化工作；

（4）部分统计信息是估算的，并非精确值；

（5）EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看。

19种最有效的sql优化技巧

1、EXPLAIN

做MySQL优化，我们要善用EXPLAIN查看SQL执行计划。

下面来个简单的示例，标注（1、2、3、4、5）我们要重点关注的数据：

MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。再例如：select id from t where num in(1,2,3)对于连续的数值，能用between就不要用in了；再或者使用连接来替换。

3、SELECT语句务必指明字段名称

SELECT*增加很多不必要的消耗（CPU、IO、内存、网络带宽）；增加了使用覆盖索引的可能性；当表结构发生改变时，前断也需要更新。所以要求直接在select后面接上字段名。

4、当只需要一条数据的时候，使用limit 1

这是为了使EXPLAIN中type列达到const类型

5、如果排序字段没有用到索引，就尽量少排序

6、如果限制条件中其他字段没有索引，尽量少用or

or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。很多时候使用union all或者是union（必要的时候）的方式来代替“or”会得到更好的效果。

7、尽量用union all代替union

union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据。

8、不使用ORDER BY RAND()

select id from dynamic order by rand() limit 1000;

-- 上面的SQL语句，可优化为：
select id from dynamic t1 join (select rand() * (select max(id) from dynamic) as nid) t2 on t1.id > t2.nidlimit 1000;

9、区分in和exists、not in和not exists

select * from 表A where id in (select id from 表B)

-- 上面SQL语句相当于

select * from 表A where exists(select * from 表B where 表B.id=表A.id)

-- 区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行[子查询](https://so.csdn.net/so/search?q=子查询&spm=1001.2101.3001.7020)。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

-- 关于not in和not exists，推荐使用not exists，不仅仅是效率问题，not in可能存在逻辑问题。如何高效的写出一个替代not exists的SQL语句？


-- 原SQL语句：

select colname … from A表 where a.id not in (select b.id from B表)

-- 高效的SQL语句：

select colname … from A表 Left join B表 on where a.id = b.id where b.id is null

取出的结果集如下图表示，A表不在B表中的数据：

select id,name from product limit 866613, 20
-- 使用上述SQL语句做分页的时候，可能有人会发现，随着表数据量的增加，直接使用limit分页查询会越来越慢。
-- 优化的方法如下：可以取前一页的最大行数的id，然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是866612。SQL可以采用如下的写法：
select id,name from product where id> 866612 limit 20

11、分段查询

在一些用户选择页面中，可能一些用户选择的时间范围过大，造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序，分段进行查询，循环遍历，将结果合并处理进行展示。

如下图这个SQL语句，扫描的行数成百万级以上的时候就可以使用分段查询：

对于null的判断会导致引擎放弃使用索引而进行全表扫描。

13、不建议使用%前缀模糊查询

例如LIKE“%name”或者LIKE“%name%”，这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”。

那如何查询%name%？

如下图所示，虽然给secret字段添加了索引，但在explain结果并没有使用：

那么如何解决这个问题呢，答案：使用全文索引。

在我们查询中经常会用到select id,fnum,fdst from dynamic_201606 where user_name like %zhangsan%;。这样的语句，普通索引是无法满足查询需求的。庆幸的是在MySQL中，有全文索引来帮助我们。

-- 创建全文索引的SQL语法是：
ALTER TABLE `dynamic_201606` ADD FULLTEXT INDEX `idx_user_name` (`user_name`);
-- 使用全文索引的SQL语句是：
select id,fnum,fdst from dynamic_201606 where match(user_name) against(zhangsan in boolean mode);

注意 ：在需要创建全文索引之前，请联系DBA确定能否创建。同时需要注意的是查询语句的写法与普通索引的区别。

14、避免在where子句中对字段进行表达式操作或者where查询范围过大，导致索引失效

where 查询范围过大 导致MySQL 最优选择全表扫描了导致索引失效， 并不是Select * 的问题

-- 比如：
select user_id,user_project from user_base where age*2=36;

-- 中对字段就行了算术运算，这会造成引擎放弃使用索引，建议改成：
select user_id,user_project from user_base where age=36/2;

15、避免隐式类型转换

-- where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换，建议先确定where中的参数类型。

16、对于联合索引来说，要遵守最左前缀法则

-- 举列来说索引含有字段id、name、school，可以直接用id字段，也可以id、name这样的顺序，但是name;school都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序，常用的查询字段放在最前面。

17、必要时可以使用force index来强制查询走某个索引

-- 有的时候MySQL优化器采取它认为合适的索引来检索SQL语句，但是可能它所采用的索引并不是我们想要的。这时就可以采用forceindex来强制优化器使用我们制定的索引。

18、注意范围查询语句

-- 对于联合索引来说，如果存在范围查询，比如between、>、<等条件时，会造成后面的索引字段失效。

19、关于JOIN优化

LEFT JOIN A表为驱动表，INNER JOIN MySQL会自动找出那个数据少的表作用驱动表，RIGHT JOIN B表为驱动表。

注意：

1）MySQL中没有full join，可以用以下方式来解决：

select * from A left join B on B.name = A.namewhere B.name is nullunion allselect * from B;

2）尽量使用inner join，避免left join：

参与联合查询的表至少为2张表，一般都存在大小之分。如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表。

3）合理利用索引：

被驱动表的索引字段作为on的限制字段。

4）利用小表去驱动大表：

从原理图能够直观的看出如果能够减少驱动表的话，减少嵌套循环中的循环次数，以减少 IO总量及CPU运算的次数。

5）巧用STRAIGHT_JOIN：

inner join是由MySQL选择驱动表，但是有些特殊情况需要选择另个表作为驱动表，比如有group by、order by等 「Using filesort」、「Using temporary」时。STRAIGHT_JOIN来强制连接顺序，在STRAIGHT_JOIN左边的表名就是驱动表，右边则是被驱动表。在使用STRAIGHT_JOIN有个前提条件是该查询是内连接，也就是inner join。其他链接不推荐使用STRAIGHT_JOIN，否则可能造成查询结果不准确。

这个方式有时能减少3倍的时间。