MySQL 优化总结

技术就是一层窗户纸,经常和技术好的人交流。

MySQL 数据库优化总结:

对 MySQL 优化是一个综合的技术,主要包括:
1,表的设计合理化(符合3NF)
2,对 SQL 语句本身进行优化
3,添加适当的索引(index)【五种:普通索引、主键索引、唯一索引、全文索引、空间索引】
4,分表技术:水平分割、垂直分割
5,读写[写:update/delete/add]分离
6,存储过程:可以提高速度、利于模块化编程

	1、可以提高速度:sql 语句在执行之前,需要事先编译好,而这个编译的过程是比较耗时的,而存储过程就是提前在数据库中编译好,程序直接调用即可。
	2、利于模块化编程
	3、缺点:移植性不好

7,对 mysql 配置优化 [my.ini配置最大并发数,调整缓存大小]
8,mysql 服务器硬件升级
9,定时清除不需要的数据,定时进行碎片整理(特别是存储引擎为MyISAM时)

一、表的设计合理化

1.1, 什么样的表才是符合 3NF(范式):

表的范式,首先符合1NF,再符合2NF,再符合3NF

1.2,三范式
1.2.1 第一范式:

表的列具有原子性,不可再分解,即列的信息,不能分解,只有数据库时关系型数据库,就自动满足第一范式。

数据库的分类:
	关系型数据库:mysql、Oracle、sql server
	非关系型数据库:面向对象、集合
	NoSql 数据库:MongoDB(面向文档)

1.2.2 第二范式:

表中的记录是唯一的,就满足 2NF ,通常我们设计一个主键来实现
主键:不含业务逻辑,比较稳定,一般自增长。

1.2.3 第三范式:

表中不要有冗余数据,就是说表的信息能够被推导出来,就不应该单独的设计一个字段来存放。即同一个表或表与表之间的字段冗余

1.3 设计经验:(可以适当违反三范式:)

如某些SQL语句十分复杂,已经严重影响性能,而增加一个冗余字段,就可以很好的解决性能的问题。即可字段冗余。
在表的 1 对 N 的情况下,为了提高效率,可能会在 1 的表中设计冗余字段,提高速度。

二、SQL 语句优化
问题点:如何从一个大项目中,迅速的定位执行速度慢的语句(定位慢查询),包括了增删改查语句。

2.1 通过 show status 命令了解各种 SQL 的执行频率

通过该命令可以知道MySQL数据库的一些运行状态(比如:当前MySQL运行的时间、一共执行了多少次select/update/delete、当前连接数)
常用命令:
	show status like 'uptime':查询当前MySQL数据库启动了多长时间,单位为秒。
	show status like 'com_select':可以知道执行了多少次 select 语句
	show status like 'com_insert':可以知道执行了多少次 insert 语句,update、delete 的以此类推
	show [session|global]  status like ....   :  [session|global]  默认是  session 会话,指取出当前窗口的执行,如果想看所有(从MySQL启动到现在,则应该 global)
	show status  like 'connections';:获取当前连接数
	show status  like  'slow_queries':显示慢查询次数
Windows系统 dos 窗口相关命令:
	netstat -an:在 cmd 命令窗口中输入该命令,可以查询已经连接当前计算机的 IP 地址
		不让别人连接:
			Linux 环境下:使用 kill 命令
			Windows 环境下:直接关掉对应的进程即可
	netstat -anb:显示连接你计算机的进程的进程号

2.2 定位执行效率较低的 SQL 语句(重点 select 语句)
慢查询定义:默认情况下 MySQL 认为 10 秒才是慢查询

① 定位慢查询:
	需要有一张数据量很大的表
② 修改 MySQL 的慢查询定义值:
	如:把慢查询定义的时间改成 1 秒,就会统计超过 1 秒的次数。然后运行一天后查询日志。
	查询当前慢查询的值:
		show  variables  like  'long_query_time'
	设置定义慢查询的值:
		set  'long_query_time' = 1
③ 如何把慢查询的 SQL 语句记录到日志中:
	在默认情况下,MySQL 不会记录慢查询,需要在启动 MySQL 的时候,指定记录慢查询的参数。
	1、在 my.ini 文件中指定设置启动参数:
		MySQL 5.5 :bin\mysql.exe--safe-mode --slow-query-log
		MySQL 5.0:bin\mysql.exe-log-slow-queries=d:/abc.log
	2、先关闭 MySQL 再启动,如果启动慢查询日志,默认把这个文件放在 my.ini 文件中记录的位置,#Path to the database root,datadir="C:/Documents and Settings/All  Users/Application Data/MySQL/MySQL  Server  5.5/Data"
	3,、执行耗时超过 1 秒的 SQL 语句,查看日志文件。

