索引模块

新生代农民工
276 阅读5分钟

为什么要使用索引

为了避免全表扫描，当然只有十几行的数据全表直接加载到数据库的速度还是很快的，通过索引来大幅度提升查找速度

索引的数据结构

  • 生成索引，建立二叉查找树进行二分查找
  • 生成索引，建立BTree结构进行查找
  • 生成索引，建立B+Tree结构进行查找
  • 生成索引，建立Hash结构进行查找

二叉查找树

image.png

时间复杂度：O（logN）

缺点：新增数据会出现O（N）

image.png

BTree

结构图自行查找

定义

  • 根节点至少包括两个孩子
  • 树中每个节点最多包含m个孩子（m>=2）,m是阶数
  • 除根节点和叶子节点外，其它每个节点至少有celi（m/2）个孩子
  • 所有叶子节点都位于同一层

B+Tree

图自行百度

B+树是B树的变体，其定义基本与B树相同，除了：

  • 非叶子节点的子树指针与关键字个数相同
  • 非叶子节点的子树指针P[i],指向关键字[k[i],k[i+1]]的子树
  • 非叶子节点仅用来做索引，数据都保存在叶子节点中
  • 所有叶子节点均用一个链指针指向下一个叶子节点

B+树更适合用来存储索引，因为B+树的磁盘读写带价更低，B+树的查询效率更快，B+树更有利于对数据库的扫描因为每个叶子都有一个链指针链接起来

Hash

hash索引也可以考虑一下

优点是查询效率也高

缺点：

  • 仅仅能满足“=”、“IN”的查询，不能使用范围查询，因为进行hash运算后的大小值并不能保证原来值的大小
  • 无法被用来避免数据的排序操作
  • 不能利用部分索引键查询
  • 不能避免表托表（会出现hash碰撞）
  • 遇到大量Hash值相等情况后性能并不一定就会比B+Tree高

BitMap

可以自行了解下，适合并发量较小，查询较多的应用场景。

密集索引和稀疏索引的区别？

密集索引文件中每个搜索码值都对应一个索引值，稀疏索引文件中只为索引码的某些值建立索引

INNODB有且只有一个密集索引，若一个主键被定义，该主键则作为密集索引，若没有主键被定义，该表的第一个唯一非空索引则作为密集索引，若不满足以上条件，innodb内部会生成一个隐藏主键（密集索引），非主键索引存储相关键位和对应主键值，包含两次查找（回表）。

Innodb（聚簇）表分布

image.png

MyISAM（非聚簇）表分布

image.png

区别:InnoDB他的数据和索引是存一块的，MyISAM的数据和索引是分开的

MySQL数据库不支持BitMap，并且InnoDB和MyISAM不支持hash索引

如何定位并优化慢查询SQL

1、根据慢查询日志定位慢查询SQL

//检查相关参数
show variables like '%quer%';
//mysql会话关闭就会清空 查看慢sql的数量
show status like '%show_queries%';
//打开慢查询
set global show_query_log = on;
//设置查询时间超过1s为慢查询，此参数设置需要重新打开MySql
set global long_query_time = 1;

2、使用explain等工具分析sql 3、修改sql或尽量让sql走索引

Explain关键字段

id--->值越大，执行优先级越高，复合查询来说里面的语句的ID是比较大的

type--->性能从高到低：

System>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all

extra--->Using filesort:表示mysql会对结果使用一个外部索引排序，而不是从表里按索引次序读取到相关内容，可能在内存或者磁盘上进行排序，Mysql无法利用索引完成的排序操作成为“文件排序”。Using temporary：表示MySql在对查询结果排序时使用临时表，常见于排序order by和分组查询group by。

案例分析

//添加索引
alter table 表名 add index 索引名（字段名）;

explain select count(id) from 表名;

分析：如果ID是主键，且该表另一个字段也是索引，则不会走主键索引，因为查询优化器来决定密集索引把其他列的数据也存放到了叶子节点，效率会比稀疏索引低，因为稀疏索引只存放了关键项和主键的值，所以查询优化器选择了它

强制使用主键索引

explain select count（id） from 表名 force index(primary)

联合索引的最左匹配成因

A和B组成联合索引，使用A和B查询走联合索引，使用A也走联合查询，使用B不走联合查询

1.最左匹配原则，非常重要的原则，mysql会一直向最左匹配直到遇到范围查询（>,<,like,between）就停止匹配，比如a=3 and b=4 and c>2 and d=6，如果建立了abcd顺序的索引，d就用不到索引的，如果建立了abdc的索引则都可以用到，abd的顺序可以任意调整 2.=和in可以乱序，比如a=1 and b=2 and c=3则建立abc可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式，mysql创建复合索引的规则是首先会对复合索引最左边的，第一个索引字段进行排序，在第一个字段进行排序后进行第二个字段进行排序。

索引是建的越多越好吗？

  • 数据量小的数据，不需要建立索引，建立会增加额外的索引开销
  • 数据变更需要维护索引，因此更多的索引意味着更多的维护成本
  • 更多的索引也意味着需要更多的空间
avatar
文章
阅读
粉丝