本文从MySQL语句优化、引擎选择、数据库设计三个方面介绍如何进行MySQL应用优化

一、基本语句优化原则

(1).尽量避免在索引列上进行运算或函数操作，这样会导致索引失效

如:

select * from t where Year(d)>=2016;

可以优化为:

select * from t where d>='2016-01-01';

(2).使用join语句时，应用小结果集驱动大结果集。因为在join多表时，可能会导致更多的锁定和拥塞

(3).注意模糊查询时避免%%，%开头的查询条件会使索引失效

(4).仅列出需要查询的字段，这对效率没有影响，但会影响内存

如:

select * from t;

可以优化为:

select name from t;

(5).使用批量交互插入语句以节省交互
如:

insert into t(id,name) values(1,"a");

insert into t(id,name) values(2,"b"); 

可以优化为:

insert into t(id,name) values(1,"a"),(2,"b");

这里也有博友质疑，贴结果:

(6).limit的基数比较大时使用between

如:

select * from article order by id limit 100000,10;

可以优化为:

select * from article between 100000 and 100010 order by id;

这里需要注意的是，如果id不连续的话，使用between获得的数据量会少于预计的数据量。

(7).避免使用NULL,这样会使mysql先进行一次是否为NULL的判定

(8).(这里颇有争议，我自己测试并查阅一下资料修改如下。)

如果id作为非主键字段,不要使用count(id),而是count(*),因为id未作非空约束时，会先进行NULL值判定

id作为主键时，在效率上,count(id)>count(*),若id作为自增主键,count(id)的效率会更高

(9).不要做不必要的排序，尽量在索引中进行排序

 

二、Mysql的存储引擎分析

	MyISAM	Memory	InnoDB
用途	快读	内存数据	完整的事务支持
锁	全表锁定	全表锁定	多重隔离级别的行锁
持久性	基于表恢复	无磁盘I/O，无可持久性	基于日志的恢复
事务特性	不支持	不支持	支持
支持索引类型	B-tree/FullText/R-tree	Hash/B-tree	Hash/B-tree

在介绍存储引擎的选择原则之前，先介绍一下读写比。读写比，即读取和写入语句执行次数的比，一般理想的读写比在100:1左右。

当读写比达到10:1的时候，即认为其是以写为主的数据库。

(1).采用MyISAM引擎（关键是快读，最简版的MySQL数据库）

R/W>100:1,且update较少

并发不高，不需要事务

表数据量小，硬件资源差

(2).采用InnoDB引擎（功能完备的MySQL数据库)

R/W比较小，数据更新频繁

海量数据，高并发

安全性、可用性高

(3).采用Memory引擎

内存充足

对数据一致性要求不高

定期归档（将过时的历史数据存入文件系统）

 

最常用的两种引擎是MyISAM和InnoDB，MyISAM注重效率，InnoDB注重事务。

 

三、数据库设计

1、范式与反范式

在数据库理论发展的过程中，逐渐形成五大范式，从第一范式到第五范式，数据库冗余降低，但查询效率也会随之降低。

上世纪硬件设备并不发达，空间成本比较高，所以设计理念是提高范式等级，减少冗余，利用时间换取空间，平衡点基本落在第三范式上。那什么又是反范式呢？随着硬件设备的发展，空间成本大幅度降低，而更多的是对时间和效率的要求，所以范式等级可以适当降低，增加冗余，最低可把范式降到第一范式。

反范式示例，由于一条记录被分到多张表中进行记录，查询需要进行多表关联，当要查询的数据量很大时，连表查询的时间成本就会很高，更严重的情况会引起数据库服务器宕机。这时候就需要建立冗余表将数据集中到一个表中记录。冗余表一般符合低等级范式。如何减少冗余表的空间成本呢？一般是定期转储。将一段时间之前的数据从数据库服务器导出，存储到其他地方，这些数据应是现在无需使用的数据。

 

2.数据库分区

讲一个数据表的文件和索引分散存储在不同的物理文件中，这样在查找的时候就不需要在整个大文件中搜索，而在固定范围中查找。

假设要存储某一地区1900-2000年之间出生的孩子信息，按年份分区，代码如下:

create table child (

id int AUTO_INCREMENT,

name varchar(12) not null,

birth date not null,
primary key(id,birth)
) engine=innoDB partition by range (year(birth))

(partition foo01 values less than(1991), partition foo02 values less than(1992),

partition foo03 values less than(1993), partition foo04 values less than(1994),

partition foo05 values less than(1995), partition foo06 values less than(1996),

partition foo07 values less than(1997), partition foo08 values less than(1998),

partition foo09 values less than(1999), partition foo10 values less than(2000));

 

3.数据库分表

分表原理和分区类似，只不过分区是在不同文件中存储数据，而分表是将一张数据库表拆分成多张数据库表。

如：
 

create table child (

id int primary key AUTO_INCREMENT,

name varchar(12) not null,

birth date not null) engine=innoDB;

可分为:
 

create table child_xxxx(

id int primary key AUTO_INCREMENT,

name varchar(12) not null,

birth date not null) engine=innoDB;

 

child_xxxx代表出生年份，如child_1900保存1900年出生的孩子信息，child_1901保存1901年出生的孩子信息，以此类推。

 

总之，数据库应用设计还得根据具体的环境来选择适当的方案。空间和时间的平衡，根据需要具体情况来把握。