来源：CodingBrick \链接：cnblogs.com/xiaoyangjia/p/11267191.html背景最近频繁出现慢SQL告警，最长执行时间高达5分钟。导出日志分析，主要是没有命中索引，没有分页处理。其实这是一个很低级的错误，不由得背脊一凉。团队成员的技术水平亟待提高。在改造这些SQL的过程中，总结了一些经验分享给大家。如有错误，请批评指正。MySQL性能的最大数据量不管数据量和并发数，谈性能都是流氓。MySQL不限制单表的最大记录数，这取决于操作系统的文件大小限制。文件系统单个文件大小限制FAT32最大4GNTFS最大64GBNTFS5.0最大2TBEXT2块大小为1024字节，最大文件容量为16GB；blocksize4096bytes，最大文件容量2TBEXT3blocksize4KB，最大文件容量4TBEXT4理论上可以如果大于16TB《阿里巴巴Java开发手册》，建议单表行数超过500万行或单表容量超过2GB。性能是由综合因素决定的。不管业务复杂度如何，影响程度是硬件配置、MySQL配置、数据表设计、索引优化。500万的价值仅供参考，并非铁律。博主曾经操作过一个单表，有4亿多行数据。分页查询最近20条记录耗时0.6秒。SQL语句大致是selectfield_1,field_2fromtablewhereid<#{prePageMinId}orderbyiddesclimit20，prePageMinId为上一页数据记录的最小ID。虽然当时查询速度还可以，但是随着数据的不断增长，总有一天肯定会不堪重负。分库分表是一项长期、高风险的工作。应该尽可能在当前架构上优化，比如升级硬件，迁移历史数据等，实在不行再做。对分库分表感兴趣的同学可以阅读分库分表的基本思想。最大并发数并发数是指数据库可以同时处理多少个请求，由maxconnections和maxuserconnections决定。maxconnections是指MySQL实例的最大连接数，上限为16384，maxuser*connections是指每个数据库用户的最大连接数。MySQL会为每个连接提供一个缓冲区，这意味着更多的内存消耗。连接数设得太高，硬件负担不起，设得太低，又不能充分利用硬件。一般要求两者之比超过10%。计算方法如下：max_used_connections/max_connections*100%=3/100*100%≈3%查看最大连接数和最大响应数：showvariableslike'%max_connections%';显示像“%max_user_connections%”这样的变量；在配置文件my.cnf中修改最大连接数[mysqld]max_connections=100max_used_connections=20查询耗时0.5秒。建议将单次查询耗时控制在0.5秒以内。0.5秒是经验值。基于用户体验的3秒原则。如果用户的操作在3秒内没有响应，他们会感到厌烦甚至退出。响应时间=客户端UI渲染时间+网络请求时间+应用处理时间+查询数据库时间，0.5秒是留给数据库处理时间的1/6。实现原理与NoSQL数据库相比，MySQL是一个脆弱的家伙。就像体育课上的女学生。一言不合就和同学闹矛盾（扩容难），跑两步就气喘吁吁（容量小并发低），经常身体不适要请假（SQL约束太多）。现在大家都会做一些分布式，扩展应用程序比做数据库要容易的多，所以实现的原则就是少做数据库的工作，多做应用程序的工作。充分利用索引而不滥用它，要知道索引也很耗磁盘和CPU。不建议使用数据库函数格式化数据，交给应用程序处理。不建议使用外键约束，使用应用保证数据的准确性。在写多读少的场景不建议使用唯一索引，使用应用保证唯一性。适当冗余字段，尝试创建中间表，使用应用程序计算中间结果，用空间换取时间。不允许将非常耗时的事务拆分为应用程序的较小事务。预估重要数据表（如订单表）的负载和数据增长趋势，提前优化。数据表设计数据类型数据类型选择原则：更简单或占用空间更小。如果能满足长度，整数类型尽量用tinyint、smallint、medium_int，不要用int。如果确定了字符串长度，则使用char类型。如果varchar可以满足，就不要用text类型。如果精度高就用decimal类型，或者用BIGINT。例如，如果您有两位小数，请将其乘以100并保存。尝试使用时间戳而不是日期时间。type占字节descriptiondatetime8byte'1000-01-0100:00:00.000000'to'9999-12-3123:59:59.999999timestamp4byte'1970-01-0100:00:01.000000'to'2038-01-1903:14:07.999999'与datetime相比，timestamp占用空间小，以UTC格式存储，自动转换时区。AvoidingNullValuesMySQL在字段为NULL时仍然会占用空间，这会使索引和索引统计变得更加复杂。从NULL值更新到非NULL值不能就地完成，并且容易发生索引分裂并影响性能。尽可能将NULL值替换为有意义的值，避免在SQL语句中判断isnotnull。文本类型优化由于文本字段存储大量数据，表容量会很早就增加，影响其他字段的查询性能。建议抽取出来分表，与业务主键关联。索引优化索引分类普通索引：最基本的索引。复合索引：建立在多个字段上的索引可以加快复合查询条件的检索。唯一索引：与普通索引类似，但索引列的值必须是唯一的，允许空值。组合唯一索引：列值的组合必须是唯一的。主键索引：一种特殊的唯一索引，用于唯一标识数据表中的某条记录。不允许空值，一般受主键约束。全文索引：用于查询海量文本。MySQL5.6之后的InnoDB和MyISAM都支持全文索引。由于查询准确性和扩展性较差，更多的公司选择了Elasticsearch。索引优化分页查询很重要，如果查询数据量超过30%，MYSQL将不会使用索引。单表索引数不超过5个，单索引字段数不超过5个。字符串可以使用前缀索引，前缀长度限制在5-8个字符。字段唯一性太低，增加索引没有意义，比如：是否删除，性别。合理使用覆盖索引，如下图：selectloginname,nicknamefrommemberwherelogin_name=?小洞是用来放水的，水面上有各种漂浮物。浮萍和树叶总是从喷口中穿过，而树枝会挡住其他物体的通道，有时会卡住，需要手动移除。MySQL是一个鱼塘，最大并发数和网络带宽是出水口，用户SQL是一个漂浮的对象。没有分页参数的查询或影响大量数据的更新和删除操作都是分支。我们需要将它们分解并分批处理。例如：业务描述：将用户所有过期的优惠券更新为不可用状态。SQL语句：updatestatus=0FROMcouponWHEREexpire_date<=#{currentDate}andstatus=1;如果大量的优惠券需要更新到不可用状态，执行这条SQL可能会阻塞其他SQL。批处理伪代码如下：intpageNo=1;intPAGE_SIZE=100;while(true){List
