# 存储引擎

常见的几种存储引擎:

  • MyISAM :一个传统引擎。不支持外键、事务、行级锁,只支持表级锁。
  • InnoDB :从 MySQL 5.5 开始成为默认引擎,支持外键、事务、行级锁。
    • 一般情况下使用 InnoDB 引擎更好。

# 外键

:让表 A 中的一个字段引用表 B 中的一个字段,从而让两张表的每行数据建立映射关系。

  • 建立外键的条件:
    • 表 A 的外键字段的值在表 B 中都存在。
    • 表 B 中被引用的字段是 unique 的。
  • 外键的几种映射关系:
    • 一对一:表 A 中的数据与表 B 中的数据一一对应。
    • 多对一:表 A 中的多条数据对应到表 B 中的一条数据。(此时,要在多的那方数据表中定义外键)
    • 多对多:表 A 中的多条数据对应到表 B 中的多条数据。(此时,可以在任意一方数据表中定义外键)
  • 定义外键的例子:
    create table tb1(
        id int primary key auto_increment,     -- 定义字段 id ,并声明为主键
        num int not null,                      -- 定义字段 num
        foreign key(num) references tb2(id)    -- 将字段 num 声明为外键,引用 tb2 表的 id 字段
    );
    
    alter table tb1 add constraint num foreign key(num) references tb2(id);  -- 将字段 num 声明为外键
    
    alter table tb1 drop foreign key num;      -- 取消外键
    
    • 在 tb1 中插入一行包含外键的数据时,该外键值必须在 tb2 中存在,否则会报错。
    • 删除 tb2 之前,必须先删除 tb1 中关联到它的所有行数据。

# 索引

:基于数据表中的某些列建立一个有序表,使得以后查询这些列时会更快,而不必遍历全表。

  • 如果在索引表中找到了匹配的数据,MySQL 就能根据这条索引所映射的物理地址,直接读取完整列的原数据。
    • 如果在索引表中没有找到匹配的数据,MySQL 依然会去遍历全表。
  • 索引可以使查询速度更快。但每次修改数据时都需要刷新索引表,导致 insert、update、delete 操作变慢。
  • 适合使用索引的情况:
    • 表的主键就是一个唯一索引。
    • 经常在 where 子句中出现的字段。
    • 需要排序的列。
  • 不适合使用索引的情况:
    • 数据量不是很多的表。(优化效果不明显)
    • 经常增删改的字段。
    • 取值容易重复的列。(此时 MySQL 会优先选择遍历全表)

MySQL 在查询时使用索引的条件:

  • 如果使用 where A = B 进行查询,当字符串 B 包含单列索引的那个字段、或组合索引的第一个字段时,才会使用相匹配的索引。
    • 另外,如果 B 没有加上双引号,可能会被 MySQL 当做其它数据类型处理,而不使用索引。比如 where name=123 就会被看作 int 类型。
  • 如果使用 where A or B 进行查询,
  • 如果使用 where A like B 进行查询,当字符串 B 是常量、且不以 % 开头时,才会使用相匹配的索引。
  • 如果 MySQL 判断使用索引的效率比全表扫描更低,就不会使用索引。

# 单列索引

:从单个列生成一个索引列。

  • 适合优化只考虑一个字段的查询语句,比如:where name='one'
  • 相关命令:
    create table tb1 (..., index index1(name)); -- 在创建表时,由列 name 生成索引,名为 index1
    create index index_text on tb1(text(100));  -- 给表添加索引
    alter  table tb1 add  index index1(name);   -- 给表添加索引
    alter  table tb1 drop index index1;         -- 删除表的索引
    
    show index from tb1;                        -- 显示表的索引
    
    • 如果由 text 类型的列生成索引,则要限制其长度。长度越短,越有利于索引的查询速度、减少存储空间。

# 组合索引

