数据库索引

1，什么是索引

索引是关系数据库中对数据库表中一列或所列数据进行排序的存储结构。好比是字典的目录

2，索引的应用场景

数据库中存放大量的数据时，直行sql语句将花费较大时间，时间过长将影响用户体验，并且我们应用中大多数接口都具有查询功能，最后都将落在数据库查询中，所以需要提高数据库查询的速度就需要使用到索引，就像通过目录查字典就会很快查到结果

3，数据库查询原理

通过IO读写在磁盘中一块一块查找目标数据，该过程称为全表扫描

4，IO读写是在磁盘中一条一条查，索引也是如此，为什么索引快

因为索引采用了不同的数据结构（B+树），该数据结构在查询上有明显优势

5，索引的分类

主键索引，普通索引，唯一索引，联合索引，全文索引。

其中联合索引需要满足最左前缀法则才能实现索引。

6，索引的物理存放位置

innoDB类型的索引与数据存放在一个文件中

myisam类型的索引和数据分别存放在两个文件中

mysql 有一项功能

slow query log

顾名思义，就是慢查询日志，日志中记录的是执行时间较长的query，也就是我们常说的slow

query，通过设--log-slow-queries[=file_name]来打开该功能并设置记录位置和文件名，

默认文件名为hostname-slowlog，默认目录也是数据目录。

两种启用方式:

1, 在mycnf 里 通过 log-slow-queries[=file_name]

2, 在mysqld进程启动时,指定--log-slow-queries[=file_name]选项

慢查询日志是文本格式，可以通过各种文本编辑器查看其中的内容。其中记录了语句执行的时刻，执行所消耗的时间，执行用户，连接主机等相关信息。MySQL 还提供了专门用来分析满查询日志的工具程序mysqlslowdump，可以解析这个文件。网上有很多关于解析此日志工具的对比说明，应该有4,5种工具吧，各有优劣。

--log-queries-not-using-indexes

这参数和slow query log 类似，记录没有应用索引的慢查询。

这两种日志都是以时间为基准的，日志记录功能。MySQL 中我还没发现根据 逻辑 IO ，或者内存消耗，作基准的方法，记录高消耗sql的方法。有好方法可以交流。

看错误是文件生成错了，和数据库没关系吧。

可能是：

拟生成的文件已存在，被其他程序使用；（comfutureutilreporttagsERTagUtilcreateReportJS）

或者用户权限不够；

或者目录不存在。

你好，根据你的问题不好判断数据库类型，不同数据库语句不同，此处给出针对Oracle数据库语句，在PL/SQL中执行以下语句即可筛选出占用数据库io高的前5句sql语句，希望对您有帮助！

SELECT SQL_TEXT

FROM (SELECT FROM V$SQLAREA ORDER BY DISK_READS desc)

WHERE ROWNUM <= 5;

数据库的IO密集型和CPU密集型是相对概念。一个查询对一个CUP很多很快的服务器而言，可能是IO密集型，对一个装备高速磁盘阵列的服务器而言可能变成CPU密集型。

英特尔最新推出了第六代酷睿产品，采用全新一代的架构，性能提示、功能降低、续航更加长久、无论办公学习、畅玩游戏或者观看超高清影音，均得心应手，您也可以试试。

SQLite创建的数据库有一种模式IN-MEMORY，但是它并不表示SQLite就成了一个内存数据库。IN-MEMORY模式可以简单地理解为，（2020 表述勘误：本来创建的数据库文件是基于磁盘的，现在整个文件使用内存空间来代替磁盘空间，没有了文件作为backingstore，不必在修改数据库后将缓存页提交到文件系统），其它 *** 作保持一致。也就是数据库的设计没有根本改变。

inmemory与tempdb是两种节约模式，节约的对象为（rollback）日志文件以及数据库文件，减少IO。inmemory将日志写在内存，并且去除数据库文件作为backingStore，缓存页不用提交到文件系统。tempdb只会在只会在脏的缓存页超过当前总量的25％才会同步刷写到文件，换句话说在临时数据库模式下，事务提交时并不总同步脏页，因此减少了IO数量，事务日志也受这种机制影响，所以在临时数据库模式下，事务日志是不是MEMORY并不重要。回过头来看，内存模式则是临时模式的一种极致，杜绝所有的IO。这两种模式都只能存在一个sqlite3连接，关闭时销毁。

