MySQL常用脚本_MySQL

导出到测试环境

大公司常有生产环境和测试环境，测试环境没法实时同步生产环境的数据，这就需要我们手工进行同步了。

备份到文件

我们不需要将生产环境的数据全量导出到测试环境，只需要满足部分条件的数据，如最近1个星期的数据，或者最近的100条数据

<code class="hljs lasso">mysqldump -u用户名 -p密码 -h生产环境节点 数据库 数据表 --where "查询语句" --skip-lock-tables  --default-character-set=utf8 --no-create-info --quick > 数据表.sql</code>

导入到测试环境

<code class="hljs lasso"><code class="hljs lasso">mysql -u测试用户 -p测试密码 -h测试节点 数据库名 < Page_Stats.sql</code></code>

<code class="hljs lasso">存在则更新，不存在则插入

<code class="hljs lasso">我们需要定期更新离线数据库，更新时，可能有重复的数据，即已经存在满足唯一性索引的数据。这个时候，如果直接删除数据库中的数据，可能引出一些BUG。mysql中有一种插入数据库的写法，如果数据不存在则插入数据，如果存在满足唯一性索引的数据，则更新相应数据

<code class="hljs lasso"><code class="hljs lasso"><code class="hljs sql">INSERT INTO table (column_1, column_2, column_3) VALUES (&#39;column_1_value&#39;, &#39;column_1_value&#39;, &#39;column_3_value&#39;) ON DUPLICATE KEY UPDATE column_1=&#39;column_1_value&#39;, column_2=&#39;column_2_value&#39;, column_3=&#39;column_3_value&#39;;
</code></code></code>

<code class="hljs lasso"><code class="hljs sql">存储优化

<code class="hljs lasso"><code class="hljs sql">假如线上数据库只记录了每个广告主最新的出价信息，而我们想观察每个广告主的历史出价信息。那么每分钟记录所有广告主的出价信息的话，数据量将会非常大，这个时候，我们就需要对存储进行优化：如果该广告主出价信息没有变化，我们就不需要记录了

<code class="hljs lasso"><code class="hljs sql">数据库表

<code class="hljs lasso"><code class="hljs sql">表：coupon_bid_realtime<br /> 字段：coupon bid record_time<br /> 唯一索引：coupon record_time

<code class="hljs lasso"><code class="hljs sql">查询所有广告主的最近出价信息

<code class="hljs lasso"><code class="hljs lasso"><code class="hljs sql"><code class="hljs sql">SELECT A.coupon, A.bid, A.record_time FROM coupon_bid_realtime AS A JOIN (SELECT coupon, MAX(record_time) AS record_time FROM coupon_bid_realtime GROUP BY coupon) AS B ON A.coupon = B.coupon AND A.record_time = B.record_time;</code></code></code></code>

<code class="hljs lasso"><code class="hljs sql"><code class="hljs sql">过滤没有变化的数据

<code class="hljs lasso"><code class="hljs sql"><code class="hljs sql">根据前面查询出来的广告主的出价信息，如果广告主没有变更出价信息，则不插入

<code class="hljs lasso"><code class="hljs sql"><code class="hljs sql">查询神器

<code class="hljs lasso"><code class="hljs sql"><code class="hljs sql">想看自己写的查询语句是否充分利用了数据库的索引，可以使用EXPLAIN，用法EXPLAIN querySQL