Mysql索引的优化分析-索引的简介_介绍下索引优化-优快云博客

本文深入探讨MySQL中的索引概念，包括其定义、作用原理、不同类型及其应用场合。通过实例讲解索引如何提升查询效率，同时也讨论了索引的局限性和不当使用的后果。

前言：好记性不如烂笔头，记录下平时的学习工作的心得，作为大数据时代，数据是根本，性能是王道，数据库索引是提高sql速度的基础，那就从mysql的开始我的博客之旅吧！

一 .索引是什么？

Mysql官方对索引的定义为：索引（Index）是帮助Mysql高效获得数据的数据结构可以得到索引的本质：索引是数据结构
可以简单的理解为：排好序的快速查找数据结构，是解决where 和orderBy 的查找方式，下图就是一种可能的索引方式

为了加快Col2的查找，可以维护一个右边的所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针。这样就可以运用二叉查找在一定的复杂度内获取到相应的数据，从而快速的检索出符合条件的记录。

假设col1 是bookId 扫码机扫到了柯南这本书的ID为4，索引引键值为91 依次和34 89 91 比对只用了三次就查找出该书对应的物理地址，如果不建立索引只有一次次的遍历，大大的降低的查询的性能
一般来说索引本身也很大，不可能全部存储在磁盘中，因此索引往往以索引文件的形式存储在磁盘上
我们平常所说的索引，如果没有特别的指明，都是指B树（多路搜索树，并不一定是二叉的）组织结构的索引
复合索引，前缀索引，唯一索引默认的都是使用B+数索引，统称为索引，当然除了B+树这种类型还有哈希索引（hash_index）等
结论：在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据上实现高级查找算法。这种数据结构就是索引

二 .索引的优缺点

1.优点

类似大学图书馆建立书目索引，提高数据检索的效率，降低数据库的IO成本，MySQL在获取一条记录的时候，可以采取逐条扫描或者索引访问两种不同的方法。假设采取第一种方法，要获取id为1234的记录，就需要顺序地、依次地访问过前1233条记录。不仅如此，还需要考虑每次读入数据页的IO开销。而如果采取索引，则可以根据索引指向的页以及记录在页中的位置，迅速地读取目标页进而获取目标记录。
通过索引对数据进行排序，降低了数据排序的成本，降低了CPU的消耗

2.缺点

实际上索引也是一张表，该表保存了主键索引的字段，并指向实体表的记录，所以索引也是要占空间的
虽然索引大大提高了查询的速度，但是却降低了更新表的速度，对表进行insert update
delete 操作时，mysql不仅仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，会调整因为更新所带来的键值变化后的索引信息。

三 .mysql的索引分类

单值索引：即一个索引只包含单个列，一个表可以有多个单列索引（一张表的索引最好不超过五个）
唯一索引：索引列的值必须唯一，但是可以为空值
复合索引：即一个索引包含多个列
基本语法

创建一个索引：
 create [UNIQUE] INDEX indexName ON mytable(columnname(length));
 ALTER mytable ADD [UNIQUE] INDEX [indexname] ON (columnname(length));

删除一个索引
DROP INDEX [idnexname] on mytable

查看索引
SHOW INDEX FROM tablename

使用ALERT命令添加数据表的索引(4种)
alert table tb_name add primary key(column_list);
表示添加一个主键，意味着索引的值必须是唯一的 

alert table tb_name add UNIQUE index_name(colunm_list);
这条语句创建的索引的值是必须唯一的(除了null以外，null可能会出现多次)

alert table ta_name add index_name(colnum_list);
添加普通的索引，索引的值可能会出现多次

alert table tb_name add fulltext index_name(colnum_list);
指定的索引的类型为fulltext,用于全文索引

四.mysql的索引分类

BTree 索引（！）
- 检索的原理
  
  一颗B+树，浅蓝代表磁盘块，每个磁盘块包含几个数据项（深蓝色）和指针（黄色），例如磁盘1包含数据项17 和35，包含指针P1、P2、P3
  P1代表<17的磁盘块，P2代表17-35之间的磁盘块，P3代表》35的磁盘块
  真实的数据存在于叶子节点上即 3 5 9 10….79 90 99
  非叶子节点不存储真实的数据，只存储指引搜索方法的数据项，例如17 35 并不存在于真实的数据表中
  查找过程： 如果要查找数据项28 ，首先会把磁盘块1由磁盘加载到内存，第一次IO，在内存中二分法查找28 在17 和35 之间，锁定P2指针，通过磁盘1的P2指针的磁盘地址把磁盘3由磁盘加载到内存中，第二次IO ，28在26 30 之间，锁定磁盘3的P2指针，通过指针加载磁盘8到内存，第三次IO,同时内存中做二分查找找到28 结束查询第三次IO
  如果是百万数据，3层的b+数可以三次IO查询，性能提高是巨大的，如果没有索引，每个数据项都要发生一次IO，成本非常高
Hash索引
full-text索引
R-Tree索引

五.哪些情况需要创建索引

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
（如果你总是要找一列书，比如侦探类的，为了查找快，应该建立索引）
查询中和其他表关联的字段，外键的关系建立索引
（比如你看的福尔摩斯，那么他属于文学侦探类，与bookCat 表的有外键关联的关系，那么关联字段应当建立索引）
频繁的更新的字段的不适合建立索引，因为每次更新不仅仅是更新记录还会更新索引，导致sql语句执行的慢
（比如你去找书，但是那个书架的书经常改变，那么索引也需要经常改变，你查索引就花费了时间，甚至会有索引失效的情况）
where条件用不到的字段不参加索引
单建/组合索引choose？who?(高并发倾向创建组合索引)
查询中排序的字段，排序字段若通过索引去访问将大大的提高排序的速度
查询中统计或者分组的字段 groupBy

六.那些情况不需要建立索引

表的内容太少
经常增删改的表
why:提高了查询的速度，同时降低了更新表的速度，因为要同时的保存索引的文件
数据重复的分布平均的表字段，没有必要建立索引
（必须每个人都有性别这个字段，要么是man 要么是women或者是李宇春^-^那么就没必要建立索引）
假如一个表有十万条记录，那么一个字段只有true和fasle 俩种值，且每个值的分布为50%，那么建立索引也不会增加查询速度
索引的选择性：是指索引列中的不同值的数目和表中的记录的比，如果一个表有10000条记录，表的索引列是9900个，那么索引的选择性就是9900/10000=0.99这个值越接近1，那么这个索引的效率就越高