MySql-引擎-索引-事务-锁

• MySql存储引擎
  1、 myisam
  不支持事务，不支持外键约束，索引文件和数据文件分开，这样在内存里可以缓存更多的索引，对查询的性能会更好，适用于那种少量的插入，大量查询的场景。之前报表系统用，所以当时用myisam比较多，但是后来人家几乎都不用了。
  2、innodb
  现在一般用mysql都是innodb，很少用其他的存储引擎，而且国内用其他存储引擎的场景和公司也不多，所以用innodb就可以了，而且这个也是mysql 5.5之后的默认存储引擎。
  主要特点就是支持事务，走聚簇索引，强制要求有主键，支持外键约束，高并发、大数据量、高可用等相关成熟的数据库架构，分库分表、读写分离、主备切换，全部都可以基于innodb存储引擎来玩。

• 索引
  1、mysql的索引是怎么实现的（b+树）

   b+树：查找的时候，就是从根节点开始二分查找 找到一个区间，然后区间会有一个指针指向叶子节
   	点。 只有叶子节点存储data，叶子节点包含了这棵树的所有键值，叶子节点不存储指针
  

	b树：每个节点都存储key和data，所有节点组成这棵树，并且叶子节点指针为null。

	为啥是b+树：
	那为什么是B+树而不是B树呢，因为它内节点不存储data，这样一个节点就可以存储更多的key。

2、myisam和innodb索引区别

1）myisam：data存的是数据行的物理地址。索引是索引，数据是数据。索引放在XX.MYI文件中，
	数据放在XX.MYD文件中，所以也叫非聚集索引

2）innodb:data存的是数据本身。索引也是数据。数据和索引存在一个XX.IDB文件中，所以也叫聚集索引。
	为啥innodb下不要用UUID生成的超长字符串作为主键？
		1、因为这么玩儿会导致所有的索引的data都是那个主键值，最终导致索引会变得过大，浪费很多磁盘空间。
		2、一般innodb表里，建议统一用auto_increment自增值作为主键值，因为这样可以保持聚簇索引直接加记录就可以，
			如果用那种不是单调递增的主键值，可能会导致b+树分裂后重新组织，会浪费时间

3）索引的使用规则
	举个例子，你如果要对一个商品表按照店铺、商品、创建时间三个维度来查询，那么就可以创建一个联合索引：shop_id、product_id、gmt_create
	在java系统里写的SQL，都必须符合最左前缀匹配原则，确保你所有的sql都可以使用上这个联合索引，通过索引来查询 create index (shop_id,product_id,gmt_create)
	
	（1）全列匹配
	这个就是说，你的一个sql里，正好where条件里就用了这3个字段，那么就一定可以用到这个联合索引的：
	select * from product where shop_id=1 and product_id=1 and gmt_create=’2018-01-01 10:00:00’
	（2）最左前缀匹配
	这个就是说，如果你的sql里，正好就用到了联合索引最左边的一个或者几个列表，那么也可以用上这个索引，在索引里查找的时候就用最左边的几个列就行了：
	select * from product where shop_id=1 and product_id=1，这个是没问题的，可以用上这个索引的
	（3）最左前缀匹配了，但是中间某个值没匹配
	这个是说，如果你的sql里，就用了联合索引的第一个列和第三个列，那么会按照第一个列值在索引里找，找完以后对结果集扫描一遍根据第三个列来过滤，第三个列是不走索引去搜索的，就是有一个额外的过滤的工作，但是还能用到索引，所以也还好，例如：
	select * from product where shop_id=1 and gmt_create=’2018-01-01 10:00:00’
	就是先根据shop_id=1在索引里找，找到比如100行记录，然后对这100行记录再次扫描一遍，过滤出来gmt_create=’2018-01-01 10:00:00’的行
	这个我们在线上系统经常遇到这种情况，就是根据联合索引的前一两个列按索引查，然后后面跟一堆复杂的条件，还有函数啥的，但是只要对索引查找结果过滤就好了，根据线上实践，单表几百万数据量的时候，性能也还不错的，简单SQL也就几ms，复杂SQL也就几百ms。可以接受的。
	（4）没有最左前缀匹配
	那就不行了，那就在搞笑了，一定不会用索引，所以这个错误千万别犯
	select * from product where product_id=1，这个肯定不行
	（5）前缀匹配
	这个就是说，如果你不是等值的，比如=，>=，<=的操作，而是like操作，那么必须要是like ‘XX%’这种才可以用上索引，比如说
	select * from product where shop_id=1 and product_id=1 and gmt_create like ‘2018%’
	（6）范围列匹配
	如果你是范围查询，比如>=，<=，between操作，你只能是符合最左前缀的规则才可以范围，范围之后的列就不用索引了
	select * from product where shop_id>=1 and product_id=1
	这里就在联合索引中根据shop_id来查询了
	（7）包含函数
	如果你对某个列用了函数，比如substring之类的东西，那么那一列不用索引
	select * from product where shop_id=1 and 函数(product_id) = 2
	上面就根据shop_id在联合索引中查询
	
