SQL笔记

1、数据库有 默认排序，尽量不使用order by
MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；
2、在 Where、Having后面的过虑条件中不要使用字段的函数表达式， 使用字段的函数表达式，数据库系统不会用到索引，要对所有记录进行扫描，因此会严重影响效率，尤其在记录较多时。
即，不要使用：
Select 字段1  From 表1  where  字段的函数表达式＝表达式值
Select 字段1  From 表1  where   DATEDIFF(now(),a.addtime)<2
解决方案： 等号右边可以使用函数

    等号右边常量的函数或多重函数的嵌套都不会影响效率，因为只计算一次，跟记录多少无关，

所以要采用下面写法：
Select 字段1  From 表1 Where 字段1＝常量的函数（或多重函数嵌套） 　　
根据上面的原则，上面语句可以改成
Select 字段1  From 表1   where  a.addtime > DATE_ADD(NOW(),INTERVAL -2 DAY)
更改后的语句数据库系统就可以使用索引了，效率会非常高。
3、避免使用Or逻辑 （部分数据库引擎已作出相应优化，在有合适索引的情况下可适当使用） 
目的： 保证数据库效率 　　
在SQL语句中，要避免使用逻辑OR条件，因为Or会使索引失效，可以改成Union或多条SQL语句，如： 
Select 字段1  From 表1 where  字段1＝值1  and  （字段2＝值2  or  字段3＝值3 ）
 可以改成
 Select 字段1  From 表1  where  字段1＝值1  and  字段2＝值2
Union [All] 

 Select 字段1  From 表1  where  字段1＝值1  and  字段3＝值3 
4、避免使用 [Not] In逻辑(续)
然后考虑改成[Not] Exist，绝大部分[Not] In子查询可以改为能用到索引的[Not] Exist子查询，修改如下：
Select字段1，字段2  From 表1   where  字段1＝值1 and Exist （Select 字段2 From 表2 Where表2.字段2＝表1.字段2    and 表2.字段3＝值3）

5、联合索引使用结论:

联合索引实际上只对第一列和全部建立索引

1):查询条件中出现联合索引第一列,或者全部,则能利用联合索引.

2):条件列中只要条件相连在一起,以本文例子来说就是:

last_name=’1′ and first_name=’1′

与

first_name=’1′ and last_name=’1′

,无论前后,都会利用上联合索引.

3):查询条件中没有出现联合索引的第一列,而出现联合索引的第二列,或者第三列,都不会利用联合索引查询.

1、默认使用InnoDB引擎
2、字符集选择utf-8
6、选择合适的类型
6.1、用INT UNSIGNED存储IPV4地址，用INET_ATON()、INET_NTOA()进行转换，基本上没必要使用CHAR(15)来存储。
6.2、枚举类型可以使用ENUM，ENUM的内部存储机制是采用TINYINT或SMALLINT(并非CHAR/VARCHAR)，性能一点都不差，记住千万别用CHAR/VARCHAR来存储枚举数据。
6.3、还个早前一直在传播的“常识性误导”，建议用TIMESTAMP取代DATETIME。其实从5.6开始，建议优先选择DATETIME存储日期时间，因为它的可用范围比TIMESTAMP更大，物理存储上仅比TIMESTAMP多1个字节，整体性能上的损失并不大。只具体到天的就用date类型。

8、关于索引
【老叶观点】除了常见的建议外，还有几个要点：
8.1、超过20个长度的字符串列，最好创建前缀索引而非整列索引(例如：ALTER TABLE t1 ADD INDEX(user(20)))，可以有效提高索引利用率，不过它的缺点是对这个列排序时用不到前缀索引。前缀索引的长度可以基于对该字段的统计得出，一般略大于平均长度一点就可以了。
8.2、定期用 pt-duplicate-key-checker 工具检查并删除重复的索引。比如 index idx1(a, b) 索引已经涵盖了index idx2(a)，就可以删除 idx2 索引了。
8.3、有多字段联合索引时，WHERE中过滤条件的字段顺序无需和索引一致，但如果有排序、分组则就必须一致了。
9.2、多表JOIN时，要把过滤性最大(不一定是数据量最小哦，而是只加了WHERE条件后过滤性最大的那个)的表选为驱动表。此外，如果JOIN之后有排序，排序字段一定要属于驱动表，才能利用驱动表上的索引完成排序。
9.3、绝大多数情况下，排序的代价通常要来的更高，因此如果看到执行计划中有 Using filesort，优先创建排序索引吧。
9.4、利用 pt-query-digest 定期分析slow query log，并结合 Box Anemometer 构建slow query log分析及优化系统。


66、避免在整个表上使用count(*) ，它可能会将整个表锁住。
68、如果合适，用 GROUP BY 代替 DISTINCT。
75、使用索引字段和 ORDER BY 来代替 MAX。

笔记：

1、mysql实现行号ROW_NUMBER()
SELECT t.*,@rownum:=@rownum+1 AS rank
  FROM __articles t,(SELECT @rownum:=0) r

2、列表查总数

SELECT SQL_CALC_FOUND_ROWS * from table

SELECT FOUND_ROWS() as count