关于java:数据库索引总结一

36次阅读

共计 3893 个字符，预计需要花费 10 分钟才能阅读完成。

为什么要应用索引？

  1. 通过创立唯一性索引，能够保障数据库表中每一行数据的唯一性。
  2. 能够大大放慢 数据的检索速度（大大减少的检索的数据量）, 这也是创立索引的最次要的起因。
  3. 帮忙服务器防止排序和长期表。
  4. 将随机 IO 变为程序 IO
  5. 能够减速表和表之间的连贯，特地是在实现数据的参考完整性方面特地有意义。

索引这么多长处，为什么不对表中的每一个列创立一个索引呢？

  1. 当对表中的数据进行减少、删除和批改的时候，索引也要动静的保护，这样就升高了数据的保护速度。
  2. 索引须要占物理空间，除了数据表占数据空间之外，每一个索引还要占肯定的物理空间，如果要建设聚簇索引，那么须要的空间就会更大。
  3. 创立索引和保护索引要消耗工夫，这种工夫随着数据量的减少而减少。

应用索引的注意事项？

  1. 在常常须要搜寻的列上，能够放慢搜寻的速度；
  2. 在常常应用在 WHERE 子句中的列下面创立索引，放慢条件的判断速度。
  3. 在常常须要排序的列上创 建索引，因为索引曾经排序，这样查问能够利用索引的排序，放慢排序查问工夫；
  4. 对于中到大型表索引都是十分无效的，然而特大型表的话保护开销会很大，不适宜建索引
  5. 在常常用在连贯的列上，这 些列次要是一些外键，能够放慢连贯的速度；
  6. 防止 where 子句中对字段施加函数，这会造成无奈命中索引。
  7. 在应用 InnoDB 时应用与业务无关的自增主键作为主键，即应用逻辑主键，而不要应用业务主键。
  8. 将打算加索引的列设置为 NOT NULL，否则将导致引擎放弃应用索引而进行全表扫描。

    勘误，来自 issue758。将某一列设置为 default null，where 是能够走索引，另外索引列是否设置 null 是不影响性能的。 然而，还是不倡议列上容许为空。最好限度 not null，因为 null 须要更多的存储空间并且 null 值无奈参加某些运算。

    《高性能 MySQL》第四章如是说：And, in case you’re wondering, allowing NULL values in the index really doesn’t impact performance。NULL 值的索引查找流程参考：https://juejin.im/post/5d5defc2518825591523a1db , 相干浏览：MySQL 中 IS NULL、IS NOT NULL、!= 不能用索引？胡扯！。

  9. 删除长期未应用的索引，不必的索引的存在会造成不必要的性能损耗 MySQL 5.7 能够通过查问 sys 库的 chema_unused_indexes 视图来查问哪些索引从未被应用
  10. 在应用 limit offset 查问迟缓时，能够借助索引来进步性能

Mysql 索引次要应用的两种数据结构

哈希索引

对于哈希索引来说，底层的数据结构就是哈希表，因而在绝大多数需要为单条记录查问的时候，能够抉择哈希索引，查问性能最快；其余大部分场景，倡议抉择 BTree 索引。

BTree 索引

MyISAM 和 InnoDB 实现 BTree 索引形式的区别

MyISAM

B+Tree 叶节点的 data 域寄存的是数据记录的地址。在索引检索的时候，首先依照 B +Tree 搜索算法搜寻索引，如果指定的 Key 存在，则取出其 data 域的值，而后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。

InnoDB

其数据文件自身就是索引文件。相比 MyISAM，索引文件和数据文件是拆散的，其表数据文件自身就是按 B +Tree 组织的一个索引构造，树的叶节点 data 域保留了残缺的数据记录。这个索引的 key 是数据表的主键，因而 InnoDB 表数据文件自身就是主索引。这被称为“聚簇索引（或汇集索引）”，而其余的索引都作为辅助索引，辅助索引的 data 域存储相应记录主键的值而不是地址，这也是和 MyISAM 不同的中央。在依据主索引搜寻时，间接找到 key 所在的节点即可取出数据；在依据辅助索引查找时，则须要先取出主键的值，在走一遍主索引。因而，在设计表的时候，不倡议应用过长的字段作为主键，也不倡议应用非枯燥的字段作为主键，这样会造成主索引频繁决裂。PS：整顿自《Java 工程师修炼之道》

笼罩索引介绍

什么是笼罩索引

如果一个索引蕴含（或者说笼罩）所有须要查问的字段的值，咱们就称之为“笼罩索引”。咱们晓得 InnoDB 存储引擎中，如果不是主键索引，叶子节点存储的是主键 + 列值。最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢笼罩索引就是把要查问出的列和索引是对应的，不做回表操作！

笼罩索引应用实例

当初我创立了索引(username,age)，咱们执行上面的 sql 语句

`select username , age from user where username = 'Java' and age = 22` 

*   1

在查问数据的时候：要查问出的列在叶子节点都存在！所以，就不必回表。

