mysql排序显示行数的语句_MySQL中sql语句count（*），orderby，随机数据展示。

count(*)这么慢，我该怎么办？

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；

而 InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

这里需要注意的是这是说没有过滤条件的 count(*)，如果加了 where 条件的话，MyISAM 表也是不能返回得这么快的。

数据很多的时候count(*)会很慢，所以我们可以把一个表数据总量存在一个字段里。不同的count方法的速度区别

count(*)、count(主键 id) 和 count(1)，count(字段)的性能区别

count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值。

所以，count(*)、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数；而 count(字段)，则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。

对于 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。

对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

对于 count(字段) 来说：

如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；

如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。

但是 count(*) 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。

看到这里，你一定会说，优化器就不能自己判断一下吗，主键 id 肯定非空啊，为什么不能按照 count(*) 来处理，多么简单的优化啊。

所以结论是：按照效率排序的话，count(字段)

“order by”是怎么工作的？

Extra 这个字段中的“Using filesort”表示的就是需要排序，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。

全字段排序

select city,name,age from t where city='杭州' order by name limit 1000 ;

索引为city

通常情况下，这个语句执行流程如下所示 ：

初始化 sort_buffer，确定放入要查询的字段；

从索引 找到第一个满足 条件的主键 id；

到主键 id 索引取出整行，取要查询的的值，存入 sort_buffer 中；

从索引取下一个记录的主键 id；

重复步骤 3、4 直到 索引 的值不满足查询条件为止；

对 sort_buffer 中的数据按照要排序的字段做快速排序；

按照排序结果取要返回的行数返回给客户端。

图中“按 name 排序”这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数 sort_buffer_size。

sort_buffer_size，就是 MySQL 为排序开辟的内存(sort_buffer)的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

内存放不下时，就需要使用外部排序，外部排序一般使用归并排序算法。可以这么简单理解，MySQL 将需要排序的数据分成 12 份，每一份单独排序后存在这些临时文件中。然后把这 12 个有序文件再合并成一个有序的大文件。

rowid 排序

在上面这个算法过程里面，只对原表的数据读了一遍，剩下的操作都是在 sort_buffer 和临时文件中执行的。但这个算法有一个问题，就是如果查询要返回的字段很多的话，那么 sort_buffer 里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。

新的算法放入 sort_buffer 的字段，只有要排序的列(即 name 字段)和主键 id。

但这时，排序的结果就因为少了 city 和 age 字段的值，不能直接返回了，整个执行流程就变成如下所示的样子：

初始化 sort_buffer，确定放入两个字段，即 name 和 id；

从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；

到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；

从索引 city 取下一个记录的主键 id；

重复步骤 3、4 直到不满足 city='杭州’条件为止，也就是图中的 ID_Y；

对 sort_buffer 中的数据按照字段 name 进行排序；

遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。

最后的“结果集”是一个逻辑概念，实际上 MySQL 服务端从排序后的 sort_buffer 中依次取出 id，然后到原表查到 city、name 和 age 这三个字段的结果，不需要在服务端再耗费内存存储结果，是直接返回给客户端的。

全字段排序 VS rowid 排序

如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。

如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。

这也就体现了 MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。

排序优化

并不是所有的 order by 语句，都需要排序操作的。从上面分析的执行过程，我们可以看到，MySQL 之所以需要生成临时表，并且在临时表上做排序操作，其原因是原来的数据都是无序的。

建立联合索引

city和name建立联合索引，保证从 city 这个索引上取出来的行，天然就是按照 name 递增排序的话，就可以不用再排序了。

建立覆盖索引会更快 覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据。

如何正确地显示随机消息？

正常时候都会用下面的sql

mysql> select word from words order by rand() limit 3;

 Extra 字段显示 Using temporary，表示的是需要使用临时表；Using filesort，表示的是需要执行排序操作。

因此这个 Extra 的意思就是，需要临时表，并且需要在临时表上排序。

order by rand() 使用了内存临时表，内存临时表排序的时候使用了 rowid 排序方法。

这种方式效率很低，所有通常用查到一个随机数据后，然后查三次。效率会提升很高。

SELECT a.id,a.cover_list FROM `mini_videos` AS a JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `mini_videos`)-(SELECT MIN(id) FROM `mini_videos`))+(SELECT MIN(id) FROM `mini_videos`)) AS id) AS t2 WHERE a.id >= t2.id ORDER BY a.id LIMIT 1;

mysql> select count(*) into @C from t;

set @Y1 = floor(@C * rand());

set @Y2 = floor(@C * rand());

set @Y3 = floor(@C * rand());

select * from t limit @Y1，1； // 在应用代码里面取 Y1、Y2、Y3 值，拼出 SQL 后执行

select * from t limit @Y2，1；

select * from t limit @Y3，1；

标签：count,orderby,buffer,sort,内存,MySQL,排序,id

来源： https://blog.csdn.net/Srodong/article/details/93877959