【Mysql】给你100万条数据的一张表，你将如何查询优化？

author：咔咔

wechat：fangkangfk

1.两种查询引擎查询速度（myIsam 引擎 ）

InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行。

MyISAM只要简单的读出保存好的行数即可。

注意的是，当count(*)语句包含 where条件时，两种表的操作有些不同，InnoDB类型的表用count(*)或者count(主键)，加上where col 条件。其中col列是表的主键之外的其他具有唯一约束索引的列。这样查询时速度会很快。就是可以避免全表扫描。

总结：

mysql 在300万条数据（myisam引擎）情况下使用 count(*) 进行数据总数查询包含条件（正确设置索引）运行时间正常。对于经常进行读取的数据我们建议使用myIsam引擎。

2.百万数据下mysql分页问题

在开发过程中我们经常会使用分页，核心技术是使用limit进行数据的读取，在使用limit进行分页的测试过程中，得到以下数据：

  

   

    

     

    
    

     

      select * from news order by id desc limit 0,10
     
    
   

    

     

    
    

     

      耗时0.003秒
     
    
   

    

     

    
    

     

      select * from news order by id desc limit 10000,10
     
    
   

    

     

    
    

     

      耗时0.058秒
     
    
   

    

     

    
    

     

      select * from news order by id desc limit 100000,10 
     
    
   

    

     

    
    

     

      耗时0.575秒
     
    
   

    

     

    
    

     

      select * from news order by id desc limit 1000000,10
     
    
   

    

     

    
    

     

      耗时7.28秒
     
    
  
 

我们惊讶的发现mysql在数据量大的情况下分页起点越大查询速度越慢，100万条起的查询速度已经需要7秒钟。这是一个我们无法接受的数值！

改进方案 1

  

   

    

     

    
    

     

      select * from news 
     
    
   

    

     

    
    

     

      where id >  (select id from news order by id desc  limit 1000000, 1)
     
    
   

    

     

    
    

     

      order by id desc 
     
    
   

    

     

    
    

     

      limit 0,10
     
    
  
 

查询时间 0.365秒，提升效率是非常明显的！！原理是什么呢？？？

我们使用条件对id进行了筛选，在子查询 (select id from news order by id desc limit 1000000, 1) 中我们只查询了id这一个字段比起select * 或 select 多个字段 节省了大量的查询开销！

改进方案2

适合id连续的系统，速度极快！

  

   

    

     

    
    

     

      select * from news 
     
    
   

    

     

    
    

     

      where id  between 1000000 and 1000010 
     
    
   

    

     

    
    

     

      order by id desc
     
    
  
 

不适合带有条件的、id不连续的查询。速度非常快！

3. 百万数据下mysql条件查询、分页查询的注意事项

接上一节，我们加上查询条件：

  

   

    

     

    
    

     

      select id from news 
     
    
   

    

     

    
    

     

      where cate = 1
     
    
   

    

     

    
    

     

      order by id desc 
     
    
   

    

     

    
    

     

      limit 500000 ,10 
     
    
  
 

查询时间 20 秒

好恐怖的速度！！利用上面方案进行优化：

  

   

    

     

    
    

     

      select * from news
     
    
   

    

     

    
    

     

      where cate = 1 and id > (select id from news where cate = 1 order by id desc limit 500000,1 ) 
     
    
   

    

     

    
    

     

      order by id desc 
     
    
   

    

     

    
    

     

      limit 0,10 
     
    
  
 

查询时间 15 秒

优化效果不明显，条件带来的影响还是很大！在这样的情况下无论我们怎么去优化sql语句就无法解决运行效率问题。那么换个思路：建立一个索引表，只记录文章的id、分类信息，我们将文章内容这个大字段分割出去。

表 news2 [ 文章表 引擎 myisam 字符集 utf-8 ]

-------------------------------------------------

id int 11 主键自动增加

cate int 11 索引

在写入数据时将2张表同步，查询是则可以使用news2 来进行条件查询：

  

   

    

     

    
    

     

      select * from news
     
    
   

    

     

    
    

     

      where cate = 1 and id > (select id from news2 where cate = 1 order by id desc limit 500000,1 ) 
     
    
   

    

     

    
    

     

      order by id desc 
     
    
   

    

     

    
    

     

      limit 0,10
     
    
  
 

