in 和 exsits 优化

原则：小表驱动大表，即小的数据集驱动大的数据集 **in：**当B表的数据集小于A表的数据集时，in优于exists

select * from A where id in (select id from B) 
 #. 等价于：
   for (select id form B) {
       select * from A where A.id = B.id
   } 

**exists：**当A表的数据集小于B表的数据集时，exists优于in 将主查询A的数据，放到子查询B中做条件验证，根据验证结果（true或false）来决定主查询的数据是否保留

select * from A where exists (select 1 from B where B.id = A.id) 
  #. 等价于：
   for (select id form A) {
       select * from B where B.id = A.id
   } 

1、EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1替换,官方说法是实际执行时会忽略SELECT清单,因此没有区别

2、EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比

3、EXISTS子查询往往也可以用JOIN来代替，何种最优需要具体问题具体分析

统计 Count 的几种形式

-- 临时关闭mysql查询缓存，为了查看sql多次执行的真实时间
mysql> set global query_cache_size=0;
mysql> set global query_cache_type=0;
mysql> EXPLAIN select count(1) from employees;
mysql> EXPLAIN select count(id) from employees;
mysql> EXPLAIN select count(name) from employees;
mysql> EXPLAIN select count(*) from employees;

注意：以上4条sql只有根据某个字段count不会统计字段为null值的数据行四个sql的执行计划一样，说明这四个sql执行效率应该差不多

**字段有索引：count(*)≈count(1)>count(字段)>count(主键 id) //字段有索引，count(字段)统计走二级索引，二级索引存储数据比主键索引少，所以count(字段)>count(主键 id) **

字段无索引：count(*)≈count(1)>count(主键 id)>count(字段) //字段没有索引count(字段)统计走不了索引，count(主键 id)还可以走主键索引，所以count(主键 id)>count(字段)

count(1)跟count(字段)执行过程类似，不过count(1)不需要取出字段统计，就用常量1做统计，count(字段)还需要取出字段，所以理论上count(1)比count(字段)会快一点。

count() 是例外，mysql并不会把全部字段取出来，而是专门做了优化，不取值，按行累加，效率很高，所以不需要用count(列名)或count(常量)来替代 count()。

为什么对于count(id)，mysql最终选择辅助索引而不是主键聚集索引？因为二级索引相对主键索引存储数据更少，检索性能应该更高，mysql内部做了点优化(应该是在5.7版本才优化)。

总结：对于 MySQL 来说 count(*) , count(1) 性能相同，可以官方文档这样解释的：

InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.

文档地址：dev.mysql.com/doc/refman/…

常见优化方法

1、查询mysql自己维护的总行数

对于myisam存储引擎的表做不带where条件的count查询性能是很高的，因为myisam存储引擎的表的总行数会被mysql存储在磁盘上，查询不需要计算 对于innodb存储引擎的表mysql不会存储表的总记录行数(因为有MVCC机制，后面会讲)，查询count需要实时计算

2、show table status

如果只需要知道表总行数的估计值可以用如下sql查询，性能很高

3、将总数维护到Redis里

插入或删除表数据行的时候同时维护redis里的表总行数key的计数值(用incr或decr命令)，但是这种方式可能不准，很难保证表操作和redis操作的事务一致性

4、增加数据库计数表

插入或删除表数据行的时候同时维护计数表，让他们在同一个事务里操作

MySQL数据类型选择

在MySQL中，选择正确的数据类型，对于性能至关重要。一般应该遵循下面两步： （1）确定合适的大类型：数字、字符串、时间、二进制； （2）确定具体的类型：有无符号、取值范围、变长定长等。 在MySQL数据类型设置方面，尽量用更小的数据类型，因为它们通常有更好的性能，花费更少的硬件资源。并且，尽量把字段定义为NOT NULL，避免使用NULL。

1、数值类型

优化建议

1. 如果整形数据没有负数，如ID号，建议指定为UNSIGNED无符号类型，容量可以扩大一倍。

2. 建议使用TINYINT代替ENUM、BITENUM、SET。

3. 避免使用整数的显示宽度(参看文档最后)，也就是说，不要用INT(10)类似的方法指定字段显示宽度，直接用INT。

4. DECIMAL最适合保存准确度要求高，而且用于计算的数据，比如价格。但是在使用DECIMAL类型的时候，注意长度设置。

5. 建议使用整形类型来运算和存储实数，方法是，实数乘以相应的倍数后再操作。

6. 整数通常是最佳的数据类型，因为它速度快，并且能使用AUTO_INCREMENT。

2、日期和时间

优化建议

1. MySQL能存储的最小时间粒度为秒。

2. 建议用DATE数据类型来保存日期。MySQL中默认的日期格式是yyyy-mm-dd。

3. 用MySQL的内建类型DATE、TIME、DATETIME来存储时间，而不是使用字符串。

4. 当数据格式为TIMESTAMP和DATETIME时，可以用CURRENT_TIMESTAMP作为默认（MySQL5.6以后），MySQL会自动返回记录插入的确切时间。

5. TIMESTAMP是UTC时间戳，与时区相关。

6. DATETIME的存储格式是一个YYYYMMDD HH:MM:SS的整数，与时区无关，你存了什么，读出来就是什么。

7. 除非有特殊需求，一般的公司建议使用TIMESTAMP，它比DATETIME更节约空间，但是像阿里这样的公司一般会用DATETIME，因为不用考虑TIMESTAMP将来的时间上限问题。

8. 有时人们把Unix的时间戳保存为整数值，但是这通常没有任何好处，这种格式处理起来不太方便，我们并不推荐它。

3、字符串

参考内容

1. dev.mysql.com

2. 《Alibaba Java 编码规范》

3. MySQL limit 分页查询优化（百万级优化）

4. Mysql中join连接算法和优化思路

5. MySQL学习笔记：count(1)、count(*)、count（字段）的区别