#SQL#

MySQL性能优化与索引策略
最新推荐文章于 2025-01-03 10:26:55 发布
最新推荐文章于 2025-01-03 10:26:55 发布
阅读量433 收藏
点赞数
CC 4.0 BY-SA版权
分类专栏： 数据库 文章标签： sql 数据库 database
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/sinat_26661453/article/details/82978511

EXPLAIN 

MySQL会根据配置文件会限制server接受的数据包的大小。如果写入大数据时，因为默认的配置太小，插入和更新操作会因为 max_allowed_packet 参数限制，而导致失败。

gh-ost

添加一条和张三一样的数据，但是名字改李四
insert into table (name,data)(select '李四',data from table where  name='张三', )

两段锁2PL：如果两个事务同时尝试写入同一个对象，则锁可确保第二个写入必须等到第一个写入完成事务（中止或提交），然后才能继续，两段锁类似，但使锁的要求更强，只要没有写入，就允许多个事务同时读取同一个对象，但对象只要有写入（修改或删除）就需要独占访问权限例1，如果事务A读取了一个对象，并且事务B想要写入该对象，事务B必须等事务A提交或中止。例2，如果事务A写入了一个对象，并且事务B想要读取该对象，则B必须等A提交或中止才能继续。

2PL和快照隔离的区别：2PL写不仅会阻塞其他写入也会阻塞读，反之亦然，快照隔离使得读不阻塞写，写也不阻塞读，2PL提供了可序化的性质，可以防止竞争条件，包括丢失更新和写入偏差。

实现两阶段锁，MySQLInnoDB的可序化隔离级别。

两阶段提交涉及多次节点间的网络通信，通信时间太长！

事务时间相对于变长了，锁定的资源的时间也变长了，造成资源等待时间也增加好多！

正是由于分布式事务存在很严重的性能问题，大部分高并发服务都在避免使用，往往通过其他途径来解决数据一致性问题。比如使用消息队列来避免分布式事务

XA事务：MySQL中基于XA实现的分布式事务

线性一致性和顺序一致性：分布式系统一致性 - Kaiyuan's Blog | May the force be with me

一致性：强一致性、顺序一致性、弱一致性和共识_chao2016的博客-优快云博客_强一致性

分区：MySQL性能优化(六)：分区_monday的博客-优快云博客

复合索引：复合索引的优点和注意事项 - summer0space - 博客园

数据库优化：MySQL性能优化的21个最佳实践 和 mysql使用索引_我是guyue，guyue就是我O(∩_∩)O-优快云博客

or的效率为O(n)，而in的效率为O(logn)

SQL语句进行left join时导致的索引失效案例

https://segmentfault.com/a/1190000023072397

书写sql语句时各关键字的顺序： 
select 
from 
where 
group by 
having 
order by

执行顺序： 
from 
where 
group by 
having 
select 
order by

自动转换导致索引失效，从而使锁的级别从行锁升级为表锁

优化

①尽可能让所有数据都通过索引来完成，避免无索引行升级为表锁。

②合理设计索引，尽量缩小锁的范围。

③尽可能使用较少的检索条件，避免间隙锁。

④尽量控制事务大小，减少锁定资源量和时间长度。

⑤尽可能降低事务隔离级别。

①最佳左前缀法则。

#1.在等值查询时，更改索引列顺序，并不会影响explain的执行结果，因为mysql底层会进行优化。

#2.在使用order by时，注意索引顺序、常量，以及可能会导致Using filesort的情况。

②group by容易产生Using temporary。

③通俗理解口诀：

   全值匹配我最爱，最左前缀要遵守；

   带头大哥不能死，中间兄弟不能断；

   索引列上少计算，范围之后全失效；

   LIKE百分写最右，覆盖索引不写星；

   不等空值还有or，索引失效要少用。

MySQL排序优化总结

最后整理一下优化MySQL排序的手段

  1. 排序和查询的字段尽量少。只查询你用到的字段，不要使用select *；使用Limit查询必要的行数据；
  2. 要排序或者查询的字段，尽量不要用不确定字符函数，避免MySQL直接分配max_sort_length，导致sort buffer空间不足；
  3. 使用索引来优化或者避免排序；
  4. 增加sort_buffer_size大小，避免磁盘排序；
  5. 不得不使用original排序算法时，增加read_rnd_buffer_size；
  6. 字段长度定义合适就好（避免过长）；
  7. tmpdir建议独立存放，放在高速存储设备上

数据库军规

  • 尽量不在数据库做运算。
  • 控制单表数据量。（纯int不超过1000W，含char不超过500W）
  • 保持表身段苗条。（表字段少而精）
  • 平衡范式与冗余。
  • 拒绝大SQL，大事务，大批量。

字段类

  • 用好数值字段类型。
  • 将字符转化为数字。
  • 优先使用枚举ENUM/SET
  • 避免使用NULL字段（很难进行查询优化，NULL列加索引，需要额外空间，含NULL复合索引无效）
  • 少用并拆分TEXT/BLOB
  • 不在数据库里存图片

