MYSQL高级

一.索引，事务，锁

1.索引

  • 优化查询操作.
  • 索引也叫键，是存储引擎用于快速找到记录的一种数据结构.
  • 数据量越大对于索引的性能越重要.
  • 一般来说，索引表占用的空间是数据表的 1.5 倍.
1.1 创建索引
  • 如果是 CHAR，VARCHAR 类型，length 可以小于字段实际长度
  • 如果是 BLOB 和 TEXT 类型，必须 指定 length。
CREATE TABLE mytable(
    ID INT NOT NULL, 
    username VARCHAR(16) NOT NULL, 
    INDEX myindex (username(16)) 
    -- index 表示索引，myindex 表示定义的索引名称（索引字段）。
);
1.2建表后创建索引
CREATE INDEX myindex ON mytable(username(16)); 
或者
ALTER table mytable ADD INDEX myindex(username)
1.4查看索引
SHOW INDEX FROM mytable
1.5 删除索引
  • 删除索引可以使用 drop 或者 alter 命令来完成.
DROP INDEX myindex ON mytable; 
或者 -- myindex 是创建索引时定义的索引名称。
ALTER TABLE mytable DROP INDEX myindex;
1.6 explain分析SQL
  • 如果 key 值为索引名称，则该查询使用了索引，如果 key 值为 null，则表示没有使用索引
explain select * from mytable where username = 'jack'
1.7 索引分类

见教程0-6互联网数据库MySql高级.pdf—P4

2.事务

  • 事务（Transaction）：一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务.
  • 事务只和 DML 语句有关，或者说 DML 语句才有事务.
  • 保护数据的一致性与并发性
2.1 MySql事务使用
  • 开启事务
start transaction; 
或者
begin transaction;
  • 提交事务
commit;
  • 回滚事务
rollback;
2.2 事务的特征（ACID）
  • 原子性(A)：事务是最小单位，不可再分；
  • 一致性©：事务要求所有的 DML 语句操作的时候，必须保证同时成功或者同时失败；
  • 隔离性(I)：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰；
  • 持久性(D)：是事务的保证，事务终结的标志(内存的数据持久到硬盘文件中)；
2.3 事务隔离级别
  1. 读未提交（read uncommitted）：隔离级别最低
  2. 读已提交（read committed）：事务 A 和事务 B，事务 A 提交的数据，事务 B 才能读取到.
  3. 可重复读（repeatable read）：可重复读是 MySQL 默认隔离级别.
  4. 串行化（serializable）：事务 A 和事务 B，事务 A 在操作数据库时，事务 B 只能排队等待.

mysql 区分度小 使用什么索引 mysql 索引高度_mysql 区分度小 使用什么索引

  • 脏读：事务 A 和事务 B，事务 A 未提交的数据，事务 B 可以读取到.
  • 不可重复读：A多次读取，B在过程中进行修改数据，导致A结果不一.
  • 幻读：A,B同时修改，查询发现一条数据没有修改成该有数据

3. 锁

  • 锁是计算机协调多个进程或线程并发访问某一资源的机制。
  • 因为悲观锁是由数据库实现的，一般不需要开发人员操作，我们只需要了解锁的作用与分类以及锁和事务的关系就可以了。
  1. 乐观锁
  • 乐观锁不是数据库自带的
  • 认为操作不会发生冲突，操作数据时不进行特殊处理，更新后才判断是否冲突
  1. 悲观锁
  • 悲观锁是由系统自己实现，直接使用数据库相关语句
  • 认为操作会发生冲突，每次操作都要获取锁才能进行相同操作
  • 耗时长
3.2.1 共享锁与排它锁
  1. 共享锁（S锁）：及读锁，允许事务读取一行数据。事务A对数据1上S锁，可以读取不可修改，其他事务只能加S锁不可加X锁，一直到A释放1上的S锁
  2. 排它锁（X锁）：及写锁，允许删除更新。事务A对数据1上X锁，可以更新查询，其他事务不可再加任何锁，到释放。
3.2.2 锁的粒度
  • 锁的粒度就是你要锁定的范围是多大。使用锁时，锁定的东西越少，对其他竞争者影响越小，这个东西的大小就是锁粒度
  1. 表级锁：开销小，加锁快，不会死锁。粒度最大，适合查询为主，只有少量按索引条件更新数据的操作。缺点是资源争用概率高
  2. 行级锁：开销大，加锁慢，会出现死锁。粒度最小，适合大量按索引条件并发更新少量不同数据，同时又有并发查询的操作。使用行级锁定的主要是 InnoDB 存储引擎
  3. 页面锁：开销介于表锁与行锁之间，会出现死锁。