Mysql面试题及千万级数据查询优化

今天在说Mysql查询优化之前,我先说一个常见的面试题,并带着问题深入探讨研究。这样会让大家有更深入的理解。

Mysql数据库中一个表里有一千多万条数据,怎么快速的查出第900万条后的100条数据?

怎么查,谁能告诉我答案?有没有人想着,不就一条语句搞定嘛

select * from table limit 9000000,100;

那我们试试,去执行下这个SQL看看吧

看见了吗,查了100条数据用了7.063s。这能算的上是快速查询吗,估计没人能接受了这种速度吧!基于这个问题,我今天就要说说大数据时的快速查询了。

首先,我演示下大数据分页查询,我的test表里有1000多万条数据,然后使用limit进行分页测试:

select * from test limit 0,100;

耗时:0.005s

select * from test limit 1000,100;

耗时:0.006s

select * from test limit 10000,100;

耗时:0.013s

select * from test limit 100000,100;

耗时:0.104s

select * from test limit 500000,100;

耗时:0.395s

select * from test limit 1000000,100;

耗时:0.823s

select * from test limit 5000000,100;

耗时:3.909s

select * from test limit 10000000,100;

耗时:10.761s

我们发现一个现象,分页查询越靠后查询越慢。这也让我们得出一个结论:

1,limit语句的查询时间与起始记录的位置成正比。

2,mysql的limit语句是很方便,但是对记录很多的表并不适合直接使用。

对大数据量limit分页性能优化

说到查询优化,我们首先想到的肯定是使用索引。利用了索引查询的语句中如果条件只包含了那个索引列,那在这种情况下查询速度就很快了。因为利用索引查找有相应的优化算法,且数据就在查询索引上面,不用再去找相关的数据地址了,这样节省了很多时间。另外Mysql中也有相关的索引缓存,在并发高的时候利用缓存就效果更好了。

我的test表使用InnoDB作为存储引擎,id作为自增主键,默认为主键索引。那我们现在用覆盖索引查询,看看效果如何:

SELECT id FROM test LIMIT 9000000,100;

总耗时4.256s,相对于7.063s少了很多。 现在优化的方案有两种,即通过id作为查询条件使用子查询实现和使用join实现;

1,id>=的(子查询)形式实现

select * from test 
where id >= (select id from test limit 9000000,1)
limit 0,100 

耗时 4.262s;

2,使用join的形式;

SELECT * FROM test a 
JOIN (SELECT id  FROM test LIMIT 9000000,100) b 
ON a.id = b.id

耗时 4.251s;

这两种优化查询使用时间比较接近,其实两者用的都是一个原理,所以效果也差不多。但个人建议最好使用join,尽量减少子查询的使用。

注:目前是千万级别查询,如果将至百万级别,速度会更快,我有亲自测试一下语句,查询时间0.410s。

SELECT * FROM test a JOIN (SELECT id  FROM test LIMIT 1000000,100) b ON a.id = b.id

你用过mysql那些存储引擎,他们都有什么特点和区别?

这是高级开发者面试时经常被问的问题。实际我们在平时的开发中,经常会遇到的,在用SQLyog等工具创建表时,就有一个引擎项要你去选。如下图:

Mysql的存储引擎有这么多种,实际我们在平时用的最多的莫过于InnoDB和MyISAM了。所有如果面试官问道mysql有哪些存储引擎,你只需要告诉这两个常用的就行。那他们都有什么特点和区别呢?

MyISAM:默认表类型,它是基于传统的ISAM类型,ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写,它是存储记录和文件的标准方法。不是事务安全的,而且不支持外键,如果执行大量的select,insert MyISAM比较适合。

InnoDB:支持事务安全的引擎,支持外键、行锁、事务是他的最大特点。如果有大量的update和insert,建议使用InnoDB,特别是针对多个并发和QPS较高的情况。

注:在MySQL 5.5之前的版本中,默认的搜索引擎是MyISAM,从MySQL 5.5之后的版本中,默认的搜索引擎变更为InnoDB。

MyISAM和InnoDB的区别:

1,InnoDB支持事务,MyISAM不支持。对于InnoDB每一条SQL语言都默认封装成事务,自动提交,这样会影响速度,所以最好把多条SQL语言放在begin和commit之间,组成一个事务;

2,InnoDB支持外键,而MyISAM不支持。

3,InnoDB是聚集索引,使用B+Tree作为索引结构,数据文件是和(主键)索引绑在一起的(表数据文件本身就是按B+Tree组织的一个索引结构),必须要有主键,通过主键索引效率很高。MyISAM是非聚集索引,也是使用B+Tree作为索引结构,索引和数据文件是分离的,索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

4,InnoDB不保存表的具体行数,执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量即可,速度很快。

5,Innodb不支持全文索引,而MyISAM支持全文索引,查询效率上MyISAM要高;5.7以后的InnoDB支持全文索引了。

