SQL

SQL语句分类

  • 1.DDL(Data Definition Language):数据定义语言，用来定义数据库对象：库、表、列等。功能：创建、删除、修改库和表结构。
  • 2.DML(Data Manipulation Language):数据操作语言，用来定义数据库记录：增、删、改表记录。
  • 3.DCL(Data Control Language)：数据控制语言，用来定义访问权限和安全级别。
  • 4.DQL(Data Query Language)：数据查询语言，用来查询记录。也是本章学习的重点。

    DCL(数据控制语言)语法（不重要）

    作用：该语言用来定义访问权限 
    1. ● 创建用户：create user zn@'%' =>identified by 'zn123'
    2. ● 给用户授权：grant all on test.* to zn
    3. ● 撤销授权：revoke all on test.* from zn
    4. ● 查看权限：show grants for zn
    5. ● 删除用户：drop user 用户名@ip地址

    DDL(数据定义语言)语法

    作用：该语言用来对数据库和表结构进行操作。
    对数据库的操作（增删查改）
    1. CREATE DATABASE test DEFAULT CHARACTER SET utf8

    Mysql锁

    1090617-20211123091527322-1401355583.jpg
    锁是计算机协调多个进程或线程并发访问某一资源的机制。 用来管理对共享资源的并发操作，从而保证数据的正确性。
    MySQL InnoDB中锁的分类
    锁锁的是索引
    Mysql索引补充点：每张表在创建后都会有一个索引即使你不指定索引，如果创建表的时候你指定了primary key则直接使用，如果没有指定primary key则InnoDB会使用unique key not null(不为空的唯一索引)，如果连唯一索引都没有则存储引擎使用默认的RowID (隐藏索引) 作为聚集索引。
    阻塞：Commit或者Rollback，同时一个事物等待其他事物释放锁在InooDB中默认是50s，超过50s后就不再获取。
    死锁： 死锁发生的条件主要有三种：1)互斥 2)不可剥夺 3)形成互相等待环路 。

一、锁粒度
1、表级锁
表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。
当然，锁定颗粒度大，所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并发度大打折扣。使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎。
2、行级锁
行级锁定最大的特点是锁定对象的颗粒度小，由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给与应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。
虽然能够在处理能力上面有较大优势，但是行级锁定也因此带来了不少弊端。
由于锁定资源的颗粒度很小，所以每次获取锁和释放锁的时候需要做的事情更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。
3、页级锁
页级锁定是Mysql中比较独特的一种锁定级别。页级锁定的特点是锁定粒度介于行级锁和表级锁中间，所以获取锁定需要的资源开销，以及锁定的并发处理能力同样是介于上面二者之间。
使用页级锁定的主要是BerkeleyDB存储引擎。
二、加锁模式
1、表锁 VS 行锁
表锁和行锁的概念很容易理解一个是锁定整张表一个是锁定一行记录，那么两者有什么区别呢。
锁的粒度：表锁 > 行锁 — 这是因为表锁会锁定更多的记录以及资源因此粒度比较大
加锁效率：表锁 > 行锁 — 这是因为表锁直接锁定了整个表资源而不需要向行锁一样一行行锁
冲突概论：表锁 > 行锁 — 锁整张表数据所有写的操作都需要阻塞因此冲突更多
并发性能：表锁 < 行锁 — 行锁的冲突概率小自然并发高
2、共享锁（ “S”锁 ）
当多个事物操作同一数据时可以共享一把锁，可以同时访问数据但是不可修改数据
3、排他锁（ “X”锁 ）
如果一个事物获取了一行数据的排他锁，其他事物不可以读或者写。 排他锁加锁的方式分为手动加锁和自动加锁，手动加锁的方式是在查询语句后加for update；自动加锁表示delete/update/insert语句会自动加X锁。 

  1. -- 加共享锁（S）
  2. select * from table_name where ... lock in share mode
  4. -- 加排它锁（X)
  5. select * from table_name where ... for update

4、意向共享锁(IS) / 意向排他锁(IX)
意向锁都是InnoDB存储引擎自己维护的用户是无法操作意向锁的。
5、意向共享锁IS：
意向2个字可以看出是在事物准备给给数据加共享锁之前先要获取这个意向，就像是你要去别人家做客需要告诉人家你有这个意向好让人家准备，只有人家有这个意向让你去做客你才能去，而对于意向锁来说在加S锁的时候需要看下表上是否有意向锁，有的话就表示已经被锁定而不需要扫描表上每行数据看是否有S锁。
6、意向排他锁IX：
同IS加意向锁的理由，在事物准备给数据加X锁的时候必须先获取该表的IX锁。下图为S锁、X锁、IS锁、IX锁锁定关系。
9a7f81a133b1b841f4d3d2ff5b5d6bc4.png
7、记录锁（Record Lock）：使用条件为如果是根据唯一索引或者主键索引等值查询就会使用记录锁算法，如select * from m2 where id=1。记录锁也是排它(X)锁,所以会阻塞其他事务对其插入、更新、删除。 

  1. SELECT * FROM `test` WHERE `id`=1 FOR UPDATE;

8、间隙锁（Gap Lock）：使用条件为记录不存在的空间如select from m2 where id=6这个时候会在4-7之间加一个Gap锁(是左开右开区间哦)，Gap锁的主要目的是阻塞在4-7之间插入数据。要是对对一条不存在的数据id=6加一个X锁，如果在4-7间隙中插入数据。 在4-7的间隙中是无法插入数据的，那么修改id=10的数据则可以修改成功。
9、临界锁（Next-key Lock）：
临界锁使用条件为范围查询，包含记录和区间，这是InnoDB中默认使用的行锁算法，想下为啥呢？请看图1如果默认使用记录锁那么当进行范围查询时则无法加锁，如果使用Gap锁会造成只要不在范围内的都加Gap锁无法适用于>=这种场景，而临界锁为左开右闭的形式，当查询id>5 and id<9的时候会发现7有记录，其他数据都在范围内这个时候就使用临界锁，当查询id>10的时候退化为Gap锁，当查询id=1的时候转化为记录锁。来看个试验。首先开启一个事物查询5*MyISAM、InnoDB支持的锁
上面已经介绍到MySQL既支持表锁也支持行锁，而MyISAM只支持表锁，通过lock tables ‘表名’ read(或write)来加表锁。