5.sql优化 及 解决锁表的问题

 

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

特点：

1.  完全支持事务得ACID特性
2.  Redo Log 和 Undo Log
3. Innodb支持行级锁（并发程度更高）

Ferderated：

        主要觉得牛逼的地方就是，数据全部放到远程服务器上，而且是多台不同的mysql

1. 提供了访问远程MySQL服务器上表的方法
2. 本地不存储数据，数据全部放到远程服务器上
3. 本地需要保存表结构和远程服务器的连接信息

使用场景

        偶尔的统计分析及手工查询（某些游戏行业）

        默认禁止，启用需要再启动时增加federated参数

MySQL中的锁

1. 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
2. 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
3. 页面锁(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 ,数据库就解锁了