从最近MySQL的优化工作想到的

本文针对MySQL存储过程中常见的问题进行了分析,特别是SQL查询优化技巧,如避免使用派生表提高group by操作效率,正确处理NULL值,以及通过内连接代替子查询来提升性能。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

    最近决定将以前同事写的存储过程查看一遍,寻找一些代码上写的不太好的地方,争取进行修改以后让这些过程达到一个很好的运行速度。下面是遇到的最多的几个问题。

    我遇到了这样的一个SQL:

    

select name, count(*) from (select name from table_1) a group by a.name;

    MySQL的执行计划对于这种派生表的解释非常的不友好,但是能直观的感觉到的是,这个SQL执行速度特别的慢。查看这个表table_1发现,name字段是有索引的。审视这段代码,可以推断出当时程序员的想法,应该是想让数据库扫描更小的结果集,因为select *是很不好的习惯。不过他应该忽略了一个MySQL的很重要的特点就是索引。MySQL的索引是个很有意思的东西,是我从Oracle转过来感觉最好玩的东西,好玩的地方就在于,可以优化group by。当我把这个SQL改成如下SQL以后:

    

select name, count(*) from table_1 group by name;

    这样一来,这段SQL的执行速度就非常的快了,extra列明确的显示了using index,索引覆盖查询,速度杠杠的。

    其实这种错误应该是程序员常犯的,因为程序员对Java等代码超级熟悉,但是对于SQL,基本上都是大学的时候学习的SQL,用SQLServer练出来的,基本上没有对数据库进行非常深入的研究,其实每种数据库中,同一条SQL的执行计划都是不尽相同的,这也就是企业有一个专业的DBA的一个作用。

    下面,就是一个让人很头疼的错误:

    

select name, userid from table_1 where name = null;

     不管是MySQL还是Oracle,对这种SQL的写法的规范都是where name is (not) null。null这个值,在不管什么数据库里都是一个让人(包括程序员和DBA)都很头疼的东西。我对MySQL的理解还不够深入,但是根据某一本《Oracle DBA手记》中记载,Oracle中每种数据类型的null都代表了不一样的意义。

     做了下面一个实验:

     

       可以看出来,不管是“= null”还是“<> null”,得到的值其实都是不确定,也就是null。因此,必须要写成is (not) null。在《剑破冰山》这本书里也有对Oracle的null值的详细介绍。

       总结一下最近的工作,我研究了小半年时间的MySQL,发现这个开源的数据库并不像我过去认为的那样,就是一个互联网数据库。这个数据库在面向OLAP复杂计算的方面确实和Oracle,DB2等商用数据库之间有不小的差距,不过在MariaDB这个分支中,这部分有了不小的进步,相信后面的MySQL版本中也会越来越好。其实这个数据库最让我感兴趣的不是开源,因为我确实看不懂那么长的源代码,我的C语言水平就是大学毕业水平。这个数据库最让我感兴趣(起码现在来讲)是它的索引,它的索引和Oracle有很大的不同,尤其是InnoDB的表整个就是用索引组织起来的,在简单的查询的时候,一个索引覆盖查询就可以无敌于天下了,在group by和order by的时候,如果是索引字段,效率会相当的高。

      其实我还想说的就是,一个团队里,如果涉及到大量存储过程的编写,一定要有一个专业的DBA人员参与其中。SQL是一个标准,横跨了所有的关系型数据库,但是每一种关系型数据库对SQL的实现又不尽相同,因此同样的一段SQL,放到不同的数据库上执行,效率上就会千差万别。而SQL又非常容易用人最习惯最简单的思维写出来,比如搜索一个订单表里美国员工生成的订单信息,SQL有可能是这样的:

      

select * from orders t1
 where t1.employee_id in (select employee_id from employee t2 
 where t2.nation = 'USA');

    如果是Oracle这样的商业数据库,这个SQL的执行效率可能会比较好,但是应该不如用exists的SQL。但是当这段SQL在MySQL中执行的时候,效率就很差了,因为很多人都知道,MySQL的子查询效率实在是不敢恭维。这段代码会被改为相关子查询,而且随着数据量的增长,执行时间会越来越长。这段代码如果改成下面的SQL,效果会更好:

    

select t1.*
  from orders t1
inner join employee t2
    on t1.employee_id = t2.employee_id
where t2.nation = 'USA';

    如果表上有索引,执行速度快极了。

    写SQL,还是要首先研究这个数据库的原理,然后慎而又慎的写。

