关于MySQL数据库的表锁和行锁

MySQL的InnoDB存储引擎默认是自动提交事务的。

1. 只有InnoDB支持行锁。
2. InnoDB通过给索引上索引项加锁来实现的；只有通过索条件检索数据，InnoDB才能使用行级锁，否则，InnoDB使用表锁。

注意：索引失效的情况，会进行全表操作，行锁会自动变表锁；
InnoDB默认的行锁可以使得操作不同行时不会产生影响，不会阻塞，解决了多事务和并发问题。前提是where条件中使用了索引；如果没有使用上索引，全表扫描，全部阻塞；

3. 特点：开销大，加锁慢；会出现死锁；锁定粒度小，锁冲突概率低，并发度高；
4. 使用场景：有大量按索引条件并发更新少量不同数据，同时又有并发查询；
5. 分析：show status like 'innodb_row_lock%'分析系统上行锁争夺情况，如果比较严重的锁争夺，InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高，通过InnoDB Monitors观察冲突的表和数据行，分析锁竞争原因。

// 关闭自动提交
set autocommit = 0;
// 手动提交
commit；
// 开启事务
begin;

针对悲观锁，行锁 和 表锁，SQL加上FOR UPDATEE即可。

select * from t_user u where u.id=1 for update 

当新建会话（开启了新事务，事务之间是隔离的），查询相同行的记录也加锁时候是无效的。

只要锁存在的时候（无论是一行还是整张表），我们对有所的地方只能查询；只有针对没有锁的行次可以进行写入操作。

set autocommit =1;

Oracle默认是需要手动提交事务的；

查询那张表被锁 然后解锁
alter system kill session ''23,1647;

表锁：

1. InnoDB,MYIsam
2. 场景：查询为主，少量按照索引更新的数据；
3. 特点：开销小，加锁快；不会出现死锁，锁定粒度大，冲突概率高，并发度最低；
4. 两种模式：表共享读锁，表独占写锁；
5. 对两张表加锁，解锁

Lock tables users read local, oders read local;
select sum(total) from orders;
select sum(subtotal) from users;
unlock tables;

6. MyISAM在查询前加读锁，写入前加写锁。

索引失效情况

1. 建立索引

create index u_id on user(id);

行锁是通过索引实现的，索引失效的时候，InnoDB的行锁会变表锁；

索引失效情况

1. 有or必全有索引
2. 复合索引没有用左列字段，单独引用复合索引非第一列的字段
3. like以%开头
4. 需要类型转换
5. where 中索引列有运算；
6. where 中索引使用了函数
7. 如果mysql默认全表扫描更快；
8. 字符串不加引号
   11,基于cost成本分析(oracle因为走全表成本会更小)：查询小表,或者返回值大概在10%以上
   12,有时都考虑到了 但就是不走索引,drop了从建试试在
   13,B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走
   联合索引的not NULL比较麻烦，实际需要测试

14,联合索引 is not null 只要在建立的索引列（不分先后）都会走,
in null时 必须要和建立索引第一列一起使用,当建立索引第一位置条件是is null 时,
其他建立索引的列可以是is null（但必须在所有列 都满足is null的时候）,
或者=一个值；当建立索引的第一位置是=一个值时,其他索引列可以是任何情况（包括is null =一个值）,
以上两种情况索引都会走。其他情况不会走。

// 查看索引是否失效
explain select * from order where id = 1 or username = 'zhangsan'; // 两个条件都需要索引
explain select * from order where user_name like 'w%'’； // %在前索引失效；
explain select * from oder where user_name = '123'; //没有引号的时候索引失效
explain select * form order where id = id+1; // 数学运算或者函数导致索引失效

- 唯一性差；
比如性别：只有两种数据，二叉树级别少，多是平级，无异于全表扫描；
- 频繁更新的字段（更新索引消耗）
列的数据变化导致索引也要变化。
- where 中不用的字段；
只有where 语句出现，mysql才会使用索引；
之后含is null/ is not null/ like '%%'不建议用索引；
索引使用<>时，效果一般；