高性能mysql笔记

高性能mysql笔记

mysql数据类型：

1.尽量使用可以正确存储数据的最小数据类型。更小的数据类型常更快，因为它们占用更少的磁盘、内存和cpu缓存，并且处理时需要的cpu周期更少。

2.整型比字符操作代价更低，因为字符集和校对规则使字符比较比整型比较更复杂。

3.尽量避免使用null,因为null的列使得索引、索引统计和值比较更复杂。
4.整数类型  tinyint  8; smallint 16;mediumint 24;int 32; bigint 64位
   整数类型又可选的unsiged,表示不允许负值
5.实数类型 decimal,可以指定精度，用于存储精确的小数，如decimal(18,9),小数点两边各存储9个数字。 浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。
6.字符串类型  varchar和char类型
varchar存储可变长字符串，比定长类型更节省空间。（例外，mysql表使用row_format=fixed创建，会浪费空间）
但由于是变长的，update时可能会使行变的比原来更长，导致需要做额外的工作
下列情况使用varchar合适：
1.字符串列的最大长度比平均长度大很多
2.列的更新很少，所以碎片不是问题
3.使用了像utf-8这样复杂的字符集，每个字符都使用不同的字节进行存储

char是定长的，根据字符串长度分配足够空间。当存储char时，mysql会删除所有的末尾空格，适合
1.存储很短的字符串，或者所有值长度接近同一个长度
2.经常变更的字符串

blob和text类型都是为粗才能很大的数据而设计的字符串类型，分别次用二进制和字符方式存储 

尽量避免使用blob和text类型。如果无法避免，在所有用到blob字段的地方都使用substring(column,length)将列值转换为字符串（order by中也适用），这样就可以使用内存临时表了

7.日期和时间类型
datetime  精度到秒，把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关，使用8个字节存储；
timestamp 和unix时间戳相同，只能表示1970年到2038年，使用4个字节存储；
mysql提供了from_unixtime()函数把unix时间转换为日期，并提供了unix_timestamp()函数把日期转换为unix时间戳

通常应尽量使用timestamp,因为它比datetime空间效率高

8.选择标识符
为标识列选择数据类型时，应选择和关联表中的对应列一样的类型
整数类型通常是标识符列最好的选择，因为它很快并且可以使用auto_increment

尽量避免使用字符串类型作为标识列，因为它很消耗空间，并且比整数类型慢。

如果存储uuid值，应移除“-”符号，更好的做法是，使用unhex（）函数转换uuid值为16字节的数字，并且存储在一个binary(16)列中，检索时通过hex()函数来格式化为十六进制格式

9.当研究一个cpu占用非常高德案例时，发现使用了非常宽的表，然而只有一小部分列会在实际中用到，这时转换的代价就非常高。

10.范式化与反范式化
1N范式：将字段拆分为不可再拆分；
2N范式：解决多对多问题；
3N范式：解决1对多问题；

范式优点：
1.更新操作更快；
2.重复数据较少或没有，所以需要修改的数据很少；
3.范式的表更小，可以更好的放进内存，执行操作更快；
4.很少的冗余数据，意味着检索表时很少用到distinct和group by语句

反范式化的优点：
所有数据在一张表中，可以避免表关联。

实际中通常是两者混用

11.缓存表 表示存储那些可以比较简单地从schema其他表获取数据的表，如逻辑上冗余的数据。
汇总表，保存的是使用group by 语句聚合数据的表

在使用汇总表和缓存表时，必须决定是实时维护数据还是定期重建

12.计时器表
表设计
create table daily_hit_counter(
day date not null,
slot tinyint unsigned not null,
cnt int unsingned not null,
primary key (day ,slot)
) ENGINE = InnoDB;


insert into daily_hit_counter (day,slot,cnt)
values (current_date,rand()*100,1)
on duplicate key update cnt = cnt + 1;
如果希望减少表的行数，避免表变得太大，可以写一个周期执行的任务，合并所有结果到0号槽，并且删除其他的槽：
update daily_hit_counter as c 
inner join (
select day, sum(cnt) as cnt, min(slot) as mslot
from daily_hit_counter 
group by day 
) as x Using(day)
set c.cnt = if(c.slot = x.mslot ,x.cnt,0),
c.slot = if(c.slot = x.mslot,0,c.slot);

delete from daily_hit_counter where slot <> 0 and cnt = 0;


13.加快 alter table操作的速度
mysql执行大部分修改表结构操作的方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除就表。对应大表来说，可能会花费很长时间，如果内存不足而表又很大，而且还有很多索引的情况下，alter table操作可能会花费数个小时到数天，这将导致mysql服务中断。

常用的技巧：
1.先在一台不提供服务的机器上执行alter table 操作，然后和提供服务的主库进行切换；
2.用要求的表结构创建一张和原标无关的新表，然后通过重命名和删表操作交换两张表，可使用工具来完成：如facebook的online schema change

 

mysql索引

InnoDB使用B+Tree
B-Tree索引
1.所有的值都是按顺序存储的，并且每一个叶子到根的距离相同
2.B-Tree对索引列是顺序组织存储的，很适合查找范围数据。
索引之所以能加快查询速度，是因为存储引擎不再需要全表扫描，而是从索引的根节点开始进行搜索。根节点中存放了指向子节点的指针，存储引擎根据指针向进入下层子节点。最终存储引擎要么找到对应的值，要么记录不存在。
3.索引对多个值进行排序的依据是create table 语句中定义索引时列的顺序。
4.B-Tree索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。
全值匹配指的是和索引中所有的列进行匹配；
匹配最左前缀指的是只使用索引的第一列；
匹配列前缀指匹配某一列的值的开头部分，如：like 'J%',只适用索引的第一列；
匹配范围值，
5.B-Tree索引的限制：
如果不是按照索引的最左列开始查找，则无法使用索引；
不能跳过索引中的列；
如果查询中有某个列的范围查询，则其右边所有的列都无法使用索引优化查找；

哈希索引（hash index）
基于哈希表实现，只有精确匹配索引所有列的查询才有效。
mysql中，只有Memory引擎支持哈希索引
查找数据方式：计算出查找数据的hash code,根据hash code找到指向数据行的行指针，根据指针找到对应的数据行的数据
hash索引限制：
哈希索引值包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。
哈希索引数据不是按照值顺序存储的，所以无法用于排序；
哈希索引不支持部分索引列的匹配，因为hash索引是根据索引列的全部内容来计算hash值的；
哈希索引只支持等值的比较，包含 = ,in(),<=> (<=>和<>是两个不同的操作)
当出现哈希冲突的时候（不同的索引值，却有相同的哈希值），必须遍历链表中的所有行指针，逐行比较
如果哈希冲突很多的话，维护索引的代价会很高

空间数据索引（R-Tree）
MyISAM表支持空间索引，可以用作地里数据存储。空间索引会从所有维护来维护数据，查询时可以用任意维护的组合来查询。
必须使用mysql的GIS相关函数如MBRCONTRINS()来维护数据，但Mysql的GIS支持并不完善。

全文索引
查找的是文本中的关键词，而不是索引中的值，适用于match against操作 

索引的优点：
1.大大减少了服务器需要全表扫描的数据量；
2.可以帮助服务器避免排序和临时表；
3.可以将随机I/O变成顺序I/O。

注意：

对于非常小的表，大部分情况下简单的表全表扫描效率更高；
对于中到大型的表，索引非常有效；
对于特大型的表，建立和使用索引的代价会随之增长。可以使用分区技术来实现数据的维护

选择合适的索引顺序

在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次第二列。索引可以按照升序或降序进行扫描，以满足符合列顺序的order by,group by和distinct.
当不考虑排序和分组时，将选择性最高的列放到索引的最前列，可最快的过滤出需要的行，对于where条件中只使用了索引前缀列的查询来说选择性更高。

聚簇索引
聚簇索引是一种数据存储方式，具体的细节依赖于其实现方式。
聚集数据的优点：
1.可把相关数据保存在一起。
2.数据访问快
3.使用覆盖索引扫描的查询可直接用页节点中的主键值

缺点：
1.插入速度依赖于插入顺序；按照主键的顺序插入是加载数据到InnoDB表速度最快的方式。
2.更新聚簇索引的代价高；因为会强制将每一个被更新的行移动到新的位置。
3.聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或页分裂导致数据存储不连续的时候。

按主键顺序插入行
如果没有什么数据需要聚集，可定义一个代理键作为主键，设置为自增，这样可保证数据行是按顺序写入，对于根据主键做关联操作的性能会更好。
使用UUID做聚簇索引，会导致插入变得完全随机，性能很糟糕。

覆盖索引
如果一个索引包含（或者说覆盖）所有需要查询的字段的值，称之为覆盖索引。

不是所有类型的索引都可称之为覆盖索引。覆盖索引必须存储索引列的值，而哈希索引，空间索引和全文索引等都不存储索引列的值，所以MySQL中只能使用B-Tree索引做覆盖索引。

如果索引不能覆盖查询所需的全部列，那需扫描一条索引记录就都回表查询一次对应的行，这基本上都是随机I/O，因此按索引顺序读取数据的速度通常要比顺序的全表扫描慢。

只有当索引列的顺序和order by子句的顺序一致，并且所有的排序方向都一样，mysql才能使用索引对结果做排序。如果查询需要关联多张表，只有当order by子句引用的字段全部为第一个表时，才能使用索引做排序。order by子句和查询的限制是一样的：需要满足索引的最左前缀的要求，否则mysql无法利用索引做排序。


