详细介绍MySQL查询优化

轻婷吖_3758

轻婷吖_3758

2017-03-26

1263人浏览

原创

1、简介

     一个好的web应用,最重要的一点是有着优秀的访问性能。数据库mysql是web应用的组成部分,也是决定其性能的重要部分。所以提升mysql的性能至关重要。

     MySQL性能的提升可分为三部分,包括硬件、网络、软件。其中硬件、网络取决于公司的财力,需要白哗哗的银两,这里就不说啦。软件又细分为很多种,在这里我们通过MySQL的查询优化从而达到性能的提升。

     最近看了一些关于查询优化的书籍,同时也在网上看一些前辈们写的文章。

以下是自己整理借鉴关于查询优化的一些总结:

2、截取SQL语句

     1、全面查询日志

     2、慢查询日志

     3、二进制日志

     4、进程列表

  SHOW FULL PROCESSLIST;

  。。。

3、查询优化基本分析命令

  1、EXPLAIN {PARTITIONS|EXTENDED}

  2、SHOW CREATE TABLE tab;

  3、SHOW INDEXS FROM tab;

  4、SHOW TABLE STATUS LIKE ‘tab’;

  5、SHOW [GLOBAL|SESSION] STATUS LIKE ‘’;

  6、SHOW VARIABLES

  。。。。

  ps:我自己都感觉上面都是没任何营养的东西。下面才是真正的干货哈。

4、查询优化几个方向

  1、尽量避免全文扫描,给相应字段增加索引,应用索引来查询

  2、删除不用或者重复的索引

  3、查询重写,等价转换(谓词、子查询、连接查询)

  4、删除内容重复不必要的语句,精简语句

  5、整合重复执行的语句

  6、缓存查询结果

5、索引优化

  5.1、索引优点:

    1、保持数据的完整性

    2、提高数据的查询性能

    3、改进表的连接操作(jion)

    4、对查询结果进行排序。没索引将会采用内部文件排序算法进行排序,效率较慢

    5、简化聚合数据操作

  5.2、索引缺点

    1、索引需要占用一定的存储空间

    2、数据插入、更新、删除时会受索引的影响,性能会降低。因为数据变更索引也需要进行更新

    3、多个索引,优化器需要耗时则优选择

  5.3、索引选择

    1、数据量大时采用

    2、数据高度重复时,不采用

    3、查询取出数据大于20%,将采用全文扫描,不用索引

  5.4、索引细究

    资料查询:

    MySQL中的InnoDB、MyISAM都是B-Tree类型索引

    B-Tree包含:PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT

    B-Tree类型索引不支持(即字段使用以下符号时,将不采用索引):

    >, =, ,like ‘%**’

    【在此先介绍一下覆盖索引】

    以我自己理解的方式介绍吧。覆盖索引并不是像主键索引、唯一索引一样真实存在,它只是对索引应用某些特定场景的一种定义【另一种理解:查询的列是索引列,因此列被索引覆盖】。它可以突破传统的限制,使用以上操作符,且依然采用索引进行查询。

    因为查询的列是索引列,所以不需要读取行,只需要读取列字段数据就可以了。【例如你看一本书,需要找某一内容,刚好那内容出现在目录中,那就不用一页页翻了,直接在目录中定位到第几页查找】

    如何激活覆盖索引呢?什么样才是特定场景呢?

    索引字段,在select中出现就是了。

    复合索引还可能有其他的特殊场景。例如,三列复合索引,仅需要在select、where、group by、order by中,任意一个地方出现一次复合索引最左边列就可以激活使用覆盖索引了。

    查看:

    EXPLAIN中Extra显示有Using index表示这条语句采用了覆盖索引。

    结论:

    不建议在查询的时候使用select*from进行查询了,应该写需要用的字段,并且增加相应的索引,以提高查询性能。

    针对以上操作符实测结果:

    1、以select*from形式,where中是primary key可以通杀【除like】(使用主键进行查询);index则全不可以。

    2、以select 字段a from tab where 字段a《以上操作符》形式测试,结果依然可以使用索引查询。【采用了覆盖索引】

    其他索引优化方法:

    1、使用索引关键字作为连接的条件

    2、复合索引使用

    3、索引合并or and,将涉及到的字段合并成复合索引

    4、where、和group by涉及字段加索引

