针对结构如下表,插入1000万数据之后删除主键、索引和约束:
-- 无任何主键、索引和约束
mysql> select * from user limit 3;
+----+-----------+--------+--------------+---------------------+
| id | varchar15 | char15 | decimal15_3 | datetime |
+----+-----------+--------+--------------+---------------------+
| 1 | 1 | 1 | 1.000 | 2016-01-24 21:42:04 |
| 2 | 2 | 2 | 2.000 | 2016-01-24 21:42:05 |
| 3 | 3 | 3 | 3.000 | 2016-01-24 21:42:06 |
+----+-----------+--------+--------------+---------------------+
3 rows in set (0.00 sec)
-- 无任何主键、索引和约束时,查询int栏
mysql> select * from user where id = 5000000 limit 1;
+---------+---------+---------+-----------+---------------------+
| 5000000 | 5000000 | 5000000 | 10000.000 | 2016-03-22 18:35:23 |
+---------+---------+---------+-----------+---------------------+
1 row in set (0.74 sec)
mysql> select * from user where id = 10000000 limit 1;
+----------+----------+----------+-----------+---------------------+
| 10000000 | 10000000 | 10000000 | 10000.000 | 2016-05-19 15:28:43 |
+----------+----------+----------+-----------+---------------------+
1 row in set (1.46 sec)
-- 无任何主键、索引和约束时,查询varchar栏
mysql> select * from user where varchar15 = '5000000' limit 1;
+---------+---------+---------+-----------+---------------------+
| 5000000 | 5000000 | 5000000 | 10000.000 | 2016-03-22 18:35:23 |
+---------+---------+---------+-----------+---------------------+
1 row in set (0.82 sec)
mysql> select * from user where varchar15 = '10000000' limit 1;
+----------+----------+----------+-----------+---------------------+
| 10000000 | 10000000 | 10000000 | 10000.000 | 2016-05-19 15:28:43 |
+----------+----------+----------+-----------+---------------------+
1 row in set (1.60 sec)
-- 无任何主键、索引和约束时,查询char栏
mysql> select * from user where char15 = '5000000' limit 1;
+---------+---------+---------+-----------+---------------------+
| 5000000 | 5000000 | 5000000 | 10000.000 | 2016-03-22 18:35:23 |
+---------+---------+---------+-----------+---------------------+
1 row in set (0.93 sec)
mysql> select * from user where char15 = '10000000' limit 1;
+----------+----------+----------+-----------+---------------------+
| 10000000 | 10000000 | 10000000 | 10000.000 | 2016-05-19 15:28:43 |
+----------+----------+----------+-----------+---------------------+
1 row in set (1.84 sec)
-- 无任何主键、索引和约束时,查询datetime栏
mysql> select * from user where datetime = '2016-03-22 18:35:23' limit 1;
+---------+---------+---------+-----------+---------------------+
| 5000000 | 5000000 | 5000000 | 10000.000 | 2016-03-22 18:35:23 |
+---------+---------+---------+-----------+---------------------+
1 row in set (0.92 sec)
mysql> select * from user where datetime = '2016-05-19 15:28:43' limit 1;
+----------+----------+----------+-----------+---------------------+
| 10000000 | 10000000 | 10000000 | 10000.000 | 2016-05-19 15:28:43 |
+----------+----------+----------+-----------+---------------------+
1 row in set (1.81 sec)