如：在列（rental_date,inventory_id,customer_id）上建了一个索引rental_date;
create table rental(
...
primary key(rental_id),
unique key rental_date（rental_date,inventory_id,customer_id）,
key idx_fk_inventory_id (inventory_id),
key idx_fk_customer_id(customer_id）,
key idx_fk_staff_id(staff_id),
...
)

1.
where rental_date = '2015-11-22' order by inventory_id,customer_id
order by 不满足索引的最左前缀的要求，也可用于查询排序，因为索引的第一列被指定为一个常数。
2.
where rental_date = '2015-11-22' order by inventory_id desc
索引的第一列提供了常量条件，而使用第二列排序，两列合在一起，形成了索引的最左前缀。
3.
where rental_date = '2015-11-22' order by rental_date, inventory_id
order by的两列就是索引的最左前缀

下面不能使用索引做排序查询：
1.
where rental_date = '2015-11-22' order by inventory_id desc,customer_id asc
索引的列都是正序排序的，不能既有正序，又有倒序
2.
where rental_date = '2015-11-22' order by inventory_id ，staff_id
引用了一个不在索引列中的列
3.
where rental_date = '2015-11-22' order by customer_id 
无法构成最左前缀
4.
where rental_date > '2015-11-22' order by inventory_id ,customer_id 
第一列上是范围查询，无法使用索引的其余列
5.
where rental_date = '2015-11-22'and inventory_id in (1,3)  order by customer_id 
inventory_id列有多个等于条件，对于排序来说，是一种范围查询。

重复索引是指在相同的列上按照相同的顺序创建相同的索引类型。
如：
create table test(
id int not null primary key,
a int not null,
b int not null,
unique(id),
index(id)
) ENGINE=InnoDB;


在主键上先加了唯一限制，又加上索引。实际上唯一限制和主键限制都是通过索引实现的。

如果创建了索引(A,B)，再创建索引（A）就是冗余索引，因为这只是前一个索引的前缀索引，如果再创建索引（B,A），则不是冗余索引，索引（B）页不是，因为索引（B）不是索引（A,B）的前缀索引。

冗余索引通常会发生在为表添加索引的时候，如：表中已经存在了索引(A)，然后添加了一个(A,B)

大多数情况下不需要冗余索引，应该尽量扩展已有的索引，而不是新建索引。但有时候出于性能考虑需要冗余索引，因为扩展已有的索引会导致其变得太大，影响其他使用该索引的查询性能。

表中的索引越多，插入速度回越慢。一般来说，增加索引会导致insert,update,delete等操作的速度变慢。

解决冗余索引的方式：找出，删除；可通过一些工具来定位。如：common_schema

Mysql sql优化：

查询的生命周期：从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。其中“执行”是生命周期中最重要的阶段，大量的检索数据到存储引擎的调用以及调用后的数据处理，包括分组排序等。

慢查询优化：
1.确认程序是否需要检索大量超过需要的数据
多余的数据会给mysql服务器带来额外负担，并增加网络开销，另外也会消耗服务器的CPU和内存。
常见的问题有：
a.先用select查询大量结果，再获取前N行。（可通过limit解决）
b.多表关联返回全部列
c.总是取出全部列
d.重复查询相同的数据（如用户头像的url，可通过缓存解决）
2.确认mysql服务层是否在分析大量超过需要的数据行。
衡量查询开销的3个指标：
a.响应时间
了解这个查询需要哪些索引及它的执行计划是什么，然后计算大概需要个顺序和随机I/O，再乘以在具体硬件下一次I/O的消耗时间。
b.扫描的行数和返回的行数
理想情况下扫描的行数和返回的行数相同，实际上一般在1:1到1:10之间。
c.扫描行数和访问类型
在EXPLAIN语句中的type列反应了访问类型。从全表扫描、范围扫描、唯一索引扫描、常数引用等，速度由慢到快。如果没有办法找到合适的访问类型，合适的办法就是增加一个合适的索引。

一般mysql能使用如下三种方式应用where条件，从好到坏依次为：
1.在索引中使用where条件来过滤不匹配的记录，这是在存储引擎层完成的。
2.使用索引覆盖扫描（在Extra列中出现了Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在mysql服务器层完成的，但无需再回表查询记录。
3.从数据表中返回数据，然后过滤不满足条件的记录（在Extra列中出现了Using where），在mysql服务器完成的，需要回表查询数据。

需要扫描大量数据，但返回少数行时，可以通过如下方式优化：
1.使用索引覆盖扫描，把所有需要用到的列都放到索引中，这样存储引擎无需回表获取数据。
2.改变库表结构，使用单独的汇总表。如现在工作中，在数据工厂中完成复杂计算，然后将结果表出库，供报表查询使用。
3.重写这个复杂的查询

重构查询方式
1.切分查询
mysql让连接和断开都很轻量级的，在返回一个小的查询结果方面很高效。
在其它条件相同的情况下，使用尽可能少的查询，且能够胜任，当然是好的，但有时候将大查询分解为多个小查询都是必要的。
如：删除旧数据。定期清除大量数据，如果使用一个大的语句一次完成，可能需要一次锁住很多数据、沾满整个事务日志、耗尽系统资源、阻塞很多小但重要的查询。例如：
delete from messages where created < date_sub(now(),interval 3 month);
可改成如下方式：
rows_affected = 0
do{
rows_affected = do_query(
"delete from messages where created < date_sub(now(),interval 3 month) limit 10000"
)
}while rows_affected > 0
一次删除一万条数据是一个比较高效且对服务器影响最小的做法。
2.分解关联查询
 select * 
from tag
join tag_post
 on tag_post.id = post.id
join post 
 on tag_post.post_id = post.id
where tag.tag='mysql';

select * from tag where tag='mysql';
select * from tag_post where tag_id=1234;
select * from post where post.id in (123,23,423,422)

应用对于分解关联查询的优势如下：
1.让缓存效率更高。可以方便地缓存单表查询对应的结果对象。对mysql来说，如果关联中的某个表发生了变化，就无法使用查询缓存了，而拆分后，如果某个表很少该表，就可以重复使用缓存。
2.拆分后，执行单个查询可以减少锁的竞争。
3.在应用层做关联，更容易对数据进行拆分。
4.查询本身效率也会有所提升。如：使用in()代替关联查询，可让mysql按照id顺序进行查询，比随机关联更高效。
5.可减少冗余记录的查询。在应用层做关联，意味着对于某条记录应用，只需要查询一次，而在数据库中做关联，可能需要重复访问。
6.相当于在应用中实现了哈希关联，而不是使用mysql的嵌套循环关联。

查询执行的基础
遵循一些原则，让优化器能够按照预想的方式运行。

show full processlist;
sleep 线程等待
    query 线程正在执行查询或正将结果发给客户端；
   locked 在mysql服务层，等待表锁；
  Annolyzing and statistics 线程正在收集存储引擎的统计信息，并生成查询的执行计划；
 Copying to tmp table [on disk] 线程正在执行查询，并将结果复制到一个临时表中，一般是在做group by，或文件排序或union操作，如果还有 on disk，表示mysql正将一个内存临时表放在磁盘上。
 Sorting result 线程正在对结果集排序

 Sending DATA 表示多种情况，传送数据，生成结果集，返回客户端数据等。

有多种原因会导致MySQL优化器选择错误的执行计划：
 1.统计信息不准确。
 2.执行计划中的成本估算不等于实际执行的成本。
 3.mysql的最优是基于成本的，和自己想的基于时间的不一样。
 4.mysql不考虑并发执行的查询
 
 优化策略：
静态优化：直接对解析树进行解析，并完成优化。静态优化在第一次完成后一直有效，即使使用不同的参数重新执行也不变。
动态优化：和查询的上下文有关，需要在每次查询的时候重新评估。

mysql能够处理的优化类型：
1.重新定义关联表的顺序
数据表的关联并不总是按照在查询中指定的顺序进行，决定关联顺序是优化器很重要的一部分功能。
2.将外连接转换为内连接
并不是所有的outer join语句都必须以外连接方式执行。如果：where条件、库表结构都会让外连接等价内连接。
3.使用等价变化规则
如：(a < b and b = c) and a = 5 改成 b > 5 and b = c and a = 5
4.优化count()、min()、max()
索引和列是否可为空，可帮助mysql优化这类表达式。
5.评估并转换为常数表达式
如：一个用户自定义的变量，在查询没有变化时就可转换为一个常数。甚至一个查询，如在索引列上执行min()，。
6.覆盖扫描索引
索引中的列覆盖了查询需要的列，使用索引返回需要的数据，无需返回表进行查询
7.子查询优化
8.提前终止查询
当已满足查询需求的时候，提前终止查询，如limit,和判断存在性的时候，
如：  select film.film_id
 from sakila.film
 left join sakila.film_actor using(film_id)
 where film_actior.film_id is null
9.列表in()的比较
in()列表中的数据进行排序，通过二分查找方式来确定表中值是否满足，这是一个O(log n)复杂度的操作
如果能够确认优化器给出的不是最优选择，并且清楚背后原理，就可以帮助优化器做进一步的优化。如：查询汇总添加hint提示，重写查询，重新设计更优的库表结构，添加更合适的索引。

Mysql执行关联查询
对任何关联都执行嵌套循环关联操作。即：一个表中循环取出单条数据，拿到另一个表中进行循环匹配。
执行计划
mysql生成查询的一颗指令树，然后通过存储引擎执行完成这课指令树并返回结果。如果对某个查询执行explain extended后，再执行show warnings，就可以看到重构出的全部查询。
 按Oracle的描述就是：小表做驱动表（主要根据返回数据的多少），大表做被驱动表，这样小表扫描大表，循环次数少，大表上建索引，扫描数据快，整体效率高。

排序优化
 1.通过索引
 2.无法使用索引时，mysql自己排序，如果数据量小，在内存中进行，如果数据量大，需要使用磁盘，mysql将这个过程称为文件排序。
 排序的两种算法：
  1.两次传输排序（旧版本）
读取行指针和需要排序的字段，对其进行排序，再根据排序结果读取需要的数据行。会产生大量随机io,数据传输成本很高。
  2.单次传输排序（新版本）
先读取查询所需要的所有列，然后根据给定列进行排序，最后直接返回排序结果。只需一次顺序I/O，无需随机I/o,缺点是如果返回的列非常多，会额外占大量空间。

Mysql的局限性
 1.union 
 无法将限制条件从外层“下推”到内层。如果希望union的各个字句能根据limit只取部分结果集，或希望先排好序，再合并结果集，就要在union的各个字句中都使用这些字句。
 2.等值传递
 某些时候，等值传递会带来很大的额外消耗。如：一个非常大的in()列表，优化器发现存在where,on，将这个列表的值和另一个表的某列值进行关联。优化器会将in()列表都复制到关联的各个表中，将导致优化和执行变得很慢。
 3.并行执行
mysql不支持多核并行
4.哈希 关联
mysql不支持哈希关联，所有的关联都是嵌套循环
5.松散的索引扫描
 不支持 ，无法按照不连续的方式扫描索引，如：select * from tab1 where b between 2 and 3
6.在同一个表上查询和更新
不允许，如：
update tb1 as outer_tb1
set cnt = (
select count(*) 
from tb1 as inner_tb1 
where inner_tb1.type = outer_tb1.type
);

查询优化器提示
通过在查询中加入相应的提示，控制该查询的执行计划。
HIGH_PRIORITY和LOW_PRIORITY
当多个语句同时访问某一个表的时候，哪些语句优先级高，只是简单控制了mysql访问某个数据表的顺序。
这两个提示只对使用表锁的存储引擎有效，不要在InnoDB或其它细粒度机制和并发控制的引擎中使用。
DELAYED
对 insert 和 replace 有效。会将使用该提示的语句立即返回给客户端，并将插入的行数据放入缓冲区，然后在表空闲时批量将数据插入。
限制：并不是所有的引擎都支持这样的做法，且会导致函数 last_insert_id()无法正常工作。
STRAIGHT_JOIN
放在select关键字之后，或任何两个关联表的名字之间。用法1：让查询中所有的表按照在语句中出现的顺序进行关联。用法2：固定其前后两个表的关联顺序。
可以使用explain查看优化器选择的顺序，然后使用该提示重写查询，再看它的关联顺序。
SQL_SMALL_RESULT 和 SQL_BIG_RESULT
只对select语句有效。告诉优化器对group by或distinct查询如何使用临时表及排序。
sql_small_result告诉优化器结果集会很小，可以将结果放到内存中的索引临时表，避免排序操作。
sql_big_result告诉优化器结果集很大，建议使用磁盘。
SQL_BUFFER_RESULT
告诉优化器将结果放入一个临时表，尽可能快的释放表锁。当没法使用客户端缓存的时候，服务器端的缓存会很有效，但会消耗更多的内存。
SQL_CACHE 和 SQL_NO_CACHE
告诉mysql这个结果集是否应缓存在查询缓存中。

SQL_CALC_FOUND_ROWS
让返回的结果集包含更多的信息
FOR UPDATE 和 LOCK IN SHARE MODE
主要控制select语句的锁机制，只对实现了行级锁的存储引擎有效。该提示对符合查询条件的数据行枷锁。尽可能避免使用该提示。
USE INDEX 、 IGNORE INDEX 和 FORCE INDEX
使用或不使用哪些索引来查询记录。当发现优化器选择了错误的索引或某些原因（如不使用order by 但希望结果有序）要使用另一个索引时，使用该提示。

优化特定类型的查询
1.优化count()查询
count()作用：
(1).统计某个列值的数据，不统计null，要求列值非空。
(2).统计行数。count(*)
如果mysql知道某列col不可能为null值，mysql内部会将count(col)表达式优化为count(*)

简单的优化：
  select count(*) from city where id > 5;
 改成如下，大大减少扫描的行数：
  select (select count(*) from city) - count(*)
  from city 
  where id <= 5;

假设通过一个查询返回各个不同颜色的商品数量，此时不能用or
如:select count(color='blue' or color='red) from items;
可如下：
select sum(if(color='blue',1,0)) as blue
  ,sum(if(color='red',1,0)) as red
from items;

2.优化关联查询
(1)确保on的列上有索引。在创建索引的时候要考虑到关联顺序。如果表A和表B用列c关联，优化器的关联顺序是B、A，就不需要在B表对应列创建索引。
(2)确保group by 和 order by中的表达式只涉及到一个表中的列，这样mysql才有可能使用索引优化这个过程。
(3)升级mysql要注意关联语法、运算符优先级等。

3.优化子查询
尽可能使用关联查询代替。

4.优化group by 和distinct
(1)使用索引优化
(2)无法使用索引时，group by使用两种策略来完成：使用临时表或文件排序来分组。可通过提升sql_big_result 和 sql_small_result。
如果需要对关联查询做group by，并且按照查找表中的某个列进行分组，通常采用查找表的标识列分组效率会比其他类更高。

优化group by with rollup
对返回的结果做一次超级聚合。
5.优化limit分页
通常是由limit加上偏移量的办法实现，同时加上合适的order by子句，如果有对应的索引，效率会比较高。
优化办法：尽可能的使用覆盖索引扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候，效率会提示很大。如：
select film_id,description 
from sakila.film 
order by title limit 50,5;
可改成：
select film.film_id,film.description
from sakila.film 
join (
select film_id 
from sakila.film
order by title limit 50,5
    ) as lim
on film.film_id = lim.film_id 

6.优化SQL_CALC_FOUND_ROWS
 243页
 
7.优化UNION查询
mysql总是填充临时表的方式执行UNION查询。因此很多优化策略在UNION查询中都无法使用。需要手工的将where,limit,order by 等子句下推到UNION的各个子查询中。
除非确实需要服务器消除重复的行，否则一定要使用UNION ALL。如果没有ALL,mysql会给临时表加上distinct选项，对整个临时表做数据唯一检查。
8.

静态查询分析
pt-query-advisor能够解析查询日志、分析查询模式，然后给出所有可能存在的问题的查询，并给出详细的建议。
9.

使用用户自定义变量
自定义变量是一个用来存储内容的临时容器，可使用如下的set和select 语句来定义它们:
set @one := 1;
set @min_ac := (select min(actor_id) from sakila.actor);
set @last_week := current_date - interval 1 week;
然后可以在任何使用表达式的地方，使用这些变量：
select ... where col <= @last_week;


变量的使用限制：
 a.无法使用查询缓存；
 b.不能在使用常量或标识符的地方使用自定义变量，如：表名、列名和limit子句中。
 c.如果使用连接池或持久化连接，可能让毫无关系的代码发生交互，通常是代码bug或连接池bug。
 d.不能显式的声明自定义变量的类型。
 e.赋值符号 := 的优先级非常低，所以需要注意，赋值表达式要使用明确的括号。
 f.使用未定义的变量不会有语法错误。
 
 优化排名语句
 可以给变量赋值的同时使用这个变量，具有“左值”特性。
 set @rownum := 0;
 select actor_id, @rownum := @rownum + 1 as rownum
  from sakila.actor limit 3;
  
--每个演员参演电影的数量 
select  actor_id,count(*) as cnt
from  salika.film_actor
group by actor_id 
order by cnt desc 
limit 10;

--一个记录当前的排名，一个记录前一个前一个演员的排名，一个记录当前演员参演电影的数量。只有当前演员参演电影的数据和前一个演员不同时，排名才变化。
set @curr_cnt := 0,@prev_cnt := 0, @rank := 0;
select actor_id,
@curr_cnt := count(*) as cnt,
@rank  := if(@prev_cnt <> @curr_cnt,@rank +１,@rank) as rank,
@prev_cnt := @curr_cnt as dummy
from sakila.film_actor
group by actor_id
order by cnt desc 
limit 10;

由于变量赋值的时间，导致出现错误；

set @curr_cnt := 0,@prev_cnt := 0, @rank := 0;
select actor_id,
@curr_cnt := count(*) as cnt,
@rank  := if(@prev_cnt <> @curr_cnt,@rank +１,@rank) as rank,
@prev_cnt := @curr_cnt as dummy
from (
select  actor_id,count(*) as cnt
 from  salika.film_actor
group by actor_id 
order by cnt desc 
limit 10
) as der

高效的更新时间戳，并查询当前的时间戳
update t1 set lastUpdated = now() where id = 1;
select lastUpdated from t1 where id = 1;
使用变量：
 update t1 set lastUpdated = now() where id = 1 and @now := now();
 select @now;
 
 --统计更新和插入的数量
insert into t1(c1,c2) values (2,3),(3,5),(2,5) on duplicate key update 
c1 = values(c1) +(0* (@x := @x + 1));

让变量赋值和取值发生在执行查询的同一阶段：
set @rownum := 0;
select actor_id,@rownum as rownum 
from sakila.actor 
where (@rownum = @rownum +１) <= 1;

select id from users where id = 123
union all 
select id from users_archived where id = 123;

改成：只有第一个表中没有数据时，才在第二个表中查询。
一旦在第一个表中找到记录，就定义一个变量@found,通过在结果列中做一次赋值来实现，然后将赋值放在函数greatest中来避免返回额外的数据。

select greatest(@found := -1,id) as id,
'users' as which_tb1
from users 
where id = 1 
union all 
select id ,
  'users_archived'
from users_archived 
where id = 1 and @found is null 
union all
select 1,
'reset'
from dual
where (@found := null) is not null;

Mysql分区：

1.对用户来说，分区是一个独立的逻辑表，但底层由多个物理字表组成。每个分区表都有一个使用#分隔命名的表文件。
2.mysql中，索引也是按照分区的字表定义，而没有全局索引。oracle可以定义索引和表是否进行分区。
3.mysql在创建表时使用partition by 字句定义每个分区存放的数据。执行查询时，优化器根据分区定义过滤那些没有我们需要的分区，这样查询就无需扫描所有分区。
create table sales(
order_date datetime not null,
--other columns mitted
) ENGINE = InnoDB partition by range(year(order_date))(
partition p_2010 values less than (2010),
partition p_2011 values less than (2011),
partition p_2012 values less than (2012),
partition p_2013 values less than (2013),
partition p_catchall values less than maxvalue);

4.分区的主要目的：将数据按照一个较粗的粒度分在不同的表中。这样做可以将相关的数据存放在一起，批量删除整个分区数据也很方便。
5.以下场景适合分区：
（1）表非常大，以至于无法全部放到内存，或只在表的最后部分有热点数据，其它均为历史数据。
（2）分区表的数据更容易维护。如：删除大量数据可以通过清除分区的方式。
（3）分区表的数据可以分布在不同的物理设备上。
（4）可以使用分区表避免某些特殊瓶颈，如：InnoDB的单个索引的互斥访问、ext3文件系统的inode锁竞争等。
6.分区表的限制
（1）一个表最多只能有1024个分区
（2）如果分区字段中有主键或者唯一索引的列，那所有的主键列和唯一索引列都必须包含进来。
（3）分区表中无法使用外键约束。
7.分区表的索引只是在各个底层表上加上一个完全相同的索引。
8.分区表的类型
 (1)范围分区
create table sales(
order_date datetime not null,
--other columns mitted
) ENGINE = InnoDB partition by range(year(order_date))(
partition p_2010 values less than (2010),
partition p_2011 values less than (2011),
partition p_2012 values less than (2012),
partition p_2013 values less than (2013),
partition p_catchall values less than maxvalue);

5.5版本后，可以直接使用列 partition by range columns(order_date)

（2）按日期分区
CREATE TABLE h5_channel_pv_month(
f_date INT,
channel STRING,
pv BIGINT
)
PARTITION BY LIST( f_date )
(
PARTITION p_20170605 VALUES IN ( 20170605 ),
PARTITION p_20170604 VALUES IN ( 20170604 ),
PARTITION p_20170603 VALUES IN ( 20170603 ),
);
partition 分区字句中可以使用各种函数，但要求表达式返回值是一个明确的整数，且不能是常数。可以使用函数year(),to_days()。根据时间间隔进行分区。
另外其他的有哈希分区，列表分区等，但用的较少。
9.如何使用分区
对于数据量巨大的表，比如10亿条数据，几年的数据，只想查最近几个月的数据，如果破？
分析：a.数据量巨大，不能做全表扫描；
 b.索引空间和维护上的消耗，且会产生大量的随机I/O，不能使用索引（数据量超大的时候索引就不起作用了）；这时候只有两个选择：1.让所有查询只在数据表上做顺序扫描，2.建数据表和索引全表缓存在内存里
综上分析：
建立分区，以非常小的代价定位到需要的数据在哪个区域，在这个区域内做顺序扫描，建索引。
全量扫描数据，不要任何索引；
如果数据有明显的热点，且其它数据甚少访问，那可以将这部分热点数据单独放在一个分区中，让这个分区的数据有机会缓存在内存中；

10.分区注意的问题：
a.null值会使分区过滤无效   null会将这个范围的值放在第一个分区，如果第一个分区非常大，当使用“全量扫描，不使用索引”策略时，代价会很大。
b.分区列和索引列不匹配 会导致查询无法进行分区过滤，如a列上建了索引，b列上进行分区
c.选择分区的成本很高
d.打开并锁住所有底层表的成本可能很高   分区过滤之前发生，影响所有的查询。
e.维护分区的成本可能很高   重组分区类似alter操作，先创建一个临时分区，然后将数据复制其中，最后再删除原分区。

11.查询优化
对于访问分区表来说，在where条件中带入分区列，有时候看似多余也要带上，这样就可以让优化器过滤掉无需访问的分区。
创建分区时可以使用表达式，但查询时只能根据列来过滤区分。

 

Mysql视图

视图
1.视图是指计算机数据库中的视图，是一个虚拟表，不存放任何数据，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。
2.不能对视图使用触发器，也不能使用drop table命令删除视图；
3.可更新视图是指可通过更新这个视图来更新视图涉及的相关表。只要符合了指定条件，就可以以更新、删除甚至向视图中写入数据。
4.如果视图定义包含了group by,distinct,union,聚合函数等，就不能更新视图了。
5.定义视图使用的check apinion子句，表示任何通过视图更新的行，都必须符合视图本身的where条件定义。
6.explain select * from <view_name>
7.mysql不会保存视图定义的原始sql语句

mysql外键约束

1.外键通常要求在每次修改数据时，都要在另外一张表中执行一次查询操作。虽然InnoDB强制外键使用索引，但还是无法消除这种约束检查带来的开销。
如果外键列选择性很低，会造成一个非常大且选择性很低的索引。
2.如果想确保两个相关表始终数据一致，使用外键比在程序中检查一致性效率要高。
3.外键维护是逐行进行的，这样的更新比批量删除和更新慢很多。
4.如果只用外键做约束，通常在程序里实现更好。对应相关数据同时更新，外键更合适些。

数据库范式笔记

--设计范式指的是可高效的方便扩充数据库的准则，但实际中也只是作为参考。
实际工作中，设计原则：根据业务尽可能的减少多表查询。

第一范式：（单表）
数据表中的每一个字段都不可再分，即都使用标准数据类型，如以下不符合：
create table member(
编号  number,
姓名  varchar2(200),
联系方式  varchar2(200)
);
对于联系方式可划分很多自字段：如手机，qq,邮箱等，所以不符合第一范式
可改为：
create table member(
编号  number,
姓名  varchar2(200),
地址  varchar2(200),
邮编  varchar2(20),
手机  varchar2(20),
qq    varchar2(20)
);
针对第一范式有两点小说明：
1.如果系统在中国则名字就表示一个字段，在国外则需要firstname、lastname,不属于不可再分
2.设计表的时候都使用标准类型（number,varchar2,clob,date）,千万不要讲生日拆分为3个字段

第二范式：（多对多关系）
数据表中不存在非关键字段对任意一后选关键字段的部分函数依赖。（说的毛线啊）
个人理解，即：多对多关系
函数关系：总价 = 单价 乘 数量
如：
create table orders(
编号   number primary key,
商品名称 varchar2(200),
单价  number,
数量  number,
总价  number
);

依赖关系：
如电影系列，从甲方乙方，到非诚勿扰，到让子弹飞
从年份、公司、导演，推出电影名称
从电影、导演 推出唯一的演员，则无法推出

实例：设计一个学生选课系统，多个学生，多个课程，多对多关系
表设计需要：分为学生表、课程表、选课关系表   

第三范式：（一对多）
数据表之中不存在非关键字段对任意一后选关键字段的传递函数依赖。

Sybase PowerDesigner

设计阶段可以说是以后系统性能的关键阶段，在这个阶段，有一个关系到以后几乎所有性能调优的过程—数据库设计。
在数据库设计完成后，可以进行初步的索引设计，好的索引设计可以指导编码阶段写出高效率的代码，为整个系统的性能打下良好的基础。
对于性能要求设计阶段，我们需要注意以下几点：

1、数据库逻辑设计的规范化
数据库逻辑设计的规范化就是我们一般所说的范式，我们可以这样来简单理解范式：

第1规范：没有重复的组或多值的列，这是数据库设计的最低要求。
第2规范：每个非关键字段必须依赖于主关键字，不能依赖于一个组合式主关键字的某些组成部分，消除部分依赖，大部分情况下，数据库设计都应该达到第二范式。
第3规范：一个非关键字段不能依赖于另一个非关键字段。消除传递依赖，达到第三范式应该是系统中大部分表的要求，除非一些特殊作用的表。

更高的范式要求这里就不再作介绍了，在马海祥看来，如果全部达到第二范式，大部分达到第三范式，系统会产生较少的列和较多的表，因而减少了数据冗余，也利于性能的提高。

2、合理的冗余
完全按照规范化设计的系统几乎是不可能的，除非系统特别的小，在规范化设计后，有计划地加入冗余是必要的。
冗余可以是冗余数据库、冗余表或者冗余字段，不同粒度的冗余可以起到不同的作用。
冗余可以是为了编程方便而增加，也可以是为了性能的提高而增加。
从性能角度来说，冗余数据库可以分散数据库压力，冗余表可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提高效率。

3、主键的设计
主键是必要的，SQL SERVER的主键同时是一个唯一索引，而且在实际应用中，我们往往选择最小的键组合作为主键，所以主键往往适合作为表的聚集索引，聚集索引对查询的影响是比较大的，这个在下面索引的叙述。
在有多个键的表，主键的选择也比较重要，一般选择总的长度小的键，小的键的比较速度快，同时小的键可以使主键的B树结构的层次更少。
主键的选择还要注意组合主键的字段次序，对于组合主键来说，不同的字段次序的主键的性能差别可能会很大，一般应该选择重复率低、单独或者组合查询可能性大的字段放在前面。

4、外键的设计
外键作为数据库对象，很多人认为麻烦而不用，实际上，外键在大部分情况下是很有用的，理由是：
外键是最高效的一致性维护方法，数据库的一致性要求，依次可以用外键、CHECK约束、规则约束、触发器、客户端程序，一般认为，离数据越近的方法效率越高。
谨慎使用级联删除和级联更新。
这里说的谨慎，是因为级联删除和级联更新有些突破了传统的关于外键的定义，功能有点太过强大，使用前必须确定自己已经把握好其功能范围，否则，级联删除和级联更新可能让你的数据莫名其妙的被修改或者丢失。
从性能看级联删除和级联更新是比其他方法更高效的方法。

5、字段的设计
字段是数据库最基本的单位，其设计对性能的影响是很大的，对此，提醒大家要注意以下几点：
A、数据类型尽量用数字型，数字型的比较比字符型的快很多。
B、数据类型尽量小，这里的尽量小是指在满足可以预见的未来需求的前提下的。
C、 尽量不要允许NULL，除非必要，可以用NOT NULL+DEFAULT代替。
D、少用TEXT和IMAGE，二进制字段的读写是比较慢的，而且，读取的方法也不多，大部分情况下最好不用。
E、自增字段要慎用，不利于数据迁移。

6、数据库物理存储和环境的设计
在设计阶段，可以对数据库的物理存储、操作系统环境、网络环境进行必要的设计，使得我们的系统在将来能适应比较多的用户并发和比较大的数据量。
这里需要注意文件组的作用，适用文件组可以有效把I/O操作分散到不同的物理硬盘，提高并发能力。

7、系统设计
整个系统的设计特别是系统结构设计对性能是有很大影响的，对于一般的OLTP系统，可以选择C/S结构、三层的C/S结构等，不同的系统结构其性能的关键也有所不同。
系统设计阶段应该归纳一些业务逻辑放在数据库编程实现，数据库编程包括数据库存储过程、触发器和函数，用数据库编程实现业务逻辑的好处是减少网络流量并可更充分利用数据库的预编译和缓存功能。

8、索引的设计
在设计阶段，可以根据功能和性能的需求进行初步的索引设计，这里需要根据预计的数据量和查询来设计索引，可能与将来实际使用的时候会有所区别。

关于索引的选择，马海祥提醒大家要注意以下几点：
A、根据数据量决定哪些表需要增加索引，数据量小的可以只有主键。
B、根据使用频率决定哪些字段需要建立索引，选择经常作为连接条件、筛选条件、聚合查询、排序的字段作为索引的候选字段。
C、把经常一起出现的字段组合在一起，组成组合索引，组合索引的字段顺序与主键一样，也需要把最常用的字段放在前面，把重复率低的字段放在前面。
D、一个表不要加太多索引，因为索引影响插入和更新的速度。

约束

1.数据类型约束

2.非空约束

    编写字段后增加  not null

3.唯一约束 uk unique

     编写字段后增加 unique

     null不属于重复的统计范畴。

     

     约束简写_字段名称，如唯一约束  简写UK，email上约束名称为UK_EMAIL

     增加错误数据：constraint uk_email unique(email)

 

4.主键约束 primary key ,PK

     主键约束=非空约束 + 唯一约束。如：公民身份证号

     增加错误数据：constraint pk_mid primary key(mid)

     

     一般至少设置一个主键或不设置，不用复合主键。

5.检查约束 check, ck

       由用户自己来设置过滤条件，如设置年龄范围：0-255.设置性别：男，女，中

       增加错误数据：constraint ck_age check (age between 0 and 255)

                     constraint ck_sex check (sex in('男','女'))

       即使设置多个检查约束，也是一个一个约束过滤；

       设置约束也多，更新时效率就越低，因此，一般的约束由程序进行校验。

     

6.主-外键约束 foreign key fk

  menber表与book表

  book表中参考member表的mid,book表中增加的mid必须是member表中存在的

  子表的某个字段与父表的主键或唯一约束的字段有关

  增加错误数据：constraint fk_mid foreign key(mid) reference menber(mid)

  

  限制1：删除父表前，一定要先删除子表。

  如果是不熟悉的数据库，删除时可强制删除(不建议)：

  drop table member cascade constraint;

  

  限制2：作为主表，必须有主键约束或唯一约束。

  限制3：数据级联操作问题。

    删除主表数据时，如果有子表数据，那么主表数据无法删除。

  级联删除：建立外键时，使用on delete cascade

  constraint fk_mid foreign key(mid) reference menber(mid) on delete cascade

  删除主表不删除字表：on delete set null

  constraint fk_mid foreign key(mid) reference menber(mid) on delete set null

 

修改约束：

首先明确：约束是在建立表的同时就已经设置完成。

只为了解的知识点：

增加约束：

alter table 表名称 add constraint 约束名称  约束类型（约束字段）[选项]

 

alter table member add constraint pk_mid primary ke (mid)

如果此时表中已经有违反约束的数据，那无法增加约束，需先将数据修改或删除，满足增加约束的条件

 

无法增加非空约,只能是通过修改字段类型方式。

alter table member modify (name varchar2(20) not null)

删除约束：

alter table member drop constraint pk_mid;

 

select * from user_cons_columns;

 

事务的4个特性

原子性：

一致性：

隔离性：

持久性：

 

--事务隔离性级别

可串行化：保证可串行化调度。

可重复读：只允许读取已提交数据，且一个事务在两个读取一个数据项期间，其它事务不能更新该数据。

已提交读：只允许读取已提交数据，但不要求可重复读。

未提交读：允许读取未提交数据。

 

--锁

共享锁

排它锁

 

--死锁原因

资源的相互等待

--预防死锁

破坏掉循环等待的情况

 

MySQL大表优化方案

当MySQL单表记录数过大时，增删改查性能都会急剧下降，可以参考以下步骤来优化：

单表优化

除非单表数据未来会一直不断上涨，否则不要一开始就考虑拆分，拆分会带来逻辑、部署、运维的各种复杂度，一般以整型值为主的表在千万级以下，字符串为主的表在五百万以下是没有太大问题的。而事实上很多时候MySQL单表的性能依然有不少优化空间，甚至能正常支撑千万级以上的数据量：

字段

尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED

VARCHAR的长度只分配真正需要的空间

使用枚举或整数代替字符串类型

尽量使用TIMESTAMP而非DATETIME，

单表不要有太多字段，建议在20以内

避免使用NULL字段，很难查询优化且占用额外索引空间

用整型来存IP

索引

索引并不是越多越好，要根据查询有针对性的创建，考虑在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描

应尽量避免在WHERE子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描

值分布很稀少的字段不适合建索引，例如"性别"这种只有两三个值的字段

字符字段只建前缀索引

字符字段最好不要做主键

不用外键，由程序保证约束

尽量不用UNIQUE，由程序保证约束

使用多列索引时主意顺序和查询条件保持一致，同时删除不必要的单列索引

查询SQL

可通过开启慢查询日志来找出较慢的SQL

不做列运算：SELECT id WHERE age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移至等号右边

 

sql语句尽可能简单：一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大sql可以堵死整个库

不用SELECT *

OR改写成IN：OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内

不用函数和触发器，在应用程序实现

避免%xxx式查询

少用JOIN

使用同类型进行比较，比如用'123'和'123'比，123和123比

尽量避免在WHERE子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描

对于连续数值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5

列表数据不要拿全表，要使用LIMIT来分页，每页数量也不要太大

引擎

目前广泛使用的是MyISAM和InnoDB两种引擎：

MyISAM

MyISAM引擎是MySQL 5.1及之前版本的默认引擎，它的特点是：

不支持行锁，读取时对需要读到的所有表加锁，写入时则对表加排它锁

不支持事务

不支持外键

不支持崩溃后的安全恢复

在表有读取查询的同时，支持往表中插入新纪录

支持BLOB和TEXT的前500个字符索引，支持全文索引

支持延迟更新索引，极大提升写入性能

对于不会进行修改的表，支持压缩表，极大减少磁盘空间占用

InnoDB

InnoDB在MySQL 5.5后成为默认索引，它的特点是：

支持行锁，采用MVCC来支持高并发

支持事务

支持外键

支持崩溃后的安全恢复

不支持全文索引

总体来讲，MyISAM适合SELECT密集型的表，而InnoDB适合INSERT和UPDATE密集型的表

系统调优参数

可以使用下面几个工具来做基准测试：

sysbench：一个模块化，跨平台以及多线程的性能测试工具

iibench-mysql：基于 Java 的 MySQL/Percona/MariaDB 索引进行插入性能测试工具

tpcc-mysql：Percona开发的TPC-C测试工具

具体的调优参数内容较多，具体可参考官方文档，这里介绍一些比较重要的参数：

back_log：back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说，如果MySql的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。可以从默认的50升至500

wait_timeout：数据库连接闲置时间，闲置连接会占用内存资源。可以从默认的8小时减到半小时

max_user_connection: 最大连接数，默认为0无上限，最好设一个合理上限

thread_concurrency：并发线程数，设为CPU核数的两倍

skip_name_resolve：禁止对外部连接进行DNS解析，消除DNS解析时间，但需要所有远程主机用IP访问

key_buffer_size：索引块的缓存大小，增加会提升索引处理速度，对MyISAM表性能影响最大。对于内存4G左右，可设为256M或384M，通过查询show status like 'key_read%'，保证key_reads / key_read_requests在0.1%以下最好

innodb_buffer_pool_size：缓存数据块和索引块，对InnoDB表性能影响最大。通过查询show status like 'Innodb_buffer_pool_read%'，保证 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests越高越好

innodb_additional_mem_pool_size：InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，当数据库对象非常多的时候，适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率，当过小的时候，MySQL会记录Warning信息到数据库的错误日志中，这时就需要该调整这个参数大小

 

innodb_log_buffer_size：InnoDB存储引擎的事务日志所使用的缓冲区，一般来说不建议超过32MB

query_cache_size：缓存MySQL中的ResultSet，也就是一条SQL语句执行的结果集，所以仅仅只能针对select语句。当某个表的数据有任何任何变化，都会导致所有引用了该表的select语句在Query Cache中的缓存数据失效。所以，当我们的数据变化非常频繁的情况下，使用Query Cache可能会得不偿失。根据命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))进行调整，一般不建议太大，256MB可能已经差不多了，大型的配置型静态数据可适当调大.
可以通过命令show status like 'Qcache_%'查看目前系统Query catch使用大小

