首页 > 代码库 > 一次innodb自增主键重要性案例
一次innodb自增主键重要性案例
一次给市场部统计报表数据的案例：
其中有个临时实体表的表创建的时候使用的create table table_name select xxx from ,所以并未创建主键
创建完成后，我还给其中字段加了索引，索引列基数为1，所以索引选择性是非常好的！
表结构如下：
mysql> desc tbl_userlogin_info_tmp2;
+--------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| userid | int(11) | YES | MUL | NULL | |
| p | bigint(21) | NO | | 0 | |
+--------+------------+------+-----+---------+-------+
字段userid上是有索引的，并且索引列基数为1
然后看执行计划：
mysql> desc select f.schoolid,d.schoolname,count(*) as numbers,f.manager
-> from cw_relations.cw_groupclassmember as a
-> inner join cw_relations.cw_groupclass as b on a.gcid=b.id
-> inner join cw_relations.cw_class as c on b.id=c.cid
-> inner join cw_relations.cw_school as d on c.schoolid=d.schoolid
-> inner join test.userid_info as m on a.userid=m.userid
-> inner join test.tbl_userlogin_info_tmp2 as n on a.userid=n.userid
-> right join test.school_id_new as f on d.schoolid=f.schoolid group by f.schoolid;
+----+-------------+-------+--------+-----------------------------------+------------+---------+-----------------------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------------------------+------------+---------+-----------------------+--------+---------------------------------+
| 1 | SIMPLE | f | ALL | NULL | NULL | NULL | NULL | 2580 | Using temporary; Using filesort |
| 1 | SIMPLE | d | eq_ref | PRIMARY | PRIMARY | 8 | test.f.schoolid | 1 | |
| 1 | SIMPLE | n | index | idx_userid | idx_userid | 5 | NULL | 652751 | Using index |
| 1 | SIMPLE | a | ref | GCID_UserID,Index 2 | Index 2 | 5 | test.n.userid | 1 | Using index |
| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 8 | cw_relations.a.GCID | 1 | Using index |
| 1 | SIMPLE | m | eq_ref | PRIMARY | PRIMARY | 4 | cw_relations.a.UserID | 1 | |
| 1 | SIMPLE | c | eq_ref | PRIMARY,SchoolID_PeriodID_GradeID | PRIMARY | 8 | cw_relations.b.ID | 1 | |
+----+-------------+-------+--------+-----------------------------------+------------+---------+-----------------------+--------+---------------------------------+
可以看出此语句在tbl_userlogin_info_tmp2表上虽然显示使用了索引，但扫描行数依然为652751 条，为全表扫描：
再看我给tbl_userlogin_info_tmp2表加上自增主键列
alter table tbl_userlogin_info_tmp2 add id int auto_increment primary key ;
mysql> desc tbl_userlogin_info_tmp2;
+--------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+----------------+
| userid | int(11) | YES | MUL | NULL | |
| p | bigint(21) | NO | | 0 | |
| id | int(11) | NO | PRI | NULL | auto_increment |
+--------+------------+------+-----+---------+----------------+
执行计划：
mysql> desc select f.schoolid,d.schoolname,count(*) as numbers,f.manager
-> from cw_relations.cw_groupclassmember as a
-> inner join cw_relations.cw_groupclass as b on a.gcid=b.id
-> inner join cw_relations.cw_class as c on b.id=c.cid
-> inner join cw_relations.cw_school as d on c.schoolid=d.schoolid
-> inner join test.userid_info as m on a.userid=m.userid
-> inner join test.tbl_userlogin_info_tmp2 as n on a.userid=n.userid
-> right join test.school_id_new as f on d.schoolid=f.schoolid group by f.schoolid;
+----+-------------+-------+--------+-----------------------------------+---------------------------+---------+-------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------------------------+---------------------------+---------+-------------------------+------+---------------------------------+
| 1 | SIMPLE | f | ALL | NULL | NULL | NULL | NULL | 2580 | Using temporary; Using filesort |
| 1 | SIMPLE | d | eq_ref | PRIMARY | PRIMARY | 8 | test.f.schoolid | 1 | |
| 1 | SIMPLE | c | ref | PRIMARY,SchoolID_PeriodID_GradeID | SchoolID_PeriodID_GradeID | 9 | cw_relations.d.SchoolID | 13 | Using index |
| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 8 | cw_relations.c.CID | 1 | Using index |
| 1 | SIMPLE | a | ref | GCID_UserID,Index 2 | GCID_UserID | 9 | cw_relations.b.ID | 58 | Using index |
| 1 | SIMPLE | m | eq_ref | PRIMARY | PRIMARY | 4 | cw_relations.a.UserID | 1 | |
| 1 | SIMPLE | n | ref | idx_userid | idx_userid | 5 | cw_relations.a.UserID | 1 | Using index |
+----+-------------+-------+--------+-----------------------------------+---------------------------+---------+-------------------------+------+---------------------------------+
可以看出执行计划显示走了索引，扫描行数也减少到了5行
其中原理我还未曾想明白，最开始的表没有自增主键列，而且我也没有给userid这个列设主键列，所以innodb应该会给表创建隐藏主键列，所以猜想效果应该是一样的，但事实胜于雄辩，可见自增主键列对Innodb表来说有多么重要！！
本文出自 “夫臣” 博客，请务必保留此出处http://fucheng.blog.51cto.com/2404495/1571358
一次innodb自增主键重要性案例