11.s_性能优化

最新推荐文章于 2024-07-22 15:12:37 发布

comochris

最新推荐文章于 2024-07-22 15:12:37 发布

阅读量145

点赞数

分类专栏： mysql基础文章标签：数据库

本文链接：https://blog.csdn.net/qq_33271192/article/details/108455608

版权

mysql基础专栏收录该内容

16 篇文章 0 订阅

订阅专栏

性能下降SQL慢执行时间长等待时间长

查询数据过多

能不能拆，条件过滤尽量少
关联太多表，太多join

join 原理。用 A 表的每一条数据扫描 B表的所有数据。所以尽量先过滤。
没有利用索引
1. 单值 ==> create index idx_user_name on user(name)
2. 复合
服务器调优及各个参数设置（缓冲、线程数等）（不重要的DBA工作）

索引

是帮助MYSQL高效率获取数据的数据结构。（本质是数据结构，可以类比字典–排好序的快速查找数据结构b树）

索引本身也很大，不可能全部存在内存中，，因此往往以引索文件的的形式存储在磁盘上。

平常没有特别指明都是B树（不一定是二叉树）结构组织的索引，其中聚集索引、次要索引、覆盖索引、复合索引、前缀索引、唯一索引默认都是B+树索引、其他还有哈希索引（hash index）

优势

类似大学图书馆数目引索、提高数据检索的效率，降低数据库的IO成本
通过引索多数据进行排序、降低数据排序的成本、降低CPU的消耗

劣势

提高查询速度，但降低了更新表的速度。多保存索引文件每次更新添加了索引列的字段
需要花时间建立最优索引，或优化查询

单值索引 一个索引只包l含单个个例，一个表可以有多个单列索引

唯一索引 索引列的值必须唯一，但允许有空值

复合索引 一个索引包含多个个例

#有四种方式来添加数据表的索引：
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)#: 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)#: 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

ALTER TABLE tbl_name ADD INDEX index_name (column_list)#: 添加普通索引，索引值可出现多次。

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)#:该语句指定了索引为 FULLTEXT ，用于全文索引。

#复合索引：
create index idx_no_name on emp(no,name);#  // no 与  name 有同一个索引 idx_no_name
#单值索引：
create index idx_no on emp(no);
create index idx_name on emp(name);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HYk35rDA-1599482056467)(C:\Users\Administrator\Desktop\数据库学习-mysql\B-tree.jpg)]

【初始化介绍】
非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

【查找过程】
如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。

真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hD5rsjUW-1599482056470)(C:\Users\Administrator\Desktop\数据库学习-mysql\B+tree.jpg)]

常见性能瓶颈

SQL中对大量数据进行比较、关联、排序、分组

实例内存满足不了缓存数据或排序等需要，产生大量物理IO

不适宜的锁设置，导致线程阻塞，性能下降

死锁，线程之间交叉调用资源，导致死锁

服务器硬件性能，top,free,iostat和vmstat来查看系统性能状态

====>>>>>> 使用 EXPLAIN 关键字可以模拟优化SQL查询语句，从而知道Mysql是如何处理sql语句的。

三步走 ===>>> 优化器、看瓶颈、使Explain

@@@@@@Explain + SQL 检查@@@@@@@@

表的读取顺序
哪些引索可以用
数据读取操作的操作类型
哪些引索被实际使用
表之间的引用
每张表有多少行被优化器查询

执行计划包含信息

join语句的优化

ALTER TABLE 'phone' ADD INDEX z('card');
ALTER TABLE 'book' ADD INDEX Y('card'); #上一个case建一个同样的
ENABLE SELECT * FROM class LEFT JOIN book ON class.card=book.card LEFT JOIN phone ON book.card = phone.card

尽可能减少join语句中NestedLoop的循环总数，“永远用小结果集驱动大结果集”
优先优化NestedLoop的内层循环
保证Join语句中被驱动表上Join条件的字段已经被索引
当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要吝惜JoinBuffer的设置

在这个使用buffer的例子中，可以看到这个地方只是对于tsecer表进行了一次扫描，而通常来说，数据库的扫描代码是最高的(因为要涉及到磁盘读取)，这样使用buffer的方式将tsecer表的扫描降低为1次，所以这个效率提高很多，特别是在涉及到的多个table，并且/或者每个table中的记录数量都很多的情况下。

索引优化

