MySQL应用优化
MySQL应用优化
lonelydawn 发表于2年前
MySQL应用优化
  • 发表于 2年前
  • 阅读 4127
  • 收藏 476
  • 点赞 6
  • 评论 20

【腾讯云】买域名送云解析+SSL证书+建站!>>>   

摘要: 本文从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年出生的孩子信息,以此类推。

 

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

 

 

 

  • 打赏
  • 点赞
  • 收藏
  • 分享
共有 人打赏支持
粉丝 40
博文 52
码字总数 53354
评论 (20)
会爬树的蜗牛
很多错误额
Fenying
我记得MySQL表分区索引键不是必须是主键吗?
记录生活
讲的蛮好的,点赞
前世疯狂
“(8).不要使用count(id),应该是count(*) ” 这句是不是说反了?
gm100861
第三条错误,第五条错误.语句都运行不了. 还有使用count列会使索引失效,你有试过吗?我试了,不失效的.
杨武兵
总结得比较全面,有一定的借鉴意义,但是有些细节不够对;对于真正的使用者来说不但要知道结论,还要知道为什么?这样就不需要死记硬背了,因为很多结论性的东西并不一定正确,它跟很多因素相关,如果不知道原理死记硬背,结论必然是错误的。
eatnothing
mysql 5.6_innodb支持全文索引7
panda大侠
不要使用count(id),应该是count(*) 这句就是反的11
zmtwkw
tu
xforgchen
select * from t where name like '%a%';等价于select * from t where name>='a' and name<'b';???
xforgchen
select * from t where name like '%a%';等价于select * from t where name>='a' and name<'b';??? 若name = 'cccaccc'是否能查到?
Raynor1

引用来自“AngusXer”的评论

我记得MySQL表分区索引键不是必须是主键吗?
你说得对。
milin
用的最多的是count(1)
milin
分表那个语句学习了,有空儿试下
相信你自己

引用来自“前世疯狂”的评论

“(8).不要使用count(id),应该是count(*) ” 这句是不是说反了?
对的
透过玻璃的光
InnoDB 对于读也要分情况,不是说完全就低于MyISAM.
而且就单表锁这一特性,MyISAM就已经不适用大部分业务场景。
松吖
你试过吗
ylxs90
id是主键时,count(1) 和count(id)呢?
墨空云
概括的好全。
skyim
一句话,实践才能出真知,这些有实际操作过吗
×
lonelydawn
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: