首页 > 数据库 >【面试突击】数据库面试实战-SQL 优化（加更）

【面试突击】数据库面试实战-SQL 优化（加更）

时间：2024-02-05 17:31:42浏览次数：28  
标签：count name 索引 数据库 面试 SQL position age employees

欢迎关注公众号【11来了】，及时收到 AI 前沿项目工具及新技术的推送！

在我后台回复 「资料」 可领取编程高频电子书！

在我后台回复「面试」可领取硬核面试笔记！


MySQL 中的 SQL 优化

这里主要说一下 MySQL 中如何对 SQL 进行优化，其实主要还是根据索引来进行优化的，如果好好了解下边的 SQL 优化，可以对 MySQL 的理解更加深入接下来的 SQL 优化，以下边这个 employees 表为例进行优化：

CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

‐‐ 插入一些示例数据
drop procedure if exists insert_emp;
delimiter ;;
create procedure insert_emp()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into employees(name,age,position) values(CONCAT('zqy',i),i,'dev');
set i=i+1;
end while;
end;;
delimiter ;
call insert_emp();

order by、group by 优化

下边是 8 种使用 order by 的情况，我们通过分析以下案例，可以判断出如何使用 order by 和 where 进行配合可以走using index condition（索引排序）而不是 using filesort（文件排序）


  • case1
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' and position = 'dev' order by age;

【面试突击】数据库面试实战-SQL 优化（加更）_sql

分析：查询用到了 name 索引，从 key_len=74 也能看出，age 索引列用在排序过程中，因此 Extra 字段里没有 using filesort


  • case2
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' order by position;

【面试突击】数据库面试实战-SQL 优化（加更）_主键_02


分析：从 explain 执行结果来看，key_len = 74，查询使用了 name 索引，由于用了 position 进行排序，跳过了 age，出现了 Using filesort



  • case3
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' order by age, position;

【面试突击】数据库面试实战-SQL 优化（加更）_主键_03

分析：查找只用到索引name，age和position用于排序，与联合索引顺序一致，因此无 using filesort。



  • case4
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' order by position, age;

【面试突击】数据库面试实战-SQL 优化（加更）_sql_04

分析：因为索引的创建顺序为 name,age,position，但是排序的时候 age 和 position 颠倒位置了，和索引创建顺序不一致，因此出现了 using filesort



  • case5
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' and age = 18 order by position, age;

【面试突击】数据库面试实战-SQL 优化（加更）_sql_05

分析：与 case 4 相比，Extra 中并未出现 using filesort，并且查询使用索引 name，age，排序先根据 position 索引排序，索引使用顺序与联合索引顺序一致，因此使用了索引排序



  • case6
EXPLAIN SELECT * FROM employees WHERE name = 'zqy' order by age asc, position desc;

分析：虽然排序字段列与联合索引顺序一样，但是这里的 position desc 变成了降序排序，导致与联合索引的排序方式不同，因此产生了 using filesort




  • case7
EXPLAIN SELECT * FROM employees WHERE name in ('LiLei', 'zqy') order by age, position;

【面试突击】数据库面试实战-SQL 优化（加更）_主键_06

分析：先使用索引 name 拿到 LiLei，zqy 的数据，之后需要根据 age、position 排序，但是根据 name 所拿到的数据对于 age、position 两个字段来说是无序的，所以需要使用到 filesort。

为什么根据 name in 拿到的数据对于 age、position 来说是无序的：

对于下图来说，如果取出 name in (Bill, LiLei) 的数据，那么对于 age、position 字段显然不是有序的，因此肯定无法使用索引扫描排序


【面试突击】数据库面试实战-SQL 优化（加更）_sql_07



  • case8
EXPLAIN SELECT * FROM employees WHERE name > 'a' order by name;

【面试突击】数据库面试实战-SQL 优化（加更）_主键_08

分析：对于上边这条 sql 来说，是 select * 因此 mysql 判断不走索引，直接全表扫描更快，因此出现了 using filesort

EXPLAIN SELECT name FROM employees WHERE name > 'a' order by name;

【面试突击】数据库面试实战-SQL 优化（加更）_主键_09

分析：因此可以使用覆盖索引来优化，只通过索引查询就可以查出我们需要的数据，不需要回表，通过覆盖索引优化，因此没有出现 using filesort



优化总结

  1. MySQL支持两种方式的排序 filesort 和 index，Using index 是指 MySQL 扫描索引本身完成排序。index 效率高，filesort 效率低。
  2. order by满足两种情况会使用Using index。
  • order by语句使用索引最左前列。
  • 使用where子句与order by子句条件列组合满足索引最左前列。
  1. 尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
  2. 如果order by的条件不在索引列上，就会产生Using filesort。
  3. 能用覆盖索引尽量用覆盖索引
  4. group by 与 order by 很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于 group by 的优化如果不需要排序的可以加上 order by null 禁止排序。注意，where 高于 having，能写在 where 中的限定条件就不要去 having 限定了。

分页查询优化

我们实现分页功能可能会用以下 sql：

select * from employees limit 10000, 10;

该 sql 表示从 employees 表的第 10001 行开始的 10 行数据，虽然只查询了 10 条数据，但是会先去读取 10010 条记录，再抛弃前 10000 条数据，因此如果查询的数据比较靠后，效率非常低


1、根据自增且连续的主键排序的分页查询

该优化必须保证主键是自增的，并且主键连续，中间没有断层。


未优化 sql 

select * from employees limit 9000, 5;

结果：

【面试突击】数据库面试实战-SQL 优化（加更）_主键_10

执行计划：

【面试突击】数据库面试实战-SQL 优化（加更）_sql_11


因为 id 是连续且自增的，所以可以直接通过 id 判断拿到 id 比 9000 大的 5 条数据，效率更高：



优化后 sql

select * from employees where id > 9000 limit 5;


结果

【面试突击】数据库面试实战-SQL 优化（加更）_主键_12

执行计划：

【面试突击】数据库面试实战-SQL 优化（加更）_sql_13



总结

  • 如果主键空缺，则不能使用该优化方法

2、根据非主键字段排序的分页查询


未优化 sql

select * from employees order by name limit 9000, 5;
> OK
> 时间: 0.066s

【面试突击】数据库面试实战-SQL 优化（加更）_主键_14

explain select * from employees order by name limit 9000, 5;

【面试突击】数据库面试实战-SQL 优化（加更）_sql_15

根据执行计划得，使用了全表扫描（type=ALL），并且 Extra 列为 using filesort，原因是联合索引为（name，age，position），但是使用了 select * 中有的列并不在联合索引中，如果使用索引还需要回表，因此 mysql 直接进行全表扫描



优化 sql

优化的点在于：让在排序时返回的字段尽量为覆盖索引，这样就会走索引并且还会使用索引排序

先让排序和分页操作查出主键，再根据主键查到对应记录

select * from employees e inner join (select id from employees order by name limit 9000, 5) ed on e.id = ed.id;
> OK
> 时间: 0.032s

【面试突击】数据库面试实战-SQL 优化（加更）_主键_16

explain select * from employees e inner join (select id from employees order by name limit 9000, 5) ed on e.id = ed.id;

【面试突击】数据库面试实战-SQL 优化（加更）_sql_17

根据执行计划得，优化后查询走了索引，并且排序使用了索引排序



总结

  • 优化后，sql 语句的执行时间时原 sql 的一半
CREATE TABLE `t1` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `a` int(11) DEFAULT NULL,
    `b` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table t2 like t1;  
‐‐ 插入一些示例数据
‐‐ 往t1表插入1万行记录
drop procedure if exists insert_t1;
delimiter ;;
create procedure insert_t1()
begin
    declare i int;
    set i=1;
    while(i<=10000)do
        insert into t1(a,b) values(i,i);
        set i=i+1;
    end while;
end;;
delimiter ;
call insert_t1();

‐‐ 往t2表插入100行记录
drop procedure if exists insert_t2;
delimiter ;;
create procedure insert_t2()
begin
    declare i int;
    set i=1;
    while(i<=100)do
        insert into t2(a,b) values(i,i);
        set i=i+1;
    end while;
end;;
delimiter ;
call insert_t2();



in 和 exists 优化

原则：小表驱动大表

in：当 B 表的数据集小于 A 表的数据集时，使用 in

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


exists：当 A 表的数据集小于 B 表的数据集时，使用 exists

将主查询 A 的数据放到子查询 B 中做条件验证，根据验证结果（true 或 false）来决定主查询的数据是否保留

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


总结

  • exists 只返回 true 或 false，因此子查询中的 select * 也可以用 select 1 替换

count(*)查询优化

‐‐ 临时关闭mysql查询缓存，为了查看sql多次执行的真实时间
set global query_cache_size=0;
set global query_cache_type=0;
EXPLAIN select count(1) from employees;
EXPLAIN select count(id) from employees;
EXPLAIN select count(name) from employees;
EXPLAIN select count(*) from employees;

【面试突击】数据库面试实战-SQL 优化（加更）_sql_18


分析：4 条 sql 语句的执行计划一样，说明这 4 个 sql 的执行效率差不多



