企业网站怎样做,什么做网站赚钱,做网站需要技术,怎么看网站建设时间什么是索引#xff1f;
索引是帮助存储引擎快速获取数据的一种数据结构#xff0c;类似于数据的目录。 索引的分类
按数据结构分类#xff1a;
MySQL 常见索引有 BTree 索引、HASH 索引、Full-Text 索引。 Innodb是MySQL5.5之后的默认存储引擎#xff0c;BTree索引类型也…什么是索引
索引是帮助存储引擎快速获取数据的一种数据结构类似于数据的目录。 索引的分类
按数据结构分类
MySQL 常见索引有 BTree 索引、HASH 索引、Full-Text 索引。 Innodb是MySQL5.5之后的默认存储引擎BTree索引类型也是MySQL采用的最多索引类型。
在创建表时InnoDB存储引擎会根据不同的场景选择不同的列作为索引
如果有主键默认会使用主键作为聚簇索引的索引键如果没有主键就选择一个唯一列作为聚簇索引的索引键;如果两个都没有将自动生成一个隐式自增 id 列作为聚簇索引的索引键;
其他索引都属于二级索引或非聚簇索引。创建的主键索引和二级索引默认使用的都是Btree索引。
按物理存储分类
索引分为聚簇索引、非聚簇索引。
聚簇索引的Btree的叶子节点存放的是实际数据所有完整的数据记录都存放在聚簇索引的BTree的叶子节点里
非聚簇索引的BTree的叶子节点存放的是主键值不是实际数据记录
因此在查询时使用了非聚簇索引如果查询的数据字段能在非聚簇索引里查询到那么就不需要回表这个过程称作覆盖索引。如果查询的数据字段不在非聚簇索引中就会先检索非聚簇索引找到对应的叶子节点获取到主键值后然后在检索聚簇索引就能查到数据了这个过程就称作回表。
按字段特性分类
索引分为主键索引、唯一索引、普通索引、前缀索引。
这里说明下前缀索引
前缀索引指的是对字符类型(char、varchar)字段的前几个字符建立的索引而不是在整个字段上建立索引。使用此类索引可以检索索引占用的存储空间提升查询效率。
create index idx_name_prefix on tbl_user(name(3));
按字段个数分类
分为单列索引、联合索引。
这里说明下联合索引它就是将多个字段组合成一个索引。
索引的结构
比如在tb_user中添加idx_name_age(name,age)联合索引
CREATE INDEX idx_name_age ON tbl_user(name, age);下图就是联合索引idx_name_age中BTree形式的大致结构 从上面的图可以看出联合索引的非叶子节点用两个字段的值作为BTree的key值。当在联合索引查询数据时先按name字段比较在name字段相同的情况下在按age字段比较。也就是说先按name字段进行排序然后再name字段相同的情况再按age字段排序。
因此使用联合索引时就会存在最左匹配原则。如果查询条件不遵守「最左匹配原则」联合索引会失效查询就无法利用到索引进行快速查询。 为什么选择BTree B 树的非叶子节点不存放实际的记录数据仅存放索引因此数据量相同的情况下相比存储即存索引又存记录的 B 树B树的非叶子节点可以存放更多的索引因此 B 树可以比 B 树更「矮胖」查询底层节点的磁盘 I/O次数会更少。 B 树有大量的冗余节点所有非叶子节点都是冗余索引这些冗余索引让 B 树在插入、删除的效率都更高比如删除根节点的时候不会像 B 树那样会发生复杂的树的变化 B 树叶子节点之间用链表连接了起来有利于范围查询而 B 树要实现范围查询因此只能通过树的遍历来完成范围查询这会涉及多个节点的磁盘 I/O 操作范围查询效率不如 B 树。
索引的优化
下面举例说明几种常见的优化索引手段
前缀索引优化覆盖索引优化主键索引最好是自增的防止索引失效
前缀索引优化
使用某个字段中字符串的前几个字符串建立索引为什么需要使用前缀来建立索引呢
目前是为了减小索引字段大小可以增加一个索引页中存储的索引值有效提高索引的查询速度。因为会存在大字符串的字段作为索引这个场景就适合使用前缀索引方式来减小索引项的大小
缺点①order by无法使用前缀索引 ②无法把前缀索引用作覆盖索引
覆盖索引优化
指的是SQL中查询的所有字段在索引BTree的叶子节点都能找得到从非聚簇索引中查询得到记录而不需要通过聚簇索引查询获得避免了回表的操作。
主键索引是自增
建表的时候我们一般把主键设置成自增为什么这么做呢
Innodb引擎中以聚簇索引为例数据存放在叶子节点中也就是说同一个叶子节点内的各个数据都是按主键顺序存放的因此当有一条新数据要插入时数据库会根据主键将其插入到对应的叶子节点中。
如果使用自增主键那么每次插入的新数据就会按顺序添加到当前索引节点的位置不需要移动已有数据当页写满就会自动开辟一个新页。因为每次插入一条新纪录都是追加操作不需要重新移动数据因此这种插入数据的方法效率非常高。
如果使用非自增主键那么每次插入主键的索引值都是随机的每次插入新的数据时就可能会插入到现有数据页中间的某个位置这将不得不移动其他数据来满足新数据的插入甚至需要从一个页复制数据到另外一个页这种情况我们称为 页分裂。页分裂可能会导致造成大量的内存碎片导致索引节后不紧凑影响查询效率。
索引最好设置为 NOT NULL
第一索引列存在NULL就会导致优化器在做索引选择的时候更加复杂难以优化。比如进行索引统计count会省略之为NULL的行第二 NULL是一个没有意义的值但是它会占用物理空间所以会带来存储空间的问题。如果表中存在允许为NULL的字段那么行格式中至少会用1字节空间存储NULL值列表。
防止索引失效
对索引使用左或者左右模糊匹配
当我们使用左或者左右模糊匹配的时候都会造成索引失效
select * from tbl_score where name like %王;
select * from tbl_score where name like %王%;
执行计划中的 typeALL 就代表了全表扫描而没有走索引。 如果查询的是右模糊的话会走索引。
select * from tbl_score like 王%;
执行计划中的typerange表示走了索引扫描。 为什么 like 关键字左或者左右模糊匹配无法走索引呢 因为索引结构是BTree它是按照「索引值」有序排序存储的只能根据前缀进行比较。
对索引使用函数
如果查询条件中对索引字段使用函数就会导致索引失效。
select * from tbl_score where mod(score, 2) 0;
执行计划中typeALL代表未走索引。 为什么对索引使用函数就无法走索引了呢 因为索引保存的是索引字段的原始值而不是经过函数计算后的值因此肯定没法走索引。但是在8.0版本后增加了函数索引。即可这对函数计算后的值建立索引也就是说索引的值是函数计算后的值。
alter table tbl_score add key idx_score_mod ((mod(score,2)));添加完后执行计划如下 对索引进行表达式计算
在查询条件中对索引进行表达式计算也是无法走索引的。
select * from tbl_score where age 210;
执行计划如下typeALL未走索引 修改查询方式
select * from tbl_score where age 10 - 2;
执行计划如下typeref走了索引 对索引隐式类型转换
如果索引字段是字符串类型但是在条件查询中输入的参数是整型的话你会在执行计划的结果发现这条语句会走全表扫描
在tbl_score中存在一个字段status 类型是varchar(4)
select * from tbl_score where status1; 执行计划中typeALL未走索引。 修改方式
select * from tbl_score where status1; 执行计划中typeref表示已走索引。 联合索引非最左匹配
联合索引要能正确使用需要遵循最左匹配原则也就是按照最左优先的方式进行索引的匹配
select * from tbl_score where score 10;
执行计划中typeALL未走索引。 为什么联合索引不遵循最左匹配原则就会失效 在联合索引的情况下数据是按照索引第一列排序第一列数据相同时才会按照第二列排序。
也就是说如果我们想使用联合索引中尽可能多的列查询条件中的各个列必须是联合索引中从最左边开始连续的列。如果我们仅仅按照第二列搜索肯定无法走索引
WHERE 子句中的 OR
在 WHERE 子句中如果在 OR 前的条件列是索引列而在 OR 后的条件列不是索引列那么索引会失效
但是or查询条件中都有字段都是索引字段并不一定走索引。还需要看优化器怎么决定。