注意条件 id > 后面使用了news2 这张表！

运行时间 1.23秒，我们可以看到运行时间缩减了近20倍！！数据在10万左右是查询时间可以保持在0.5秒左右，是一个逐步接近我们能够容忍的值！

但是1秒对于服务器来说依然是一个不能接受的值！！还有什么可以优化的办法吗？？我们尝试了一个伟大的变化：

将 news2 的存储引擎改变为innodb，执行结果是惊人的！

  

   

    

     

    
    

     

      select * from news
     
    
   

    

     

    
    

     

      where cate = 1 and id > (select id from news2 where cate = 1 order by id desc limit 500000,1 ) 
     
    
   

    

     

    
    

     

      order by id desc 
     
    
   

    

     

    
    

     

      limit 0,10
     
    
  
 

只需要 0.2秒，非常棒的速度。

MySQL性能优化的一些经验

a.为查询优化你的查询

大多数的MySQL服务器都开启了查询缓存。这是提高性能最有效的方法之一，而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候，这些查询结果会被放到一个缓存中，这样，后续的相同的查询就不用操作表而直接访问缓存结果了。

这里最主要的问题是，对于程序员来说，这个事情是很容易被忽略的。因为，我们某些查询语句会让MySQL不使用缓存。

请看下面的示例：

  

   

    

     

    
    

     

      // 查询缓存不开启
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      $r = mysql_query("SELECT username FROM user WHERE     signup_date >= CURDATE()");
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      // 开启查询缓存
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      $today = date("Y-m-d");
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      $r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");
     
    
  
 

上面两条SQL语句的差别就是 CURDATE() ，MySQL的查询缓存对这个函数不起作用。所以，像 NOW() 和 RAND() 或是其它的诸如此类的SQL函数都不会开启查询缓存，因为这些函数的返回是会不定的易变的。所以，你所需要的就是用一个变量来代替MySQL的函数，从而开启缓存。

b.学会使用EXPLAIN

使用EXPLAIN关键字可以让你知道MySQL是如何处理你的SQL语句的。

select id, title, cate from news where cate = 1
 

发现查询缓慢，然后在cate字段上增加索引，则会加快查询

c.当只要一行数据时使用LIMIT 1

当你查询表的有些时候只需要一条数据，请使用 limit 1。

d.正确的使用索引

索引并不一定就是给主键或是唯一的字段。如果在你的表中，有某个字段你总要会经常用来做搜索、拍下、条件，那么，请为其建立索引吧。

e.不要ORDER BY RAND()

效率很低的一种随机查询。

f.避免SELECT *

从数据库里读出越多的数据，那么查询就会变得越慢。并且，如果你的数据库服务器和WEB服务器是两台独立的服务器的话，这还会增加网络传输的负载。必须应该养成一个需要什么就取什么的好的习惯。

g.使用 ENUM 而不是 VARCHAR

ENUM 类型是非常快和紧凑的。在实际上，其保存的是 TINYINT，但其外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美。

如果你有一个字段，比如“性别”，“国家”，“民族”，“状态”或“部门”，你知道这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不是 VARCHAR。

h.使用 NOT NULL

除非你有一个很特别的原因去使用 NULL 值，你应该总是让你的字段保持 NOT NULL。这看起来好像有点争议，请往下看。

首先，问问你自己“Empty”和“NULL”有多大的区别（如果是INT，那就是0和NULL）？如果你觉得它们之间没有什么区别，那么你就不要使用NULL。（你知道吗？在 Oracle 里，NULL 和 Empty 的字符串是一样的！)

不要以为 NULL 不需要空间，其需要额外的空间，并且，在你进行比较的时候，你的程序会更复杂。 当然，这里并不是说你就不能使用NULL了，现实情况是很复杂的，依然会有些情况下，你需要使用NULL值。

文章来源: blog.csdn.net，作者：咔咔-，版权归原作者所有，如需转载，请联系作者。

原文链接：blog.csdn.net/fangkang7/article/details/86673078