Mysql高级(索引+调优)

最新推荐文章于 2024-07-13 10:38:18 发布

小敬是个码农

最新推荐文章于 2024-07-13 10:38:18 发布

阅读量466

点赞数

分类专栏：笔记文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/qq_45912039/article/details/123012523

版权

笔记专栏收录该内容

32 篇文章 0 订阅

订阅专栏

存储引擎

MySQL体系结构

在这里插入图片描述

存储引擎简介

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式，存储引擎是基于表的，而不是基于库的，所以引擎也可被称为表类型

# 查询所有的存储引擎
show engines

存储引擎特点

1.InnoDB
	InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL5.5之后，InnoDB是MySQL的默认存储引擎
	特点：
		DML操作遵循ACID模型，支持事务
		行级锁，提高并发访问性能
		支持外键约束，保证数据的完整性和正确性
	文件：
		xxx.ibd:xxx代表表名，InnoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构(frm,sdi)、数据和索引
		参数：innodb_file_per_table
2.MyISAM
	mysql早期的默认存储引擎
	特点：
		不支持事务、不支持外键
		支持表锁、不支持行锁
		访问速度快
	文件：
		xxx.sdi:存储表结构信息
		xxx.MYD:存储数据
		xxx.MYI:存储索引

存储引擎选择

根据应用系统的特点选择

索引

概念

索引(index)是帮助MySQL高效获取数据的数据结构(有序).在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引
在这里插入图片描述

优点:
	1.提高数据检索效率，减少数据库IO成本
	2.通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗
缺点：
	1.索引列也是需要占用空间的
	2.索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行增删改，效率降低

索引结构

不同的存储引擎，有不同的数据结构

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

在这里插入图片描述

B+Tree

二叉树： 左分支都是小的，右分支都是大的
		缺点： 如果都是小或者都是大，还是相当于查整表
红黑树(平衡二叉树):自动平衡

在这里插入图片描述

B-Tree(多路平衡查找树)
以一颗最大度数(max-degree)为5(5阶)的b-tree为例(每个节点最多存储4个key,5个指针)
几个指针就是几阶
每个节点都会存储数据

# 每次达到n个key会自动将中间元素向上分裂,如果是双数就是中间前一个，如果上边的节点不超过四个，就合并，超过就再创建一个节点
https://www.cs.usfca.edu/~galles/visualization/BTree.html

在这里插入图片描述

B+Tree
以一颗最大度数(max-degree)为4(4阶)的b+tree为例

只在叶子节点存储数据，所有元素都会出现在叶子节点，叶子节点形成单向链表

# 每次达到n个key会自动将中间元素向上分裂,但是只是分裂一个索引,叶子节点还会存在
# 如果是双数就是中间后一个，如果上边的节点不超过四个，就合并，超过就再创建一个节点

在这里插入图片描述

mysql优化了B+Tree,叶子节点双向链表加首尾相连
在这里插入图片描述

Hash

哈希索引就是采用一定的算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中
出现hash冲突使用链表解决

在这里插入图片描述

1.hash索引只能用于对等比较(=,in),不支持范围查询(between,>,<)
2.无法利用索引完成排序操作
3.查询效率高，通常只需要一次检索就可以了，效率通常要高于B+Tree

InnoDB引擎具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的

索引分类

1.主键索引	# 针对表中主键创建的索引	默认自动创建，只能有一个  关键字 primary
2.唯一索引	# 避免同一个表中某数据列中的值重复	可以有多个	关键字 unique
3.常规索引	# 快速定位特定数据	可以有多个	
4.全文索引	# 全文索引查找的是文本中的关键词，而不是比较索引的值	关键字 FULLTEXT

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下几种
# 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据 必须有，而且只有一个
1.聚集索引(Clustered index)	

# 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键  可以存在多个
2.二级索引(Secondary index)


# 聚集索引选取规则
	1.如果存在主键，主键索引就是聚集索引
	2.如果不存在主键，第一个唯一(unique)索引就是聚集索引
	3.如果都没有，就会自动生成一个rowid作为隐藏的聚集索引

在这里插入图片描述

索引语法

# 创建索引	(字段名 index_col_name)
	CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name,...)
