数据库索引

最新推荐文章于 2022-05-09 10:13:26 发布

初心魏

最新推荐文章于 2022-05-09 10:13:26 发布

阅读量145

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/qq_42306803/article/details/102958310

版权

数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

文章目录

一、数据库索引
二、联合索引
三、索引的一些特点
四、索引优化和索引失效

一、数据库索引

1.1 Cardinality关键字

数据库中有一个关键字Cardinality，优化器会根据这个关键字的值来判断是否使用索引，一般使用索引的字段是这个字段的值是高选择性的，即这个字段有大量的值，该字段的值越接近1，则表示越有必要使用该索引。
这个字段不是及时更新的，需要更新的话：

ANALYZE TABLE SnameIndex

1.2 Inndb存储引擎Cardinality的策略

统计叶子结点的总数，记为A
随机抽取8个叶子节点，统计每个页的不同记录数，记为P1~P8
根据采样信息得到Cardinality的预估值
（P1 P2 … P8）*A/8

在使用InnoDB存储引擎时，insert和update操作会影响Cardinality关键字的值，但是并不是每次都会更新。只有当表中的数据超过1/6被修改时会更新Cardinality。

1.3 Fast Index Creation（快速索引创建）

MySql5.5之前，在创建索引时，需要创建一张新的临时表，然后将旧表的数据复制到新表上
Mysql5.5之后，InnoDB使用一种新的方法创建索引，即给创建索引的表加上一个共享锁，在创建索引的时候，就不需要重新和创建临时表了，在删除辅助索引的时候，只需更新内部视图，并且将辅助索引空间标记为可用即可，这种效率就大大的提高。

1.4 在线数据创建

在mysql5.6之后，允许辅助索引创建的同时，可以进行其他insert，delete，update等操作，这样就大大提高了数据库的可用性。

1.5 使用新的语法创建索引

ALTER TABLE table_name ADD [UNIQUE|FULLLTEXT] INDEX index_name (column(length))
[ALGORITHM = {DEFAULT|INPLACE|COPY}]
[LOCK = {DEFAULT|NONE|SHARED|EXLUSIVE}]

ALGORITHM

COPY：创建临时表的方式
INPLACE：不需要创建临时表
DEFAULT：根据参数old_alter_table参数判断，如果是OFF,采用INPLACE的方式

LOCK表示对表添加锁的情况

NONE：不加任何锁
SHARE：加一个S锁，并发读可以进行，写操作需要等待
EXCLUSIVE：加一个X锁，读写都不能并发进行
DEFAULT：先判断是否可以使用NONE，如不能，判断是否可以使用SHARE，如不能，再判断是否可以使用EXCLUSIVE模式。

二、联合索引

2.1 联合索引

将数据表的多个字段组成来联合索引，在使用多条件查询时需要用到联合索引，主要是为了提高多条件查询的效率。

2.2 使用规则-最左匹配原则

在查询语句中，如果查询条件包含联合索引中最左边的列，则会使用索引。

查看使用查询语句时是否会使用到索引：

EXPLAIN SELECT sname FROM indextable WHERE sname = "tom"

在这里插入图片描述

是否使用索引：看possible_keys关键字
看使用了几个索引：看key_len关键字

2.3 自动排序

使用联合索引的下一个字段是会自动排序的，比如（a,b,c,d），现在使用到了(a,b)索引，则c字段是会自动排序的。

2.4 索引提示

我们可以显示的告诉优化器使用哪种类型的索引，尤其是在有很多索引供选择的情况下。

三、索引的一些特点

3.1 索引的缺点

创建和维护索引需要耗费一定的时间，且随着数据量的增加，时间上的消耗也会增加。
索引占一定的物理空间

3.2 联合索引的特点

建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！
覆盖索引
如果查询条件覆盖了联合索引的一部分，则直接通过查询索引就可以找到所需的数据，这样就不需要回表，减少很多的随机IO，覆盖索引是主要的提升性能的优化手段之一。
索引列越多，通过索引筛选出的数据越少，这样只需在很少的数据中回表再筛选出最终需要的数据。

3.3 覆盖索引

mysql-覆盖索引

四、索引优化和索引失效

4.1 索引优化

Multi-Range Read 优化
减少磁盘的随机访问，变为较为顺序的数据访问，
Index Condition Pushdown（ICP）优化
这种优化方式也是从MySQL5.6开始支持的，不支持这种方式之前，当进行索引查询时，首先我们先根据索引查找记录，然后再根据where条件来过滤记录。然而，当支持ICP优化后，MySQL数据库会在取出索引的同时，判断是否可以进行where条件过滤，也就是将where过滤部分放在了存储引擎层，大大减少了上层SQL对记录的索取。