MySql批量插入优化Sql执行效率实例详解,Mysql效率优化定位较低sql的两种方式

MySql批量插入优化Sql执行效率实例详解

关于mysql效率优化一般通过以下两种方式定位执行效率较低的sql语句。

网站系统上线至今,数据量已经不知不觉上到500M,近8W记录了。涉及数据库操作的基本都是变得很慢了,用的人都会觉得躁火然后把这个情况在群里一贴,包括机器配置什么的一说,马上就有群友发话了,而且帮我确定了不是机器配置的问题,“深圳-枪手”热心人他的机器512内存过百W的数据里也跑得飞快,甚至跟那些几W块的机器一样牛(吹过头了),呵呵~

itemcontractprice数量1万左右,每条itemcontractprice 插入5条日志。

通过慢查询日志定位那些执行效率较低的 SQL 语句,用
–log-slow-queries[=file_name] 选项启动时, mysqld 会
写一个包含所有执行时间超过 long_query_time 秒的 SQL
语句的日志文件,通过查看这个日志文件定位效率较低的 SQL 。

  在群友的分析指点下,尝试把排序、条件等一个一个去除来做测试,结果发现问题就出在排序部分,去除排序的时候,执行时间由原来的48秒变成0.3x秒,这是个什么档次的变化呀~~看着这个结果我激动ing…..

updateInsertSql.AppendFormat("UPDATE itemcontractprice AS p INNER JOIN foreigncurrency AS f ON p.ForeignCurrencyId = f.ContractPriceId SET p.RemainPrice = f.RemainPrice * {0},p.BuyOutPrice = f.BuyOutPrice * {0},p.ReservedPrice = f.ReservedPrice * {0},p.CollectedPrice = f.CollectedPrice * {0},p.AccessPrice = f.AccessPrice * {0} WHERE p.CurrencyId = {1} AND p.date BETWEEN '{2:yyyy-MM-dd}' AND '{3:yyyy-MM-dd}';", rate.ExchangeRate, exchangeRate.CurrencyId, rate.BeginDate, rate.EndDate); 

updateInsertSql.AppendFormat("INSERT INTO `itemcontractpricelog`(`ContractPriceType`,`ContractPrice`,`FcContractPrice`,`IsExpire`,`LogRemark`,`CreatedByName`,`CreatedById`,`CreatedDate`,`LogTypeId`,`ProviderId`,`StageId`,`Date`,`CurrencyId`,`ContractPriceId`,`StockPattern`,`ItemId`) SELECT 0,c.RemainPrice,f.RemainPrice,c.RemainIsExpire,'外币汇率调整,重新计算人民币底价','job',0,NOW(),5,c.ProviderId,c.StageId,c.Date,c.CurrencyId,c.ContractPriceId,0,c.ItemId FROM itemcontractprice AS c INNER JOIN foreigncurrency AS f ON c.ForeignCurrencyId = f.ContractPriceId WHERE c.CurrencyId={0} AND c.date BETWEEN '{1:yyyy-MM-dd}' AND '{2:yyyy-MM-dd}';", exchangeRate.CurrencyId, rate.BeginDate, rate.EndDate); 

updateInsertSql.AppendFormat(" INSERT INTO `itemcontractpricelog`(`ContractPriceType`,`ContractPrice`,`FcContractPrice`,`IsExpire`,`LogRemark`,`CreatedByName`,`CreatedById`,`CreatedDate`,`LogTypeId`,`ProviderId`,`StageId`,`Date`,`CurrencyId`,`ContractPriceId`,`StockPattern`,`ItemId`) SELECT 1,c.BuyOutPrice,f.BuyOutPrice,c.BuyOutIsExpire,'外币汇率调整,重新计算人民币底价','job',0,NOW(),5,c.ProviderId,c.StageId,c.Date,c.CurrencyId,c.ContractPriceId,0,c.ItemId FROM itemcontractprice AS c INNER JOIN foreigncurrency AS f ON c.ForeignCurrencyId = f.ContractPriceId WHERE c.CurrencyId={0} AND c.date BETWEEN '{1:yyyy-MM-dd}' AND '{2:yyyy-MM-dd}';", exchangeRate.CurrencyId, rate.BeginDate, rate.EndDate); 

updateInsertSql.AppendFormat("INSERT INTO `itemcontractpricelog`(`ContractPriceType`,`ContractPrice`,`FcContractPrice`,`IsExpire`,`LogRemark`,`CreatedByName`,`CreatedById`,`CreatedDate`,`LogTypeId`,`ProviderId`,`StageId`,`Date`,`CurrencyId`,`ContractPriceId`,`StockPattern`,`ItemId`) SELECT 2,c.ReservedPrice,f.ReservedPrice,c.ReservedIsExpire,'外币汇率调整,重新计算人民币底价','job',0,NOW(),5,c.ProviderId,c.StageId,c.Date,c.CurrencyId,c.ContractPriceId,0,c.ItemId FROM itemcontractprice AS c INNER JOIN foreigncurrency AS f ON c.ForeignCurrencyId = f.ContractPriceId WHERE c.CurrencyId={0} AND c.date BETWEEN '{1:yyyy-MM-dd}' AND '{2:yyyy-MM-dd}';", exchangeRate.CurrencyId, rate.BeginDate, rate.EndDate); 