# 查看索引
	SHOW INDEX FROM table_name;
# 删除索引
	DROP INDEX index_name ON table_name

# 查询索引
show index from tb_user;

# 给name添加常规索引，不需要加关键字
create index idx_user_name on tb_user(name);

# 给phone添加唯一索引
create unique index idx_user_phone on tb_user(phone);

# 给profession,age,status创建联合索引
create index idx_user_pro_age_sta on tb_user(profession,age,status);

# 给email添加索引
create index idx_user_email on tb_user(email);

# 删除索引
drop index idx_user_email on tb_user;

性能分析

查看SQL执行频次(语句类型执行权重)

# 查看服务器状态信息
	show [session|global] status

# 查看sql增删改查语句访问频次	(一个下划线代表一个字符  7个)
	show global status like 'Com______';

慢查询日志(具体的语句)

1.慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位：秒，默认10秒)的所有SQL语句的日志
2.MySql的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息:
	# 开启MYSQL慢查询开关
		slow_query_log=true;
	# 设置慢查询的时间为2秒，SQL语句超过2秒的就会被视为慢查询，记录慢查询日志
		long_query_time=2
	# 配置完毕后重启mysql 查看慢日志文件 /var/lib/mysql/localhost-slow.log
	# windows C:\ProgramData\MySQL\MySQL Server 8.0\Data

# 查看慢查询是否开启
show variables like 'slow_query_log';

# windows my.ini
# my.ini文件在datedir的上一级	C:\ProgramData\MySQL\MySQL Server 8.0
show variables like '%data%'

show profiles

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了
通过have_profiling参数，能够看到当前MySQL是否支持profile操作
select @@have_profiling

# 当前profiling是否开启
select @@profiling

set profile = 1	# 可以开启

# 查看每条语句耗时时间
show profiles

# 查看指定query_id的SQL语句各个阶段的耗时情况
	show profile for query query_id;

# 查看指定query_id的SQL语句CPU的使用情况
	show profile cpu for query query_id;

explain（常用）

explain或者desc命令获取MySQL如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序

# 语法
# 直接在select语句之前加上关键字 explain/desc
explain select 字段列表 from 表名 where 条件;

explain查询出的每一列

1.Id
	select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下;id不同，值越大，越先执行)
2.select_type
	表示select的类型，常见的取值有SAMPLE(简单表，不使用表连接或子查询)，primary(主查询，即外层的查询)，union(union中的第二个或者后面的查询语句)，subquery(select/where之后包含的子查询)
3.type
	表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all
4.possible_key
	显示可能应用在这张表上的索引，一个或多个
5.key
	显示实际应用的索引，没有为null
6.Key_len
	表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
7.rows
	MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的
8.filtered
	表示返回结果的行数占需读取行数的百分比，filtered的值越大越好
9.extra
	额外信息

索引使用

验证索引效率

# 在未建立索引之前，测试sql效率  sn不是primary
	select * from tb_sku where sn = '1000000002314';	# 20s
# 在建立索引之后，测试sql效率
	create index idx_sku_sn on tb_sku(sn);	# 构建B+树，耗时比较长
	select * from tb_sku where sn = '1000000002314';	# 0.01s

最左前缀法则

如果索引是联合索引，要遵守最左前缀法则，最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列
如果跳过某一列，索引将部分失效(后面的字段索引失效)

# 例子 三个索引联合，在表中位置在最左边的列必须出现，否则不走索引，如果最左边的列出现，但是中间的列没有出现，那么没有出现的列之后的列出现也不会走索引

在这里插入图片描述

范围查询

select * from tb_user where profession = '软件工程' and age > 30 and status = '0'
# 使用范围查询，范围查询 之后 出现的联合查询的列 不会使用索引查询

---规避
	使用范围查询时，如果业务允许，尽量带上等于号

索引失效

1.索引列运算
	不要再索引列上进行运算操作，否则索引失效
	例如函数运算
	explain select * from 表名 where substring(phone,10,2) = '15'
2.字符串不加单引号
	字符串不加引号，类型转换，导致索引失效
	explain select * from 表名 where phone = 12345678912   # 没有加单引号，需要类型转换为str