总结

  • 当字段有索引，执行效率：count(*) ≈ count(1) > count(字段) > count(主键id)
    如果字段有索引，走二级索引，二级索引存储的数据比主键索引少，所以 count(字段) 比 count(主键id) 效率更高
  • 当字段无索引，执行效率：count(*) ≈ count(1) > count(主键id) > count(字段)
  • count(1) 和 count(*) 比较
  • count(1) 不需要取出字段统计，使用常量 1 做统计，count(字段) 还需要取出字段，所以理论上 count(1) 比 count(字段) 快
  • count(*) 是例外，mysql 并不会把全部字段取出来，会忽略所有的列直接，效率很高，所以不需要用
    count(字段) 或 count(常量) 来替代 count(*)
  • 为什么对于 count(id)，mysql最终选择辅助索引而不是主键聚集索引？因为二级索引相对主键索引存储数据更少，检索
    性能应该更高，mysql内部做了点优化（在5.7版本才优化）。



标签：count,name,索引,数据库,面试,SQL,position,age,employees
From： https://blog.51cto.com/u_16186397/9611743

相关文章

  • Eralng 学习笔记第六天, Fun,进程,电子邮件,数据库,端口
    ErlangFun  示例:-module(helloworld). -export([start/0]). start() ->    A = fun(X) ->       io:fwrite("~p~n",[X])       end,    A(5).输出5----------------------------------------------------module(helloworld). -export(......
  • PowerDesigner 导出mysql
    首先打开powerdesigner，可以通过文件打开一个项目或者直接双击项目通过powerdesigner进行打开。修改导出数据库类型。点击工具栏上的“Database”，选择“ChangeCurrentDBMS”进行修改导出脚本类型，可以选择mysql、sqlserver/oracle、db2等主流的数据库。在DBMS中点击下拉菜单，选择......
  • 数据库连接池
    在前面我们所讲解的mybatis中，使用了数据库连接池技术，避免频繁的创建连接、销毁连接而带来的资源浪费。下面我们就具体的了解下数据库连接池。3.1介绍没有使用数据库连接池：客户端执行SQL语句：要先创建一个新的连接对象，然后执行SQL语句，SQL语句执行后又需要关闭连接对象从而释......
  • 面试经典 150 题 （十）
    用一个变量存放当前所能到达的最远的下标位置classSolution{publicbooleancanJump(int[]nums){intfarestIndex=0;//记录当前最远能到达的下标for(inti=0;i<=farestIndex&&i<nums.length;i++){if((nums[i]+i)>......
  • 面试经典 150 题 （九）
    动态规划，五种状态，关键是找出状态转移式classSolution{publicintmaxProfit(int[]prices){intbuy1=-prices[0];intsell1=0;intbuy2=-prices[0];intsell2=0;for(inti=1;i<prices.length;i++......
  • MySQL常用图形管理工具
    MySQL图形化管理工具极大地方便了数据库的操作与管理，常用的图形化管理工具有MySQLWorkbench、phpMyAdmin、Navicat、MySQLDumper、SQLyog、MySQLODBCConnector。其中，phpMyAdmin和Navicat提供中文操作界面；MySQLWorkbench、MySQLODBCConnector、MySQLDumper为英文界面。下......
  • MySql聚集索引和非聚集索引的区别
    MySql中的聚集索引和非聚集索引主要体现在以下几个方面：1.存储方式：聚集索引的叶子节点存储的是整行数据，非聚集索引叶子节点存储的是键值和整行的标识。2.索引结构：聚集索引的索引结构于数据表的物理结构相同，非聚集索引的索引结构与数据表的物理结构不同。   在数......
  • sql server生成时间序列
    DECLARE@startDateDATE='2024-01-01';--定义结束日期DECLARE@endDateDATE=DATEADD(DAY,365,@startDate)--生成日期序列;WITHDateSequenceAS(SELECT@startDateAS[Date],1AS[DayNumber]UNIONALLSELECTDATEADD(DAY,1,[Date]......
  • oracle常用sql
    --备份tbluser的数据到tbluser_bak中createtabletbluser_bakasselect*fromtbluser;--此处不加as也行--删除备份表的数据truncatetabletbluser_bak;--还原表数据insertintotbluser_bakselect*fromtbluser;--查看数据是否恢复select*fromtbluser_bak;-......
  • python时实检测数据库表的数据变化
     在实际的数据处理和监控应用中，有时我们需要实时监测数据库表的数据变化，以便及时发现和处理数据更新、插入或删除操作。本文将介绍如何使用Python和数据库连接库来实现对数据库表的实时监测，帮助读者了解如何利用Python编写监控程序，实时捕获数据库表的数据变化。 第一步：连接数据......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99