做后端运维、开发的朋友都知道，MySQL数据库就像是咱们系统的“心脏”，不管是中小型项目还是大型平台，几乎都离不开它。但日常工作中，咱们最头疼的就是MySQL出问题——运维时的各种小故障、高并发下的死锁、慢查询拖垮整个系统，这些问题一旦出现，轻则影响用户体验，重则导致系统瘫痪，损失可不小。今天就结合我多年的实战经验，用最接地气的口语，跟大家好好聊聊MySQL运维、死锁排查和查询语句优化的干货，全程实战，看完就能用，新手也能轻松上手。
先跟大家说个大实话：MySQL运维不是“瞎忙活”，死锁排查不是“碰运气”，查询优化也不是“凭感觉”，这三件事都有固定的思路和实操方法，只要掌握了核心逻辑，就能少踩坑、提高效率，甚至能提前规避大部分问题。咱们先从最基础的MySQL日常运维说起，这是根基，根基打牢了，后面的死锁、慢查询问题也能少出现。
一、MySQL日常运维实战：筑牢数据库“根基”
很多朋友觉得MySQL运维就是“启动服务、备份数据”，其实不然。真正的运维，是“防患于未然”，既要保证数据库稳定运行，也要在出现小问题时能快速解决，还要做好备份，防止数据丢失。咱们分几个高频场景，一步步说实操方法，全是日常工作中用得上的。
1. 日常监控：盯紧这几个核心指标，提前发现异常
运维的核心就是“监控”，就像医生给病人测心率、血压一样，咱们要实时盯紧MySQL的关键指标，一旦出现异常，就能及时介入，避免小问题变大故障。这里不用搞复杂的监控工具（新手可以先从命令行入手），重点盯紧5个指标，简单好操作。
第一个是连接数，这是最容易出问题的地方。很多时候系统报“连接失败”，不是数据库崩了，而是连接数打满了。咱们可以用这个命令查看当前连接数：show global status like 'Threads_connected'; ，再用show variables like 'max_connections'; 查看MySQL允许的最大连接数。正常情况下，连接数应该控制在最大连接数的70%以内，如果经常接近上限，要么调高max_connections（临时调整：set global max_connections=1000; 永久调整需要修改my.cnf配置文件），要么排查哪些连接是闲置的，用kill 线程ID; 杀掉无用连接。这里提醒一句，max_connections也不能调太高，不然会占用过多服务器内存，根据自己的服务器配置来，一般4核8G的服务器，设置1000-1500就足够了。
第二个是CPU和内存占用，MySQL是耗资源的主，尤其是高并发场景下，CPU和内存很容易飙满。咱们可以用top命令查看服务器CPU占用，看mysqld进程的CPU占比，如果长期超过70%，就要排查是不是有慢查询、索引失效等问题；内存方面，重点关注innodb_buffer_pool_size（InnoDB的缓冲池大小），这个参数建议设置为服务器物理内存的50%-70%，比如8G内存，设置为4G-5G，这样能减少磁盘IO，提高查询速度。查看缓冲池使用情况的命令：show global status like 'Innodb_buffer_pool_%';
第三个是磁盘IO和磁盘空间，MySQL的数据都存在磁盘上，磁盘满了或者IO过高，都会导致数据库卡顿。用df -h命令查看磁盘空间，重点关注存放MySQL数据的目录（一般是/var/lib/mysql），如果剩余空间少于10%，就要及时清理无用数据、日志文件；用iostat命令查看磁盘IO，要是%util（IO使用率）长期接近100%，说明磁盘IO压力太大，可以考虑更换更快的磁盘（比如NVMe盘），或者优化慢查询，减少磁盘读写。
第四个是慢查询，慢查询是拖垮MySQL的“元凶”之一，咱们要开启慢查询日志，及时发现并优化。开启方法很简单：修改my.cnf配置文件，添加这两行：slow_query_log=1（开启慢查询日志），slow_query_log_file=/var/lib/mysql/slow.log（慢查询日志存放路径），long_query_time=0.5（查询时间超过0.5秒的视为慢查询），然后重启MySQL生效。之后定期查看慢查询日志，用mysqldumpslow工具分析，比如mysqldumpslow -s t /var/lib/mysql/slow.log，就能看到最耗时的查询语句，针对性优化。
第五个是主从同步状态（如果做了主从架构），很多项目为了提高可用性，都会做主从复制，主库写数据，从库读数据，一旦主从同步延迟过高，就会导致从库数据不一致，影响业务。查看主从同步状态的命令：show slave status\G，重点看Slave_IO_Running和Slave_SQL_Running两个字段，只要有一个是No，就说明主从同步失败，需要排查原因（常见原因：网络中断、从库SQL执行失败、主库binlog文件丢失）；还要看Seconds_Behind_Master字段，这个字段表示从库比主库延迟的秒数，正常情况下应该是0，要是超过30秒，就要排查是不是有大事务、慢查询导致的同步延迟。
2. 备份与恢复：数据安全的“最后一道防线”
做MySQL运维，备份是重中之重，千万不能偷懒！不管你的数据库多稳定，都有可能出现意外（比如服务器宕机、误操作删除数据、病毒攻击），一旦数据丢失，没有备份，那就只能“哭晕在键盘前”。这里给大家分享两种常用的备份方法，新手优先用第一种，简单易操作；第二种适合大型数据库，效率更高。
第一种：mysqldump备份（适合中小型数据库，数据量小于10G）。这是MySQL自带的备份工具，不需要额外安装，命令简单，备份的是SQL文件，恢复也方便。备份命令：mysqldump -u root -p --all-databases > /backup/mysql_full_backup_$(date +%Y%m%d).sql，输入密码后，就会把所有数据库备份到指定路径，文件名带有日期，方便区分。这里提醒几点：一是备份时最好加上--lock-tables=0（InnoDB引擎），避免备份时锁表，影响业务；二是定期备份，比如每天凌晨备份一次，每周做一次全量备份，每天做一次增量备份；三是备份文件要存放在不同的服务器上，避免本地服务器宕机，备份文件也丢失。
恢复命令也很简单：mysql -u root -p < /backup/mysql_full_backup_20260417.sql，输入密码后，就能将备份的数据恢复到数据库中。如果只需要恢复某个数据库，备份时可以指定数据库名称，比如mysqldump -u root -p test_db > /backup/test_db_backup.sql，恢复时也是指定数据库：mysql -u root -p test_db < /backup/test_db_backup.sql。
第二种：xtrabackup备份（适合大型数据库，数据量大于10G）。mysqldump备份时会锁表（MyISAM引擎），而且备份速度慢，对于大型数据库来说，推荐用xtrabackup，备份速度快，不锁表，支持增量备份。安装方法就不详细说了，网上有很多教程，重点说备份命令：全量备份命令：innobackupex --user=root --password=123456 /backup/xtrabackup_full_$(date +%Y%m%d)，增量备份命令：innobackupex --user=root --password=123456 --incremental /backup/xtrabackup_incr_$(date +%Y%m%d) --incremental-basedir=/backup/xtrabackup_full_20260417（基于上一次全量备份）。恢复时需要先准备备份文件，再恢复，具体步骤可以参考xtrabackup的官方文档，虽然比mysqldump复杂一点，但效率高，适合大型项目。
另外，备份之后一定要测试恢复，很多人备份完就不管了，等到真正需要恢复时，才发现备份文件损坏、无法恢复，那就晚了。建议每周抽时间测试一次恢复，确保备份文件有效。
3. 权限管理：避免“权限滥用”导致的安全风险
很多运维新手图方便，给所有用户都分配root权限，这是非常危险的！一旦账号泄露，攻击者就能随意修改、删除数据，后果不堪设想。正确的权限管理，应该是“最小权限原则”，也就是给用户分配刚好能完成工作的权限，不多分配一分。
比如，开发人员只需要查询、插入、更新数据，那就给他们分配SELECT、INSERT、UPDATE权限，不要给DELETE、DROP权限；运维人员需要备份数据，那就给他们LOCK TABLES、SELECT权限，不需要给修改数据的权限；只有管理员才能拥有root权限，负责数据库的配置、用户管理等操作。
创建用户并分配权限的命令：create user 'dev_user'@'%' identified by 'Dev@123456';（创建用户dev_user，允许远程登录，密码Dev@123456），grant SELECT,INSERT,UPDATE on test_db.* to 'dev_user'@'%';（给dev_user分配test_db数据库的查询、插入、更新权限），flush privileges;（刷新权限，立即生效）。如果需要回收权限，用revoke命令：revoke DELETE on test_db.* from 'dev_user'@'%';，同样需要刷新权限。
另外，要定期清理无用的用户，比如离职员工的账号，及时删除或锁定，避免安全隐患；密码也要定期更换，设置复杂密码，不要用简单的123456、admin等，防止暴力破解。
4. 常见运维故障处理：快速解决“突发问题”
不管运维做得多好，难免会出现一些突发故障，这里给大家总结几个最常见的故障，以及快速解决的方法，帮大家节省排查时间。
故障1：MySQL启动失败。常见原因：my.cnf配置文件错误、数据目录权限不足、端口被占用。解决方法：先查看错误日志（/var/lib/mysql/主机名.err），根据错误信息排查，比如配置文件错误，就修改配置文件；权限不足，就执行chown -R mysql:mysql /var/lib/mysql，修改数据目录权限；端口被占用，就用netstat -tuln | grep 3306，找到占用3306端口的进程，kill掉，再重启MySQL。
故障2：连接MySQL报错“Access denied for user 'root'@'localhost'”。常见原因：密码错误、用户权限不足、root用户被锁定。解决方法：如果是密码错误，就重置密码（MySQL8.0重置密码的方法：systemctl stop mysqld，mysqld_safe --skip-grant-tables &，然后登录MySQL，alter user 'root'@'localhost' identified by '新密码';）；如果是权限不足，就给用户分配对应的权限；如果是用户被锁定，就执行unlock tables; 解锁。
故障3：磁盘写满，MySQL无法写入数据。解决方法：先用df -h查看磁盘空间，找到占用空间大的文件，比如慢查询日志、binlog日志，删除无用的日志文件（注意：binlog日志不要随意删除，要是做了主从同步，删除binlog可能导致主从同步失败，建议用purge binary logs to 'binlog.000123'; 按文件名删除，或者设置binlog过期时间，在my.cnf中添加expire_logs_days=7，让MySQL自动删除7天前的binlog日志）；如果是数据文件太大，就清理无用数据，或者扩容磁盘。
二、MySQL死锁排查实战：快速破解“锁竞争”难题
聊完运维，咱们再来说说最让人头疼的死锁问题。很多朋友在高并发场景下，都会遇到“Deadlock found when trying to get lock”的报错，这就是死锁。简单来说，死锁就是两个或多个事务，互相拿着对方需要的锁，谁也不让谁，导致所有事务都卡住，无法继续执行，就像两辆车在狭窄的路口互不相让，谁也走不了。
很多人遇到死锁，第一反应就是“重启MySQL”，虽然能临时解决问题，但根本原因没找到，过不了多久还会出现。其实死锁排查有固定的步骤，只要按照步骤来，就能快速找到死锁原因，彻底解决问题。咱们从“死锁怎么发生”“怎么排查死锁”“怎么解决和预防死锁”三个方面，结合实战案例，一步步讲清楚。
1. 死锁的常见场景：知道“怎么发生”，才能“提前规避”
死锁不是随机发生的，有固定的场景，掌握这些场景，就能提前规避大部分死锁问题。下面这3个场景，是工作中最常见的，大家一定要记好。
场景1：双事务交叉更新（最常见）。比如有两张表，订单表（order）和库存表（inventory），事务A先更新订单表，再更新库存表；事务B先更新库存表，再更新订单表，这样就很容易出现死锁。举个具体的例子：
事务A：begin; update order set status=1 where id=1; update inventory set stock=stock-1 where product_id=1; commit;
事务B：begin; update inventory set stock=stock-1 where product_id=1; update order set status=1 where id=1; commit;
当两个事务同时执行时，事务A拿到了订单表id=1的行锁，想要拿库存表product_id=1的行锁；事务B拿到了库存表product_id=1的行锁，想要拿订单表id=1的行锁，双方互相等待，就形成了死锁。
场景2：范围查询导致的间隙锁死锁。InnoDB引擎在执行范围查询（比如between、>、<）时，会添加间隙锁，锁住条件范围内的所有记录，包括不存在的记录，多个事务的范围条件重叠时，就容易出现死锁。比如用户积分表（user_points），事务A更新user_id在2-6之间的积分，事务B更新user_id在4-8之间的积分，两者的范围有重叠，就可能出现死锁。
场景3：唯一键冲突导致的死锁。并发执行“INSERT ... ON DUPLICATE KEY UPDATE”语句时，如果两个事务插入相同的唯一键值，会先尝试插入（加插入意向锁），检测到冲突后，会转为更新锁，互相等待，形成死锁。比如用户账号表（user_account），mobile字段是唯一键，两个事务同时插入mobile=13800138000的记录，就可能出现死锁。
2. 死锁排查步骤：3步找到“罪魁祸首”
遇到死锁报错，不要慌，按照下面3步来，就能快速找到死锁原因，定位到具体的SQL语句。
第一步：查看最近一次死锁详情。这是最关键的一步，用命令：show engine innodb status\G，执行后，会输出很多信息，咱们重点找“LATEST DETECTED DEADLOCK”这部分，里面会详细显示死锁的相关信息，包括：发生死锁的两个事务ID、各自执行的SQL语句、持有哪些锁、等待哪些锁、哪个事务被回滚了。
举个例子，假设输出信息中有这样一段：
LATEST DETECTED DEADLOCK
140673348012032
*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 10, OS thread handle 140673347909632, query id 123 localhost root updating
update order set status=1 where id=1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 123 page no 4 n bits 72 index PRIMARY of table `test`.`order` trx id 12345 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 12346, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 11, OS thread handle 140673348012032, query id 124 localhost root updating
update inventory set stock=stock-1 where product_id=1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 123 page no 4 n bits 72 index PRIMARY of table `test`.`order` trx id 12346 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 124 page no 5 n bits 72 index PRIMARY of table `test`.`inventory` trx id 12346 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (1)
从这段信息中，咱们能看出：事务12345（线程10）执行update order set status=1 where id=1，等待订单表id=1的行锁；事务12346（线程11）持有订单表id=1的行锁，执行update inventory set stock=stock-1 where product_id=1，等待库存表product_id=1的行锁；最后MySQL回滚了事务12345，解决死锁。这样就能快速定位到是两个事务交叉更新导致的死锁。
第二步：查看当前锁等待情况。如果死锁正在发生，上面的命令可能看不到最新的死锁信息，这时候可以用下面的SQL语句，实时监控锁等待情况，找到阻塞的源头：
SELECT r.trx_id AS waiting_trx_id, r.trx_mysql_thread_id AS waiting_thread, r.trx_query AS waiting_query, b.trx_id AS blocking_trx_id, b.trx_mysql_thread_id AS blocking_thread, b.trx_query AS blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;
执行后，会显示等待锁的事务、阻塞事务的ID、对应的SQL语句和线程ID，找到blocking_thread（阻塞线程ID），用kill 线程ID; 就能临时解除死锁，让系统恢复正常。
第三步：分析死锁原因，定位根本问题。结合前两步的信息，分析死锁的场景，比如是交叉更新、间隙锁还是唯一键冲突，然后找到对应的SQL语句，分析为什么会出现锁竞争，比如是不是事务顺序不一致、是不是没有用索引导致锁升级、是不是事务太大导致锁持有时间过长。
3. 死锁解决与预防：彻底杜绝“重复踩坑”
找到死锁原因后，就要针对性解决，同时做好预防，避免以后再出现。下面结合前面的常见场景，给大家分享具体的解决方法和预防措施，全是实战干货。
针对场景1：双事务交叉更新。解决方法：统一资源访问顺序，所有事务都按照相同的顺序操作表或行记录。比如前面的订单表和库存表，约定所有事务都先更新订单表，再更新库存表，这样就不会出现交叉等待的情况。代码层面可以做统一封装，比如写一个公共方法，处理订单和库存的更新，确保所有业务都调用这个方法，避免顺序不一致。
如果业务无法统一顺序，也可以在应用层引入分布式锁（比如Redis、ZooKeeper），保证同一时间只有一个线程在处理某条关联数据，避免并发冲突。
针对场景2：范围查询导致的间隙锁死锁。解决方法：缩小锁粒度，避免大范围锁表。比如将范围更新改为单条更新，原来的“update user_points set points=points+10 where user_id between 2 and 6;”，改为逐条更新：update user_points set points=points+10 where user_id=2; update user_points set points=points+10 where user_id=3; ... ，并且按固定顺序更新（比如按user_id升序）；也可以用ORDER BY确保加锁顺序，避免间隙锁冲突。
另外，如果业务允许，可以将事务隔离级别从默认的REPEATABLE READ改为READ COMMITTED，这样可以减少间隙锁的使用，降低死锁概率（修改方法：set session transaction isolation level read committed; 临时生效，永久生效需要修改my.cnf配置文件）。
针对场景3：唯一键冲突导致的死锁。解决方法：先锁定，再操作。比如执行“INSERT ... ON DUPLICATE KEY UPDATE”之前，先用SELECT ... FOR UPDATE锁定对应的行，避免并发插入冲突。具体SQL如下：
begin;
select * from user_account where mobile='13800138000' for update;
if 存在该记录 then
update user_account set balance=balance+100 where mobile='13800138000';
else
insert into user_account (mobile, balance) values ('13800138000', 100);
end if;
commit;
也可以将唯一键冲突的业务逻辑异步化，通过消息队列串行处理，彻底避免并发冲突。
除了针对具体场景的解决方法，还有几个通用的预防措施，大家一定要落实：
1. 尽量用短事务：事务中不要执行远程调用、复杂计算等耗时操作，减少事务的执行时间，避免长时间持有锁，降低锁竞争的概率。比如一个事务，只做“更新数据”这一件事，不要在事务中调用其他服务、查询大量无关数据。
2. 优化索引：确保UPDATE、DELETE语句的WHERE条件使用索引，避免因为没有索引导致行锁升级为表锁，增大死锁概率。比如update order set status=1 where id=1，id是主键索引，会只锁一行；如果没有索引，就会锁整个订单表，很容易出现死锁。
3. 避免热点行更新：如果某一行数据被频繁更新（比如热门商品的库存），会导致大量事务竞争这一行的锁，容易出现死锁。可以通过分表、分库，或者将热点数据拆分，减少锁竞争。
4. 开启死锁日志：在my.cnf配置文件中添加innodb_print_all_deadlocks=1，这样所有死锁信息都会记录到error.log中，方便后续排查和分析，找到高频死锁场景，提前优化。
三、MySQL查询语句优化实战：告别“慢查询”，提升数据库性能
聊完死锁，咱们再来说说查询语句优化，这是日常工作中最频繁的操作，也是提升MySQL性能的关键。很多时候，系统卡顿、数据库压力大，不是因为服务器配置不够，而是因为查询语句写得太“烂”——没有用索引、全表扫描、冗余查询，这些都会导致查询速度变慢，拖垮整个系统。
查询优化的核心原则很简单：让MySQL少扫描数据，尽量用索引，减少磁盘IO和内存占用。下面结合实战案例，从“如何分析慢查询”“索引优化”“SQL语句优化”三个方面，给大家分享具体的优化方法，新手也能轻松上手，看完就能优化自己的SQL语句。
1. 第一步：用EXPLAIN分析慢查询，找到优化方向
优化SQL的前提，是知道SQL的执行计划——MySQL是怎么执行这条SQL的，有没有用索引，有没有全表扫描，扫描了多少行数据。这时候就需要用到EXPLAIN命令，这是MySQL优化的“神器”，用法很简单：在SELECT语句前面加EXPLAIN，执行后，MySQL会返回执行计划，不会真正执行查询。
比如，我们要优化“select * from user where name='张三'”，就执行“explain select * from user where name='张三';”，执行后会返回一张表，里面有很多字段，咱们重点看4个字段：type、key、rows、Extra，这4个字段能直接告诉我们SQL的执行情况，记住下面的口诀，新手也能快速判断：
看type：type表示MySQL用哪种方式找到数据，性能从好到坏排序：system > const > eq_ref > ref > range > index > ALL。咱们只需要记住：ALL是最坏的情况，代表全表扫描，必须优化；range、ref是正常情况，不错；const是最好的情况，代表主键/唯一索引，一次命中。
看key：key表示实际使用的索引，如果是NULL，说明没有用到任何索引，需要优化；如果显示索引名称，说明用到了对应的索引。比如key=PRIMARY，说明用到了主键索引；key=idx_user_name，说明用到了name字段的索引。
看rows：rows表示MySQL预估要扫描多少行数据才能找到结果，数字越小越快。如果大表的rows达到几十万、几百万，说明查询效率很低，需要优化索引。
看Extra：Extra表示MySQL做的额外操作，重点看三个：①Using filesort：坏信号，说明MySQL无法用索引排序，需要额外排序，是慢查询重灾区；②Using temporary：非常坏的信号，说明用到了临时表，通常是GROUP BY、ORDER BY没有建好索引；③Using index：好信号，说明用到了覆盖索引，直接从索引取数据，不用回表，效率很高。
举几个实战案例，帮大家理解：
案例1：没索引，全表扫描。执行“explain select * from user where name='张三';”，结果显示type=ALL，key=NULL，rows=10000，Extra=NULL，说明是全表扫描，需要给name字段建索引。
案例2：用主键索引，极快。执行“explain select * from user where id=100;”，结果显示type=const，key=PRIMARY，rows=1，Extra=NULL，说明用到了主键索引，一次命中，效率最高。
案例3：出现坏信号Using filesort。执行“explain select * from user order by age;”，结果显示Extra=Using filesort，说明没有给age字段建索引，MySQL需要额外排序，需要给age字段建索引。
通过EXPLAIN分析，咱们能快速找到慢查询的问题所在——是没有索引、索引失效，还是需要排序、临时表，然后针对性优化。
2. 第二步：索引优化，避免“索引失效”陷阱
索引是查询优化的核心，正确的索引能让查询速度提升几十倍、上百倍，但如果用错了，不仅起不到优化作用，还会影响插入、更新、删除的效率（因为索引需要维护）。下面给大家分享索引的核心优化原则，以及常见的索引失效场景，帮大家避开陷阱。
首先，索引的创建原则（新手必看）：
1. 按需创建索引：只给查询频率高的字段建索引，不要给每个字段都建索引。比如用户表的name、id、mobile字段，查询频率高，可以建索引；而gender、address字段，查询频率低，不需要建索引。
2. 联合索引遵循“最左前缀匹配原则”：如果创建了联合索引（a,b,c），那么查询时，必须从最左列a开始，才能用到索引；如果跳过a，直接查b、c，索引会失效。比如联合索引（name,age,position），查询“where age=30 and position='engineer'”，索引会失效；查询“where name='张三' and age=30”，能用到索引。
3. 避免索引列计算：在WHERE子句中，不要对索引列进行函数计算、加减乘除等操作，否则会导致索引失效。比如索引字段是birthday（DATE类型），查询“where YEAR(birthday)=1990”，会导致索引失效；应该改为“where birthday between '1990-01-01' and '1990-12-31'”。
4. 范围查询右列失效：联合索引中，如果某一列用了范围查询（>、<、between、in），那么该列右边的列，索引会失效。比如联合索引（dept_id,salary），查询“where dept_id=101 and salary>10000”，只有dept_id字段的索引生效，salary字段的索引失效；可以调整索引顺序，将范围列放在最右边，比如创建索引（salary,dept_id），再查询“where salary>10000 and dept_id=101”，就能用到索引。
5. 优先用覆盖索引：如果查询的字段，都在索引中，MySQL就不需要回表查询数据，效率很高。比如联合索引（name,age），查询“select name,age from user where name='张三'”，就能用到覆盖索引（Extra显示Using index）；如果查询“select * from user where name='张三'”，就需要回表查询其他字段，效率较低。
接下来，常见的索引失效场景（一定要避开）：
1. 模糊查询以%开头：比如“select * from user where name like '%张三'”，索引会失效；如果是“like '张三%'”，索引会生效。如果必须用“%张三%”，可以考虑用全文索引。
2. 隐式转换：索引字段是字符串类型，查询时用了数字，会导致索引失效。比如name字段是varchar类型，查询“where name=123”，会进行隐式转换，索引失效；应该改为“where name='123'”。
3. OR条件中有未建索引的字段：比如“select * from user where name='张三' or gender='男'”，如果gender字段没有建索引，那么整个查询会全表扫描，索引失效；要么给gender字段建索引，要么拆分查询。
4. NULL值判断：索引字段允许NULL值，查询“where name is null”，索引可能失效；可以将NULL值替换为默认值（比如空字符串），或者在创建索引时，指定NOT NULL。
举个实战优化案例：电商订单表（orders），表结构：id（主键）、user_id、product_id、status、create_time，原来的查询是“select * from orders where status=1 and create_time>'2026-01-01'”，执行EXPLAIN分析，发现type=ALL，key=NULL，全表扫描，耗时1200ms。优化步骤：①分析查询条件，status和create_time是查询条件，创建联合索引（status,create_time）；②重写查询，只查需要的字段，比如“select id,user_id,product_id from orders where status=1 and create_time>'2026-01-01'”，用到覆盖索引；优化后，查询耗时降至45ms，索引命中率100%。
3. 第三步：SQL语句优化，写出“高效SQL”
除了索引优化，SQL语句本身的写法也很重要，同样的需求，不同的写法，效率可能天差地别。下面给大家分享10个高频SQL优化技巧，结合实战案例，帮大家写出高效SQL。
技巧1：避免SELECT *，只查需要的字段。很多人图方便，不管需要什么字段，都用SELECT *，这样会查询很多无用的字段，增加网络传输和内存占用，还可能无法用到覆盖索引。比如“select id,name from user where id=100”，比“select * from user where id=100”效率高很多。实战案例：一个报表系统，将SELECT *改为具体字段后，查询时间从3秒降低到0.5秒，内存占用减少70%。
技巧2：优化JOIN查询，小表驱动大表。JOIN查询是慢查询的常见源头，优化原则是“小表驱动大表”，也就是用数据量少的表，驱动数据量大的表，减少循环次数。同时，确保JOIN字段上有索引，避免全表扫描。比如“select u.* from user u join order o on u.id=o.user_id where o.status=1”，如果user表数据量少，order表数据量大，就是小表驱动大表，效率高；如果JOIN字段id没有索引，就给id字段建索引。另外，尽量避免多表JOIN（超过3张表），可以拆分查询，或者用子查询（但子查询要优化）。
技巧3：用JOIN替代子查询。MySQL处理子查询的效率通常较低，尤其是相关子查询，尽量用JOIN重写子查询。比如原来的查询“select * from user where id in (select user_id from order where status=1)”，效率很低；改为“select u.* from user u join order o on u.id=o.user_id where o.status=1”，效率会大幅提升。实战案例：将子查询改为JOIN后，执行时间从8秒减少到0.3秒。
技巧4：优化分页查询，避免LIMIT offset过大。大数据量分页时，“LIMIT offset, count”在offset很大时，效率很低，因为MySQL会扫描offset+count行数据，再丢弃前offset行。比如“LIMIT 1000000,20”，会扫描1000020行数据，效率极低。优化方法：用游标分页，基于ID分页，比如“where id>last_id LIMIT 20”，last_id是上一页的最后一条数据的ID，这样MySQL只需要扫描20行数据，效率极高。实战案例：百万级数据表分页，优化后响应时间从12秒降至0.01秒。
技巧5：避免在WHERE子句中使用函数和表达式。前面提到过，索引列使用函数会导致索引失效，同样，使用表达式也会导致索引失效。比如“where age+1=30”，会导致索引失效；应该改为“where age=29”。
技巧6：优化GROUP BY和ORDER BY。GROUP BY和ORDER BY如果没有用到索引，会导致Using filesort、Using temporary，效率很低。优化方法：给GROUP BY和ORDER BY的字段建索引，确保用到索引排序，避免额外排序和临时表。比如“select name,count(*) from user group by name”，给name字段建索引，就能避免Using temporary和Using filesort。
技巧7：使用批量操作，减少数据库交互。批量插入、更新数据时，不要用单条操作，用批量操作，减少数据库交互次数，提高效率。比如批量插入1000条数据，用“insert into user (name,age) values ('张三',20),('李四',22),...”，比单条插入1000次效率高很多；批量更新可以用“update user set status=1 where id in (1,2,3,...)”，或者用CASE WHEN语句。实战案例：万条数据插入，从单条执行的5分钟，优化为批量插入的3秒钟。
技巧8：避免重复查询，缓存查询结果。如果某条查询语句，频繁执行，且结果变化不大，可以将查询结果缓存起来（比如用Redis缓存），避免每次都查询数据库，减轻数据库压力。比如首页的热门商品列表，查询频率高，数据变化慢，可以缓存10分钟，10分钟后再重新查询更新缓存。
技巧9：拆分大查询，避免长时间锁表。如果一条查询语句，要扫描大量数据，执行时间很长，会锁定大量数据，影响其他查询。可以将大查询拆分为多个小查询，分批执行。比如“select * from user where create_time>'2026-01-01'”，如果数据量很大，可以拆分为“select * from user where create_time between '2026-01-01' and '2026-01-10'”“select * from user where create_time between '2026-01-11' and '2026-01-20'”，分批查询。
技巧10：定期优化表结构，清理碎片。随着数据的插入、更新、删除，表会产生碎片，碎片会导致查询速度变慢。可以定期使用OPTIMIZE TABLE命令，优化表结构，清理碎片，比如“OPTIMIZE TABLE user;”，适用于InnoDB和MyISAM引擎。实战案例：一个频繁更新的表，经过优化后，查询性能恢复到原始水平的80%。
最后跟大家说一句：MySQL查询优化不是一蹴而就的，而是一个持续的过程。日常工作中，要定期查看慢查询日志，用EXPLAIN分析SQL，不断优化索引和SQL语句，同时结合业务场景，找到最适合的优化方案。比如电商场景，订单表数据量大，就要重点优化订单查询的索引；后台管理系统，查询频率低，就不需要过度优化，保证代码简洁即可。
另外，优化也要适度，不要为了优化而优化。比如给一个查询频率很低的字段建索引，反而会影响插入、更新的效率，得不偿失。记住：优化的核心是“平衡”，在查询效率和写入效率之间找到平衡，在性能和维护成本之间找到平衡。
来源：618同城网 www.tiancebbs.cn
