MySQL从入门到高级 --- 15.优化 16.pymysql

2024-06-10 23:12
文章标签 mysql 16 入门 15 优化 高级 database pymysql

本文主要是介绍MySQL从入门到高级 --- 15.优化 16.pymysql，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

文章目录

    • 第十五章 && 第十六章：
      • 15.优化
        • 15.1 查询SQL执行效率
        • 15.2 定位低效率执行SQL
        • 15.3 explain分析执行计划 - 基本使用
        • 15.4 explain分析执行计划 - id
        • 15.5 explain分析执行计划 - select_type
        • 15.6 explain分析执行计划 - type
        • 15.7 explain分析执行计划 -其他指标字段
        • 15.8 show profile分析SQL
        • 15.9 trace分析优化器执行计划
        • 15.10 使用索引优化
          • 15.10.1 数据
          • 15.10.2 避免索引失效应用 - 权值匹配
          • 15.10.3 避免索引失效应用 - 最左前缀法则
          • 15.10.4 避免索引失效应用 - 其他匹配
        • 15.11 SQL优化
          • 15.11.1 大批量数据加载优化
          • 15.11.2 insert优化
          • 15.11.3 order by优化
            • FileSort优化
          • 15.11.4 子查询优化
          • 15.11.5 limit优化
      • 16.pymysql
        • 16.1 查询操作
        • 16.2 增删改操作

第十五章 && 第十六章：

15.优化

优化方式：

  • 从设计上

  • 从查询上

  • 从索引上

  • 从存储上

15.1 查询SQL执行效率

语法：show [session | global |] status

  • 查看服务器状态信息

在这里插入图片描述

参数意义
Com_select执行select操作数次，一次查询只累加1
Com_insert执行insert操作数次，对批量插入的insert操作，只累加1次
Com_update执行update操作数次
Com_delete执行delete操作数次
Innodb_rows_readselect查询返回的行数
Innodb_rows_inserted执行insert操作插入的行数
Innodb_rows_updated执行update操作更新的行数
Innodb_rows_deleted执行delete操作删除的行数
Connections试图连接MySQL服务器的次数
Uptime服务器工作时间
Slow_queries慢查询的次数
15.2 定位低效率执行SQL

两种方式：

  • 慢查询日志：定位执行效率较低的SQL语句

  • show processlist：该指令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可实时查看SQL执行情况，同时对一些锁表操作进行优化

在这里插入图片描述

15.2.1 show processlist

在这里插入图片描述

  1. id：用户登录mysqld时，系统分配的"connection_id"，可用函数connection_id()查询

  2. user：显示当前用户。若不是root，该命令只显示用户权限范围的SQL语句

  3. host：显示语句从哪个ip端口上发的，可用来跟踪出现问题语句的用户

  4. db：显示进程目前连接的数据库

  5. command：显示当前连接执行的命令，一般取值为休眠(sleep),查询(query)，连接(connect)等

  6. time：显示状态持续的时间，单位是秒

  7. state：显示使用当前连接的SQL语句状态。描述的是语句执行中的某一个状态

  8. info：显示SQl语句，判断问题语句的重要依据

15.3 explain分析执行计划 - 基本使用

在这里插入图片描述

  • id：序列号，查询中执行select子句或者是操作表的顺序

  • select_type：SELECT的类型

  • table：输出结果集的表

  • type：表的连接类型

  • possible_keys：查询时，可能使用的索引

  • key：实际使用的索引

  • key_len：索引字段的长度

  • rows：扫描行的数量

  • extra：执行情况的说明和描述

15.4 explain分析执行计划 - id

  • 相同id表示加载表的顺序是从上到下

  • 不同id值越大，优先级越高，越先被执行

  • id有相同，也有不同，同时存在。id相同可认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先被执行

15.5 explain分析执行计划 - select_type
  • 表示SELECT类型
