保险网站建设,打不开wordpress的登陆界面,山东省建设工程管理局网站,文创产品有哪些背景说明#xff1a;
Mysql调优#xff0c;是大家日常常见的调优工作。所以Mysql调优是一个非常、非常核心的面试知识点。
在40岁老架构师 尼恩的读者交流群(50)中#xff0c;其相关面试题是一个非常、非常高频的交流话题。
近段时间#xff0c;有小伙伴面试网易#x…背景说明
Mysql调优是大家日常常见的调优工作。所以Mysql调优是一个非常、非常核心的面试知识点。
在40岁老架构师 尼恩的读者交流群(50)中其相关面试题是一个非常、非常高频的交流话题。
近段时间有小伙伴面试网易说遇到一个SQL 深度分页 查询 调优的面试题 MySQL 百万级数据怎么做分页查询说说你的思路 社群中还遇到过大概的变种 形式1如何解决Mysql深分页问题 形式2mysql如何实现高效分页 形式3后面的变种应该有很多变种…会收入 《尼恩Java面试宝典》。 这里尼恩给大家 调优做一下系统化、体系化的梳理使得大家可以充分展示一下大家雄厚的 “技术肌肉”让面试官爱到 “不能自已、口水直流”。
也一并把这个 SQL 深度分页 题目以及参考答案收入咱们的《尼恩Java面试宝典》供后面的小伙伴参考提升大家的 3高 架构、设计、开发水平。 注本文以 PDF 持续更新最新尼恩 架构笔记、面试题 的PDF文件请从这里获取码云 深挖问题MySQL分页起点越大查询速度越慢
在数据库开发过程中我们经常会使用分页核心技术是使用用limit start, count分页语句进行数据的读取。
我们分别看下从10 1000 10000 100000开始分页的执行时间每页取20条。
select * from product limit 10, 20 0.002秒
select * from product limit 1000, 20 0.011秒
select * from product limit 10000, 20 0.027秒
select * from product limit 100000, 20 0.057秒我们已经看出随着起始记录的增加时间也随着增大
这说明分页语句limit跟起始页码是有很大关系的那么我们把起始记录改为100w看下
select * from product limit 1000000, 20 0.682秒我们惊讶的发现MySQL在数据量大的情况下分页起点越大查询速度越慢
300万条起的查询速度已经需要1.368秒钟。
这是为什么呢?
因为limit 3000000,10的语法实际上是mysql扫描到前3000020条数据, 之后丢弃前面的3000000行
这个步骤其实是浪费掉的。
select * from product limit 3000000, 20 1.368秒从中我们也能总结出两件事情
limit语句的查询时间与起始记录的位置成正比mysql的limit语句是很方便但是对记录很多的表并不适合直接使用。
基础知识mysql中limit的用法
语法
SELECT * FROM 表名 limit m,n;
SELECT * FROM table LIMIT [offset,] rows;注释Limit子句可以被用于强制 SELECT 语句返回指定的记录数。
Limit接受一个或两个数字参数,参数必须是一个整数常量。
如果给定两个参数
第一个参数指定第一个返回记录行的偏移量第二个参数指定返回记录行的最大数目。
1.m代表从m1条记录行开始检索n代表取出n条数据。(m可设为0)
如SELECT * FROM 表名 limit 6,5;表示从第7条记录行开始算取出5条数据
2.值得注意的是n可以被设置为-1当n为-1时表示从m1行开始检索直到取出最后一条数据。
如SELECT * FROM 表名 limit 6,-1;表示取出第6条记录行以后的所有数据。
3.若只给出m则表示从第1条记录行开始算一共取出m条
如SELECT * FROM 表名 limit 6;以年龄倒序后取出前3行
mysql select * from student order by age desc;
-------------------------
| SNO | SNAME | AGE | SEX |
-------------------------
| 1 | 换换 | 23 | 男 |
| 2 | 刘丽 | 22 | 女 |
| 5 | 张友 | 22 | 男 |
| 6 | 刘力 | 22 | 男 |
| 4 | NULL | 10 | NULL |
-------------------------
5 rows in set (0.00 sec)mysql select * from student order by age desc limit 3;
-------------------------
| SNO | SNAME | AGE | SEX |
-------------------------
| 1 | 换换 | 23 | 男 |
| 2 | 刘丽 | 22 | 女 |
| 6 | 刘力 | 22 | 男 |
-------------------------
3 rows in set (0.00 sec)跳过前3行后再2取行.
mysql select * from student order by age desc limit 3,2;
-------------------------
| SNO | SNAME | AGE | SEX |
-------------------------
| 6 | 刘力 | 22 | 男 |
| 4 | NULL | 10 | NULL |
-------------------------回到问题MySQL百万级数据大分页查询优化
我们惊讶的发现MySQL在数据量大的情况下分页起点越大查询速度越慢
300万条起的查询速度已经需要1.368秒钟。
那么该如何优化呢
方法1: 直接使用数据库提供的SQL语句
语句样式
MySQL中,可用如下方法:
SELECT * FROM 表名称 LIMIT start, count功能
Limit限制的是从结果集的start 位置处取出count 条输出,其余抛弃.
原因/缺点
全表扫描,速度会很慢
而且 有的数据库结果集返回不稳定(如某次返回1,2,3,另外的一次返回2,1,3).
适应场景
适用于数据量较少的情况
元祖数量、记录数量级别百/千级
方法2: 建立主键或唯一索引, 利用索引(假设每页10条)
语句样式
SELECT * FROM 表名称 WHERE id_pk (pageNum*10) LIMIT M除了主键也可以 利用唯一键索引快速定位部分元组避免全表扫描
比如: 读第1000到1019行元组(pk是唯一键).
SELECT * FROM 表名称 WHERE pk1000 ORDER BY pk ASC LIMIT 0,20原因
索引扫描,速度会很快.
缺点
如果数据查询出来并不是按照pk_id排序并且pk_id全部数据都存在没有缺失可以作为序号使用不然分页会有漏掉数据
适应场景
适用于数据量多的情况(元组数上万)id数据没有缺失可以作为序号使用
方法3: 基于索引再排序
语句样式
MySQL中,可用如下方法:
SELECT * FROM 表名称 WHERE id_pk (pageNum*10) ORDER BY id_pk ASC LIMIT M适应场景
适用于数据量多的情况(元组数上万).
最好ORDER BY后的列对象是主键或唯一索引,
id数据没有缺失可以作为序号使用
使得ORDERBY操作能利用索引被消除但结果集是稳定的
原因
索引扫描,速度会很快.
但MySQL的排序操作,只有ASC没有DESC
mysql中索引存储的排序方式是ASC的没有DESC的索引。
这就能够理解为啥order by 默认是按照ASC来排序的了吧
虽然索引是ASC的但是也可以反向进行检索就相当于DESC了
方法4: 基于索引使用prepare
语句样式MySQL中,可用如下方法:
PREPARE stmt_name FROM SELECT * FROM 表名称 WHERE id_pk (*10) ORDER BY id_pk ASC LIMIT M第一个问号表示pageNum
适应场景
大数据量
原因
索引扫描速度会很快.
prepare语句又比一般的查询语句快一点。
方法5: 利用子查询索引快速定位元组
利用子查询索引快速定位元组的位置,然后再读取元组.
比如(id是主键/唯一键)
利用子查询示例:
SELECT * FROM your_table WHERE id (SELECT id FROM your_table ORDER BY id desc LIMIT ($page-1)*$pagesize ORDER BY id desc LIMIT $pagesize方法6: 利用连接索引快速定位元组的位置,然后再读取元组.
比如(id是主键/唯一键,蓝色字体时变量)
利用连接示例:
SELECT * FROM your_table AS t1 JOIN (SELECT id FROM your_table ORDER BY id desc LIMIT ($page-1)*$pagesize AS t2 WHERE t1.id t2.id ORDER BY t1.id desc LIMIT $pagesize;方法7: 利用表的索引覆盖来调优
我们都知道利用了索引查询的语句中如果只包含了那个索引列也就是索引覆盖那么这种情况会查询很快。
为什么呢
因为利用索引查找有优化算法且数据就在查询索引上面不用再去找相关的数据地址了这样节省了很多时间。另外Mysql中也有相关的索引缓存在并发高的时候利用缓存就效果更好了。
在我们的例子中我们知道id字段是主键自然就包含了默认的主键索引。现在让我们看看利用覆盖索引的查询效果如何。
这次我们之间查询最后一页的数据利用覆盖索引只包含id列如下
select id from product limit 866613, 20 0.2秒如果查询了所有列的37.44秒这里只要0.2秒提升了大概100多倍的速度
那么如果我们也要查询所有列有两种方法一种是id的形式另一种就是利用join看下实际情况
SELECT * FROM product WHERE ID (select id from product limit 866613, 1) limit 20查询时间为0.2秒
另一种写法
SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID b.id查询时间也很短
方法8利用复合索引进行优化
假设数据表 collect ( id, title ,info ,vtype) 就这4个字段其中 title 用定长info 用text, id 是逐渐vtype是tinyintvtype是索引。这是一个基本的新闻系统的简单模型。
现在往里面填充数据填充10万篇新闻。
最后collect 为 10万条记录数据库表占用硬1.6G。
看下面这条sql语句
select id,title from collect limit 1000,10;很快基本上0.01秒就OK再看下面的
select id,title from collect limit 90000,10;从9万条开始分页结果
8-9秒完成my god 哪出问题了
看下面一条语句:
select id from collect order by id limit 90000,10;很快0.04秒就OK。
为什么
因为用了id主键做索引, 这里实现了索引覆盖方法7当然快。
所以可以按照方法7进行优化具体如下
select id,title from collect where id(select id from collect order by id limit 90000,1) limit 10;再看下面的语句带上where
select id from collect where vtype1 order by id limit 90000,10; 很慢用了8-9秒
注意vtype 做了索引了啊怎么会慢呢vtype做了索引是不错如果直接对vtype进行过滤比如
select id from collect where vtype1 limit 1000,10;是很快的基本上0.05秒可是提高90倍从9万开始那就是0.05*904.5秒的速度了。
和测试结果8-9秒到了一个数量级。
其实加了where 就不走索引这样做还是全表扫描解决的办法是复合索引
加一个复合索引 search_index(vtype,id) 这样的索引。
然后测试
select id from collect where vtype1 limit 90000,10;非常快0.04秒完成再测试:
select id ,title from collect where vtype1 limit 90000,10;非常遗憾8-9秒没走search_index 复合索引不是索引覆盖
综上
如果对于有where 条件又想走索引用limit的
必须设计一个索引将where 放第一位limit用到的主键放第2位而且只能select 主键
按这样的逻辑百万级的limit 在0.0x秒就可以分完。完美解决了分页问题了。
看来mysql 语句的优化和索引时非常重要的
像这种分页最大的页码页显然这种时间是无法忍受的。
40岁老架构师尼恩提示
回答到了这里已经接近满分了
但是面试是一个需要120分的活儿
怎么得到120分呢
可以告诉面试官如何 提升SQL的性能 还是要从 表设计、索引设计、SQL设计等全方位解决具体请看MySQL数据库开发的三十六条军规
接下来就给面试官介绍一下MySQL数据库开发的三十六条军规
MySQL数据库开发的三十六条军规
一.核心军规
尽量不在数据库做运算cpu计算的事务必移至业务层;控制表、行、列数量【控制单张表的数据量 1年/500W条超出可做分表】【单库表数据量不超过300张】 、【单张表的字段个数不超过50个多了拆表】三大范式没有绝对的要使用效率优先时可适当牺牲范式拒绝3B(拒绝大sql语句big sql、拒绝大事物big transaction、拒绝大批量big batch);
二.字段类军规
用好数值类型(用合适的字段类型节约空间); 如一个字段注定就只有1跟2 要设计成 int(1) 而不是 int(11)字符转化为数字(能转化的最好转化,同样节约空间、提高查询性能); 如一个字段注定就只有1跟2要设计成int(1) 而不是char(1) 查询优化如字段类型是 char(1) 查询应当where xx‘1’ 而不是 xx1 会导致效率慢避免使用NULL字段
NULL字段很难查询优化、NULL字段的索引需要额外空间、NULL字段的复合索引无效;
如 要设计成 c int(10) NOT NULL DEFAULT 0
而不是 c int(10) NOT NULL少用text/blob类型(尽量使用varchar代替text字段), 需要请拆表不在数据库存图片请存图片路径然后图片文件存在项目文件夹下。
三.索引类军规
合理使用索引
改善查询,减慢更新,索引一定不是越多越好;
如不要给性别创建索引字符字段必须建前缀索引;
pinyin varchar(100) DEFAULT NULL COMMENT 小区拼音, KEY idx_pinyin (pinyin(8)),不在索引做列运算;
如WHERE to_days(current_date) – to_days(date_col) 10
改为WHERE date_col DATE_SUB(2011-10- 22,INTERVAL 10 DAY);innodb主键推荐使用自增列
主键建立聚簇索引,主键不应该被修改,字符串不应该做主键
如用独立于业务的AUTO_INCREMENT不用外键(由程序保证约束);
四.SQL类军规
sql语句尽可能简单
一条sql只能在一个cpu运算,大语句拆小语句,减少锁时间,一条大sql可以堵死整个库; 简单的事务;避免使用trig/func(触发器、函数不用由客户端程序取而代之);不用select *(消耗cpu,io,内存,带宽,这种程序不具有扩展性);
如select a ,b,c 会比 select * 好 只取需要列OR改写为 IN
如where a1 or a2 改 a in(1,2)OR改写为UNION
针对不同字段 where a1 or b1
改select 1 from a where a1 union select 1 from a where b1避免负向%;
如where a like %北京% 改为 where a like 北京%limit高效分页(limit越大效率越低);
如Limit 10000,10 改为 where id xxxx limit 11使用union all替代union(union有去重开销);高并发db少用2个表以上的join;使用group by 去除排序加快效率;
如group by name 默认是asc排序
改group by name order by null 提高查询效率请使用同类型比较;
如where 双精度双精度 数字数字 字符字符 避免转换导致索引丢失打散大批量更新;
如在凌晨空闲时期更新执行五.约定类军规
隔离线上线下
如开发用dev库测试用qa库模拟用sim库
线上用线上库开发无线上库操作权限不在程序端加锁即外部锁外部锁不可控会导致 高并发会炸极难调试和排查统一字符 UTF-8 校对规则 utf8_general_ci 出现乱码 SET NAMES UTF8统一命名规范库表名一律小写索引前缀用idx_ 库名 用缩写2-7字符不使用系统关键字保留字命名
40岁老架构师尼恩提示
问题回答到这里已经20分钟过去了面试官已经爱到 “不能自已、口水直流” 啦。
参考链接
https://blog.csdn.net/qq_43518425/article/details/113876287
https://blog.csdn.net/whzhaochao/article/details/49126037
https://blog.csdn.net/wuzhangweiss/article/details/101156910
https://blog.csdn.net/Jerome_s/article/details/44992549
推荐阅读
《网易二面CPU狂飙900%该怎么处理》
《阿里二面千万级、亿级数据如何性能优化 教科书级 答案来了》
《Linux命令大全2W多字一次实现Linux自由》
《峰值21WQps、亿级DAU小游戏《羊了个羊》是怎么架构的》
《场景题假设10W人突访你的系统如何做到不 雪崩》
《2个大厂 100亿级 超大流量 红包 架构方案》
《Nginx面试题史上最全 持续更新》
《K8S面试题史上最全 持续更新》
《操作系统面试题史上最全、持续更新》
《Docker面试题史上最全 持续更新》
《Springcloud gateway 底层原理、核心实战 (史上最全)》
《Flux、Mono、Reactor 实战史上最全》
《sentinel 史上最全》
《Nacos (史上最全)》
《TCP协议详解 (史上最全)》
《分库分表 Sharding-JDBC 底层原理、核心实战史上最全》
《clickhouse 超底层原理 高可用实操 史上最全》
《nacos高可用图解秒懂史上最全》
《队列之王 Disruptor 原理、架构、源码 一文穿透》
《环形队列、 条带环形队列 Striped-RingBuffer 史上最全》
《一文搞定SpringBoot、SLF4j、Log4j、Logback、Netty之间混乱关系史上最全》
《单例模式史上最全》
《红黑树 图解 秒懂 史上最全》
《分布式事务 秒懂》
《缓存之王Caffeine 源码、架构、原理史上最全10W字 超级长文》
《缓存之王Caffeine 的使用史上最全》
《Java Agent 探针、字节码增强 ByteBuddy史上最全》
《Docker原理图解秒懂史上最全》
《Redis分布式锁图解 - 秒懂 - 史上最全》
《Zookeeper 分布式锁 - 图解 - 秒懂》
《Zookeeper Curator 事件监听 - 10分钟看懂》
《Netty 粘包 拆包 | 史上最全解读》
《Netty 100万级高并发服务器配置》
《Springcloud 高并发 配置 一文全懂》## 推荐阅读
《网易二面CPU狂飙900%该怎么处理》
《阿里二面千万级、亿级数据如何性能优化 教科书级 答案来了》
《峰值21WQps、亿级DAU小游戏《羊了个羊》是怎么架构的》
《场景题假设10W人突访你的系统如何做到不 雪崩》
《2个大厂 100亿级 超大流量 红包 架构方案》
《Nginx面试题史上最全 持续更新》
《K8S面试题史上最全 持续更新》
《操作系统面试题史上最全、持续更新》
《Docker面试题史上最全 持续更新》
《Springcloud gateway 底层原理、核心实战 (史上最全)》
《Flux、Mono、Reactor 实战史上最全》
《sentinel 史上最全》
《Nacos (史上最全)》
《TCP协议详解 (史上最全)》
《分库分表 Sharding-JDBC 底层原理、核心实战史上最全》
《clickhouse 超底层原理 高可用实操 史上最全》
《nacos高可用图解秒懂史上最全》
《队列之王 Disruptor 原理、架构、源码 一文穿透》
《环形队列、 条带环形队列 Striped-RingBuffer 史上最全》
《一文搞定SpringBoot、SLF4j、Log4j、Logback、Netty之间混乱关系史上最全》
《单例模式史上最全》
《红黑树 图解 秒懂 史上最全》
《分布式事务 秒懂》
《缓存之王Caffeine 源码、架构、原理史上最全10W字 超级长文》
《缓存之王Caffeine 的使用史上最全》
《Java Agent 探针、字节码增强 ByteBuddy史上最全》
《Docker原理图解秒懂史上最全》
《Redis分布式锁图解 - 秒懂 - 史上最全》
《Zookeeper 分布式锁 - 图解 - 秒懂》
《Zookeeper Curator 事件监听 - 10分钟看懂》
《Netty 粘包 拆包 | 史上最全解读》
《Netty 100万级高并发服务器配置》
《Springcloud 高并发 配置 一文全懂》
