Mysql的索引大全以及如何添加索引

YuShiwen
559 阅读6分钟

持续创作，加速成长！这是我参与「掘金日新计划 · 6 月更文挑战」的第1天，点击查看活动详情

往期优秀文章

  1. MySQL的存储引擎(InnoDB与MyISAM)
  2. mysql索引的数据结构为什么用B+树，知其所以然

1 索引分类

  • 普通索引 普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHEREcolumn=）或排序条件（ORDERBYcolumn）中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。

  • 唯一索引 普通索引允许被索引的数据列包含重复的值。比如说，因为人有可能同名，所以同一个姓名在同一个“员工个人资料”数据表里可能出现两次或更多次。 如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。 这么做的好处：一是简化了mysql对这个索引的管理工作，这个索引也因此而变得更有效率；二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。 也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。 注意：唯一索引和普通索引使用的结构都是B+树,执行时间复杂度都是O(log n)。

  • 主索引 在前面已经反复多次强调过：必须为主键字段创建一个索引，这个索引就是所谓的"主索引"。主索引与唯一索引的唯一区别是：前者在定义时使用的关键字是PRIMARY而不是UNIQUE。

  • 外键索引 如果为某个外键字段定义了一个外键约束条件，MySQL就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。

    建立外键的好处：

    1. 由数据库保证数据完整性，比程序保证完整性更可靠，多应用时（如有应用A,B,C他们之间的实体存在关联关系），由程序来保证数据完整性变得困难

    2. 外键约束使得数据库的ER图可读性变强，有助于业务逻辑设计

    不建立外键的好处：

    1. 可以用触发器或应用程序保证数据的完整性

    2. 开发变得简单，维护数据时不用考虑外键约束

    3. 性能高，大数据量插入操作时不用考虑维护外键

    结论： 不建立外键约束，关联关系由程序控制，另外还需要删除现有的外键关系

    此外，从面向对象设计的角度来看。是应该取消掉外键约束的。因为数据库的作用就是高效的存取数据。而不是表达业务逻辑关系。把业务逻辑关系放到数据库中来维护是一种非常面向过程的思维。使得程序设计，用例规则直接面向数据库而不是面向业务。怎么看都不是一种好的方式。

  • 复合索引 索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索引。这种索引的特点是MySQL可以有选择地使用一个这样的索引。如果查询操作只需要用到columnA数据列上的一个索引，就可以使用复合索引INDEX(columnA, columnB)。不过，这种用法仅适用于在复合索引中排列在前的数据列组合。比如说，INDEX(A, B, C)可以当做A或(A, B)的索引来使用，但不能当做B、C或(B, C)的索引来使用。

  • 全文索引

    MyISAM存储引擎才支持全文索引，InnoDB存储引擎不支持全文索引。

    文本字段上的普通索引只能加快对出现在字段内容最前面的字符串(也就是字段内容开头的字符)进行检索操作。如果字段里存放的是由几个、甚至是多个单词构成的较大段文字，普通索引就没什么作用了。这种检索往往以LIKE %word%的形式出现，这对MySQL来说很复杂，如果需要处理的数据量很大，响应时间就会很长。 　　这类场合正是全文索引(full-text index)可以大显身手的地方。在生成这种类型的索引时，MySQL将把在文本中出现的所有单词创建为一份清单，查询操作将根据这份清单去检索有关的数据记录。全文索引即可以随数据表一同创建，也可以等日后有必要时再使用下面这条命令添加： 　　ALTER TABLE tablename ADD FULLTEXT(column1, column2) 　　有了全文索引，就可以用SELECT查询命令去检索那些包含着一个或多个给定单词的数据记录了。下面是这类查询命令的基本语法： 　　SELECT * FROM tablename 　　WHERE MATCH(column1, column2) AGAINST(‘word1’, ‘word2’, ‘word3’) 　　上面这条命令将把column1和column2字段里有word1、word2和word3的数据记录全部查询出来。

2对索引的操作：

2.1如何看表中的索引：

show keys from users;

image-20220524215826857.png

红框中的Cardinality叫做基数，指表中对应索引不重复数据的数量，我们也可以通过看这个来考虑是否要给某个字段加索引。

2.2前缀索引：

同时，为了节省索引在B+树节点中索引key存放的空间大小，我们可以设置前缀索引，但前缀索引也有它的缺点，不能在 order by 或者 group by 中触发前缀索引，也不能把它们用于覆盖索引。

当字符串本身可能比较长，而且前几个字符就开始不相同，适合使用前缀索引；相反情况下不适合使用前缀索引，比如，整个字段的长度为 20，索引选择性为 0.9，而我们对前 10 个字符建立前缀索引其选择性也只有 0.5，那么我们需要继续加大前缀字符的长度，但是这个时候前缀索引的优势已经不明显，就没有创建前缀索引的必要了。

我们也可以通过如下的分组统计语句来查看前缀索引的长度应该设置多长。

select count(*) as num ,name as name  from users group by name;

上面这条语句是查看原本长度以name分组统计的数量

select count(*) as num ,left(name,4) as name  from users group by name;

之后我们在使用上面这条语句，不断的增加left的第二个参数的大小，看num列的数值与上一次查询的结果相差多少，如果相差不大，我们就基本上可以确定这个前置的长度是多少了。（笔者这里的数据只有一行，所以一直都是1）

image-20220524220216387.png

Author:YuShiwen
于稀土掘金
2022.5.26
avatar
文章
阅读
粉丝