mysql事务和group by优化

mysql的客户端和服务端建立连接，select会监听socket事件为新来的连接请求分配一个线程，多个线程在对统一资源的访问，由于并发的不确定性，所以引入事务的概念，事务有四大特性：原子性、隔离性、一致性、持久性。隔离性由于每个线程对同一资源的修改是不能相互影响，就需要加锁来保证数据修改的一致性，事务的修改访问有两种方式，对1于修改数据要加锁访问2对于读数据要保证数据的可靠性，以加锁的方式读会影响性能，不加锁可能会胀读，不可重复度，幻读等问题，为了解决问题通过mvcc来处理加上隔离级别的方式来解决特点问题，mysql的默认隔离级别是可重复读，隔离级别由高到低

1可串行化serializable（读写都加锁 最严格的隔离级别）

2可重复读repeattable 写加锁读默认不加锁，mvcc（读取事务开始之前的版本且非锁定都）事务提交会为每一行变动的数据增加版本信息例如线程A执行读事务A sql1 sql2，事务A在在创建的时候会会获取sql数据的最新版本号，事务B线程在sql1后提交修改sql3，则事务A对所有sql记录的版本号不会该变，sql2读的时候不受影响，它不会去读sql3的版本。（读取事务之前的版本即快照读）3当前读，快照读，sql1，sql2都没有修改某一行，就是快照读，如果修改了就会当前读，就是读这个事务中修改的内容，如果没有修改就是快照读事务记录的版本号读。

3读已提交read committed 写加锁，读默认不加锁，mvcc（读取事务最新的状态）不可重复读、幻读（每降低一次隔离级别就会出现一些问题）读未提交会出现胀读、读已提交会出现不可重复读问题、可重复读会出现幻读问题，读未提交这三个问题都会出现，也是最低的隔离级别。

4读未提交read uncommitted写加锁，读不加锁也没有版本信息，读到的数据永远是最新数据：可能问题胀读、不可重复读、幻读。sql1，sql3一个事务sql2，sql4一个事务，sql2比sql3先执行，没有提交，sql3可以读到sql2的信息，sql没有提交，出现胀读。存在即合理应用场景 主从复制 主数据库rr 从数据库1ru从数据库2ru，从读数据提高写性能对读不加锁读的性能也得到高。

胀读：产生原因：读不加锁，没有使用mvcc（版本产生dml操作提交的时候才会产生某行版本信息）

不可重复读：出现读已提交、读未提交的隔离级别上，读和写的影响造成，一个事务读到另外一个事务提交到的数据，并且该事务之前已读一次，同一事务造成读取数据不一致。

幻读：在读已经提交中，读到额外的未知的插入的数据、不可重复读是修改了每一行导致读的数据和前面同一事务读到的不一致，也就是两次查询得到的结果集不一致，如insert、delete、数量不一致、内容不一致如先增加1行，后删除1行出现幻读

不可重复读与幻读的区别？从隔离级别出发

读已提交：mvcc 读取最新版本 ，写是加锁，读是当前读，读到其他事务提交的数据会导致同一事务内，读到不同数据

可重复读 ：mvcc 读取事务开始之前的版本，写加锁，快照读不会产生不可重复度问题，如果该事务内修改后就读取该事务内修改后的版本

共同点：其他事务dml提交影响了本事务的读取

不同点：不可重复度，同一事务两次读到的记录不一致 幻读两次读同一范围的记录不一致的结果集。

具体解决：不可重复度，提升隔离级别可以解决。

幻读：把快照读改成当前读（mvcc关系了当前读和快照读）当前读就手动加锁如select * from t for update 加写锁或lock in share mode；加读锁，当前修改也是当前读如 insert inito 在哪个位置插入也会当前读，update table set 、delete from 也是当前读五种。select是快照读，

如何让快照读改成当前读，就是加上读锁lock in share mode；那么其他事务的插入的也会出现阻塞，等该读释放读锁后其他事务写锁才会执行否则阻塞。

死锁问题：并发产生死锁、并发读异常，两个事务以上争夺锁资源，造成互相等待。

加s锁 行锁如select * from t where id >=2 lock in share mode如果where 条件是主，键聚集索引B+树上面扫描，在叶子节点上给每一条记录加行锁，插入也会加锁，如果id是自增加ai锁即自增锁，不是自增加插入意向锁 写锁，其他事务的行锁会阻塞插入，直到行锁执行完成被事务提交，该插入才会执行，避免了幻读出现。

mysql死锁：1相反加锁顺序造成死锁。锁 b+树（聚集索引b+树 辅助索引b+树）2锁冲突造成

锁有表级别锁：意向锁避免全表扫面 行级别锁：S共享锁x排他锁写锁 间隙锁 插入意向锁 ai锁即自增锁。

解决：通过加锁的顺序解决mysql死锁问题。通常mysql死锁是rr级下的间隙锁和插入意向锁造成冲突发生死锁。

mysql集群方案；mysql分库分表 mycat中间层。NewSQL分布式关系型数据库，分布式系统解决横向扩展+加关系型sql+分布事务。

ld一张员工表有id，name，age,city等字段，我们可以根据group by查出城市的种数，每个城市是有同一城市的员工，单独select city from emp  group by city没有意义，返回分组的情况，所以group by需要结合聚合函数进行组内统计，比如查找每个城市的员工人数，

Select city,count(id) from emp group by city现实郑州 10 武汉 6

优化group by city可以给city建立索引，alter table temp add index idx_age(age)

查出每个城市分组中年纪大于30的员工所在组和组内员工数量

Select city,count (*) num from emp where age＞30 group by city只有满足组内有数据的那组城市才会显示。

使用explain执行，key使用了idx_age索引，Extra中using index condition索引下推using tempoary使用了临时表， using filesort文件排序。type表示用到了索引并且range使用了范围查询

需求2查出每个城市中员工数量大于1的分组，注意这是对分组的筛选，需要用到having

Select city,count(* num) from temp group by city having num＞2注意having子句执行时机，是在分组后在统计的条件，所以前面的变量num可以组内条件判断

Where和having区别

1 Having是分组后组内的筛选，where是对表的筛

2Having配合group by 和聚合函数使用的，用于区域统计

3where子句后面不能跟聚合函数

Group by原理如上explain select city count (I'd) num from temp group by city中使用临时表和文件排序

会创建一个临时表 city num字段，执行计划中type为all全表扫描

 Grouo by执行原理如图，他会默认进行排序影响性能，在合适的场景下可以取消排序order by null

如果不想用到临时表和额外的文件排序就可以给city建立索引那么Exera就为null，这就是建立索引的好处，有序，索引树的形式提高性能

 而且type非all全表扫描而是走索引。除了索引优化外还可以通过不走索引优化临时表所在内存还是磁盘sql_big_result就可以取消内存临时表的使用，额外有文件排序，解决大型数据内存不够用问题，可能会性能慢。

 尽量使用内存临时表不要使用磁盘临时表

设置内存临时表大小set global tem_table_size=

 只有大量数据时，所设置的内存临时表不够用，又没有索引，就可以使用sql_big_result不不使用内存临时表，只有了文件排序的消耗，但是会慢