提到内存，许多人就会简单地理解为，内存比磁盘速度快很多，所以内存模式比磁盘模式的数据库速度也快很多，甚至有人望文生意就把它变成等同于内存数据库。

它并不是为内存数据库应用而设计的，本质还是文件数据库。它的数据库存储文件有将近一半的空间是空置的，这是它的B树存储决定的，（2020 勘误：对于固定长度记录，页面使用率最大化，对于非自增计数键的索引，页面一般会保留20～60％的空间，方便插入）请参看上一篇SQLite存储格式。内存模式只是将数据库存储文件放入内存空间，但并不考虑最有效管理你的内存空间，其它临时文件也要使用内存，事务回滚日志一样要生成，只是使用了内存空间。它的作用应该偏向于临时性的用途。

（2020 补充：下面的测试有局限性，）

我们先来看一下下面的测试结果，分别往memory和disk模式的sqlite数据库进行1w, 10w以及100w条数据的插入，采用一次性提交事务。另外使用commit_hook捕捉事务提交次数。

（注：测试场景为在新建的数据库做插入 *** 作，所以回滚日志是很小的，并且无需要在插入过程中查找而从数据库加载页面，因此测试也并不全面）

内存模式



磁盘模式



在事务提交前的耗时 (事务提交后的总耗时)：

1w 10w 100w

内存模式 004s 035s 360s

磁盘模式 006s (027s) 047s (072s) 395s (462s)

可以看到当 *** 作的数据越少时，内存模式的性能提高得越明显，事务IO的同步时间消耗越显注。

上图还有一组数据比较，就是在单次事务提交中，如果要为每条插入语句准备的话

1w 10w 100w

内存模式 019s 192s 1946s

磁盘模式 021s (035s) 206s (226s) 1988s (2041s)

我们从SQLite的设计来分析，一次插入 *** 作，SQLite到底做了些什么。首先SQLite的数据库 *** 作是以页面大小为单位的。在单条记录插入的事务中，回滚日志文件被创建。在B树中查找目标页面，要读入一些页面，然后将目标页面以及要修改的父级页面写出到回滚日志。 *** 作目标页面的内存映像，插入一条记录，并在页面内重排序（索引排序，无索引做自增计数排序，参看上一篇《SQLite数据库存储格式》）。最后事务提交将修改的页面写出到数据库文件，成功后再删除日志文件。在这过程中显式进行了2次写磁盘（1次写日志文件，1次同步写数据库），还有2次隐式写磁盘（日志文件的创建和删除），这是在 *** 作目录节点。以及为查找加载的页面读 *** 作。更加详细可以参看官方文档的讨论章节《Atomic Commit In SQLite》。

如果假设插入100条记录，每条记录都要提交一次事务就很不划算，所以需要批量 *** 作来减少事务提交次数。假设页面大小为4KB，记录长度在20字节内，每页可放多于200条记录，一次事务提交插入100条记录，假设这100条记录正好能放入到同一页面又没有产生页面分裂，这样就可以在单条记录插入事务的IO开销耗损代价中完成100条记录插入。

当我们的事务中，插入的数据越多，事务的IO代价就会摊得越薄，所以在插入100w条记录的测试结果中，内存模式和磁盘模式的耗时都十分接近。实际应用场合中也很少会需要一次插入100w的数据。有这样的需要就不要考虑SQLite。

（补充说明一下，事务IO指代同步数据库的IO，以及回滚日志的IO，只在本文使用）

除了IO外，还有没有其它地方也影响着性能。那就是语句执行。其实反观一切，都是在对循环进行优化。



for (i = 0; i < repeat; ++i)

{

exec("BEGIN TRANS");

exec("INSERT INTO ");

exec("END TRANS");

}



批量插入：



exec("BEGIN TRANS");

for (i = 0; i < repeat; ++i)

{

exec("INSERT INTO ");

}

exec("END TRANS");



当我们展开插入语句的执行



