深入浅出MySQL笔记02--开发篇

深入浅出MySQL 开发篇

1. 表类型（存储引擎）的选择

存储引擎概述

​ 插件式存储引擎是MySQL数据库重要特性之一，可根据应用的需要选择如何存储和索引数据、是否使用事务等。MySQL默认支持多种存储引擎，以适应不同领域的db应用需求。

​ MySQL 5.0 支持的存储引擎：MyISAM, InnoDB, DBD, MEMORY, MERGE,等。其中InnoDB, DBD提供事务安全表，其他都是非事务安全表。

​ MySQL 5.5 之前的默认存储引擎是MyISAM，5.5 之后改为了InnoDB。

各种存储引擎的特性
1. MyISAM

​ 不支持事务、不支持外键，优势是访问速度快，对事务完整性没有要求或以SELECT INSERT为主的应用可以使用这个引擎创建表。

​ 每个MyISAM在磁盘上存储成3个文件，其文件名为表名，扩展名分别是

  • .frm （存储表定义）

  • .MYD （MYData, 存储数据）

  • .MYI （MYIndex, 存储索引）

    MyISAM表还支持3种不同的存储格式：

  • 静态（固定长度）表

  • 动态表

  • 压缩表

​ 静态表是默认的存储格式，表中字段都是非变长字段，从而每个记录长度固定，优点是存储迅速，容易缓存，出故障易恢复；缺点是占用空间比动态表多。静态表数据在存储时会按列宽定义补足空格，应用访问时空格在返回给应用之前被去掉。 ​ 动态表中包含变长字段，记录长度不固定，占用空间少，但频繁地更新和删除记录会产生碎片，需定期执行OPTIMIZE TABLE 或 myisamchk-r来改善命令。出故障不易维护。 ​ 压缩表由myisampack工具创建， 占据非常小的磁盘空间。每个记录被单独压缩，只有非常小的访问开支。

2. InnoDB

​ 提供了具有提交、回滚和崩溃恢复能力的事务安全。对比MyISAM，InnoDB写的处理效率差一些，并会占用更多的磁盘空间以保留数据和索引。

​ InnoDB的表的特点：

  1. 自动增长列

    定义方式：col_def auto_increment

    ​ eg: create table tname (i smallint not null auto_increment)

    设置自动增长列的初始值：ALTER TABLE *** AUTO_INCREMENT = n (默认从1开始)

    查询当前线程最后插入记录使用的值：select LAST_INSERT_ID();

    对于InnoDB，自动增长列必须是索引，也必须是组合索引的第一列。

  2. 外键约束

    1. MySQL支持外键的存储引擎只有InnoDB。在创建外键时，要求父表必须有对应的索引，子表在创建外键时也会自动创建对应的索引。

    2. 创建索引时，可以指定在删除、更新父表时，对子表进行的相应操作，包括：

    • RESTRICT：限制在子表有关联记录的情况下父表不能更新
    • CASCADE: 父表在更新和删除时，更新或删除子表对应记录
    • SET NULL: 父表在更新和删除时，子表对应记录的对应字段被SET NULL
    • NO ACTION: 同RESTRICT
    1. 当某个表被其他表创建了外键参照，该表的对应索引或主键禁止被删除。
    2. 查看外键信息：show create table 和 show table status

  3. 存储方式

    • 使用共享表空间存储，创建的表的表结构存在.frm 文件中，数据和索引存在innodb_data_home_dir 和innodb_data_file_path定义的表空间中，可以是多个文件。
    • 使用多表空间存储，创建的表的表结构存在.frm 文件中，但每个表的数据和索引单独保存在.ibd中。如果是分区表，则每个分区对应单独的.ibd文件，文件名是“表名+分区名”。

    ​ 多表空间的数据文件无大小限制，不需设置初始大小和文件的最大限制、扩展大小等参数。

3. MEMORY

​ 使用存在于内存中的内容创建表。每个MEMORY表只对应一个磁盘文件，格式是.frm。MEMORY表访问非常快，因为数据放在内存中，且默认用HASH索引（创建索引时可以指定为BTREE），但一旦服务关闭，表中数据就会丢掉。

​ 服务器需要足够内存维持所有同一时间使用的MEMORY表，当不再需要MEMORY表时，需要执行DELETE 或 TRUNCATE TABLE或DROP TABLE释放其占用的内存。

​ MEMORY主要用于内容变化不频繁的代码表，或作为统计操作的中间结果表，便于高效对中间结果进行分析。

4. MERGE

​ 是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，MERGE表本身没有数据，对MERGE表的删改查操作实际是对内部的MyISAM表进行的。对MERGE表的插入操作通过INSERT_METHOD子句定义插入的表。

​ 对MERGE表的DROP操作只是删除MERGE表的定义，对内部表没有任何影响。

​ MERGE表 vs 分区表：MERGE表并不能智能地将记录写到对应的表中，分区表可以。通常使用分区表透明地对多个表进行查询和更新操作。