全值匹配
最佳左前缀法则
- 查询从索引最左列开始，且不跳过引索中的列
不要再索引列上做任何操作（计算、函数、（自动/手动）类型转换），会导致全表扫描
```
explain SELECT * FROM staff WHERE left(NAME,4) = 'July':
```
尽量使用覆盖引索（只访问引索的查询（索引列和查询列一致））减少select *
使用不等于（!= 或者 <>）的时候无法使用索引会导致全表扫描
is not null无法使用引索，但是is null可以使用

like以通配符开头（’%abc’）mysql 引索失效会变成全表扫描操作，%加在右边会好点

使用覆盖索引来解决

#create index
CREATE INDEX idx_user_nameAge ON tbl_user(name,age);

SELECT id,NAME FROM tbl_user WHERE NAME LIKE '%aa%'
SELECT id,NAME,age FROM tbl_user WHERE NAME LIKE '%aa%'
SELECT NAME,age FROM tbl_user WHERE NAME LIKE '%aa%'

#以上都有效  查询与建立索引字段个数顺序

字符串不加单引号引索失效（隐式类型转换）
少用or

假设index(abc)

Where语句索引是否被使用
where a = 3 Y,使用到a
where a = 3 and b = 5 Y,使用到a，b
where a = 3 and b = 5 and c = 4 Y,使用到a,b,c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4 N
where a = 3 and c = 5 使用到a，但是c不可以，b中间断了
where a = 3 and b > 4 and c = 5 使用到a和b， c不能用在范围之后，b后断了
where a = 3 and b like ‘kk%’ and c = 4 Y,使用到a,b,c
where a = 3 and b like ‘%kk’ and c = 4 Y,只用到a
where a = 3 and b like ‘%kk%’ and c = 4 Y,只用到a
where a = 3 and b like ‘k%kk%’ and c = 4 Y,使用到a,b,c

面试

查询优化器可以优化mysql语句（查询顺序）

#index(c1,c2,c3,c4)
#const const const const
explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4'

#范围之后全失效 keylen => 93
explain select * from test03 where c1='a1' and c2='a2' and c3>'a3' and c4='a4'

#底层调优 ==> 3 x const keylen => 124
explain select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3'

#c3作于与排序而非查找，没有统计于const				const keylen => 64
explain select * from test03 where c1='a1' and c2='a2' and c4='a4' order by c3

#extre ==> Using filesort	性能下降	       const keylen => 64
explain select * from test03 where c1='a1' and c2='a2' order by c4

#extre ==> c2,c3用于排序	      
explain select * from test03 where c1='a1' and c5='a5' order by c2,c3

#extre ==> Using filesort                     
explain select * from test03 where c1='a1' and c5='a5' order by c3,c2

#extre ==> c2,c3用于排序	      
explain select * from test03 where c1='a1' and c2 = 'a2' order by c2,c3

#extre ==>无 Using filesort  ！！！！ 此时c2为常量          复合引索树结构            
explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c3,c2

#extre ==>
explain select * from test03 where c1='a1' and c4='a4' order by c2,c3

#extre ==> using temporary using filesort      
explain select * from test03 where c1='a1' and c5='a5' order by c3,c2

group by 几乎和order by一致

对于单值索引，尽量选择对当前query过滤性更好的索引
组合索引时，当前query过滤性更好的索引位置越靠前越好
尽量选择可以能够包含query中where字句中更多字段的索引
尽可能通过分析统计信息和调整query写法来达到选择合适索引的目的

官方文档 https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-types

order by一致

对于单值索引，尽量选择对当前query过滤性更好的索引
组合索引时，当前query过滤性更好的索引位置越靠前越好
尽量选择可以能够包含query中where字句中更多字段的索引
尽可能通过分析统计信息和调整query写法来达到选择合适索引的目的

官方文档 https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-types

comochris

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
11.s_性能优化

性能下降SQL慢执行时间长等待时间长查询数据过多能不能拆，条件过滤尽量少关联太多表，太多joinjoin 原理。用 A 表的每一条数据扫描 B表的所有数据。所以尽量先过滤。没有利用索引单值 ==> create index idx_user_name on user(name)复合服务器调优及各个参数设置（缓冲、线程数等）（不重要的DBA工作）索引是帮助MYSQL高效率获取数据的数据结构。（本质是数据结构，可以类比字典–排好序的快速查找数据结构b
复制链接

扫一扫