一质和、InnoDB在任何情況下都是按主鍵正序排列的么稳摄？
很早之前稚字，我無(wú)知，以為InnoDB中的數(shù)據(jù)都是按照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;
三個(gè)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 |
+---------+------------+----------------+---------------+----------+----------+---------------------+---------------------+
結(jié)果并不是按照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)
數(shù)據(jù)按照 account_id,log_type,id 升序排序
兩個(gè)column的情況
如果索引是如下方式創(chuàng)建
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 是正序排序的减噪。由于索引中沒(méi)有l(wèi)og_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)
以上情況并沒(méi)有出現(xiàn) Using filesort 的情況
數(shù)據(jù)按照account_id,id 升序排列
在沒(méi)有索引的情況
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)
數(shù)據(jù)按照主鍵id升序排列。我們以為按照主鍵升序的情況筹裕，都是這個(gè)例子醋闭。
查看索引組織數(shù)據(jù)
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)
);
從以上例子也可以證明，索引中的數(shù)據(jù)是有序的朝卒。
聯(lián)合索引证逻，是依次按照索引順序，正序排列的抗斤。但不能保證所有字段都是正序排列的囚企。
二、MySQL 關(guān)鍵字瑞眼，以及關(guān)鍵字帶來(lái)的坑
很早之前龙宏，我無(wú)知，以為名字可以隨便起伤疙，直到我看到了下面的例子银酗。
表結(jié)構(gòu)如下：
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: 我的權(quán)限
label: 我的權(quán)限
group: 我的權(quán)限
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: 我的權(quán)限
label: 我的權(quán)限
group: 我的權(quán)限
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: 我的權(quán)限
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 均為關(guān)鍵字
關(guān)鍵字在查詢秸歧，排序，分組等SQL語(yǔ)句中都會(huì)有異常
如果記不住那么多關(guān)鍵字衅澈，使用ad_key键菱，ad_desc， ad_label今布，ad_group 這種自定義前綴的方式命名column