2.3 通过 explain 语句分析,mysql 是如何执行 SQL 语句的
explain:可以帮助我们在不真正执行某个 sql 语句时,就知道 mysql 怎样执行,这样利于我们去分析 sql 指令。

在这里插入图片描述

2.4 确定问题并采取相应的优化措施

常用 sql 语句小技巧:

1,大批量插入数据(mysql管理员)了解
	对于 MyISAM:
		alter table 表名 disable keys;    // 禁用索引
		loading data                              // insert 语句
		alter table 表名 enable keys;    // 启用索引
	对于 Innodb:
		1,将要导入的数据按照主键排序
		2,set unique_check = 0;        // 关闭唯一性校验
		3,set autocommit = 0;			// 关闭自动提交
2,优化 group by 
	在使用 group by 分组查询时,默认分组之后还会排序,可能会降低速度。
	解决措施:在 group by 后面增加一个order by null 就可以防止排序。
	例如:	

group by 小技巧

3,有些情况下,可以使用连接来代替子查询。因为使用 join,MySQL 不需要在内存中创建临时表。
	select * from dept,emp where dept.deptno = emp.deptno;      // 简单处理方式
	select * from dept left join emp on dept.deptno = emp.deptno;   // 使用左外连接

4,选择合适的存储引擎
	MyISAM:
		默认的 MySQL 存储引擎。如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性要求不是很高。其优势是访问速度快。(比如: bbs 中的发帖表)
		
	InnoDB:
		对事务要求高,保存的数据都是重要数据,我们考虑使用 InnoDB,比如 订单表,账号表。	但是对比 MyISAM,写的处理效率差一些并且会占用更多的磁盘空间。
		
	Memory:
		该存储引擎的数据没有保存到磁盘中,而是保存到内存中,当数据变化频繁,不需要入库,同时又频繁的查询和修改,我们考虑使用 memory
		
	问题: MyISAM 和 InnoDB 的区别:
		1,事务安全
		2,查询和添加速度
		3,支持全文索引
		4,锁机制
		5,外键 MyISAM 不支持外键,InnoDB 支持外键。
			外键约束:
				插入数据时如果存在外键列,则该字段插入的值必须在对应的外键表对应的列中存在这个值,否则插入失败。	
				删除:

各个数据库的引擎之间的特点:
各个

三、添加适当的索引(index)
索引包括以下五种:普通索引、主键索引、唯一索引、全文索引、空间索引。

3.1,添加索引

1,主键索引:
	建表时,把某个列设为主键的时候,则该列就是主键索引。主键索引不能为 null ,且不能重复。
		create table aaa(id int unsiged primary key auto_increment, name carchar(32) not null defaul '');
		这是 id 列就是主键索引
	创建表后添加:
		alter table 表名 add primary key(列名);
2,普通索引:
	一般来说,普通索引的创建,是先创建表,然后在创建普通索引。
	create index 索引名 on 表 (列);
3,全文索引:
	全文索引主要是针对对文本的检索,如文章。全文索引针对 MyISAM 存储引擎生效,InnoDB无效。
	创建全文索引:
		create table articles(
			id int unsigned auto_increment not null permary key,
			title varchar(200),
			body text,
			fulltext (title,body)
		) engine = myisam charset utf8; 
	
		insert into articles(title, body) values(
			('mysql tutonal', 'dbms stands for dataBase34351.......'),
			('mysql tutonalas', 'dbms stands for dataBase21212.......'),
			('mysql tutonaldada', 'dbms stands for dataBase123.......')
		);

	如何使用全文索引:
		错误用法:
			select * from articles where body like '%mysql%';			【这里不会使用全文索引】
			证明:explain select * from articles where body like '%mysql%';	
		正确的用法:
			select * from articles where match(title, body) anginst ('dataBase');
			证明:explain select * from articles where match(title, body) anginst ('dataBase');	
		说明:
			1,在 mysql 中 fulltext 索引值针对 myisam 生效
			2,只针对英文生效(sphinx(coreseek)技术支持中文)
			3,使用方法是 match(字段名。。) against('关键字')
			4,全文索引有一个“停止词”,因为在一个文本中创建索引是一个无穷大的数,因此对一些常用的词和字符就不会去创建,这些词称为停止词。
4,唯一索引
	当表的某一列被指定为 unique 约束时,这列就是一个唯一索引
	创建唯一索引:
		建表时:
			create table aa(id int primary key auto_increment, name varchar(32) unique);
		建表后:
			create unique index 索引名 on 表名 (列名);
			alter table aa add index。。。
	这时 name 列就是一个唯一索引,name 列可以为 null,并且可以有多个。但是如果是具体内容,则不能重复。

3.2,查询索引

有以下三种方式:
	desc 表名 【该方法的缺点是: 不能够显示索引名】
	show index from 表名;     或   show indexes from 表名;
	show keys from 表名;