updateInsertSql.AppendFormat("INSERT INTO `itemcontractpricelog`(`ContractPriceType`,`ContractPrice`,`FcContractPrice`,`IsExpire`,`LogRemark`,`CreatedByName`,`CreatedById`,`CreatedDate`,`LogTypeId`,`ProviderId`,`StageId`,`Date`,`CurrencyId`,`ContractPriceId`,`StockPattern`,`ItemId`) SELECT 3,c.CollectedPrice,f.CollectedPrice,c.CollectedIsExpire,'外币汇率调整,重新计算人民币底价','job',0,NOW(),5,c.ProviderId,c.StageId,c.Date,c.CurrencyId,c.ContractPriceId,0,c.ItemId FROM itemcontractprice AS c INNER JOIN foreigncurrency AS f ON c.ForeignCurrencyId = f.ContractPriceId WHERE c.CurrencyId={0} AND c.date BETWEEN '{1:yyyy-MM-dd}' AND '{2:yyyy-MM-dd}';", exchangeRate.CurrencyId, rate.BeginDate, rate.EndDate); 
updateInsertSql.AppendFormat("INSERT INTO `itemcontractpricelog`(`ContractPriceType`,`ContractPrice`,`FcContractPrice`,`IsExpire`,`LogRemark`,`CreatedByName`,`CreatedById`,`CreatedDate`,`LogTypeId`,`ProviderId`,`StageId`,`Date`,`CurrencyId`,`ContractPriceId`,`StockPattern`,`ItemId`) SELECT 4,c.AccessPrice,f.AccessPrice,c.AccessIsExpire,'外币汇率调整,重新计算人民币底价','job',0,NOW(),5,c.ProviderId,c.StageId,c.Date,c.CurrencyId,c.ContractPriceId,0,c.ItemId FROM itemcontractprice AS c INNER JOIN foreigncurrency AS f ON c.ForeignCurrencyId = f.ContractPriceId WHERE c.CurrencyId={0} AND c.date BETWEEN '{1:yyyy-MM-dd}' AND '{2:yyyy-MM-dd}';", exchangeRate.CurrencyId, rate.BeginDate, rate.EndDate); 
//var curContractPriceList = itemContractPriceList.Where(o => o.CurrencyId == exchangeRate.CurrencyId && o.Date >= rate.BeginDate && o.Date <= rate.EndDate).ToList(); 
logger.InfoFormat("底价更新和日志sql:{0}", updateInsertSql.ToString()); 
//if (curContractPriceList.Count == 0) continue; 
int effctRows = 0; 
using (var tran = UnitOfWorkManager.Begin()) 
{ 
  effctRows = taskRepository.ExecuteSql(updateInsertSql.ToString(), false); 
  tran.Complete(); 
} 
logger.InfoFormat("底价更新影响行数:{0}", effctRows); 

慢查询日志在查询结束以后才纪录,所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题,可以使用
show processlist 命令查看当前 MySQL
在进行的线程,包括线程的状态、是否锁表等,可以实时地查看 SQL 的
执行情况,同时对一些锁表操作进行优化。

  于是我把涉及排序的字段组成一个联合索引alter table xx add index
indexname(x1,x2,x3),经过2分钟创建新索引之后再执行同一个SQL语句,哇塞0.28S。。。。爽

正常情况下大概20秒钟就ok.

下面我们举例说明一下,如何通过慢查询日志定位执行效率底的 SQL 语句:

  于是按照同样的思路把其它几个常用的SQL作了过些优化,效果马上见效

之前是用EF操作,查询出来 ,要耗时,然后再组装 update语句
,然后再插入日志(每条数据5条日志),这个网络交互的时间加上数据库连接打开关闭的时间,总的执行时间,大概10多分钟。

开启慢查询日志 , 配置样例:

  过了30分钟再查slow
sql记录文件,不好了,发现原来一个好好的SQL变得灰常慢了,神马情况?

用sql语句批量操作,可以说效率提升了
40倍,就是大量数据的传输和数据库的处理次数耗时。

log-slow-queries

  几经分析和测试原来就是因为添加了联合索引的原因,而且这个SQL语句当中有个or,当把这个or改用union之后问题排除。

所以说,软件开发不是开发完成就行,而是要解决性能上的问题,这才是开发的进阶。

在 my.cnf 配置文件中增加上述配置项并重启 mysql 服务,这时 mysql
慢查询功能生效。慢查询 日志将写入参数 DATADIR
(数据目录)指定的路径下,默认文件名是 host_name-slow.log 。

  这回又得出一个心得:写SQL的时候千万别一时就手,随便写个就OK,那会为以为带来很严重的后果。

感谢阅读,希望能帮助到大家,谢谢大家对本站的支持!

和错误日志、查询日志一样,慢查询日志记录的格式也是纯文本,可以被直接读取。下例中演示了慢查询日志的设置和读取过程。

  再附上一段关于Where子句的执行顺序:

