mysql sql性能分析工具 mysql sql性能优化

前言
今天，数据库的操作越来越成为整个应用的性能瓶颈了，这点对于Web应用尤其明显。关于数据库的性能，这并不只是DBA才需要担心的事，而这更是我们程序 员需要去关注的事情。当我们去设计数据库表结构，对操作数据库时（尤其是查表时的SQL语句），我们都需要注意数据操作的性能。这里，我们不会讲过多的 SQL语句的优化，而只是针对MySQL这一Web应用最多的数据库。希望下面的这些优化技巧对你有用。

1、查询缓存优化你的查询
大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一，而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时 候，这些查询结果会被放到一个缓存中，这样，后续的相同的查询就不用操作表而直接访问缓存结果了。这里最主要的问题是，对于程序员来说，这个事情是很容易 被忽略的。因为，我们某些查询语句会让MySQL不使用缓存。请看下面的示例：

SELECT username FROM user WHERE signup_date >= CURDATE()；//查询缓存不开启
SELECT username FROM user WHERE signup_date >= '2017-03-26‘   //查询缓存开启

上面两条SQL语句的差别就是 CURDATE() ，MySQL的查询缓存对这个函数不起作用。所以，像 NOW() 和 RAND() 或是其它的诸如此类的SQL函数都不会开启查询缓存，因为这些函数的返回是会不定的易变的。所以，你所需要的就是用一个变量来代替MySQL的函数，从而 开启缓存。

2、当只要一行数据时使用 LIMIT 1
当你查询表的有些时候，你已经知道结果只会有一条结果，但因为你可能需要去fetch游标，或是你也许会去检查返回的记录数。在这种情况下，加上 LIMIT 1 可以增加性能

select  wx_merchant_id from lp_liquidator_wx_account where store_id = '******' limit 1;

3、根据业务需要增加索引或者联合索引，举例****订单表：