6、子查询优化

  在from中为非相关子查询,可以上拉子查询到父层。在多表连接查询考虑连接代价再选择。

  查询优化器对子查询一般采用嵌套执行的方式,即对父查询中的每一行,都执行一次子查询,这样子查询会执行很多次。这种执行方式效率很低。

  子查询转化为连接查询优点:

  1、子查询不用执行很多次

  2、优化器可以根据信息来选择不同的方法和连接顺序

  3、子查询的连接条件,过滤条件变成父查询的筛选条件,以提高效率。

  优化:

  子查询合并,若多个子查询,能合并的尽量合并。

win可用的mysql指令skill
win可用的mysql指令skill

安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录,或需要将该能力封装成可公开复用的 Codex skill。

下载

  子查询展开,即上拉变成多表查询(时刻保证等价变化)

  注意:

  子查询展开只能展开简单的查询,若子查询含有聚集函数、GROUP BY、DISTINCT,则不能上拉。

  select * from t1 (select*from tab where id>10) as t2 where t1.age>10 and t2.age

  select*from t1,tab as t2 where t1.age>10 and t2.age10;

  具体步骤:

  1、from与from合并,修改相应参数

  2、where与where合并,用and连接

  3、修改相应的谓词(in改=)

7、等价谓词重写:

  1、BETWEEEN AND改写为 >= 、

  2、in转换多个or。字段为索引时,两个都能用到索引,or效率相对in好一点

  3、name like ‘abc%’改写成name>=’abc’ and name

  注意:百万级数据测试,name没有索引之前like比后一种查询快;给字段增加索引后,后面的快一点点,相差不大,因为两种方法在查询的时候都用到了索引。

  。。。。

8、条件化简与优化

  1、将where、having(不存在groupby和聚集函数时)、join-on条件能合并的尽量合并

  2、删除不必要的括号,减少语法分许的or和and树层,减少cpu消耗

  3、常量传递。a=b and b=2转换为 a=2 and b=2。尽量不使用变量a=b或a=@var

  4、消除没用的SQL条件

  5、where等号右边尽量不出现表达式计算;where中不要对字段进行表达式计算、函数的使用

  6、恒等变换、不等式变换。例:测试百万级数据a>b and b>10变为a>b and a>10 and b>10优化显著

9、外连接优化

  即将外连接转为内连接

  优点:

  1、优化处理器处理外连接比内连接步骤多且耗时

  2、外连接消除后,优化器选择多表连接顺序有更多选择,可以择优而选

  3、可以将筛选条件最为严格的表作为外表(连接顺序最前面,是多层循环体的外循环层),

  可以减少不必要的I/O开销,能加快算法执行的速度。

  on a.id=b.id与where a.id=b.id的差别,on则表进行连接,where则进行数据对比

  注意:前提必须是结果为NULL决绝(即条件限制不要NULL数据行,语意上是内连接)

  优化原则:

  精简查询,连接消除,等效转换,去除多余表对象连接

  例如:主键/唯一键作为连接条件,且中间表列只作为等值条件,可以去掉中间表连接

10、其他查询优化

  1、以下将会造成放弃索引查询,采用全文扫描

    1.1、where 子句中使用!=或操作符  注意:主键支持。非主键不支持

    1.2、避免使用or

      经测试,并非是使用了or就一定不能使用索引,大多情况下是没用到索引,但还有少数情况是用到的,因此具体情况具体分析。

      类似优化:

      select * from tab name=’aa’ or name=’bb’;

      =>

      select * from tab name=’aa’

      union all

      select * from tab name=’bb’;

      实测:

      1、十万数据测试,没任何索引的情况下,上面比下面的查询速率快一倍。

      2、三十万数据测试,aa与bb都是单独索引情况下,下面的查询速率比or快一点。

    1.3、避免使用not in

      not in一般不能使用索引;主键字段可以

    1.4、where中尽量避免使用对null的判断

    1.5、like不能前置百分号 like ‘%.com’

      解决:

        1、若必须使用%前置,且数据长度不大,例如URL,可将数据翻转存入数据库,再来查。LIKE REVERSE‘%.com’;

        2、使用覆盖索引

    1.6、使用索引字段作为条件的时候,假若是复合索引,则应该使用索引最左边前缀的字段名

  2、将exists代替in

    select num from a where num in(select num from b)

    select num from a where exists(select 1 from b where num=a.num)

    一百万条数据,筛选59417条数据用时6.65s、4.18s。没做其他优化,仅仅只是将exists替换in。

  3、字段定义是字符串,查询时没带引号,不会用索引,将会进行全文扫描。

  【以下是摘抄于半夜乱弹琴博文http://www.cnblogs.com/lingiu/p/3414134.html,本人没进行相应的测试】

  4、尽量使用表变量来代替临时表

  5、避免频繁创建和删除临时表,以减少系统表资源的消耗

  6、如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定

  7、尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写

  8、大数据量,若数据量过大,应该考虑相应需求是否合理。

  9、尽量避免大事务操作,提高系统并发能力。