read_buffer_size：MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，可以通过增加该变量值以及内存缓冲区大小提高其性能

sort_buffer_size：MySql执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能，可以尝试增加sort_buffer_size变量的大小

read_rnd_buffer_size：MySql的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。

record_buffer：每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，可能想要增加该值

thread_cache_size：保存当前没有与连接关联但是准备为后面新的连接服务的线程，可以快速响应连接的线程请求而无需创建新的

table_cache：类似于thread_cache_size，但用来缓存表文件，对InnoDB效果不大，主要用于MyISAM

升级硬件

Scale up，这个不多说了，根据MySQL是CPU密集型还是I/O密集型，通过提升CPU和内存、使用SSD，都能显著提升MySQL性能

读写分离

也是目前常用的优化，从库读主库写，一般不要采用双主或多主引入很多复杂性，尽量采用文中的其他方案来提高性能。同时目前很多拆分的解决方案同时也兼顾考虑了读写分离

缓存

缓存可以发生在这些层次：

MySQL内部：在系统调优参数介绍了相关设置

数据访问层：比如MyBatis针对SQL语句做缓存，而Hibernate可以精确到单个记录，这里缓存的对象主要是持久化对象Persistence Object

应用服务层：这里可以通过编程手段对缓存做到更精准的控制和更多的实现策略，这里缓存的对象是数据传输对象Data Transfer Object

