MySQL优化(一)

2/13/2017来源:SQL技巧人气:3983

MySQL优化(一)

1. MySQL优化概述

有些系统无法使用memcache作为缓存服务器来提升系统的执行效率。

 

优化的思路:

Ø 存储层(数据): 存储引擎、列类型、范式规范

Ø 设计层(单台服务器): 索引、缓存、分区(分表)

Ø 架构层(多台服务器): 读写分离(主从复制)

Ø sql语句层:     更合适的sql语句

 

2. 存储引擎

 2.1什么是存储引擎

MySQL中的数据是通过各种不同的技术(格式)存储在文件(或者内存)中的。技术和本身的特性就称为"存储引擎"。

每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的存储引擎,你能够获得不同的速度或者功能,从而改善你的应用的整体功能

 2.2 MySQL支持的存储引擎的种类

使用: show engines;

 

 

查看数据库中表的存储引擎。、

Show table status from 库名;

 

 2.3 MyISAM引擎

  2.3.1创建基于MyISAM引擎的数据表

创建表SQL:

CREATE TABLE user1(

  uid INT AUTO_INCREMENT PRIMARY KEY,

  uname VARCHAR(20) NOT NULL

)ENGINE=MYISAM DEFAULT CHARSET=utf8;

 

  2.3.2文件结构

 

  2.3.3 MyISAM数据存储顺序

 

  2.3.4 MyISAM数据压缩

随着数据库中的数据量变大,会使SQL的效率降低,如果我们将数据表文件进行压缩,减小文件尺寸,就能够提升SQL效率。

 

 

在命令行(cmd.exe)中执行该命令

命令格式: myisampack.exe  数据库文件路径\表名;

 

压缩之前:

 

 

执行压缩:

 

 

压缩之后的效果:

 

 

重建索引:myisamchk.exe

 

 

命令格式: myisamchk.exe  -rq  数据库文件路径\表名;

 

 

在实际项目中有一些表是建立好之后就不会发生改变了,这样的表就非常适合进行压缩。

例如:  省市区无限分类表

       京东商城的商品分类表

 

解压数据表:myisamchk.exe

命令格式: myisamchk.exe  --unpack  数据库文件路径\表名

 

 

解压缩之后索引文件还可以继续使用。

 

 2.4 InnoDB引擎

  2.4.1创建基于InnoDB的数据表

CREATE TABLE user2(

  uid INT PRIMARY KEY,

  uname VARCHAR(20) NOT NULL

)ENGINE=INNODB DEFAULT CHARSET=utf8;

 

  2.4.2 InnoDB文件结构

 

 

 

任何库中的InnoDB数据表的数据和索引文件都存放在该文件中。

  2.4.3为InnoDB引擎设置独立存储空间

 

两种方式修改:

临时修改:SET GLOBAL innodb_file_per_table=1;

 

永久修改: 修改my.ini文件

 

 

  2.4.4 InnoDB数据存储顺序

 

所以在执行插入操作时,InnoDB效率低于MyISAM效率

 

 2.5 MyISAM与InnoDB对比总结

  2.5.1 MyISAM和InnoDB对比

 

 

  2.5.2如何选择MyISAM、InnoDB引擎

1) 如果项目对数据的完整性要求不高,偏重SQL的执行效率就选择MyISAM。例如:CMS(内容管理系统), 论坛, 微博,  QQ空间。

2) 项目对数据的完整性要求非常高,必须使用InnoDB。例如:银行系统,财务系统,ERP(企业资源计划),OA, 网上商城。

 2.6 其他存储引擎介绍

  2.6.1 Memory引擎

数据置于内存的存储引擎,拥有极高的插入,更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在Mysql重新启动时丢失

  2.6.2 archive引擎

归档存储引擎,只支持数据的查询和写入。

经常用于存储日志等相关信息。

 

 

3. 列类型优化

 3.1 为什么要优化列类型

不同的数据类型所用的存储空间不一样,尽量选择占用空间下的类型。

不同的数据类型,SQL的执行效率是不一样的。

 3.2 整型数据优化

 

   1) 人的年龄(unsigned tiny int)、狗的年龄(tinyint)、乌龟的年龄(unsigned smallint)

   2) 京东商城的商品类别id(unsigned smallint)

   3) 华为erp系统的用户id(mediumint)

   4) 优酷会员id(unsigned int)

   5) 新浪的新闻id(int), 会使用备份服务器,来存储之前的数据。

 3.3 字符串类型优化

Varchar、char、text

