java-mysql专题

1.sql优化

1.1select字句中不要使用*    理由：把*替换成表中的字段名，需要解析一次数据字典，有性能损耗

1.2from字句后的主表最好是记录数最少的表(oracle rbo模式，从from最右边的表开始扫描)

1.3可以过滤出最少数据的条件放在where 最前面，where字句的解析顺序是从左到右(早些时候听说过oracle和这个是相反的)

1.4子查询in迭代的是子表，子表小用in，exists迭代的是外表，外表小用exists

1.5无论何时not exists由于not in

1.6mysql中 join 子查询 性能高于 where 子查询

1.7存在null值的列随数据分布情况，可能不走索引，count字句不计算含有null值的记录

1.8like字句左%不会使用索引

1.9order by后尽量使用索引列排序，多个排序字段含有不同的索引时也会导致索引失效

1.10索引列不能是表达式的一部分

不使用索引：select uid from user where uid + 1 = 2;

使用索引：select uid from user where uid = 2 - 1;

1.11范围查询时尽可能缩小范围

1.12子查询不易嵌套过多层

2.mysql事务隔离级别

[RU]读未提交：脏读、不可重复读、幻读
[RC]读已提交：会出现不可重复读、幻读，避免脏读
[RR]可重复读：会出现幻读，避免不可重复读，脏读（默认）
[Serializable]串行化：脏读，不可重复读，幻读都避免，性能差

脏读：事务A读到了事务B还没有提交的数据
不可重复读：事务A中多次读取到的数据不一致
幻读：两个并发的事务中其中一个发生了插入、删除操作，被另一个事务提交前读取到

spring事务传播行为

如果有父事务
1.REQUIRED 则加入，没有则新建(默认) 受父事务影响，影响父事务
2.REQUIRED_NEW 则挂起，新建事务，没有则新建，不受父事务影响，不影响父事务
3.MANDATORY 则加入，没有就抛出异常 
4.SUPPORTS 则加入，没有则以没有事务方式执行

5.NEVER 则抛异常

3.mysql锁机制

3.1mysql存储引擎

	支持事务	锁粒度	存储	外键	全文索引	树索引
MyISAM	N	表锁	256TB	N	Y	Y
MEMORY	N	表锁	RAM	N	N	Y
InnoDB(默认)	Y	行锁、表锁	64TB	Y	N	Y
Archive	N	页锁、表锁	NONE	N	N	N

Archive使用场景为只有大量的select或insert语句

3.2表锁、页锁、行锁（根据锁粒度区分）

行锁：通过给索引上的索引项加锁来实现行锁，意味着如果sql语句没有使用到索引，是不会加行锁的，只会加表锁，有死锁产生的可能

页锁：页锁是位于行锁和表锁之间的，性能也在两者之间

表锁：顾名思义，给全表加锁，所以不会产生死锁

死锁：并发的两个事物中，例如同时update操作 A事物锁定11行等待12行的锁，B事务锁定12行等待11行的锁，此时则引发死锁

一次简单的死锁演示：

首先代开menu_item表

以第一行数据为例，分别开启两个update的事务不要提交：

可见第二个窗口已经产生了死锁。

执行 SELECT a.trx_mysql_thread_id, a.* FROM INFORMATION_SCHEMA.INNODB_TRX a; 语句查询死锁线程

最后kill掉产生的两个死锁线程即可

  

3.3共享锁、排它锁（根据锁的功能区分）

共享锁：其实就是读锁，语法select ... lock in share mode。其他事务可以读取，可以再加共享锁，不可以加排他锁。

排它锁：排它锁就是写锁，语法select ... for update。其他事务不可读，不可加任何锁，只能阻塞等锁释放，mysql innodb中默认会为update、delete、insert操作加排他锁。

如何尽可能避免死锁：

1、以固定的顺序访问表和行。比如两个更新数据的事务，事务A 更新数据的顺序 为1，2；事务B更新数据的顺序为2，1。这样便可能会造成死锁。

2、大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。

3、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。

4、降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。

5、使用mybaitis之类的orm框架时，尽量不要在不同的mapper中对同一张表执行update操作，jpa可以很好的避免死锁的产生。

4.mysql索引详解

不走索引即全表扫描

普通索引、普通联合索引

唯一索引、唯一联合索引

添加索引：ALTER table table_name ADD INDEX index_name (column_list)
删除：DROP INDEX [indexName] ON tableName;

ALTER TABLE table_name ADD UNIQUE index_name (column_list)

显示索引信息：SHOW INDEX FROM table_name;

mysql 全文索引、hash索引和b+tree索引

聚集索引和非聚集索引

5.sql执行计划

explan sql语句
type：all全表扫描、index、range、const 性能依次升高
possible_keys: 可能使用的索引
key：实际使用到的索引
rows：sql语句扫描了多少行