自己收集的一些数据库设计和使用的优化方法。
优化SQL语句
- 应避免全表扫描，首先应考虑在where和order by的列上建立索引
- 避免在where条件中进行null判断，且表的设计也应该尽量使用not null
《高性能MySQL》书中提到：MySQL难以优化引用可为null的查询，它会使索引、索引统计和值更加复杂。可为null的列需要更多的存储空间，还需要mysql内部进行特殊处理。可空列被索引后，每条记录都需要一个额外的字节，还能导致MyISAM中固定大小的索引变成可变大小的索引。
- 应避免使用
!=或<>操作符，MySQL只有对以下操作才使用索引：<、<=、=、=>、>、BETWEEN、IN、以及形如name LIKE 'Alan%'的LIKE(%放在后面，%放在前面的可以使用全文索引) - 应避免在
where中使用or来连接条件，否则会导致引擎放弃使用索引而进行全表扫描，可以使用union all进行联合查询 - 使用联合查询时，应尽量使用
union all代替union，因为union会对结果进行去重 in和not in也会导致全表扫描，对于连续的数值，应尽量使用between ... and ...- 避免在
where中对字段进行表达式操作和函数操作 - 避免在
where中使用参数，其也会导致全表扫描。因为SQL在执行时才能解析局部变量，因此在编译时无法建立索引，可以修改为强制使用索引：SELECT id FROM table FORCE INDEX(index_name) WHERE num=@num - 可以使用
exists代替in - 一张表的索引应尽量不多于6个，虽然索引可以加速查询，但同时也提高了索引的维护成本，降低插入数据及更新数据的速度
- 尽可能避免更新聚集(clustered)索引数据列，因为操作聚集索引会消耗相当大的资源（聚集索引、非聚集索引及主键）
- 只含数值信息的字段尽量使用数字型字段，以提高查询和连接的性能
- 使用
varchar代替char - 查询时只查询需要的字段，而不是使用
* - 当连接多个表时，使用
as设置表的别名并使用别名访问列，如：SELECT t1.name, t2.class_name FROM table1 AS t1 JOIN table2 AS t2 ON t1.class_id=t2.id - 尽量少用子查询
- 不要超过5个以上的
JOIN，可以使用中间变量或临时表存放中间结果 - 统计行数时使用
COUNT(*) - 尽量使用
>=代替> - 通过将不需要的记录在
GROUP BY之前过滤掉 - 使用慢查询日志查看慢查询的语句
- 使用
explain分析SQL语句的执行计划 - 使用
LIMIT查询只需要的数据 - 建立索引
index(a,b,c)，存储引擎不能使用索引范围条件右边的列作为索引，例如：select * from where a='Alan' and b>25 and c='dev'，此时，只有a和b使用到索引 - 尽量只访问索引列的查询，避免
select * is null或is not null也会无法使用索引- 隐式转换：
select * from test where name=123，此时不会使用索引，应为name加上单引号 - 少用
or
如何建立索引
- 每个表都需要主键索引
- 数据量超过300的表应该建立索引
- 对经常出现在where条件中或order by中的字段建立索引
- 经常需要与其他表进行连接的表，在其连接字段上建立索引
- 索引应该建立在数据量小的字段
- 频繁进行数据操作的表不应建立过多索引
- 复合索引应注意前缀原则
慎用
NULL!=或<>- 形如
name LIKE %Alan的LIKE IN和NOT IN*- 子查询