MySQL增加变量性能 [英] Mysql Incrementing variable performance

问题描述

我正在寻找一种对mysql表中的项目进行排序的方法. 这是表格的简化版本

I has looking for a way to sort items in a mysql table. Here a simplified version of the table

sqlfiddle => http://sqlfiddle.com/#!2/78521b/3/0

sqlfiddle => http://sqlfiddle.com/#!2/78521b/3/0

CREATE TABLE IF NOT EXISTS `test_sort` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sort` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sort` (`sort`)
);
INSERT INTO `test_sort` (`id`,`sort`, `name`) VALUES
(1, 1, 'Joker'),
(2, 3, 'Queen of Spade'),
(3, 6, 'King of Heart'),
(4, 4, 'Ace of Diamond'),
(5, 17, 'Three of Clubs'),
(6, 60, 'Seven of Clubs'),
(7, 2, 'Ten of Spades'),
(8, 5, 'Ace of Heart');

因此，一旦用户对项目(卡片)进行了排序，我想在sort列上运行查询，以使其保持一致.

So once the items (cards) has been sorted by the user i want to run the query on the sort column so it remains consistent.

在这里找到解决方案: MySQL使用增量变量更新字段

Solution found here : MySQL update a field with an incrementing variable

SET @n=0;
UPDATE `test_sort` SET `sort` = @n := @n + 1 ORDER BY `sort`

问题:如果该查询用于数千(或数百万)条记录，该查询将如何起作用(性能明智)?

QUESTION: how this query would act (performance wise) if it was used on thousands (or millions) of records?

推荐答案

不要在表中存储sort；将其存储在单独的表中.此外，不要UPDATE该表，请重新创建它.此外，请使用以下内容来避免任何停机时间:

Don't store sort in the table; store it in a separate table. Furthermore, don't UPDATE that table, recreate it. Further-furthermore, use the following to avoid any downtime:

CREATE TABLE New SELECT ... -- generate new sort order
RENAME TABLE Sort TO Old, New To Sort;
DROP TABLE Old; 

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