sql解析鱼骨图
执行顺序:from > on > join >where > group by > avg,sum.... > having > select > distinct > order by > limit
group by--执行顺序从左往右分组
提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉。即在GROUP BY前使用WHERE来过虑,而尽量避免GROUP BY后再HAVING过滤。
SQL优化
通过 执行计划 explain,然后show warnings;可以看到这条sql在数据库中最终优化后的sql
explain select * from account t where t.id in (select t2.id from account t2) show warnings;
不难看出,用in的经过mysql优化后,变为了 join
常用的数据库存储引擎
mysql默认的是: Innodb
Innodb是一种事务性存储引擎:应用场景,绝大多数的场景都是用的Innodb
特点:
- 完全支持事务得ACID特性
- Redo Log 和 Undo Log
- Innodb支持行级锁(并发程度更高)
Ferderated:
主要觉得牛逼的地方就是,数据全部放到远程服务器上,而且是多台不同的mysql
- 提供了访问远程MySQL服务器上表的方法
- 本地不存储数据,数据全部放到远程服务器上
- 本地需要保存表结构和远程服务器的连接信息
使用场景
偶尔的统计分析及手工查询(某些游戏行业)
默认禁止,启用需要再启动时增加federated参数
MySQL中的锁
- 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
- 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
- 页面锁(gap锁,间隙锁):开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。
InnoDB锁
在mysql 的 InnoDB引擎支持行锁
共享锁又称:读锁。当一个事务对某几行上读锁时,允许其他事务对这几行进行读操作,但不允许其进行写操作,也不允许其他事务给这几行上排它锁,但允许上读锁。
排它锁又称:写锁。当一个事务对某几个上写锁时,不允许其他事务写,但允许读。更不允许其他事务给这几行上任何锁。包括写锁。
上共享锁的写法:lock in share mode 例如: select * from 表 where 条件 lock in share mode; 上排它锁的写法:for update 例如:select * from 表 where 条件 for update;
1.两个事务不能锁同一个索引。
2.insert ,delete , update在事务中都会自动默认加上排它锁。
3.行锁必须有索引才能实现,否则会自动锁全表,那么就不是行锁了。
锁的等待问题
#查询正则等待的锁
#仅5.7适用 select * from sys.innodb_lock_waits # 5.6 或 5.7适用 SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;
然后不知道哪个家伙,一直没有释放锁,直接大招 kill 29 ,数据库就解锁了