2. 合适的数据类型

  • 对于字符类型，要根据存储引擎进行相应的选择
  • 对精度要求较高的应用中，建议用定点数存储数值，以保证结果的正确性
  • 对含有TEXT和BLOB字段的表，如果经常增删记录，要定期执行OPTIMIZE TABLE对表进行碎片整理
  • 日期类型如果要让不同时区的用户使用，最好使用TIMESTAMP，因为日期类型中只有它能和实际时区对应。

3. 字符集

  • 如果应用需要处理各种文字，或将发布到不同语言的国家地区，应该选择Unicode(对MySQL来说就是UTF-8)字符集。
  • 如果数据库只需要支持一般中文，数据量大且要求性能，应该选择双字节定长编码的中文字符集，eg：GBK。GBK中每个汉字只占2字节，而UTF-8汉字编码是3字节。
    • 如果应用主要处理英文字符，UTF-8更好，因为GBK等的西文编码也是2字节。
  • 如果数据库需要大量的比较、排序等字符运算，那么选择定长字符集处理速度更快。

4. 索引

​ 所有MySQL列类型都可以被索引。MyISAM和InnoDB的表默认创建的是BTREE索引；MEMORY默认使用HASH索引。

设计索引的原则：
  • 最适合索引的列是出现在WHERE子句中的列，或连接子句中指定的列
  • 使用唯一索引：索引的列的基数（集合里的概念，不同的值的个数）越大，索引效果越好。eg，在出生日期列建索引比在性别（只有两个值）列效果好。
  • 使用短索引：如果对字符串列进行索引，应尽可能指定一个前缀长度。短索引节省索引空间，也可能使查询更快；此外，对于较短的键值，索引高速缓存中的块能容纳更多键值，增加了找到行而不用读取索引中较多块的可能性。
  • 利用最左前缀：
  • 不要过度索引：额外的索引会占用额外磁盘空间，降低写操作的性能。修改表内容时，索引也需要更新甚至重构。
  • 对于InnoDB的表
    • 记录默认按照一定的顺序保存
      • 有明确定义的主键：按主键顺序
      • 无明确定义的主键但有唯一索引：按唯一索引顺序
      • 都无：自动生成一个内部列，按该列顺序
    • 按照主键或内部列进行访问是最快的，所以InnoDB表尽可能指定主键
    • 当表中多个列都是唯一时，选最常作为访问条件的列作为主键
    • InnoDB表的普通索引也会保存主键的键值，所以主键要尽可能短
BTREE 索引和HASH索引
  • HASH索引特点：
    • 只用于使用=或<=>操作符的等式比较
    • 优化器不能使用HASH索引加速ORDER BY
    • 只能用整个关键字来搜索一行
  • 对于BTREE索引，使用> , < , >= , <= , BETWEEN , != 或者 <> , 或者 LIKE （模式不以通配符开始）时，都可以使用相关列上的索引

5. 视图

​ 视图（view）是一种虚拟存在的表，并不在数据库中实际存在，使用透明。

视图相对于普通的表优势：
  • 简单：用户无需关心后面对应的表结构、关联条件、筛选条件，对用户来说是过滤好的复合条件的结果集。
  • 安全：用户只能访问被允许查询的结果集
  • 数据独立：一旦视图结构确定，可以屏蔽表结构变化对用户的影响
视图操作

  • 创建或修改视图

    创建视图需要有CREATE VIEW 权限，并对查询涉及的列有SELECT 权限。若使用CREATE OR REPLACE 或ALTER修改视图，还需要视图的DROP权限。

    创建视图语法：

    CREATE [OR REPLACE]

    ​ VIEW vname [(col_list)]

    ​ AS select_statement

    修改视图语法：

    ALTER

    ​ VIEW vname [(col_list)]

    ​ AS select_statement

  • 删除视图

    DROP VIEW [IF EXISES] vname

  • 查看视图

    MySQL 5.1开始 SHOW TABLES [STATUS]同时会显示视图信息。

6. 存储过程和函数

​ 存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程和函数可以简化工作，减少数据在数据库和应用服务器之间的传输，利于提高数据处理效率。

​ 存储过程和函数的区别在于函数必须有返回值，而存储过程没有，存储过程的参数可以使用IN, OUT, INOUT类型，函数的参数只能是IN。

相关权限
  • 创建：需要CREATE ROUTINE 权限
  • 修改或删除：需要ALTER ROUTINE 权限
  • 执行：需要EXECUTE 权限
创建or修改存储过程和函数

CREATE PROCEDURE sp_name ([proc_param]) ​ [characteristic] routine_body

CREATE FUNCTION sp_name ([func_param]) ​ RETURNS type ​ [characteristic] routine_body

调用

CALL sp_name ([proc_param])

存储过程的好处