您可能感兴趣的文章:

  • mysql
    in语句子查询效率慢的优化技巧示例
  • Mysql效率优化定位较低sql的两种方式
  • 详解Mysql多表联合查询效率分析及优化
  • MySQL优化之如何查找SQL效率低的原因
  • MySQL查询优化:LIMIT
    1避免全表扫描提高查询效率
  • MySQL 联合索引与Where子句的优化
    提高数据库运行效率
  • mysql数据库查询优化
    mysql效率
  • 提升MYSQL查询效率的10个SQL语句优化技巧

( 1 )首先查询一下 long_query_time 的值 。

  在用MySQL查询数据库的时候,连接了很多个用,发现非常慢。例如:

mysql> show variables like 'long%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| long_query_time | 10 |
+-----------------+-------+
1 row in set (0.00 sec)

  SELECT … WHERE p.languages_id = 1 AND m.languages_id = 1 AND
c.languages_id = 1 AND t.languages_id = 1 AND p.products_id IN
(472,474)

( 2 )为了方便测试,将修改慢查询时间为 5 秒。

  这样查询需要20多秒,虽然在各个字段上都建立了索引。用分析Explain
SQL一分析,发现在第一次分析过程中就返回了几万条数据:

mysql> set long_query_time=5;
Query OK, 0 rows affected (0.02 sec)

  WHERE p.languages_id = 1 ,然后再依次根据条件,缩小范围。

( 3 )依次执行下面两个查询语句。

  而我改变一下WHERE 字段的位置之后,速度就有了明显地提高:

第一个查询因为查询时间低于 5 秒而不会出现在慢查询日志中:

  WHERE p.products_id IN (472,474) AND p.languages_id = 1 AND
m.languages_id = 1 AND c.languages_id = 1 AND t.languages_id = 1

mysql> select count(*) from order2008;
+----------+
| count(*) |
+----------+
| 208 |
+----------+
1 row in set (0.00 sec)

  这样,第一次的条件是p.products_id IN
(472,474),它返回的结果只有不到10条,接下来还要根据其它的条件来过滤,自然在速度上有了较大的提升。

第二个查询因为查询时间大于 5 秒而应该出现在慢查询日志中:

  经过实践发现,不要以为WHERE中的字段顺序无所谓,可以随便放在哪,应该尽可能地第一次就过滤掉大部分无用的数据,只返回最小范围的数据。

mysql> select count(*) from t_user;
+----------+
| count(*) |
+----------+
| 6552961 |
+----------+
1 row in set (11.07 sec)

  希望能帮到有同样遭遇的朋友。

( 4 )查看慢查询日志。

您可能感兴趣的文章:

  • mysql
    in语句子查询效率慢的优化技巧示例
  • MySql批量插入优化Sql执行效率实例详解
  • MySql批量插入优化Sql执行效率实例详解,Mysql效率优化定位较低sql的两种方式。Mysql效率优化定位较低sql的两种方式
  • 详解Mysql多表联合查询效率分析及优化
  • MySQL优化之如何查找SQL效率低的原因
  • MySQL查询优化:LIMIT
    1避免全表扫描提高查询效率
  • mysql数据库查询优化
    mysql效率
  • 提升MYSQL查询效率的10个SQL语句优化技巧
[root@localhost mysql]# more localhost-slow.log
# Time: 081026 19:46:34
# User@Host: root[root] @ localhost []
# Query_time: 11 Lock_time: 0 Rows_sent: 1 Rows_examined: 6552961
select count(*) from t_user;

从上面日志中,可以发现查询时间超过 5 秒的 SQL ,而小于 5
秒的则没有出现在此日志中。
如果慢查询日志中记录内容很多,可以使用 mysqldumpslow 工具( MySQL
客户端安装自带)来对慢查询日志进行分类汇总。下例中对日志文件
mysql_master-slow.log 进行了分类汇总,只显示汇总后摘要结果:

[root@mysql_master mysql_data]# mysqldumpslow mysql_master-slow.log
Reading mysql slow query log from mysql_master-slow.log
Count: 2 Time=11.00s (22s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@mysql_master
select count(N) from t_user;

对于 SQL 文本完全一致,只是变量不同的语句, mysqldumpslow
将会自动视为同一个语句进行统计,变量值用 N
来代替。这个统计结果将大大增加用户阅读慢查询日志的效率,并迅速定位系统的
SQL 瓶颈。

注意:慢查询日志对于我们发现应用中有性能问题的 SQL
很有帮助,建议正常情况下,打开此日志并经常查看分析。

以上是给大家介绍的Mysql效率优化定位较低sql的两种方式
,希望以上所述对大家有所帮助。

您可能感兴趣的文章:

  • mysql
    in语句子查询效率慢的优化技巧示例
  • MySql批量插入优化Sql执行效率实例详解
  • 详解Mysql多表联合查询效率分析及优化
  • MySQL优化之如何查找SQL效率低的原因
  • MySQL查询优化:LIMIT
    1避免全表扫描提高查询效率
  • MySQL 联合索引与Where子句的优化
    提高数据库运行效率
  • mysql数据库查询优化
    mysql效率
  • 提升MYSQL查询效率的10个SQL语句优化技巧