博主(编码砖家)负责的我的项目次要采纳阿里云数据库MySQL，最近频繁呈现慢SQL告警，执行工夫最长的居然高达5分钟。导出日志后剖析，次要起因居然是没有命中索引和没有分页解决 。

其实这是十分低级的谬误，我不禁后背一凉，团队成员的技术水平亟待进步啊。革新这些SQL的过程中，总结了一些教训分享给大家，如果有谬误欢送批评指正。

MySQL性能

最大数据量

抛开数据量和并发数，谈性能都是耍流氓 。MySQL没有限度单表最大记录数，它取决于操作系统对文件大小的限度。

《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才举荐分库分表。性能由综合因素决定，抛开业务复杂度，影响水平顺次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。博主已经操作过超过4亿行数据的单表，分页查问最新的20条记录耗时0.6秒，SQL语句大抵是 select field_1,field_2 from table where id < #{prePageMinId} order by id desc limit 20，prePageMinId是上一页数据记录的最小ID。尽管过后查问速度还对付，随着数据一直增长，有朝一日必然不堪重负。分库分表是个周期长而危险高的大活儿，应该尽可能在以后构造上优化，比方降级硬件、迁徙历史数据等等，切实没辙了再分。对分库分表感兴趣的同学能够浏览分库分表的根本思维。

最大并发数

并发数是指同一时刻数据库能解决多少个申请，由maxconnections和maxuserconnections决定。**maxconnections是指MySQL实例的最大连接数，上限值是16384，maxuserconnections是指每个数据库用户的最大连接数。MySQL会为每个连贯提供缓冲区，意味着耗费更多的内存。如果连接数设置太高硬件吃不消，太低又不能充分利用硬件。个别要求两者比值超过10%，计算方法如下：

查看最大连接数与响应最大连接数：

在配置文件my.cnf中批改最大连接数

查问耗时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后保留。

尽量采纳timestamp而非datetime。

相比datetime，timestamp占用更少的空间，以UTC的格局贮存主动转换时区。

防止空值

MySQL中字段为NULL时仍然占用空间，会使索引、索引统计更加简单。从NULL值更新到非NULL无奈做到原地更新，容易产生索引决裂影响性能。尽可能将NULL值用有意义的值代替，也能防止SQL语句外面蕴含 is not null的判断。

text类型优化

因为text字段贮存大量数据，表容量会很早涨下来，影响其余字段的查问性能。倡议抽取进去放在子表里，用业务主键关联。

索引优化

索引分类

  1. 一般索引：最根本的索引。
  2. 组合索引：多个字段上建设的索引，可能减速复合查问条件的检索。
  3. 惟一索引：与一般索引相似，但索引列的值必须惟一，容许有空值。
  4. 组合惟一索引：列值的组合必须惟一。
  5. 主键索引：非凡的惟一索引，用于惟一标识数据表中的某一条记录，不容许有空值，个别用primary key束缚。
  6. 全文索引：用于海量文本的查问，MySQL5.6之后的InnoDB和MyISAM均反对全文索引。因为查问精度以及扩展性不佳，更多的企业抉择Elasticsearch。

索引优化

  1. 分页查问很重要，如果查问数据量超过30%，MYSQL不会应用索引。
  2. 单表索引数不超过5个、单个索引字段数不超过5个。
  3. 字符串可应用前缀索引，前缀长度管制在5-8个字符。
  4. 字段唯一性太低，减少索引没有意义，如：是否删除、性别。
  5. 正当应用笼罩索引，如下所示：

select loginname, nickname from member where login_name = ?

loginname, nickname两个字段建设组合索引，比login_name简略索引要更快

SQL优化

分批解决

博主小时候看到鱼塘挖开小口子放水，水面有各种漂浮物。浮萍和树叶总能顺利通过出水口，而树枝会挡住其余物体通过，有时还会卡住，须要人工清理。MySQL就是鱼塘，最大并发数和网络带宽就是出水口，用户SQL就是漂浮物。不带分页参数的查问或者影响大量数据的update和delete操作，都是树枝，咱们要把它打散分批解决，举例说明：业务形容：更新用户所有已过期的优惠券为不可用状态。SQL语句：update status=0 FROMcoupon WHERE expire_date <= #{currentDate} and status=1;如果大量优惠券须要更新为不可用状态，执行这条SQL可能会堵死其余SQL，分批解决伪代码如下：

操作符<>优化

通常<>操作符无奈应用索引，举例如下，查问金额不为100元的订单：select id from orders where amount != 100;如果金额为100的订单极少，这种数据分布重大不均的状况下，有可能应用索引。鉴于这种不确定性，采纳union聚合搜寻后果，改写办法如下：

OR优化

在Innodb引擎下or无奈应用组合索引，比方：

OR无奈命中mobileno + userid的组合索引，可采纳union，如下所示：

此时id和product_name字段都有索引，查问才最高效。

IN优化

  1. IN适宜主表大子表小，EXIST适宜主表小子表大。因为查问优化器的一直降级，很多场景这两者性能差不多一样了。
  2. 尝试改为join查问，举例如下：

select id from orders where user_id in (select id from user where level = 'VIP');

采纳JOIN如下所示：

不做列运算

通常在查问条件列运算会导致索引生效，如下所示：查问当日订单

date_format函数会导致这个查问无奈应用索引，改写后：

防止Select all

如果不查问表中所有的列，防止应用 SELECT *，它会进行全表扫描，不能无效利用索引。

Like优化

like用于含糊查问，举个例子(field已建设索引)：

这个查问未命中索引，换成上面的写法：

去除了后面的%查问将会命中索引，然而产品经理肯定要前后含糊匹配呢?全文索引fulltext能够尝试一下，但Elasticsearch才是终极武器。

Join优化

join的实现是采纳Nested Loop Join算法，就是通过驱动表的后果集作为根底数据，通过该结数据作为过滤条件到下一个表中循环查问数据，而后合并后果。如果有多个join，则将后面的后果集作为循环数据，再次到后一个表中查问数据。

  1. 驱动表和被驱动表尽可能减少查问条件，满足ON的条件而少用Where，用小后果集驱动大后果集。
  2. 被驱动表的join字段上加上索引，无奈建设索引的时候，设置足够的Join Buffer Size。
  3. 禁止join连贯三个以上的表，尝试减少冗余字段。

Limit优化

limit用于分页查问时越往后翻性能越差，解决的准则：放大扫描范畴 ，如下所示：

先筛选出ID放大查问范畴，写法如下：

如果查问条件仅有主键ID，写法如下：

如果以上计划仍然很慢呢?只好用游标了，感兴趣的敌人浏览JDBC应用游标实现分页查问的办法

其余数据库

作为一名后端开发人员，务必精通作为存储外围的MySQL或SQL Server，也要积极关注NoSQL数据库，他们曾经足够成熟并被宽泛采纳，能解决特定场景下的性能瓶颈。