Web层：针对web页面做缓存

浏览器客户端：用户端的缓存

可以根据实际情况在一个层次或多个层次结合加入缓存。这里重点介绍下服务层的缓存实现，目前主要有两种方式：

直写式（Write Through）：在数据写入数据库后，同时更新缓存，维持数据库与缓存的一致性。这也是当前大多数应用缓存框架如Spring Cache的工作方式。这种实现非常简单，同步好，但效率一般。

回写式（Write Back）：当有数据要写入数据库时，只会更新缓存，然后异步批量的将缓存数据同步到数据库上。这种实现比较复杂，需要较多的应用逻辑，同时可能会产生数据库与缓存的不同步，但效率非常高。

表分区

MySQL在5.1版引入的分区是一种简单的水平拆分，用户需要在建表的时候加上分区参数，对应用是透明的无需修改代码

对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成，实现分区的代码实际上是通过对一组底层表的对象封装，但对SQL层来说是一个完全封装底层的黑盒子。MySQL实现分区的方式也意味着索引也是按照分区的子表定义，没有全局索引

 

用户的SQL语句是需要针对分区表做优化，SQL条件中要带上分区条件的列，从而使查询定位到少量的分区上，否则就会扫描全部分区，可以通过EXPLAIN PARTITIONS来查看某条SQL语句会落在那些分区上，从而进行SQL优化，如下图5条记录落在两个分区上：