3.模糊查询
	如果仅仅是尾部模糊匹配，索引不会失效，如果头部模糊，索引失效
	explain select * from 表名 where profession like '软件%'
	explain select * from 表名 where profession like '%工程'
4.or连接的条件
	用or分隔开的条件，如果or前的条件中的列有索引，而后面的条件没有索引，那么涉及的索引都不会生效
5.数据分布影响
	如果MySQL评估使用索引比全表更慢，则不使用索引

SQL提示

# 一个字段有多个索引，mysql默认自己选择   联合索引

# 可以告诉数据库用哪个索引
	use index:	# 告诉数据库使用的索引	(建议)
		explain select * from tb_user use index(index_name) where 条件
	ignore index:	# 告诉数据库不使用的索引
		explain select * from tb_user ignore index(index_name) where 条件
	force index:	# 强制数据库使用哪个索引
		explain select * from tb_user force index(index_name) where 条件

覆盖索引和回表查询

覆盖索引

尽量使用覆盖索引(查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到)，较少select *

在这里插入图片描述

前缀索引

当字段类型为字符串(varchar,text)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。
此时可以只讲字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高效率

# n 代表前缀长度
create index idx_xxxx on table_name(column(n))
# 前缀长度
	可以根据索引的选择性来决定，而选择性是指不重复的索引值(基数)和数据表的记录总数的比值，索引选择性越高则查询效率越高
	唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的
select count(distinct email) / count(*) from tb_user;
select count(distinct substring(email,1,5) / count(*) from tb_user

单列索引和联合索引

1.单列索引
	单列索引 使用 and 查询时，只会使用第一个列的索引查询，其他会回表查询
2.联合索引
	联合索引 使用 and 查询时，条件中的列是联合查询，只要不缺最左列和跳列，就会都索引查询

# 推荐使用联合索引

在这里插入图片描述

索引设计原则

1.数据量大，查询比较频繁的表建立索引   （百万级）
2.针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引
3.尽量选择区分度高的列作为索引，尽量建立唯一索引
4.如果是字符串类型的字段，字段的长度较长，可以针对字段的特点，建立前缀索引
5.尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，避免回表
6.要控制索引的数量，索引并不是多多益善，索引越多，维护代价越大，影响增删改的效率
7.如果索引列不能存储null值，请在创建表时使用not null约束，当优化器知道每列是否包含null时，可以更好的确定哪个索引最有效地用于查询

SQL优化

插入数据

insert 优化

1.批量插入
	insert into tb_test values(),(),();	# 数据量也不要太大   500-1000
2.手动提交事务(防止事务多次开启)
	start transaction;
		insert
	commit;
3.主键顺序插入

4.load 大批量插入数据
	如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令
	
	#客户端连接服务端时，加上参数 --local-infile
	mysql --local-infile -u root -p
	# 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
	set global local_infile = 1;
	# 执行load指令将准备好的数据，加载到表结构中
	load data local infile '路径' into tale 'table_name' fields terminated by ',' lines terminated by '\n';

在这里插入图片描述

主键优化

# innodb存储引擎中的数据组织方式
	在Inoodb存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(index organized table IOT)

在这里插入图片描述

主键设计原则

1.满足业务需求的情况下，尽量降低主键的长度
2.插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增原则
3.尽量不要使用UUID做主键或者是其他自然主键，如身份证号
4.业务操作时，避免对主键的修改

order by 排序优化

在这里插入图片描述

1.尽量使用覆盖索引
2.多字段排序，一个升序，一个降序，此时需要注意联合索引在创建时的规则(ASC/DESC)
3.如果不可避免的出现filesort,大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)

group by 分组优化

覆盖索引

limit 优化

覆盖索引加子查询

update优化

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引还不能失效，否则会从行锁升级为表锁

小敬是个码农

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql高级(索引+调优)

存储引擎MySQL体系结构存储引擎简介存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式，存储引擎是基于表的，而不是基于库的，所以引擎也可被称为表类型# 查询所有的存储引擎show engines存储引擎特点1.InnoDB InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL5.5之后，InnoDB是MySQL的默认存储引擎特点： DML操作遵循ACID模型，支持事务行级锁，提高并发访问性能支持外键约束，保证数据的完整性和正确性文件：
复制链接

扫一扫