MySQL优化班13期课后作业,20180724

T3.2
1、主键索引和辅助索引有什么区别
2、主键索引和聚集索引有什么区别
3、什么是覆盖索引,和联合索引有什么关系吗
 
T3.3
1、既然InnoDB表通过主键就可以快速访问row data,那么还有必要留着辅助索引吗
2、InnoDB表如果选用rowid作为聚集索引,会有什么问题吗
已邀请:

aaron8219 - Oracle DBA

赞同来自: k2865 jackmao90

T3.2
1、主键索引和辅助索引有什么区别?
答:
1)Innodb表是索引组织表(IOT),采用B+Tree索引结构,主键索引的叶子节点包含了全部记录,而辅助索引(二级索引)的叶子节点只包含了主键值;
2)通过辅助索引查询数据时,如果不是覆盖索引的话,是需要回表的,而通过主键索引查询则没有回表的概念。

2、主键索引和聚集索引有什么区别?
答:主键索引一定是聚集索引,但聚集索引不一定是主键索引,还可以是唯一索引或rowid列
1)Innodb引擎表有且只有一个聚集索引,当显式地指定了该表的主键,那么这个主键索引就是聚集索引;
2)当没有显式地创建主键索引,那么Innodb引擎会找第一个唯一索引作为该表的聚集索引;
3)如果既没有主键索引,也没有非唯一索引,那么Innodb引擎会采用Rowid列作为该表的聚集索引。

3、什么是覆盖索引,和联合索引有什么关系吗?
答:概念不同,无必然联系
1)覆盖索引是指查询结果可以直接通过索引获取到全部列值,而不需要回表取数据(体现在执行计划中的Extra列为“using index”);
2)联合索引(组合索引)其本质也是辅助索引/二级索引,是指将多个列按做左匹配原则创建索引,如果恰好查询结果也能通过该索引得到,那么也无需回表,此时也可称之为覆盖索引。

T3.3
1、既然InnoDB表通过主键就可以快速访问row data,那么还有必要留着辅助索引吗?
答:需要
1)在Innodb引擎表中,通常会采用无业务意义的int或bigint自增列作为主键,因此业务中通常都是对一些非主键列的常用字段进行查询,那么就需要在这些字段上创建辅助索引;
2)通过对这些常用字段创建单列索引或联合索引(组合索引),如果能达到覆盖索引(不回表),查询的效率也是很高的;
3)一张表上也不可能只有一个主键索引(聚集索引)而没有辅助索引,尤其是字段非常多的大表,需要有多个辅助索引互相配合,完成高效查询。

2、InnoDB表如果选用rowid作为聚集索引,会有什么问题吗?
答:会
1)采用rowid作为聚集索引,就表示没有显式地创建int/bigint类型业务无关性的自增主键,也没有唯一键,会影响dml语句性能;
2)没有主键的情况下,其他辅助索引(二级索引)也无法通过主键键值回表检索数据,除非能在索引中完成查询,即覆盖索引的情况;
3)在主从复制结构中,无主键的innodb引擎表在从库回放时会导致全表扫描,造成从库延迟。
 

k2865 - A793-文彦章-成都

赞同来自: aaron8219

黎明哥的作业回复太专业了..本来想再复习一下再回..结果感觉看了你的回复我就复习了..所也没不好意思回复了.只有多看几遍..

liyh

赞同来自: aaron8219

有个疑问:
1.innodb 隐藏的全局rowid是一个什么形态?是一个自增值?是十六进制?这个rowid 占用多少字节?用rowid做聚集索引,写磁盘的时候是顺序IO吗?
 
 
 

lprincewz - A912-王争-深圳

赞同来自:

前面回答太全面了,只补充几点
T3.2
1、主键索引和辅助索引有什么区别?
    1)主键索引唯一且不允许为空,唯一索引唯一但是可以为空,普通索引可以为空且不要求唯一
    2)主键索引叶子节点时整行数据,mysql主键索引实际就是整个表,索引叶子节点时索引值及主键值

3、什么是覆盖索引,和联合索引有什么关系吗
    1)是不同概念,但是一般对性能要求极高的SQL,覆盖索引就是通过联合索引实现,比如优化下面SQL
select col2 from tab1 where col1 ="a";

T3.3
1、既然InnoDB表通过主键就可以快速访问row data,那么还有必要留着辅助索引吗
    需要
    1)当SQL条件不是主键而是其他列时,如果没有对应的辅助索引,则要扫描整个表
    2)因为主键索引叶子节点时所用字段,所以主键索引必然比辅助索引大,扫描耗时更高,效率必然不如覆盖索引情况
2、InnoDB表如果选用rowid作为聚集索引,会有什么问题吗
    1)使用全局的rowid为聚集索引会占用全局系统资源,如果这种表数量较多且并发较大,可能会面临性能瓶颈

liyh

赞同来自:

T3.2
1.主键索引和辅助索引的区别
主键索引是聚集索引,是非空,唯一索引。innodb 是索引组织表,是按照聚集索引的顺序物理存储的,因此主键检索时效率非常高。主键索引存储了主键键值,事务id,回滚指针,和其他所有列的值,因此找到主键,就找到了其他列值。
辅助索引,是普通索引,二级索引。记录索引列键值和主键键值。辅助索引可以为空,可以有重复值。在检索数据时使用到辅助索引,如果不能找到所有需要的列(索引不能覆盖所需列),需要通过主键值回表,找到其他列值。

2.主键索引和聚集索引的区别
1)如果显示声明主键,那么主键索引就是聚集索引。
2)如果没有显示声明的主键,innodb 会选择非空的唯一索引列作为聚集索引,如果有多个符合条件的唯一索引,选择排位靠前的列。
3)如果没有主键,且没有合适的唯一索引作为聚集索引,innodb 会选择全局rowid作为聚集索引。
4)也就是说,innodb表可以没有主键,但是一定有聚集索引,如果没有显示的聚集索引,就会按照规则生成隐式聚集索引,并按聚集索引顺序物理存储数据的顺序。
5)主键是非空的唯一值,聚集索引可以有重复值。

3.什么是覆盖索引
覆盖索引:是指在利用索引时,要查询的返回值全部包含在索引内,不需要回表便可以得到结果。
联合索引:所列组合在一起的索引
覆盖索引和索引覆盖是一个意思,本质是就是联合索引,只是使用联合索引时如果返回字段全部包含在联合索引内就叫覆盖或覆盖索引,索引覆盖。

T3.3
1.需要保留辅助索引
1)主键索引时按照主键值来排序的存储的,虽然含有row data所有值,但是如果查询条件并非主键,而是其他列值就没法用主键索引啊。这个时候条件列如果有索引,利用这个辅助索引可以快速定位到要查找的结果。
2)多数情况主键是没有业务意义的自增列值,作为查询条件的概率不高。

2.innodb如果选用rowid做聚集索引
1)rowid做聚集索引,说明这张表没有显示的主键,rowid是全局id,因此不能保证在同一表内是连续的,大量插入时可能有性能问题
2)没有主键,二级索引没法通过主键来回表。

要回复问题请先登录注册