索引类

  • 谨慎合理添加索引（改善查询，减慢更新，索引不是越多越好）
  • 能不加的索引尽量不加（综合评估数据密度和数据分布，最好不超过字段数的20%）
  • 结合核心SQL优先考虑覆盖索引。
  • 字符字段必须建前缀索引。
  • 不在索引列做运算。
  • 自增列或全局ID做InnoDB主键。
  • 尽量不用外键。

SQL类

  • SQL语句尽可能简单，大SQL拆成多条简单SQL（一条大SQL会吧整个数据库堵死，简单SQL缓存命中率高，减少锁表时间，用上多CPU，一条SQL只能在一个CPU运算）
  • 保持事务（连接）短小（用完即关，减少锁资源占用）
  • 尽可能给你避免使用存储过程，触发器，函数处理
  • 尽量不用select * （会消耗CPU，内存，IO，网络带宽）只取需要数据列。
  • 改写or为in （or效率On,in效率OLogn）注意in个数小于200，
  • 不同字段将or改写为union
  • 避免负向查询（not，！=，not in等）和%前缀模糊查询（使用不了索引导致全表扫描）
  • 减少count（*），（资源开销大）

  • 1.如果在开发中确实需要用到count()聚合，那么优先考虑count(*)，因为mysql数据库本身对于count(*)做了特别的优化处理。

    有主键或联合主键的情况下，count(*)略比count(1)快一些。 
    没有主键的情况下count(1)比count(*)快一些。 
    如果表只有一个字段，则count(*)是最快的。
    2.使用count()聚合函数后，最好不要跟where age = 1；这样的条件，会导致不走索引，降低查询效率。除非该字段已经建立了索引。使用count()聚合函数后，若有where条件，且where条件的字段未建立索引，则查询不会走索引，直接扫描了全表。 
    3.count(字段),非主键字段，这样的使用方式最好不要出现。因为它不会走索引

  • limit分页（Limit 10000,10.偏移量越大越慢，推荐where id>=12345 limit 11#10+1（每页10条））
  • 分解连接保证高并发（高并发不建议两个表以上的join）
  • group by去除排序（自动排序）
  • 同数据类型的列值比较（数字对数字，字符对字符where nuber=1 and char='1'）
  • 打散大批量更新（凌晨操作避开高峰）

约定类

  • 隔离线上线下
  • 禁止未经DBA确认的子查询上线
  • 永远不再程序端显示加锁
  • 统一字符集UTF8
  • 统一命名规范

