怎么优化mysql？基础步骤和关键因素有哪些

mysql优化的基础步骤

刚开始接触mysql优化时，我总觉得这是个高深的技术活，直到自己真正踩过坑才明白，其实从基础做起就能解决大部分问题，记得有次公司项目上线后，用户反馈页面加载特别慢，后台日志里全是mysql查询超时的报错，当时我一头雾水，对着数据库发呆，后来跟着老同事一步步排查,才发现优化原来有章可循。

**慢查询日志是发现性能瓶颈的重要工具**，我当时在mysql配置文件里开启了slow_query_log，设置long_query_time为2秒，没过多久日志文件就记录下了几条执行时间超过5秒的SQL，其中一条是查询用户订单列表的语句，没加索引，全表扫描导致效率极低，这一步就像给mysql做“体检”,先找到生病的部位。

接着要分析查询语句，用explain命令查看那条慢查询的执行计划，发现type字段显示为ALL，Extra里有“Using filesort”，这说明mysql在做全表扫描，还需要额外排序，老同事告诉我，这时候就得考虑加索引了，我给订单表的用户ID字段建了普通索引，再执行查询，时间从5秒多降到了0.1秒，页面瞬间就快了起来，这一步就像给堵塞的道路拓宽车道,让数据通行更顺畅。

基础步骤里还有检查服务器资源使用情况，用top命令看CPU和内存占用，发现当时mysql进程占用了70%的内存，而服务器总内存才8G，后来调整了innodb_buffer_pool_size参数，从默认的128M改成4G，让更多数据缓存到内存里，减少磁盘IO，查询效率又提升了一截，这些基础步骤就像给mysql“搭骨架”，把最基本的支撑做好,性能自然不会差。

mysql优化的关键性能指标

搞懂关键性能指标，就像开车时看懂仪表盘，知道哪里该加油、哪里该减速，我刚开始优化时，只盯着查询时间，后来才发现还有很多指标能反映mysql的“健康状况”，比如QPS（每秒查询次数），它代表mysql的“工作量”，如果QPS突然飙升，可能是出现了异常查询或者流量暴增，有次促销活动，QPS从平时的500涨到了2000，服务器差点扛不住,后来通过缓存热点数据才稳住。

**连接数也是个重要指标**，mysql有个max_connections参数，默认是151，如果连接数超过这个值，新的请求就会被拒绝，之前有个项目因为没调大这个参数，用户登录时老提示“连接数据库失败”，查了半天才发现是连接数满了，后来把max_connections调到500，问题就解决了，这就像餐厅的座位，座位不够,客人自然进不来。

缓存命中率也不能忽视，InnoDB的缓冲池命中率（Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads)）如果低于95%，说明内存缓存不够用，需要调大innodb_buffer_pool_size，我之前遇到过命中率只有80%的情况，数据频繁从磁盘读取，导致查询变慢，调大缓冲池后，命中率提升到98%,查询速度明显加快。

还有锁等待情况，通过show engine innodb status命令可以看到锁等待的详细信息，有次两个事务同时更新同一条数据，出现了行锁等待，导致后面的请求排队，后来调整了事务的执行顺序，避免同时操作同一行数据，锁等待问题就解决了，这些指标就像mysql的“脉搏”，时刻反映着它的运行状态,盯着它们才能及时发现问题。

mysql优化的索引优化方法

索引就像图书馆的目录，没有索引，查询数据就像在书堆里一本本翻找，效率极低，但索引也不是越多越好，我就犯过给表加了十几个索引的错误，结果插入数据时慢得像蜗牛，后来才明白，索引是“双刃剑”，建对了能加速查询,建错了反而拖慢写入。

**最左前缀原则是索引优化的核心**，比如有个联合索引（name, age, gender），查询条件是name=‘张三’ and age=20，能用到索引；但如果是age=20 and gender=‘男’，就用不到这个索引，我之前写过一个查询，条件是age和gender，结果没走索引，后来把联合索引调整为（age, gender），查询速度立马快了10倍，这就像查字典，要按拼音的顺序查,跳着查肯定找不到。

避免索引失效也很关键，比如在索引字段上做函数操作，像where SUBSTRING(name,1,3)=‘张三’，mysql就不会走索引，有次我想查询名字前三个字是“李小明”的用户，用了SUBSTRING函数，结果全表扫描，后来改成where name like ‘李小明%’，才用上了name字段的索引，还有使用不等于（!=、<>）、is null、is not null这些条件，也可能导致索引失效,写SQL时得特别注意。

