MYSQL的随机查询的实现方法,mysql优化索引

mysql优化索引——Using filesort

MYSQL的随机查询的实现方法

MySQL的大小写敏感设置

 

 

 

用Explain分析SQL语句的时候,经常发现有的语句在Extra列会出现Using filesort,根据mysql官方文档对他的描述:

MYSQL的随机抽取实现方法。举个例子,要从tablename表中随机提取一条记录,大家一般的写法就是:SELECT * FROM tablename ORDER BY RAND() LIMIT 1。

在 MySQL 中,数据库和表对就于那些目录下的目录和文件。因而,操作系统的敏感性决定数据库和表命名的大小写敏感。这就意味着数据库和表名在 Windows 中是大小写不敏感的,而在大多数类型的 Unix 系统中是大小写敏感的。 

  www.2cto.com  

  www.2cto.com  

  www.2cto.com  

引用

但是,后来我查了一下MYSQL的官方手册,里面针对RAND()的提示大概意思就是,在ORDER BY从句里面不能使用RAND()函数,因为这样会导致数据列被多次扫描。但是在MYSQL 3.23版本中,仍然可以通过ORDER BY RAND()来实现随机。

奇怪的是列名与列的别名在所有的情况下均是忽略大小写的,而表的别名又是区分大小写的。 

MySQL must do an extra pass to find out how to retrieve the rows in sorted order. The sort is done by going through all rows according to the join type and storing the sort key and pointer to the row for all rows that match the WHERE clause.

 

 

 

但是真正测试一下才发现这样效率非常低。一个15万余条的库,查询5条数据,居然要8秒以上。查看官方手册,也说rand()放在ORDER BY 子句中会被执行多次,自然效率及很低。

要避免这个问题,你最好在定义数据库命名规则的时候就全部采用小写字母加下划线的组合,而不使用任何的大写字母。 

中文手册上翻译的很别扭:

You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times.

 

 

搜索Google,网上基本上都是查询max(id) * rand()来随机获取数据。

或者也可以强制以 -O lower_case_table_names=1 参数启动 mysqld(如果使用 --defaults-file=...my.cnf 参数来读取指定的配置文件启动 mysqld 的话,你需要在配置文件的 [mysqld] 区段下增加一行 lower_case_table_names=1)。这样MySQL 将在创建与查找时将所有的表名自动转换为小写字符(这个选项缺省地在 Windows 中为 1 ,在 Unix 中为 0。从 MySQL 4.0.2 开始,这个选项同样适用于数据库名)。 

引用

SELECT *

  www.2cto.com  

“Mysql需要额外的一次传递,以找出如何按排序顺序检索行,通过根据联接类型浏览所有行并为所有匹配where子句的行保存排序关键字和行的指针来完成排序,然后关键字被排序,并按排序顺序检索行。”

FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `table`)) AS id) AS t2

当你更改这个选项时,你必须在启动 mysqld 前首先将老的表名转换为小写字母。 

 

WHERE t1.id >= t2.id

 

 

ORDER BY t1.id ASC LIMIT 5;

换句话说,如果你希望在数据库里面创建表的时候保留大小写字符状态,则应该把这个参数置0: lower_case_table_names=1 。否则的话你会发现同样的sqldump脚本在不同的操作系统下最终导入的结果不一样(在Windows下所有的大写字符都变成小写了)。 

总的来说,Using filesort 是Mysql里一种速度比较慢的外部排序,如果能避免是最好的了,很多时候,我们可以通过优化索引来尽量避免出现Using filesort,从而提高速度。

 

 

 

但是这样会产生连续的5条记录。解决办法只能是每次查询一条,查询5次。即便如此也值得,因为15万条的表,查询只需要0.01秒不到。

在 MySQL 中,数据库和表对就于那些目录下的目录和文件。因而,操作系统的敏感性决定数据库和表命名的大小写敏感...

这里举个简单的例子:

 

 

下面的语句采用的是JOIN,mysql的论坛上有人使用

CREATE TABLE `testing` (

SELECT *

   `id` int(10) unsigned NOT NULL auto_increment,

FROM `table`

   `room_number` int(10) unsigned NOT NULL default '0',

WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` )

   PRIMARY KEY   (`id`),

ORDER BY id LIMIT 1;

   KEY `room_number` (`room_number`)

 

) ENGINE=MyISAM DEFAULT CHARSET=latin1

我测试了一下,需要0.5秒,速度也不错,但是跟上面的语句还是有很大差距。总觉有什么地方不正常。

 

 

  www.2cto.com  

于是我把语句改写了一下。

写个存储过程askwan,插入10万条测试数据

SELECT * FROM `table`

 

WHERE id >= (SELECT floor(RAND() * (SELECT MAX(id) FROM `table`)))

mysql> DELIMITER $$

ORDER BY id LIMIT 1;

本文由ca88手机版登录发布于亚洲城ca88手机版官网,转载请注明出处:MYSQL的随机查询的实现方法,mysql优化索引

TAG标签: ca88手机版登录
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。