:由多个列组成一个索引列。

  • 定义的方法与单列索引类似:
    alter table tb1 add index id_name(id, name);
    
  • 组合索引适合优化同时考虑多个字段的查询语句,比如:where id>1 and name='one'
  • 一张表可以有任意个单列索引、组合索引。

# 全文索引

:用于处理大文本。

# explain

  • 可以在执行 SQL 命令时加上 explain 关键字作为前缀,分析执行命令时的查询过程。如下:

    mysql> explain select * from tb1 where id!=0;
    +----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
    | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra                    |
    +----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
    |  1 | SIMPLE      | tb1   | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 20032  |   100.00 | Using where; Using index |
    +----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
    1 row in set, 1 warning (0.00 sec)
    
    • type :查询类型。效率从高到低依次分为:
      const   # 查询某个唯一的索引键,找到了就立即返回。比如唯一索引
      eq_ref  # 每个索引键只对应一行数据。比如唯一索引
      ref     # 每个索引键可能对应多行数据
      range   # 只检索索引表的某个范围。比如 where...between、in、is null
      index   # 遍历了索引表
      all     # 遍历了全表,效率最低
      
    • possible_keys :该查询可能使用的索引。
    • key :实际使用的索引。
    • ref :使用了索引表的第几列。
    • rows :找到目标数据需要读取的行数。
  • describe 关键字等价于 explain ,缩写为 desc 。

  • 可以主动控制是否使用索引,便于测试索引的效果:

    explain select * from tb1 use    index(index1) where name='one';  -- 只使用某些索引
    explain select * from tb1 ignore index(index1) where name='one';  -- 忽略某些索引
    explain select * from tb1 force  index(index1) where name='one';  -- 强制使用某些索引
    

