MySQL性能优化——学习扛得住的MySQL数据库架构有感

一、MySQL优化原则：
where子句哪些操作将导致MySQL引擎放弃索引而进行全表扫描？

对字段进行where num is null判断。【给num设置默认值0】
使用!=或<>。
使用or连接条件，如：where num=10 or num=20【select……union all select……替代】
使用in或not in，如：where num in(1,2,3)【连续数值可用where num between 1 and 3替代】
like‘李%’【考虑全文检索】
使用参数

1、【只要一行数据】或【只需要确认是否包含该数据】： LIMIT 1
查询时，你已经知道结果只会有一条，但因为可能需要去fetch游标，或是你会去检查返回的记录数。在这种情况下，加上 LIMIT 1 可以增加性能。这样，MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据。
测试数据：
MySQL自带数据库：sakila库rental表（数据量16W+），待查询数据在10467行。

SELECT * FROM rental WHERE rental_date ='2005-08-01 09:45:58' # 耗时0.018s

;
2、为搜索字段建索引
索引也有消耗，性别无需索引。
3、避免select *
读出数据越多，查询就越慢。如果数据库和web服务器独立，还会增加网络传输的负载。请求所有列再丢掉不需要的列？减少表结构的影响。

4、不要 ORDER BY RAND()
【随机挑选数据】【打乱返回数据】
看着很方便，但会让你的数据库性能指数级下降（MySQL不得不执行RAND()函数，耗CPU时间）。

SELECT rental_id FROM rental ORDER BY RAND() LIMIT 1;
这条要求似乎是针对就数据库，在MySQL 5.7.10测试时，仅第一次耗时达到几百毫秒，随后都是几毫秒。

5、用 ENUM 而不是 VARCHAR
ENUM 类型是非常快和紧凑的。在实际上，其保存的是 TINYINT，但其外表上显示为字符串。如果你有一个字段，比如“性别”，“国家”，“民族”，“状态”或“部门”，你知道这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不是 VARCHAR。
测试数据：
MySQL自带数据库：world库country表（暂略）

6、尽可能使用not null
①null是空值吗？不是，空值不占用空间，而数据库里的null占用空间，数据库里任何数跟NULL进行运算都是NULL, 判断值是否等于NULL，不能简单用=，而要用IS NULL关键字。

7、快速删除表
先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

8、拒绝大SQL
一条SQL叧能在一个CPU运算，5000+ QPS（每秒查询率Query Per Second）的高幵发中，1秒大SQL可能一条大SQL就把整个数据库堵死。拆解成多条简单SQL，简单SQL缓存命中率更高，减少锁表时间，特别是MyISAM，用上多CPU。

9、范围查找
同一字段：in代替or
or效率：O(n)；in效率：O(Log n)，建议n小于200。

select*FROM rental WHERE return_date='2005-08-04 10:49:24'or return_date='2005-08-08 06:46:39'; # 8ms

select*FROM rental WHERE return_date IN('2005-08-04 10:49:24','2005-08-08 06:46:39'); # 7ms
不同字段：union代替or

select*FROM rental WHERE return_date='2005-08-04 10:49:24' or inventory_id=3157;

selectFROM rental WHERE return_date='2005-08-04 10:49:24' UNION SELECT FROM rental WHERE inventory_id=3157;

10、limit高效分页
Select from table limit 10000,10;
改为：
Select from table WHERE id>=23423 limit 11; #10+1 (每页10条)

11、union all而不是union
若无需对结果进行去重，则用UNION ALL，UNION 要去重，有开销。【此处效果不是很明显，或许数据量不够16W+】

Select SQL_NO_CACHE from study.rentalUNION SELECT FROM sakila.rental; #0.74s

Select SQL_NO_CACHE from study.rental UNION ALL SELECT FROM sakila.rental; #0.72s
12、同类型比较
数字比数字，字符比字符。
数值比字符：同时转为双精度再比对；字符比数值：字符整列转数值，无法使用索引。

参考资料：
提高mysql千万级大数据SQL查询优化30条经验（Mysql索引优化注意） http://www.jincon.com/archives/120/?spm=5176.100239.blogcont36780.4.ifPIKE