MySQL索引优化(为排序)

本文涉及的产品
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL索引优化(为排序)

MySQL优化


一般面试MySQL怎么做优化的答题思路:

1.先至少让系统跑一天,看看系统的慢SQL情况

2.开启慢查询日志,设置阙值,比如超过5秒的就是慢SQL,并将它抓取出来

3.explain+慢SQL 进行分析

4.show profile查询SQL在MySQL服务器里面的执行细节和生命周期情况

5.SQL数据库服务器的参数调优


一、优化原则:小表驱动大表,即小的数据集驱动大的数据集。


当B表的数据集必须小于A表的数据集时,用in优于exists


SELECT * FROM A WHERE id IN (SELECT id FROM B);
等价于
SELECT * FROM A WHERE EXISTS (SELECT  1 FROM B WHERE B.id=A.id)

当A表的数据集必须小于B表的数据集时,用exists优于in


EXISTS
SELECT ... FROM table WHERE EXISTS(subquery)


该语法可以理解为:将主查询的数据,放到子查询中做条件验证,根据验证结果(true/false)来决定主查询的数据结果是否得以保留。


20200218102938453.png


二、Order By优化


1.ORDER BY子句,尽量使用index方式排序,避免使用FileSort方式排序

(1)建立一个表tblA,并在age和birth字段上建立复合索引


20200218104044935.png

(2)分析Order By后的索引使用情况


EXPLAIN SELECT * FROM tblA WHERE age>20 ORDER BY age;


20200218104435264.png

EXPLAIN SELECT * FROM tblA WHERE age>20 ORDER BY age,birth;

20200218104604534.png

EXPLAIN SELECT * FROM tblA WHERE age>20 ORDER BY birth;


20200218104701145.png

产生了filesort

EXPLAIN SELECT * FROM tblA WHERE age>20 ORDER BY birth,age;

20200218104932154.png

产生了filesort


20200218105254167.png


MySQL支持两种方式的排序,FileSort和Index,index效率高,它指MySQL扫描索引本身完成排序,FileSort方式效率较低。


order by满足两情况时,会使用index方式排序:1.order by语句使用索引最左前列 2.使用where子句与order by子句条件组合满足索引最左前列。

尽可能在索引列上完成排序操作,遵照索引建立的最佳左前缀原则。

如果不在索引列上,filesort有两种算法,双路排序和单路排序:

image.png


单路排序会有些问题,例如:

image.png

优化策略:

20200218113407691.png


为什么呢?


image.png


最后来一个总结:


image.png


三、Group By优化


20200218113846216.png

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
3天前
|
缓存 关系型数据库 MySQL
MySQL优化
【7月更文挑战第21天】MySQL优化
10 1
|
7天前
|
关系型数据库 MySQL 调度
MySQL高级功能与优化策略深度探索
MySQL高级功能与优化策略深度探索
|
13天前
|
关系型数据库 MySQL
MySQL 保姆级教程(三):排序检索数据
MySQL 保姆级教程(三):排序检索数据
|
11天前
|
关系型数据库 MySQL API
MySQL上亿数据查询优化:实践与技巧
MySQL亿级数据查询优化涉及索引设计、分区表、查询语句优化和数据库架构调整。例如,通过为常用查询列创建索引、使用EXPLAIN分析查询计划、避免全表扫描和SELECT *,以及采用垂直拆分、水平拆分和读写分离来提升性能。分区表能减少查询数据量,API接口测试可验证优化效果。
21 0
|
12天前
|
存储 关系型数据库 MySQL
如何理解Mysql的索引及他们的原理--------二叉查找树和平衡二叉树和B树和B+树
如何理解Mysql的索引及他们的原理--------二叉查找树和平衡二叉树和B树和B+树
|
19天前
|
关系型数据库 MySQL 数据库
mysql,归零,无法自动排序,删除id,表单的数据没有从零开始出现怎样解决?删除数据仍然从删除的地方该怎样解决?表单的数据没有从2开始,而是从之前的删除的序号开始自增。
mysql,归零,无法自动排序,删除id,表单的数据没有从零开始出现怎样解决?删除数据仍然从删除的地方该怎样解决?表单的数据没有从2开始,而是从之前的删除的序号开始自增。
|
20天前
|
SQL Oracle 关系型数据库
MySQL单表千万级数据查询优化大家怎么说(评论有亮点)
单表千万级数据是MySQL查询的一个坎,可能还不是天花板。“一个人走的慢,一群人走的快”,通过讨论可以发现MySQL千万数据的全貌大概是怎样的。
33 0
|
21天前
|
关系型数据库 MySQL 数据库
MySQL索引的类型与优化方法
MySQL索引的类型与优化方法
|
22天前
|
存储 关系型数据库 MySQL
MySQL索引设计原则与优化策略
MySQL索引设计原则与优化策略
|
22天前
|
存储 关系型数据库 MySQL
MySQL删除索引的方法与注意事项
MySQL删除索引的方法与注意事项