mysql 语句优化查询

最新推荐文章于 2024-09-25 14:34:16 发布

lgb934

最新推荐文章于 2024-09-25 14:34:16 发布

阅读量402

点赞数

分类专栏： hibernate 文章标签： mysql null sql table subquery 优化

本文链接：https://blog.csdn.net/lgb934/article/details/5579158

版权

hibernate 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

我用的是mysql5.0，

我程序中用到了一句sql是

SQL code

   
   
    
    
    
    select
    
     
    
    count
    
    (
    
    DISTINCT
    
     id) 
    
    from
    
     Policyinfo p 
    
    where
    
     p.filename 
    
    not
    
     
    
    in
    
    
(
    
    select
    
     p2.filename 
    
    from
    
     Policyreadinfo p2 
    
    where
    
     p2.reader
    
    =
    
    '
    
    test
    
    '
    
    )

但是每次在程序中每次执行这句sql时网页就特慢，要10多秒，而这2个表的记录行数也就1000多而已，我把这句sql单独拿到SQLyog Enterprise里执行也很慢，和网页的执行时间差不多。我想在policyreadinfo表里建个索引，但是每次我执行建立索引的sql时，MYSQL就会停掉，建不了索引。

我用的到的结果如下：
"id", "select_type", "table","type","possible_keys","key","key_len","ref","rows","Extra"
"1", "PRIMARY", "p", "ALL", /N, /N, /N, /N, "1715","Using where"
"2", "DEPENDENT SUBQUERY","p2", "ALL", /N, /N, /N, /N, "4196","Using where"

其中表Policyinfo结构为

SQL code

   
   
    
    
    
    

    
    CREATE
    
     
    
    TABLE
    
     `policyinfo` (
  `id` 
    
    int
    
    (
    
    11
    
    ) 
    
    NOT
    
     
    
    NULL
    
     auto_increment,
  `filenum` 
    
    varchar
    
    (
    
    50
    
    ) 
    
    default
    
     
    
    NULL
    
    ,
  `filename` 
    
    varchar
    
    (
    
    100
    
    ) 
    
    default
    
     
    
    NULL
    
    ,
  `filecontent` longtext,
  `writetime` 
    
    datetime
    
     
    
    default
    
     
    
    NULL
    
    ,
  `writer` 
    
    varchar
    
    (
    
    100
    
    ) 
    
    default
    
     
    
    NULL
    
    
  
    
    PRIMARY
    
     
    
    KEY
    
      (`id`)
) ENGINE
    
    =
    
    InnoDB 
    
    DEFAULT
    
     CHARSET
    
    =
    
    utf8;

Policyreadinfo 表的结构是

SQL code

   
   
    
    
    
    


    
    CREATE
    
     
    
    TABLE
    
     `policyreadinfo` (
  `id` 
    
    int
    
    (
    
    11
    
    ) 
    
    NOT
    
     
    
    NULL
    
     auto_increment,
  `filename` 
    
    varchar
    
    (
    
    500
    
    ) 
    
    default
    
     
    
    NULL
    
    ,
  `reader` 
    
    varchar
    
    (
    
    100
    
    ) 
    
    default
    
     
    
    NULL
    
    ,
  `readtime` 
    
    datetime
    
     
    
    default
    
     
    
    NULL
    
    ,
  
    
    PRIMARY
    
     
    
    KEY
    
      (`id`)
) ENGINE
    
    =
    
    InnoDB 
    
    DEFAULT
    
     CHARSET
    
    =
    
    utf8;


   
   
这是我在论坛上提出的问题。源文链接：http://topic.csdn.net/u/20100325/10/801d54f6-121b-4431-873d-e061dc60c6a6.html

后来经过我的测试，终于早到解决办法啦。在2个表filename上分别建立索引。之前也有想过这个办法，但是不知道为什么一执行建立索引的SQL，MYSQL就会死掉。所以后来就没有尝试这种方法啦。

后来在本机测试发现建立索引后的查询速度是建立前的N倍。

于是就修改policyreadinfo在他这里加一个policyinfo_id 的字段保存policyinfo的id字段。然后再在policyinfo_id上建立索引。问题终于解决了。呵呵

总的优化SQL语句是

-- 增加Id 字段
ALTER TABLE policyreadinfo ADD `policyinfo_id` int(11);

-- 更新policyreadinfo 表，有些字段为null是因为他把政策名字改了
update policyreadinfo t2 set policyinfo_id=(
select t1.id as 'policyinfo_id' from policyinfo t1 where t1.filename = t2.filename limit 1
);

-- 删除ID为空的记录
DELETE from policyreadinfo  where policyinfo_id is null
-- 建立索引
ALTER TABLE `policyreadinfo` ADD INDEX ( `policyinfo_id` );