数据库SQL优化

数据库设计

数据库设计是指对于一个给定的应用环境，构造最优的数据库模式，建立数据库及其应用系统，使之能够有效地存储数据，满足各种用户的应用需求，重要的是信息要求和处理要求。

数据库设计方法

• 试凑法，依靠设计人员的工作经验
• 规划化设计法，3NF
• 计算机辅助设计法。PowerDesigner

数据库设计阶段

1. 需求分析。信息要求和处理要求，自顶向下的结构化分析法
2. 逻辑结构设计。概念模型ER图
3. 概念结构设计。数据模型：关系和非关系型模型
4. 物理设计。存储安排和方法选择
5. 实施阶段。编写模式、装入数据

数据库SQL优化

1、创建索引。对于查询占主要的应用来说，索引显得尤为重要。如果不加索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。

2、复合索引。由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在多个列上创建复合索引的话将带来更高的效率。如果创建了area、age和salary的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。因此在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。

3、索引不会包含有NULL值的列。只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以在数据库设计时不要让字段的默认值为NULL。

4、使用短索引。对字符串列进行索引，如果可能应该指定一个前缀长度。例如有一个CHAR(255)的列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

5、排序的索引问题。mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

6、like语句操作。一般情况下不鼓励使用like操作，如果非使用不可，like “%aaa%” 不会使用索引而like“aaa%”可以使用索引。

7、不要在列上进行运算。例如select * from users where YEAR(adddate)<2023。将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此可以改成select * from users where adddate<‘2023-01-01’;

8、不使用NOT IN和<>操作。NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以NOTEXISTS代替，id<>3则可使用id>3 or id<3来代替。

Sql语句的优化

1、对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引

2、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如select id from t where num is null，最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库。不要以为 NULL 不需要空间。

3、应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。

4、应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，如将or查询修改为集合合并操作union

5、in 和 not in 也要慎用，否则会导致全表扫描

6、模糊查询可能会将导致全表扫描。例如like ‘%abc%’，若要提高效率，可以考虑全文检索。

7、应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。

8、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

9、在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

10、对于多张大数据量的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差。

11、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

12、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

13、任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

14、尽量避免大事务操作，提高系统并发能力。

15、尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。