您的位置: 首页  >  文章  >  你需要掌握的 mysql 性能优化的一些要点

你需要掌握的 mysql 性能优化的一些要点

分类: 文章 • 2023-12-28 11:06:28

缓存优化

大多数的MySQL服务器都开启了查询缓存，当有很多相同的查询被执行了多次的时候，这些查询结果会被放到一个缓存中，这样，后续的相同的查询就不用操作表而直接访问缓存结果了

// 如果在sql语句中直接使用一些动态的函数查询缓存不开启
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
 
// 开启查询缓存
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

如果不想使用缓存，可以在每次查询时直接加参数SQL_NO_CACHE来禁止使用缓存

SQL_NO_CACHE解释如下：

  • 对当前query不使用数据库已有缓存来查询，则当前query花费时间会多点
  • 对当前query的产生的结果集不缓存至系统query cache里，则下次相同query花费时间会多点
select SQL_NO_CACHE picname, smallimg from pics where user_id=17853;

IN和EXISTS性能对比

EXISTS用法

exists对外表用loop逐条查询，每次查询都会查看exists的条件语句，当exists里的条件语句能够返回记录行时(无论记录行是的多少，只要能返回))，条件就为真，返回当前loop到的这条记录，反之如果exists里的条件语句不能返回记录行，则当前loop到的这条记录被丢弃，exists的条件就像一个bool条件，当能返回结果集则为true，不能返回结果集则为 false。

//每查一条记录都会判断后面子查询返回的数据是否存在
SELECT
	SQL_NO_CACHE *
FROM
	new_component
WHERE
	 EXISTS (SELECT new_dashboard.id FROM new_dashboard where new_dashboard.id = new_component.dashboard_id);
IN的用法

in查询相当于多个or条件的叠加

SELECT
	SQL_NO_CACHE *
FROM
	new_component
WHERE
	new_component.dashboard_id IN (SELECT id FROM new_dashboard);
两者性能对比

考虑下面两条sql：

（1）select * from A where exists (select * from B where B.id = A.id);

（2）select * from A where A.id in (select id from B);
  • 如果存在索引，使用 exists 函数查询时，不需要查询整张表，只需要查询索引即可
  • 如果使用 exists 查询，那么只需要查到一行满足条件即可停止
  • in 查询会生成临时表，而 exists 查询不会
  • 当A表和B表大小差不多时，且存在索引时，使用exists查询
  • 无索引的情况下，当A表小，B表大时，第一条sql的性能高
  • 无索引的情况下，当B表小，A表大，第二条sql的性能高

or, in, union all， union性能对比

-- (1)in操作
select SQL_NO_CACHE * from new_component where type in ('comment', 'rect', 'indicator');
-- (2)or操作
select SQL_NO_CACHE * from new_component where type = 'comment' or type = 'rect' or type = 'indicator';

-- (3)union all操作
select SQL_NO_CACHE * from new_component where type = 'comment'
UNION all
select  * from new_component where type = 'rect'
UNION all
select  * from new_component where type = 'indicator'
UNION all
select  * from new_component where type = 'indicator';

-- (4)union操作
select SQL_NO_CACHE * from new_component where type = 'comment'
UNION
select  * from new_component where type = 'rect'
UNION
select  * from new_component where type = 'indicator'
UNION
select  * from new_component where type = 'indicator';
  • 不要迷信union all就比 or及in 快，要结合实际情况分析到底使用哪种情况;
  • 对于复杂的索引列，尤其是要计算的时候，最好使用union all，因复杂的查询【包含运算等】将使or、in放弃索引而全表扫描，除非你能确定or、in会使用索引；
  • 对于只有非索引字段来说你就用or或in，因为非索引字段本来要全表扫描而union all只成倍增加表扫描的次数；
  • union操作会比union all操作耗时,因为union操作在合并以后，还要作去重操作，如果几个需要select语句本来就已知是去重的，那么就直接使用union all，否则使用union

