如何进行mysql数据库的优化？ --慢查询定位 --索引详解 -- 定时备份

mysql数据库优化的常见方法:

1.表的设计要合理(满足3NF) 3范式
2.创建适当索引[主键索引|唯一索引|普通索引|全文索引|空间索引]
3.对SQL语句优化---->定位慢查询(explain)
4.使用分表技术(重点【水平分表，垂直分表】), 分区技术(了解)
5.读写分离,主从同步(配置)
6.创建适当存储过程，函数，触发器
7.对my.ini优化，优化配置
8.软件硬件升级

1NF

指表的属性(列)具有原子性， 即表的列的不能再分了
不能有重复的列

2NF

所谓2NF,就是指我们的表中不能有完全重复的一条记录（行）.一般情况下通过设置一个主键来搞定，而且该主键是自增的。

3NF(外键)

所谓3NF就是指，如果列的内容可以被推导(显式推导，隐式推导)出，那么我们就不要单独的用一列存放。

如何分析有问题的sql

怎么找？（如何定位慢查询）

开启慢查询的两种方法:

• 启动时，这样启动
  cmd>bin/mysqld.exe --safe-mode --slow-query-log
• 在my.ini的[mysqld]下添加一下代码并且***重启***
  log-slow-queries = D:/server/mysql/mysqlslowquery.log(注意斜杠)
  注：mysql5.6版本slow-query-log-file
  long_query_time = 1 指定超过1秒算慢查询

什么时候使用慢查询?(开启慢查询牺牲sql的执行效率)

如何使用慢查询?

1. 系统上线之后,将慢查询开启一个星期.
2. 当你认为系统所由于数据增多导致系统执行缓慢.再开启慢查询找到执行慢的sql语句,然后在优化它.

如何分析一个sql语句的问题-explain 工具

explain sql语句\G

mysql的变量查询

mysql>show variables;
mysql>show variables like ‘%xxxx%’;
show tables like ‘数据表名’； //查询一个数据表是否存在

索引

主键索引

增加主键
alter table 表名 add primary key(字段)
主键索引的特点
1)一个表最多只能有一个主键
2)一个主键可以指向多列(复合主键)
3)主键索引的效率是最高，因此我们应该给id,一般id是自增.
4)主键索引列是不能重复，也不能为null

唯一索引

添加唯一索引
create unique index 索引名 on 表名(字段名)
alter table 表名 add unique (字段名)
唯一索引的特点
1)一张表可以有多个唯一索引
2)唯一索引不能重复，但是如果你没有指定not null ，唯一索引列可以为null,而且可以有多个.
3)什么时候使用唯一索引，当某列数据不会重复，才能使用
唯一索引效率也很高，可以考虑优先使用。

普通索引

特点
1)一张表中可以有多个普通索引，一个普通索引页可以指向多列
2)普通索引列的数据可以重复
3)效率相对而言低.

删除索引

drop index 索引名 on 表名
alter table 表名 drop index 索引名；

索引的查询

desc 表名
show keys from 表名\G
show index from 表名\G
show indexes from 表名\G

索引的注意事项

索引的原理

B+tree

sql语句的优化和正确使用索引

• 对于创建的多列(复合)索引，只要查询条件使用了最左边的列，索引一般就会被使用
• 对于使用like的查询，查询如果是‘%aaa’‘_aa’ 不会使用到索引，‘aaa%’会使用到索引
• 如果条件中有or，则要求or的所有字段都必须有索引，否则不会使用索引
• 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引
• 有些情况下，可以使用连接来替代子查询。因为使用join，MySQL不需要在内存中创建临时表
  -管理员在导入大量数据，可以先关闭索引，再开启索引，提高速度

• 如何选择存储引擎

  如何选择的原则
  (1)MyISAM:默认的MySQL存储引擎。如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性要求不是很高。其优势是访问的速度快。(尤其适合论坛的帖子/信息表/新闻/商品表表)
  (2)InnoDB：提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM，写的处理效率差一些并且会占用更多的磁盘空间(如果对安全要求高，则使用innodb)。[账户，积分,余额]

• 如何选择正确的数据类型

  (1)在满足需求的情况下尽量选择小的类型.
  (2)在精度要求高的应用中，建议使用定点数来存储数值，以保证结果的准确性。decimal 不要用float.
  (3)对存储引擎是MyISAM的表，要定时碎片整理
  optimize: 该命令可以使表中的数据彻底从数据文件中删除.

分表技术和分区技术

当一个表很大很大(超过千万级的表)时，我们可以考虑添加索引，当索引都解决不了这个问题时，我们可以使用分表技术或者分区技术搞定。
水平分割表
1.按时间（按年、月分表）
2.按区间范围（id每1000000放一张表），总量是稳定的就是用按区间分表，暴风增长的表，不适合
实例：按时间分表的订单表
垂直分表
所谓垂直分割，就是把表的某个大字段(而且很少查询)，单独的取出，放入到另外一个表，并通过id关联.
分区
< 限制 >
1.只能对数据表的整型列进行分区，或者数据列可以通过分区函数转化成整型列
2.最大分区数目不能超过1024
3.不支持外键
4.不支持全文索引（fulltext）
5.按日期进行分区非常适合，因为很多日期函数可以用。但是对于字符串来说合适的分区函数不太多

Mysql 数据库如何分区、分表

分表可以通过三种方式：Mysql 集群、自定义规则和 merge 存储引擎。
分区有四类：

• RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。
• LIST 分区：类似于按 RANGE 分区，区别在于 LIST 分区是基于列值匹配一个离散值集合中的某个值来进行选择。
• HASH 分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含 MySQL 中有效的、产生非负整数值的任何表达式。
• KEY 分区：类似于按 HASH 分区，区别在于 KEY 分区只支持计算一列或多列，且 MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

数据库my.ini配置(一般是挑战服务器）

数据库配置最重要的参数就是内存，如果主要用的是innodb引擎，需要把下面两个参数调大

　innodb_additional_mem_pool_size = 64M
　innodb_buffer_pool_size =1G

对于myisam，需要调整key_buffer_size
当然调整参数还是要看状态，用show status语句可以看到当前状态，以决定改调整哪些参数
在my.ini修改端口3306，默认存储引擎和最大连接数
max_connetions 100 =>调整到 2500
query_cache_size 100m 缓存

定时备份

备份： mysqldump -u用户名 -p密码 数据库名 >备份的路径/文件.sql
mysql5.6以上需要加参数:mysqldump --no-defualts
恢复：source 备份数据库文件路径
定时任务：
(1)可以直接写一个mytask.bat 文件，双击即可备份。在该文件中写入指令

@echo off
set "Ymd=%date:~,4%%date:~5,2%%date:~8,2%"
mysqldump -uroot -padmin ajaxdemo >d:/ajaxdemo_%Ymd%.sql
@echo on

(1)下面我们可以使用系统计划任务：计算机管理–>系统管理–>任务计划程序,创建定时任务，将写好的脚本mytask.bat添加进去