Mysql数据库优化学习总结

      对于一个以数据为中心的应用，数据库的好坏直接影响到程序的性能，因此数据库性能至关重要。一般来说，要保证数据库的效率，要做好以下四个方面的工作：SQL语句及索引的优化、数据库表结构优化、系统配置的优化、硬件的优化。

一、SQL语句及索引的优化

①将经常要用到的字段设置为索引；

②不要轻易使用select *，仅列出所要查询的字段；

③尽可能使用varchar/nvarchar来代替char/nchar，因为首先变长字段存储空间小，可节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率会更高；

④尽量避免在列上做运算，这样会导致索引失效；

例如：我们想要将表tb_test中id大于100的数据记录中的age和name查找出来。

原语句：select age,name from tb_test where id/100 > 1;

优化后：select age,name from tb_test where id > 1*100;

⑤尽量避免对索引列使用函数调用；

例如：我们想要表tb_test中name为'lv',addr为'cq'的记录中的id和age查找出来。

原语句：select id,age from tb_test where concat(name,’ ‘,addr) = ‘lv cq’;

优化为：select id,age from tb_test where name=’lv’ and addr=’cq’;

⑥尽量避免在索引列上is null或is not null的使用； 

例如，我们想要将表tb_test中id大于等于“0”的记录中的age查找出来。

原语句：select age from tb_test where id is not null;

优化为：select age from tb_test where id >= 0;

⑦尽量避免在索引列上or的使用 

例如，我们想要将表tb_test中id等于101或102的记录中的age和name查找出来。

原语句：select age,name from tb_test where id = 101 or id = 102;

优化为：select age,name from tb_test where id = 101 union select age,name from tb_test where id = 102;

⑧尽量避免索引列在like的首字符使用通配符 

例如，我们想要将表tb_test中name匹配“zho”的记录中的id和age查找出来。

原语句：select id,age from tb_test where name like ‘%ho%’;

优化为：select id,age from tb_test where name like ‘zho%’;

⑨尽量避免复合索引的使用 
如果我们建立的索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引。

例如，我们在表tb_test上新建了如下索引：

create index idx4_tb_test on tb_test(id,name,addr);

以上索引idx4_tb_test相当于建立了index(id)、index(id,name)、index(id,name,addr) 这3个索引。在SQL语句的where条件中单独使用name或addr时不会使用到该索引，必须使用id时才会使用到该索引。

二、数据库表结构的优化

很多人将数据库设计范式作为数据库表结构设计的基准，但是我们除了遵循数据库设计范式外，我们还应该遵循以下原则：

　　数据库操作中最为耗时的操作就是 IO 处理，大部分数据库操作 90% 以上的时间都花在了 IO 读写上面。所以尽可能减少 IO 读写量，可以在很大程度上提高数据库操作的性能。

　　我们无法改变数据库中需要存储的数据，但是我们可以在这些数据的存储方式方面花一些心思。下面的这些关于字段类型的优化建议主要适用于记录条数较多，数据量较大的场景

　　数字类型：非万不得已不要使用DOUBLE，不仅仅只是存储长度的问题，同时还会存在精确性的问题。同样，固定精度的小数，也不建议使用DECIMAL，建议乘以固定倍数转换成整数存储，可以大大节省存储空间，且不会带来任何附加维护成本。对于整数的存储，在数据量较大的情况下，建议区分开 TINYINT / INT / BIGINT 的选择，因为三者所占用的存储空间也有很大的差别，能确定不会使用负数的字段，建议添加unsigned定义。当然，如果数据量较小的数据库，也可以不用严格区分三个整数类型。

　　字符类型：避免使用 TEXT 数据类型，其处理方式决定了他的性能要低于char或者是varchar类型的处理。定长字段，建议使用 CHAR 类型，不定长字段尽量使用 VARCHAR，且仅仅设定适当的最大长度，而不是非常随意的给一个很大的最大长度限定，因为不同的长度范围，mysql也会有不一样的存储处理。

      尽量使用 NOT NULL

　　NULL 类型比较特殊，SQL 难优化。虽然 MySQL NULL类型和 Oracle 的NULL 有差异，会进入索引中，但如果是一个组合索引，那么这个NULL 类型的字段会极大影响整个索引的效率。此外，NULL 在索引中的处理也是特殊的，也会占用额外的存放空间。

　　很多人觉得 NULL 会节省一些空间，所以尽量让NULL来达到节省IO的目的，但是大部分时候这会适得其反，虽然空间上可能确实有一定节省，倒是带来了很多其他的优化问题，不但没有将IO量省下来，反而加大了SQL的IO量。所以尽量确保 DEFAULT 值不是 NULL，也是一个很好的表结构设计优化习惯。

三、系统配置的优化

四、硬件优化