Java语言数据库索引详解

Java语言数据库索引知识点分析，索引的介绍:书本字典的目录，提升查询效率，避免全表扫描。

索引

• 1、索引的介绍
  • 索引的介绍：书本字典的目录，提升查询效率，避免全表扫描
  • 索引的使用场景
    • 应创建索引的场景
      • where子句上的列
      • 需要排序的列
      • 根据范围搜索
      • 用于连接的列
      • 常需要搜索的列
      • 总结：作为where条件的列和需要查询出来的列
    • 不应该创建索引的场景
      • 搜索较少的列
      • 很少数据值的列
      • text、image的列，数据量大
      • 修改次数远大于查询次数的列
      • 总结：查询少、数据值少、数据量大、修改次数多的列
• 2、索引的优缺点
  • 优点
    • 提高检索效率，降低IO成本
    • 通过索引列排序，降低数据库排序成本，降低cpu消耗
    • 总结：提高检索效率，降低IO成本和CPU消耗
  • 缺点
    • 索引也是一张表，存储主键和索引列，指向实体表的记录，所以索引列也要占空间
    • 索引提高查询速度，但降低更新速度，更新索引列时，会动态更新索引
    • 索引只是提高效率的一个因素，大量数据时，需要建立最优秀的索引或者优化查询语句
    • 总结：索引也需要占用空间、修改频繁得不偿失、索引是提高效率的因素之一
• 3、索引的类别
  • 普通索引：最基本的索引，值可以为空
  • 唯一索引：索引列的值必须唯一，值可以为空
  • 主键索引：特殊的唯一索引，一个表只能有一个主键，不允许有空值
  • 组合索引：多列值组成的一个索引，遵循最左前缀集合，查询条件中只有使用了创建索引时的第一个字段，索引才会被引用
  • 全文索引：目前只有char、varchar、text列可创建全文索引，主要用来查找文本中的关键字，而非直接值比较。数据量大时，先放数据后建索引优于先建索引再放数据。
    • 总结：全文索引是用来查询文中关键字而非直接比较
• 4、索引的失效场景
  • 1、like模糊查询，前模糊或者全模糊不走索引
  • 2、or条件中只要有一个字段没有索引，就不走索引
  • 3、union all代替or有索引列的就会走索引
  • 4、in走索引，not in 不走索引
  • 5、is null走索引，is not null不走索引
  • 6、!=、<>不走索引
  • 7、隐式转化不走索引，如name字段为String类型，这里123为数值类型，进行了类型转换，所以不走索引，改为'123'则走索引
  • 8、函数运算不走索引
  • 9、and 语句，多条件字段最多只能用到一个索引，如果需要，可以建组合索引；
  • 如何查看执行计划分析是否使用索引？
    • 1、使用EXPLAIN PLAN FOR 执行sql
    • 2、接着执行：SELECT * FROM TABLE(DBMS XPLAN.DISPLAY)
    • 3、或者直接选中sql按F5快捷键执行
• 5、聚集索引和非聚集索引的区别
  • 从物理存储方式来区别
    • 聚集索引的物理存储：表结构 和 表索引+数据两个文件。由于索引和数据聚集在同一文件中所以称为聚集索引
    • 非聚集索引的物理存储：表结构 和 表数据 和 表索引三个文件；由于索引和数据没有被聚集在同一文件中所以被称为非聚集索引。
  • 从底层数据结构检索区别
    • 聚集索引：索引和数据保存在同一个B-Tree中，表的数据行都存放在索引树的叶子节点中
    • 非聚集索引：叶子节点中保存的是实际指向存放数据块的指针，非聚集索引需要先查询一遍索引文件，得到索引，根据索引获取数据。