Mysql数据库优化

学无止境,数据库优化分为方方面面,在这里,我进行了比较全的总结,分享给正在工作或者学习的同行们。

数据库的优化分为以下七大方面:

1、表的设计要符合三范式适当的反三范式也可以);

2、添加适当的索引,索引对查询速度影响很大,必须添加索引(主键索引,唯一索引,普通索引,全文索引);

3、添加适当存储过程,触发器,事务等;

4、读写分离(主从数据库);

5、对sql语句的一些优化,(查询执行速度比较慢的sql语句);

6、分表分区(分表:把一张大表分成多张表。分区:把一张表里面的分配到不同的区域存储);

7、对MySQL服务器硬件的升级操作。

接下来我将具体讲解优化的方式。

一、三范式

第一范式:

原子性:表里面的字段不能再分割,只要是关系型数据库,就天然的自动满足第一范式

关系型数据库(有行和列的概念):mysql、sql server、Oracle、db2、infomix、sybase、postgresql,在设计时,先有库->表->字段->具体记录(内容):在存储数据时,要设计字段。

非关系型数据库(泛指nosql数据库):memcache、Redis、momgodb等。

第二范式:

一个表中没有完全相同的记录,通过一个主键即能解决

第三范式:

表中不能存储冗余数据

反三范式设计:

相册表
ID相册名称相册浏览量
1生活100
2工作照100
照片表
ID照片名称相册ID浏览量
1我的小狗149
2我的小猫151
3我的同事2100

如果要算一个相册的浏览量,我们可以在相册表中添加相册浏览量字段,浏览照片的时候同时更新相册浏览量。

二、开启慢查询

Mysql慢查询默认是关闭的,默认记录超过10秒的sql语句。

1.查看慢查询记录时间:

show variables like ‘long_query_time’;

2.修改慢查询时间:

set long_query_time=2;

3.通过如下的一个函数来进行测试

benchmark(count,expr)   函数可以测试执行count次expr操作需要的时间

三、建立索引

1、主键索引的特点:

(1)一个表中最多只有一个主键索引

(2)一个主键索引可以指向多个列

(3)主键索引的列,不能有重复的值,也不能有null

(4)主键索引的效率高。

2、唯一索引的特点:

(1)一个表中可以有多个唯一索引

(2)一个唯一索引可以指向多个列,

(3)如果在唯一索引上,没有指定not null,则该列可以为空,同时可以有多个null,

(4)唯一索引的效率较高。

3、普通索引:

使用普通索引主要是提高查询效率

4、全文索引

mysql自带的全文索引mysql5.5不支持中文,支持英文,同时要求表的存储引擎是myisam。如果希望支持中文,有两个方案,

(1)使用aphinx中文版coreseek (来替代全文索引)

(2)插件mysqlcft。

添加索引主要的问题:

(1)较频繁的作为查询条件字段应该创建索引,唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件,更新非常频繁的字段不适合创建索引

(2)不会出现在WHERE子句中字段不该创建索,索引是由代价的,虽然是查询速度提高了,但是,会影响增该删的效率。而且索引文件会占用空间。

四、分表、分区

垂直分表(内容主表+附加表):

内容主表:存储各种数据的一些公共信息,比如数据的名称,添加时间等,

可以使用多个附加表,附加表存储一些数据的独特的信息。

主要原因:是内容主表里面的数据访问比较频繁。

特点:表结构不同

水平分表:

将表数据存在不同的表中

特点:表结构相同

分区:

就是把一个表存储到磁盘不同区域,仍然是一张表。

基本的概念:

(1)Range(范围)–这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区。

(2)List(预定义列表)–这种模式允许系统通过预定义的列表的值来对数据进行分割。

(3)Hash(哈希)–这中模式允许通过对表的一个或多个列的Hash Key进行计算,最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。

(4)Key(键值)-上面Hash模式的一种延伸,这里的Hash Key是MySQL系统产生的。

分区表的限制:

(1)只能对数据表的整型列进行分区,或者数据列可以通过分区函数转化成整型列。

(2)最大分区数目不能超过1024。

(3)如果含有唯一索引或者主键,则分区列必须包含在所有的唯一索引或者主键在内。

(4)按日期进行分区很非常适合,因为很多日期函数可以用。但是对于字符串来说合适的分区函数不太多。

五、并发处理的锁机制

锁机制:在执行时,只有一个用户获得锁,其他用户处于阻塞状态,需要等待解锁。

mysql 的锁有以下几种形式:

表级锁:开销小,加锁快,发生锁冲突的概率最高,并发度最低。myisam引擎属于这种类型。

行级锁:开销大,加锁慢,发生锁冲突的概率最低,并发度也最高。innodb属于这种类型。

表锁的演示:

1.对myisam表的读操作(加读锁),不会阻塞其他进程对同一表的读请求,但会阻塞对同一表的写请求。只有当读锁释放后,才会执行其他进程的操作。

2.表添加读锁后,其他进程对该表只能查询操作,修改时会被阻塞。

3.当前进程,能够执行查询操作,不能执行修改操作。不能对没有锁定的表进行操作。

4.锁表的语法:

lock table 表名 read|write

5.也可以锁定多个表

6.对myisam表的写操作(加写锁),会阻塞其他进程对锁定表的任何操作,不能读写,

7.表加写锁后,则只有当前进程对锁定的表,可以执行任何操作。其他进程的操作会被阻塞。

 行锁的演示:

1.innodb存储引擎是通过给索引上的索引项加锁来实现的,这就意味着:只有通过索引条件检索数据,innodb才会使用行级锁,否则,innodb使用表锁。

2.开启行锁后,当前进程在针对某条记录执行操作时,其他进程不能操作和当前进程相同id的记录。

PHP里面有文件锁,在实际的项目中多数使用文件锁,因为表锁,会阻塞,当对一些表添加写锁后,其他进程就不能操作了。这样会阻塞整个网站,会拖慢网站的速度。


未经允许请勿转载:熊先生个人博客 » Mysql数据库优化

赞一个 (4)

评论 板你个凳

换个身份

取消评论
  1. #1
    访客
    访客 4周前 (07-09)回复
    今日,在上海举办的“爱奇艺夏日悦享会”上,爱奇艺副总裁、节目开发中心总经理姜滨透露,“我们从2018年开始,团队陆续输出了《偶像练习生》,青春年轻是偶像题材永恒追求的主题,我们一直朝着这个目标在努力。我们在明年将全新的打造偶像男团,会持续地输出青春有你这个IP。明年还将是制作团队的原班人马。http://www.authorstream.com/mcxjd/Podcasts/

分享:

支付宝

微信