select_type意义
SIMPLEselect查询，查询中不包含子查询或UNION
PRIMARY查询中包含任何复杂的子查询，最外层查询标记为该标识
SUBQUERYSELECT或WHERE列表中包含了子查询
DERIVEDFROM列表中包含的子查询，被标记为DERIVED，MySQL会递归执行子查询，将结果放入临时表中
UNION若第二个SELECT出现在UNION后，则标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记DERIVED
UNION RESULTUNION表获取结果的SELECT
15.6 explain分析执行计划 - type
  • 显示访问类型
type意义
NULL不访问任何表，索引，直接返回结果
system系统表，少量数据，通常不需要进行磁盘IO
const命中主键或唯一索引
eq_ref对于前表的每一行，后表只有一行被扫描
ref非唯一性索引扫描，返回匹配某个单独值的所有行
range只检索给定返回的行，使用一个索引来选择行
index需要扫描索引上的全部数据
all全表扫描，此时id上无索引

结果值从最好到最坏：system->const->eq_ref->ref->range->index->all

15.7 explain分析执行计划 -其他指标字段

  • table：显示所访问数据库中表名称有时不是真实的表名字，可能是简称

  • rows：扫描行的数量

  • possible_keys：显示可能应用在该表的索引，一个或多个

  • key：实际使用的索引，若为NULL，则没有使用索引

  • key_len：索引中使用的字节数，该值为索引字段最大可能长度，不是实际使用长度，在不损失精度的情况下，长度越短越好

  • extra：

extra意义
using filesort说明数据库会对数据使用一个外部的索引排序，不按照表内的索引顺序进行读取，效率较低
using temporary需建立临时表来暂存中间结果，效率较低
using indexSQL所需返回的所有列数据均在一颗索引树上，避免访问表的数据行，效率可观
15.8 show profile分析SQL

通过have_profiling参数，可看到当前MySQL是否支持profile

select @@have_profiling;
set profiling=1 -- 开启profiling开关

show profile for query query_id 可查看到该SQL执行过程中每个线程的状态和消耗时间

在这里插入图片描述

15.9 trace分析优化器执行计划
set optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;
  • 打开trace，设置格式为json，并设置trace最大能用的内存大小，避免解析过程中默认内存太小而不能完整展示

通过information_schema.optimizer_trace可知道MySQL如何执行SQL

-- 执行SQL
select * from emp;
select * from information_schema.optimizer_trace \G;

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

15.10 使用索引优化
15.10.1 数据
create table stu(id int,name varchar(20),age int,sex varchar(20),address varchar(20),primary key(id)
);insert into stu values(1,'xiaoming',19,'男','北京');
insert into stu values(2,'xiaowang',25,'女','深圳');
insert into stu values(3,'xiaohong',42,'男','北京');
insert into stu values(4,'xiaowu',35,'男','北京');
insert into stu values(5,'xiaoli',52,'女','上海');
insert into stu values(6,'xiaomi',21,'男','北京');
insert into stu values(7,'xiaolin',35,'女','深圳');
insert into stu values(8,'xiaowa',41,'男','深圳');
insert into stu values(9,'xiaoya',23,'女','深圳');
insert into stu values(10,'xiaoyi',27,'女','上海');
insert into stu values(11,'xiaoniu',23,'男','北京');
insert into stu values(12,'xiaohei',43,'女','上海');create index index_name_sex_address on stu(name,sex,address); -- 创建组合索引
15.10.2 避免索引失效应用 - 权值匹配
  • 该情况下，索引生效，执行效率高
-- 和字段匹配成功即可，和字段无关
explain select * from stu where name = 'xiaoming' and age = '19' and address = '北京';
explain select * from stu where address = '北京' and name = 'xiaoming' and age = '19';
15.10.3 避免索引失效应用 - 最左前缀法则
  • 该情况下，索引生效，执行效率高
-- 若索引了多列，要遵守最左前缀法则。查询从索引的最左前列开始，且不跳过索引中的列
explain select * from stu where name = 'xiaoming';-- 违反最左前缀法则，索引失效
explain select * from stu where id = '1';-- 复合最左前缀法则，但出现跳跃某一列，只有最左列索引生效
explain select * from stu where name = 'xiaoming' and address = '北京';
15.10.4 避免索引失效应用 - 其他匹配
  • 该情况下，索引生效，执行效率高
