Ruby China

数据库 MySQL 你以为你以为的就是你以为的？

ibugs · 2015年08月28日 · 最后由 ibugs 回复于 2015年08月31日 · 3894 次阅读

一、InnoDB 在任何情况下都是按主键正序排列的么？

很早之前，我无知，以为 InnoDB 中的数据都是按照 id 正序排序的，直到我看到了下面的例子。。。

索引如下：

CREATE TABLE `pay_account_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_id` int(11) NOT NULL DEFAULT '0' ,
  `b_amount` int(11) NOT NULL DEFAULT '0' ,
  `r_amount` int(11) NOT NULL DEFAULT '0' ,
  `amount` int(11) NOT NULL DEFAULT '0' ,
  `log_type` int(11) NOT NULL DEFAULT '0' ,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_created_at` (`created_at`,`id`),
  KEY `idx_account_type` (`account_id`,`log_type`,`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

三个 column 的索引

mysql> explain SELECT SQL_NO_CACHE `pay_account_logs`.* FROM `pay_account_logs` WHERE `pay_account_logs`.`account_id` = 408 AND (created_at >= '2015-08-23 00:00:00');
+----+-------------+------------------+------+---------------------------------+------------------+---------+-------+------+-------------+
| id | select_type | table            | type | possible_keys                   | key              | key_len | ref   | rows | Extra       |
+----+-------------+------------------+------+---------------------------------+------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | pay_account_logs | ref  | idx_created_at,idx_account_type | idx_account_type | 4       | const |    4 | Using where |
+----+-------------+------------------+------+---------------------------------+------------------+---------+-------+------+-------------+
1 row in set (0.01 sec)


mysql> SELECT SQL_NO_CACHE `pay_account_logs`.* FROM `pay_account_logs` WHERE `pay_account_logs`.`account_id` = 408;
+---------+------------+----------------+---------------+----------+----------+---------------------+---------------------+
| id      | account_id | b_amount | r_amount | amount   | log_type | created_at          | updated_at          |
+---------+------------+----------------+---------------+----------+----------+---------------------+---------------------+
| 9999959 |     408 |              0 |             0 |  -210000 |        1 | 2015-08-23 13:18:43 | 2015-08-23 13:18:43 |
| 9999945 |     408 |              0 |             0 | -1000000 |        2 | 2015-08-23 13:14:44 | 2015-08-23 13:14:44 |
| 9999943 |     408 |        1000000 |             0 |  1000000 |        4 | 2015-08-23 13:13:46 | 2015-08-23 13:13:46 |
| 9999955 |     408 |         210000 |             0 |   210000 |        4 | 2015-08-23 13:18:17 | 2015-08-23 13:18:17 |
+---------+------------+----------------+---------------+----------+----------+---------------------+---------------------+

结果并不是按照id正序排列的
account_id 和 log_type 是有序的，但是id并不是有序的。

explain SELECT SQL_NO_CACHE `pay_account_logs`.* FROM `pay_account_logs` WHERE `pay_account_logs`.`account_id` = 408 order by id desc;
+----+-------------+------------------+------+----------------------------+----------------------------+---------+-------+------+-----------------------------+
| id | select_type | table            | type | possible_keys              | key                        | key_len | ref   | rows | Extra                       |
+----+-------------+------------------+------+----------------------------+----------------------------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | pay_account_logs | ref  | idx_of_account_id_log_type | idx_of_account_id_log_type | 4       | const |    4 | Using where; Using filesort |
+----+-------------+------------------+------+----------------------------+----------------------------+---------+-------+------+-----------------------------+
1 row in set (0.01 sec)

