InnoDB 建立索引案例-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

InnoDB 建立索引案例

简介:

实例:一般id作为表的主键,在存在unique key 的情况下,索引的建立有两种方式:

     1、 primary key 与 unique 并存

     2、 将 unique key 转变为primary key .

测试:

    表 uah   Primary key  (id), UNIQUE KEY (`user_id`,`fight_xml_id`)

CREATE TABLE `uah`  
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `fight_xml_id` bigint(20) NOT NULL,
 `user_id` bigint(20) NOT NULL,
 `awards` varchar(1000) NOT NULL,
 `created_at` bigint(20) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `index_uid_xmlid` (`user_id`,`fight_xml_id`)
) ENGINE=InnoDB AUTO_INCREMENT=95097 DEFAULT CHARSET=utf8
    表: com_uah Primary key   (user_id,fight_xml_id )
CREATE TABLE `com_uah` (
 `fight_xml_id` bigint(20) NOT NULL,
 `user_id` bigint(20) NOT NULL,
 `awards` varchar(1000) NOT NULL,
 `created_at` bigint(20) NOT NULL,
 PRIMARY KEY (`user_id`,`fight_xml_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 
     表中都含有9W条记录
测试select 查询:
     表 uah
explain select  awards from uah where user_id=80204 and fight_xml_id=42104\G
*************************** 1. row ***************************
           id: 1
 select_type: SIMPLE
        table: uah
         type: const
possible_keys: index_uid_xmlid
          key: index_uid_xmlid
      key_len: 16
          ref: const,const
         rows: 1
        Extra: 
1 row in set (0.00 sec)
   索引采用 unique key , key_len=16, type=const,在没有cache的情况下,select 通过unique key 找到主键 再通过主键找到awards 值。
   表 com_uah:
explain select awards from com_uah where user_id=80204 and fight_xml_id=42104\G
*************************** 1. row ***************************
           id: 1
 select_type: SIMPLE
        table: com_uah
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 16
          ref: const,const
         rows: 1
        Extra: 
1 row in set (0.00 sec)

     直接通过主键查找。属于常量查询。 Key_len=16,没有索引二次查找。

两个表的基础信息:
show table status like 'uah'\G
*************************** 1. row ***************************
           Name: uah
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 94803
 Avg_row_length: 160
    Data_length: 15220736
Max_data_length: 0
   Index_length: 4734976
      Data_free: 24117248
 Auto_increment: 95097
    Create_time: 2012-10-31 14:58:53
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment: 
1 row in set (0.00 sec)
 
mysql> show table status like 'com_uah'\G
*************************** 1. row ***************************
           Name: com_uah
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 88328
 Avg_row_length: 172
    Data_length: 15253504
Max_data_length: 0
   Index_length: 0
      Data_free: 24117248
 Auto_increment: NULL
    Create_time: 2012-10-31 15:03:16
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment: 
1 row in set (0.00 sec)

    两个表在 data_length  com_uah > uah

    Avg_row_length :      com_uah > uah

    究其原因是 对于com_uah 表主键相比于id 大很多。 如果 在 com_uah 含有二级索引的话,他们都会存储 primary key ,com_uah 将会更大。

    对于游戏行业来说 频繁的 update ,insert ,delete来说可能造成更多的碎片 而导致更多的随机IO.

    综上: 利用id 做主键还是比较合适

    可能有同学要问:如果 com_uah 中 user_id 做primary key 呢?
在业务允许的情况下,uid 可以做为primary key ,含有二级索引的情况下同样也可以。(不能一概而论,具体情况具体分析,可这个案例业务不允许。)





本文转自 位鹏飞 51CTO博客,原文链接:http://blog.51cto.com/weipengfei/1045421,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章
最新文章
相关文章