​ 处理逻辑封装在数据库端，调用者不需要了解中间的处理逻辑，一旦处理逻辑发生改变，只需要修改存储过程，而对调用者的程序完全没有影响。

7. 触发器

​ 触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。可以协助应用在数据库端确保数据的完整性。

创建触发器

CREATE TRIGGER trigger_name trigger_time trigger_event

ON tb1_name FOR EACH ROW trigger_stmt

触发器只能创建在永久表上， 不能对临时表创建触发器。

  • trigger_time ：触发时间，可以是
    • BEFORE , 在检查约束前触发
    • AFTER , 在检查约束后触发
  • trigger_event：触发事件，可以是增删改

对同一个表相同触发时间的相同触发事件，只能定义一个触发器。

可以使用别名OLD和NEW来引用触发器中发生变化的记录内容。

触发顺序
  • 对于有重复记录、需要进行UPDATE 操作的INSERT,触发器触发的顺序是BEFORE INSERT -> BEFORE UPDATE -> AFTER UPDATE;
  • 对于没有重复记录的INSERT,就是简单执行INSERT, 触发顺序是BEFORE INSERT -> AFTER INSERT
  • 对实际执行UPDATE 操作的记录，仍会执行BEFORE INSERT触发器的内容

8. 事务控制和锁定语句

​ 默认情况下，表锁和行锁都是自动获得的，不需要额外命令。但是在有的情况下，用户需要明确进行锁表或进行事务的控制，以确保事务的完整性。

LOCK TABLE 与 UNLOCK TABLE
  • LOCK TABLES 锁定用于当前线程的表，如果表被其他线程锁定，则当前线程会等待，直到可以获取所有锁定为止
  • UNLOCK TABLES 释放当前线程获得的任何锁定
事务控制

​ 默认情况下，MySQL是自动提交的。

  • START TRANSACTION 或BEGIN 开始一项新的事务
  • COMMIT 和 ROLLBACK 用来提交或回滚事务
  • CHAIN 和 RELEASE 子句分别定义事务提交或回滚之后的操作，
    • CHAIN 会立即启动一个新事务，并和刚才的事务有相同的隔离级别；
    • RELEASE 会断开和客户端的连接
  • SET AUTOCOMMIT (=0) 修改当前连接的提交方式，设为0时之后的所有事务都需要明确的命令进行提交或回滚。

如果在锁表期间，用start transaction开始一个新事务，会隐含执行 unlock tables

9. SQL中的安全问题

SQL注入

10. MySQL 分区

​ 分区是指根据一定的规则，数据库把一个表分解成多个更小的、更容易管理的部分。对于访问数据库的应用来说，逻辑上只有一个表或一个索引，实际上这个表可能有数10个物理分区对象组成，每个分区都是一个独立对象，可以独自处理，可以作为表的一部分处理。分区对于应用来说是完全透明的，不影响应用的业务逻辑。

分区的优点
  • 和单个磁盘或文件系统相比，可以存储更多数据
  • 优化查询。在Where子句中包含分区条件时，可以只扫描必要的一个或多个分区来提高查询效率；同时在涉及SUM和COUNT这类聚合函数的查询时，可以容易地在每个分区上并行处理，最终只需要汇总所有分区得到的结果
  • 对于已经过期或不需要保存的数据，可以删除与这些数据有关的分区来快速删除数据
  • 跨多个磁盘分散数据查询，以获得更大的查询吞吐量
分区概述

​ 分区有利于管理非常大的表，采用了分治逻辑，分区引入了分区键的概念。分区键用于根据某个区间值、特定值列表、HASH函数值执行数据的聚集，让数据根据规则分布在不同的分区中。

分区类型
  • RANGE分区：基于一个给定连续区间范围，把数据分配到不同分区
  • LIST分区：基于枚举出的值列表分区
  • HASH分区：基于给定的分区个数，分配数据
  • KEY分区：类似HASH分区

注意：无论哪种分区类型， 都不能用主键/唯一键字段之外的其他字段分区

Range分区

利用取值范围将数据分成分区，区间要连续且不能互相重叠，使用VALUES LESS THAN进行分区的定义。

注意：每个分区都按顺序进行定义，从最低到最高。

可以使用VALUES LESS THAN MAXVALUE子句提供给所有大于明确指定的最高值的值。

特别适用于：

  • 需要删除过期数据，eg：ALTER TABLE tname DROP PARTITION p0
  • 经常运行包含分区键的查询
List 分区

建立离散的值列表告诉数据库特定的值属于哪个分区。

使用PARTITION BY LIST + VALUES IN (X,X)

LIST 分区不需要声明任何特定的顺序。

若试图插入的列值不包含在分区值列表中时，INSERT会失败并报错。

Columns分区

可以细分为RANGE Columns和LIST Columns分区，都支持整数、日期时间、字符串三大数据类型。

Columns分区还支持多列分区。

Hash分区

主要用来分散热点读，确保数据在预先确定个数的分区中尽可能平均分布。