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,如需转载请自行联系原作者
目录
相关文章
|
7月前
|
存储 算法 关系型数据库
深入理解InnoDB索引数据结构和算法
1. **索引定义**:索引是提升查询速度的有序数据结构,帮助数据库系统快速找到数据。 2. **索引类型**:包括普通索引、唯一索引、主键索引、空间索引和全文索引,每种有特定应用场景。 3. **数据结构**:InnoDB使用B+树作为索引结构,确保所有节点按顺序排列,降低查询时的磁盘I/O。 4. **B+树特性**:所有数据都在叶子节点,非叶子节点仅存储索引,提供高效范围查询。 5. **索引优势**:通过减少查找数据所需的磁盘I/O次数,显著提高查询性能。 **总结:**InnoDB索引通过B+树结构,优化了数据访问,使得查询速度快,尤其适合大数据量的场景。
423 0
深入理解InnoDB索引数据结构和算法
|
7月前
|
存储 关系型数据库 MySQL
索引大战:探秘InnoDB数据库中B树和Hash索引的优劣
索引大战:探秘InnoDB数据库中B树和Hash索引的优劣
72 0
|
28天前
|
存储 算法 关系型数据库
InnoDB与MyISAM实现索引方式的区别
InnoDB和MyISAM均采用B+树索引,但在实现上有所不同。InnoDB的主键索引在叶子节点存储完整数据记录,辅助索引则存储主键值;而MyISAM的主键索引与数据文件分离,仅存数据地址,且主辅索引无区别,支持非唯一主索引。
41 1
|
7月前
|
存储 SQL 关系型数据库
系统设计场景题—MySQL使用InnoDB,通过二级索引查第K大的数,时间复杂度是多少?
系统设计场景题—MySQL使用InnoDB,通过二级索引查第K大的数,时间复杂度是多少?
89 1
系统设计场景题—MySQL使用InnoDB,通过二级索引查第K大的数,时间复杂度是多少?
|
7月前
|
存储 算法 关系型数据库
InnoDb行格式、数据页结构、索引底层原理和如何建立索引
InnoDb行格式、数据页结构、索引底层原理和如何建立索引
125 0
|
存储 关系型数据库 MySQL
6.2.2 【MySQL】InnoDB中的索引方案
6.2.2 【MySQL】InnoDB中的索引方案
89 0
|
存储 关系型数据库 MySQL
6.2.3 【MySQL】InnoDB的B+树索引的注意事项
6.2.3 【MySQL】InnoDB的B+树索引的注意事项
84 0
|
12天前
|
存储 关系型数据库 MySQL
Mysql索引:深入理解InnoDb聚集索引与MyisAm非聚集索引
通过本文的介绍,希望您能深入理解InnoDB聚集索引与MyISAM非聚集索引的概念、结构和应用场景,从而在实际工作中灵活运用这些知识,优化数据库性能。
65 7
|
7月前
|
存储 人工智能 关系型数据库
10个行锁、死锁案例⭐️24张加锁分析图🚀彻底搞懂Innodb行锁加锁规则!
10个行锁、死锁案例⭐️24张加锁分析图🚀彻底搞懂Innodb行锁加锁规则!
|
6月前
|
存储 关系型数据库 MySQL
【MySQL技术内幕】5.1-InnoDB存储引擎索引概述
【MySQL技术内幕】5.1-InnoDB存储引擎索引概述
61 0