/*
Extra:using index ：使用覆盖索引时会出现using where ：查找使用索引情况下，需回表去查询所需数据using index condition ：查找使用了索引，但需要回表查询数据using index;using where ：查找使用了索引，但所需的数据都在索引列中能找到，因此无需回表查询数据
*/-- 范围查询右边的列，不能使用索引
explain select * from stu where name = 'xiaoming' and age > '18' and address = '北京';-- 不要在索引列上进行运算操作，不然索引将失效
explain select * from stu where substring(name,5,4) = 'ming';-- 字符串不加单引号，会造成索引失效
explain select * from stu where name = 'xiaoming' and age > 18;-- 尽量使用覆盖索引，避免select * 
explain select * from stu where name = 'xiaoming' and age = 19; -- 需从原表及磁盘上读取数据，效率较低-- 从索引树中就可查询所有数据
explain select name from stu where name = 'xiaoming' and address = '北京'; -- 效率较高
explain select name,age from stu where name = 'xiaoming' and address = '北京'; -- 效率较高
explain select name,age,sex,address from stu where name = 'xiaoming' and address = 'beijing'; -- 效率较高-- 用or分隔的条件，若or前的条件中的列有索引，而后面的列没有索引，那么涉及的索引都不会被用到
explain select * from stu where name = 'xiaoming' or address = '北京';-- 以%开头的like模糊查询，索引失效
explain select * from stu where name like 'ming%'; -- 使用索引
explain select * from stu where name like '%ming'; -- 不用索引
explain select * from stu where name like '%ming%'; -- 不用索引-- 弥补不足，不用*,使用索引列
explain select name from stu where name like '%ming%';-- 若MySQL评估使用索引比全表慢，则不使用
create index index_stu_address on stu(address);explain select * from stu where address = '北京'; -- 没用索引-- is NULL, is NOT NULL  有时有效 有时失效
create index index_age on stu(age);
explain select * from stu where address is NULL;  -- 有效
explain select * from stu where age is not NULL; -- 无效-- in 有效 , not in 索引无效
-- 普通索引
explain select * from stu where name in('xiaoming','xiaohong'); -- 使用索引
explain select * from stu where name not in('xiaoming','xiaohong'); -- 不使用索引-- 主键索引 
explain select * from stu where age in(18, 19); -- 使用索引
explain select * from stu where age not in(18, 19); -- 不使用索引-- 单列索引和复合索引，尽量使用复合索引
create index index_stu_name_age_address on stu(name, age, address);
-- 等价 ---》 name / name + age / name + age + addresscreate index index_address on stu(address);explain select * from stu where name = 'xiaoming' and age > 18 and address = '北京';-- 若一张表有多个单列索引，即使WHERE中都使用了索引列，则只有一个最优索引列生效
create index index_name on stu(name);
create index index_age on stu(age);
create index index_address on stu(address);
explain select * from stu where name = 'xiaoming' and age = '19' and address = '北京';
15.11 SQL优化
15.11.1 大批量数据加载优化

使用load命令导入数据时，适当设置可提高导入效率。对于InnoDB类型的表，以下方式可提高导入的效率：

  • 主键顺序插入：InnoDB类型的表按照主键的顺序保存，将导入的数据按照主键的顺序排列，可有效提高导入数据的效率

  • 关闭唯一性校验：导入数据前执行SET UNIQUE_CHECKS=0,关闭唯一性校验，导入结束后SET UNIQUE_CHECKS=1, 恢复唯一性校验，可提高导入效率

