MySQL-创建高性能的索引

最新推荐文章于 2024-06-22 19:36:08 发布

Ustinian565

最新推荐文章于 2024-06-22 19:36:08 发布

阅读量62

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_56446161/article/details/132677811

版权

文章目录

前言

主要介绍了MySQL-创建高性能的索引。

一、索引类型

B+Tree索引
B-Tree每个结点都存数据，B+Tree只有叶子结点存数据(大大提高索引的数据存储效率，只需要3-4层就可以存储千万级别的数据量)
- 存储引擎
  - InnoDB
    聚簇索引, 索引叶子结点存储真实数据值；非聚簇二级索引，叶子节点存数据主键值和二级索引列(减少行移动和页分裂出现时的维护工作)
  - MyISAM
    非聚簇索引，索引叶子结点存储数据的行指针
- 所有值按顺序存放，对ORDER BY、GROUP BY速度很快，可以将随机I/O变成顺序I/O
- 常见应用
  - 最左前缀匹配
    多个列的索引可以对按照顺序的前面几个列建立的查询利用索引；必须严格遵循索引列顺序，不可乱序；一个列索引(如字符串)的前部分值也可
  - 匹配范围值
    B+Tree索引的结点按照顺序排序，对范围查找速度很快；多个列索引的前一列利用了范围索引，后列不能再利用索引
  - 覆盖索引
    索引表中存储了所需要查找的数据，InnoDB的聚簇主键索引、利用含有二级索引的列查找主键(二级索引数据存主键)无需回表
哈希索引
- 存储引擎
  - Memory支持非唯一hash索引
    允许多值对应一个hash值的hash冲突，数组+链表形式
  - InnoDB支持自适应hash索引
    在B+Tree的基础上算hash值提高查找速度，在不支持哈希索引的存储引擎上都可以建立自适应哈希索引
- 哈希索引表结点中只存数据的哈希值，使得哈希索引表很小查找效率很高
  计算hash值不能用MD5(作为强加密函数可以用于https的SSL)和SHA1两个函数做hash函数，因为这两个对应的hash值是一个很长的字符串，索引应该小而简单
- 在查找数据时先对比hash值相同的结点，然后在对比结点对应数据是否equals
- 特点
  - 不能覆盖索引
    hash索引表的结点只存hash值和行指针(非聚簇)
  - 不能最左匹配原则
    对应索引的全列值计算该行数据的hash值
  - 不支持范围查找
    只能查找hash等值的数据
  - 出现hash冲突时
    需要遍历链表中所有行指针找到符合条件的行
  - hash冲突太多，索引的维护代价也很高
    java对于hash冲突采用扰动函数(将数据存储的地址先右移再相与最后取模求hash值，融合上下地址信息降低冲突的概率)，非线程安全的hash数组在并发时容易出现死链(两个线程一起并发扩容时一个扩容成功，一个在扩容中但拿到扩容成功的指针，取出顺序和插入顺序一致导致扩容前后元素顺序不一致，引发死链问题)错误
空间数据索引R-TRee
- MyISAM存储引擎支持
- 从所有维度来索引数据，可以用作地理数据索引
全文索引
- 存储引擎
  - MySQL5.6以前，只有MyISAM支持
  - MySQL5.6以后，MyISAM和InnoDB都支持
- 类似于搜索引擎，查找文本中关键字，不是一般的WHERE条件匹配

二、索引优点

小规模数据全表扫描查找效率更佳(减少索引维护的资源消耗)；中规模数据适合用索引；大规模数据建立索引的维护资源消耗大于其优点

减少了服务器需要扫描的数据量
利用索引筛选而非全表搜索
帮助服务器避免排序和临时表
B+Tree索引中的数据已经有序
将随机I/O变成顺序I/O
B+Tree索引中数据有序

三、高性能索引策略

独立的列
查询语句中列不要是需要计算的表达式形式(WHERE id+1=4)，表达式形式的列不能利用索引
前缀索引和索引选择性
索引是长字符串时，只对其列值的前部分索引，但是太短的前缀会导致索引选择性降低，即索引变得容易重复，要选择使得索引选择性最大的前缀长度
多列索引
要重点关注，多列索引中列的顺序；
当多列索引建立的不是很完美时，查询语句中出现AND、OR等需要多列参与的查询时，会执行索引合并(将单列索引合并成多列索引)，但是这种策略很浪费资源
索引列的顺序
选择性高的列放前面；查询频率高(即基数高)的列放前面；范围查询的列应该放在索引的后面；特殊查询不能简单参照经验法则
聚簇索引
非叶子节点存索引+叶子节点存实际数据，一个表只能建立一个聚簇，索引覆盖类似于聚簇；InnoDB选择主键聚簇，无主键选择唯一的非空，无唯一非空隐式自定义一个代理自增主键
- 缺点
  - 非主键顺序的插入会降低效率还可能导致页分裂
    多个线程按照自增的主键顺序插入，可能会出现并发插入间隙锁的竞争这类问题
  - 二级索引需要回表，进行两次索引查询，第一次找到主键，第二次找到数据，自适应hash索引可以减少这种重复工作
覆盖索引
索引的叶子节点包含所需要查询的数据，无需回表
- 通过B+Tree建立覆盖索引，因为覆盖索引的叶子节点要存主键值和索引列的值
- 所需查询的列包含索引中的列或主键列，可以通过查询二级索引实现覆盖索引，无需回表一次查询即可得到结果
下拉索引(index condition pushdown索引条件推送)
将查询发送到数据上，在存储引擎层实现查询条件过滤(非下拉索引是将数据从存储引擎层拉到服务器层，再根据查询条件过滤)
索引和锁
索引通过减少访问行数来减少InnoDB加的行锁，但是必须在下拉索引情况下，即在存储引擎层就通过查询条件过滤了不必要的行，在服务器端就已经释放不必要行锁；若只能把数据从存储引擎层拉到服务层再通过查询条件过滤，无法实现非必要行锁的释放，这种情况只有事务提交后才能释放锁
利用索引实现排序(ORDER BY+LIMIT)
索引最好既满足排序也能覆盖查找的所有列，此时无需回表和文件排序操作，直接通过索引就可获得排序的结果
- 排序操作满足索引列顺序的正确及最左匹配原则还有匹配范围值的原则(前列范围，后列不能索引)才可以通过索引实现排序
压缩索引
MyISAM使用前缀压缩减少索引的大小，使得内存可以放下更多索引(对行指针也如此)，但是会降低扫描的效率，每个值的压缩前缀依赖前一个值，不能使用二分查找只能从头扫描
重复、冗余和未使用的索引
重复指的是一模一样的索引和未使用索引一样最好删除；冗余索引指的是建立了可以通过其他多列索引的最左匹配原则实现的非必要索引；但是如果多列索引太长，或者扩展的多列索引使得原单列和主键索引失效，建立单列的冗余索引还是有必要的