MySQL索引优化之路

程序员阿杜
153 阅读5分钟

文章已同步至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相关知识，欢迎访问！

avatar
文章
阅读
粉丝