15.11.2 insert优化
-- 原始方法
insert into stu values(1,'xiaoming',19,'男','北京');
insert into stu values(2,'xiaowang',25,'女','深圳');
insert into stu values(3,'xiaohong',42,'男','北京');-- 优化后
insert into stu values(1,'xiaoming',19,'男','北京'),(2,'xiaowang',25,'女','深圳'),(3,'xiaohong',42,'男','北京');-- 大大缩减客户端与数据库之间的连接、关闭等消耗-- 在事务中进行数据插入
begin;
insert into stu values(1,'xiaoming',19,'男','北京');
insert into stu values(2,'xiaowang',25,'女','深圳');
insert into stu values(3,'xiaohong',42,'男','北京');
commit;-- 数据有序插入
insert into stu values(3,'xiaoming',19,'男','北京');
insert into stu values(1,'xiaowang',25,'女','深圳');
insert into stu values(2,'xiaohong',42,'男','北京');-- 优化后
insert into stu values(1,'xiaoming',19,'男','北京');
insert into stu values(2,'xiaowang',25,'女','深圳');
insert into stu values(3,'xiaohong',42,'男','北京');
15.11.3 order by优化

  • 通过对返回数据进行排序，该情况为filesort排序，所有不是通过索引直接返回排序结果的排序都叫FileSort排序

  • 通过有序索引顺序扫描直接返回有序数据，该情况为using index，无需额外排序，操作效率高

    explain select * from stu order by age; -- using filesort
explain select * from stu order by age,sex; -- using filesortexplain select id from stu order by age; -- using index
explain select id,age from stu order by age; -- using index
explain select id,age,sex from stu order by age; -- using index-- order by后的多个排序字段尽量排序方式相同 
explain select id,age from stu order by age asc, sex desc; -- Using index;Using filesort
explain select id,age from stu order by age desc, sex desc; -- Backward index scan;Using index-- order by后边的多个排序字段字段顺序尽量和组合索引字段一致
explain select id,age from stu order by sex,age; -- using index, using filesort
FileSort优化

通过创建合适的索引，减少FileSort的出现，需要加快排序操作，MySQL有两种排序算法：

  • 两次扫描算法：先根据条件取出排序字段和行指针信息，然后在排序区sort buffer中排序，若sort buffer不够，则在临时表temporary table中存储排序结果。完成排序后，再根据行指针回表读取记录，该操作会导致大量随机I/O操作

  • 一次扫描算法：一次性取出满足条件的所有字段，，然后在排序区sort buffer中排序后直接输出结果集。排序时内存开销较大，但排序效率比两次扫描算法更高
    MySQL通过比较系统变量max_length_for_sort_data和Query语句取出的字段总大小，判定使用哪种算法，若max_length_for_sort_data更大，使用第二种优化后的算法，反之使用第一种算法。可适当提高max_length_for_sort_data和sort_buffer_size系统变量，来增大排序区的大小，提高排序效率

show variables like 'max_length_for_sort_data'; -- 4096
show variables like 'sort_buffer_size'; -- 262144
15.11.4 子查询优化

使用子查询可一次性完成很多逻辑上需要多个步骤才能完成的操作，同时还可避免事务或表锁死，写起来也比较容易。但在有些情况，子查询可被更加高效的连接(JOIN)替代

explain select * from stu where uid in (select uid from stu_role);explain select * from stu s, stu_role sr where s.uid = ur.uid

system>const>eq_ref>ref>range>index>ALL

连接(JOIN)查询效率更高些的原因是MySQL不需要在内存中创建临时表来完成逻辑上需要两个步骤的查询工作

15.11.5 limit优化

  • 在索引上完成排序分页操作，最后根据主键关联回原表查询所需的其他列内容

  • 可把limit查询转换程某个位置的查询，适用于主键自增的表

select count(*) from stu;select * from stu limit 0,10;select * from * from stu limit 900000,10; -- 0.582select * from stu a, (select id from stu2 order by id limit 900000,10) b where a.id = b.id -- 0.375explain select * from stu where id > 90000 limit 10;

16.pymysql

16.1 查询操作

在这里插入图片描述

16.2 增删改操作

在这里插入图片描述
在这里插入图片描述

