一、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