explain SELECT SQL_NO_CACHE `pay_account_logs`.* FROM `pay_account_logs` WHERE `pay_account_logs`.`account_id` = 408 order by log_type desc, id desc;
+----+-------------+------------------+------+----------------------------+----------------------------+---------+-------+------+-------------+
| id | select_type | table            | type | possible_keys              | key                        | key_len | ref   | rows | Extra       |
+----+-------------+------------------+------+----------------------------+----------------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | pay_account_logs | ref  | idx_of_account_id_log_type | idx_of_account_id_log_type | 4       | const |    4 | Using where |
+----+-------------+------------------+------+----------------------------+----------------------------+---------+-------+------+-------------+
1 row in set (0.01 sec)

explain SELECT SQL_NO_CACHE `pay_account_logs`.* FROM `pay_account_logs` WHERE `pay_account_logs`.`account_id` = 408 order by log_type asc, id desc;
+----+-------------+------------------+------+----------------------------+----------------------------+---------+-------+------+-----------------------------+
| id | select_type | table            | type | possible_keys              | key                        | key_len | ref   | rows | Extra                       |
+----+-------------+------------------+------+----------------------------+----------------------------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | pay_account_logs | ref  | idx_of_account_id_log_type | idx_of_account_id_log_type | 4       | const |    4 | Using where; Using filesort |
+----+-------------+------------------+------+----------------------------+----------------------------+---------+-------+------+-----------------------------+
1 row in set (0.01 sec)

explain SELECT SQL_NO_CACHE `pay_account_logs`.* FROM `pay_account_logs` WHERE `pay_account_logs`.`account_id` = 408 order by log_type desc;
+----+-------------+------------------+------+----------------------------+----------------------------+---------+-------+------+-------------+
| id | select_type | table            | type | possible_keys              | key                        | key_len | ref   | rows | Extra       |
+----+-------------+------------------+------+----------------------------+----------------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | pay_account_logs | ref  | idx_of_account_id_log_type | idx_of_account_id_log_type | 4       | const |    4 | Using where |
+----+-------------+------------------+------+----------------------------+----------------------------+---------+-------+------+-------------+
1 row in set (0.01 sec)

数据按照 account_id,log_type,id 升序排序

两个 column 的情况

如果索引是如下方式创建

mysql> alter table pay_account_logs add index `idx_of_account_id` (`account_id`, `id`);

mysql> SELECT SQL_NO_CACHE `pay_account_logs`.* FROM `pay_account_logs` WHERE `pay_account_logs`.`account_id` = 408;
+---------+------------+----------------+---------------+----------+----------+---------------------+---------------------+
| id      | account_id | b_amount | r_amount | amount   | log_type | created_at          | updated_at          |
+---------+------------+----------------+---------------+----------+----------+---------------------+---------------------+
| 9999943 |     408 |        1000000 |             0 |  1000000 |        4 | 2015-08-23 13:13:46 | 2015-08-23 13:13:46 |
| 9999945 |     408 |              0 |             0 | -1000000 |        2 | 2015-08-23 13:14:44 | 2015-08-23 13:14:44 |
| 9999955 |     408 |         210000 |             0 |   210000 |        4 | 2015-08-23 13:18:17 | 2015-08-23 13:18:17 |
| 9999959 |     408 |              0 |             0 |  -210000 |        1 | 2015-08-23 13:18:43 | 2015-08-23 13:18:43 |
+---------+------------+----------------+---------------+----------+----------+---------------------+---------------------+
4 rows in set (0.00 sec)

mysql> explain SELECT SQL_NO_CACHE `pay_account_logs`.* FROM `pay_account_logs` WHERE `pay_account_logs`.`account_id` = 408;
+----+-------------+------------------+------+-------------------+-------------------+---------+-------+------+-------+
| id | select_type | table            | type | possible_keys     | key               | key_len | ref   | rows | Extra |
+----+-------------+------------------+------+-------------------+-------------------+---------+-------+------+-------+
|  1 | SIMPLE      | pay_account_logs | ref  | idx_of_account_id | idx_of_account_id | 4       | const |    4 |       |
+----+-------------+------------------+------+-------------------+-------------------+---------+-------+------+-------+
1 row in set (0.01 sec)

