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年出生的孩子信息,以此类推。

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏bboysoul

给自己的主机测个速度

昨天有人问我,为什么他的国外服务器看有土鳖的视频那么卡,其实归纳一下有下面这么几点导致你的服务器速度不高

1062
来自专栏杨建荣的学习笔记

MySQL中的反连接(r12笔记第45天)

关于Oracle的半连接,反连接,我一直认为这是一个能讲很长时间的话题,所以在我的新书《Oracle DBA工作笔记》中讲性能优化的时候,我花了不少的笔墨做...

2695
来自专栏数据和云

巧用复合索引,有效降低系统IO

我们知道索引至关重要,合理的索引使用能够在很大程度上改善数据库的性能。然而很多人都会走入这样一个误区:走索引的SQL语句的性能一定比全表扫描好。真的是这样吗?今...

2979
来自专栏数据和云

DBA入门之路:察微知渐细致入微

在DBA的职业生涯中,要面临无数的艰难险阻、排忧解难,所以细致入微,严谨认真的风格必不可少。养成了察微知渐的习惯,才能在分析诊断故障时层剖缕析,直指核心;而我也...

2123
来自专栏大愚Talk

MySQL InnoDB引擎锁的总结

我们开的的各式各样系统中,系统运行需要CPU、内存、I/O、磁盘等等资源。但除了硬资源外,还有最为重要的软资源:数据。

1983
来自专栏别先生

增量数据,如果下次增量数据存在重复数据,如何解决。

1、如果增量数据,每次增量数据可能会存在增量数据,如何解决。思路,首先可以复制一个备份表,然后将主表中存在的数据,在备份表中进行删除,然后将备份表插入到主表,最...

751
来自专栏WindCoder

网易MySQL微专业学习笔记(十一)-MySQL业务优化与设计

这个系列属于个人学习网易云课堂MySQL数据库工程师微专业的相关课程过程中的笔记,本篇为其“MySQL业务优化与设计”中的MySQL数据类型相关笔记。

1041
来自专栏全栈工程师成长之路

深入浅出后端开发(MySQL篇)

38018
来自专栏大白虾谈架构

数据库主外建适用场景

995
来自专栏Java后端技术栈

MySQL数据库开发常见问题及几点优化!

MySQL数据库是被广泛应用的关系型数据库,其体积小、支持多处理器、开源并免费的特性使其在Internet中小型网站中的使用率尤其高。在使用 MySQL的过程中...

771

扫码关注云+社区

领取腾讯云代金券