记录下mysql调优&B树B+树在物理层面上的区别

虽然写了一年多web后台，由于低qps，我们的系统一直都是单进程单线程，总数据量也很小，所以导致对mysql性能要求极低。但是，昨晚和珺主同学聊了一晚上，从人生理想谈到技术优化，发现我对mysql性能调优完全是空白。今天就在网上学习了一下，认为sql性能调优的学习应该氛围两块内容：1.要点的熟悉；2.原理的探究。因为时间关系，今天就结合平时系统中的使用情况，对我能理解和接受的sql调优记录如下：

1. SELECT子句中避免使用 ' * '：这个显而易见了。

2. 减少访问数据库的次数：刚来这边，第一次做一个贼小的项目的时候，就犯过这个错误。能一次查的数据，就一次查出来，为了方便在内存中使用，可以设计成{key_id:  sql_model}格式，方便获取。

3. 尽量多使用COMMIT：commit当然会释放数据库的资源，但是，为了保证事务的一致性，要慎重考虑commit的位置。最好把commit封装成装饰器，对外使用不可见。

4. 用Where子句替换HAVING子句：

   HAVING 只会在检索出所有记录之后，才对结果集进行过滤。WHERE子句限制记录的数目。

5. 减少对表的查询：同2啦。

6. 使用表的别名(Alias)：这个是在join操作的时候，语句要写成

   table.column格式。如果只写column格式，会导致语法错误等。

7. 用EXISTS替代IN、用NOT EXISTS替代NOT IN：这个也是在join后，处理结果时使用。有时会根据join结果，去掉为空的字段之类的。（目前这个我遇到的少）

8. 用IN代替OR：对于大多数的数据库操作系统来说，IN 要比 OR 执行的快。所以如果可以的话，要将 OR 换成 IN。

9. 用索引提高效率：用索引很关键。B+树的叶子节点，记录了索引的数据

10. 避免在索引列上使用NOT ：WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描。且，联合索引中的一个成为where 子句，另一个不在，则联合索引不起作用。

11. 避免在索引列上使用IS NULL和IS NOT NULL

    对于单列索引，如果列包含空值，索引中将不存在此记录。对于复合索引，如果每个列都为空，索引中同样不存在此记录；如果至少有一个列不为空，则记录存在于索引中。

12. 需要当心的WHERE子句：带有DISTINCT、UNION、MINUS、INTERSECT、ORDER BY的SQL语句会启动SQL引擎。执行耗费资源的排序(SORT)功能。DISTINCT需要一次排序操作，而其他的至少需要执行两次排序。

13. 如果检索数据量超过30%的表中记录数.使用索引将没有显著的效率提高。在特定情况下, 使用索引也许会比全表扫描慢, 但这是同一个数量级上的区别. 而通常情况下,使用索引比全表扫描要块几倍乃至几千倍!

14. 优化GROUP BY：提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉。低效：

    SELECT JOB , AVG(SAL) FROM EMP GROUP by JOB HAVING JOB = 'PRESIDENT' OR JOB = 'MANAGER'。高效：SELECT JOB , AVG(SAL) FROM EMP WHERE JOB = 'PRESIDENT' OR JOB = 'MANAGER' GROUP by JOB

15. Select Count (*)和Select Count(1) 区别：

    如果表沒有主键(Primary key), 那么count(1)比count(*)快；

    如果有主键的話，那主键作为count的条件时候，即：count(主键)最快

    如果你的表只有一个字段的话那count(*)就是最快的

16. IS NULL 与 IS NOT NULL：

    不能用null作索引，任何包含null值的列都将不会被包含在索引中。

    任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。

17. Order by语句：

    所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。

     

【后记】最近两天，和亮亮聊到为什么B+树比B树快。只想到B+树有一层叶子节点的链表是不准确的。因为当年手撕B树B+树的时候，其实是没有真实的data这一层的。所以无法直观地感受到data给树的存储带来的影响。