定期维护索引也很重要，用show index from 表名命令可以查看索引的使用情况，通过information_schema库的statistics表能看到索引的 Cardinality（基数），基数低的索引（比如性别字段，只有男、女两个值）基本起不到加速作用，反而占用空间，这种就该删掉，我之前给一个状态字段（只有0和1两个值）建了索引，后来发现查询根本不用它，就删掉了,表的写入速度都快了不少。

mysql优化的查询语句优化技巧

写SQL就像说话，同样的意思，表达方式不同，效果天差地别，我刚工作时写过一条查询，用了好多子查询和or条件，执行时间超过10秒，后来老同事帮我改成join和union，时间直接降到0.5秒，这才知道，查询语句优化是mysql优化的“重头戏”。

**避免select *是最基础的技巧**，很多人图方便写select *，但这样会返回不需要的字段，增加网络传输和内存开销，有次我查用户表，只需要id和name两个字段，却写了select *，结果返回了20多个字段，数据量从10KB变成了200KB，传输时间多了好几倍，后来改成select id,name，查询速度快了不少,服务器的网络负载也降了。

合理使用join代替子查询，子查询就像嵌套的盒子，一层套一层，mysql执行起来费劲，而join是“平铺”的，效率更高，比如查询“购买过商品A的用户信息”，子查询可能写成select * from user where id in (select user_id from order where product_id=1)，而join写成select u.* from user u join order o on u.id=o.user_id where o.product_id=1，后者执行效率通常更高，我试过把一个多层子查询改成join,执行时间从8秒降到了1秒。

控制结果集大小也很重要，用limit限制返回行数，尤其是分页查询，比如limit 10000,10，mysql会先扫描10010行再取最后10行，效率很低，可以用“延迟关联”优化，先查主键id，再关联取数据，比如select u.* from (select id from user limit 10000,10) t join user u on t.id=u.id，这样能减少扫描的数据量，我之前处理一个百万级数据的分页查询，用这种方法把时间从5秒降到了0.3秒。

mysql优化的配置参数调整

mysql的配置文件就像它的“控制面板”，调对了参数，性能能翻倍；调错了，可能比默认还慢，我刚开始不敢动配置，觉得这是“高级操作”，后来跟着文档试了几次，才发现只要掌握几个关键参数,就能有明显效果。

**innodb_buffer_pool_size是最重要的参数**，它决定了InnoDB缓存数据和索引的内存大小，建议设为服务器物理内存的50%-70%（如果服务器只跑mysql），我之前用的服务器是16G内存，默认配置才128M，调大到10G后，缓存命中率从85%升到98%，磁盘IO减少了一半，查询速度快了不少，但也不能设太大，留一部分内存给操作系统和其他进程，否则会导致swap,反而变慢。

max_connections和wait_timeout也得关注，max_connections设小了，连接数不够；设太大，内存会被占满，一般根据并发量来定，比如预计同时有300个连接，就设为500（留些余量），wait_timeout是连接空闲超时时间，默认8小时，太长会导致大量空闲连接占用资源，设成300秒（5分钟）比较合适，我之前遇到过连接数满的问题，调大max_connections到500，同时把wait_timeout设为300,连接数就稳定了。

innodb_log_file_size和innodb_log_buffer_size也很关键，日志文件（ib_logfile0、ib_logfile1）太小，会频繁切换日志，增加IO；太大，恢复时会慢，一般设为512M比较合适（不要超过4G），日志缓冲（innodb_log_buffer_size）默认16M，写频繁的话可以设大些，比如64M，我之前把日志文件从默认的48M改成512M，写入操作的平均耗时从0.5秒降到了0.2秒。

mysql优化的数据存储优化

数据存储就像整理房间，东西摆放得整齐，找起来才方便，mysql的数据存储优化，主要是让数据占用空间更小、读写更快，我之前接手过一个老项目，表用的都是MyISAM引擎，数据文件有好几个G，查询慢得不行，后来改成InnoDB，又做了分表分库,性能一下子提上来了。

**选择合适的存储引擎很重要**，MyISAM不支持事务和行锁，适合读多写少、不需要事务的场景，比如日志表；InnoDB支持事务、行锁、外键，适合大部分业务场景，尤其是写操作频繁的情况，我之前把一个订单表从MyISAM改成InnoDB，解决了并发更新时的表锁问题,订单提交速度快了3倍。

