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

我不是女神ヾ 2022-03-22 10:23 447阅读 0赞

**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`。