mysql怎么进行优化_mysql优化什么方法
插入记录时，影响插入速度的主要是索引、唯一性校验、一次插入记录条数等。根据这些情况，可以分别进行优化。下面由学习啦小编为大家整理的mysql优化的方法，希望大家喜欢!
mysql优化的方法
一. 对于MyISAM引擎表常见的优化方法如下：
1. 禁用索引。对于非空表插入记录时，MySQL会根据表的索引对插入记录建立索引。如果插入大量数据，建立索引会降低插入记录的速度。为了解决这种情况可以在插入记录之前禁用索引，数据插入完毕后在开启索引。禁用索引的语句为： ALTER TABLE tb_name DISABLE KEYS; 重新开启索引的语句为： ALTER TABLE table_name ENABLE KEYS; 对于空表批量导入数据，则不需要进行此操作，因为MyISAM引擎的表是在导入数据之后才建立索引的。
2. 禁用唯一性检查：数据插入时，MySQL会对插入的记录进行唯一性校验。这种唯一性校验也会降低插入记录的速度。为了降低这种情况对查询速度的影响，可以在插入记录之前禁用唯一性检查，等到记录插入完毕之后再开启。禁用唯一性检查的语句为： SET UNIQUE_CHECKS=0; 开启唯一性检查的语句为： SET UNIQUE_CHECKS=1;
3. 使用批量插入。使用一条INSERT语句插入多条记录。如 INSERT INTO table_name VALUES(....),(....),(....)
4. 使用LOAD DATA INFILE批量导入当需要批量导入数据时，使用LOAD DATA INFILE语句导入数据的速度比INSERT语句快。
二. 对于InnoDB引擎的表，常见的优化方法如下：
1. 禁用唯一性检查。同MyISAM引擎相同，通过 SET UNIQUE_CHECKS=0; 导入数据之后将该值置1。
2. 禁用外键检查。插入数据之前执行禁止对外键的查询，数据插入完成之后再恢复对外键的检查。禁用外键检查语句为： SET FOREIGN_KEY_CHECKS=0; 恢复对外键的检查语句为： SET FOREIGN_KEY_CHECKS=1;
3. 禁止自动提交。插入数据之前禁止事务的自动提交，数据导入完成之后，执行恢复自动提交操作。禁止自动提交语句为： SET AUTOCOMMIT=0; 恢复自动提交只需将该值置1。
优化Mysql数据库性能的方法
对表进行水平划分
如果一个表的记录数太多了，比如上千万条，而且需要经常检索，那么我们就有必要化整为零了。如果我拆成100个表，那么每个表只有10万条记录。当然这需要数据在逻辑上可以划分。一个好的划分依据，有利于程序的简单实现，也可以充分利用水平分表的优势。比如系统界面上只提供按月查询的功能，那么把表按月拆分成12个，每个查询只查询一个表就够了。如果非要按照地域来分，即使把表拆的再小，查询还是要联合所有表来查，还不如不拆了。所以一个好的拆分依据是 最重要的。关键字：UNION
例：
订单表根据订单产生时间来分表(一年一张)
学生情况表
查询电话费，近三个月的数据放入一张表，一年内的放入到另一张表
对表进行垂直划分
有些表记录数并不多，可能也就2、3万条，但是字段却很长，表占用空间很大，检索表时需要执行大量I/O，严重降低了性能。这个时候需要把大的字段拆分到另一个表，并且该表与原表是一对一的关系。 (JOIN)
【试题内容】、【答案信息】两个表，最初是作为几个字段添加到【试题信息】里的，可以看到试题内容和答案这两个字段很长，在表里有3万记录时，表已经占 了1G的空间，在列试题列表时非常慢。经过分析，发现系统很多时候是根据【册】、【单元】、类型、类别、难易程度等查询条件，分页显示试题详细内容。而每 次检索都是这几个表做join，每次要扫描一遍1G的表。我们完全可以把内容和答案拆分成另一个表，只有显示详细内容的时候才读这个大表，由此 就产生了【试题内容】、【答案信息】两个表。
选择适当的字段类型，特别是主键
选择字段的一般原则是保小不保大，能用占用字节小的字段就不用大字段。比如主键， 建议使用自增类型，这样省空间,空间就是效率!按4个字节和按32个字节定位一条记录，谁快谁慢太明显了。涉及到几个表做join时，效果就更明显了。
建议使用一个不含业务逻辑的id做主角如s1001。例：
int 4 bigint 8 mediumint smallint 2 tinyint 1md5 char(32)id ：整数 tinyint samllint int bigintstudent表id stuno stuname adress s1001 小民 深圳
文件、图片等大文件用文件系统存储
数据库只存储路径。图片和文件存放在文件系统，甚至单独放在一台服务器(图床 / 视频服务器 ).
数据库参数配置
最重要的参数就是内存，我们主要用的innodb引擎，所以下面两个参数调的很大
innodb_additional_mem_pool_size = 64Minnodb_buffer_pool_size =1G
对于myisam，需要调整key_buffer_size，当然调整参数还是要看状态，用show status语句可以看到当前状态，以决定改调整哪些参数
在my.ini修改端口3306，默认存储引擎和最大连接数
在my.ini中.port=3306 [有两个地方修改]default-storage-engine=INNODB max_connections=100
合理的硬件资源和操作系统
如果你的机器内存超过4G，那么毋庸置疑应当采用64位操作系统和64位mysql 5.5.19 or mysql5.6
读写分离
如果数据库压力很大，一台机器支撑不了，那么可以用mysql复制实现多台机器同步，将数据库的压力分散。
Master
Slave1
Slave2
Slave3
主库master用来写入，slave1—slave3都用来做select，每个数据库分担的压力小了很多。
要实现这种方式，需要程序特别设计，写都操作master，读都操作slave，给程序开发带来了额外负担。当然目前已经有中间件来实现这个代理，对程 序来读写哪些数据库是透明的。官方有个mysql-proxy，但是还是alpha版本的。新浪有个amobe for mysql，也可达到这个目的
定时完成数据库的备份
项目实际需求，请完成定时备份某个数据库，或者定时备份数据库的某些表的操作
windows 下每隔1小时，备份一次数据newsdb
windows 每天晚上2:00 备份 newsdb 下 某一张表
cmd> mysqldump –u root –p密码 数据库名 > 把数据库放入到某个目录
案例,备份 mydb 库的所有表
进入mysqldump所在的目录
cmd> mysqldump –u root –phsp shop> d:/shop.log [把shop数据库的所有表全部导出]
cmd> mysqldump –u root –phsp shop temusers emp > d:/shop2.log [shop数据库的 temusers和emp导出]
如何恢复数据的表
进入的mysql操作界面
mysql>source 备份文件的全路径
定时备份:(把命令写入到my.bat 问中)
windows 如何定时备份 (每天凌晨2:00)
使用windows自带的计划任务，定时执行批处理命令。
增量备份和还原
定义：mysql数据库会以二进制的形式，自动把用户对mysql数据库的操作，记录到文件，当用户希望恢复的时候，可以使用备份文件进行恢复。
增量备份会记录dml语句、创建表的语句，不会记录select。记录的东西包括：sql语句本身、操作时间，位置
进行增量备份的步骤和恢复
注意：mysql5.0及之前的版本是不支持增量备份的
1、配置my.ini文件或者my.conf，启用二进制备份。
打开my.ini文件，查找log-bin，进行配置：log-bin=G:\Database\mysqlbinlog\mylog
在G:\Database目录下面新建目录mysqlbinlog
2、重启mysql服务
这个时候会在mysqlbinlog目录下面看到以下两个文件：
mylog.000001：日志备份文件。如果要查看这个日志文件里面的信息，我们可以使用mysqlbinlog程序查看，mysqlbinlog程序存放在mysql的bin目录下面(“C:\Program Files\MySQL\MySQL Server 5.6\bin”)。
执行sql语句
UPDATE emp set ename='zouqj' where empno=100003;开始——运行——cmd，mysqlbinlog 备份文件路径
C:\Program Files\MySQL\MySQL Server 5.6\bin>mysqlbinlog G:\Database\mysqlbinlog\mylog.000001
mylog.index：日志索引文件，里面记录了所以的日志文件。(G:\Database\mysqlbinlog\mylog.000001)
3、假设现在问题来了，我这条update是误操作，如何进行恢复
在mysql日志中会记录每一次操作的时间和位置，所以我们既可以根据时间来恢复，也可以根据位置来恢复。
那么，我们现在马上可以从上图看出，这条语句产生的时间是"2016-04-17 12:01:36"，位置是614
按时间来恢复
我们可以选择在语句产生时间的前一秒
执行cmd命令：mysqlbinlog --stop-datetime="2016-04-17 12:01:35" G:\Database\mysqlbinlog\mylog.000001 | mysql -uroot -p
这个时候我再执行SQL语句查看
SELECT * from emp where empno=100003;结果变成了按位置来恢复
执行cmd命令：mysqlbinlog --stop-position="614" G:\Database\mysqlbinlog\mylog.000001 | mysql -uroot -p
这个时候再执行SQL来查看结果，又变回来了。
如何MySQL主从同步原理
主从同步需求
要实现 MySQL 的 Replication ，首先必须打开 Master 端的BinaryLog(mysql-bin.xxxxxx)功能，否则无法实现。因为整个复制过程实际上就是Slave从Master端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作。打开 MySQL 的 Binary Log 可以通过在启动 MySQL Server 的过程中使用“—log-bin” 参数选项，或者在 my.cnf 配置文件中的 mysqld 参数组([mysqld]标识后的参数部分)增加“log-bin” 参数项。
主从同步过程
MySQL 复制的基本过程如下：
1.Slave上面的IO线程连接上Master，并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容;
2.Master接收到来自Slave的IO线程的请求后，通过负责复制的IO线程根据请求信息读取指定日志指定位置之后的日志信息，返回给Slave端的 IO线程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息在Master端的Binary Log文件的名称以及在Binary Log中的位置;
3.Slave的IO线程接收到信息后，将接收到的日志内容依次写入到 Slave 端的RelayLog文件(mysql-relay-bin.xxxxxx)的最末端，并将读取到的Master端的bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”。
4.Slave的SQL线程检测到Relay Log中新增加了内容后，会马上解析该Log文件中的内容成为在Master 端真实执行时候的那些可执行的Query语句，并在自身执行这些Query。这样，实际上就是在Master端和Slave端执行了同样的Query，所以两端的数据是完全一样的。
实际上，在老版本中，MySQL 的复制实现在 Slave 端并不是由 SQL 线程和 IO线程这两个线程共同协作而完成的，而是由单独的一个线程来完成所有的工作。但是 MySQL的工程师们很快发现，这样做存在很大的风险和性能问题，主要如下：
1.首先，如果通过一个单一的线程来独立实现这个工作的话，就使复制 Master 端的，BinaryLog日志，以及解析这些日志，然后再在自身执行的这个过程成为一个串行的过程，性能自然会受到较大的限制，这种架构下的Replication 的延迟自然就比较长了。
3.其次，Slave 端的这个复制线程从 Master 端获取 Binary Log 过来之后，需要接着解析这些内容，还原成Master 端所执行的原始 Query，然后在自身执行。在这个过程中，Master端很可能又已经产生了大量的变化并生成了大量的Binary Log 信息。如果在这个阶段 Master端的存储系统出现了无法修复的故障，那么在这个阶段所产生的所有变更都将永远的丢失，无法再找回来。这种潜在风险在Slave端压力比较大的时候尤其突出，因为如果 Slave压力比较大，解析日志以及应用这些日志所花费的时间自然就会更长一些，可能丢失的数据也就会更多。
所以，在后期的改造中，新版本的 MySQL 为了尽量减小这个风险，并提高复制的性能，将 Slave端的复制改为两个线程来完成，也就是前面所提到的 SQL 线程和 IO线程。最早提出这个改进方案的是Yahoo!的一位工程师“JeremyZawodny”。通过这样的改造，这样既在很大程度上解决了性能问题，缩短了异步的延时时间，同时也减少了潜在的数据丢失量。
当然，即使是换成了现在这样两个线程来协作处理之后，同样也还是存在 Slave数据延时以及数据丢失的可能性的，毕竟这个复制是异步的。只要数据的更改不是在一个事务中，这些问题都是存在的。
