MySQL使用及优化

貌似很久没有写东西了,今天内部简单分享了下MySQL的使用及优化,对比着最近使用的mongodb体会,贴出来一份了

MySQL 使用及优化

MySQL 与 MongoDB

MySQL 关系型数据库管理系统

MongoDB 是一个基于分布式文件存储的数据库.

文档型数据库

文档是键值对的组合,是处理信息的基本单位。文档数据库允许创建许多不同类型的非结构化的或任意格式的字段,与关系数据库的主要不同在于,它不提供对参数完整性和分布事务的支持。

关系型数据库

  关系型数据库是建立在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据

关系模型

  关系模型是用二维表格结构来表示实体及实体之间联系的数据模型。由关系数据结构、 关系操作集合和完整性约束三部分组成。

1. 关系数据结构

单一的数据结构 — 关系

  现实世界的实体以及实体间的各种联系均用关系来表示。

A. 基本关系 实际存在的表

B. 视图表 由基本表和其他表导出的表

C. 查询表 查询结果对应的表

数据的逻辑结构 — 二维表

  从用户角度,关系模型中数据的逻辑结构是一张二维表,每一列称为一个属性,每行成为一个元组,若某一个属性能唯一标识一个元组,则该属性称为候选码。如果有若干个候选码,可选择其中一个作为主码

2. 操作集合

1. 操作

查询: 选择、投影、连接、除、并、交、差

数据更新: 插入(insert)、删除(delete)、修改(update)

2. 集合操作。 操作的对象和结构都是集合

3. 常用的描述关系操作的语言SQL。具有关系代数和关系演算双重特点。

3. 约束

1)实体完整性

所有元组的唯一性。行不允许重复

2)参照完整性

引用关系的完整性。学生的系别不能是院系表中不存在的院系

3)用户定义的完整性

例如定义姓名不能为空等,性别男或女等

MongoDB 与 MySQL 概念对比

MongoDB    MySQL

Database         Database

Collection       Table

Document      row

Key                   column

 

关系规范化

规范化程度越高,数据的冗余和更新就越少,同时由于连接运算费时,规范化应根据具体情况权衡利弊。

目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。满足最低要求的范式是第一范式(1NF)。在第一范式的基础上进一步满足更多规范要求的称为第二范式(2NF),其余范式以次类推。一般说来,数据库只需满足第三范式(3NF)就行了。