account_id 和 id 是正序排序的。由于索引中没有log_type 所以log_type并不是有序的。


EXPLAIN SELECT SQL_NO_CACHE `pay_account_logs`.* FROM `pay_account_logs` WHERE `pay_account_logs`.`account_id` = 408 ORDER BY id desc;

+----+-------------+------------------+------+-------------------+-------------------+---------+-------+------+-------------+
| id | select_type | table            | type | possible_keys     | key               | key_len | ref   | rows | Extra       |
+----+-------------+------------------+------+-------------------+-------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | pay_account_logs | ref  | idx_of_account_id | idx_of_account_id | 4       | const |    4 | Using where |
+----+-------------+------------------+------+-------------------+-------------------+---------+-------+------+-------------+
1 row in set (9.51 sec)

以上情况并没有出现 Using filesort 的情况

数据按照account_id,id 升序排列

在没有索引的情况

mysql> explain SELECT SQL_NO_CACHE `pay_account_logs`.* FROM `pay_account_logs` WHERE `pay_account_logs`.`account_id` = 408;
+----+-------------+------------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table            | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+------------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | pay_account_logs | ALL  | NULL          | NULL | NULL    | NULL | 1684032 | Using where |
+----+-------------+------------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql> SELECT SQL_NO_CACHE `pay_account_logs`.* FROM `pay_account_logs` WHERE `pay_account_logs`.`account_id` = 408;
+---------+------------+----------------+---------------+----------+----------+---------------------+---------------------+
| id      | account_id | balance_amount | refund_amount | amount   | log_type | created_at          | updated_at          |
+---------+------------+----------------+---------------+----------+----------+---------------------+---------------------+
| 9999943 |     408 |        1000000 |             0 |  1000000 |        4 | 2015-08-23 13:13:46 | 2015-08-23 13:13:46 |
| 9999945 |     408 |              0 |             0 | -1000000 |        2 | 2015-08-23 13:14:44 | 2015-08-23 13:14:44 |
| 9999955 |     408 |         210000 |             0 |   210000 |        4 | 2015-08-23 13:18:17 | 2015-08-23 13:18:17 |
| 9999959 |     408 |              0 |             0 |  -210000 |        1 | 2015-08-23 13:18:43 | 2015-08-23 13:18:43 |
+---------+------------+----------------+---------------+----------+----------+---------------------+---------------------+
4 rows in set (2.47 sec)

数据按照主键 id 升序排列。我们以为按照主键升序的情况，都是这个例子。

查看索引组织数据

CREATE TABLE people(
  last_name varchar(50) not null,
  first_name varchar(50) not null,
  dob  date not null,
  gender enum('m','f') not null,
  key(last_name, first_name, dob)
);

从以上例子也可以证明，索引中的数据是有序的。

联合索引，是依次按照索引顺序，正序排列的。但不能保证所有字段都是正序排列的。

二、MySQL 关键字，以及关键字带来的坑

很早之前，我无知，以为名字可以随便起，直到我看到了下面的例子。

表结构如下：

mysql> desc a_authorities;

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| key        | varchar(100) | NO   | UNI |         |                |
| desc       | varchar(100) | NO   |     |         |                |
| label      | varchar(100) | NO   |     |         |                |
| group      | varchar(100) | NO   |     |         |                |
| created_at | datetime     | NO   |     | NULL    |                |
| updated_at | datetime     | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
7 rows in set (12.89 sec)

where 条件之后


mysql> select * from a_authorities where key = 'manage_roles'\G;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key = 'manage_roles'' at line 1
ERROR: 
No query specified

mysql> select * from a_authorities where `key` = 'manage_roles'\G;
*************************** 1. row ***************************
        id: 2
       key: manage_roles
      desc: 我的权限
     label: 我的权限
     group: 我的权限
created_at: 2014-10-28 11:12:02
updated_at: 2014-10-28 11:12:02
1 row in set (0.01 sec)

