吾生有涯,而知无涯,适当止学.循序渐进,步步提升 Talk is cheap, show me the code.
分类: Mysql/postgreSQL
2012-07-11 15:19:04
关于索引的有趣现象:
今天在创建索引的时候,发现一个版本的差异。
在5.1版本创建索引.
mysql> create index idx_username on bk_user(username(20));
Query OK, 226 rows affected (0.11 sec)
Records: 226 Duplicates: 0 Warnings: 0
mysql> create index idx_email on bk_user(email(30));
Query OK, 226 rows affected (0.12 sec)
Records: 226 Duplicates: 0 Warnings: 0(在5.1创建的索引,它们的记录都是有着影响的。影响了226条)
(system@localhost) [baike]> show index from bk_user;(查看索引的时候,也是建好了的)
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| bk_user | 0 | PRIMARY | 1 | uid | A | 83799 | NULL | NULL | | BTREE | | |
| bk_user | 1 | loginstatus | 1 | loginstatus | A | 3 | NULL | NULL | | BTREE | | |
| bk_user | 1 | idx_username | 1 | username | A | 83799 | 20 | NULL | YES | BTREE | | |
| bk_user | 1 | idx_email | 1 | email | A | 185 | 30 | NULL | YES | BTREE | | |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
在5.5版本上面,建立索引的时候。
(system@localhost) [baike]> create index idx_email on bk_user(email(30));
Query OK, 0 rows affected (0.49 sec)
Records: 0 Duplicates: 0 Warnings: 0(竟然发现0条数据,我被它欺骗了。其实是隐性的影响了相应的条数,你看时间0.49秒,就知道它是受了影响的)
这个现象挺有意思,说明mysql在版本的改变的时候,对这一块代码的实现做了修改。