6.mysql优化

qq_44949002

已于 2023-03-21 23:58:27 修改

阅读量187

点赞数

分类专栏： mysql 文章标签： mysql 索引

于 2021-05-21 00:09:37 首次发布

本文链接：https://blog.csdn.net/qq_44949002/article/details/117093906

版权

mysql 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

在这里插入图片描述
2.

什么是存储引擎？
数据库存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。
现在许多数据库管理系统都支持多种不同的存储引擎。MySQL 的核心就是存储引擎。

MySQL支持的存储引擎
mysql支持的存储引擎有，InnoDB、MyISAM、Memory、Merge、Archive、Federated、CSV、BLACKHOLE
等其中MyISAM是5.5之前的默认存储引擎，InnoDB是5.5以及之后的默认存储引擎

在这里插入图片描述
Innodb引擎（聚簇索引、主键索引）
聚簇索引并不是一种单独的索引类型，而是一种数据存储方式，具体细节依赖于其实现方式。
聚簇索引：
将数据存储和索引放到了一块，找到了索引也就找到了数据
一般情况下主键会默认创建聚簇索引，且一张表只允许存在一个聚簇索引。
非聚簇索引：
将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行。
MyISAM通过key_buffer把索引先缓存到了内存中，当需要访问数据时(通过索引访问数据)，在内存中直接查找索引，然后通过索引找到磁盘相应数据。这也就是为什么索引不在key buffer命中时，速度慢的原因
4.

数据库在设计阶段需要选择正确的数据类型对于获得高性能是至关重要的。比如说varchar
（200）和varchar（5）存储一个“hai”对于空间的开销是一样，但是varchar（5）对性能提
升就要高于varchar（200）。虽然占用的硬盘是一样的但是varchar（200）的列占用的内
存都会使用200个字节来存每个字段。所以最好的策略只是分配合适的需要空间。


什么时候适合添加索引：
	1、主键自动建立主键索引(唯一索引)
	2、where字句中的列，频繁作为查询字段的列
	3、表连接关联的列
	4、排序用到的列
	5，列的基数越大(选择性大)，索引的效率就越高

不适合索引的情况：
	1、表记录太少
	2、频繁修改的字段
	3、数据重复且分布平均的字段
	
索引会失效：
In notin  不等  对列进行计算或者使用函数时

索引的分类：
	1、单列索引
		 - 即一个索引只包含单个列，一个表可以有多个单列索引
	2、复合索引（多列索引）
		- 即一个索引包含多个列
	3、唯一性索引
		- 索引列的值必须唯一，但是允许有空值
	4、全文索引
		- 索引的是内容中的关键词，用于全文检索
	5、主键索引

他会先拿到索引的指针去查询表里的数据行这个过程中和数据多少条没有关系，因为他是根据索引第几行然后去表里面去查多少条就好了。这个索引和姓名有关系，这个表中出现姓名都会出现在我的表里面那为什么找表的时候是全局找，找索引却不是全索引能立刻找到呢？和数据结构有关系

在这里插入图片描述
5.

为什么mysql索引不用哈希表做数据结构而用B-Tree因为如果你的条件是一个范围查询比如年龄大于18，哈希表是没有用的，哈希表是本来就是乱序的，就必须扫描整个哈希表还不如扫描整表所以用树查询

5.1：
索引的底层是一颗B+树，那么联合索引当然还是一颗B+树，只不过联合索引的健值数量不是一个，而是多个。构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树。其实说白了就是存储信息双向链表的
6. 优化案列
在这里插入图片描述
6.2

6.3
列子：单表查

select * from student where age = 16 order by cid asc;

6.4查看这条sql语句执行时间

#profile操作
show variables like 'profiling';
set profiling = on;
show profiles;

6.5查看sql是如何执行的；发现是全表扫描，文件外排序，（索引本身就已经排序好了）

explain select c.cname, count(*) from score sc join course c on sc.cid = c.id

在这里插入图片描述

6.6 为了得到索引之后的查询时间，但是mysql是有缓存的得把他关闭了‘’

#关闭查询缓存 - 临时设置，永久设置，wordes是在修改my.cnf   虚拟机是 my.ini
show variables like '%cache%'
set query_cache_type=0;
set global query_cache_size=0;

6.7 创建索引

create index idx_age_cid on student(age,cid);

7关联查询

select c.cname, count(*) from score sc join course c on sc.cid = c.id
group by c.id

7.1 方案一：原sql加索引 0.69s

 create index idx_cid on score(cid)

在这里插入图片描述

7.2 方案二：成绩表分组 + 查询 + 子查询 0.616s 关联查询其实就是a表的每个字段，在b表全表扫描。我们可以写子查询

explain
select (select cname from course where id = sc.cid), count(*) 
	from score sc group by sc.cid

8.分页查询 #优化前：10W页 - 5.2s

explain 
select s.name, count(*) 
	from student s join score sc on s.id = sc.sid 
	group by sc.sid
	order by sc.sid desc
	limit 199998,2

执行计划
在这里插入图片描述
8.1：优化后 1.3s 但是还有全表扫

create index idx_sid on score(sid)

在这里插入图片描述
8.2 优化后 0.06s
#10W页的id覆盖索引查询
#id -> 关联其他的表查询数据（延迟关联）
create index idx_id_name on student(id,name

explain
select (select name from student where id = t.id), count(*) from score sc join (
	select id from student s
	order by s.id desc
	limit 199998,2
) t on t.id = sc.sid group by t.id