MVCC
Multiversion concurrency control (多版本并发控制)
并发访问(读或写)数据库时，对正在事务内处理的数据做 多版本的管理。以达到用来避免写操作的堵塞，从而引发读操 作的并发问题。
备注：查询的条件1和条件2是&&关系
备注：先执行3,4没有commit。解决不可重复读或脏读采用的方案不是MVCC，而是快照读
Mysql服务器参数类型
一.基于参数的作用域：
全局参数
set global autocommit = ON/OFF;
会话参数(会话参数不单独设置则会采用全局参数)
set session autocommit = ON/OFF;
注意：
全局参数的设定对于已经存在的会话无法生效
会话参数的设定随着会话的销毁而失效
全局类的统一配置建议配置在默认配置文件中， 否则重启服务会导致配置失效
二.如何寻找配置文件
mysql --help 寻找配置文件的位置和加载顺序
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
mysql --help | grep -A 1 'Default options are read from the following
files in the given order'
三、Mysql内存参数配置
每一个connection内存参数配置：
sort_buffer_size connection排序缓冲区大小
建议256K(默认值)-> 2M之内
当查询语句中有需要文件排序功能时， 马上为connection分配配置的内
存大小
join_buffer_size connection关联查询缓冲区大小
建议256K(默认值)-> 1M之内
当查询语句中有关联查询时， 马上分配配置大小的内存用这个关联查
询， 所以有可能在一个查询语句中会分配很多个关联查询缓冲区
上述配置4000连接占用内存：
4000*(0.256M+0.256M) = 2G
Innodb_buffer_pool_size
innodb buffer/cache的大小（ 默认128M）
Innodb_buffer_pool
数据缓存
索引缓存
缓冲数据
内部结构
大的缓冲池可以减小多次磁盘I/O访问相同的表数据以提高性能
参考计算公式：
Innodb_buffer_pool_size = （ 总物理内存 - 系统运行所用 - connection 所用） * 90%
四、常见的全局配置文件
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
max_connections=2000
lower_case_table_names = 0 #表名区分大小写
server-id = 1
tmp_table_size=16M
transaction_isolation = REPEATABLE-READ
ready_only=1
五、Mysql其他配置参数
wait_timeout
服务器关闭非交互连接之前等待活动的秒数
innodb_open_files
限制Innodb能打开的表的个数
innodb_write_io_threads
innodb_read_io_threads
innodb使用后台线程处理innodb缓冲区数据页上的读写 I/O(输入输出)请求
innodb_lock_wait_timeout
InnoDB事务在被回滚之前可以等待一个锁定的超时秒数
https://www.cnblogs.com/wyy123/p/6092976.html 常见配置的帖子
主键顺序插入和随机插入对性能的影响
首先来看顺序插入的情况。如果主键是顺序的，所以InnoDB会把每插入的记录存储在上一条记录的后面。当达到页的最大填充因子时（InnoDB默认的最大填充因子是15/16，流出不封空间用于以后可能产生的修改），下一条记录就会写入新的页中。一旦数据按照这种顺序的的方式插入，主键就会近似于被顺序的记录填满。
再来看随机插入的情况，比如使用了uuid聚簇索引的表插入数据。因为新插入的值是随机，可能比上一个插入的主键值大，也可能小，所以InnoDB无法简单的总是把新的记录插入到索引的最后，也就是说插入的位置很有可能是在现有数据的中间。这往往会导致性能恶化。
这种随机插入方式可能会有以下缺点：
- 写入的目标页可能不在内存缓存区，那么插入记录的时候需要先从磁盘读取目标页到内存中。这会导致大量的随机IO.如果是顺序插入，由于是插入到上一个记录的后面，则大多数情况下（不需要开辟新页的情况）磁盘页是已经加载到内存了的。
- 因为写入是乱序的，InnoDB可能需要不断的的做页分裂操作，以便为新的行分配空间。而页分裂会导致移动大量的数据，而且一次分裂至少要修改三个页而不是一个页。
- 由于频繁的分页，页面会变得稀疏并被不规则的填充，最后会导致数据碎片。
所以，当把随机值载入到聚簇索引后，最好做一次optimize table来重建表并优化页的填充。
当然顺序的主键在一些情况下也有可能有一些缺点。比如对于高并发的场景，如果按主键顺序插入可能会造成明显的争用。因为所有的插入都是上一次记录的后面，所以主键的上边界就会成为热点，这可能会导致间隙锁竞争。如果是随机插入的话，因为插入的地方可能都不一样，所以竞争就是少一点。
MySQL的SQL书写和优化技巧
一、MySQL正则表达式
使用REGEXP和NOT REGEXP操作符（或RLIKE和NOT RLIKE，它们是同义词）
“.”匹配任何单个的字符。（单字节字符）
“ * ”匹配零个或多个在它前面的东西
“^”在模式开始处
“$”在模式的结尾
SELECT * FROM user WHERE u_name REGEXP ‘^三’; SELECT * FROM user WHERE u_name REGEXP ‘三$’;
二、需求：随机从表中取N条数据
SELECT * FROM t_share ORDER BY rand() LIMIT 10;
百万大表随机取N条数据的优化
SELECT * FROM t_table WHERE id >= ( (SELECT max(id) FROM t_table) - (SELECT min(id) FROM t_table) ) * RAND() + (SELECT min(id) FROM t_table) LIMIT 10
三、分组统计
MySQL提供了 group by with rollup 函数进行group by 字段的汇总
SELECT IFNULL(b.城市,"总计") AS 城市,SUM(IF(b.年月=201607,b.金额,NULL)) AS 7月金额,SUM(IF(b.年月=201608,b.金额,NULL)) AS 8月金额,SUM(IF(b.年月=201609,b.金额,NULL)) AS 9月金额 FROM ( SELECT IFNULL(city,'空城市') AS 城市,DATE_FORMAT(order_time,"%Y%m") AS 年月,SUM(pay_money) AS 金额 FROM test_a03order AS a GROUP BY city,DATE_FORMAT(order_time,"%Y%m") ) AS b GROUP BY b.城市 WITH ROLLUP
四、MySQL中，追踪SQL。在线设置Trace，执行的sql保存在log文本中。
-- 查看MySQL的版本号
select version();
-- 查询关于log的设置 （若版本号为 ‘5.1.29’以上版本 like 'general_log%'）
SHOW VARIABLES LIKE 'general_log%';
-- 开启日志
SET GLOBAL general_log ='ON';
-- 设置日志的文件路径：本例在Windows下
SET GLOBAL general_log_file='D:/MySql/Sql.log';
-- 设置结束，执行的sql语句会保存在‘D:/MySql/Sql.log’，可通过notepad++查看wu
五、MySQL中ORDER BY与LIMIT一起使用（有坑）
1、如果你只需要结果集中的某几行，那么建议使用limit。这样这样的话可以避免抓取全部结果集，然后再丢弃那些你不要的行。
2、对于order by查询，带或者不带limit可能返回行的顺序是不一样的。
3、如果limit row_count 与 order by 一起使用，那么在找到第一个row_count就停止排序，直接返回。
4、如果order by列有相同的值，那么MySQL可以自由地以任何顺序返回这些行。换言之，只要order by列的值不重复，就可以保证返回的顺序。
5、可以在order by子句中包含附加列，以使顺序具有确定性。