【SQL知识】SQL注入中-- +和#的区别
KP_0x01的博客
06-28 455
适用于大多数数据库（MySQL、Oracle、SQL Server 等）。在 URL 中会被解码为空格，避免空格被过滤。：仅适用于 MySQL，在 URL 中需编码为。：通用性强，适用于大多数数据库，攻击者希望绕过密码验证，构造。在 URL 中需要编码为。返回所有用户数据（因为。（其他数据库可能不支持。同样返回所有用户数据。
[极客大挑战 2019]LoveSQL
qq_37301470的博客
11-12 680
万能密码登录成功没有发现flag 判断藏在数据库里 check.php?username=1' or '1'='1&password=123456' or '1'='1 登陆成功返回的密码 7a2d6a8fd56b61a79bba61ee8f5ad02c 直接开始联合注入 check.php?username=1' union select 1,2,3%23&password=1 check.php?username=1' union select 1,2,database()%23&
# SQL
yaoge1225的博客
07-17 118
SQL load_file()函数读取一个文件并将其内容作为字符串返回 其中file_name是文件的完整路径。 堆叠注入 1';show databases;# 1';show tables;# 1';show columns from FlagHere;# 1'; HANDLER FlagHere OPEN;HANDLER FlagHere READ FIRST;HANDLER FlagHere CLOSE;# 一个知识点： HANDLER … OPEN语句打开一个表，使其可以使用后续
#SQL简介
weixin_30279751的博客
09-20 403
SQL是结构化查询语言（Structured Query Language）简称，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。 MySQL是一个关系型数据库管理系统（RDBMS），由瑞典MySQL AB公司开发，目前属于Oracle旗下产品。MySQL是最流行的关系型数据库管理系统之一。 结构化查询语言包含6个部分： 数据查询语言（DQL:Data Q...
# Sql 常用语句
不积跬步，无以至千里；不积小流，无以成江海
04-26 1082
常用工具Sql explain 查看Sql执行计划 explain select * from testone order by age asc limit 0,3; 查看所有进程 show full processlist; 查询事物 select * from information_schema.INNODB_TRX it ; 结束进程：kill {id} DDL 创建数据库 create database ldtest; 删除数据库 drop databases test; 创建新表
SQL中，# 和 $ 用于不同的占位符语法
最新发布
SSHLY3的博客
01-03 757
预编译处理：#{} 中的内容会被视为一个 JDBC 预编译语句的参数。直接替换：${} 中的内容会被直接替换为变量的值，不会进行预编译处理。这相当于将变量值直接拼接到 SQL 字符串中。存在 SQL 注入风险：由于是直接替换，如果输入未经严格校验，可能会导致 SQL 注入攻击。防止 SQL 注入：由于使用了预编译语句，可以有效防止 SQL 注入攻击。类型安全：MyBatis 会根据 Java 类型自动处理数据类型的转换。灵活性：适用于一些动态 SQL 场景，例如表名或列名的动态替换。
ZGG2016#sql-practice-niuke#SQL48：将所有获取奖金的员工当前的薪水增加10%1
07-25
1、题目 2、题解 3、涉及内容
lvshaoyang#Reading-Notes#SqlServer远程备份到本地1
07-25
2、--在sql中建立映射 4、--将备份好的文件复制到本地 5、关闭xp_cmdshell
tencentyun#intlcloud-documents#SQL 基本操作_intl_zh1
07-25
本文为您列举了部分常用 SQL 命令。如需了解更详细的 SQL 命令信息，包括命令参数和限制条件等，请参见 MySQL 官方指南。查询版本方法一：方法二：方法三
Java-Edge#Java-Interview-Tutorial#SQL注入1
07-25
解决sql注入sql预编译在服务器启动时，mysql client把sql语句模板（变量采用占位符）发给mysql服务器，mysql服务器对sql语句模板进行编
### MyBatis动态SQL介绍说明、使用技巧和优缺点
04-28
### MyBatis动态SQL介绍说明、使用技巧和优缺点 #### 一、MyBatis动态SQL简介 MyBatis作为一款出色的持久层框架，通过XML或注解的方式配置SQL语句，有效地实现了Java方法与SQL语句之间的映射。动态SQL是MyBatis的...
【sql语句中的符号】（通配符+#{}+${}）
灵活的小胖子
08-17 9714
背景：别人写的代码，由于数据库添加了一个is_delete（0：未删除；1：已删除）字段用来做假删除，大家都知道，如果说一条数据删除了，那么肯定是不能查出来的了，因此，我的任务就是修改由于添加一个字段而引发的灾难。 问题： 我们的sql: select i.id, i.project_id, i.sets, i.name, i.length, i.likes, i.url, i.type, p.project_name from
sql #和$的区别
张铎
06-13 3839
select id,name, email,age from student where id=#{studentId} # 的结果： select id,name, email,age from student where id=? select id,name, email,age from student where id=${studentId} $ 的结果：select id,name, email,age from student where id=1001 Str..
SQL语句
silence_lu_的博客
08-01 479
区别： （1）#将传入的数据都当成一个字符串，会对自动传入的数据加一个双引号。如：order by #user_id#，如果传入的值是id，则解析成的sql为order by "id"。 （2）$将传入的数据直接显示生成在sql中。如：order by $user_id$，如果传入的值是id，则解析成的sql为order by id。 （3）#方式在很大程度上能够防止sql注入。 （4）$...
SQL中#与$的区别
weixin_47918681的博客
09-23 3700
一 、在这里用到了#{},使用#时： 1、用来传入参数，sql在解析的时候会加上” “,当成字符串来解析 ，如这里 role_id = “roleid”; 2、#{}能够很大程度上防止sql注入; 延伸： 1、用{roleId,jdbcType=INTEGER},那么sql在解析的时候值为roleId = roleId，执行时会报错; 2、${}方式无法防止sql注入; 3、$一般用入传入数据库对象，比如数据库表名; 4、能用#{}时尽量用#{}; 注意： mybaties排序时使用order by 动态参
SQL中#和--+的区别——2021-10-27
stumiss的博客
10-27 1456
SQL中#和–+的区别 https://www.cnblogs.com/impulse-/p/13184023.html
sql 中 ${} 和 #{}的区别
qq_34266804的博客
03-01 723
在sql中对于传入参数有两种写法，刚开始学的时候总是不知道是什么原因，故此写下此笔记作为解释： $将传入的数据直接显示生成在sql中，那么我们使用 ${}的时候 select * from user where name = ${name}; ${}在动态解析的时候，会将我们传入的参数当做String字符串填充到我们的语句中，就会变成下面的语句 select * from u...
sql中 # 和 $ 的区别
u013232219的博客
02-20 2083
三：sql中 # 和 $ 的区别： #可以防止sql注入 #是占位符，在DBMS才进行替换，在预编译时使用?占位，能防止sql注入； deletefromuserwherename = ?; 而传入的参数将会经过PreparedStatement方法的强制类型检查和安全检查等处理，最后作为一个合法的字符串传入。 在#{}预...
专业SQL优化培训资料
由于您提供的信息中只包含标题、描述和标签，没有具体提供压缩包子文件的文件名称列表中的详细内容，我将基于标题、描述和标签的信息，从SQL优化的角度出发，为您详细阐述相关知识点。 ### SQL优化概述 SQL优化是...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值