4）索引的缺点以及使用注意
	索引是有缺点的，比如常见的就是会增加磁盘消耗，因为要占用磁盘文件，同时高并发的时候频繁插入和修改索引，会导致性能损耗的。

	建议，尽量创建少的索引，比如说一个表一两个索引，两三个索引，十来个，20个索引，高并发场景下还可以。
	
	你觉得你建立索引还有意义吗？几乎跟全表扫描都差不多了
	select * from table where status=1，相当于是把100行里的50行都扫一遍
	你有个id字段，每个id都不太一样，建立个索引，这个时候其实用索引效果就很好，你比如为了定位到某个id的行，其实通过索引二分查找，可以大大减少要扫描的数据量，性能是非常好的
	在创建索引的时候，要注意一个选择性的问题，select count(discount(col)) / count(*)，就可以看看选择性，就是这个列的唯一值在总行数的占比，如果过低，就代表这个字段的值其实都差不多，或者很多行的这个值都类似的，那创建索引几乎没什么意义，你搜一个值定位到一大坨行，还得重新扫描。
	就是要一个字段的值几乎都不太一样，此时用索引的效果才是最好的
	还有一种特殊的索引叫做前缀索引，就是说，某个字段是字符串，很长，如果你要建立索引，最好就对这个字符串的前缀来创建，比如前10个字符这样子，要用前多少位的字符串创建前缀索引，就对不同长度的前缀看看选择性就好了，一般前缀长度越长选择性的值越高。

• 事务 [公众号]

• 锁

mysql的锁类型，一般其实就是表锁、行锁和页锁

innodb的行锁有共享锁（S）和排他锁（X），两种，其实说白了呢，共享锁就是，多个事务都可以加共享锁读同一行数据，但是别的事务不能写这行数据；排他锁，就是就一个事务可以写这行数据，别的事务只能读，不能写。

innodb的表锁，分成意向共享锁，就是说加共享行锁的时候，必须先加这个共享表锁；还有一个意向排他锁，就是说，给某行加排他锁的时候，必须先给表加排他锁。这个表锁，是innodb引擎自动加的，不用你自己去加。

insert、update、delete，innodb会自动给那一行加行级排他锁

select，innodb啥锁都不加，因为innodb大家记得么，默认实现了可重复读，也就是mvcc机制，所以多个事务随便读一个数据，一般不会有冲突，大家就读自己那个快照就可以了，不涉及到什么锁的问题

但是innodb从来不会自己主动加个共享锁的，除非你用下面的语句自己手动加个锁：

手动加共享锁：select * from table where id=1 lock in share mode，那你就给那一行加了个共享锁，其他事务就不能来修改这行数据了

手动加排他锁：select * from table where id=1 for update，那你就给那一行加了个排他锁，意思就是你准备修改，别的事务就别修改了，别的事务的修改会hang住。这个要慎用，一般我们线上系统不用这个，容易搞出问题来。

琢磨琢磨默认的数据库锁机制
对一行数据，如果有人在修改，会加个排他锁，然后你不能修改，你只能等着获取这把锁，但是这个时候你可以随便select，你就是查询你的事务开始之前那行数据的某个版本而已。然后如果你修改某行数据，会同时拿这个表的排他锁，但是呢，如果不同的事务修改不同的行，会拿不同行的行级排他锁，但是大家都会拿一个表的排他锁，ok，实际上innodb的表级排他锁可以随便拿，这个是没冲突的。

所以这个就是mysql innodb存储引擎的默认锁模式，其实还挺不错的。相当于就是一行数据，同一个时刻只能一个人在修改，但是别人修改，你可以随便读，读是读某个版本的，走mvcc机制。大家理解这个就好。

悲观锁和乐观锁是啥

mysql里的悲观锁是走select * from table where id=1 for update，就这个，意思是我很悲观，我担心自己拿不到这把锁，我必须先锁死，然后就我一个人可以干这事儿，别人都干不了了，不能加共享锁，也不能加排他锁。

乐观锁，就是说我觉得应该没啥问题，我修改的时候感觉差不多可以获取到锁，不需要提前搞一把锁，我就先查出来某个数据，select id,name,version from table where id=1，接着再执行各种业务逻辑之后再修改，update table set name=’新值’,version=version+1 where id=1 and version=1，就是说每次修改，比较一下这条数据的当前版本号跟我之前查出来的版本号是不是一样的，如果是一样的就修改然后把版本号加1，否则就不会更新任何一行数据，此时就重新查询后再次更新。

一般悲观锁什么时候用呢？比如你查出来了一条数据，要在内存中修改后再更新到数据库中去，但是如果这个过程中数据被别人更新了，你是不能直接干这个操作的，这个时候，你就得走上面那个操作，查询之后就不让别人更新了，你搞完了再说。

但是真有这种场景，推荐你还是用乐观锁把，悲观锁实现简单一点，但是太有风险了，很容易很容易死锁，比如事务A拿了数据1的锁，事务B拿了数据2的锁，然后事务A又要获取数据2的锁就会等待，事务B又要获取数据1的锁，也会等待，此时尴尬了，死锁，卡死，互相等待，永不释放。

所以select … for update这个语法，轻易不要用，我们几乎线上很少用。

死锁
事务A
select * from table where id=1 for update
事务B
select * from table where id=2 for update
事务A
select * from table where id=2 for update
事务B
select * from table where id=1 for update
常见的死锁就是类似上面那种，给大家说过了，分别都持有一个锁，结果还去请求别人持有的那把锁，结果就是谁也出不来，死锁了
情况太多，不一一列举了，其实就给大家说下发现死锁的时候怎么排查吧
其实很简单，就是找dba看一下死锁日志，就ok了，然后根据对应的sql，找下对应的代码，具体判断一下为啥死锁了