数据库基础 ——索引及优化

一、关系型数据库的大致架构

  关系型数据库大致分为两个部分：

  1. DB：数据库（Database），即存储数据的仓库，其本质是一个文件系统，保存一系列有组织的数据。

  2. DBMS：数据库管理系统（Database ManagementSystem）是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一的管理和控制。用户需要通过数据库管理系统来访问数据库中的数据。

  MySQL大致架构如下图所示：

 

 

二、索引模块

  1. 为什么要用索引？

  当数据较少时，全表扫描可以满足我们的检索需要。但当数据量庞大时，全表扫描的速度太慢，需要一种快速查找的方式。

  2. 什么样的信息能成为索引

  主键、唯一键以及普通键都可以作为索引

  3. 索引的数据结构

•   平衡二叉查找树：具有以下性质的二叉树

    （1） 若它的左子树不空，则左子树上所有节点的值均小于它的根节点的值；

    （2） 若它的右子树不空，则右子树上所有节点的值均大于它的根节点的值；

    （3） 左右子树的深度之差不超过一

    （4） 它的左右子树也分别为平衡二叉树；

  为什么不被用在索引上：动态平衡增大开销；结构深度大，IO频繁，速度慢

•   B树：是一种平衡的多路查找树，一棵m阶的B树，或为空树，或为满足以下特征的m叉树：

    （1）树中的每个节点至多有m棵子树；

    （2）若根节点不是叶子节点，则至少有两颗子树

    （3）除根之外的所有非终端节点至少有 [m/2]棵子树；

    （4）所有的非终端节点中包含下列信息数据：（n，P0，K1，P1，K2，P2，.......，Kn，Pn），其中，Ki为关键字，且Ki < Ki+1;  Pi为指向子树节点的指针，且指针Pi-1所指子树中所有节点的关键字均小于Ki，Pn所指子树中所有节点的关键字均大于Kn，n为关键字的个数（n+1为子树个数）

    （5）所有叶子节点都出现在同一层次上

   

•   B+树：应文件系统所需而出的一种B树的变种

  一棵m阶B+树与m阶B树的区别在于：

  （1）有n棵子树的节点中含有n个关键字

  （2）所有的叶子节点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子节点本身依关键字的大小自小而大顺序链接

  （3）所有的非终端节点可以看成是索引部分，节点中仅含有其子树中的最大或最小的关键字

    

  B+树更适合做索引的存储结构：

  （1）B+ 树的磁盘读写代价更低：由于中间节点不存放数据只存放索引信息，这些中间节点相比B树更小，因此同一盘块空间中可以存放更多的节点，那么一次性读入的需要查找的关键字就越多，也就降低了IO读写次数

  （2）B+树的查询效率更加稳定：所有数据的搜索都要从根节点——>叶子节点。查询深度都相同，时间复杂度均为O(LogN)

  （3）B+树更有利于对数据库的扫描：由于所有叶子节点都是顺序链接的，B+树提高了元素遍历的效率，可以快速扫描全部关键字信息，可以更快的范围查询

 

•   Hash索引

  结构类似于Hash表。一定程度上可以快速 查找。

  缺点：1. 不能范围查找 2. 不能排序 3. 不能利用部分索引查询 4. 当Hash值重复多的时候，仍然要扫描

•   BitMap 位图索引

  当某一字段只有有限种情况时，适合建立位图索引，位图索引对该字段的所有情况建立一个向量，例如性别、婚姻状况等。如下表所示：

  

RowId	1	2	3	4	5	....
男	0	1	0	1	1	.....
女	1	0	1	0	0	.....

对于性别这一字段生成向量：男：01011...; 女：10100;

RowId	1	2	3	4	5	....
已婚	1	0	1	0	0	....
未婚	0	1	0	0	0	...
离婚	0	0	0	1	1	...

对于婚姻状况这一字段，有向量：已婚：10100...; 未婚：01000...; 离婚：00011....。。。当我们使用查询语句select * from table where gender = '男' and marry = '已婚'时，只需要将两个向量做 '位与' 操作即可获得哪些行是要查询的。

