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索引的数据结构类型:
索引类型有哪些?
主键索引:是一种特殊的唯一索引,不允许有空值。
唯一索引:索引列中的值必须是唯一的,但是允许为空值。
普通索引:给表中某一个字段添加索引。
组合索引:给表中多个字段添加索引,主要针对多条件查询。
全文索引:只有在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倍的时间。