3.3,删除索引

alter table 表名 drop index 索引名;
deop index 索引名 on 表名;
如果删除主键索引:
	alter table 表名 drop primary key;

3.4,修改索引

先删除再重新创建

3.5,索引的原理:
首先索引是有开销的,可以在 my.ini 文件中找到,例如建一张 emp 表

索引文件所在的位置:
#Path to the database root
datadir="C:Documents and settings/All Users/Application Data/MySQL Server 5.5/Data/temp"
每一张表对应有三个文件:
	emp.frm:记录表的结构(innodb存储引擎这一个文件,具体的数据对应的文件在与temp目录的同一目录下)
	emp.MYD:表的数据本身
	emp.MYI:表的索引文件(当创建索引的时候,该文件会变大很多,)

为什么创建索引之后,速度就会变快呢?

二叉树算法:
	先找出所有的数据,然后取出中间的数据,然后把小的数分在左边,左边第一个放的是这些小的数里面处于中间的那个数,以此类推,
	左边永远是小的数,右边永远是大的数。(向上取整、向下取整)
	索引文件,先查询数据的总条数,然后找到中间的那条记录并把这条记录对应的磁盘的具体物理地址,

索引的原理图

二叉树检索次数与检索的数据量的关系为:log2n (比如检索10次,检索的数据量就是2的10次方) 
BTREE 索引:

3.6 索引的使用注意事项:

索引的代价:		
	1,占用磁盘空间
	2,对 dml ( update delete insert ) 语句的效率影响(变慢)。因为更新数据之后,索引要重新维护即二叉树要重新维护。
创建索引的注意事项:
	1,较频繁的作为 where 语句的 条件 字段应该创建索引
	2,该字段的内容不是唯一的几个值(例如性别字段)
		更新非常频繁的字段不适合创建索引
		不会出现在 where 字句中字段不该创建索引
使用索引的注意事项:
	以下几种情况可能使用到索引:
		1,对于创建的联合索引,只要查询条件使用了最左边的列,索引一般就会被使用。
		2,对于使用 like 的查询,查询如果是 '%aaa' 不会使用索引,而 'aaa%' 会使用索引。
	下列情况将不会使用索引:
		1,如果条件中有 or,即使其中有条件带索引也不会使用。换句话说:就是要求所有字段都必须建立索引,所以我们建议尽量避免使用 or 关键字。
		2,对于多列索引,不是使用的第一部分,则不会使用索引。
		3,like 查询是以 % 开头,例如:like '%aaa' 不会使用索引,在 like 查询时,关键字的“关键字”,最前面,不能使用 % 或者 _ 这样的字符,如果一定要前面有变化值,则考虑使用 全文索引 sphinx
		4,如果列类型是字符串,那一定要在条件中将数据使用单引号包括起来。否则不使用索引。(添加时,字符串必须 '')
		5,如果 mysql 估计使用全表扫面要比使用索引快,则不使用索引。

3.7 查看索引使用的情况:

show status like 'Handler_read%';
handler_read_key:这个值越高越好,越高表示使用索引查询到的次数越多,
handler_read_rnd_next:这个值越高,说明查询效率越低。

四,分表技术:水平分割、垂直分割
问题点:当一个表有海量数据,怎么办?
4.1,水平分割
某个表数据量很大时,保留表的结构不变,创建成几个表,然后把大表的数据按照一定的规则,分配到新创建的表中,这就是水平分割。

要找到分表的标准,例如:QQ 可以使用 QQ 号;而像平安保险这种,可以按照时间分表。
我们在提供检索时,应该根据业务的需求,找到分表的标准,并在检索页面,约束用户检索的方式,并且分页。如果有大表检索的需求,也是少数。

分表规则:
  时间规则分表:缺点,数据不均匀(例如按照年限来分表,不同的年限注册的用户量差别很大)
  根据不同规则:例如:地点 --- 交易、日志、报表
  手机号码规则:135 158 130 等,查找方便
  取模拆分(一致性hash算法)数据均匀分配
  。。。。等等

示例:
使用表的自增id字段,进行水平拆分,例如分 3 张表,id%3 分到不同的表即可。
在这里插入图片描述

4.2,垂直分割
把某个表的某些字段,在查询时,并不是关键字段,但是这些字段的数据量很大时,可以把这些字段单独的放到另一张表中,从而提高查询效率,保持原有的关联关系即可。

关于网站和视频的存放:
	数据表中,一般只是存放图片或者视频的路径,资源是直接放在文件系统上的,往往配合独立的服务器。
数据库参数的配置:

五、读写分离
如果数据库压力很大,一台机器支撑不了,那么可以用 mysql 复制实现多台机器同步,将数据库的压力分散。

