1.数据库引擎
什么是数据库引擎
引擎（engine）能够决定程序管理和数据操作的程序或程序段。数据库引擎就是操作数据库的一段程序或程序段
简单说来，一个数据库的存储引擎就是用来对数据表进行信息存储、处理、查询等操作。
查看数据库引擎
SHOW ENGINES;MySql有十几种数据库引擎，像测试的这个MySQL就内置了9种数据库引擎，但是我们一般常用的数据库引擎只有两种---MyISAM引擎和InnoDB引擎。这两种数据库引擎已经可以处理绝大部分业务场景了。但是总的来说没有十全十美的数据库存储引擎。InnoDB 和 MyISAM 虽然比较通用，但是它们并不是对所有的场景都是完美支持的，也许其他的存储引擎可以很好地支持你的应用。这个就需要DBA来设计了，我们暂时不用关心这么深入。
查看表所使用的引擎
show table status from mydb2 where name='vote_record';一个表只能用一种引擎,同一个库中的不同表允许使用不同的引擎
每个表的应用场景不同，所需要的处理策略也有所不同，每张表不一样的引擎配置可以使数据库灵活的适应各种场景。
2.MyISAM引擎和InnoDB引擎
- MyISAM引擎在5.5以前是作为MySQL的默认引擎的，5.5之后改为了InnoDB引擎。
- MyISAM由早期的ISAM所改良。虽然性能极佳，但却有一个缺点：不支持事务处理（transaction）。不过，在这几年的发展下，MySQL也导入了InnoDB（另一种数据库引擎），以强化参考完整性与并发违规处理机制，后来就逐渐取代MyISAM。
- InnoDB，是MySQL的数据库引擎之一，为MySQL AB发布binary的标准之一。InnoDB由Innobase Oy公司所开发，2006年五月时由甲骨文公司并购。与传统的ISAM与MyISAM相比，InnoDB的最大特色就是支持了ACID兼容的事务（Transaction）功能并且实现了SQL标准的四种隔离级别，该引擎还提供了行级锁和外键约束。
主要区别：
- MyISAM是非事务安全型的，而InnoDB是事务安全型的。
- •MyISAM锁的粒度是表级，而InnoDB支持行级锁定。
- •MyISAM支持全文类型索引，而InnoDB不支持全文索引。
- •MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。
- •MyISAM表是保存成文件的形式，在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦。
- •InnoDB 中不保存表的具体行数，也就是说，执行select count(*) fromtable时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM中存储了表的行数，只要简单的读出保存好的行数即可。
- •InnoDB表比MyISAM表更安全，可以在保证数据不会丢失的情况下，切换非事务表到事务表（alter table tablename type=innodb）。
应用场景：
- MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。--select
- InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。--insert，update
引擎操作常用命令：
1.查看表的存储类型（三种）：
•show create table tablename
•show table status from dbname where name=tablename
•mysqlshow -u user -p password --status dbname tablename2.修改表的存储引擎：
alter table tablename type=InnoDB3.启动mysql数据库的命令行中添加以下参数使新发布的表都默认使用事务：
--default-table-type=InnoDB4.临时改变默认表类型：
•set table_type=InnoDB
•show variables like 'table_type'数据库引擎常用命令
（1）查看表的存储类型（三种）：
•show create table tablename
•show table status from dbname where name=tablename
•mysqlshow -u user -p password --status dbname tablename（2）修改表的存储引擎：
alter table tablename type=InnoDB（3）启动mysql数据库的命令行中添加以下参数使新发布的表都默认使用事务：
--default-table-type=InnoDB（4）临时改变默认表类型：
set table_type=InnoDB
show variables like 'table_type'（5）关闭mysql服务net stop mysql
启动mysql服务net start mysql
实验
1.InnoDB引擎下
插入1W条数据
2.MyISAM引擎下
插入1W条数据
3.MyISAM引擎下
select * from vote_record4.InnoDB引擎下
select * from vote_record引擎操作语句:
show table status from mydb2 where name='vote_record';
alter table vote_record engine=MyISAM;
alter table vote_record engine=INNODB;InnoDB写入:
call add_vote(10000);受影响的行：1
时间：223.621s
MyISAM写入：
call add_vote(10000);受影响的行：1
时间：2.721s
select * from vote_recordInnoDB查询:
0.282s
MyISAM查询：
0.016s
实验总结：
看之前介绍的时候在高并发的状态下InnoDB的行级锁设计应该是比MyISAM表级锁的设计更高效的
但是现在没有高并发，两个数量级的差距，这是多大的高并发才能展现出InnoDB的高并发状态优于MyISAM
既然现在不存在高并发的问题，那么问题就应该是出在其他的差别方面。
我们知道InnoDB是支持事务的，而MyISAM是不支持事务的，那有没有可能是因为事务的关系呢？
写操作:
MyISAM比InnoDB快了两个数量级， 这仅仅是1W条数据的无并发插入
3.日志写入优化
MySQL事务
数据库本身不支持事务，老师上课讲事务知识点的时候，提到一个概念，数据库默认支持事务的，不过只是针对一条sql语句。所以现在更加深入理解是，数据库采用Innodb这个支持事务引擎的时候，引擎在做sql操作的时候会为每一条sql语句增加事务。所以每条sql的事务操作，就是使InnoDB引擎的添加要比MyISAM慢的主要原因。
Undo Log原理：
为了满足事务的原子性，在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为Undo Log）。然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。
Redo Log原理：
和Undo Log相反，Redo Log记录的是新数据的备份。在事务提交前，只要将Redo Log持久化即可，不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是Redo Log已经持久化。系统可以根据Redo Log的内容，将所有数据恢复到最新的状态。
总结：简单来说，redo log 记录事务修改后的数据, undo log 记录事务前的原始数据。
如何解决这个问题呢？
了解了事务机制之后，我们知道，数据库在写数据的时候，不单单只保存了数据，同时还要写入日志文件。日志文件也是先写到缓冲区，然后再刷写到磁盘的。所以我们现在来看下日志写入方面的相关参数配置，看下有什么能够优化的地方
日志统
错误日志：记录启动，运行或停止MySQL时出现的问题
通用日志：记录建立的客户端连接和执行语句
更新日志(记录所有更改数据的语句。5.1中已不再使用)
二进制日志：记录所有更改数据的语句
慢查询日志：记录所有执行时间超过long_query_tim秒的所有查询或不使用索引的查询
InnoDB日志：innodb redo log
InnoDB对日志写入策略的参数
1.innodb_flush_log_at_trx_commit
参数定制化commit返回时日志是否刷新到磁盘
2.sync_binlog是MySQL innodb
将二进制日志文件刷新到磁盘上。
1.innodb_flush_log_at_trx_commit参数：
# If set to 1, InnoDB will flush (fsync) the transaction logs to the
# disk at each commit, which offers full ACID behavior. If you are
# willing to compromise this safety, and you are running small
# transactions, you may set this to 0 or 2 to reduce disk I/O to the
# logs. Value 0 means that the log is only written to the log file and
# the log file flushed to disk approximately once per second. Value 2
# means the log is written to the log file at each commit, but the log
# file is only flushed to disk approximately once per second.
innodb_flush_log_at_trx_commit=1
===================================================================0
每隔一秒，把事务日志缓存区的数据写到日志文件中，以及把日志文件的数据刷新到磁盘上； log buffer会每秒写入到日志文件并刷写（flush）到磁盘。但每次事务提交不会有任何影响，也就是 log buffer 的刷写操作和事务提交操作没有关系。在这种情况下，MySQL性能最好，但如果 mysqld 进程崩溃，通常会导致最后 1s 的日志丢失。
意味着数据的写入，跟事务的提交是分开的，数据该怎么写入就怎么写入，而事务的提交没有任何写入操作，系统每秒会将事务的提交写入日志缓存，同时刷写到磁盘
1
当设为默认值1的时候，每次提交事务的时候，都会将log buffer刷写到日志。
这意味着事务提交和数据的写入是同步的，每次提交数据，就执行写入磁盘的操作。
2
如果设为2,每次提交事务都会写日志，但并不会执行刷的操作。每秒定时会刷到日志文件。要注意的是，并不能保证100%每秒一定都会刷到磁盘，这要取决于进程的调度。
总结:
1的时候，提交一次，写入一次事务日志到磁盘，写入数据到磁盘。
0的时候，写入数据，事务的提交跟数据的写入没有关系，异步操作。
2的时候，每次提交事务都会写入缓存里，每一秒定时刷到磁盘里这里是同步的操作，但是不是一条条的写入磁盘，而是先写入缓存，最后每隔1秒钟写入一次磁盘。
默认值1是为了保证完整的ACID。当然，你可以将这个配置项设为1以外的值来换取更高的性能，但是在系统崩溃的时候，你将会丢失1秒的数据。设为0的话，mysqld进程崩溃的时候，就会丢失最后1秒的事务。设为2,只有在操作系统崩溃或者断电的时候才会丢失最后1秒的数据。InnoDB在做恢复的时候会忽略这个值。
实验
修改innodb_flush_log_at_trx_commit值
set global innodb_flush_log_at_trx_commit=0;
set global innodb_flush_log_at_trx_commit=1;
set global innodb_flush_log_at_trx_commit=2;
查看innodb_flush_log_at_trx_commit值
SHOW GLOBAL VARIABLES LIKE 'innodb_flush_log%';实验数据
call add_vote(10000);0 : 2.700s
1：231.832s
2：5.788s
2.sync_binlog参数:
sync_binlog = N： N>0 每向二进制日志文件写入N条SQL或N个事务后，则把二进制日志文件的数据刷新到磁盘上；
N=0 不主动刷新二进制日志文件的数据到磁盘上，而是由操作系统决定；
实验:
设置
set global sync_binlog=N;(0-N)查看
SHOW GLOBAL VARIABLES like 'sync_binlog';实验数据
call add_vote(10000);N=2,0 4.754s
N=2,1 4.909s
N=2,100 4.838s
数据安全性
当innodb_flush_log_at_trx_commit和sync_binlog 都为1时是最安全的，在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。但是鱼与熊掌不可兼得，都为1会导致频繁的IO操作，因此该模式也是最慢的一种方式。
当innodb_flush_log_at_trx_commit设置为0，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
当innodb_flush_log_at_trx_commit设置为2，只有在操作系统崩溃或者系统掉电的情况下，上一秒钟所有事务数据才可能丢失。
双1适合数据安全性要求非常高，而且磁盘IO写能力足够支持业务，比如订单,交易,充值,支付消费系统。双1模式下，当磁盘IO无法满足业务需求时，推荐的做法是innodb_flush_log_at_trx_commit=2 ，sync_binlog=N (N为500 或1000) 且使用带蓄电池后备电源的缓存cache，防止系统断电异常。
推荐配置组合：
N=1,1 — 适合数据安全性要求非常高，而且磁盘IO写能力足够支持业务，比如充值消费系统；
N=1,0 — 适合数据安全性要求高，磁盘IO写能力支持业务不富余，允许备库落后或无复制；
N=2,0或2,m(0<m<100) — 适合数据安全性有要求，允许丢失一点事务日志，复制架构的延迟也能接受；
N=0,0 — 磁盘IO写能力有限，无复制或允许复制延迟稍微长点能接受，例如：日志性登记业务；
当两个参数设置为双1的时候，写入性能最差，sync_binlog=N (N>1 ) innodb_flush_log_at_trx_commit=2 时，(在当前模式下)MySQL的写操作才能达到最高性能。
4.其他引擎配置
一、公共选项
1.skip-external-locking
避免MySQL的外部锁定，减少出错几率增强稳定性
2.skip-name-resolve
禁止MySQL对外部链接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间，单需要注意的是，如果开启该选项，则所有远程主机链接授权都要使用IP地址的方式，否则MySQL将无法正常处理请求链接
3.max_connection
指定MySQL允许的最大链接进程数。
4.query_cache_size
默认为0，指定MySQL查询缓冲区的大小
5.sort_buffer_size
每个线程的排序缓存大小，该选项对排序order by，group by起作用。
对于内存在4GB左右的服务器推荐设置为6-8M
6.record_buffer
每个进行一个顺序烧面的线程，为其扫描的每张表分配这个大小的一个缓冲区。
7.table_cache
为所有线程打开表的数量。增加该值能增加MySQL要求文件描述符的数量。MySQL对每个唯一打开的表需要2个文件描述符
二、MyISAM选项
1.key_buffer_size
指定用于索引的缓冲区大小，增加它可得到更好的索引处理性能对于内存在4G左右的服务器该参数可设置为256M或384M。
2.read_buffer_size
读查询操作所能使用的缓冲区大小。和sort_buffer_size参数一样，该参数对应的分配内存也是每连接独享
3.myisam_sort_buffer_size
默认设置为16M,设置，恢复，修改表的时候使用的缓冲大小，值不要设置的太大。
4.join_buffer_size
联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每连接独享
三、InnoDB选项
1.innodb_buffer_pool_size
这个是Innodb最重要的参数，和MyISAM的key_buffer_size有相似之处，但也是有差别的。
这个参数主要缓存innodb表的索引，数据，插入数据时的缓冲。
该参数分配内存的原则：
这个参数默认分配只有8M，可以说是非常小的一个值。
如果是一个专用DB服务器，那么他可以占到内存的70%-80%。
这个参数不能动态更改，所以分配需多考虑。分配过大，会使Swap占用过多，致使Mysql的查询特慢。
如果你的数据比较小，那么可分配是你的数据大小+10%左右做为这个参数的值。
例如：数据大小为50M,那么给这个值分配innodb_buffer_pool_size＝64M
2.Innodb_additional_mem_pool_size
用来存放Innodb的内部目录这个值不用分配太大，系统可以自动调。不用设置太高。通常比较大数据设置16Ｍ够用了，如果表比较多，可以适当的增大。如果这个值自动增加，会在error log有中显示的。
3.innodb_log_file_size
在日志组中每个日志文件的大小，一般是innodb_buffer_pool_size的25%，官方推荐是innodb_buffer_pool_size的40-50%。一般控制在几个ＬＯＧ文件相加大小在２Ｇ以内为佳。具体情况还需要看你的事务大小，数据大小为依据。说明：这个值分配的大小和数据库的写入速度，事务大小，异常重启后的恢复有很大的关系。
4.Innodb_log_files_in_group=2
指定你有几个日志组。分配原则：一般我们可以用２－３个日值组。默认为两个。
5.innodb_log_buffer_size＝3M
事务在内存中的缓冲。分配原则：控制在2-8M.这个值不用太多的。他里面的内存一般一秒钟写到磁盘一次。具体写入方式和你的事务提交方式有关。在oracle等数据库了解这个，一般最大指定为３Ｍ比较合适。
2906
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