<think>好的,我现在需要帮助用户了解如何优化MySQL中存在碎片的。首先,我得回忆一下MySQL碎片产生的原因。碎片通常是由于频繁的插入、更新和删除操作导致的,这些操作会让数据在物理存储上变得不连续,影响查询性能。用户提到“optimize fragmented tables techniques”,也就是优化碎片的技术,我需要列出具体的方法。 首先,想到的是OPTIMIZE TABLE命令。这是MySQL官方推荐的整理碎片的方法,特别是对于使用InnoDB和MyISAM存储引擎的。不过需要注意,执行这个命令时会锁定,可能会影响在线业务,所以最好在低峰期进行。另外,对于InnoDB,OPTIMIZE TABLE实际上会重建,并释放未使用的空间,这需要确认用户的MySQL版本,不同版本可能有不同的行为。 接下来,可以考虑使用ALTER TABLE命令。例如,ALTER TABLE table_name ENGINE=InnoDB; 这种方式也可以重建,从而消除碎片。这种方法同样会锁定,但可能在某些情况下比OPTIMIZE TABLE更高效,特别是当用户需要同时修改的其他属性时。 然后,定期维护策略很重要。用户可能需要设置定期任务,比如每周或每月在系统负载低的时候运行优化命令。此外,监控碎片的工具也是关键,比如通过查询information_schema.TABLES中的DATA_FREE字段来判断碎片程度。DATA_FREE显示未使用的空间,如果这个值较大,说明碎片较多,需要进行优化。 另外,配置InnoDB的innodb_file_per_table参数也很重要。启用这个参数后,每个InnoDB会有独立的空间文件,这样在删除或索引时,空间可以更有效地释放回操作系统,减少碎片。需要确认用户是否已经启用了这个设置,如果没有,建议启用。 对于MyISAM,除了OPTIMIZE TABLE,还可以使用myisamchk工具,但需要注意的是,使用这个工具时需要停止MySQL服务或者锁定,否则可能导致数据不一致。不过现在MyISAM的使用已经不太常见,大部分用户可能在使用InnoDB,所以可能需要重点讲解InnoDB的优化方法。 此外,应用程序的设计优化也能减少碎片产生。比如避免频繁的随机插入,使用批量插入代替单条插入,合理设计索引,避免不必要的更新操作等。这些预防措施可以减少碎片的产生,从而降低需要优化的频率。 还需要考虑MySQL版本的不同带来的差异。例如,在MySQL 5.6及以上版本中,InnoDB的在线DDL功能可能允许某些ALTER操作不锁定,或者减少锁定的时间,这对高并发环境下的优化有帮助。需要提醒用户根据他们的版本来选择合适的操作方法。 最后,要提醒用户在进行任何优化操作前备份数据,避免操作失误导致数据丢失。同时,监控优化后的性能变化,确保优化措施有效。如果用户的非常大,OPTIMIZE TABLE可能会耗时较长,影响服务可用性,这时候可以考虑分或者使用在线工具来进行优化。</think>### MySQL碎片优化方法 当MySQL因频繁增删改操作产生碎片时,可通过以下技术进行优化: #### 一、直接优化命令 1. **$OPTIMIZE TABLE$ 命令** 执行`OPTIMIZE TABLE table_name;`可重建并整理碎片,适用于InnoDB/MyISAM引擎[^1]。该操作会锁定,建议在业务低峰期执行。 ```sql -- 示例:优化订单 OPTIMIZE TABLE orders; ``` 2. **$ALTER TABLE$ 重建** 通过`ALTER TABLE table_name ENGINE=InnoDB;`强制重建结构,效果等同于优化命令[^3]。 ```sql -- 示例:重建用户 ALTER TABLE users ENGINE=InnoDB; ``` #### 二、配置预防策略 1. **启用独立空间** 设置`innodb_file_per_table=ON`,使每个InnoDB拥有独立文件,便于空间回收[^1]: ```sql SET GLOBAL innodb_file_per_table=1; ``` 2. **定期维护计划** 创建定时任务(如每周日凌晨3点执行): ```sql -- 使用事件调度器 CREATE EVENT optimize_tables ON SCHEDULE EVERY 1 WEEK STARTS '2024-01-01 03:00:00' DO BEGIN OPTIMIZE TABLE critical_table1, critical_table2; END ``` #### 三、碎片监控方法 通过系统检测碎片程度: ```sql SELECT TABLE_NAME, ENGINE, DATA_LENGTH, INDEX_LENGTH, DATA_FREE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_database' AND DATA_FREE > 1024*1024*100; -- 显示碎片超过100MB的 ``` #### 四、应用层优化建议 - 避免`UUID()`等随机值作为主键 - 使用批量插入代替单条插入 - 定期清理历史数据
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值