(更多范式介绍 参考资料:http://baike.baidu.com/view/402020.htm)

第一范式:数据表每一列都是不可分割的原子数据项

第二方式:属性完全依赖于主键。

第三范式:在1NF基础上,任何非主属性不依赖于其它非主属性[在2NF基础上消除传递依赖]

 

MySQL 与 MongoDB 建表举例

1. 实现景区POI的编辑,基本信息,名称,经纬度,所属区域,地址,照片,简介,描述

MySQL

POI基本信息表 存储名称,经纬度,地址,所属城市ID,简介,描述

poi: ID name longitude latitude address cityId summary detail

create table `pois`(

Id  INT(11)  NOT NULL AUTO_INCREMENT,

name  VARCHAR(50) NOT NULL COMMENT ‘POI 名称’,

longitude  FLOAT NOT NULL DEFAULT 0 COMMENT ‘经度’,

latitude  FLOAT NOT NULL DEFAULT 0 COMMENT ‘维度’,

address  VARCHAR(200) NOT NULL DEFAULT ” COMMENT ‘地址’,

cityId  INT(11) COMMENT ‘城市ID’,

`summary`  VARCHAR(11) NOT NULL DEFAULT ”,

detail  TEXT,

primary key(id)

)

COMMENT=’POIS表’

ENGINE=MyISAM

COLLATE=utf8_general_ci;

国家省县三级行政区域表 存储行政区域

COUNTRY:  ID name

Province:  ID name countryId

City:  ID name provinceId

相册表 管理图片

Albums: ID poiid title summary image

 查询:

Select * from pois as p left join city as c on p.cityId = c.id

Left join province as pr on c.provinceId= pr.id left join country as co on co.id = pr.countryId

Select * from albums where poiid=8

MongoDB

区域集合 region

Id name parentId

POI集合

Mongoose.schema(

Id: Number,

Name: String,

Coord : [longitude, latitude],

regionName : [String],

regionId: [Number],

Address: String,

Summary: String,

Detail : String,

Albums: [{

Name: String,

Image: String,

Summary: String

}]

);

 查询:

db.pois.findOne({id: id}, function(err, poi){

// poi

});

MySQL 与 MongoDB区别

MySQL 关系型数据库管理系统

1. 支持事务处理

2. 支持级联查询

MongoDB 是一个基于分布式文件存储的数据库.

1. 面向集合存储,每个集合在数据库中都有一个唯一的标识名,并且可以包含无限数目的文档。 类似于关系数据库中的表

2. 模式自由

MongoDB 命令与SQL语法对比

SQL 集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能与一体

MongoDB命令的实现与MySQL比较相像,常用的增删改查功能基本相似。

常用的功能检索,排序,分组,过滤(包括通配符%_过滤,正则过滤),计算拼接,处理函数(文本,日期),聚集函数(AVG()、SUM()),子查询,联接表,组合查询等

参考资料:http://www.2cto.com/database/201201/116110.html

 

MYSQL几个高级特性、概念

1. 视图 虚拟的表 用来查询检索数据,对视图的更新也可以映射到具体表中数据的更新

Create view 视图名 as 常用的某个SQL筛选语句

2. 存储过程 (CALL 调用)

为以后的使用而保存的一条或多条MySQL语句的集合。类似于批处理文件。可以简化复杂的操作,复杂操作创建统一的流程。先创建再使用

3. 游标(cursor)

游标是一个存储在MySQL服务器上的数据库查询,它不是一条SELECT语句,而是被该语句检索出来的结果集,主要用于交互应用。Mongo 查询中的 it查看更多 也是一种游标应用

4. 触发器

某个表发生改动时自动触发。例如订购商品时会自动减掉库存

MySQL5增加(仅DELETE、 INSERT、 UPDAT语句支持触发),表才有触发器

eg:

CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num

INSERT INTO orders(order_date, cust_id) VALUES(Now(), 10001); order_num 由Mysql自动生成,并自动返回

eg:

CREATE TRIGGER testref BEFORE INSERT ON test1

  FOR EACH ROW BEGIN

    INSERT INTO test2 SET a2 = NEW.a1;

    DELETE FROM test3 WHERE a3 = NEW.a1;

    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;

  END

触发器 http://dev.mysql.com/doc/refman/5.1/zh/triggers.html

5. 事务处理

InnoDB支持。用来维护数据库的完整性,保证成批的MySQL操作要么完全执行,要么完全不执行。几个术语:

事务(transaction) 一组SQL语句

回退(rollback) 撤销指定SQL语句过程

提交(commit) 将未存储的SQL语句结果写入数据库表

保留点(savepoint) 事务处理中设置的临时占位符,可用用来发布回退

eg: 

SELECT * FROM orders; #有数据

START TRANSACTION;

DELETE FROM orders; #删除

SELECT * FROM orders;#为空

ROLLBACK;

SELECT * FROM orders;#有数据

MySQL 优化

1. 表结构

A. 存储引擎选择 MyISAM or InnoDB.

MyISAM 适用于大量查询的场合,并发读性能好,对于某个字段的写操作则会是整个表被锁定。

InnoDB 支持对行锁定,并发写性能好;支持事务操作, 适于交易等数据安全及准确性较高的领域;不支持全文索引

B. 存储编码的选择。utf-8, gb2312等,不同的编码占用字节不同。Utf-8比较通用, 默认占3字节, gb2312 适用中文网站,汉字存储占两字节。

C. 数据类型的选择

选择合适数据类型,可节约空间,加快索引

TINY INT/MEDIUM INT/INT,

CHAR/VARCHAR/TEXT  Text适合存储大量文本,比如文章内容等

FLOAT与DOCUBLE FLOAT占用空间少

TIMESTAMP是DATETIME存储的一半

IP存储为UNSIGNED INT, 选择一定IP段会变得方便,INET_ATON()把IP字符串转为UNSIGNED INT, INET_NTOA() 则可将INT类型转为IP字符串

ENUM枚举等

2. 索引

A. 每张表有一个ID主键,设置AUTO_INCREMENT。原因:整型的索引要比字符串的索引性能要好,检索更快

B. 合理使用索引,常用的搜索字段,排序字段要建立索引,可以加快查询,索引会增加额外的存储空间与操作。(适用mongodb 为需要的字段增加索引)

3. SQL优化

A. LIMIT 使用, 更新、查询、删除时,使用limit限定查询结果,可避免多余的扫表和误操作的危害如delete * (适用mongodb findOneAndUpdate(), findOneAndRemove(),  limit 使用)

B. 对指定的字段进行查询,避免select *。

一、可以节省带宽,减少不必要数据的返回;(适用Mongodb)

二、可以减少I/O次数。MySQL I/O操作是以page(block)方式,字段少了,每个page 就可以存更多的行。

三、提高缓存命中率。单page行数增加,page总数减少,可加快内存page切换与结果的匹配

 (适用mongodb,  MongoDB  $ 操作符, 可以选择指定的子文档及子文档的字段)

C. 减少join 查询。可适当的冗余。Join也会带来性能降低,根据适当场合允许一定数据冗余,减少join

D. 使用join 查询代替子查询。子查询有性能问题,大数据的表会查询会导致数据库服务器卡死

E. 少用or查询,效率低。(适用mongodb)

F.  Order by rand() 少用,严重影响性能,rand() 会增加CPU计算,也会进行排序。

可以自己去生产随机数

G. 使用其它工具对慢查询进行分析优化,如EXPLAIN statement,tools等

4. 编程操作

A. 批量的操作。 批量的查询,批量的更新,减少网络传输带来的时间开销,对于大数据表一次不要查询所有,可分成每次Limit 300 ~500,防止单次操作时间过长,影响其它操作进程

B. 尽早过滤,优先将可以大量快速对数据筛选的语句放到左边,某些数据可以先分页查出,然后再去查询其他表或join操作。

5. 高级优化

A. 配置文件参数的调整;根据业务需求对某些参数进行调整

B. 分表操作。(MongoDB 分片操作)

分表:垂直分表,将不常用字段单独分为一个表,提高主表的查询速度;水平分表,数据量过大时,可对数据分段处理,可避免文件存储的问题,同时能加快查询速度。

C . 使用MySQL集群;D. 硬件上的升级;CPU、内存、硬盘等扩展

(更多mysql优化 可参考资料: http://coolshell.cn/articles/1846.html )

MySQL 安全编程

1. 程序对数据入库要进行转义。防止被攻击。常见的单双引号,#等

1 //正常语句

2 $sql =”select * from phpben where user_name=’$username’ and pwd =’$pwd'”;

3 //利用的用户名是benwin 则用户名框输入benwin’#  密码有无都可,则$sql变成

4 $sql =”select * from phpben where user_name=’ benwin’#’ and pwd =’$pwd'”;

(更多参考资料 http://blog.csdn.net/heiyeshuwu/article/details/8024567 )

2. 数据库用户权限控制

总结

Mysql 与 MongoDB 所具备功能还是比较相似的,此外MySQL有Join功能,事务操作,比较侧重范式化设计,存储修改方便。MongoDB设计时偏重反范式化,每个文档尽量具备所有的属性,可以实现快速查询,存储时数据的同步正确就变得复杂。

 

4 thoughts on “MySQL使用及优化”

Leave a Reply

电子邮件地址不会被公开。 必填项已用*标注

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>