CREATE TABLE `lp_bank_order` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `bank_id` varchar(32) NOT NULL COMMENT '银行ID',
  `order_sn` varchar(32) NOT NULL COMMENT '清算平台订单号',
  `pay_platform_order_sn` varchar(64) NOT NULL DEFAULT '' COMMENT '支付平台订单号，支付宝或微信返回的订单号',
  `liquidator_order_sn` varchar(64) NOT NULL COMMENT '清算方订单号',
  `sub_mch_id` varchar(32) NOT NULL DEFAULT '' COMMENT '子商户号',
  `liquidator_id` varchar(32) NOT NULL COMMENT '清算方id',
  `liquidator_name` varchar(128) NOT NULL COMMENT '清算方名称',
  `liquidator_merchant_id` varchar(32) NOT NULL COMMENT '清算方商户id',
  `merchant_name` varchar(128) NOT NULL COMMENT '商户名称',
  `merchant_rate` varchar(64) NOT NULL DEFAULT '' COMMENT '商户自定义费率',
  `store_id` varchar(32) NOT NULL COMMENT '清算平台商户ID',
  `notify_url` varchar(255) NOT NULL DEFAULT '' COMMENT '支付成功回调地址',
  `pay_type` tinyint(4) NOT NULL COMMENT '''支付类型： 1.支付宝线下支付 2.支付宝线上支付 3.微信线下支付 4.微信线上支付 5.京东线下支付 6.京东线上支付 7.翼支付线下支付 8.翼支付线上支付 9.QQ钱包线下支付 10.QQ钱包线上支付 50.绿洲计划 51.蓝海行动'',',
  `order_type` tinyint(4) NOT NULL COMMENT '订单类型： 1.刷卡 2.扫码 3.线下公众号 4.小程序支付 5.app支付 6.H5支付 7.线上公众号',
  `pay_platform_type` tinyint(4) NOT NULL COMMENT '支付平台类型: 1.支付宝 2.微信 3.京东 4.翼支付 5.QQ 6.银联',
  `real_money` decimal(16,2) NOT NULL COMMENT '实际收款金额：清算方上传的收款金额',
  `net_money` decimal(16,2) NOT NULL COMMENT '净收入 ， 实际收款金额－总手续费， 总手续费＝佣金手续费＋返佣手续费＋支付宝手续费',
  `pay_platform_rate` decimal(10,6) NOT NULL COMMENT '支付平台费率',
  `pay_platform_fee` decimal(16,2) NOT NULL COMMENT '支付平台手续费',
  `bank_commission_rate` decimal(10,6) NOT NULL COMMENT '佣金费率,每笔交易，清算平台收取的费率',
  `bank_commission_fee` decimal(16,2) NOT NULL COMMENT '佣金费率,每笔交易，清算平台收取的费用',
  `gateway_commission_rate` decimal(10,6) NOT NULL DEFAULT '0.000000' COMMENT ' 佣金费率，每笔交易,网关费率 ',
  `gateway_commission_fee` decimal(16,2) NOT NULL DEFAULT '0.00' COMMENT ' 佣金费率，每笔交易，网关费用 ',
  `liquidator_commission_rate` decimal(10,6) NOT NULL COMMENT '返佣费率,每笔交易，返给清算方的佣金费率',
  `liquidator_commission_fee` decimal(16,2) NOT NULL COMMENT '返佣费率,每笔交易，返给清算方的佣金费',
  `pay_status` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '0.未支付 1.支付成功 2.支付失败 3.退款中 4.退款成功 5.退款失败 6.订单关闭 7.已撤销',
  `pay_time` bigint(20) NOT NULL DEFAULT '-1' COMMENT '支付时间',
  `create_day` int(11) NOT NULL COMMENT '创建时间日期的时间(yyyyMMdd)，精度到天',
  `pay_day` int(11) NOT NULL COMMENT '支付时间日期的时间',
  `sub_appid` varchar(32) NOT NULL DEFAULT '' COMMENT '微信sub_appid',
  `body` varchar(255) NOT NULL DEFAULT '' COMMENT '订单商品名称',
  `refund_type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '退款类型 0.不退款 1.部分退款 2.全额退款',
  `liquidator_ext_rate` varchar(500) NOT NULL DEFAULT '' COMMENT '商户终端扩展费率（JSON）',
  `union_overflow_fee` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '银联溢出费用',
  `voucher_num` varchar(64) NOT NULL DEFAULT '' COMMENT '银联对账付款凭证号',
  `device_info` varchar(32) NOT NULL DEFAULT '' COMMENT '终端设备号',
  `bank_type` varchar(20) NOT NULL DEFAULT '' COMMENT '支付卡类型',
  `is_credit` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否使用卡交易 1.信用卡 0.储蓄卡',
  `liquidator_default_rate` decimal(10,6) NOT NULL COMMENT '后台清算方设置的默认费率',
  `ext1` varchar(255) NOT NULL,
  `ext2` varchar(255) NOT NULL,
  `ext3` varchar(255) NOT NULL,
  `ext4` varchar(500) NOT NULL,
  `ext5` varchar(255) NOT NULL,
  `ext6` varchar(255) NOT NULL,
  `create_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
  `update_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '修改时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_order_sn` (`order_sn`),
  UNIQUE KEY `uniq_liqordersn_liqid` (`liquidator_order_sn`,`liquidator_id`) USING BTREE,
  KEY `idx_sid_payday_payplattype_paystatus` (`store_id`,`pay_day`,`pay_platform_type`,`pay_status`) USING BTREE,
  KEY `idx_payday_liqid_paystatus` (`pay_day`,`liquidator_id`,`pay_status`) USING BTREE,
  KEY `idx_payday_bankid` (`pay_day`,`bank_id`,`pay_status`) USING BTREE,
  KEY `idx_createday_bankid_sid_liqid` (`create_day`,`bank_id`,`store_id`,`liquidator_id`,`pay_status`),
  KEY `idx_liqid_ctime` (`liquidator_id`,`create_time`),
  KEY `idx_platform_order_sn` (`pay_platform_order_sn`),
  KEY `idx_payday_updatetime_paystatus` (`pay_day`,`update_time`,`pay_status`) USING BTREE,
  KEY `idx_payday_paystatus_updatetime` (`pay_day`,`pay_status`,`update_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='订单表';

订单号是唯一索引，业务需要清算方+清算方订单号查询，建立联合索引，业务需要查询商户+payday的信息。索引的建立一定合理考虑业务需要建立。

4、选择最合适的字段属性
Mysql是一种关系型数据库，可以很好地支持大数据量的存储，但是一般来说，数据库中的表越小，在它上面执行的查询也就越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度舍得尽可能小。

5、永远为每张表设置一个ID
我们应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的（推荐使用UNSIGNED），并设置上自动增加的 AUTO_INCREMENT标志。

6、尽可能的使用 NOT NULL
除 非你有一个很特别的原因去使用 NULL 值，你应该总是让你的字段保持 NOT NULL。这看起来好像有点争议，请往下看。首先，问问你自己“Empty”和“NULL”有多大的区别（如果是INT，那就是0和NULL）？如果你觉 得它们之间没有什么区别，那么你就不要使用NULL。（你知道吗？在 Oracle 里，NULL 和 Empty 的字符串是一样的！) 不要以为 NULL 不需要空间，其需要额外的空间，并且，在你进行比较的时候，你的程序会更复杂。 当然，这里并不是说你就不能使用NULL了，现实情况是很复杂的，依然会有些情况下，你需要使用NULL值。下面摘自MySQL自己的文档：

“NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”

7、拆分大的 DELETE 或 INSERT 语句
对于单条insert以及单条的delete命令，都需要数据连接池建立与数据库的链接，在性能上很消耗。

8、尽量减少子查询，多使用连接join
如果你的应用程序有很多JOIN查询，你应该确认两个表中JOIN的字段是被建立过索引的。这样MySQL内部 会启动为你优化JOIN的SQL语句的机制。而且这些被用来JOIN的字段，应该是相同的类型的。

9、使用联合(UNION)来代替手动创建的临时表

10、查询条件字段不要使用计算函数以及逻辑计算函数

SELECT * FROM t WHERE YEAR(d) >= 2016; //会使索引失效
SELECT * FROM t WHERE d >= ‘2016-01-01’;//修正之后

11、 用IN来替换OR

12、LIKE双百分号无法使用到索引

13、避免查询条件字段类型强制转换

14、禁止不必要的ORDER BY排序