适用场景：

  1. 建在值重复度高的列上，GP手册建议在100到100000之间。重复度过低则对比其他类型索引没有明显优势；重复度过高，则空间效率和性能会大大降低。

  2. 特定的查询， 例如count、and、or等、

  3. 多维分析，例如一张表有100个字段，用户会使用其中的20个字段作为查询条件（20个中的任意组合），几乎没有办法创建其他合适的索引。但是在这些列上创建20个BitMap索引，那么所有的查询都可以应用到索引。

不适用场景：

  1. 重复度过低的字段 

  2. 重复度过高的字段，如性别，可以建立BitMap索引，但不建议单独作为查询条件使用，建议与其他条件共同过滤

  3. 经常需要修改的列。由于 bitmap 索引结构，插入、删除或修改一条记录都有可能导致原有索引的重构。

  4. BitMap索引锁的粒度非常大，当数据修改时，不仅仅锁住这一条数据，而是锁定bitmap索引中与这条记录处于同一索引范围中的所有记录。

BitMap索引要注意三点：

  1. 尽可能批量操作，减少维护 bitmap 索引的代价；

  2. 在操作结束后马上提交，减少对表的锁定，同时应经常注意 v$lock 视图，检查包含 bitmap 索引的表是否经常被锁；

  3. 点是注意收集统计信息，只有使优化器得到正确的信息，它才能做出正确的判断。

 

4. InnoDB和MyISAM

 密集索引、稀疏索引 和聚集索引、非聚集索引这是两对概念。

 密集索引： 在密集索引中，数据库中的每个搜索键值都有一个索引记录。这样可以加快搜索速度，但需要更多空间来存储索引记录本身。索引记录包含搜索键值和指向磁盘上实际记录的指针。

 稀疏索引：在稀疏索引中，不会为每个搜索关键字创建索引记录。此处的索引记录包含搜索键和指向磁盘上数据的实际指针。要搜索记录，我们首先按索引记录进行操作，然后到达数据的实际位置。如果我们要寻找的数据不是我们通过遵循索引直接到达的位置，那么系统将开始顺序搜索，直到找到所需的数据为止。

聚集索引：索引的叶子节点包含了完整的行数据，即索引与数据保存在同一棵B+树、同一个文件中。搜索到了叶子位置，也就找到了数据。

非聚集索引（辅助索引）：索引的叶子节点不保存完整数据，而是保存可以找到完整数据的信息（主键、物理指针）。

MySQL最主要的两种搜索引擎，它们B+树的索引架构：

  Innodb：每张表有且仅有一个聚集索引，保存在.idb后缀的文件中。

• 若定义了主键，则该主键作为聚集索引。
• 若没有主键，该表的第一个唯一非空索引作为聚集索引
• 若不满足以上条件，innodb内部会生成一个6字节长度的隐藏主键作为聚集索引
• 其它索引均为非聚集索引，但并不存储记录的物理地址，只保存相关键位和其对应的主键值。

  检索过程大致如下：

  MyISAM引擎中表的所有索引均为非聚集索引。叶子节点中保存指向数据的指针。索引保存在  .MYI文件中， 数据保存在  .MYD文件中。

  非聚集索引（辅助索引）一定是密集索引，聚集索引可以是密集索引，也可以是稀疏索引。原因：非聚集索引并不保存数据，数据也不是按照该索引排序的，如果是稀疏索引，找到开头也无法顺序遍历。而聚集索引就可以。

 三、SQL优化

　　 1. 优化慢查询SQL

    show variables like '%query%';  查询数据库参数。slow_query_log: 慢查询日志开关；slow_query_log_file: 慢查询日志位置; long_query_time: 慢查询时间参数。

    show status like '%slow_queries%'; 查看慢查询条数

    如果有些参数没有打开： set global variable_name = value; 有些变量需要重连才能刷新。可以在配置文件中设置默认值 Windows：my.ini; Linux: my.cnf；防止服务重启还原。

    

 

 

2. explain 关键字解析

当出现慢查询时，可以使用explain关键字分析select语句的执行情况，执行结果如下：

  

 

