7.MySQL.md

1.MYSQL

1.1 MySQL 有那系诶存储引擎(myisam和innodb)，都有哪些区别？

1.1.1 myisam

myisam不支持事物，不支持外键约束，索引文件和数据文件分开，查询性能更好，适用于少量插入，大量查询。

不需要事物，一次性批量导入，接下来一天纯查询。

1.1.2 innodb

主要特点：支持事物，走聚簇索引，强制要求有主键，支持外键约束，高并发，大数据量，高可用等相关成熟的数据库架构，分库分表、读写分离、主备切换、全部都可以基于innodb实现。

1.2 MySQL索引原理

B-树：

现在对id建个索引：15/56/77/20/49

B+树：

B+树内节点不存储data，值存储key，叶子节点不存储指针。

1.2.1 myisam的存储索引

myisam最大的特点就是数据文件和索引文件是分开，上面的索引文件里搜索，然后导数据文件定位一行。

1.2.2 innodb的存储索引

innodb的存储索引，要求必须有主键，可以默认内置的就会根据主键建立一个索引，叫做聚簇索引，innodb的数据文件本身同时也是个索引文件，这个索引就是默认根据主键建立的，叫做聚簇索引。

15这个节点对应的就是一行完整的数据。

innodb这种原生的数据文件就是索引文件的组织结构，就叫默认的主键索引为聚簇索引，就是因为这个原因，所以innodb必须要有主键，但myisam不要求必须有主键。

innodb存储引擎下，如果对某个非主键的额字段创建个索引，那么最后那个叶子节点的值就是主键的值，可以用主键的值到聚簇索引里根据主键值再次查找到数据。

1.2.3 使用索引的规则

create index(shop_id, product_id, gmt_create)

• 1.全列匹配

  where条件的就用这三个字段，那么就可以做到联合索引。

  select * from product where shop_id = 1 and product_id = 1 and gmt_create = '2020-10-10 10:00:00'

• 2.最左前缀匹配

  sql中，正好用到了联合索引中最左边的一个或者几个列表，那么也可以用上面那个索引。

  select * from pruduct where shop_id = 1 and product_id = 1

• 3.最左前缀匹配了，但是中间某个值没有匹配。

  sql里就用了联合索引的第一列和第三个列，那么会按照第一列去索引，找完以后在根据第三列来过滤，第三列是不走索引的。

  select * from product where shop_id = 1 and gmt_create = '2020-10-10 10:00:00'

• 没有最左前缀匹配原则

  这个一定没用索引。

• 前缀匹配

  like操作，like 'xx%',只有%放后面的查询才可以用

  例如：select * from product where shop_id = 1 and product_id = 1 and gmt_create like '2020%'

• 范围列匹配

  只能使符合最左前缀的规则才可以范围，范围之后的列就不用索引了。

  select * from product where shop_id >= 1 and product_id = 1

  只有shop_Id根据索引来查了。

• 包含函数

  如果你对某个列用了函数，那么那一列不用索引。

  select * from product where shop_id = 1 and 函数(product_id) = 2

  上面就是根据shop_id在联合索引中查询。

  1.2.4 索引的缺点和使用注意

• 1.会增加磁盘消耗。

1.3 事务特性，事物隔离级别

1.3.1 事务的ACID

• 1.原子性(Atomimc)
• 2.一致性(Consistency)
• 3.隔离性(Isolation):多个事物不能互相影响
• 4.持久性(Durablity):对数据的修改是永久有效的。

1.3.2 事务的隔离级别

• 1.读未提交

  

• 2.不可重复读

  

• 3.可重复读

• 4.幻读

  

• 5.串行化

MySQL默认的可重复读，MySQL是通过MVCC机制来实现的，就是多版本并发控制。

事物id，在mysql内部是全局唯一递增的。

1.4 MySQL锁实现原理

数据库锁有那些类型，锁是如何实现的，MySQL行级锁有哪两种，一定会锁定指定的行么？悲观锁和乐观锁是什么，使用场景是什么，mysql死锁原理以及如何定位和解决。

1.4.1 MySQL锁

mysql锁一般分为表锁，行锁，页锁。

一般myisam会加表锁，在myisam引擎下，执行的时候，会默认加个表共享锁，也就是表读锁，别人来查的时候，不能写数据。在myisam写的时候，也会加个表独占锁，别人不能写。

innodb的行锁有共享锁和排他锁。

• 共享锁。多个事务都可以加共享锁读同一行数据，但是别的事务不能写这行数据
• 排他锁，就是一个事务可以写这行数据，别的事务只能读不能写。

innodb会在insert、update、delete情况下自动给那一行加行级排他锁。select都不加锁。

innodb 的表锁分为：意向共享锁和意向排他锁

• 意向共享锁：就是加共享行锁的时候，必须先加个共享行锁
• 意向排他锁：给某行加排他锁的时候，必须先给表加排他锁。

手动共享锁：select * from table where id = 1 lock in share mode

手动排他锁：select * from table where id = 1 for update

1.4.2 悲观锁和乐观锁

• 悲观锁

  select * from table where id = 1 for update

• 乐观锁

1.5 MySQL调优