MySQL索引原理

x33g5p2x  于2022-04-27 转载在 Mysql  
字(5.6k)|赞(0)|评价(0)|浏览(278)

索引类型

MySQL索引类型可以按不同纬度分为如下几种:

  • 从索引存储结构划分:B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引
  • 从应用层次划分:普通索引、唯一索引、主键索引、复合索引
  • 从索引键值类型划分:主键索引、辅助索引(二级索引)
  • 从数据存储和索引键值逻辑关系划分:聚集索引和非聚集索引
  1. 普通索引

指基于普通字段建立的索引。建立索引的方法如下:

CREATE INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名);
CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名) );
  1. 唯一索引

与“普通索引”类似,不同的是:索引字段的值必须唯一,但允许有空值。在创建或修改表时追加唯一约束,就会自动创建对应的唯一索引。

创建唯一索引的方法如下:

CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);
CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (字段名) ;
  1. 主键索引

它是一种特殊的唯一索引,不允许有空值。在创建或修改表时追加主键约束即可,每个表只能有一个主键。

创建主键索引的方法如下:

CREATE TABLE tablename ( [...], PRIMARY KEY (字段名) );
ALTER TABLE tablename ADD PRIMARY KEY (字段名);
  1. 复合索引

单一索引是指索引列为一列的情况,即新建索引的语句只实施在一列上;用户可以在多个列上建立索引,这种索引叫做组复合索引(组合索引)。复合索引可以代替多个单一索引,相比多个单一索引复合索引所需的开销更小。

创建组合索引的方法如下:

CREATE INDEX <索引的名字> ON tablename (字段名1,字段名2...);
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1,字段名2...);
CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名1,字段名2...) );

复合索引使用注意事项:

  • 何时使用复合索引,要根据where条件建索引,注意不要过多使用索引,过多使用会对更新操作效率有很大影响。
  • 如果表已经建立了(col1,col2),就没有必要再单独建立(col1);如果现在有(col1)索引,如果查询需要col1和col2条件,可以建立(col1,col2)复合索引,对于查询有一定提高。
  1. 全文索引

查询操作在数据量比较少时,可以使用like模糊查询,但是对于大量的文本数据检索,效率很低。如果使用全文索引,查询速度会比like快很多倍。在MySQL 5.6 以前的版本,只有MyISAM存储引擎支持全文索引,从MySQL5.6开始MyISAM和InnoDB存储引擎均支持。

创建全文索引的方法如下:

CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);
CREATE TABLE tablename ( [...], FULLTEXT KEY [索引的名字] (字段名) ;

和常用的like模糊查询不同,全文索引有自己的语法格式,使用 match 和 against 关键字,比如

select * from user
where match(name) against('aaa');

索引原理

MySQL官方对索引定义:存储引擎用于快速查找记录的一种数据结构,需要额外开辟空间和数据维护工作。

  • 索引是物理数据页存储,在数据文件中,利用数据页(page)存储
  • 索引可以加快索引速度,但是同时也会降低增删改操作速度,索引维护需要代价。

索引涉及的理论知识:二分查找法、Hash、B+Tree

  1. 二分查找法

二分查找法也叫做折半查找法,它是在有序数组中查找指定数据的搜索算法。他的优点是等值查询、范围查询性能好,缺点是更新数据、新增数据、删除数据维护成本高。

查找步骤:

  • 首先定位left和right两个指针
  • 计算(left+right)/2,除不尽向下取余。并记为m
  • 判断除2后索引位置值与目标值的大小比对
  • 索引位置值大于目标值,right移动(right=m-1);如果小于目标值,left移动(left=m+1)

举例:下面的有序数组有17个值,查找的目标值是7

......
依次类推,直到索引位置值=查找的目标值
2. Hash

Hash底层实现是由Hash表来实现的,是根据键值<key,value>存储数据的结构

  1. B+Tree结构

我们先来看看B树和B+树结构:

  • B Tree结构

  • 索引值和data数据分布在整棵树结构中

  • 每个节点可以存放多个索引值及对应的data数据

  • 树节点中的多个索引值从左到右升序排列
    B树的搜索:从根节点开始,对节点内的索引值序列采用二分法查找,如果命中就结束查找。没有命中会进入子节点重复查找过程,直到所对应的节点点指针为空,或已经是叶子节点了才结束。

  • B+Tree结构

  • 非叶子节点不存储data数据,只存储索引值,这样便于存储更多的索引值
  • 叶子节点包含了所有的索引值和data数据
  • 叶子节点用指针连接,提高范围查询性能
    B+树范围查询时,只需要查找定位两个节点的索引值,然后利用叶子节点的指针遍历即可。而B树需要遍历范围内所有的节点和数据。

聚簇索引、辅助索引、非聚簇索引

Mysql的索引普遍使用B+树做索引。

聚簇索引和非聚簇索引:B+Tree的叶子节点存放主键索引值和行记录就属于聚簇索引;索引值和行记录分开存放就属于非聚簇索引

主键索引和辅助索引:B+Tree的叶子节点存放的是主键字段就属于主键索引;存放的是非主键就属于辅助索引(也叫二级索引)

InnoDB里的索引:

聚簇索引:

InnoDB的聚簇索引是按照主键顺序构建B+Tree结构。B+Tree的叶子节点就是行记录,行记录和主键值存在一块的。也就意味着InnoDB的主键索引就是数据表本身,它按照主键顺序存放了整张表的数据。通常来说主键索引就是聚簇索引。

辅助索引:

InnoDB辅助索引,是根据普通索引列构建B+Tree结构。在B+Tree叶子节点中只存了索引列和主键的信息。二级索引占用的空间会比聚簇索引小很多,但查询需要进行两遍检索,先从辅助索引处获得主键,然后再用主键去主键索引里获得行记录。

MyISAM里的索引:

非聚簇索引:

MyISAM数据表的索引文件和数据文件是分开的,它的索引文件保存的不是完整的数据记录而是数据记录的地址。在MyISAM中,主键索引和辅助索引在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复

索引分析和优化

1. Expain的使用

  1. id列
    id代表有几条查询,有几条就有几个id,id越大越先执行,id相等则从上到下执行
  2. select_type 查询类型
- simple:简单查询
- primary:复杂查询最外层的select
- subquery:包含在select中的子查询
- derived:包含着from子句中的子查询。Mysql会将结果存在一个临时表(也叫派生表)中。
- union:在union中的第二个和随后的sselect
- union select:从union的临时表检索结果的select
  1. type

表示关联类型或访问类型,即决定如何查找表中的行

从优到差分别是:ststem > const > eq_ref > ref > range > index > ALL

一般来说,保证达到range,最好达到ref

system,const:一般就是只有一条记录,可以用常量代替的

eq_ref:使用的索引是主键索引或唯一索引

ref:使用的是普通索引

range:范围扫描通常出现在in(),bewteen,>,<,=

index:扫描全表索引,通常比ALL快一点

ALL:扫描全表
4. key

会用到的索引

  1. possible_key 可能会用到的索引
  2. key_len

索引长度,不同的类型长度计算方式不同

  1. rows

可能会扫描到的行数
8. Extra

额外信息。有以下情况

  • Using index时,意思为覆盖索引,即能在索引中拿到数据,而不用在通过索引去查数据。
  • Using Where:查询的列未被索引覆盖
  • Using where Useing index:查询的列被索引覆盖,并且where筛选的条件是索引列之一,但不是前导列
  • Using temporary:查询的时候使用了临时表
  • Using filesort:文件排序

最佳实践

  1. 全值匹配

如果用了联合索引,那么最好所有的值都使用上。比如某个表有一个3个字段组成的联合索引,那么查询时where里面这三个都带上
2. 索引最左前缀原理

建立的联合索引的顺序是什么样,使用时就要什么样。(虽然你不按照顺序写,但优化器可能会优化)
3. 不要在索引上做任何操作计算,函数,类型转换等
4. Mysql使用!=或<>, isnull,is not null,会导致全表扫描
5. like查询时,name like '%admi' 不走索引, name like 'adm%' 会走索引
6. 字符串不加单引号索引失效
7. 少用or或in,用他连接时索引可能会失效,跟数据量有关
8. in和exists的区别

//当A表数据大于B表时,in优于exists
select * from A where id in(select id from B)
  1. 索引与排序

MySQL查询支持filesort和index两种方式的排序,filesort是先把结果查出,然后在缓存或磁盘进行排序操作,效率较低。使用index是指利用索引自动实现排序,不需另做排序操作,效率比较高。

以下两种情况用index的方式排序:

  • ORDER BY 子句索引列组合满足索引最左前列
explain select id from user order by id; //对应(id)、(id,name)索引有效
  • WHERE子句+ORDER BY子句索引列组合满足索引最左前列
explain select id from user where age=18 order by name; //对应
(age,name)索引

以下几种情况,会使用filesort方式的排序:

  • 对索引列同时使用了ASC和DESC
explain select id from user order by age asc,name desc; //对应
(age,name)索引
  • WHERE子句和ORDER BY子句满足最左前缀,但where子句使用了范围查询(例如>、<、in
    等)
explain select id from user where age>10 order by name; //对应
(age,name)索引
  • ORDER BY或者WHERE+ORDER BY索引列没有满足索引最左前列
explain select id from user order by name;  //对应(age,name)索引
  • 使用了不同的索引,MySQL每次只采用一个索引,ORDER BY涉及了两个索引
explain select id from user order by name,age; //对应(name)、(age)两个索
引
  • WHERE子句与ORDER BY子句,使用了不同的索引
explain select id from user where name='tom' order by age; //对应
(name)、(age)索引

2. 慢查询日志

  1. 开启慢查询日志

查询慢日志是否开启和文件位置的命令:

SHOW VARIABLES LIKE 'slow_query_log%'

开启慢查询日志命令:

SET global slow_query_log = ON;
SET global slow_query_log_file = 'OAK-slow.log';
SET global log_queries_not_using_indexes = ON;  #表示会记录没有使用索引的查询SQL
SET long_query_time = 10;  #单位秒
  1. 查看慢日志

1)用文本编辑器打开

  • time:日志记录的时间
  • User@Host:执行的用户及主机
  • Query_time:执行的时间
  • Lock_time:锁表时间
  • Rows_sent:发送给请求方的记录数,结果数量
  • Rows_examined:语句扫描的记录条数
  • SET timestamp:语句执行的时间点
  • select....:执行的具体的SQL语句

2)使用mysqldumpslow查看

MySQL 提供了一个慢查询日志分析工具mysqldumpslow,可以通过该工具分析慢查询日志内容。(需要安装perl环境)

在MySQL的bin目录下执行命令:(注意慢日志文件目录不要带空格)

perl mysqldumpslow.pl -t 5 -s AT D:\DESKTOP-C1N48D3-slow.log

除了使用mysqldumpslow工具,也可以使用第三方分析工具,比如pt-query-digest、mysqlsla等。

3. 慢查询优化

  • 增加索引
  • 索引覆盖
  • 提高索引过滤性

如下sql:

select * from student where age=18 and name like '张%';(全表扫
描)

优化1:我们可以增加索引

alter table student add index(age,name);

优化2:我们可以将name的第一个字取出来做一个虚拟列(first_name),然后创建一个联合索引(first_name,age)

alter table student add first_name varchar(2) generated always as
 (left(name, 1)), add index(first_name, age);

书山有路勤为径,学海无涯苦作舟

相关文章

微信公众号

最新文章

更多