相关文章

PHP速学视频免费教程(入门到精通)
PHP速学视频免费教程(入门到精通)

PHP怎么学习?PHP怎么入门?PHP在哪学?PHP怎么学才快?不用担心,这里为大家提供了PHP速学教程(入门到精通),有需要的小伙伴保存下载就能学习啦!

下载

相关标签:

mysql mysql优化

本站声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

相关专题

更多
GoFrame框架Web开发与路由中间件
GoFrame框架Web开发与路由中间件

本专题聚焦GoFrame框架的Web开发,拆解路由注册与匹配规则、请求参数获取、参数校验与中间件执行顺序,说明统一响应、JWT鉴权、文件上传与接口文档生成的落地方式,并补充优雅重启与测试实践。

2026.11.17

20

15

GoFrame框架安装与gf命令行工具
GoFrame框架安装与gf命令行工具

本专题围绕GoFrame框架的安装与工具链配置展开,讲清Go版本要求、gf命令行的安装方式、代理镜像与依赖准备,说明项目初始化、热编译、交叉编译打包的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.17

20

15

GoFrame框架零基础入门
GoFrame框架零基础入门

本专题整理GoFrame框架的入门知识,从模块化设计理念、环境准备到跑通第一个Web服务,讲清目录分层、配置加载、日志与中间件的用法,并对比Gin、Go-Zero的差异,帮助Go开发者建立整体认知。

2026.11.17

20

15

SQLAlchemy 一对多关系怎么建立
SQLAlchemy 一对多关系怎么建立

SQLAlchemy 一对多关系怎么建立?本专题整理父子表外键、relationship 双向映射、关联查询、级联删除和加载优化等相关文章,帮助你理解并实现常见的 ORM 关系建模。

2026.11.17

20

10

SQLAlchemy 如何增删改查
SQLAlchemy 如何增删改查

SQLAlchemy 如何增删改查?本专题整理 ORM 新增记录、条件查询、更新和删除、Session 事务提交与批量处理等实用文章,帮助你掌握 Python 数据库操作流程。

2026.11.17

20

11

SQLAlchemy ORM 模型怎么写
SQLAlchemy ORM 模型怎么写

SQLAlchemy ORM 模型怎么写?本专题整理模型类定义、Mapped 字段映射、主键与约束、表结构创建和常见报错的相关文章,帮助你从基础配置到实际建模逐步掌握 SQLAlchemy。

2026.11.17

20

16

Go-Zero API开发使用教程
Go-Zero API开发使用教程

本专题聚焦Go-Zero的API开发,拆解.api描述文件的语法结构、路由与分组定义、参数绑定与校验规则,说明统一响应体、JWT鉴权、跨域与接口文档生成的落地方式,并给出网关聚合与接口测试的实践建议。

2026.11.12

180

15

Go-Zero安装与goctl环境配置教程
Go-Zero安装与goctl环境配置教程

本专题围绕Go-Zero的安装与工具链配置展开,讲清Go版本要求、goctl安装方式、代理镜像、protoc依赖与环境变量设置,说明多版本共存、升级回退与CI流水线安装的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.12

120

15

Go-Zero零基础入门教程
Go-Zero零基础入门教程

本专题整理Go-Zero的入门知识,从框架定位、环境准备到跑通第一个API服务,讲清目录分层、配置加载、日志与中间件的基本用法,并对比Gin、Kratos的差异,帮助Go开发者建立整体认知,判断它是否适合当前项目。

2026.11.12

120

15

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程