ERROR: 
No query specified

mysql> select * from a_authorities where a_authorities.key = 'manage_roles'\G;
*************************** 1. row ***************************
        id: 2
       key: manage_roles
      desc: 我的权限
     label: 我的权限
     group: 我的权限
created_at: 2014-10-28 11:12:02
updated_at: 2014-10-28 11:12:02
1 row in set (0.40 sec)

ERROR: 
No query specified

select 之后

mysql> select desc from a_authorities where id = 2\G;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc from a_authorities where id = 2' at line 1
ERROR: 
No query specified

mysql> select `desc` from a_authorities where id = 2\G;
*************************** 1. row ***************************
desc: 我的权限
1 row in set (0.00 sec)

ERROR: 
No query specified

分组


mysql> select count(id), group from a_authorities group by group;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group from a_authorities group by group' at line 1

以上 column key，desc，label，group 均为关键字

关键字在查询，排序，分组等 SQL 语句中都会有异常

如果记不住那么多关键字，使用 ad_key，ad_desc，ad_label，ad_group 这种自定义前缀的方式命名 column

MySQL 中的关键字

1 个赞
hooopo #1 2015年08月28日

简单的说就是按照使用的索引顺序，当然这个大部分没有条件情况下是主键顺序...

你用同一条语句去查，加上force index看的效果更明显。

1 个赞
ibugs #2 2015年08月28日 
SELECT SQL_NO_CACHE id, account_id, log_type FROM `pay_account_logs` WHERE `pay_account_logs`.`account_id` = 408;

+---------+------------+----------+
| id      | account_id | log_type |
+---------+------------+----------+
| 59 |     408 |        1 |
| 45 |     408 |        2 |
| 43 |     408 |        4 |
| 55 |     408 |        4 |
+---------+------------+----------+

EXPLAIN SELECT SQL_NO_CACHE `pay_account_logs`.* FROM `pay_account_logs` WHERE `pay_account_logs`.`account_id` = 408\G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pay_account_logs
         type: ref
possible_keys: idx_of_account_id_log_type
          key: idx_of_account_id_log_type
      key_len: 4
          ref: const
         rows: 4
        Extra: 
1 row in set (0.01 sec)

SELECT SQL_NO_CACHE id, account_id, log_type FROM `pay_account_logs` FORCE KEY(PRIMARY) WHERE `pay_account_logs`.`account_id` = 408;

+---------+------------+----------+
| id      | account_id | log_type |
+---------+------------+----------+
| 43 |     408 |        4 |
| 45 |     408 |        2 |
| 55 |     408 |        4 |
| 59 |     408 |        1 |
+---------+------------+----------+

EXPLAIN SELECT SQL_NO_CACHE `pay_account_logs`.* FROM `pay_account_logs` FORCE KEY(PRIMARY) WHERE `pay_account_logs`.`account_id` = 408\G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pay_account_logs
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1684051
        Extra: Using where
1 row in set (0.00 sec)

可以看到，如果在 有 (account_id, log_type, id) 的情况下，强制按照主键排序，会用不到索引。 再次证明了，如果在这种多列索引中，id 就不一定是升序排列的。

3Q for @hooopo

sun1752709589 #3 2015年08月31日

mysql innodb 的左前缀匹配规则就表明 mysql 中多列索引的话是按照列顺序排列的，即是：先按第一列排，第一列相同的那些按第二列排，依次类推.......我不认为排序时和某一列是不是 id 列有多大关系

ibugs #4 2015年08月31日

@sun1752709589 正解，多列索引，第一列正序排列，第二列次之，。。。第二列之后的数据组织，并不是有序的，和 id 没有关系。如果没有索引的话，数据按照主键 id 正序排列，这个是聚簇索引。我们大多数认为的情况是这种聚簇索引。

需要 登录 后方可回复, 如果你还没有账号请 注册新账号