一、前言

最近工作中遇到了三个问题，分别是没有order by下的排序，datatime类型非空字段，和模糊查询时索引使用情况。详细情况，请看下文。

二、 mysql的默认排序问题

1、问题

select * from table ;（表中有主键）select col1 from table;（col1非主键列）select col1,col2 from table;（col1、col2均非主键列）

注意：以下所有代码，可以左右移动屏幕阅读。

2、环境说明

数据库版本

root@localhost [test]>select version();+------------+| version()  |+------------+| 5.7.23-log |+------------+1 row in set (0.00 sec)

建表语句

##innoDB引擎CREATE TABLE `t_test_20200104_innodb` (  `id` int(11) NOT NULL ,  `sid` varchar(18) DEFAULT NULL,  `NAME` varchar(18) DEFAULT NULL,  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,  PRIMARY KEY (`id`),  KEY `idx_sid` (`sid`)) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COMMENT='测试表';

##MyISAM引擎CREATE TABLE `t_test_20200104_myisam` (  `id` int(11) NOT NULL ,  `sid` varchar(18) DEFAULT NULL,  `NAME` varchar(18) DEFAULT NULL,  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,  PRIMARY KEY (`id`),  KEY `idx_sid` (`sid`)) ENGINE=MyISAM  DEFAULT CHARSET=utf8mb4 COMMENT='测试表';

插入数据

##innoDB引擎insert into t_test_20200104_innodb (id,sid,name) values('1','a','aa');insert into t_test_20200104_innodb (id,sid,name) values('4','d','dd');insert into t_test_20200104_innodb (id,sid,name) values('2','b','bb');insert into t_test_20200104_innodb (id,sid,name) values('3','c','cc');

##MyISAM引擎insert into t_test_20200104_myisam (id,sid,name) values('1','a','aa');insert into t_test_20200104_myisam (id,sid,name) values('4','d','dd');insert into t_test_20200104_myisam (id,sid,name) values('2','b','bb');insert into t_test_20200104_myisam (id,sid,name) values('3','c','cc');

3、实验

root@localhost [test]>select * from t_test_20200104_innodb;+----+------+------+---------------------+| id | sid  | NAME | create_time         |+----+------+------+---------------------+|  1 | a    | aa   | 2020-01-04 10:32:01 ||  2 | b    | bb   | 2020-01-04 10:32:07 ||  3 | c    | cc   | 2020-01-04 10:32:23 ||  4 | d    | dd   | 2020-01-04 10:32:04 |+----+------+------+---------------------+4 rows in set (0.00 sec)
root@localhost [test]>select * from t_test_20200104_myisam;+----+------+------+---------------------+| id | sid  | NAME | create_time         |+----+------+------+---------------------+|  1 | a    | aa   | 2020-01-04 10:47:02 ||  4 | d    | dd   | 2020-01-04 10:47:06 ||  2 | b    | bb   | 2020-01-04 10:47:09 ||  3 | c    | cc   | 2020-01-04 10:47:14 |+----+------+------+---------------------+4 rows in set (0.00 sec)

4、总结

通过以上实验我们可以总结：

对于innodb引擎的表是按主键顺序排序的
对于非innodb引擎表，如果数据没有被修改过，则按插入时间排序的。

以下是mysql官方的回答

当表是myisam

SELECT * FROM tbl -- this will do a "table scan". If the table has never had any DELETEs/REPLACEs/UPDATEs, the records will happen to be in the insertion order, hence what you observed.

当表是myisam引擎时，在没有增删改的操作下，执行 select 不带order by，那么会按照插入顺序进行排序。

当表是innodb

If you had done the same statement with an InnoDB table, they would have been delivered in PRIMARY KEY order, not INSERT order. Again, this is an artifact of the underlying implementation, not something to depend on.

对于innodb引擎表来说，在相同的情况下，select 不带order by，会根据主键来排序，从小到大。

注意：由于mysql的排序规则的较为复杂，如果要排序，切记要使用`order by`显示的说明排序方式，而不是由mysql默认的排序规则实现！！！

遗留问题：

1、如果innodb引擎的主键是字符串类型，是如何排序的？

2、如果非innodb引擎的表被删改后，再插入又是如何排序的？

3、对于各种引擎，`select col1 from table;`（col1非主键列）和 `select col1,col2 from table;`（col1、col2均非主键列），又是如何排序的？

三、mysql的表不为空的时间类型列的问题

1、背景

