首頁 資料庫 mysql教程 错误使用MySQL前缀索引导致的慢查询_MySQL

错误使用MySQL前缀索引导致的慢查询_MySQL

Jun 01, 2016 pm 01:34 PM
mysql 專案

bitsCN.com

前端时间跟一个DB相关的项目,alanc反馈有一个查询,使用索引比不使用索引慢很多倍,有点毁三观。所以跟进了一下,用explain,看了看2个查询不同的结果。

不用索引的查询的时候结果如下,实际查询中速度比较块。

mysql> explain select * from rosterusers limit 10000,3 ;

+----+-------------+-------------+------+---------------+------+---------+------+---------+-------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows    | Extra |
+----+-------------+-------------+------+---------------+------+---------+------+---------+-------+
|  1 | SIMPLE      | rosterusers | ALL  | NULL          | NULL | NULL    | NULL | 2010066 |       |
+----+-------------+-------------+------+---------------+------+---------+------+---------+-------+

 

 

而使用索引order by的查询结果如下,速度反而慢的惊人。

mysql> explain select * from rosterusers order by username limit 10000,3 ;

+----+-------------+-------------+------+---------------+------+---------+------+---------+----------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows    | Extra          |
+----+-------------+-------------+------+---------------+------+---------+------+---------+----------------+
|  1 | SIMPLE      | rosterusers | ALL  | NULL          | NULL | NULL    | NULL | 2010087 | Using filesort |
+----+-------------+-------------+------+---------------+------+---------+------+---------+----------------+

 

区别在于,使用索引查询的Extra变成了,Using filesort。居然用了使用外部文件进行排序。这个当然慢了。

但数据表上在username,的确是有索引的。怎么会反而要Using filesort?

看了一下数据表定义。是一个开源聊天服务器ejabberd的一张表。初看以为主键i_rosteru_user_jid是username,和jid的联合索引,那么使用order by username时应该是可以使用到索引才对呀?

CREATE TABLE `rosterusers` (

`username` varchar(250) NOT NULL,

`jid` varchar(250) NOT NULL,

UNIQUE KEY `i_rosteru_user_jid` (`username`(75),`jid`(75)),

KEY `i_rosteru_jid` (`jid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

仔细检查突然发现其主键定义,不是定义的完整的主键名称,而跟了一个75的长度描述,稍稍一愣,原来用的是前缀索引,而不是整个字段都是索引。(我的记忆里面InnoDB还不支持这玩意,估计是4.0后什么版本加入的),前缀索引就是将数据字段中前面N个字节作为索引的一种方式。。

发现了这个问题后,我们开始怀疑慢查询和这个索引有关,前缀索引的主要用途在于有时字段过程,而MySQL支持的很多索引长度是有限制的。

首先不带order by 的limit 这种查询,本质可能还是和主键相关的,因为MySQL 的INNODB的操作实际都是依靠主键的(即使你没有建立,系统也会有一个默认的),而limit这种查询,使用主键是可以加快速度,(explain返回的rows 应该是一个参考值),虽然我没有看见什么文档明确的说明过这个问题,但从不带order by 的limit 查询的返回结果基本可以证明这点。

但当我们使用order by username的时候,由于希望使用的是username的排序,而不是username(75)的排序,但实际索引是前缀索引,不是完整字段的索引。所以反而导致了order by的时候完全无法利用索引了。(我在SQL语句里面增加强制使用索引i_rosteru_user_jid也不起作用)。而其实使用中,表中的字段username 连75个都用不到,何况定义的250的长度。完全是自己折腾导致的麻烦。由于这是其他产品的表格,我们无法更改,暂时只能先将就用不不带排序的查询讲究。

总结:

  • 前缀索引,并不是一个万能药,他的确可以帮助我们对一个写过长的字段上建立索引。但也会导致排序(order by ,group by)查询上都是无法使用前缀索引的。
  • 任何时候,对于DB Schema定义,合理的规划自己的字段长度,字段类型都是首要的事情。

【本文作者是雁渡寒潭,本着自由的精神,你可以在无盈利的情况完整转载此文档,转载时请附上BLOG链接:http://www.cnblogs.com/fullsail/ 或者http://blog.csdn.net/fullsail,否则每字一元,每图一百不讲价。对Baidu文库,360doc加价一倍】

bitsCN.com
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn

熱門文章

R.E.P.O.能量晶體解釋及其做什麼(黃色晶體)
1 週前 By 尊渡假赌尊渡假赌尊渡假赌
倉庫:如何復興隊友
3 週前 By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒險:如何獲得巨型種子
3 週前 By 尊渡假赌尊渡假赌尊渡假赌

熱門文章

R.E.P.O.能量晶體解釋及其做什麼(黃色晶體)
1 週前 By 尊渡假赌尊渡假赌尊渡假赌
倉庫:如何復興隊友
3 週前 By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒險:如何獲得巨型種子
3 週前 By 尊渡假赌尊渡假赌尊渡假赌

熱門文章標籤

記事本++7.3.1

記事本++7.3.1

好用且免費的程式碼編輯器

SublimeText3漢化版

SublimeText3漢化版

中文版,非常好用

禪工作室 13.0.1

禪工作室 13.0.1

強大的PHP整合開發環境

Dreamweaver CS6

Dreamweaver CS6

視覺化網頁開發工具

SublimeText3 Mac版

SublimeText3 Mac版

神級程式碼編輯軟體(SublimeText3)

PHP 的大數據結構處理技巧 PHP 的大數據結構處理技巧 May 08, 2024 am 10:24 AM

PHP 的大數據結構處理技巧

如何優化 PHP 中的 MySQL 查詢效能? 如何優化 PHP 中的 MySQL 查詢效能? Jun 03, 2024 pm 08:11 PM

如何優化 PHP 中的 MySQL 查詢效能?

如何在 PHP 中使用 MySQL 備份和還原? 如何在 PHP 中使用 MySQL 備份和還原? Jun 03, 2024 pm 12:19 PM

如何在 PHP 中使用 MySQL 備份和還原?

如何使用 PHP 插入資料到 MySQL 表? 如何使用 PHP 插入資料到 MySQL 表? Jun 02, 2024 pm 02:26 PM

如何使用 PHP 插入資料到 MySQL 表?

Java 枚舉類型在資料庫中的應用場景有哪些? Java 枚舉類型在資料庫中的應用場景有哪些? May 05, 2024 am 09:06 AM

Java 枚舉類型在資料庫中的應用場景有哪些?

如何修復 MySQL 8.4 上的 mysql_native_password 未載入錯誤 如何修復 MySQL 8.4 上的 mysql_native_password 未載入錯誤 Dec 09, 2024 am 11:42 AM

如何修復 MySQL 8.4 上的 mysql_native_password 未載入錯誤

如何在 PHP 中使用 MySQL 預存程序? 如何在 PHP 中使用 MySQL 預存程序? Jun 02, 2024 pm 02:13 PM

如何在 PHP 中使用 MySQL 預存程序?

如何使用 PHP 建立 MySQL 表? 如何使用 PHP 建立 MySQL 表? Jun 04, 2024 pm 01:57 PM

如何使用 PHP 建立 MySQL 表?

See all articles