`
jiangduxi
  • 浏览: 456686 次
  • 性别: Icon_minigender_1
  • 来自: 深圳
社区版块
存档分类
最新评论

SQL优化的笔记

阅读更多
  一般使用SQL的时候你是不会去想到优化。但是面对一个有SQL性能问题的数据库时,我们应该如何入手进行系统的分析,使得能够尽快定位问题SQL,并且尽快解决问题。

1.使用show status 命令了解各种SQL的执行频率
引用

例如在Mysql的Cline上输入
show status like 'Com_%';

显示的是一些:Com_xxx.
Com_xxx 表示每个xx语句执行的次数。通常情况下我们比较关注如下一些操作:
引用

  Com_select:执行select操作的次数
  Com_insert:执行Insert操作的次数,对于批量插入的INSERT操作,只累加一次
  Com_update:执行update操作的次数
  Com_delete:执行Delete操作的次数

上面这些参数对于所有存储引擎的表操作都会进行累加。下面有些参数只针对InnoDB存储引擎的,累加的算法也有点不一样。
引用

  Innodb_rows_read:select查询返回的行数
  Innodb_rows_inserted:执行INSERT操作插入的行数
  Innodb_rows_updated:执行Update操作更新的行数
  Innodb_rows_deleted:执行Delete操作删除的行数

通过上面的一些参数,我们可以了解当前数据库的应用是以插入为主还是以查询为主。以及各种类型的SQL大致的执行比例是多少。对于更新操作的计数,是对执行次数的计数,不管提交还是回滚都会进行累加。

对于事务型的应用,通过Com_commit和Com_rollback进行分析。如果回滚操作非常频繁那么要思考下是不是编写存在问题。
下面有几个参数用于了解数据库的基本情况
引用

  Connections:试图连接Mysql服务器的次数(执行的命令是:show status like 'Con_%';)
  Uptime: 服务器工作时间(执行的命令是:show status like 'Up_%';)
  Slow_queries:慢查询的次数(执行的命令是:show status like 'Slow_%';)


2. 定位执行效率较低的SQL语句

要想定义效率较低的SQL可以按照下面两种方式试试。
引用

  1. 通过慢查询日志定位那些执行效率较低的SQL语句,用 --log-slow-queries[=file_name]选项启动时,mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。
2. 慢查询日志在查询结束以后才记录,所以在应用反映执行效率出现问题的时候进行查询慢查询日志并不能定位问题,可以使show processlist 命令查看当前MySQL在进行的线程,包括线程的状态,是否锁表等,可以实时地查看SQL的执行情况,同时对一些锁表操作进优化。


3. 使用EXPLAIN分析低效SQL的执行计划。
  在查询到效率低的SQL语句后,那我们可以使用explain或者DESC命令获取Myswl如何执行SELECT语句的信息,包括在Select语句执行过程中表如何连接和连接的顺序。
例如你想计数xxxx年公司的销售额,那么需要操作sales和comapny table,并对money字段进行sum操作。看看怎么使用explain:
引用

   explain select sum(moneys) from sales a company b where a.company_id = b.id and a.year=XXXX \G;(注意加上\G是为了更好的看)
显示如下:
*********************** 1. row***************************
          id: 1
select_type: SIMPLE
       table: a
        type: ALL
   possible_keys: NULL
         key:NULL
       key_len: NULL
       ref: NULL
       rows:1000
       Extra: Using where
*********************** 2. row***************************
          id: 2
select_type: SIMPLE
       table: b
        type: ref
   possible_keys: ind_company_id
         key:ind_comapany_id
       key_len: 5
       ref: sakila.a.company_id
       rows:1
       Extra: Using where;Using index

下面解释下每个列的含义:
引用

  select_type: 表示SELECT的类型,常见的取值为SIMPLE(简单表,不使用表连接或者子查询)、PRIMARY(主查询,即外层的查询)、UNION、SUBQUERY

table: 输出结果集的表

type: 表示表的连接类型,性能由好到差的类型类型为
(System(表中仅有一行,即常量表),
const(单表中最多有一个匹配行),
eq_ref(对于前面的每一行,在此表中只查询一条记录),
ref(使用普通的索引),
ref_or_null(和ref类似,但是条件中包含对于NULL查询),
index_merge(索引合并优化),
unique_subquery(in的后面是一个查询主键字段的子查询),
index_subquery(类似unique_subquery,主要是in的后面是查询非唯一索引字段的子查询),
range(单表中的范围查询),
index(对于当前的每一行,都通过查询索引来得到数据),
all(对于当前的每一行,都通过全表扫描来得到数据))

possible_keys: 表示查询时,可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
rows:扫描行的数量
Extra:执行情况的说明和描述


通过上面的思路进行分析和定位SQL语句有可能出现的低效率的情况。并且进行解决。下篇我讲叙述关于SQL优化之索引的问题。
分享到:
评论

相关推荐

    sqlserver优化笔记

    ### SQL Server 优化知识点 #### 一、SQL性能优化技巧 **1.1 查询的模糊匹配** - **问题描述**:使用 `LIKE '%parm1%'` 的查询方式会导致索引失效,降低查询效率。 - **解决方案**: - **前端改进**:改变用户...

    oracle 优化笔记

    在Oracle数据库管理中,SQL优化是一项至关重要的任务,它直接影响到数据库系统的运行效率和..."Oracle SQL优化笔记.doc"很可能是对此类知识的详细整理和实例分析,对于理解和实践Oracle数据库优化具有很高的参考价值。

    JAVA 与 Sql学习笔记

    此外,笔记还涵盖了存储过程中的参数传递,特别是传入数组的方法,以及SQL优化的一些关键点。 1. **FORALL 用法**: FORALL语句是一种在PL/SQL中批量处理数据的方式,它可以避免为集合中的每个元素单独执行SQL语句...

    mysql优化笔记+资料

    以下是一份详细的MySQL优化笔记,涵盖了多个方面: 一、查询优化 1. 使用索引:为经常用于搜索的列创建索引可以显著加快查询速度。B树和哈希索引是最常见的类型,适用于不同的查询场景。 2. 避免全表扫描:尽量使用...

    sql优化全套视频 sql优化必备 适合初中级开发人员

    sql优化视频 学习sql优化必备 适合初中级开发人员 包括笔记 视频 脚本

    SQL2005学习笔记

    《SQL2005学习笔记》是一份深入探讨SQL Server 2005核心概念、功能及优化策略的宝贵资料。SQL Server 2005是微软推出的一款强大的关系型数据库管理系统,它在数据存储、处理和分析方面具有广泛的应用。这份笔记旨在...

    SQL Server精华 (CHM)_sqlserver_SQLServer笔记_

    《SQL Server精华 (CHM)_sqlserver_SQLServer笔记_》是一部综合性的SQL Server学习资源,旨在帮助用户深入理解和掌握Microsoft SQL Server数据库管理系统的核心概念、功能和最佳实践。这部笔记涵盖了一系列关键知识...

    高效SQL学习笔记

    三、SQL优化 1. 索引:提高查询速度的关键,理解B树和哈希索引的工作原理,以及如何创建和管理索引。 2. 查询优化:避免全表扫描,合理使用WHERE子句和索引,减少子查询和关联操作。 3. 分区表:对大数据量表进行...

    oracle_sql笔记

    这篇笔记主要涵盖了Oracle SQL的基础知识、高级特性和实际应用技巧。 一、基础SQL语法 Oracle SQL遵循标准的SQL语法,包括SELECT、INSERT、UPDATE、DELETE四大语句。用于查询数据的基本结构是SELECT字段列表FROM...

    SQL SERVER 2008 学习笔记:日常维护、深入管理、性能优化.part1/2

    SQL SERVER 2008 学习笔记:日常维护、深入管理、性能优化。

    VC++ SQL 学习笔记

    10. **数据库性能优化**:如索引策略、查询优化器的工作原理,以及如何编写高效的SQL语句。 在VC++中,我们还需要了解如何处理结果集,如创建`CRecordset`对象来遍历和操作查询结果。此外,笔记可能还会讨论如何...

    20170909学习sql笔记

    标题“20170909学习sql笔记”表明这是一个关于SQL学习的资料,可能包含了一天的学习记录或者一个教程的集合。SQL,全称Structured Query Language,是用于管理和处理关系数据库的标准语言。这个标题暗示我们将探讨...

    SQL语言基础学习笔记

    SQL语言基础学习笔记 SQL,全称为“结构化查询语言”(Structured Query Language),是数据库管理系统的核心语言,用于管理和操作关系型数据库。SQL的特点包括: 1. **大小写不敏感**:SQL语句在实际执行时,对大...

    SQL学习笔记+记录sql各种应用

    这份“SQL学习笔记+记录sql各种应用”涵盖了SQL的基础概念、语法以及在实际工作中的多种应用。 一、SQL基础 1. 数据库与表:SQL中的数据库是由一个或多个表组成的集合,表由行和列构成,每一行代表一条记录,每一列...

    C#,UML ,sql学习笔记

    综上所述,这份“C#,UML,sql学习笔记”涵盖了C#编程中的核心概念,如权限管理、多线程,以及数据库管理和设计的高级技巧SQL优化,还包括了软件工程中重要的设计和规划工具UML。学习这些内容不仅可以提升个人的技术...

    Microsoft.SQL.Server.2008.学习笔记:日常维护、深入管理、性能优化.part2.rar

    Microsoft.SQL.Server.2008.学习笔记:日常维护、深入管理、性能优化.part2.rar; 中文版; 第二部分(共两部分)

    T-SQL课堂笔记

    ### T-SQL课堂笔记知识点详解 #### 创建架构与表结构 在T-SQL中,首先创建了一个名为`S_T`的架构,这是SQL Server数据库中用于组织对象如表、存储过程等的一种方式。随后,创建了三个表:`Student`、`Course`以及`...

    sql server笔记

    SQL Server是微软公司推出的一款关系型数据库管理系统,广泛应用于企业级数据存储、管理和分析。它提供了强大的数据处理能力,支持事务处理、数据仓库、数据挖掘等多种功能,是IT行业中不可或缺的工具之一。以下是对...

    c#,java,html,sqlsever记笔记软件

    描述中提到的"有我自己的一些SQL Server笔记,可自己写笔记"意味着这个压缩包可能包含了个人对SQL Server数据库管理系统的理解、实践经验以及一个可以用来创建和编辑笔记的平台。 首先,让我们从C#开始。C#是一种由...

    sql server 2008笔记

    在维护和优化SQL Server 2008时,我们需要注意以下几个关键知识点: 1. **RAID配置**: - RAID10:提供高读写速度和数据冗余,但磁盘利用率相对较低,适合存放对性能要求极高的日志文件。 - RAID5:适用于读取...

Global site tag (gtag.js) - Google Analytics