mysql>explain partitions select count(1) from user_partition where id in (1,2,3,4,5);

+----+-------------+----------------+------------+-------+-------+|id| select_type|table| partitions|type|possible_keys|key| key_len |ref| rows | Extra|

+----+-------------+----------------+------------+-------+-------|1|SIMPLE|user_partition|p1,p4|range|PRIMARY|PRIMARY|8 NULL|5|Using where; Using index |

+----+-------------+----------------+------------+-------+-------

1 row in set (0.00 sec)

分区的好处是：

可以让单表存储更多的数据

分区表的数据更容易维护，可以通过清楚整个分区批量删除大量数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作

部分查询能够从查询条件确定只落在少数分区上，速度会很快

分区表的数据还可以分布在不同的物理设备上，从而搞笑利用多个硬件设备

可以使用分区表赖避免某些特殊瓶颈，例如InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争

可以备份和恢复单个分区

分区的限制和缺点：

一个表最多只能有1024个分区

如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来

分区表无法使用外键约束

NULL值会使分区过滤无效

所有分区必须使用相同的存储引擎

分区的类型：

RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区

LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择

HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式

KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值

分区适合的场景有：

最适合的场景数据的时间序列性比较强，则可以按时间来分区，如下所示：

CREATE TABLE members (

    firstname VARCHAR(25) NOT NULL,

    lastname VARCHAR(25) NOT NULL,

    username VARCHAR(16) NOT NULL,

    email VARCHAR(35),

    joined DATE NOT NULL

)PARTITION BY RANGE( YEAR(joined) ) (

    PARTITION p0 VALUES LESS THAN (1960),

    PARTITION p1 VALUES LESS THAN (1970),

    PARTITION p2 VALUES LESS THAN (1980),

    PARTITION p3 VALUES LESS THAN (1990),

    PARTITION p4 VALUES LESS THAN MAXVALUE

);

