Arash Mousavi's questions

在 MySQL 中，当我尝试通过此查询更改主键时：

 ALTER TABLE `resources` DROP PRIMARY KEY, ADD
 PRIMARY KEY (`role_id`, `category_id`, `lang`, `model`, `rule`);

我使用 MySQL root 用户执行此查询，但发生此错误：

#1878 - Temporary file write failure.

我的桌子设计是：

CREATE TABLE `resources` (
    `role_id` INT(11) UNSIGNED NOT NULL ,
    `category_id` SMALLINT(5) UNSIGNED NOT NULL DEFAULT 1 ,
    `lang` BINARY(2) NOT NULL ,
    `model` SMALLINT(5) UNSIGNED NOT NULL ,
    `rule` CHAR(4) NOT NULL ,
    PRIMARY KEY (`category_id`, `lang`, `model`, `rule`, `role_id`)
 )
 ENGINE = InnoDB;

该表大约有 2000 行。

我使用 MySQL 5.6

为什么会出现这个错误？

我应该怎么办？

我有这张桌子：

CREATE TABLE IF NOT EXISTS `usergroups` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `user_id` int(11) unsigned NOT NULL,
      `group_id` smallint(5) unsigned NOT NULL,
      PRIMARY KEY (`group_id`,`user_id`),
      KEY `id` (`id`),
      KEY `user_id` (`user_id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5496 ;

运行此查询时：

EXPLAIN SELECT `UserGroup`.`user_id` FROM   `usergroups` AS `UserGroup` WHERE  `UserGroup`.`group_id` = 1 limit 30

它的输出是：

1       SIMPLE        UserGroup      ref  PRIMARY  PRIMARY  2        const  543      Using index

我觉得不对劲！因为它影响了 543 行，但我认为它最多应该影响 30 行。是真的吗？

我有一个具有这种结构的表：

mysql> describe user_groups;

+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | int(11) unsigned     | NO   | MUL | NULL    | auto_increment |
| user_id  | int(11) unsigned     | NO   | PRI | NULL    |                |
| group_id | smallint(5) unsigned | NO   | PRI | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+

该表的主键是：PRIMARY(user_id, group_id) 我有一个简单的查询：

mysql> EXPLAIN SELECT `UserGroup`.`user_id` , `UserGroup`.`group_id` FROM `user_groups` AS `UserGroup` WHERE `UserGroup`.`user_id` IN ( 1, 2 );
+----+-------------+-----------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | UserGroup | range | PRIMARY       | PRIMARY | 4       | NULL |    4 | Using where; Using index |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+--------------------------+

它有using where;，根据它有额外的磁盘搜索，我知道using where;概念，而且我也知道这并不可怕，但从概念上我想知道为什么这个查询有using where;，虽然条件直接在主键上并且它没有顺序或任何导致using where;. 我该如何解决这个问题using where;？

我们有高流量的新闻网站，我想添加一个功能，每个用户都可以搜索网站的所有内容，例如news, polls, comments,galleries等。每种内容类型都有自己的表格。

我决定创建一个包含所有类型的所有内容的表：

CREATE TABLE full_text_search
(
    master_id INT NOT NULL,
    content_text TEXT NOT NULL,
    PRIMARY KEY ( master_id )
);

我为所有类型的每个内容生成一个唯一编号，以master_id识别表中的每个内容。content_textfull_text_search

例如：

News table:
+----+-------------+---------+---------+----------+------------+
| id | news_title  | lead    | subtitle|  content | master_id  |
+----+-------------+---------+---------+----------+------------+
|  1 |  sometitle  |some lead| subtitle|content 1 |     3      |
|  2 |  some title |some lead| subtitle|content 2 |     5      |
+----+-------------+---------+---------+----------+------------+

article table:
+----+-------------+---------+------------------+---------+------------+
| id | title       | author  | short description| content | master_id  |
+----+-------------+---------+------------------+---------+------------+
|  1 |  sometitle  | someone | very short desc  |content1 |     1      |
|  2 |  some title | otherone|  some short desc |content2 |     4      |
+----+-------------+---------+------------------+---------+------------+

如您所见master_id，上述表格之间是独一无二的。当插入每种类型的新内容时，我也应该INSERT将其放入full_text_search表格中。

问题

• 对于一天的许多插入（所有类型大约 3000 个），这是一个好的解决方案还是反模式？
• 如果我将此表与其他表分开并将其放入任何其他数据库（例如其他 RDBMS 或 NoSQL）中，这是更好的选择吗？
• 还有其他解决方案吗？

我使用复杂的选择查询通过CREATE TABLE ... AS SELECT.... 如何在此查询中添加自动增量主键？

例如：

create table `user_mv` select `user`.`firstname` as 
   `firstname`,
   `user`.`lastname` as `lastname`,
   `user`.`lang` as `lang`,
   `user`.`name` as `user_name`,
   `group`.`name` as `group_name`
from `user`
  inner join `user_groups` on (`user`.`user_id`=`user_groups`.`user_id`)
  left  join `group` on (`group`.`group_id`=`user_groups`.`group_id`)
where `user`.`lang`=`group`.`lang` 

此查询创建一个包含firstname, lastname, lang, username,group_name列的表。我希望它也有一个id作为自动增量主键的列。

有没有办法通过更改此查询来做到这一点？我知道我可以通过在执行此查询后更改表来做到这一点，但是如果有任何方法可以直接在create table语句中执行此操作，我想知道如何执行此操作。

当我CREATE TEMPORARY TABLE用来创建一个临时表时，这个表的排序规则是utf8_unicode_ci默认的。为什么默认是utf8_unicode_ci？如何更改默认值？

我有一个包含 post_order 列的 posts 表。我在其中存储每个帖子的顺序。当我将一行的顺序从 25 更改为 15 时，我应该将所有行从 15 更新到末尾。这对几行很好，但在几千行时最差。

有没有更好的排序帖子设计，效率更高？

在哪里可以找到 tpcc-mysql 文档？我用谷歌搜索，但找不到任何关于它的文档。

当我想创建表时，我安装了 Percona Server 5.6.12-rc60.4：

CREATE TABLE IF NOT EXISTS `mydb`.`table` (
`id` INT NOT NULL AUTO_INCREMENT ,
`title` VARCHAR( 64 ) NOT NULL ,
PRIMARY KEY ( `id` ) 
) ENGINE = XtraDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci;

发生此错误：

 #1286 - Unknown storage engine 'XtraDB' 

如何使用 XtraDB 引擎创建表？

对于布尔目的列，哪个更好：可为空char(0)或tinyint(1).

我知道 bool 是 tinyint(1) 的别名，但在 O'reilly 出版的“高性能 MySQL”一书中说：

“如果您想在单个存储空间中存储真/假值，另一种选择是创建一个可为空的 CHAR(0) 列。该列能够存储缺少值 (NULL) 或零-length 值（空字符串）。"

哪个更适合大小、性能、索引或...

更新：我发现这个链接对这个问题很有用： Innodb Tables 的 Efficient Boolean value storage

我知道对于每个外键，我们应该在其列上建立一个索引，但是当该列位于 PRIMARY 索引的第一部分时，就没有必要了。

MySQL 接受这个查询：

CREATE TABLE IF NOT EXISTS `poll_option_i18ns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `poll_option_id` int(11) NOT NULL,
  `lang` char(2) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `title` varchar(127) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`poll_option_id`,`lang`),
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

