×

首页>讲师原创专区

教师图片

史跃东老师

38文章总数

97953总阅读数

查看Ta的文章>>

Effective MySQL之sql优化——DBA速成篇02

发布于:2018年10月27日 浏览:3000次 2

1.2 如何优化查询


接上一篇。

基于我们的Oracle知识,第一反应,显然就是加个索引。

mysql> alter table t add index (id);

Query OK, 0 rows affected (2.44 sec)

Records: 0  Duplicates: 0  Warnings: 0


注意:

加这个索引的时间比我们前面执行查询的时间还长。在实际的生产系统中,加索引是个需要慎重的事情。

因为我们需要考虑很多因素。在创建索引期间,由于alter table是阻塞操作,因此所有为表添加和修改数据的额外请求都将被阻塞。根据其他DML语句的执行顺序,此时select语句也会被阻塞而无法完成。如果表更大一些,一个alter语句要花费的时间可能就更长。

另外一个需要考虑的因素,就是在一个表有多个索引的情况下DML语句的性能开销。


这里稍微说一下,在oracle中,我们创建索引或者alter table时,基本不需要考虑在执行操作时会阻塞表上其他DML操作的问题,但是在MySQL中就不同了。往往一个alter操作会相当耗时并且阻塞其他操作。不过现在可以借助于Percona toolkit中的一些工具来实现在线alter table。


创建完索引以后,再重新执行查询:

mysql> select * from t where id = 999001;     

+--------+--------+

| id     | name   |

+--------+--------+

| 999001 | 999001 |

+--------+--------+

1 row in set (0.00 sec)

速度明显改善了,再查看一下执行计划:

mysql> explain select * from t where id = 999001 \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t

   partitions: NULL

         type: ref

possible_keys: id

          key: id

      key_len: 5

          ref: const

         rows: 1

     filtered: 100.00

        Extra: NULL

1 row in set, 1 warning (0.00 sec)

优化器现在使用了id列上的索引,rows也变为了1.



1.3 正确的做法


给表添加索引的确有很多优点。但是在添加索引之前,通常至少应该做如下两项检查:

1,验证表的现有结构;

2,确认表的大小。


mysql> show create table t \G

*************************** 1. row ***************************

       Table: t

Create Table: CREATE TABLE `t` (

  `id` int(11) DEFAULT NULL,

  `name` varchar(30) DEFAULT NULL,

  KEY `id` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.01 sec)


mysql> show table status like 't' \G

*************************** 1. row ***************************

           Name: t

         Engine: InnoDB

        Version: 10

     Row_format: Dynamic

           Rows: 933052

 Avg_row_length: 39

    Data_length: 37289984

Max_data_length: 0

   Index_length: 0

      Data_free: 4194304

 Auto_increment: NULL

    Create_time: 2016-11-09 15:24:33

    Update_time: NULL

     Check_time: NULL

      Collation: latin1_swedish_ci

       Checksum: NULL

 Create_options: 

        Comment: 

1 row in set (0.00 sec)


1.4 其他


优化sql的正确方法,除了创建索引之外,也包括理解和验证该sql语句以及与表相关的sql的业务需求。某些时候,从业务需求出发,调整sql的实现逻辑,比如说添加更高选择率的过滤条件等等,往往会收到更好的优化效果。当然对于某些审核严格的生产系统而言,这样可能需要走比较漫长的流程。


本周热文

推荐专题

专栏图标 专栏图标 专栏图标 专栏图标 专栏图标 专栏图标

我赢职场APP
扫码立即下载

  • 微信图标官方公众号
    二维码扫描二维码
    关注东方瑞通官方公众号
    小图标
  • 微信图标PMP公众号
    二维码扫描二维码
    关注东方瑞通PMP公众号
    小图标
  • 微博图标新浪微博
    二维码扫描二维码
    关注东方瑞通新浪微博
    小图标
  • 微信图标客服小瑞
    二维码扫描二维码
    添加东方瑞通客服小瑞
    小图标

PMI, PMP, Project Management Professional, CAPM, PgMP, PfMP, PMI-ACP, PMI-RMP, PMI-SP, PMI-PBA and PMBOK are registered marks of the Project Management Institute, Inc.

Copyright © 2006-2018 东方瑞通(北京)咨询服务有限公司版权所有

京ICP备 13009094号 京公网安备 11010802014211号