# 事务

  • InnoDB 引擎支持事务。
  • 可以将连续执行的多个 SQL 命令声明为一个事务:
    BEGIN;           -- 开始一个事务,也可写作 START TRANSACTION
    ...
    ROLLBACK;        -- 回滚到 BEGIN 时的状态
    ...
    SAVEPOINT p1;    -- 声明一个回滚点
    ...
    ROLLBACK TO p1;  -- 回滚到指定位置
    ...
    COMMIT;          -- 提交事务(在此之前不会自动提交)
    
  • 相关命令:
    SELECT * FROM information_schema.innodb_trx;   -- 查询当前的所有事务
    SET AUTOCOMMIT=0`       -- 取消自动提交事务。默认每执行一条 SQL 命令就会作为一个事务提交
    

# 数据库锁

  • 关于表级锁的命令:

    LOCK TABLES [<tb> <lock_type>],...    -- 给指定表加锁,使当前客户端会话获得表锁,例如 LOCK TABLES tb1 WRITE, tb2 READ;
    UNLOCK TABLES                         -- 释放当前会话的所有表锁
    
    FLUSH TABLES                          -- 强制关闭所有被打开的表,并将写缓存中的数据写入磁盘
                tb,...                    -- 只处理指定的表
                WITH READ LOCK            -- flush 之后加上全局只读锁。这是全局锁,因此不需要获得 lock tables 的表锁
    
    SHOW STATUS LIKE 'Table_locks%';      -- 统计获得表锁的耗时
    
    • 客户端执行 LOCK TABLESSTART TRANSACTION 时,都会隐式地释放已获得的表锁。
      • 客户端断开会话时,服务器会释放其获得的表锁。
  • 关于行级锁的命令:

    SELECT * FROM ... WHERE ... LOCK IN SHARE MODE      -- 给查询到的所有数据行加行级共享锁
    SELECT * FROM ... WHERE ... FOR UPDATE              -- 加行级排它锁
    
    SHOW STATUS LIKE 'InnoDB_row_lock%';                -- 统计获得行锁的耗时
    SELECT * FROM performance_schema.data_locks;        -- 显示所有获取行锁的请求,包括已经获得的、等待获得的
    SELECT * FROM performance_schema.data_lock_waits;   -- 显示 data_lock_waits 中的哪些请求在被哪些请求阻塞
    
    • InnoDB 的行锁是通过给索引中的索引键加锁来实现的。

      • 如果不使用索引进行查询,则行锁不起作用,只能使用表锁。
      • 如果针对不同的数据行加行锁,却使用相同的索引键,则也会发生锁冲突。
    • InnoDB 在申请行锁时,会先隐式地申请该表的意向锁(intention lock),类型也为共享或排它。

      • 意向锁是一种特殊的表锁,表示意图对该表加行锁。
        • 意向锁不与意向锁冲突。
        • 意向共享锁与表级共享锁不冲突,其它意向锁与表级锁都冲突。
      • 意向锁由 InnoDB 自动获得、释放,客户端不能控制。
        • 使用意向锁,InnoDB 能更快地发现表级锁是否冲突。
      • 例:
        1. 事务 A 执行 SELECT * FROM tb1 WHERE id=1 FOR UPDATE; ,先请求获得对 tb1 表的意向排它锁,成功之后再请求获得对 id=1 的数据行的排它锁。
        2. 事务 B 执行 SELECT * FROM tb1 WHERE id=2 FOR UPDATE; ,与事务 A 不冲突,能够获得意向排它锁、行级排它锁。
        3. 事务 C 执行 LOCK TABLES tb1 READ; ,请求获得表级只读锁,但 tb1 表已有意向排它锁,因此阻塞等待。
    • InnoDB 提供的行锁属于悲观锁,用户可以自己编程实现乐观锁。如下:

      select name from tb1 where id = 1;                    -- 先查询下修改之前的值,这里假设此时 name 的值为 'one'
      update tb1 set name='two' where id=1 and name='one';  -- 执行之后,根据返回值判断是否修改成功
      
      • 可以根据 timestap 等字段来判断数据是否被修改。
  • 相关配置:

    innodb_lock_wait_timeout = 50     # 事务请求获取 row lock 时,等待的超时时间,默认为 50s 。超时则报错:Lock wait timeout exceeded
    innodb_rollback_on_timeout = OFF  # innodb_lock_wait_timeout 时,是否回滚整个事务。默认为 OFF ,只回滚最后一条语句,可能破坏事务原子性
    

# 表碎片

  • InnoDB 表以 B+ 树结构存储数据。删除一行数据时,并不会释放存储空间,而是标记为 free ,等待写入新数据。这些 free 空间称为碎片(fragment)。

    • 执行 delete、update 操作可能产生碎片。
    • 如果碎片空间比新数据行体积小,则一直不能写入新数据。
    • 一般按 data_free / (data_length + index_length) 计算碎片率。
    • 如果碎片率长时间较高,建议手动清理碎片,从而减少存储空间、减少读取表的耗时。
  • 相关命令:

    -- 显示表状态,其中 Data_free 表示 free 数据占用的存储空间,单位 bytes
    show table status;
    
    -- 统计所有表的碎片率
    select TABLE_SCHEMA,TABLE_NAME,ENGINE,
        DATA_LENGTH/1024/1024 as data_length,
        INDEX_LENGTH/1024/1024 as index_length,
        DATA_FREE/1024/1024 as data_free,
        DATA_FREE/(DATA_LENGTH+INDEX_LENGTH) as free_rate
        from information_schema.tables where DATA_FREE/1024/1024 > 10;
    
    -- MyISAM 表可用该命令清理碎片,这会暂时锁定表
    optimize table <tb>...;
    
    -- InnoDB 表可用该命令清理碎片。这会以 Online DDL 方式拷贝出一个无碎片的新表,然后重命名为原表
    alter table <tb> engine=InnoDB;
    analyze table <tb>;
    
    mysqlcheck
        -h <host> -P <port> -u <user> -p
        -o                    # --optimize ,优化表
          <db> <tb>           # 指定数据库中的数据表
          --databases <db>... # 指定多个数据库的数据表
          --all-databases     # 指定所有数据库