阅读 38

MySQL索引优化之路

文章已同步至GitHub开源项目: Java超神之路

概念

  • MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构

​ 可以理解为:排好序的快速查找的数据结构

  • 数据本身之外,数据库还维护着一个满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这样就可以在这些数据结构的基础上实现高级查找算法,这种数据结构就是索引。

  • 一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上

优势

  • 类似大学图书馆建书目索引,提高数据检索的效率,降低数据库的IO成本

  • 通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗

劣势

  • 实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用空间的

虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息

  • 索引只是提高效率的一个因素,如果有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句

mysql索引分类

  • 主键索引

    ​ 设定为主键后数据库会自动建立索引,innodb为聚簇索引

  • 单值索引

​ 即一个索引只包含单个列,一个表可以有多个单列索引

  • 唯一索引

    ​ 索引列的值必须唯一,但允许有空值

  • 复合索引

    ​ 即一个索引包含多个列

索引基本语法

  • 创建

    #方式1
    CREATE [UNIQUE] INDEX 索引名 ON 表名(字段名1(长度),字段名2(长度))
    #方式2
    ALTER 表名 ADD  [UNIQUE ]  INDEX [索引名] ON (字段名1(长度),字段名2(长度)) 
    复制代码
  • 删除

    DROP INDEX [索引名] ON 表名; 
    复制代码
  • 查看

    SHOW INDEX FROM 表名;
    复制代码

mysql索引结构

  • BTree索引

    • 原理图

      image-20211003202741092

    • 说明

      • 【初始化介绍】 一颗b树,浅蓝色的块我们称之为一个磁盘块,可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示),如磁盘块1包含数据项17和35,包含指针P1、P2、P3,P1表示小于17的磁盘块,P2表示在17和35之间的磁盘块,P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点不存储真实的数据,只存储指引搜索方向的数据项,如17、35并不真实存在于数据表中。

      【查找过程】如果要查找数据项29,那么首先会把磁盘块1由磁盘加载到内存,此时发生一次IO,在内存中用二分查找确定29在17和35之间,锁定磁盘块1的P2指针,内存时间因为非常短(相比磁盘的IO)可以忽略不计,通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存,发生第二次IO,29在26和30之间,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次IO,同时内存中做二分查找找到29,结束查询,总计三次IO。

      • 真实的情况是,3层的b+树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的,如果没有索引,每个数据项都要发生一次IO,那么总共需要百万次的IO,显然成本非常非常高。
  • full-text全文索引 [Java工程师知道即可]

  • Hash索引 [Java工程师知道即可]

  • R-Tree索引 [Java工程师知道即可]

哪些情况需要创建索引

  1. 主键自动建立唯一索引

  2. 频繁作为查询条件的字段应该创建索引(where 后面的语句)

  3. 查询中与其它表关联的字段,外键关系建立索引

  4. 单键/组合索引的选择问题,who?(在高并发下倾向创建组合索引)

  5. 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度

  6. 查询中统计或者分组字段

哪些情况不要创建索引

  • 表记录太少(统计数据显示,大于300万记录的表,建立索引会有效果)

  • 经常增删改的表

    • Why:提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件
  • Where条件里用不到的字段不创建索引

  • 重复且可选项较少的字段不需要建立索引,没有效果

    • 假如一个表有10万行记录,有一个字段A只有T和F两种值,且每个值的分布概率大约为50%,那么对这种表A字 段建索引一般不会提高数据库的査询速度。
    • 索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录,表索引列有1980个 不同的值,那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近1,这个索引的效率就越高

文章已同步至GitHub开源项目: Java超神之路 更多Java相关知识,欢迎访问!

文章分类
后端
文章标签