6,InnoDB支持表、行级锁(默认),而MyISAM支持表级锁。;

7,InnoDB表必须有主键(用户没有指定的话会自己找或生产一个主键),而Myisam可以没有。

8,Innodb存储文件有frm、ibd,而Myisam是frm、MYD、MYI。

Innodb:frm是表定义文件,ibd是数据文件。

Myisam:frm是表定义文件,myd是数据文件,myi是索引文件。

Mysql复杂查询语句的优化,你会怎么做?

说到复杂SQL优化,最多的是由于多表关联造成了大量的复杂的SQL语句,那我们拿到这种sql到底该怎么优化呢,实际优化也是有套路的,只要按照套路执行就行。复杂SQL优化方案:

1,使用EXPLAIN关键词检查SQL。EXPLAIN可以帮你分析你的查询语句或是表结构的性能瓶颈,就得EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的,你的数据表是如何被搜索和排序的,是否有全表扫描等;

2,查询的条件尽量使用索引字段,如某一个表有多个条件,就尽量使用复合索引查询,复合索引使用要注意字段的先后顺序。

3,多表关联尽量用join,减少子查询的使用。表的关联字段如果能用主键就用主键,也就是尽可能的使用索引字段。如果关联字段不是索引字段可以根据情况考虑添加索引。

4,尽量使用limit进行分页批量查询,不要一次全部获取。

5,绝对避免select *的使用,尽量select具体需要的字段,减少不必要字段的查询;

6,尽量将or 转换为 union all。

7,尽量避免使用is null或is not null。

8,要注意like的使用,前模糊和全模糊不会走索引。

9,Where后的查询字段尽量减少使用函数,因为函数会造成索引失效。

10,避免使用不等于(!=),因为它不会使用索引。

11,用exists代替in,not exists代替not in,效率会更好;

12,避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤,这个处理需要排序,总计等操作。如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销。

13,千万不要 ORDER BY RAND()

本文分享自微信公众号 - JAVA葵花宝典(Javakhbd)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-10-26

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏云服务器如何搭建Discuz! 论坛

腾讯云云服务器如何搭建Discuz! 论坛

CrossdayDiscuz! Board(简称 Discuz!)是北京康盛新创科技有限责任公司推出的一套通用的社区论坛软件系统。自2001年6月面世以来,Di...

18060
来自专栏猪圈子

一条查询SQL执行原理

客户端将查询sql按照mysql通信协议传输到服务端。服务端接受到请求后,服务端单起一个线程执行sql

10630
来自专栏杨建荣的学习笔记

小白学MySQL要多久?我整理了10多个问题的答案

首先啰嗦下学习MySQL的一些背景:MySQL作为目前最为活跃热门的开源数据库之一,以低成本和简易操作的组合方案在互联网企业中被广泛采用而大放异彩。在炙手可热的...

27220
来自专栏java思维导图

所谓代码生成,简单!我详细分析给你看!

现在很多开源的脚手架项目一般都会有自己的代码生成器,能够帮助快速生成代码,一般都是根据表结构生成实体,和实体对应的操作类,比如controller、servic...

12850
来自专栏高性能服务器开发

写一手好 SQL 很有必要

博主负责的项目主要采用阿里云数据库MySQL,最近频繁出现慢SQL告警,执行时间最长的竟然高达5分钟。导出日志后分析,主要原因竟然是没有命中索引和没有分页处理。...

10620
来自专栏云端漫步

MySQL中DATETIME与TIMESTAMP

真理永远都不过时,今天由于工作的事情涉及到了这里,印象中只记得DATETIME类型占用8字节,TIMESTAMP类型占用4字节,心想这么久没有更新的知识万一过时...

16520
来自专栏杨建荣的学习笔记

MySQL中的主键和rowid,看似简单,其实有一些使用陷阱需要注意

大家在MySQL中我们可能听到过rowid的概念,但是却很难去测试实践,不可避免会有一些疑惑,比如:

19330
来自专栏腾讯技术工程官方号的专栏

江湖急诏令:腾讯数据库王者挑战赛赏金万两募英豪!

腾讯云数据库王者挑战赛开始了 可以花几分钟参加比赛免费将☟☟抱回家! MacBook/iPhone 11/AirPods 25台Kindle 8万元腾讯云...

11450
来自专栏微信公众号【Java技术江湖】

在工作中常用到的SQL

最近在公司做了几张报表,还记得刚开始要做报表的时候都快把SQL给忘光了(当时在广州休假了1个月多,在实习期间也没咋写过SQL),回到公司的第一个需求就是做报表。

11130
来自专栏光城(guangcity)

Mysql系列 - 第2天:详解mysql数据类型(重点)

上面表格中有符号和无符号写反了,[]包含的内容是可选的,默认是无符号类型的,无符号的需要在类型后面跟上unsigned

7920

扫码关注云+社区

领取腾讯云代金券

年度创作总结 领取年终奖励