首页/应用软件/内容

Mysql数据库优化的办法总结(必看)

应用软件2024-01-30 阅读()
[摘要]本篇文章给大家带来的内容是关于Mysql数据库优化的方法总结,有一定的参考价值,有需要的朋友可以参考一下,希望对你有所帮助。学无止境,数据库优化分为方方面面,在这里,我进行了比较全的总结,分享给正在...
本篇文章给大家带来的内容是关于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(北联网教程,专业提供视频软件下载)

第1页  第2页 

……

相关阅读