读写分离原理:
有一个主数据库,若干个从数据库, dml 语句全部由主数据库来执行,执行之后把数据同步到从库中,查询 语句全部由从库执行,具体由哪个从库执行,根据负载均衡来决定。

在这里插入图片描述

在这里插入图片描述

注意:主库的数据同步到从库存在延时的可能,如果对查询的数据实时性要求比较高,需要在查询的方法上加上 @Transactional 注解来保证数据的实时性。

六、存储过程

https://blog.csdn.net/qq_38946877/article/details/86654521

七、对 mysql 配置优化

1,最重要的参数就是缓存大小,我们主要用的 innodb 引擎,所以下面两个参数调的很大:
	innodb_additional_mem_pool_size = 64M
	innodb_buffer_pool_size = 1G
2,对于 myisam,需要调整 key_buffer_size
	当然调整参数还是要看状态,用 show status 语句可以看到当前状态,以决定改调整那些参数
3,修改 my.ini 文件的配置
	修改端口:3306
	默认存储引擎:
	最大连接数:max_connections=1000 				// 瞬间并发数

八、mysql 服务器硬件升级

九、定时进行碎片整理
如果数据库的存储引擎是 myisam,一定要记得定时进行碎片整理。因为该存储引擎的表在执行 delete 语句时,表本身数据对应的文件大小不会改变。

对于存储引擎是 MyISAM 的数据库,如果经常做删除和修改记录的操作,要定时执行 optimize table 表名;  或 mysql_query("optimize tables $表名"); 功能对表进行碎片整理。
	主从复制:insert into test01 select id,name from test01;
	日期类型要根据实际需要选择能够满足应用的最小存储的早期类型
	create table bbs (id int, con varchar(1.24), pub_time int);

已删除的数据进行恢复

十、数据库数据备份:

10.1,手动备份数据库(表)的方法:

在 dos 窗口中使用以下命令,备份出来的文件名任意,文件后缀任意。
mysqldump -u root -proot 数据库 [表名1 表名2] > 文件路径
	例如:把temp 整个数据库备份到 d:temp.bak
		mysqldump -u -root -proot temp > d:\temp.bak
	备份指定的表(可以多张)
		mysqldump -u root -proot temp dept > d:\temp.dept.bak        // proot 中的 root 是数据库密码

如何使用备份文件恢复我们的数据:
	在 mysql 数据库控制台中输入以下指令
	source d:\temp.dept.bak

10.2,使用定时器来自定义完成
把备份数据库指令,写入到 bat 批处理文件中,然后通过定时任务管理器定时调用 bat 文件( Linux环境下使用:crontab 命令)

bat 文件中的具体内容如下:
	C:\myenc\mysql5.5.27\bin\mysqldump -u root -proot temp dept > d:\temp.dept.bak        // bin 前面的部分是 mysql 的安装目录,后面的部分才是具体的命令。
执行时,直接双击 bat 批处理文件即可

注意:如果你的 mysqldump.exe 文件另有空格,则一定要使用 "" 双引号包括起来

把 mytask.bat 做成一个定时任务,并定时调用在 凌晨 2:00 调用一次。

Linux 环境下:编写一个 sh 脚本,使用 crontab () () () () () mytask.sh 命令

10.3,增量备份
定义:mysql 数据库会以二进制的形式,自动把用户对 mysql 数据库的操作,记录到文件中,当用户希望恢复的时候可以使用备份文件,进行恢复。

增量备份会记录(dml 语句,创建表的语句,创建和删除数据库),不会记录 select 语句。
记录的(a:操作语句本身,b:操作的时间,c:操作的位置 position)

案例:如何进行增量备份,和恢复。
步骤:
1,配置 my.ini 文件,或者 my.cof,启用二进制备份。
	[mysqld]
	log-bin=d:/binlog/mylog            // 这里指定把备份文件放在哪个目录下
2,启动 mysql 得到文件
	d:/binlog/mylog/mylog.index		// 索引文件,有哪些增量备份文件
	d:/binlog/mylog/mylog.000001    // 存放用户对数据库操作的文件
3,查看增量备份文件内容
	可以使用 mysql\bin\mysqlbinlog 程序来查看 备份文件的内容
	在 cmd 控制台中:进入mysql 的 bin 目录下,使用以下命令查看备份的文件 mysqlbinlog 备份文件路径
4,增量备份常用的命令:

在这里插入图片描述

在这里插入图片描述

5,如何在工作中将全备份和增量备份配合使用
	方案:每周一做一个全备份,mysqldump,启用增量备份,把过期时间设为 大于等于 7
	如果出现数据库崩了,就可以通过时间或者位置恢复。(需要看增量备份日志)
发布了16 篇原创文章 · 获赞 2 · 访问量 4240
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览