超详细 mysql 主键索引、唯一索引、普通索引、联合索引的区别与实例说明

文章目录

• • 前言
  • （1）功能区别
  • • 1 主键索引
    • 2 唯一索引
    • 3 普通索引
    • 4 联合索引
    • （2）使用条件
    • • 1 索引列的数值
      • 2 数据类型选择
      • 3 数据表复杂且量大
      • （3）索引实例
      • • 1 创建索引（alter | create）
        • 2 删除索引
        • 3 查询效果
        • • （a） explain 字段说明
          • （b） 索引实例
          • （4）失效场景
          • • 1 创建索引失败
            • 2 索引不生效

              前言

              我们很多项目都有可能会使用到mysql，当遇到表格数据量很庞大时，导致慢查询，查询效率就会特别低。
              这时往往会采用索引来提高查询效率，但是盲目使用索引则极有可能达不到期望的效果。
              因此，我们需要先理解，索引的功能区别，与使用索引的条件，以及索引的失效场景。
              

              （1）功能区别

              1 主键索引

              主键也是一种索引，且是我们最常用的索引，使用方法为`primary key(ColumnList)`。
              一张表只允许一个主键索引，且主键列的值不允许重复，不允许为空`NULL`。
              

              2 唯一索引

              使用方法是`unique index IndexName(ColumnList)`。
              一张表格可以有多个唯一索引，唯一索引列的值不允许重复，但允许为空`NULL`。
              

              3 普通索引

              使用方式为`index IndexName(ColumnList)`
              一张表格可以有多个普通索引，普通索引列的值允许重复，允许为空`NULL`。
              

              4 联合索引

              实际上，所有的索引都可以由多个字段列创建，这就叫联合索引。
              但联合索引的功能区别也跟单列索引的功能区别一致。
              

              （2）使用条件

              1 索引列的数值

              索引列、或者组成索引列的数值尽可能唯一。
              唯一的数值索引，在查询的时候可以更快的定位数据记录。
              

              2 数据类型选择

              整型（int）的索引列查询效率要比字符串（char、varchar、text）高。
              

              3 数据表复杂且量大

              索引的创建会对应维护一张索引表，因此在采用索引时候应该是因为数据表复杂且量大。
              

              （3）索引实例

              1 创建索引（alter | create）

              

              

              2 删除索引

              

              3 查询效果

              （a） explain 字段说明

              标识符explain，常被我们用于测试操作语句效率情况。其中需要额外注意的是这些字段：

              

              • type：指查询语句的查询范围，ALL是全表查找；
                • 其中查询效率，由快到慢依次是：const > eq_ref > ref > range > index > all。
                • possible_keys：指查询语句可能会用到的索引列表，NULL表示没有使用索引；
                • ref：指查询语句的关联的列，NULL是没有关联的列；
                • rows：指有多少条记录被查询了；
                • filtered：指查询的记录过滤的比例；
                • Extra：指优化器查询记录的时候，采用的优化方式。
                  • 其中查询效率，由快到慢依次是：Using index > Using where > Using index condition > Using filesort > Using temporary，其他的额外信息并没有太大的差距，主要看查询语句的整体。

                    （b） 索引实例

                    • 不使用索引

                      

                    • 使用单列索引

                      

                    • 使用联合索引

                      

                      （4）失效场景

                      1 创建索引失败

                      • 创建主键索引失败

                        主键索引，一张表只允许存在一个，且列的值不允许重复，不允许为空。
                        如果创建失败，多半是这三个条件有一个不满足。
                        

                        • 创建唯一索引失败

                          唯一索引，一张表中允许存在多个，且列的值不允许重复，但允许为空。
                          如果创建失败，多半是列的值存在重复。
                          

                          2 索引不生效

                          • 联合索引没有生效

                            

                            联合索引，会采用左匹配的方式，activeUserID进行匹配时，会采用索引查找，效率依然很快；
                            但如果使用 tieToUserID进行匹配，则不会触发索引，而依然使用全表遍历，导致索引失效。