查询时加上时间范围条件效率会非常高，同时对于不需要的历史数据能很容的批量删除。

如果数据有明显的热点，而且除了这部分数据，其他数据很少被访问到，那么可以将热点数据单独放在一个分区，让这个分区的数据能够有机会都缓存在内存中，查询时只访问一个很小的分区表，能够有效使用索引和缓存

另外MySQL有一种早期的简单的分区实现 - 合并表（merge table），限制较多且缺乏优化，不建议使用，应该用新的分区机制来替代

垂直拆分

垂直分库是根据数据库里面的数据表的相关性进行拆分，比如：一个数据库里面既存在用户数据，又存在订单数据，那么垂直拆分可以把用户数据放到用户库、把订单数据放到订单库。垂直分表是对数据表进行垂直拆分的一种方式，常见的是把一个多字段的大表按常用字段和非常用字段进行拆分，每个表里面的数据记录数一般情况下是相同的，只是字段不一样，使用主键关联

比如原始的用户表是：

 

垂直拆分后是：

 

垂直拆分的优点是：

可以使得行数据变小，一个数据块(Block)就能存放更多的数据，在查询时就会减少I/O次数(每次查询时读取的Block 就少)

可以达到最大化利用Cache的目的，具体在垂直拆分的时候可以将不常变的字段放一起，将经常改变的放一起

数据维护简单

缺点是：

主键出现冗余，需要管理冗余列

会引起表连接JOIN操作（增加CPU开销）可以通过在业务服务器上进行join来减少数据库压力

依然存在单表数据量过大的问题（需要水平拆分）

事务处理复杂

水平拆分

概述

水平拆分是通过某种策略将数据分片来存储，分库内分表和分库两部分，每片数据会分散到不同的MySQL表或库，达到分布式的效果，能够支持非常大的数据量。前面的表分区本质上也是一种特殊的库内分表

库内分表，仅仅是单纯的解决了单一表数据过大的问题，由于没有把表的数据分布到不同的机器上，因此对于减轻MySQL服务器的压力来说，并没有太大的作用，大家还是竞争同一个物理机上的IO、CPU、网络，这个就要通过分库来解决

前面垂直拆分的用户表如果进行水平拆分，结果是：

 

实际情况中往往会是垂直拆分和水平拆分的结合，即将Users_A_M和Users_N_Z再拆成Users和UserExtras，这样一共四张表

水平拆分的优点是:

不存在单库大数据和高并发的性能瓶颈

应用端改造较少

提高了系统的稳定性和负载能力

缺点是：

分片事务一致性难以解决

跨节点Join性能差，逻辑复杂

数据多次扩展难度跟维护量极大

分片原则

能不分就不分，参考单表优化

分片数量尽量少，分片尽量均匀分布在多个数据结点上，因为一个查询SQL跨分片越多，则总体性能越差，虽然要好于所有数据在一个分片的结果，只在必要的时候进行扩容，增加分片数量

分片规则需要慎重选择做好提前规划，分片规则的选择，需要考虑数据的增长模式，数据的访问模式，分片关联性问题，以及分片扩容问题，最近的分片策略为范围分片，枚举分片，一致性Hash分片，这几种分片都有利于扩容

尽量不要在一个事务中的SQL跨越多个分片，分布式事务一直是个不好处理的问题

查询条件尽量优化，尽量避免Select * 的方式，大量数据结果集下，会消耗大量带宽和CPU资源，查询尽量避免返回大量结果集，并且尽量为频繁使用的查询语句建立索引。

通过数据冗余和表分区赖降低跨库Join的可能

这里特别强调一下分片规则的选择问题，如果某个表的数据有明显的时间特征，比如订单、交易记录等，则他们通常比较合适用时间范围分片，因为具有时效性的数据，我们往往关注其近期的数据，查询条件中往往带有时间字段进行过滤，比较好的方案是，当前活跃的数据，采用跨度比较短的时间段进行分片，而历史性的数据，则采用比较长的跨度存储。

