MySQL限制性能 [英] Mysql Limit Performance

问题描述

我在mysql中有一个大表，大约有100万条记录.

I have a large table in mysql, about 1 million records.

我在where子句和顺序中使用带有不同参数的动态查询，因此我无法使用某些代码，例如AND id > 34000 LIMIT 10

I'm using a dynamic query with different parameters in where clause and order, so i cant use some code like AND id > 34000 LIMIT 10

我在WHERE和LIMIT和ORDER的字段中都有索引，但是索引并不能单独解决.

I have index on my fields in WHERE and LIMIT and ORDER but index doesn't help alone.

与LIMIT 34000, 10相比，我需要更好的方法，有没有办法消除偏移延迟?

I need a better way than LIMIT 34000, 10, Is there any way to slove offset delay?

我放了我的表模式，但是我只是复制了更多可用的字段而没有任何索引，因为我使用的是动态查询.

I put my table schema but i just copy more usable field without any index, because i'm using dynamic queries.

CREATE TABLE IF NOT EXISTS `p_apartmentbuy` (
  `property_id` mediumint(8) unsigned NOT NULL,
  `dateadd` int(10) unsigned NOT NULL,
  `sqm` smallint(5) unsigned NOT NULL,
  `sqmland` smallint(5) unsigned NOT NULL,
  `age` tinyint(2) unsigned NOT NULL,
  `price` bigint(12) unsigned NOT NULL,
  `pricemeter` int(11) unsigned NOT NULL,
  `floortotal` tinyint(3) unsigned NOT NULL,
  `floorno` tinyint(3) unsigned NOT NULL,
  `unittotal` smallint(4) unsigned NOT NULL,
  `unitthisfloor` tinyint(3) unsigned NOT NULL,
  `room` tinyint(1) unsigned NOT NULL,
  `parking` tinyint(1) unsigned NOT NULL,
  `renovate` tinyint(1) unsigned NOT NULL,
  `address` varchar(255) COLLATE utf8_general_ci NOT NULL,
  `describe` varchar(500) COLLATE utf8_general_ci NOT NULL,
  `featured` tinyint(1) unsigned NOT NULL,
  `l_location_id` smallint(5) unsigned NOT NULL,
  `l_city_id` smallint(4) unsigned NOT NULL,
  `pf_furnished_id` tinyint(2) unsigned NOT NULL,
  PRIMARY KEY (`property_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

推荐答案

具有1个工厂记录的表的问题将不是AND id > 34000 LIMIT 10或LIMIT 34000, 10，这将取决于结构和查询的其余部分.即，您需要索引，PK，FK来加快查询速度，此外，Order by可能会减慢其速度，请执行搜索like '%text%'会使查询变慢.这也取决于表的引擎

the problem with a table with 1 mill records wont be the AND id > 34000 LIMIT 10 or LIMIT 34000, 10 that will up to the Structure and the rest of the query. I.E, you need index, PK, FK to speed up the query, beside that an Order by probably will slow it down, make search like '%text%' it will make your query SLOW. Also it's up to the table's Engine

因此，不要期望更改限制10会有很大的不同.有两种工具可以帮助您确定更好"的查询，但是并非所有查询都能以相同的方式工作，因此不要指望最佳解决方案"，因为它不存在.

So don't expect that changing limit 10 will make a huge difference. There are a couple of tool that will help you to determinate a 'better' query, but not all queries works as the same so don't expect the "best solution" because it doesn't exists.

您可以使用Show create table或Describe select ......或explain来查看发生了什么，或者使用命令benchmark来查看要对其进行改进的功能的大概时间

You can use Show create table or Describe select ...... or explain to see what's going on, or use the command benchmark to see the approximate time of a function that you are applying to improve it

一些用于MySQL的工具 我建议您看一下该程序，该程序将帮助您实现这部分性能.

Some tools for MySQL I'll recommend you to take a look to this program that will help you with this part of performance.

• Mysqlslap(就像基准测试一样，但是您可以自定义更多结果).
• SysBench(测试CPU性能，I/O性能，互斥争用，内存速度，数据库性能).
• Mysqltuner(通过它您可以分析常规统计信息，存储引擎统计信息，性能指标).
• mk-query-profiler(对SQL语句进行分析).
• mysqldumpslow(很高兴知道女巫的查询正在引起问题).

这篇关于MySQL限制性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！