尽量避免排序使用，在排序时使用索引

  • 会使用到排序的关键词有：group by/order by/sum/avg/max/min/distinct/union/rank 等
  • 能使用union all的地方不要使用union，因为union需要排序
  • 在聚合函数中尽量使用索引，这个时候排序只需要扫描索引即可，不需要进行全表扫描
  • 能写在 where 条件中判断不要写在 having 子句中，因为 group by 会对数据进行排序，如果事先排除掉一些数据，会减少排序量，还有就是聚合后的视图可能索引条件已经丢失
  • group by/order by 的列使用索引，可以实现高速排序
  • 参与计算后或者使用函数后，索引会失效，比如 where col * 3 < 15 不会使用索引，而 col < 15/3会使用索引
  • IS NULL 或者 IS NOT NULL 查询会使索引失效
  • <>/!=/ NOT IN 会使索引失效
  • 联合索引必须有顺序要求
  • like谓词时只有头部一致才能使用索引 where col like “abc%”
  • 默认类型转换不仅增加开销，还会使索引失效，比如 col 是 vchar 类型，那么 where col = ‘10’ 会使用索引，而 where col = 10 不会使用索引

减少使用中间表

  • having 子句和 group by 子句一起使用时比先 group by 成中间表再执行 where 要快
  • 合理使用视图，视图里面使用聚合函数，可能会带来巨大的性能消耗，视图有两种创建算法，可以在创建时指定
# 1. merge 算法会把视图中的sql合并到查询的sql中，和C语言中的宏展开有点类似，这个时候和普通查询没什么区别，但是不是任何时候都可以使用merge算法的，有些时候包含了聚合函数/group by/having就不能使用merge算法
# 2. temptable 算法，先执行视图定义，将其结果保存在临时表里，后续操作都以这个临时表为准，此时会造成丢失索引，并且存在临时表中间态，性能开销较大

使用索引优化性能

  • 索引过多会影响数据库写性能。索引不够查询会慢；
  • 数据的变更（增删改）都需要维护索引，因此更多的索引意味着更多的维护成本，更多的空间，所以索引不是越多越好；
  • 对于like查询，”xxxx%” 是可以用到索引的
  • 联合索引的最左匹配规则，如果有一个三列索引(col1,col2,col3)，则已经对(col1)、(col1,col2)、(col1,col2,col3)上建立了索引，而(col2, col3)类似这样的查询不会有索引
  • 一般来说，列的值唯一性太小（如性别，类型什么的），不适合建索引（怎样叫太小？一半说来，同值的数据超过表的百分之15，那就没必要建索引了）
  • 太长的列，可以选择只建立部分索引，（如：只取前十位做索引）
  • 更新非常频繁的数据不适宜建索引（怎样叫非常？意会）
  • 一次查询只能用一个索引
  • 如果select只选择索引字段，会走index查询，提高效率，尽量不要使用select *查询
  • 在Join表的时候使用相关联的列，并将其索引
  • 索引匹配到范围查询时，就会终止索引，所以在联合索引时，最好把需要精确查询的字段放在左边，而范围查询的字段放在右边
  • 如果计划在列上建索引的话，就应该尽量避免设计成可为NULL的列

show processlist 查找需要优化的sql

show processlist 显示用户正在运行的线程，需要注意的是，除了 root 用户能看到所有正在运行的线程外，其他用户都只能看到自己正在运行的线程，看不到其它用户正在运行的线程。除非单独个这个用户赋予 PROCESS 权限。

当遇到 sql 查询超时或者慢查询时，我们可以使用 show processlist 命令来查询是哪些sql查询影响到了我们的业务的正常运行，这样我们可以针对特定的慢查询sql做优化。show processlist 显示的信息都是来自MySQL系统库 information_schema 中的 processlist 表。所以下面两条语句返回的结果一样：

select * from information_schema.processlist;
show processlist;

