手机版
你好,游客 登录 注册 搜索
背景:
阅读新闻

MySQL 二级索引会不会自动补齐主键

[日期:2014-09-27] 来源:Linux社区  作者:huanghualiang [字体: ]

开篇:一直对MySQL 二级索引是否自动加入主键问题有怀疑,今天又时间就5.5和5.6分析了一把:

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.16-log |
mysql> create table t9(
    -> id int not null ,
    -> a int ,
    -> b int,
    -> c int,
    -> primary key(id),
    -> key ab_idx(a,b)
    -> )engine=innodb;
Query OK, 0 rows affected (0.00 sec)
show variables like '%optimizer_swit%';
....  use_index_extensions=off
mysql> alter table t9 drop primary key ;
Query OK, 16 rows affected (0.01 sec)
Records: 16  Duplicates: 0  Warnings: 0
 
mysql> alter table t9 add primary key(id,id2);
mysql> desc select * from t9 where a=2 and b=1 order by id;
+----+-------------+-------+------+---------------+--------+---------+-------------+------+-------------+
id | select_type | table | type | possible_keys | key    | key_len | ref         | rows | Extra       |
+----+-------------+-------+------+---------------+--------+---------+-------------+------+-------------+
|  1 | SIMPLE      | t9    | ref  | ab_idx        | ab_idx | 10      | const,const |    3 | Using where |
+----+-------------+-------+------+---------------+--------+---------+-------------+------+-------------+
1 row in set (0.00 sec)
 
mysql> desc select * from t9 where a=2 and b=1 order by id,id2;
+----+-------------+-------+------+---------------+--------+---------+-------------+------+-------------+
id | select_type | table | type | possible_keys | key    | key_len | ref         | rows | Extra       |
+----+-------------+-------+------+---------------+--------+---------+-------------+------+-------------+
|  1 | SIMPLE      | t9    | ref  | ab_idx        | ab_idx | 10      | const,const |    3 | Using where |
+----+-------------+-------+------+---------------+--------+---------+-------------+------+-------------+
* 发现是会自动补齐
mysql> select version(); 
+------------+
| version()  |
+------------+
| 5.5.36-log |
mysql> CREATE TABLE t01 (
    ->   a char(32) not null,
    ->   b char(32) not null,
    ->   c char(32) not null,
    ->   d char(32) not null,
    ->   PRIMARY KEY (a,b),
    ->    KEY idx2 (d,b)
    -> ) Engine=InnoDB;
mysql> explain select * from t01 where d='w' and b='g' order by a;                                                                   
+----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+
id | select_type | table | type | possible_keys | key  | key_len | ref         | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+
|  1 | SIMPLE      | t01   | ref  | idx2          | idx2 | 192     | const,const |    3 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+
1 row in set (0.00 sec)
 
mysql> explain select * from t01 where d='w' and b='g' order by a,b;
+----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+
id | select_type | table | type | possible_keys | key  | key_len | ref         | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+
|  1 | SIMPLE      | t01   | ref  | idx2          | idx2 | 192     | const,const |    3 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+

MySQL 5.6.16 源码 storage/innobase/dict/dict0dict.cc

/*******************************************************************//**
Builds the internal dictionary cache representation for a clustered
index, containing also system fields not defined by the user.
@return own: the internal representation of the clustered index */
static
dict_index_t*
dict_index_build_internal_clust(
/*============================*/
    const dict_table_t*    table,  /*!< in: table */
    dict_index_t*      index)  /*!< in: user representation of
                    a clustered index */
{
    dict_index_t*  new_index;
    dict_field_t*  field;
    ulint      trx_id_pos;
    ulint      i;
    ibool*      indexed;
 
    ut_ad(table && index);
    ut_ad(dict_index_is_clust(index));
    ut_ad(mutex_own(&(dict_sys->mutex)));
    ut_ad(table->magic_n == DICT_TABLE_MAGIC_N);
 
    /* Create a new index object with certainly enough fields */
    new_index = dict_mem_index_create(table->name,
                      index->name, table->space,
                      index->type,
                      index->n_fields + table->n_cols);
    /* Remember the table columns already contained in new_index */
    indexed = static_cast<ibool*>(
        mem_zalloc(table->n_cols * sizeof *indexed));
 
    /* Mark the table columns already contained in new_index */
    for (i = 0; i < new_index->n_def; i++) {
 
        field = dict_index_get_nth_field(new_index, i);
 
        /* If there is only a prefix of the column in the index
        field, do not mark the column as contained in the index */
 
        if (field->prefix_len == 0) {
 
            indexed[field->col->ind] = TRUE;
        }
    }

总结:

1.从源代码看出在引擎层是做了主动补齐主键到二级索引的最后面,但是server层并不知道主键补齐到后面,mysql server层不一定动能自动识别二级索引后面的主键列,强烈建议创建二级索引的时候加上主键列。

2.我上面的演示实例M ySQL server层是自动自动识别了二级索引后的主键列,看运气吧,目前还没遇到过不识别的。

--------------------------------------分割线 --------------------------------------

Ubuntu 14.04下安装MySQL http://www.linuxidc.com/Linux/2014-05/102366.htm

《MySQL权威指南(原书第2版)》清晰中文扫描版 PDF http://www.linuxidc.com/Linux/2014-03/98821.htm

Ubuntu 14.04 LTS 安装 LNMP Nginx\PHP5 (PHP-FPM)\MySQL http://www.linuxidc.com/Linux/2014-05/102351.htm

Ubuntu 14.04下搭建MySQL主从服务器 http://www.linuxidc.com/Linux/2014-05/101599.htm

Ubuntu 12.04 LTS 构建高可用分布式 MySQL 集群 http://www.linuxidc.com/Linux/2013-11/93019.htm

Ubuntu 12.04下源代码安装MySQL5.6以及Python-MySQLdb http://www.linuxidc.com/Linux/2013-08/89270.htm

MySQL-5.5.38通用二进制安装 http://www.linuxidc.com/Linux/2014-07/104509.htm

--------------------------------------分割线 --------------------------------------

本文永久更新链接地址http://www.linuxidc.com/Linux/2014-09/107267.htm

linux
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款