mysql uniall_MySQL 你以为你以为的就是你以为的？

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