mysql同步到pgsql时报错，报错原因·`时间类型`某个字段不该为空的字段传了空值。排查问题时，检查了mysql的源端和pgsql的目标端表结构都是一致的，该字段都是非空的，但是也确实报不为空的字段传了个空值。

报错信息如下：

org.postgresql.util.PSQLException: ERROR: null value in column "create_time" violates not-null constraint Detail: Failing row contains. Where: COPY table_name, line 224

源表结构：

`table_name` ( ...  `CREATE_TIME` datetime NOT NULL DEFAULT '1970-12-31 00:00:00' COMMENT '创建时间',  `UPDATE_TIME` datetime NOT NULL DEFAULT '1970-12-31 00:00:00' COMMENT '更新时间',  PRIMARY KEY (`TENANT_ID`,`SIGN_ID`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ;

发现CREATE_TIME列确实是非空的。

在源库查询CREATE_TIME为空时的结果集，确实查到0000-00-00 00:00:00的结果集，结果如下：

[mysql]>select create_time from table_name where create_time is null;+---------------------+| create_time         |+---------------------+| 0000-00-00 00:00:00 || 0000-00-00 00:00:00 || 0000-00-00 00:00:00 || 0000-00-00 00:00:00 || 0000-00-00 00:00:00 || 0000-00-00 00:00:00 || 0000-00-00 00:00:00 || 0000-00-00 00:00:00 || 0000-00-00 00:00:00 || 0000-00-00 00:00:00 || 0000-00-00 00:00:00 || 0000-00-00 00:00:00 || 0000-00-00 00:00:00 || 0000-00-00 00:00:00 || 0000-00-00 00:00:00 || 0000-00-00 00:00:00 || 0000-00-00 00:00:00 || 0000-00-00 00:00:00 || 0000-00-00 00:00:00 || 0000-00-00 00:00:00 || 0000-00-00 00:00:00 || 0000-00-00 00:00:00 || 0000-00-00 00:00:00 || 0000-00-00 00:00:00 || 0000-00-00 00:00:00 || 0000-00-00 00:00:00 || 0000-00-00 00:00:00 |+---------------------+27 rows in set (0.01 sec)

2、问题

根据上述实验产生一个问题：明明是非空的列，为什么可以插入`0000-00-00 00:00:00`，而该值是等于空值的（因为0000-00-00 00:00:00传入到pg是作为null，所以同步才会非空列出现空值的错误）。

3、原因

而当sql_mode为空时，不为空的列时可以插入`0000-00-00 00:00:00`，且该值被认为是null空值。

NO_ZERO_IN_DATE,NO_ZERO_DATE是无法默认设置为：'0000-00-00 00:00:00'的根源。

NO_ZERO_IN_DATE：在严格模式下，不允许日期和月份为零

NO_ZERO_DATE：设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。

STRICT_TRANS_TABLES：严格模式

root@localhost [test]>insert into t_test_20200104_null(create_time)  values('0000-00-00 00:00:00');ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'CREATE_TIME' at row 1root@localhost [test]>root@localhost [test]>root@localhost [test]>root@localhost [test]>show variables like 'sql_mode'\G;*************************** 1. row ***************************Variable_name: sql_mode        Value:  STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE1 row in set (0.00 sec)
ERROR: No query specified

而当sql_mode为空时，不为空的列时可以插入`0000-00-00 00:00:00`，且该值即被认为是null空值，也被认为不是空值，而在pgsql是不被允许的。

root@localhost [test]>show variables like 'sql_mode';+---------------+-------+| Variable_name | Value |+---------------+-------+| sql_mode      |       |+---------------+-------+1 row in set (0.01 sec)

root@localhost [test]>insert into t_test_20200104_null(create_time)  values('0000-00-00 00:00:00');Query OK, 1 row affected (0.00 sec)

###查询create_time为空时，0000-00-00 00：00：00也能被查询出root@localhost [test]>select * from t_test_20200104_null where create_time is null;+----+------+------+---------------------+| id | sid  | NAME | CREATE_TIME         |+----+------+------+---------------------+|  1 | NULL | NULL | 0000-00-00 00:00:00 ||  3 | NULL | NULL | 0000-00-00 00:00:00 ||  4 | NULL | NULL | 0000-00-00 00:00:00 ||  5 | NULL | NULL | 0000-00-00 00:00:00 ||  6 | NULL | NULL | 0000-00-00 00:00:00 ||  7 | NULL | NULL | 0000-00-00 00:00:00 ||  8 | NULL | NULL | 0000-00-00 00:00:00 ||  9 | NULL | NULL | 0000-00-00 00:00:00 || 10 | NULL | NULL | 0000-00-00 00:00:00 || 11 | NULL | NULL | 0000-00-00 00:00:00 |+----+------+------+---------------------+10 rows in set (0.00 sec)
###查询create_time不为空时，0000-00-00 00：00：00也能被查询出root@localhost [test]>select * from t_test_20200104_null where create_time is not null;+----+------+------+---------------------+| id | sid  | NAME | CREATE_TIME         |+----+------+------+---------------------+|  1 | NULL | NULL | 0000-00-00 00:00:00 ||  2 | NULL | NULL | 2020-01-04 12:43:14 ||  3 | NULL | NULL | 0000-00-00 00:00:00 ||  4 | NULL | NULL | 0000-00-00 00:00:00 ||  5 | NULL | NULL | 0000-00-00 00:00:00 ||  6 | NULL | NULL | 0000-00-00 00:00:00 ||  7 | NULL | NULL | 0000-00-00 00:00:00 ||  8 | NULL | NULL | 0000-00-00 00:00:00 ||  9 | NULL | NULL | 0000-00-00 00:00:00 || 10 | NULL | NULL | 0000-00-00 00:00:00 || 11 | NULL | NULL | 0000-00-00 00:00:00 |+----+------+------+---------------------+11 rows in set (0.00 sec)

4、总结

当sql_mode为空时，mysql中datatime类型不为空的字段，可以插入'0000-00-00 00:00:00'
在mysql中datatime类型不为空的字段， '0000-00-00 00:00:00'即被认为是null也，被认为不是null。
当sql_mode为严格模式且对时间类型有约束时，无法在datatime类型不为空的字段中，插入'0000-00-00 00:00:00'值，报`ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column `错误信息。

MySQL官方说明地址：

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_da

四、mysql的最前索引使用

1、背景

营销平台有一个模糊查询，语句如下：

select user_name from users where user_id like 'XXX%';

该表的`user_id`字段是有索引的，理论上该语句是会使用到索引，查询时间应该在毫秒级，但是实际情况是该语句执行了200多秒CPU使用率到达100%，当时我都震惊了。。。

2、解决方案

找到具体sql如下

SELECT  SYMBOL_IDFROM  usersWHERE  IDENTIFIERS LIKE '45%';

后跟业务方沟通后，该表的IDENTIFIERS字段大部分存储的是id，如果查询以45开头的数据，则会查询70多万的结果集，虽然此时仍然使用了索引，但是选择性非常差，导致耗时非常高，且由于是查询的明细，语句会将70多万的明细给打印出来，导致sql执行了超过200秒。

执行计划如下，其中rows: 1125540。

root@58.59.11.86 [loanuser]>explain SELECT    -> SYMBOL_ID,    -> TENANT_ID     -> FROM    -> user_auth_id_symbol     -> WHERE    -> ( IDENTIFIERS LIKE '45%')\G;*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: user_auth_id_symbol   partitions: NULL         type: rangepossible_keys: UNI_IDENTIFIERS          key: UNI_IDENTIFIERS      key_len: 388          ref: NULL         rows: 1125540     filtered: 0.10        Extra: Using index condition; Using where1 row in set, 1 warning (0.00 sec)

ERROR: No query specified

而如果where条件是IDENTIFIERS LIKE '11145%'，仍然使用模糊查询，但是该条件下选择性很高，查询效率很高，因为该字段以11145开头的几乎没有。

其执行计划如下，其中rows: 1。

root@58.59.11.86 [loanuser]>explain SELECT    -> SYMBOL_ID,    -> TENANT_ID     -> FROM    -> user_auth_id_symbol     -> WHERE    -> ( IDENTIFIERS LIKE '11145%')\G;*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: user_auth_id_symbol   partitions: NULL         type: rangepossible_keys: UNI_IDENTIFIERS          key: UNI_IDENTIFIERS      key_len: 388          ref: NULL         rows: 1     filtered: 5.00        Extra: Using index condition; Using where1 row in set, 1 warning (0.00 sec)
ERROR: No query specified

3、总结

在mysql中，当where使用like 'XX%'查询时,是会使用索引的。
在mysql中，使用索引的首要原则是选择性，选择性越高效率也越高。
在mysql中，即时语句一摸一样，仅仅where条件中具体值不同时，效率也可以差几百上千倍。

ITPub博客

MySQL中默认排序等问题和原因

最新文章