分表分库是处理大数据量的“法宝”，当单表数据量超过千万，查询和写入都会变慢，这时候就要分表，水平分表按数据行拆分，比如按用户ID取模分表，user_0、user_1...；垂直分表按字段拆分，把大字段（比如text类型）单独放一张表，我之前有个用户表，数据量2000万，查询很慢，按用户ID取模分成10张表，每张表200万数据，查询时间从2秒降到了0.3秒，分库则是把不同表放到不同数据库，减轻单库压力,比如把订单库和用户库分开。

选择合适的数据类型也能节省空间和提升效率，比如性别字段用char(1)存‘男’‘女’，比varchar(10)更省空间；手机号用char(11)，不用int（手机号可能以0开头，int会丢数据）；时间用datetime或timestamp，不用varchar，我之前把一个表的多个varchar(255)字段改成更合适的类型，表空间从1G减到了600M,查询速度也快了些。

mysql优化的常见问题及解决

mysql优化时总会遇到各种“坑”，踩得多了，也就知道怎么应对了，我整理了几个常见问题和解决方法,希望能帮大家少走弯路。

**连接超时问题**，有时候应用程序会报“Can't connect to MySQL server”，可能是max_connections不够，或者wait_timeout设得太小，也可能是网络问题，之前有个项目，连接超时频繁发生，查了发现max_connections才151，而同时连接数有200多，调大到500后就好了，如果是网络问题，ping一下mysql服务器，看延迟高不高,或者检查防火墙是否拦截了3306端口。

死锁问题，当两个事务互相等待对方释放锁，就会发生死锁，比如事务A更新表1的行，事务B更新表2的行，然后事务A想更新表2的行，事务B想更新表1的行，就会死锁，解决方法是让所有事务按相同的顺序操作表，或者减少事务持有锁的时间，我之前遇到过死锁，把事务里更新表的顺序统一后,就再也没发生过。

磁盘空间满了，mysql的数据文件和日志文件会占用磁盘空间，如果满了，mysql会无法写入数据，这时候要清理没用的日志（比如慢查询日志、binlog），或者把数据迁移到更大的磁盘，我之前有次binlog没设置过期时间，存了半年的日志，占了200G磁盘，设置expire_logs_days=7（保留7天日志）后,空间就释放了。

主从同步延迟，主从复制时，从库数据比主库慢，可能是网络延迟、从库压力大，或者sql_thread线程慢，解决方法有：优化从库配置（比如加大slave_parallel_workers开启并行复制）、减少主库写操作（分库分表）、确保主从网络通畅，我之前主从延迟有10分钟，把从库的slave_parallel_workers从0改成4,延迟降到了1分钟以内。

mysql优化与同类工具对比优势

现在数据库工具不少，比如PostgreSQL、SQL Server、MongoDB等，mysql能在其中站稳脚跟，肯定有它的优势，我用过PostgreSQL和SQL Server，对比下来,mysql在很多方面更适合中小规模应用。

**开源免费是mysql最大的优势**，PostgreSQL也是开源的，但SQL Server是收费的，对于初创公司或小团队来说，mysql能节省一大笔 license 费用，我之前待过一个小公司，预算有限，用mysql完全不用考虑版权问题,省下来的钱可以投到其他地方。

社区活跃，资料丰富，mysql用的人多，遇到问题在网上一搜，很多人都遇到过，解决方案也多，比如我刚开始学mysql优化时，在论坛、博客上找到很多教程，还有官方文档也很详细，基本上所有问题都能找到答案，PostgreSQL的资料相对少一些,遇到冷门问题可能要查英文文档。

部署简单，兼容性好，mysql安装配置很方便，在Linux、Windows、Mac上都能跑，和各种编程语言（Java、Python、PHP等）的兼容性也很好，我之前在Linux服务器上装mysql，一条yum命令就搞定了，而SQL Server在Linux上部署就麻烦些，很多云服务厂商都提供mysql的托管服务，比如阿里云RDS、腾讯云CDB,用起来很方便。

性能适合中小规模应用，虽然在高并发、复杂查询场景下，PostgreSQL可能更有优势，但对于大部分中小公司的业务（比如电商网站、CMS系统），mysql的性能完全够用，我之前做的一个电商项目，日活10万，用mysql支撑起来没问题，查询响应时间都在100ms以内，而且mysql的优化手段成熟,遇到性能问题容易解决。