总体上来说，分片的选择是取决于最频繁的查询SQL的条件，因为不带任何Where语句的查询SQL，会遍历所有的分片，性能相对最差，因此这种SQL越多，对系统的影响越大，所以我们要尽量避免这种SQL的产生。

解决方案

由于水平拆分牵涉的逻辑比较复杂，当前也有了不少比较成熟的解决方案。这些方案分为两大类：客户端架构和代理架构。

客户端架构

通过修改数据访问层，如JDBC、Data Source、MyBatis，通过配置来管理多个数据源，直连数据库，并在模块内完成数据的分片整合，一般以Jar包的方式呈现

这是一个客户端架构的例子：

 

可以看到分片的实现是和应用服务器在一起的，通过修改Spring JDBC层来实现

客户端架构的优点是：

应用直连数据库，降低外围系统依赖所带来的宕机风险

集成成本低，无需额外运维的组件

缺点是：

限于只能在数据库访问层上做文章，扩展性一般，对于比较复杂的系统可能会力不从心

将分片逻辑的压力放在应用服务器上，造成额外风险

代理架构

通过独立的中间件来统一管理所有数据源和数据分片整合，后端数据库集群对前端应用程序透明，需要独立部署和运维代理组件

这是一个代理架构的例子：

 

代理组件为了分流和防止单点，一般以集群形式存在，同时可能需要Zookeeper之类的服务组件来管理

代理架构的优点是：

能够处理非常复杂的需求，不受数据库访问层原来实现的限制，扩展性强

对于应用服务器透明且没有增加任何额外负载

缺点是：

需部署和运维独立的代理中间件，成本高

应用需经过代理来连接数据库，网络上多了一跳，性能有损失且有额外风险

各方案比较

出品方

架构模型

支持数据库

分库

分表

读写分离

外部依赖

是否开源

实现语言

支持语言

最后更新

Github星数

MySQL Fabric

MySQL官方

代理架构

MySQL

有

有

有

无

是

python

无限制

4个月前

35

Cobar

阿里巴巴

代理架构

MySQL

有

无

无

无

是

Java

无限制

两年前

1287

Cobar Client

阿里巴巴

客户端架构

MySQL

有

无

无

无

是

Java

Java

三年前

344

TDDL

淘宝

客户端架构

无限制

有

有

有

Diamond

只开源部分

Java

Java

未知

519

Atlas

奇虎360

代理架构

MySQL

有

有

有

无

是

C

无限制

10个月前

1941

Heisenberg

百度熊照

代理架构

MySQL

有

有

有

无

是

Java

无限制

2个月前

197

TribeDB

个人

代理架构

MySQL

有

有

有

无

是

NodeJS

无限制

3个月前

126

ShardingJDBC

当当

客户端架构

MySQL

有

有

有

无

是

Java

Java

当天

1144

Shark

个人

客户端架构

MySQL

有

有

无

无

是

Java

Java

两天前

84

KingShard

个人

代理架构

MySQL

有

有

有

无

是

Golang

无限制

两天前

1836

OneProxy

平民软件

代理架构

MySQL

有

有

有

无

否

未知

无限制

未知

未知

MyCat

社区

代理架构

MySQL

有

有

有

无

是

Java

无限制

两天前

1270

Vitess

Youtube

代理架构

MySQL

有

有

有

无

是

Golang

无限制

当天

3636

Mixer

个人

代理架构

MySQL

有

有

无

无

是

Golang

无限制

9个月前

472

JetPants

Tumblr

客户端架构

MySQL

有

有

无

无

是

Ruby

Ruby

10个月前

957

HibernateShard

Hibernate

客户端架构

无限制

有

有

无

无

是

Java

Java

4年前

57

MybatisShard

MakerSoft

客户端架构

无限制

有

有

无

无

是

Java

Java

11个月前

119

Gizzard

Twitter

代理架构

无限制

有

有

无

无

是

Java

无限制

3年前

2087

如此多的方案，如何进行选择？可以按以下思路来考虑：

1.确定是使用代理架构还是客户端架构。中小型规模或是比较简单的场景倾向于选择客户端架构，复杂场景或大规模系统倾向选择代理架构

2.具体功能是否满足，比如需要跨节点ORDER BY，那么支持该功能的优先考虑

3.不考虑一年内没有更新的产品，说明开发停滞，甚至无人维护和技术支持

4.最好按大公司->社区->小公司->个人这样的出品方顺序来选择

5.选择口碑较好的，比如github星数、使用者数量质量和使用者反馈

6.开源的优先，往往项目有特殊需求可能需要改动源代码

按照上述思路，推荐以下选择：

客户端架构：ShardingJDBC

代理架构：MyCat或者Atlas

数据库优化

1.通过命令行参数和配置文件

2.位置/etc/my.cnf或/etc/mysql/my.cnf

3.最好在配置文件中带上注释

4.一般mysql默认最好使用InnoDB存储引擎，最好显示配置

5.配置mysql正确使用内存的步骤：

1.确定可以使用内存的上限

2.确定每个连接mysql需要使用多少内存，如排序缓冲和临时表

3.确定操作系统需要多少内存才够用。包括同一台机器上其它程序使用的内存，如定时任务

4.剩下的全部给mysql内存，如InnoDB的缓冲池，InnoDB日志文件和MyISAM数据的操作系统缓存，查询缓存

6.InnoDB的I/O配置

InnoDB事务日志

InnoDB使用日志来减少提交事务时的开销。

InnoDB用日志把随机I/O变成顺序I/O

InnoDB使用一个后台线程智能地刷新这些变更到数据文件。

InnoDB使用多个文件作为一组循环日志

数据大小和访问模式影响恢复时间。

日志缓冲必须被刷新到持久化存储，以确保提交的事务完全被持久化了。

InnoDB把数据保存在表空间内，本质上是一个由一个或多个磁盘文件组成的虚拟文件系统。

通过innodb_data_file_path配置项定制表空间文件。

7.MyISAM的I/O配置

8.InnoDB的并发配置

9.MyISAM的并发配置

10.基于工作负载的配置

11.完成基本配置

配置文件：

 

[client]

port = 3306

socket = /tmp/mysql.sock

[mysqld]

port = 3306

socket = /tmp/mysql.sock

basedir = /usr/local/mysql

datadir = /data/mysql

pid-file = /data/mysql/mysql.pid

user = mysql

bind-address = 0.0.0.0

server-id = 1 #表示是本机的序号为1,一般来讲就是master的意思

skip-name-resolve

# 禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，

# 则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求

#skip-networking

back_log = 600

# MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用，

# 然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。

# 如果期望在一个短时间内有很多连接，你需要增加它。也就是说，如果MySQL的连接数据达到max_connections时，新来的请求将会被存在堆栈中，

# 以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。

# 另外，这值（back_log）限于您的操作系统对到来的TCP/IP连接的侦听队列的大小。

# 你的操作系统在这个队列大小上有它自己的限制（可以检查你的OS文档找出这个变量的最大值），试图设定back_log高于你的操作系统的限制将是无效的。

max_connections = 1000

# MySQL的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。可以过'conn%'通配符查看当前状态的连接数量，以定夺该值的大小。

max_connect_errors = 6000

# 对于同一主机，如果有超出该参数值个数的中断错误连接，则该主机将被禁止连接。如需对该主机进行解禁，执行：FLUSH HOST。

open_files_limit = 65535

# MySQL打开的文件描述符限制，默认最小1024;当open_files_limit没有被配置的时候，比较max_connections*5和ulimit -n的值，哪个大用哪个，

# 当open_file_limit被配置的时候，比较open_files_limit和max_connections*5的值，哪个大用哪个。

table_open_cache = 128

# MySQL每打开一个表，都会读入一些数据到table_open_cache缓存中，当MySQL在这个缓存中找不到相应信息时，才会去磁盘上读取。默认值64

# 假定系统有200个并发连接，则需将此参数设置为200*N(N为每个连接所需的文件描述符数目)；

# 当把table_open_cache设置为很大时，如果系统处理不了那么多文件描述符，那么就会出现客户端失效，连接不上

max_allowed_packet = 4M

# 接受的数据包大小；增加该变量的值十分安全，这是因为仅当需要时才会分配额外内存。例如，仅当你发出长查询或MySQLd必须返回大的结果行时MySQLd才会分配更多内存。

# 该变量之所以取较小默认值是一种预防措施，以捕获客户端和服务器之间的错误信息包，并确保不会因偶然使用大的信息包而导致内存溢出。

binlog_cache_size = 1M

# 一个事务，在没有提交的时候，产生的日志，记录到Cache中；等到事务提交需要提交的时候，则把日志持久化到磁盘。默认binlog_cache_size大小32K

max_heap_table_size = 8M

# 定义了用户可以创建的内存表(memory table)的大小。这个值用来计算内存表的最大行数值。这个变量支持动态改变

tmp_table_size = 16M

# MySQL的heap（堆积）表缓冲大小。所有联合在一个DML指令内完成，并且大多数联合甚至可以不用临时表即可以完成。

# 大多数临时表是基于内存的(HEAP)表。具有大的记录长度的临时表 (所有列的长度的和)或包含BLOB列的表存储在硬盘上。

# 如果某个内部heap（堆积）表大小超过tmp_table_size，MySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表。还可以通过设置tmp_table_size选项来增加临时表的大小。也就是说，如果调高该值，MySQL同时将增加heap表的大小，可达到提高联接查询速度的效果

read_buffer_size = 2M

# MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。

# 如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能