1).  id： 代表sql的编号。id越大优先级越高，也就约先执行。例如：如果有子查询，就会先执行子查询。那么子查询的id就更大。

2).  select_type: 表示查询的类型

• simple：简单select，不使用子查询。
• primary/subquery： 当有子查询时，最外层的select标记为primary,  内层为subquery。

    

• denpendent subquery： 子查询的查询方式依赖于外面的查询结果。导致需要先用外部查询的表筛选一遍内部查询。这是什么鬼东西，不要让它出现，表结构要多不合理才要这样查？？？ 
• union、dependent union 、union result：union、union操作中，查询中处于内层的select（内层的select语句与外层的select语句有依赖关系）
• derived: 被驱动的子查询（子查询位于from子句）
• materialized：被物化的子查询（即子查询变成了临时表存储）： 例如下图

3). table： 输出结果集的表

4). type： 表示的连接类型

  all：全表扫描
 
  index: 索引全扫描，MySQL遍历整个索引来查找匹配的行
 
  range:索引范围扫描，常见于<、<=、>、>=、between等操作符
 
  ref: 使用非唯一性索引或者唯一索引的前缀扫描，返回匹配某个单独值的记录行
 
  eq_ref: 相对于ref来说就是使用的是唯一索引，对于每个索引键值，只有唯一的一条匹配记录（在联表查询中使用primary key或者unique key作为关联条件）
 
  const、system: 单表中最多只有一条匹配行，查询起来非常迅速，所以这个匹配行中的其他列中的值可以被优化器在当前查询中当做常量来处理。例如根据主键或者唯一索引进行的查询。
 
  NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

  type 的性能排序：

    

5). possible_keys: 查询时可能使用的索引。查询的字段中包含哪些索引

6). key：表示实际使用的索引。

7). key_len: 索引字段的长度。

8). ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

9). rows：每张表有多少行被优化器查询。

10). extra： 执行情况的描述和说明

  Using filesort： 表示MySQL会对结果使用一个外部索引排序，而不是从表里按索引次序读到相关内容。可能在内存或磁盘上进行排序。MySQL无法利用索引完成的排序操作成为文件排序。

  Using temporary: 表示MySQL在对查询结果排序时使用临时表。常见于order by 和 group by。

  Using index：表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行。覆盖索引：通过辅助索引就已经足够找到信息，不必查询聚集索引。

  Using where： .....

  Using join buffer: 表明使用了连接缓存。出现在当两个表连接查询时驱动表没有走索引的情况下。

3. 联合索引

  联合索引是指对表上的多个列进行索引。例如：

create table test（
    a int,
    b int,
    primary key (a),
    key idx_a_b(a, b)
）

  本质上联合索引也是一棵b+树，不同的是联合索引的键值的数量是索引列的个数，且键值按定义的顺序排序。如下图所示：

  

  对于查询 select a, b from test where a = xxx and b= xxx;显然是可以使用（a，b）联合索引的。对于单个的a列条件查询，也可以使用这个索引。但是对b的查询select a，b from test where b =xxxx。则不可以使用这棵B+树索引。因为单独的b是没有排序的。但是对特定的a来说，b又是排好序的。例如select * from test where a = 1 order by b desc  SQL优化器会选择使用联合索引。

  最左匹配原则： mysql 会一直匹配直到遇到范围查询（>, <, between, like）就停止匹配，比如 a=3 and b=4 and c> 5 and d = 6 如果建立（a，b，c，d）顺序的联合索引，则d是用不到索引的，如果建立（a，b，d，c）的索引则都可以用到， abd顺序可以任意调整。

4. 一些技巧

• SQL语句中IN包含的值不应过多
• select 指明字段，避免用 * 查询
• 只查询一条时，使用limit 1
• 避免在where子句中对字段进行表达式操作
• 使用联合索引要遵循最左匹配原则 
• 在where子句中使用or拼接，实测无论or两边是否都为索引列，均会全表扫描
• 插入多条数据时尽量批量插入。多次插入数据时，采用手动提交事务
• innodb引擎下 SQL如果没有用到索引，会升级成表锁