Varchar: 可变类型。 Varchar(20)  存储:abc   实际占用长度3字节。

Char: 固定长度。 Char(20)    存储:abc     实际占用20字节。

 

Char的SQL效率更高,varchar效率低,占用空间小

 

手机号: char(11)

座机号: 3位区号-8位号码    4位区号-8位号码    4位区号-7位号码  char(12)

用户密码: md5  char(32)。  

用户名: varchar(30)

文章标题: varchar(30)

文章摘要: varchar(255)

 3.4 时间类型优化

1) date  datetime 等

2) unsigned int

经常参数预算的时间类型,建议使用整型。

用户注册时间:   datetime  

用户最后登录时间: unsigned  int

 

unix_timestamp()   将字符串日期转换为unix时间戳

from_unixtime()     将unix时间戳转换为字符串日期

 

 

 

 3.5 枚举类型与集合类型(复合类型的数据)

Enum(枚举): 单选。

Set(集合):  多选。

 

能够使用枚举或者集合,一定不要使用字符串。

 

Sex: 使用枚举。

Hobby: 使用集合。

 

在底层,枚举和集合是整型保存。

 3.6 ip类型数据的存储

ip: 202.145.178.196   最长的ip

使用函数将字符串转为整型:

 

mysql: inet_aton(ip) ip转整型   inet_ntoa(数字) 整型转ip

php:   ip2long(ip)  ip转整型   long2ip(数字)   整型转ip

 

 

 

封用户名:

封IP :使用inet_aton将ip地址转为整型保存。

 

 3.7尽量使用 not null

除非你有一个非常特殊的需求,否则一定要加 not null属性。 允许空比非空占用的空间更大,允许空字段在执行sql时,会执行更复杂的运算。

最好再加上  default。

 3.8 字段类型使用总结

1) 整型: 在满足需求的情况下,尽量选择小类型。

2) 能用整型就不用字符串: 枚举、集合来代替字符串,保存ip地址时将字符串转为整型

3) 在使用字符串时,固定长度一定选择char;可变长度,尽量减小长度。

4) 时间类型,参与运算比较多的采用int,不参与运算的采用date、datetime等。

Unix_timestamp() 将字符串时间转为时间戳

From_unixtime() 将时间戳转为字符串时间

5) 尽量使用 not null。

 

4. 设计范式

人们在长期设计数据库工作中总结出来的数据库设计的原则。

 4.1 第一范式(确保每列保持原子性)

示例:

id

name

passwd

tel

email

addr

1

zs

123

010-87563233

[email protected]

北京市昌平区西三旗建材城西路89号

2

ls

123

029-87463829

[email protected]

陕西省西安市碑林区兴庆路67号

 

问题: 统计全国所有省份的用户数量。

 

更好的方法就是讲  省  市  区 独立成单独的字段

id

name

passwd

tel

email

prev

city

area

addr

 

 

 

 

 

 

 

 

 

 

 

 4.2 第二范式(确保表中的每列都和主键相关)

 

 

问题:商品名称、单位、价格这三个字段是不需要的

      客户、所属单位、联系方式这三个字段,应该使用客户编号来代替。

 

客户、所属单位、联系方式三个字段无法和主键联系起来

 

 

Order表中: 使用 订单编号和商品编号作为复合主键

 

 

 

 4.3 第三范式(确保每列都和主键列直接相关,而不是间接相关)

 

 

 

客户表:

cid

cname

ctel

cemail

..

 

 

 

 

 

 

 

 

 

 

 

商品表:

gid

gname

price

unit

...

 

 

 

 

 

 

 

 

 

 

 

订单表:

oid

C_id

G_id

num

 

 

 

 

 

 

 

 

 

 4.4 逆范式(违反三范式所定义的原则)

在实际项目中,设计数据表时,可以根据用户操作习惯,系统的实际要求来违反三范式的设计原则。

 

id

name

passwd

tel

email

prev

city

area

addr

 

 

 

 

 

 

 

 

 

 

tel字段不是原子性的,用户在实际操作中必须看到“区号-主号”的形式。

 

 

购物车:

id

c_id

g_id

num

gname

price

 

 

 

 

 

 

 

 

 

 

 

 

 

增加了  gname、price字段,让SQL查询由原来的多表操作变为单表操作。 单表操作快于多表操作。

 

假设购物车表中有 1000万条数据,商品表中有1000万条数据。

 

假设购物车表中300条数据,商品表中有300条数据。

 

 4.5 总结

1) 新手阶段时,严格按照三范式的原则。

2) 高手阶段,根据经验来设计数据表结构。