这篇关于MySQL从入门到高级 --- 15.优化 16.pymysql的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1049538。

相关文章

Vue3 的 shallowRef 和 shallowReactive：优化性能

Vue3 的 shallowRef 和 shallowReactive：优化性能

大家对 Vue3 的 ref 和 reactive 都很熟悉，那么对 shallowRef 和 shallowReactive 是否了解呢？ 在编程和数据结构中，“shallow”（浅层）通常指对数据结构的最外层进行操作，而不递归地处理其内部或嵌套的数据。这种处理方式关注的是数据结构的第一层属性或元素，而忽略更深层次的嵌套内容。 1. 浅层与深层的对比 1.1 浅层（Shallow） 定义
阅读更多...
Ilya-AI分享的他在OpenAI学习到的15个提示工程技巧

Ilya-AI分享的他在OpenAI学习到的15个提示工程技巧

Ilya（不是本人，claude AI）在社交媒体上分享了他在OpenAI学习到的15个Prompt撰写技巧。 以下是详细的内容： 提示精确化:在编写提示时,力求表达清晰准确。清楚地阐述任务需求和概念定义至关重要。例:不用"分析文本",而用"判断这段话的情感倾向:积极、消极还是中性"。 快速迭代:善于快速连续调整提示。熟练的提示工程师能够灵活地进行多轮优化。例:从"总结文章"到"用
阅读更多...
Spring Security 从入门到进阶系列教程

Spring Security 从入门到进阶系列教程

Spring Security 入门系列 《保护 Web 应用的安全》 《Spring-Security-入门（一）：登录与退出》 《Spring-Security-入门（二）：基于数据库验证》 《Spring-Security-入门（三）：密码加密》 《Spring-Security-入门（四）：自定义-Filter》 《Spring-Security-入门（五）：在 Sprin
阅读更多...
SQL中的外键约束

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点： 1.确保子表中的某个字段（外键）只能引用父表中的有效记录2.主表中的列被删除时，子表中的关联列也会被删除3.主表中的列更新时，子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示
阅读更多...
基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
这15个Vue指令，让你的项目开发爽到爆

这15个Vue指令，让你的项目开发爽到爆

1. V-Hotkey 仓库地址: github.com/Dafrok/v-ho… Demo: 戳这里 https://dafrok.github.io/v-hotkey 安装: npm install --save v-hotkey 这个指令可以给组件绑定一个或多个快捷键。你想要通过按下 Escape 键后隐藏某个组件，按住 Control 和回车键再显示它吗？小菜一碟： <template
阅读更多...
如何去写一手好SQL

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数，谈性能都是耍流氓。MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。 博主曾经操作过超过4亿行数据
阅读更多...
HDFS—存储优化（纠删码）

HDFS—存储优化（纠删码）

纠删码原理 HDFS 默认情况下，一个文件有3个副本，这样提高了数据的可靠性，但也带来了2倍的冗余开销。 Hadoop3.x 引入了纠删码，采用计算的方式，可以节省约50％左右的存储空间。 此种方式节约了空间，但是会增加 cpu 的计算。 纠删码策略是给具体一个路径设置。所有往此路径下存储的文件，都会执行此策略。 默认只开启对 RS-6-3-1024k
阅读更多...
性能分析之MySQL索引实战案例

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题，本文将一起从代码层去分析为什么没有建立索引？ 开源ERP项目地址：https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置
阅读更多...
使用opencv优化图片（画面变清晰）

使用opencv优化图片（画面变清晰）

文章目录 需求影响照片清晰度的因素 实现降噪测试代码 锐化空间锐化Unsharp Masking频率域锐化对比测试 对比度增强常用算法对比测试 需求 对图像进行优化，使其看起来更清晰，同时保持尺寸不变，通常涉及到图像处理技术如锐化、降噪、对比度增强等 影响照片清晰度的因素 影响照片清晰度的因素有很多，主要可以从以下几个方面来分析 1. 拍摄设备 相机传感器：相机传
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2