06Mysql数据库

Mysql数据库

一、基础

1.1、事务的隔离级别

• 读未提交：如果一个事务已经开始写数据，则另外一个事务不允许同时进行写操作，但允许其他事务读此行数据。解决了丢失修改
• 读已提交：如果一个读事务允许其他事务读写，如果是写事务则禁止其他事务访问数据。解决了丢失修改和脏读
• 可重复读：在一个事务内，多次读同一数据，在这个事务还没结束时，其他事务不能访问该数据（包括读写）。避免了丢失修改，不可重复读和脏读
• 可串行化：要求事务一个接一个执行，最高级别。

1.2、并发事务的问题

• 丢失修改：两个事务更新同一数据，最后一个事务的更新会覆盖掉前面事务的更新，导致更新数据的丢失
• 脏读：一个事务读取了另一个事务没有提交的数据
• 不可重复读：在同一事务中，读取同一数据得到不同的内容
• 幻读：一个事务执行中读取到另一事务已提交的插入数据或删除数据消失。

1.3、查询连接（join）

• INNER JOIN：如果查询的表中至少一个匹配，则返回行，返回几个表的交集

  select <> from Tab A INNER JOIN Tab B ON A.Key=B.Key
  

• LEFT JOIN：关键字会左表 那里返回所有的行，即使在右表中没有匹配的行。

  select <> from Tab A LEFT JOIN Tab B ON A.Key=B.Key
  

  • 注意还有一种情况是不要交集：

    select <> from Tab A LEFT JOIN Tab B ON A.Key=B.Key where B.Key=null

• RIGHT JOIN：关键字会右表那里返回所有的行，即使在左表 中没有匹配的行。

  select <> from Tab A RIGHT JOIN Tab B ON A.Key=B.Key
  

  • 注意还有一种情况是不要交集：

    select <> from Tab A RIGHT JOIN Tab B ON A.Key=B.Key where B.Key=null

• FULL JOIN：只要查询的某个表存在匹配，FULL JOIN 关键字就会返回行。

  select <> from Tab A FULL JOIN Tab B ON A.Key=B.Key
  

• 注意：Mysql中没有FULL JOIN，使用UNION，UNION定义：UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

1.4、两种存储引擎对比

• MyIASM不支持事务，而InnoDB支持事务
• MyIASM锁的粒度是表级的，而InnoDB支持行级锁
• MyIASM支持全文类型索引，而InnoDB不支持全文索引
• MyIASM相对简单，效率上要优于InnoDB，小型应用可以考虑使用MyIASM

二、索引

2.1索引

• 索引是帮助MySQL高效获取数据的数据结构，排好序的快速查找的数据结构，目的是提高查找效率
• 索引会影响where后边的查找和order By后边的排序

2.2索引分类

• 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引
• 唯一索引：索引列的值必须唯一，但允许有空值
• 复合索引：即一个索引包含多个列

2.3建立索引情况（BTree索引）

• 主键自动建立唯一索引
• 频繁作为查询条件的字段应该创建索引
• 查询中与其他表关联的字段，外键关系建立索引
• 频繁更新的字段不适合创建索引
• where条件用不到的字段不创建索引

2.4索引失效

• 最好全值匹配；
• 最左前缀法则：如果索引了多列，查询从索引的最左前列开始，且不能跳过索引中的列；
• 不在索引列上做任何操作（计算，函数，类型转换），会导致索引时校而转向全表扫描；
• 存储引擎不能使用索引中范围条件右边的列，即范围之后全失效；
• 尽量使用覆盖索引，只访问索引的查询（索引列和查询列一致），减少selec *；（覆盖索引：建索引的列和要查询的列相同，例如索引列是c1，c2，select 查询的也是c1，c2；）
• MySQL在使用不等于的时候无法使用索引会导致全表扫描；
• is null，is not null 也无法使用索引；
• like 以通配符开头（’%aa‘）索引会失效，变成全表扫描；
• 字符串不加单引号，索引失效；
• 少用 or，用它来连接时候会索引失效

三、锁

3.1分类

• 对数据操作类型：读锁（共享锁）和写锁（排它锁）
  • 读锁：针对同一份数据，多个读操作可以同时进行
  • 写锁：当前写操作没有完成前，它会阻断其他写锁和读锁。
• 对数据操作 的粒度：表锁和行锁

3.2锁粒度

• 表锁

  • 特点：偏向MyISM存储引擎，开销小，加锁块；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

• 行锁

  • 特点：偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率最低，并发度最高。