从数据库索引到数据库优化

从数据库索引到数据库优化

数据库索引

• 索引是什么？

  索引是一种数据结构，把关键码和它对应的记录相关联。一个索引包含若干个索引项，索引项中应该包含该关键字所对应记录的位置

• 索引的结构

  • 树形索引

  • 多级索引

  • 线性索引

    • 稠密索引 ：对于稠密索引来说，索引项是关键码有序的排列
      

    • 分块索引：将记录分成若干块，块内不要求有序，但是块间是有序（例如第二块最小值大于第一块最大值）。索引表是有序的，记录块的最大关键码和块长和记录对应的位置。
      

    • 倒排索引：索引表记录属性值和对应记录的位置或者对应主键的位置。
      

• 索引的作用
  提高查询速度

• 数据库中索引的存储类型和分类

  • 索引的存储类型：BTREE 和 Hash
  • 分类：

    • 唯一索引和普通索引：唯一索引 UNIQUE 字段中属性值只能出现一次(允许null值) ，主键是一种特殊的唯一索引不允许null值。普通索引允许空值和重复值

    • 单列索引和组合索引：单列索引，在一列中建立的索引。组合索引，在多个列建立的索引，查询的时候只有满足最左前缀才会使用索引。

    • 全文索引和空间索引：全文索引支持CHAT TEXT VARCHAR,在定义的索引列允许全文查找。空间索引，建立索引的时候要求表的存储引擎切换到MyISAM

• 数据库中添加索引：

  • 创建表的时候添加
  • 修改表的结构添加索引：

数据库调优

• 从哪些方面可以优化数据库？

  • 优化查询

    1. 分析查询语句，利用EXPLAIN关键字查看
    2. 设计合理的索引，使用索引查询。但是设计的索引在查询的时候可能会失效：使用LIKE进行模糊查询时，通配符%在第一个位置、使用多列索引的时候不满足最左前缀原则、使用OR关键字的时候，or连接的字段两边都必须是索引列才会使用索引。
    3. 优化子查询：使用子查询数据库会创建一个临时表，用多表连接查询代替。

  • 优化数据库结构

    1. 对于字段较多的表可以进行拆分

    2. 对于经常使用到的查询语句可以建立一个中间表或者在对应的表中添加冗余字段。

       优点：不需要多表连接查询，增加查询速度。

       缺点： 浪费空间，额外添加的表和字段需要和基准表中的数据保持一致。感觉不如直接使用视图

    • 优化插入记录的速度

      插入记录的时候，影响插入速度的主要是索引，唯一性检查，外键约束，一次插入数据的速度，数据库事务的提交

      MyISAM:

      1. 插入的时候关闭索引，插入完成开启索引
      2. 禁用唯一性检查
      3. 使用批量插入 ：一条insert语句中插入多条记录，使用LOAD DATA INFILE

      InooDB:

      1. 禁用唯一性检查，插入完毕后开启
      2. 禁用外键检查，插入完毕后开启
      3. 禁止事务的自动提交

  • 分析表、检查表、优化表

    1. 分析表：ANALYZE TABLE 分析关键字的分布
    2. 优化表：OPTIMIZE TABLE 消除因为删除或者更新造成的空间浪费
    3. 检查表：CHECK TABLE 检查表中的错误

  • 优化数据库服务器（从服务器硬件和软件考虑）

    • 硬件的角度： 增大服务器的内存
    • 软件：服务器参数的优化，主要指堆，内存的初始化大小，允许的最大连接线程数。

• 优化数据库服务器（从服务器硬件和软件考虑）

  • 硬件的角度： 增大服务器的内存
  • 软件：服务器参数的优化，主要指堆，内存的初始化大小，允许的最大连接线程数。