sql 语句索引 sqlyog索引

转载

laokugonggao 2024-04-23 17:22:32

文章标签 sql 语句索引 sql 数据库 lucene 主键 文章分类 数据仓库大数据

SQL复习之索引

1.概述

索引是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构一某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

索引优点：提高数据检索效率，降低数据库的io成本。通过索引对数据进行排序，减小cpu的消耗
索引缺点：索引也是要占用空间的，在增加查询的效率时会使增删改的效率降低

###2.索引结构

MySQL的索引结构是基于存储引擎实现的，不同的储存引擎有不同的索引结构，mysql默认的存储引擎为InnoDB，索引结构类型为以下几种

索引结构	描述
B+Tree索引	最常见的索引类型，大部分引擎都支持 B+ 树索引
Hash索引	底层数据结构是用哈希表实现的, 只有精确匹配索引列的查询才有效, 不支持范围查询
R-tree(空间索引）	空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text(全文索引）	是一种通过建立倒排索引,快速匹配文档的方式。类似于 Lucene,Solr,ES

不同的存储引擎对应着不同的索引结构的支持情况

索引	InnoDB	MyISAM	Memory
B+tree索引	支持	支持	支持
Hash 索引	不支持	不支持	支持
R-tree 索引	不支持	支持	不支持
Full-text	5.6版本之后支持	支持	不支持

虽然索引种类有很多，当绝大多数情况我们使用到到索引结构都为B+tree

3. B+tree

在说B+tree之前先了解下其它树状的数据结构，看看它们适不适合做索引

二叉树
假设索引数据结构为二叉树，那么理想的数据结构如下图

如果是主键顺序插入的话，则会形成一个单向链表。结构如下图

sql 语句索引 sqlyog索引_sql 语句索引

从上面两张图可以看到，如果选择二叉树做索引结构，会存在以下缺点

顺序插入时，会形成一个链表，查询性能大大降低
大数据量情况下，层级较深，检索速度慢。

红黑树
红黑树是一种自平衡的二叉树，这样一来无论插入多少数据都会形成一颗平衡的二叉树

但是红黑树始终都是一颗二叉树，因此它也有二叉树的缺点：

大数据量情况下，层级较深，检索速度慢。

B+tree
因为以上缺点mysql的索引结构中并没有使用二叉树或红黑树，而是使用了B+树，B+树是一种多路平衡树是B树的一个变种，其特点是相对于二叉树，B+树每个节点可以有多个分支，即多叉。可以看看其结构图

绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据。
红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据。

其特点为：

所有的数据都会出现在叶子节点。
叶子节点形成一个单向链表
非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点

的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序

sql 语句索引 sqlyog索引_sql 语句索引_02

4.索引类型

按类型分

分类	含义	特点	关键字
主键索引	正对主键创建的索引	默认主键自动创建，只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个	INDEX
全文索引	全文索引查找的是文本中的关键词，而不是比	可以有多个	FULLTEXT

按存储结构分

分类	含义	特点
聚集索引	将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据	必须用，只能有一个
二级索引	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

聚集索引选取规则：

一般为主键，主键索引就是聚集索引
如果没有主键，将使用第一个唯一索引作为聚集索引
如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索
引。

查询流程

聚集索引：根据主键查询，查到最后的叶子节点上有整行的数据，直接返回。
二级索引：根据索引字段查询，查到最后的叶子节点上为主键值，再根据主键值进行一边查询。

回表查询：这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取
数据的方式，就称之为回表查询。

5.索引语法

创建索引

create [索引类型] index [索引名称] on [表名] ([要加索引的字段名])

为一张名为student的表的name字段加常规索引

create index idx_student_name on student (name);

为表中的phone字段加唯一索引

create UNIQUE index idx_student_phone on student (phone);

为表中的三个字段添加联合索引

CREATE index idx_student_pas on student(profession,age,status)；

查看表中索引

SHOW INDEX FROM 表名 ;

删除索引

DROP INDEX 索引名 ON 表名 ;

6.SQL性能分析

我们可以通过sql的性能分析来查看sql语句的执行情况，并分析该sql语句是否需要进行优化下面给出性能分析的几种方法

sql执行频率
MySQL连接客户端后，可以通过 show [session|global] status 来查看服务器状态信息，可以看到当前数据库的INSERT，SELECT，UPDATA，DELETE的访问次数

-- session:查看当前会话
-- global：查看全局数据
show session status like 'com_______' -- 七个_

Com_delete:删除次数

Com_insert:插入次数

Com_select:查询次数

Com_update:更新次数

通过上述指令，我们可以查看到当前数据库到底是以查询为主，还是以增删改为主，从而为数据库优化提供参考依据。如果是以增删改为主，我们可以考虑不对其进行索引的优化。如果是以查询为主，那么就要考虑对数据库的索引进行优化了。

2.慢日志查询

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有

SQL语句的日志。

通过查看‘slow_query_log’来查看是否开启了慢日志查询

show VARIABLES like 'slow_query_log'

sql 语句索引 sqlyog索引_数据库_03

上面可以看到已经开启，如果没有开启的话可以在mysql的配置文件中配置如下信息

# 开启MySQL慢日志查询开关
slow_query_log=1 
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

开启后可以通过show variables like 'slow_query%';来查看日志信息在哪个位置

执行一条超时sql

select sleep(3);

可以看到日志中出现以下提示

# Time: 2022-06-22T07:55:14.551579Z
# User@Host: root[root] @ localhost [127.0.0.1]  Id:     2
# Query_time: 3.053325  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1655884514;
select sleep(3);

explain查看sql执行计划
通过explain或者desc可以查看mysql是如何执行select语句的信息，语法如下：

explain select * from tb_user whwere name = "张三";

sql 语句索引 sqlyog索引_主键_04

可以得到这么多信息，这些字段代表如下

id:select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下；id不同，值越大，越先执行)。
select_type:表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、
SUBQUERY（SELECT/WHERE之后包含了子查询）等.
type:表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、 index、all
partitions:匹配的分区
possible_keys:可能用到的索引
key:实际用到的索引
ref：列与索引的比较
rows：扫描出的行数（估算的行数）
filtered：按表条件过滤行的百分比
Extra:是否有回表