MySQL性能优化的最佳20+条经验
今天，数据库的操作越来越成为整个应用的性能瓶颈了，这点对于Web应用尤其明显。关于数据库的性能，这并不只是DBA才需要担心的事，而这更是我们程序员需要去关注的事情。当我们去设计数据库表结构，对操作数据库时（尤其是查表时的SQL语句），我们都需要注意数据操作的性能。这里，我们不会讲过多的SQL语句的优化，而只是针对MySQL这一Web应用最多的数据库。希望下面的这些优化技巧对你有用。
mysql如何定期分析检查与优化表
1. 对表进行优化 ( 优化表主要作用是消除删除或者更新造成的空间浪费)2. 对表进行分析（分析关键字的分布, 分析并存储MyISAM和BDB表中键的分布）3. 对表进行检查（检查表的错误，并且为MyISAM更新键的统计内容）4. 对表进行修复（修复被破坏的MyISAM表）分析表ANALYZE TABLE 表名1 [,表名2…] ;ANALYZE TABLE分析表的过程中，数据库系统会对表加一个只读锁。在分析期间，只能读取表中的记录，不能更新和插入记录。ANALYZE TABLE语句能够分析InnoDB和MyISAM类型的表。对表的定期分析可以改善性能，且应该成为常规维护工作的一部分。因为通过更新表的索引信息对表进行分析，可改善数据库性能。检查表MySQL中使用CHECK TABLE语句来检查表。CHECK TABLE语句能够检查InnoDB和MyISAM类型的表是否存在错误。还可以检查视图是否存在错误.check table 表名优化表随着MySQL的使用，包括BLOB和VARCHAR字节的表将变得比较繁冗，因为这些字段长度不同，对记录进行插入、更新或删除时，会占有不同大小的空间，记录就会变成碎片，且留下空闲的空间。像具有碎片的磁盘，会降低性能，需要整理，因此要优化。 （个人理解：当删除数据之后，原来的索引文件位置会空出来。等待新文件的插入，optimize命令就是整理索引文件）针对MyISAM表，直接使用如下命令进行优化 optimize table table1[,table2][,table3] myisaminnodbTable does not support optimize, doing recreate + analyze instead。因为Innodb结构下删除了大量的行，此时索引会重组并且会释放相应的空间因此不必优化。 show table status like ‘表名’；
SQL数据库如何优化
之前在BAT里参与过一个公司级应用（非市场级，投入的人力也不会那么大），上线2年后，总是被用户投诉，原因是这个应用使用MySql数据库来做持久层，但是2年了，有一张非常重要的存储历史任务的表实在是太大了，导致通过页面想要查询历史数据的速度变得非常非常慢，所以用户很是不满意。分析下来，这不是用Redis能解决的缓存问题，而是历史数据的查询响应速度问题。我们最开始是希望能够通过增加索引的解决，但是面对千万级别的数据量，我们也不敢贸然加索引，因为一旦数据库hang住，期间的所有数据库写入请求都会被放到等待队列中，如果请求是通过http请求发过来的，很有可能导致服务发生分钟级别的超时不响应。虽然经常被用户投诉反应慢，也不能破罐破摔，直接超时不响应了吧。于是我们陷入了两难的境地。后来我们分了两个部分来优化持久层。MySQL的主从配置第一步就是配置MySQL的主从库，通过将读写请求分离，来提高数据库的响应速度。从上图可知，来自同一台服务器的请求，经过MySQL-proxy被分流给了不同的MySQL节点，其中写请求给了主节点，读请求给了从节点。因此，我们首先通过分流的，减轻了单节点MySQL的响应压力，实现了优化的第一步。引入ElasticSearch但是，只配置MySQL的主从是远远不够的。通过查阅论坛，相关资料，我们最终敲定在持久层引入ElasticSearch。Elastic Search是一个轻量级的持久层工具，它支持动态多节点部署，自动备份，节点掉线后能够自动切换主从，动态广播发现新上线的节点，而这些优点的应用，无须修改任何server端配置。可以这样理解，如果你部署了4个elastic search节点，其中2个掉了，服务器还是可以很好的继续运行。此外，它还有一个最重要的优势，那就是支持大数据快速查询。一张几千万的表，如果用MySQL查询，可能需要几秒到几十秒不等，但是如果用elastic search，只需要毫秒级别就能查询到结果。完美的解决了我们当前的问题，还顺带帮我们巩固了持久层的稳定性问题。综上，优化Mysql的目的是为持久层服务，除了引入主从配置，当MySQL自身局限性导致无法继续优化后，引入其他技术也是十分必要的。如果你对这篇回答有任何问题，欢迎在下方点赞，留言。我是苏苏思量，来自BAT的java开发工程师，头像是本人，每天都会分享科技类见闻，欢迎关注我，与我共同进步。
优化mysql的limit offset的例子
在mysql中，通常使用limit做分页，而且经常会跟order by 连用。在order by 上加索引有时候是很有帮助的，不然系统会做很多的filesort经常碰到的一个问题是limit的offset太高，如：limit 100000,20，这样系统会查询100020条，然后把前面的100000条都扔掉，这是开销很大的操作，导致查询很慢。假设所有分页的页面访问频率一样，这样的查询平均扫描表的一半数据。优化的方法，要么限制访问后面的页数，要么提升高偏移的查询效率。一个简单的优化办法是使用覆盖查询（covering index）查询，然后再跟全行的做join操作。如：复制代码代码如下:SQL>select * from user_order_info limit 1000000,5;这条语句就可以优化为：复制代码代码如下:select * from user_order_info inner join (select pin from user_order_info limit 1000000,5) as lim using(pin);SQL>explain select * from user_order_info limit 1000000,5;+----+-------------+-----------------+------+---------------+------+---------+------+----------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-----------------+------+---------------+------+---------+------+----------+-------+| 1 | SIMPLE | user_order_info | ALL | NULL | NULL | NULL | NULL | 23131886 | |+----+-------------+-----------------+------+---------------+------+---------+------+----------+-------+1 row in set (0.00 sec)SQL>explain extended select * from user_order_info inner join (select pin from user_order_info limit 1000000,5) as lim using(pin);+----+-------------+-----------------+--------+---------------+---------+---------+---------+----------+----------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-----------------+--------+---------------+---------+---------+---------+----------+----------+-------------+| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | || 1 | PRIMARY | user_order_info | eq_ref | PRIMARY | PRIMARY | 42 | lim.pin | 1 | 100.00 | || 2 | DERIVED | user_order_info | index | NULL | PRIMARY | 42 | NULL | 23131886 | 100.00 | Using index |+----+-------------+-----------------+--------+---------------+---------+---------+---------+----------+----------+-------------+3 rows in set, 1 warning (0.66 sec)根据两个explain的对比，可以清晰发现，第一个未使用索引，扫描了23131886行，第二个也扫描了同样的行数，但是使用了索引，效率提高了。这样可以直接使用index得到数据，而不去查询表，当找到需要的数据之后，在与全表join，获得其他的列。
