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,如需转载请自行联系原作者

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

相关文章
1分钟了解MyISAM与InnoDB的索引差异
数据库的索引分为主键索引(Primary Inkex)与普通索引(Secondary Index)。InnoDB和MyISAM是怎么利用B+树来实现这两类索引,其又有什么差异呢?这是今天要聊的内容。
299 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
10399 0
MongoDB索引介绍
一、Single Field Indexes 示例文档: { "_id": ObjectId("570c04a4ad233577f97dc459"), "score": 1034, "location": { state: "NY", city: "New York" } } 1、语法: db.
763 0
+关注
1338
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载