执行 show processlist 命令返回的结果如下图：
你需要掌握的 mysql 性能优化的一些要点
我们解释一下每个字段的含义：

  • Id: 就是这个线程的唯一标识，当我们发现这个线程有问题的时候，可以通过 kill 命令，加上这个Id值将这个线程杀掉，前面我们说了show processlist 显示的信息时来自information_schema.processlist 表，所以这个Id就是这个表的主键。
  • User: 就是指启动这个线程的用户。
  • Host: 记录了发送请求的客户端的 IP 和 端口号。通过这些信息在排查问题的时候，我们可以定位到是哪个客户端的哪个进程发送的请求。
  • DB: 当前执行的命令是在哪一个数据库上，如果没有指定数据库，则该值为 NULL 。
  • Command: 是指此刻该线程正在执行的命令：
- Binlog Dump: 主节点正在将二进制日志 ，同步到从节点
- Change User: 正在执行一个 change-user 的操作
- Close Stmt: 正在关闭一个Prepared Statement 对象
- Connect: 一个从节点连上了主节点
- Connect Out: 一个从节点正在连主节点
- Create DB: 正在执行一个create-database 的操作
- Daemon: 服务器内部线程，而不是来自客户端的链接
- Debug: 线程正在生成调试信息
- Delayed Insert: 该线程是一个延迟插入的处理程序
- Drop DB: 正在执行一个 drop-database 的操作
- Execute: 正在执行一个 Prepared Statement
- Fetch: 正在从Prepared Statement 中获取执行结果
- Field List: 正在获取表的列信息
- Init DB: 该线程正在选取一个默认的数据库
- Kill : 正在执行 kill 语句，杀死指定线程
- Long Data: 正在从Prepared Statement 中检索 long data
- Ping: 正在处理 server-ping 的请求
- Prepare: 该线程正在准备一个 Prepared Statement
- ProcessList: 该线程正在生成服务器线程相关信息
- Query: 该线程正在执行一个语句
- Quit: 该线程正在退出
- Refresh：该线程正在刷表，日志或缓存；或者在重置状态变量，或者在复制服务器信息
- Register Slave： 正在注册从节点
- Reset Stmt: 正在重置 prepared statement
- Set Option: 正在设置或重置客户端的 statement-execution 选项
- Shutdown: 正在关闭服务器
- Sleep: 正在等待客户端向它发送执行语句
- Statistics: 该线程正在生成 server-status 信息
- Table Dump: 正在发送表的内容到从服务器
  • Time: 表示该线程处于当前状态的时间。
  • State: 线程的状态，和 Command 对应。
  • Info: 一般记录的是线程执行的语句。默认只显示前100个字符，也就是你看到的语句可能是截断了的，要看全部信息，需要使用 show full processlist

经常用到的查询sql：

-- 按客户端 IP 分组，看哪个客户端的链接数最多
select client_ip,count(client_ip) as client_num from (select substring_index(host,':' ,1) as client_ip from processlist ) as connect_info group by client_ip order by client_num desc;

-- 查看正在执行的线程，并按 Time 倒排序，看看有没有执行时间特别长的线程
select * from information_schema.processlist where Command != 'Sleep' order by

-- 找出所有执行时间超过 5 分钟的线程，拼凑出 kill 语句，方便后面查杀
select concat('kill ', id, ';') from information_schema.processlist where Command != 'Sleep' and Time > 300 order by Time desc;

使用 mysql 时要注意的细节

  • 对于事务的操作，检查在事务中是否存在RPC调用、HTTP调用、消息队列操作、缓存、循环查询等耗时的操作，这个操作应该移到事务之外，因为这些操作会增加事务的处理时间，使sql查询不稳定，理想的情况是事务内只处理数据库操作；
  • 可以针对sql查询提供报警功能，如果某个sql查询时间大于某个阈值时，应该立即报警；
  • 读写分离，提高并发效率，主从复制，提高高可用性；
  • 当只要一行数据时使用LIMIT 1,这样检索到一条数据后，就停止搜索了；
  • 根据数据值的范围，选择正确的数据类型进行存储；
  • 在定义字段类型时，如果一些字符串范围固定，使用enum类型而不是varchar类型，提高性能
  • 使用 explain 函数分析查询性能进行优化

参考文献

相关推荐