抉择索引和编写利用这些索引的查问的 3 个准则

  1. 单行拜访是很慢的。特地是在机械硬盘存储中(SSD 的随机 I / O 要快很多，不过这一点依然成立）。如果服务器从存储中读取一个数据块只是为了获取其中一行，那么就节约了很多工作。最好读取的块中能蕴含尽可能多所须要的行。应用索引能够创立地位引，用以晋升效率。
  2. 按程序拜访范畴数据是很快的，这有两个起因。第一，程序 I/O 不须要屡次磁盘寻道，所以比随机 I / O 要快很多（特地是对机械硬盘）。第二，如果服务器可能按须要程序读取数据，那么就不再须要额定的排序操作，并且 GROUPBY 查问也无须再做排序和将行按组进行聚合计算了。
  3. 索引笼罩查问是很快的。如果一个索引蕴含了查问须要的所有列，那么存储引擎就 不须要再回表查找行。这防止了大量的单行拜访，而下面的第 1 点曾经写明单行访 问是很慢的。

为什么索引能进步查问速度

以下内容整顿自：掘金
地址：数据库两大神器【索引和锁】
作者：Java3y

先从 MySQL 的根本存储构造说起

MySQL 的根本存储构造是页(记录都存在页里边)：

  • 各个数据页能够组成一个双向链表

  • 每个数据页中的记录又能够组成一个单向链表

    • 每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候能够在页目录中应用二分法疾速定位到对应的槽，而后再遍历该槽对应分组中的记录即可疾速找到指定的记录
    • 以其余列 (非主键) 作为搜寻条件：只能从最小记录开始顺次遍历单链表中的每条记录。

所以说，如果咱们写 select * from user where indexname = ‘xxx’这样没有进行任何优化的 sql 语句，默认会这样做：

  1. 定位到记录所在的页：须要遍历双向链表，找到所在的页
  2. 从所在的页内中查找相应的记录：因为不是依据主键查问，只能遍历所在页的单链表了

很显著，在数据量很大的状况下这样查找会很慢！这样的工夫复杂度为 O（n）。

应用索引之后

索引做了些什么能够让咱们查问加快速度呢？其实就是将无序的数据变成有序(绝对)：

要找到 id 为 8 的记录简要步骤：

很显著的是：没有用索引咱们是须要遍历双向链表来定位对应的页，当初通过 “目录” 就能够很快地定位到对应的页上了！（二分查找，工夫复杂度近似为 O(logn)）

其实底层构造就是 B + 树，B+ 树作为树的一种实现，可能让咱们很快地查找出对应的记录。

对于索引其余重要的内容补充

以下内容整顿自：《Java 工程师修炼之道》

最左前缀准则

MySQL 中的索引能够以肯定程序援用多列，这种索引叫作联结索引。如 User 表的 name 和 city 加联结索引就是(name,city)，而最左前缀准则指的是，如果查问的时候查问条件准确匹配索引的右边间断一列或几列，则此列就能够被用到。如下：

`select * from user where name=xx and city=xx ; -- 能够命中索引
select * from user where name=xx ; -- 能够命中索引
select * from user where city=xx ;  -- 无奈命中索引 ` 

*   1
*   2
*   3

这里须要留神的是，查问的时候如果两个条件都用上了，然而程序不同，如 city= xx and name =xx，那么当初的查问引擎会主动优化为匹配联结索引的程序，这样是可能命中索引的。

因为最左前缀准则，在创立联结索引时，索引字段的程序须要思考字段值去重之后的个数，较多的放后面。ORDER BY 子句也遵循此规定。

留神防止冗余索引

冗余索引指的是索引的性能雷同，可能命中 就必定能命中，那么 就是冗余索引如（name,city）和（name）这两个索引就是冗余索引，可能命中后者的查问必定是可能命中前者的 在大多数状况下，都应该尽量扩大已有的索引而不是创立新索引。

MySQL 5.7 版本后，能够通过查问 sys 库的 schema_redundant_indexes 表来查看冗余索引

Mysql 如何为表字段增加索引？？？

1. 增加 PRIMARY KEY（主键索引）

``ALTER TABLE `table_name` ADD PRIMARY KEY (`column`)`` 

*   1

2. 增加 UNIQUE(惟一索引)

``ALTER TABLE `table_name` ADD UNIQUE (`column`)`` 

*   1

3. 增加 INDEX(一般索引)

``ALTER TABLE `table_name` ADD INDEX index_name (`column`)`` 

*   1

4. 增加 FULLTEXT(全文索引)

``ALTER TABLE `table_name` ADD FULLTEXT (`column`)`` 

*   1

5. 增加多列索引

``ALTER TABLE `table_name` ADD INDEX index_name (`column1`, `column2`, `column3`)`` 

*   1

作者：Snailclimb
链接：数据库索引总结 1
起源：github

正文完
java
发表至： java
2020-11-19
 0
关于java:记一次接口慢查排查
关于java:子类继承父类-private
关于java:Java短视频小程序开发-全栈式实战项目2021最新升级版
关于java:JMMjava内存模型