整理的mysql优化内容

1，当只要一行数据时使用 LIMIT 1
如果明确只取一条数据，要加上limit 1;

2，避免 SELECT *,根据需要获取字段
应该养成一个需要什么就取什么的好的习惯。

3，使用 ENUM 而不是 VARCHAR
ENUM 类型是非常快和紧凑的。在实际上，其保存的是 TINYINT，但其外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美。如果你有一个字段，比如“性别”，“国家”，“民族”，“状态”或“部门”，你知道这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不是 VARCHAR。

4，把IP地址存成 UNSIGNED INT
进行选择合适并且占用空间小的字段，特别是需要建索引的字段更需要尽量减少字段的长度减少表的大小以提高查询的速度。
如IP地址尽量使用无符号整数int unsigned来保存，对状态和类型上的标识尽量使用tinyint unsigned，如果使用varchar则尽量长度不要超过16个字节。
//将IP转换为整数$ip_num = ip2long(‘211.157.144.20’);
//将整数转换为IP$ip = long2ip(‘3718090772’);

5,随机去5条数据
SELECT * FROM User ORDER BY rand() LIMIT 5; （避免）
5 rows in set (15.33 sec)
select * from User as t1 join (select round(rand() * (select max(user_id) from User)) as user_id ) as t2 where t1.user_id > t2.user_id order by t1.user_id asc limit 5;
5 rows in set (0.00 sec)
但是这样会产生连续的5条记录。解决办法只能是每次查询一条，查询5次。即便如此也值得，因为15万条的表，查询只需要0.01秒不到。

6，尽量减少表连接查询，最好是单表查询

7，避免全表扫描
对查询进行优化，应尽量避免全表扫描，首先应考虑在where及order by 涉及的列上建立索引。

8,左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

9,尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

10，知识点–explain
mysql解释语句
type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，得保证查询至少达到range级别，最好能达到ref。
explain select * from User where num > 5 and status = ‘normal’ limit 2;
+—-+————-+——-+——+—————+——–+———+——-+——–+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——–+———+——-+——–+————-+
| 1 | SIMPLE | User | ref | status | status | 258 | const | 164739 | Using where |
+—-+————-+——-+——+—————+——–+———+——-+——–+————-+
1 row in set (0.00 sec)

id：查询序列号；
select_type：查询类型，常见的取值有 SIMPLE （简单表，即不使用表连接或者子查询）、 PRIMARY （主查询，即外层的查询）、 UNION （ UNION 中的第二个或者后面的查询语句）、 SUBQUERY （子查询中的第一个 SELECT ）等；
table：输出结果集的表；
type：扫描方式，all表示全表扫描，表示表的连接类型，性能由好到差的连接类型为
system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all
possible_keys：可能使用的索引
key：实际使用的索引
key_len：索引字段长度
rows：该SQL扫描了多少行，可能得到的数据行
Extras：额外信息，如排序方式等

mysql> explain select * from User where update_ts < ‘2016-01-01’ limit 10 ;
+—-+————-+——-+——-+—————+———–+———+——+——–+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+———–+———+——+——–+————-+
| 1 | SIMPLE | User | range | update_ts | update_ts | 4 | NULL | 164739 | Using where |
+—-+————-+——-+——-+—————+———–+———+——+——–+————-+

mysql> explain select * from User where year(update_ts) < 2016 limit 10 ;
+—-+————-+——-+——+—————+——+———+——+——–+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+——–+————-+
| 1 | SIMPLE | User | ALL | NULL | NULL | NULL | NULL | 329478 | Using where |
+—-+————-+——-+——+—————+——+———+——+——–+————-+
同样的情形也会发生在对数值型字段进行计算的时候：
mysql> explain select user_id from User where total=4;
+—-+————-+——-+——+—————+——-+———+——-+——+————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——-+———+——-+——+————————–+
| 1 | SIMPLE | User | ref | total | total | 5 | const | 3308 | Using where; Using index |
+—-+————-+——-+——+—————+——-+———+——-+——+————————–+
1 row in set (0.00 sec)

mysql> explain select user_id from User where total/2=2;
+—-+————-+——-+——-+—————+——-+———+——+——–+————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+——-+———+——+——–+————————–+
| 1 | SIMPLE | User | index | NULL | total | 5 | NULL | 331815 | Using where; Using index |
+—-+————-+——-+——-+—————+——-+———+——+——–+————————–+
1 row in set (0.00 sec)