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 就可以防止排序。
	例如：	

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
	如果出现数据库崩了，就可以通过时间或者位置恢复。（需要看增量备份日志）