目录
1、简介
2、截取SQL语句
3、查询优化基本分析命令
4、查询优化几个方向
5、索引优化
  5.1、索引优点:
  5.2、索引缺点
  5.3、索引选择
  5.4、索引细究
6、子查询优化
7、等价谓词重写:
8、条件化简与优化
9、外连接优化
10、其他查询优化
首页 数据库 mysql教程 详细介绍MySQL查询优化

详细介绍MySQL查询优化

Mar 26, 2017 am 11:46 AM

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类型索引不支持(即字段使用以下符号时,将不采用索引):

    >, <, >=, <=, BETWEEN, !=, <>,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、子查询的连接条件,过滤条件变成父查询的筛选条件,以提高效率。

  优化:

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

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

  注意:

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

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

  select*from t1,tab as t2 where t1.age>10 and t2.age<25 and t2.id>10;

  具体步骤:

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

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

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

7、等价谓词重写:

  1、BETWEEEN AND改写为 >= 、<=之类的。实测:十万条数据,重写前后时间,1.45s、0.06s

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

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

  注意:百万级数据测试,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、尽量避免大事务操作,提高系统并发能力。

以上是详细介绍MySQL查询优化的详细内容。更多信息请关注PHP中文网其他相关文章!

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

热AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover

AI Clothes Remover

用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool

Undress AI Tool

免费脱衣服图片

Clothoff.io

Clothoff.io

AI脱衣机

Video Face Swap

Video Face Swap

使用我们完全免费的人工智能换脸工具轻松在任何视频中换脸!

热工具

记事本++7.3.1

记事本++7.3.1

好用且免费的代码编辑器

SublimeText3汉化版

SublimeText3汉化版

中文版,非常好用

禅工作室 13.0.1

禅工作室 13.0.1

功能强大的PHP集成开发环境

Dreamweaver CS6

Dreamweaver CS6

视觉化网页开发工具

SublimeText3 Mac版

SublimeText3 Mac版

神级代码编辑软件(SublimeText3)

MySQL:世界上最受欢迎的数据库的简介 MySQL:世界上最受欢迎的数据库的简介 Apr 12, 2025 am 12:18 AM

MySQL是一种开源的关系型数据库管理系统,主要用于快速、可靠地存储和检索数据。其工作原理包括客户端请求、查询解析、执行查询和返回结果。使用示例包括创建表、插入和查询数据,以及高级功能如JOIN操作。常见错误涉及SQL语法、数据类型和权限问题,优化建议包括使用索引、优化查询和分表分区。

MySQL的位置:数据库和编程 MySQL的位置:数据库和编程 Apr 13, 2025 am 12:18 AM

MySQL在数据库和编程中的地位非常重要,它是一个开源的关系型数据库管理系统,广泛应用于各种应用场景。1)MySQL提供高效的数据存储、组织和检索功能,支持Web、移动和企业级系统。2)它使用客户端-服务器架构,支持多种存储引擎和索引优化。3)基本用法包括创建表和插入数据,高级用法涉及多表JOIN和复杂查询。4)常见问题如SQL语法错误和性能问题可以通过EXPLAIN命令和慢查询日志调试。5)性能优化方法包括合理使用索引、优化查询和使用缓存,最佳实践包括使用事务和PreparedStatemen

为什么要使用mysql?利益和优势 为什么要使用mysql?利益和优势 Apr 12, 2025 am 12:17 AM

选择MySQL的原因是其性能、可靠性、易用性和社区支持。1.MySQL提供高效的数据存储和检索功能,支持多种数据类型和高级查询操作。2.采用客户端-服务器架构和多种存储引擎,支持事务和查询优化。3.易于使用,支持多种操作系统和编程语言。4.拥有强大的社区支持,提供丰富的资源和解决方案。

apache怎么连接数据库 apache怎么连接数据库 Apr 13, 2025 pm 01:03 PM

Apache 连接数据库需要以下步骤:安装数据库驱动程序。配置 web.xml 文件以创建连接池。创建 JDBC 数据源,指定连接设置。从 Java 代码中使用 JDBC API 访问数据库,包括获取连接、创建语句、绑定参数、执行查询或更新以及处理结果。

docker怎么启动mysql docker怎么启动mysql Apr 15, 2025 pm 12:09 PM

在 Docker 中启动 MySQL 的过程包含以下步骤:拉取 MySQL 镜像创建并启动容器,设置根用户密码并映射端口验证连接创建数据库和用户授予对数据库的所有权限

centos安装mysql centos安装mysql Apr 14, 2025 pm 08:09 PM

在 CentOS 上安装 MySQL 涉及以下步骤:添加合适的 MySQL yum 源。执行 yum install mysql-server 命令以安装 MySQL 服务器。使用 mysql_secure_installation 命令进行安全设置,例如设置 root 用户密码。根据需要自定义 MySQL 配置文件。调整 MySQL 参数和优化数据库以提升性能。

centos7如何安装mysql centos7如何安装mysql Apr 14, 2025 pm 08:30 PM

优雅安装 MySQL 的关键在于添加 MySQL 官方仓库。具体步骤如下:下载 MySQL 官方 GPG 密钥,防止钓鱼攻击。添加 MySQL 仓库文件:rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm更新 yum 仓库缓存:yum update安装 MySQL:yum install mysql-server启动 MySQL 服务:systemctl start mysqld设置开机自启动

MySQL的角色:Web应用程序中的数据库 MySQL的角色:Web应用程序中的数据库 Apr 17, 2025 am 12:23 AM

MySQL在Web应用中的主要作用是存储和管理数据。1.MySQL高效处理用户信息、产品目录和交易记录等数据。2.通过SQL查询,开发者能从数据库提取信息生成动态内容。3.MySQL基于客户端-服务器模型工作,确保查询速度可接受。

See all articles