如何对数据库进行优化-购够乐网上商城对其进行分析优化,

1. 数据库设计优化

1.1 选择最合适的字段属性

关系型数据库可以支持大量数据的访问，但一般来说，表越小，执行速度越快。 因此，在新建表时，在满足我们业务需求的基础上，将字段的宽度设置得尽量小。

比如在定义邮政编码字段时，如果设置为CHAR(100)，显然给数据库增加了不必要的空间，甚至使用VARCHAR也是多余的，因为CHAR(6)可以很好的完成任务。 同样，如果TINYINT能够满足我们的业务需求，那么我们就不需要使用INT或者BIGINT了。

1.2 数据库索引

建立索引是提高数据库性能最常用的方法。 可以大大提高数据库查询的效率，尤其是当查询语句中包含MAX()、MIN()、ORDER BY等函数和语句时，性能提升更为明显。

通常情况下，索引应该建立在将用于JOIN连接、WHERE判断和ORDERBY排序的字段上。 尽量不要索引数据库中包含大量重复值的字段。 比如user表中的gender字段不适合建立索引（因为gender只有male和female两个值），在这样的字段上建立索引不会提高数据库查询的效率，反而可能降低数据库的性能。

索引越多越好，索引可以提高对应SELECT的效率，但是也会降低INSERT和UPDATE的效率，因为INSERT或者UPDATE会时不时的更新索引，所以需要如何建索引慎重考虑，视具体情况而定。 一张表的索引最好不要超过6个。 如果过多，则应考虑是否有必要在一些不常用的列上建立索引。

2.SQL语句优化

2.1 尽量避免使用子查询，可以改用JOIN链接查询

常用的关系型数据库都支持子查询如何对数据库进行优化，即使用SELECT语句创建一个查询结果，然后在另一个查询中将该结果作为临时表使用。 使用子查询可以一次完成多步SQL操作，也可以避免事务或表锁，写起来也更简单。 但是使用子查询MYSQL会在内存中为外层查询创建一个临时表，因此会降低查询效率。 这时候我们可以使用JOIN链接操作来代替子查询。

2.2 UNION 都可以满足业务需求，不用UNION

如果我们需要将两个或多个 SELECT 语句的结果合并为一个整体，我们可以使用 UNION 或 UNION ALL 关键字。 UNION（联合）和UNION ALL的作用是将多个结果组合在一起并显示。

两者的区别在于：

UNION 会自动压缩多个结果集中的重复结果，而 UNION ALL 会显示所有结果，无论是否重复。 所以当UNION ALL可以满足业务需求时，尽量使用UNION ALL而不是UNION。

2.3 WHERE子句尽量避免使用!=或运算符

在WHERE子句中使用!=或运算符，查询条件不会使用索引，进行全表查询。 即影响查询效率。

2.4 使用OR优化WHERE子句

通常我们可以使用 UNION ALL 或 UNION 来代替 OR 以获得更好的结果。 因为在 WHERE 子句中使用了 OR，所以不会使用索引。

例如:SELECT ID FROM TABLENAME WHERE ID = 1 OR ID = 2 ;
优化:SELECT ID FROM TABLENAME WHERE ID = 1 UNION ALL SELECT ID FROM TABLENAME WHERE ID = 2 ;

2.5 WHERE子句使用IN或NOT IN优化

IN和NOT IN也要谨慎使用，否则可能会造成全表扫描。

可以替换为：

方案一:BETWEEN AND替换IN
例如:SELECT ID FROM TABLENAME WHERE ID IN(1,2,3);
优化:SELECT ID FROM TABLENAME WHERE ID BETWEEN 1 AND 3;
方案二:EXISTS替换IN
例如:SELECT ID FROM TABLEA WHERE ID IN (SELECT ID FROM TABLEB ) 
优化:SELECT ID FROM TABLEA AS A WHERE ID EXISTS(SELECT 1 FROM TABLEB AS A WHERE B.ID = A.ID)
方案三:LEFT JOIN替换IN
例如:SELECT ID FROM TABLEA WHERE ID IN(SELECT ID FROM TABLEB) 
优化:SELECT ID FROM TABLEA AS A LEFT JOIN TABLEB AS B ON A.ID = B.ID

2.6 在WHERE子句中使用IS NULL或IS NOT NULL优化

如果在WHERE子句中使用了IS NULL或IS NOT NULL判断，则该索引将被丢弃，进行全表查询。

例如:SELECT ID FROM TABLENAME WHERE AGE IS NULL 
优化成AGE上设置默认值0，确保表中AGE没有NULL值，
优化：SELECT ID FROM TABLENAME WHERE AGE = 0

2.7 LIKE语句优化

一般来说，不推荐使用LIKE操作，尤其是数据量大的表。

例如:SELECT NAME FROM TABLEA WHERE NAME LIKE '%张%';不会使用索引
优化:SELECT NAME FROM TABLEA WHERE NAME LIKE '张%';会使用索引

2.8 避免在WHERE子句中对字段进行表达式操作

WHERE子句中=的左边尽量不要进行函数、算术运算或其他表达式操作，否则系统将无法正确使用索引。

例如:SELECT ID FROM TABLENAME WHERE ID/2 = 50 
优化:SELECT ID FROM TABLENAME WHERE ID = 50*2
例如:SELECT ID FROM TABLENAME WHERE substring(name,1,2) = '欧阳' 
优化:SELECT ID FROM TABLENAME WHERE LIKE '欧阳%'

2.9 不要使用 SELECT * FROM TABLENAME

在定义SQL语句字段列表替换“*”时，尽量避免返回无用的，使用特定的字段。

2.10 LIMIT页面优化

MYSQL数据库一般使用LIMIT来实现分页，但是当offset比较大的时候，LIMIT的效率会很低，导致查询超时。

如下SQL：
SELECT ID FROM TABLENAME LIMIT 1000,10   执行很快

SELECT ID FROM TABLENAME LIMIT 100000,10 执行很慢
优化方法：
方法一：SELECT ID FROM TABLENAME ORDER BY ID LIMIT 100000,10; 执行很快(因为用了ID主键做索引)
上述方法一是我们最常用的，但是如果表中的数据是千万级别的，即便使用方法一，查询速度可能还是比较慢，这时候我们可以把上一页ID的最大值作为查询条件来实现分页,如方法二。
方法二：SELECT ID FROM TABLENAME WHERE id > @MAXID limit 10;
@MAXID的值是上一页查询结果中ID的最大值。

2.11 EXISTS 代替 IN

SELECT ID FROM TABLEA WHERE ID IN (SELECT ID FROM TABLEB)

如上面的SQL，执行IN的时候，是在内存中遍历比较。 IN(SELECT ID FROM TABLEB)括号中的语句只执行一次，缓存TABLEB表中的所有ID字段，然后检查TABLEA表的ID是否与TABLEB表中的ID相同。 ID相等，如果ID相等，则将TABLEA表中的记录添加到结果集中，直到遍历完TABLEA表中的所有记录。

SELECT ID FROM TABLEA WHERE ID EXISTS(SELECT ID FROM TABLEA.ID= TABLEB.ID)

和上面的SQL一样，EXISTS查询就是遍历TABLEA中的数据。 TABLEA 中的每条数据都与 TABLEB 链接。 如果有返回结果，则将记录添加到结果集中。 因此如何对数据库进行优化，当TABLEB中的数据量远大于TANLEA时，EXISTS的效率要比IN好很多。 当TABLEA表中的数据和TABLEB表中的数据一样大时，IN和EXISTS的效率几乎是一样的。