mysql 优化方案整理

一、数据库结构的设计

为了保证数据库的一致性和完整性,尽可能的降低数据的冗余,数据的完整性容易得到保证,提高了数据吞吐速度,保证了数据的完整性。不要用自增属性字段作为主键与子表关联。不便于系统的迁移和数据恢复。对外统计系统映射关系丢失

表的设计具体注意的问题:

1、数据行的长度不要超过8020字节,如果超过这个长度的话在物理页中这条数据会占用两行从而造成存储碎片,降低查询效率。
2、能够用数字类型的字段尽量选择数字类型而不用字符串类型的(电话号码),这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接回逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。

3、对于不可变字符类型char和可变字符类型varchar 都是8000字节,char查询快,但是耗存储空间,varchar查询相对慢一些但是节省存储空间。在设计字段的时候可以灵活选择,例如用户名、密码等长度变化不大的字段可以选择CHAR,对于评论等长度变化大的字段可以选择VARCHAR。

4、字段的长度在最大限度的满足可能的需要的前提下,应该尽可能的设得短一些,这样可以提高查询的效率,而且在建立索引的时候也可以减少资源的消耗。

二、查询的优化

1、不要过多地使用通配符如SELECT * FROM T1语句,要用到几列就选择几列如:SELECT COL1,COL2 FROM T1;

2、SARG的定义:用于限制搜索的一个操作,因为它通常是指一个特定的匹配,一个值的范围内的匹配或者两个以上条件的AND连接。形式如下:
列名 操作符 <常数 或 变量> 或 <常数 或 变量> 操作符 列名
列名可以出现在操作符的一边,而常数或变量出现在操作符的另一边。Name=’张三’ and 价格>5000

3、应尽量避免在 where 子句中对字段进行 null 值判断

可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:
select id from t where num=0

4、应尽量避免在 where 子句中使用!=或<>操作符

5、应尽量避免在 where 子句中使用 or 来连接条件

可以这样查询:
select id from t where num=10
union all
select id from t where num=20

6、in 和 not in 也要慎用

对于连续的数值,能用 between 就不要用 in 了:
select id from t where num between 1 and 3

7、尽量避免在索引过的字符数据中,使用非打头字母搜索。

SELECT * FROM T1 WHERE NAME LIKE ‘L%’

8、应尽量避免在 where 子句中对字段进行表达式操作 最右原则

任何对列的操作都将导致表扫描,它包括数据库函数、计算表达式等等,查询时要尽可能将操作移至等号右边。

9、应尽量避免在where子句中对字段进行函数操作

10、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算

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

12、很多时候用 exists是一个好的选择:

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

14、避免使用不兼容的数据类型。例如float和int、char和varchar、binary和varbinary是不兼容的。数据类型的不兼容可能使优化器无法执行一些本来可以进行的优化操作。

15、充分利用连接条件,在某种情况下,两个表之间可能不只一个的连接条件,这时在 WHERE 子句中将连接条件完整的写上,有可能大大提高查询速度。

16、能用DISTINCT的就不用GROUP BY

17、尽量不要用SELECT INTO语句。语句会导致表锁定,阻止其他用户访问该表。

18、“水可载舟,亦可覆舟”,索引也一样。索引有助于提高检索性能,但过多或不当的索引也会导致系统低效。因为用户在表中每加进一个索引,数据库就要做更多的工作。过多的索引甚至会导致索引碎片。

19、尽量全值匹配

20、最佳左前缀法则 非聚索引

21、不在索引列上做任何操作

22、范围条件放最后

23、覆盖索引尽量用

24、不等于要甚用

25、自定定义为NOT NULL

26、字符类型加引号

27、OR改UNION效率高

发表评论

您的邮箱地址不会被公开。 必填项已用 * 标注

滚动至顶部