认识达内从这里开始

我们在前几期的文章中给大家简单介绍了程序员在学习数据库索引的时候需要掌握的一些基础知识点等内容，而本文我们就继续来了解一下，索引的设计原则与用法分享。

索引的设计原则?

对于经常作为查询条件的字段，应该建立索引，以提高查询速度

为经常需要排序、分组和联合操作的字段建立索引

索引列的区分度越高，索引的效果越好。比如使用性别这种区分度很低的列作为索引，效果就会很差。

避免给"大字段"建立索引。尽量使用数据量小的字段作为索引。因为MySQL在维护索引的时候是会将字段值一起维护的，那这样必然会导致索引占用更多的空间，另外在排序的时候需要花费更多的时间去对比。

尽量使用短索引，对于较长的字符串进行索引时应该指定一个较短的前缀长度，因为较小的索引涉及到的磁盘I/O较少，查询速度更快。

索引不是越多越好，每个索引都需要额外的物理空间，维护也需要花费时间。

频繁增删改的字段不要建立索引。假设某个字段频繁修改，那就意味着需要频繁的重建索引，这必然影响MySQL的性能

利用左前缀原则。

索引什么时候会失效?

导致索引失效的情况：

对于组合索引，不是使用组合索引左边的字段，则不会使用索引

以%开头的like查询如%abc，无法使用索引;非%开头的like查询如abc%，相当于范围查询，会使用索引

查询条件中列类型是字符串，没有使用引号，可能会因为类型不同发生隐式转换，使索引失效

判断索引列是否不等于某个值时

对索引列进行运算

查询条件使用or连接，也会导致索引失效

什么是前缀索引?

有时需要在很长的字符列上创建索引，这会造成索引特别大且慢。使用前缀索引可以避免这个问题。

前缀索引是指对文本或者字符串的前几个字符建立索引，这样索引的长度更短，查询速度更快。

创建前缀索引的关键在于选择足够长的前缀以保证较高的索引选择性。索引选择性越高查询效率就越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的数据行。

什么是左匹配原则?

如果SQL语句中用到了组合索引中的左边的索引，那么这条SQL语句就可以利用这个组合索引去进行匹配。当遇到范围查询(>、<、between、like)就会停止匹配，后面的字段不会用到索引。

对(a,b,c)建立索引，查询条件使用a/ab/abc会走索引，使用bc不会走索引。

对(a,b,c,d)建立索引，查询条件为a=1andb=2andc>3andd=4，那么a、b和c三个字段能用到索引，而d无法使用索引。因为遇到了范围查询。

左前缀

当a的值确定的时候，b是有序的。例如a=1时，b值为1，2是有序的状态。当a=2时候，b的值为1，4也是有序状态。当执行a=1andb=2时a和b字段能用到索引。而执行a>1andb=2时，a字段能用到索引，b字段用不到索引。因为a的值此时是一个范围，不是固定的，在这个范围内b值不是有序的，因此b字段无法使用索引。

什么是聚集索引?

InnoDB使用表的主键构造主键索引树，同时叶子节点中存放的即为整张表的记录数据。聚集索引叶子节点的存储是逻辑上连续的，使用双向链表连接，叶子节点按照主键的顺序排序，因此对于主键的排序查找和范围查找速度比较快。

聚集索引的叶子节点就是整张表的行记录。InnoDB主键使用的是聚簇索引。聚集索引要比非聚集索引查询效率高很多。

对于InnoDB来说，聚集索引一般是表中的主键索引，如果表中没有显示指定主键，则会选择表中的一个不允许为NULL的索引。如果没有主键也没有合适的索引，那么InnoDB内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键长度为6个字节，它的值会随着数据的插入自增。

什么是覆盖索引?

select的数据列只用从索引中就能够取得，不需要回表进行二次查询，也就是说查询列要被所使用的索引覆盖。对于innodb表的二级索引，如果索引能覆盖到查询的列，那么就可以避免对主键索引的二次查询。

不是所有类型的索引都可以成为覆盖索引。覆盖索引要存储索引列的值，而哈希索引、全文索引不存储索引列的值，所以MySQL使用b+树索引做覆盖索引。

对于使用了覆盖索引的查询，在查询前面使用explain，输出的extra列会显示为usingindex。

比如user_like用户点赞表，组合索引为(user_id,blog_id)，user_id和blog_id都不为null。

【免责声明】：本内容转载于网络，转载目的在于传递信息。文章内容为作者个人意见，本平台对文中陈述、观点保持中立，不对所包含内容的准确性、可靠性与完整性提供形式地保证。请读者仅作参考。更多内容请加抖音达内三江区域学习了解。