exec("BEGIN TRANS");

for (i = 0; i < repeat; ++i)

{

// unwind exec("INSERT INTO ");

prepare("INSERT INTO ");

bind();

step();

finalize();

}

exec("END TRANS");



又发现循环内可以移出部分语句



exec("BEGIN TRANS");

// unwind exec("INSERT INTO ");

prepare("INSERT INTO ");

for (i = 0; i < repeat; ++i)

{

bind();

step();

}

finalize();

exec("END TRANS");



这样就得到了批量插入的最终优化模式。

所以对sql语句的分析，编译和释放是直接在损耗CPU，而同步IO则是在饥饿CPU。

请看下图



分别为内存模式1w和10w两组测试，每组测试包括4项测试

1只编译一条语句，只提交一次事务

2每次插入编译语句，只提交一次事务

3只编译一条语句，但使用自动事务。

4每次插入编译语句，并使用自动事务。

可以看到测试项目4基本上就是测试项目2和测试项目3的结果的和。

测试项目1就是批量插入优化的最终结果。

下面是探讨内存模式的使用：

经过上面的分析，内存模式在批量插入对比磁盘模式提升不是太显注的，请现在开始关注未批量插入的结果。

下面给出的是磁盘模式01w和02w两组测试，每组测试包括4项测试



可以看到在非批量插入情况，sqlite表现很差要100秒来完成1000次单条插入事务，但绝非sqlite很吃力，因为cpu在空载，IO阻塞了程序。

再来看内存模式20w测试



可以看到sqlite在内存模式，即使在20w次的单条插入事务，其耗时也不太逊于磁盘模式100w插入一次事务。

01w 02w 20w

内存模式（非批量插入） 1587s

磁盘模式（非批量插入） 974s 19828s

编译1次插入语句 每次插入编译1次语句

内存模式（20w，20w次事务） 1110s 1587s

磁盘模式（100w，1次事务） 462s 2041s

in子查询、exists子查询、连接，效率的探讨

以下是SQL的帮助 （高级查询优化概念）

Microsoft® SQL Server™ 2000 使用内存中的排序和哈希联接技术执行排序、交集、联合、差分等 *** 作。SQL Server 利用这种类型的查询计划支持垂直表分区，有时称其为分列存储。

SQL Server 使用三种类型的联接 *** 作：

嵌套循环联接

合并联接

哈希联接

如果一个联接输入很小（比如不到 10 行），而另一个联接输入很大而且已在其联接列上创建索引，则索引嵌套循环是最快的联接 *** 作，因为它们需要最少的 I/O 和最少的比较。有关嵌套循环的更多信息，请参见了解嵌套循环联接。

如果两个联接输入并不小但已在二者联接列上排序（例如，如果它们是通过扫描已排序的索引获得的），则合并联接是最快的联接 *** 作。如果两个联接输入都很大，而且这两个输入的大小差不多，则预先排序的合并联接提供的性能与哈希联接相似。然而，如果两个输入的大小相差很大，则哈希联接 *** 作通常快得多。有关更多信息，请参见了解合并联接。

哈希联接可以有效处理很大的、未排序的非索引输入。它们对复杂查询的中间结果很有用，因为：

中间结果未经索引（除非已经显式保存到磁盘上然后创建索引），而且生成时通常不为查询计划中的下一个 *** 作进行适当的排序。

查询优化器只估计中间结果的大小。由于估计的值在复杂查询中可能有很大的误差，因此如果中间结果比预期的大得多，则处理中间结果的算法不仅必须有效而且必须适度弱化。

哈希联接使得对非规范化的使用减少。非规范化一般通过减少联接 *** 作获得更好的性能，尽管这样做有冗余之险（如不一致的更新）。哈希联接则减少使用非规范化的需要。哈希联接使垂直分区（用单独的文件或索引代表单个表中的几组列）得以成为物理数据库设计的可行选项。有关更多信息，请参见了解哈希联接。

以上就是关于数据库索引全部的内容，包括:数据库索引、mysql 哪个sql 占io、linux数据库查询的时候报at java.io.UnixFileSystem.createFileExclusively(Native Method)等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！