认识达内从这里开始

数据库的开发架构是大多数软件开发程序员都需要熟练掌握的一个编程技术，而本文我们就通过案例分析来简单了解一下，数据库索引基础知识分享。

为什么使用索引

通过创建性索引，可以保证数据库表中每一行数据的性。

可以大大加快数据的检索速度，这也是创建索引的主要的原因。

帮助服务器避免排序和临时表。

将随机IO变为顺序IO。

可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

索引的分类

普通索引：仅加速查询

索引：加速查询+列值(可以有null)

主键索引：加速查询+列值(不可以有null)+表中只有一个

组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

全文索引：对文本的内容进行分词，进行搜索

索引合并：使用多个单列索引组合搜索

覆盖索引：select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖

聚簇索引：表数据是和主键一起存储的，主键索引的叶结点存储行数据(包含了主键值)，二级索引的叶结点存储行的主键值。使用的是B+树作为索引的存储结构，非叶子节点都是索引关键字，但非叶子节点中的关键字中不存储对应记录的具体内容或内容地址。叶子节点上的数据是主键与具体记录(数据内容)

什么时候需要/不需要创建索引

索引大的好处是提高查询速度，但是索引也是有缺点的，比如：

需要占用物理空间，数量越大，占用空间越大;

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大;

会降低表的增删改的效率，每次增删改索引，B+树为了维护索引有序性，需要进行动态维护。

所以，索引不是万能钥匙，它也是根据场景来使用的。

什么时候适用索引?

字段有性限制的，比如商品编码;

经常用于WHERE查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。

经常用于GROUPBY和ORDERBY的字段，这样在查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在B+Tree中的记录都是排序好的。

什么时候不需要创建索引?

WHERE条件，GROUPBY，ORDERBY里用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的，因为索引是会占用物理空间的。

字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女，如果数据库表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为MySQL还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。

表数据太少的时候，不需要创建索引;

经常更新的字段不用创建索引，因为索引字段频繁修改，由于要维护B+Tree的有序性，那么需要频繁的重建索引，这个过程是会影响数据库性能的。

优化索引的方法

前缀索引优化;

前缀索引顾名思义就是使用某个字段中字符串的前几个字符建立索引。

使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。

覆盖索引优化;

覆盖索引是指SQL中query的所有字段，在索引B+Tree的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作。

使用覆盖索引的好处就是，不需要查询出包含整行记录的所有信息，也就减少了大量的I/O操作。

主键索引好是自增的;

如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为每次插入一条新记录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高。

主键字段的长度不要太大，因为主键字段长度越小，意味着二级索引的叶子节点越小(二级索引的叶子节点存放的数据是主键值)，这样二级索引占用的空间也就越小。

防止索引失效;

用上了索引并不意味着查询的时候会使用到索引，所以我们心里要清楚有哪些情况会导致索引失效，从而避免写出索引失效的查询语句，否则这样的查询效率是很低的。

发生索引失效的情况：

当我们使用左或者左右模糊匹配的时候，也就是like%xx或者like%xx%这两种方式都会造成索引失效;

当我们在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效;

联合索引要能正确使用需要遵循左匹配原则，也就是按照左优先的方式进行索引的匹配，否则就会导致索引失效。

在WHERE子句中，如果在OR前的条件列是索引列，而在OR后的条件列不是索引列，那么索引会失效。

索引使用的注意事项

MySQL索引通常是被用于提高WHERE条件的数据行匹配时的搜索速度，在索引的使用过程中，存在一些使用细节和注意事项。

函数，运算，否定操作符，连接条件，多个单列索引，左前缀原则，范围查询，不会包含有NULL值的列，like语句不要在列上使用函数和进行运算

1)不要在列上使用函数，这将导致索引失效而进行全表扫描。

2)尽量避免使用!=或notin或等否定操作符

3)多个单列索引并不是佳选择

4)复合索引的左前缀原则

5)覆盖索引的好处

6)范围查询对多列查询的影响

7)索引不会包含有NULL值的列

8)隐式转换的影响

9)like语句的索引失效问题

【免责声明】本文系本网编辑部分转载，转载目的在于传递更多信息，并不代表本网赞同其观点和对其真实性负责。如涉及作品内容、版权和其它问题，请在30日内与管理员联系，我们会予以更改或删除相关文章，以保证您的权益!请读者仅作参考。更多内容请加抖音太原达内IT培训学习了解。