Mysql索引优化（一）_索引类型

ephererid 发布于2019-08-01 14:03 / 3100人阅读

摘要：因为哈希索引不是按照索引值顺序存储的，所以哈希索引也不能用于排序。哈希索引不支持部分索引列查询，比如将表是索引改为，那么查询必须用到才会使用到索引。哈希索引只支持等值比较，所以等范围查询是不会使用到索引的。

索引对于良好的性能非常关键，尤其是在数据量越来越大的时候。恰当的索引对性能的帮助是非常巨大的，不恰当的索引不禁不能对性提升有帮助，当数据量达到一定级别的时候还可能造成性能的下降。所以了解索引对Mysql性能优化有着至关重要的作用。

Mysql索引基本类型有 B-Tree,哈希索引,全文索引,空间数据索引(R-Tree)。其中B-Tree、哈希、全文索引是我们经常用到的。

B-Tree索引

B-Tree索引是我们口中经常说的索引类型（有些存储引擎中使用的是B+Tree。如InnoDB）。每个引擎对于BTREE索引使用方式是不一样的。
如

MyISAM引擎使用的是前置压缩技术，这样索引会变的很小。而InnoDB则是按照原有的数据格式来存储的。

MyISAM索引是通过数据的物理位置来找到被索引的行，而InnoDB则是根据被索引的行的主键来找到被索引行的。

B-Tree索引的所有值都是按顺序存储的，并且每个叶子节点到根节点的距离是相同的。下面给出一个简单的示意图

假设有下表：

CREATE TABLE student(
    first_name varchar(20) not null,
    last_name varchar(20) not null,
    age tinyint(3) not null,
    created_at timestamp not null,
    key(first_name ,last_name)
);

可以使用到B-Tree索引的查询

全值匹配全值匹配指对索引中的所有列进行匹配。如查询姓名是 zhang san的人 select * from student where first_name="zhang" and last_name="san"; 这里使用了索引的第一列与第二列

匹配最左前缀，如查询姓为张的人 select * from student where first_name="zhang" ;这里使用了索引的第一列

匹配列前缀，也可以值匹配某一列的开头部分,如 select * from student where first_name="zha" ;这里使用了索引的第一列

匹配范围值,如 select * from student where first_name>"bao" and first_name<"zhang";这样也会使用到索引的第一列

只访问索引的查询，如果查询条件是 select first_name,last_name from student where first_name="zhang" ;那么查询就只会访问索引，而不会再去根据主键回表查询数据。

这里需要注意的是；B-Tree 索引需要根据最左前缀查询，如果不是按照索引的最左列开始查询，那么是不会使用到索引的。例如:
select * from student  where last_name="san";
select * from student  where first_name like "%ha%";  
这样的sql是没办法命中索引的。对于第二条sql如果需要使用索引，那么应该改为 select * from student  where first_name like "ha%";

哈希索引

哈希索引是基于哈希表实现的，只有精确匹配索引所有列的查询才会使用到索引，只有Memory引擎才支持哈希索引。
假设有下表：

CREATE TABLE student(
    first_name varchar(20) not null,
    last_name varchar(20) not null,
    age tinyint(3) not null,
    created_at timestamp not null,
    key using hash(first_name)
) engine=memory;

如果我们要执行select last_name from student where first_name="zhang";,Mysql会先计算zhang的哈希值，然后用该值寻找对应的记录指针，最后再去比较first_name是否等于zhang。
因为哈希索引只存储对于的哈希值和行指针，所以哈希索引的结构很紧凑，查询速度非常快。但是也有一些缺点。

因为哈希索引只有哈希值与指针，所以每次查询必须回表去读取数据行。

因为哈希索引不是按照索引值顺序存储的，所以哈希索引也不能用于排序。

哈希索引不支持部分索引列查询，比如将student表是索引改为 hash(first_name,last_name)，那么查询必须用到first_name,last_name才会使用到索引。

哈希索引只支持等值比较，所以<,>等范围查询是不会使用到索引的。

哈希索引也会存在哈希冲突，当出现冲突的时候，查询效率就很降低很多。

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/49259.html

Mysql索引优化（一）_索引类型(索引策略)

摘要：上一篇已经讲了索引的基本类型，这一篇主要介绍下如何选择更高效的索引类型。索引选择性指不重复的索引值和数据表总数的比值。唯一索引的选择性最高为，性能也是最好的。上一篇已经讲了索引的基本类型，这一篇主要介绍下如何选择更高效的索引类型。独立的列现在有下面一张学生成绩表 CREATE TABLE `student` ( `id` int(10) unsigned NOT NULL A...

googollee 2019-08-01 14:05 评论0 收藏0
深入浅出Mysql索引的那些事儿

摘要：如果删除组成索引的所有列，则整个索引将被删除。组合索引与前缀索引在这里要指出，组合索引和前缀索引是对建立索引技巧的一种称呼，并不是索引的类型。索引要建立在值比较唯一的字段上。一.索引的作用一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，所以查询语句的优化显然是重中之重。在数据量和访问量不...

zoomdong 2019-08-27 14:27 评论0 收藏0
MySQL深入浅出（二）：索引的设计原则、SQL优化、MySQL日志、备份与恢复

摘要：不用访问表或者索引，直接就能得到结果，如表示查询时可能使用的索引。表示为了得到结果，使用了临时表，这通常是出现在多表联合查询，结果排序的场合。此日志对于灾难时的数据恢复起着极其重要的作用。上一篇：MySQL深入浅出（一）：SQL基础、数据类型、运算符一、索引的设计原则查看字段散列度/离散度：select count(distinct col_name),... from ta...

LeexMuller 2019-07-31 18:01 评论0 收藏0
Mysql联合索引最左匹配原则

摘要：最左前缀匹配原则在建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，示例对列列和列建一个联合索引联合索引实际建立了三个索引。前言之前在网上看到过很多关于mysql联合索引最左前缀匹配的文章，自以为就了解了其原理，最近面试时和面试官交流，发现遗漏了些东西，这里自己整理一下这方面的内容。最左前缀匹配原则在mysql建立联合索引时会遵循最左...

megatron 2019-07-31 18:10 评论0 收藏0
MySql - 索引

摘要：如果该表大部分是单条查询，则应使用索引，因为索引时间复杂度索引为。子句的查询条件里有，索引将无效。索引列尽量避免，应该指定列为或设置默认值。索引不存值，所以会导致不会返回为的数据。条件中不能含有对索引进行的操作。索引长度索引长度短，区分度就低，索引长度长，区分度高，查询效率高，但是索引要占内存，所以要找到一个平衡点；举个例子：（张，张三，张三哥），如果索引长度取1的话，那么每...

liangzai_cool 2019-07-31 17:38 评论0 收藏0