read_rnd_buffer_size = 8M

# MySQL的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，

# MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大

sort_buffer_size = 8M

# MySQL执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。

# 如果不能，可以尝试增加sort_buffer_size变量的大小

join_buffer_size = 8M

# 联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每连接独享

thread_cache_size = 8

# 这个值（默认8）表示可以重新利用保存在缓存中线程的数量，当断开连接时如果缓存中还有空间，那么客户端的线程将被放到缓存中，

# 如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，

# 增加这个值可以改善系统性能.通过比较Connections和Threads_created状态的变量，可以看到这个变量的作用。(–>表示要调整的值)

# 根据物理内存设置规则如下：

# 1G  —> 8

# 2G  —> 16

# 3G  —> 32

# 大于3G  —> 64

query_cache_size = 8M

#MySQL的查询缓冲大小（从4.0.1开始，MySQL提供了查询缓冲机制）使用查询缓冲，MySQL将SELECT语句和查询结果存放在缓冲区中，

# 今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果。根据MySQL用户手册，使用查询缓冲最多可以达到238%的效率。

# 通过检查状态值'Qcache_%'，可以知道query_cache_size设置是否合理：如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况，

# 如果Qcache_hits的值也非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小；如果Qcache_hits的值不大，则表明你的查询重复率很低，

# 这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。此外，在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲

query_cache_limit = 2M

#指定单个查询能够使用的缓冲区大小，默认1M

key_buffer_size = 4M

#指定用于索引的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使它太大，

# 系统将开始换页并且真的变慢了。对于内存在4GB左右的服务器该参数可设置为384M或512M。通过检查状态值Key_read_requests和Key_reads，

# 可以知道key_buffer_size设置是否合理。比例key_reads/key_read_requests应该尽可能的低，

# 至少是1:100，1:1000更好(上述状态值可以使用SHOW STATUS LIKE 'key_read%'获得)。注意：该参数值设置的过大反而会是服务器整体效率降低

ft_min_word_len = 4

# 分词词汇最小长度，默认4

transaction_isolation = REPEATABLE-READ

# MySQL支持4种事务隔离级别，他们分别是：

# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.

# 如没有指定，MySQL默认采用的是REPEATABLE-READ，ORACLE默认的是READ-COMMITTED

log_bin = mysql-bin

binlog_format = mixed

expire_logs_days = 30 #超过30天的binlog删除

log_error = /data/mysql/mysql-error.log #错误日志路径

slow_query_log = 1

long_query_time = 1 #慢查询时间 超过1秒则为慢查询

slow_query_log_file = /data/mysql/mysql-slow.log

performance_schema = 0

explicit_defaults_for_timestamp

#lower_case_table_names = 1 #不区分大小写

skip-external-locking #MySQL选项以避免外部锁定。该选项默认开启

default-storage-engine = InnoDB #默认存储引擎

innodb_file_per_table = 1

# InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间

# 独立表空间优点：

# 1．每个表都有自已独立的表空间。

# 2．每个表的数据和索引都会存在自已的表空间中。

# 3．可以实现单表在不同的数据库中移动。

# 4．空间可以回收（除drop table操作处，表空不能自已回收）

# 缺点：

# 单表增加过大，如超过100G

# 结论：

# 共享表空间在Insert操作上少有优势。其它都没独立表空间表现好。当启用独立表空间时，请合理调整：innodb_open_files

innodb_open_files = 500

# 限制Innodb能打开的表的数据，如果库里的表特别多的情况，请增加这个。这个值默认是300

innodb_buffer_pool_size = 64M

# InnoDB使用一个缓冲池来保存索引和原始数据, 不像MyISAM.

# 这里你设置越大,你在存取表里面数据时所需要的磁盘I/O越少.

# 在一个独立使用的数据库服务器上,你可以设置这个变量到服务器物理内存大小的80%

# 不要设置过大,否则,由于物理内存的竞争可能导致操作系统的换页颠簸.

# 注意在32位系统上你每个进程可能被限制在 2-3.5G 用户层面内存限制,

# 所以不要设置的太高.

innodb_write_io_threads = 4

innodb_read_io_threads = 4

# innodb使用后台线程处理数据页上的读写 I/O(输入输出)请求,根据你的 CPU 核数来更改,默认是4

# 注:这两个参数不支持动态改变,需要把该参数加入到my.cnf里，修改完后重启MySQL服务,允许值的范围从 1-64

innodb_thread_concurrency = 0

# 默认设置为 0,表示不限制并发数，这里推荐设置为0，更好去发挥CPU多核处理能力，提高并发量

innodb_purge_threads = 1

# InnoDB中的清除操作是一类定期回收无用数据的操作。在之前的几个版本中，清除操作是主线程的一部分，这意味着运行时它可能会堵塞其它的数据库操作。

# 从MySQL5.5.X版本开始，该操作运行于独立的线程中,并支持更多的并发数。用户可通过设置innodb_purge_threads配置参数来选择清除操作是否使用单

# 独线程,默认情况下参数设置为0(不使用单独线程),设置为 1 时表示使用单独的清除线程。建议为1

innodb_flush_log_at_trx_commit = 2

# 0：如果innodb_flush_log_at_trx_commit的值为0,log buffer每秒就会被刷写日志文件到磁盘，提交事务的时候不做任何操作（执行是由mysql的master thread线程来执行的。

# 主线程中每秒会将重做日志缓冲写入磁盘的重做日志文件(REDO LOG)中。不论事务是否已经提交）默认的日志文件是ib_logfile0,ib_logfile1

# 1：当设为默认值1的时候，每次提交事务的时候，都会将log buffer刷写到日志。

# 2：如果设为2,每次提交事务都会写日志，但并不会执行刷的操作。每秒定时会刷到日志文件。要注意的是，并不能保证100%每秒一定都会刷到磁盘，这要取决于进程的调度。

# 每次事务提交的时候将数据写入事务日志，而这里的写入仅是调用了文件系统的写入操作，而文件系统是有 缓存的，所以这个写入并不能保证数据已经写入到物理磁盘

# 默认值1是为了保证完整的ACID。当然，你可以将这个配置项设为1以外的值来换取更高的性能，但是在系统崩溃的时候，你将会丢失1秒的数据。

# 设为0的话，mysqld进程崩溃的时候，就会丢失最后1秒的事务。设为2,只有在操作系统崩溃或者断电的时候才会丢失最后1秒的数据。InnoDB在做恢复的时候会忽略这个值。

# 总结

# 设为1当然是最安全的，但性能页是最差的（相对其他两个参数而言，但不是不能接受）。如果对数据一致性和完整性要求不高，完全可以设为2，如果只最求性能，例如高并发写的日志服务器，设为0来获得更高性能

innodb_log_buffer_size = 2M

# 此参数确定些日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，但意外的故障将会丢失数据。MySQL开发人员建议设置为1－8M之间

innodb_log_file_size = 32M

# 此参数确定数据日志文件的大小，更大的设置可以提高性能，但也会增加恢复故障数据库所需的时间

innodb_log_files_in_group = 3

# 为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3

innodb_max_dirty_pages_pct = 90

# innodb主线程刷新缓存池中的数据，使脏数据比例小于90%

innodb_lock_wait_timeout = 120

# InnoDB事务在被回滚之前可以等待一个锁定的超时秒数。InnoDB在它自己的锁定表中自动检测事务死锁并且回滚事务。InnoDB用LOCK TABLES语句注意到锁定设置。默认值是50秒

bulk_insert_buffer_size = 8M

# 批量插入缓存大小， 这个参数是针对MyISAM存储引擎来说的。适用于在一次性插入100-1000+条记录时， 提高效率。默认值是8M。可以针对数据量的大小，翻倍增加。

myisam_sort_buffer_size = 8M

# MyISAM设置恢复表之时使用的缓冲区的尺寸，当在REPAIR TABLE或用CREATE INDEX创建索引或ALTER TABLE过程中排序 MyISAM索引分配的缓冲区

myisam_max_sort_file_size = 10G

# 如果临时文件会变得超过索引，不要使用快速排序索引方法来创建一个索引。注释：这个参数以字节的形式给出

myisam_repair_threads = 1

# 如果该值大于1，在Repair by sorting过程中并行创建MyISAM表索引(每个索引在自己的线程内)

interactive_timeout = 28800

# 服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。默认值：28800秒（8小时）

wait_timeout = 28800

# 服务器关闭非交互连接之前等待活动的秒数。在线程启动时，根据全局wait_timeout值或全局interactive_timeout值初始化会话wait_timeout值，

# 取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义)。参数默认值：28800秒（8小时）

# MySQL服务器所支持的最大连接数是有上限的，因为每个连接的建立都会消耗内存，因此我们希望客户端在连接到MySQL Server处理完相应的操作后，

# 应该断开连接并释放占用的内存。如果你的MySQL Server有大量的闲置连接，他们不仅会白白消耗内存，而且如果连接一直在累加而不断开，

# 最终肯定会达到MySQL Server的连接上限数，这会报'too many connections'的错误。对于wait_timeout的值设定，应该根据系统的运行情况来判断。

# 在系统运行一段时间后，可以通过show processlist命令查看当前系统的连接状态，如果发现有大量的sleep状态的连接进程，则说明该参数设置的过大，

# 可以进行适当的调整小些。要同时设置interactive_timeout和wait_timeout才会生效。

[mysqldump]

quick

max_allowed_packet = 16M #服务器发送和接受的最大包长度

[myisamchk]

key_buffer_size = 8M

sort_buffer_size = 8M

read_buffer = 4M

write_buffer = 4M