mysql 优化

一 Mysql的表优化

1. 定长与变长分开存放。

核心：核心常用的字段已建成定长int char time放在同一张表中，varchar text 变长的字段乙方在一张表中

1. 大字段单独存放。长文本text的字段应该单独存一张表 与主表通过ID关联
2. 字段冗余。当表中的数据较多且关联的表也很多的时候 数据要做必要的冗余以便提高查询的效率。

二 字段的设计原则

1字段类型的选择  整型> date time>enum char >varchar > blob text

Tiyint   1  2  与 char ‘1’

在排序的时候 Char类型需要经过 字符集 和 校对集（排序的规则）

Enum(‘男’  ‘女’) 底层也是用了整型与之对应的

2 字段长度的选择 够用就行

  Varchar(20)  和 varchar(2000)

区别：在硬盘的存储是一样的大小。根据实际的长度来存放，是对于内存来说，则不是。其时使用固定大小的内存块来保存值。简单的说，就是使用字符类型中定义的长度，即200个字符空间。显然，这对于排序或者临时表(这些内容都需要通过内存来实现)作业会产生比较大的不利影响所以如果某些字段会涉及到文件排序或者基于磁盘的临时表时，分配VARCHAR数据类型时仍然不能够太过于慷慨。还是要评估实际需要的长度，然后选择一个最长的字段来设置字符长度。

1. 避免使用NULL

空值''是不占用空间的,mysql中的NULL其实是占用空间的

NULL 其实并不是空值，而是要占用空间，所以mysql在进行比较的时候，，所以对效率有一部分影响，而且B树索引时不会存储NULL值的，

  所以如果索引的字段可以为NULL，索引的效率会下降很多。

空值（null）是不能参与任何计算，因为空值参与任何计算都为空

在统计过程中，这个count函数会自动忽略空值的数据。此时统计出来的就是有值的信息。 如果采用的是空字符''的数据，则这个函数会将其统计进去

对于可为空的具有默认值的属性列  AUTO_INCREMENT、timestamp,会自动添加默认值 

Mysql 语句的优化

查询作为查询条件字段应该创建索引

唯一性太差的字段不适合单独创建索引,即使频繁 

Select * from emp where sex=’男’

频繁更新字段，也不要定义索引。

不会出现在where语句的字段不要创建索引

 

总结:满处一下条件的字段，才应该创建索引

• 肯定在where条件经常使用
• 该字段的内容不是唯一的几个值
• 字段内容不是频繁变化
• 使用group by 分组查询是，默认分组后，还会排序，可能会降低速度，

在group by 后面增加 order by null 就可以防止排序.

explain select * from emp  group by deptno order by null;

• 有些情况下，可以使用连接来替代子查询。因为使用join，MySQL不需要在内存中创建临时表。

select * from dept, emp where dept.deptno=emp.deptno; [简单处理方式]

select * from dept left join emp on dept.deptno=emp.deptno;  [左外连接，更ok!]

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

应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null

最好不要给数据库留 NULL，尽可能的使用 NOT NULL 填充数据库.

备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用 NULL。

不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL 也包含在内），都是占用 100 个字符的空间的，如果是 varchar 这样的变长字段， null 不占用空间。

可以在 num 上设置默认值 0，确保表中 num 列没有 null 值，然后这样查询：

select id from t where num = 0

组合索引 与顺序有关 左前缀匹配原则

建立索引 a b c d 四个索引

 

 

 

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

我们知道，数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找（linear search），这种复杂度为O(n)的算法在数据量很大时显然是糟糕的，好在计算机科学的发展提供了很多更优秀的查找算法，例如二分查找（binary search）、二叉树查找（binary tree search）等。如果稍微分析一下会发现，每种查找算法都只能应用于特定的数据结构之上，例如二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上，但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织），所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。