ALTER TABLE `poll_option_i18ns`
  ADD CONSTRAINT `poll_option_id` FOREIGN KEY (`poll_option_id`) REFERENCES `poll_options` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

但是在 MySQL 工作台中，我无法在没有索引的情况下添加外键！这是 MySQL Workbench 错误还是我做错了？

我使用 MySQL 5.6，我有两个表，每个表有 1600 万行：

CREATE TABLE IF NOT EXISTS `newsstudios` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=16855382 ;

CREATE TABLE IF NOT EXISTS `newsstudio_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `newsstudio_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `newsstudio_id` (`newsstudio_id`),
  KEY `category_id` (`category_id`),
  KEY `newsstudio_id_category_id` (`newsstudio_id`,`category_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=16864013 ;

我有一个订单查询order by newsstudios.id ASC：

SELECT SQL_NO_CACHE id FROM `newsstudios` WHERE exists 
(
  select newsstudio_id from newsstudio_categories 
  where newsstudios.id=newsstudio_categories.newsstudio_id 
  and newsstudio_categories.category_id in (1303,1313,1323,1333,1343,632)
) 
order by newsstudios.id limit 5;

此查询的结果是：

+------+
| id   |
+------+
|   27 |
|   47 |
|   87 |
|  110 |
|  181 |
+------+
5 rows in set (0.19 sec)

但是当我将 order by 的方向更改为DESC查询执行时间时减少了 100 倍：

+------+
| id   |
+------+
| 98232|
| 98111|
| 95222|
| 88132|
| 78181|
+------+
5 rows in set (21 sec)

第一：为什么这种排序方向的改变会导致性能上的巨大差异？

第二：在此查询之前，我尝试过LEFT JOIN查询WHERE IN而不是查询，WHERE EXISTS但它们有重复的结果，我应该使用GROUP BY它导致的结果using filesort，这会using temporary大大降低性能。您对查询有更好的性能有什么建议吗？

我无法理解优化数据大小中的这个语句：

在具有相同数据类型的不同表中声明具有相同信息的列，以加速基于相应列的连接。

任何人都可以用例子来描述这个陈述吗？