MySQL经常使用技巧

建表

1. 每一个表都有一个id字段，最好为UNSIGNED。如 INT(9) UNSIGNED NOT NULL
2. 在表使用一段时间后，使用PROCEDURE ANALYSE得到建议，如select * from log_table where record_date > “2015-06-01” PROCEDURE ANALYSE (100,1000);
3. 尽量使用NOT NULL的字段，由于NULL也是占空间的
4. 将IP存成UNSIGNED INT，SQL中用INET_ATON() 将IP地址转换成整型。使用INET_NTOA()将其还原
5. 使用固定长度的字段能够节省空间
6. 字段长度尽可能短，以便优化速度。可是要保证每一个字段有足够的空间以满足需求
7. 字符串字段：由于mysql默认不区分大写和小写，假设字符串字段作为主键的话。须要将其改成bin类型。如

CREATE TABLE T( 
    A VARCHAR(10) BINARY 
); 

1. 索引：将常常查询的字段创建成一个索引字段
2. 索引：假设一个字段在key中。尽量将其转换成整数。以加快查询速度。

   比方我们常常查询各个机型的日活，就能够将机型映射成整数存在DB中，以加快查询速度

查询

1. 想看自己写的查询语句是否充分利用了数据库的索引。能够使用EXPLAIN，使用方法EXPLAIN querySQL
2. 使用查询缓存，不要使用 NOW() 、 RAND() 等
3. 假设仅仅须要一条数据的话，使用LIMIT 1
4. 对搜索的字段建立索引
5. JOIN时，对连接字段使用同样的数据类型。并对字段建立索引
6. 不要使用ORDER BY RAND()。由于查询前，该查询会对表中每一行记录都运行RAND()
7. 查询时尽量指定查询字段。避免使用SELECT *，以提高IO速度

使用

1. 含有大量数据的DELETE或者INSERT时，使用分片，如DELETE FROM logs WHERE log_date <= ‘2009-10-01’ LIMIT 10000

导出到測试环境

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

备份到文件

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

mysqldump -uusername -ppassword -h生产环境节点 数据库 数据表 --where "查询语句" --skip-lock-tables  --default-character-set=utf8 --no-create-info --quick > 数据表.sql

导入到測试环境

mysql -u測试用户 -p測试password -h測试节点 数据库名 < Page_Stats.sql

更新

存在则更新，不存在则插入

我们须要定期更新离线数据库，更新时。可能有反复的数据。即已经存在满足唯一性索引的数据。

这个时候，假设直接删除数据库中的数据。可能引出一些BUG。

mysql中有一种插入数据库的写法，假设数据不存在则插入数据，假设存在满足唯一性索引的数据。则更新对应数据

INSERT INTO table (column_1, column_2, column_3) VALUES ('column_1_value', 'column_1_value', 'column_3_value') ON DUPLICATE KEY UPDATE column_1='column_1_value', column_2='column_2_value', column_3='column_3_value';

主键存在则将某个字段的数值添加，不存在则插入

INSERT INTO T(app_id, count) VALUES(10086, 10) ON DUPLICATE KEY UPDATE count = 10 + count"

维护数据

对于有时效性的数据，能够定期清除n天前的数据。

存储优化

假如线上数据库仅仅记录了每一个广告主最新的出价信息。而我们想观察每一个广告主的历史出价信息。那么每分钟记录全部广告主的出价信息的话，数据量将会很大，这个时候，我们就须要对存储进行优化：假设该广告主出价信息没有变化，我们就不须要记录了

数据库表

表：coupon_bid_realtime
字段：coupon bid record_time
唯一索引：coupon record_time

查询全部广告主的近期出价信息

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;

过滤没有变化的数据

依据前面查询出来的广告主的出价信息，假设广告主没有变更出价信息，则不插入

參考

1. Top 20+ MySQL Best Practices