《深入浅出MySQL》备忘录

Eriasuitor
493 阅读14分钟

此文章为《深入浅出MySQL》第二版的备忘录，不做教程使用。

第2章 SQL基础

SQL语句分为:

  • DDL(Data Definition Languages)语句: 数据库结构定义为语言。
  • DML(Data Manipulation Languages)语句: 数据操作语句。
  • DCL(Data Control Languages)语句: 权限控制语句。

MySQL自带数据库:

  • information_schema: 存储数据库对象信息，如用户表，列，权限，字符集，分区等信息。此为虚拟数据库，其中的表均为视图。
    • SCHEMATA: 当前mysql实例的所有数据库信息，show databases信息来源。
    • TABLES: 关于数据库的表(视图)信息，show table from ${schame_name}信息来源。
    • COLUMNS: show columns from ${schema_name}.${table_name}信息来源。
    • STATISTICS: 表的索引信息。show index from ${schema_name}.${table_name}信息来源。
  • cluster: 存储集群信息。
  • mysql: 存储用户权限信息。
  • test: 系统自动创建的测试数据库，任何用户均可使用。

在MySQL世界，drop语句操作的结果都显示 0 rows affected。

WITH ROLLUP可对分类聚合后的结果进行再汇总。 UNION于UNION ALL的区别是会进行一次DISTINCT操作。 ? contents 或者 ? <key_words> 可快速查询使用手册。

第3章 数据类型

若列被指定了zerofill，则会自动添加UNSIGNED属性。 DECIMAL默认为DECIMAL(10, 0)

TIMESTAMP:

  1. 表中的第一个TIMESTAMP列的默认值将被设置为当前时间。
  2. 其值的存储与读取与时区有关。

VARCHAR会保留存储后端空格，CHAR不会。

第4章 运算符

<=>: NULL安全的等于， 也即:

  • SELECT NULL <=> NULL return 1
  • SELECT NULL = NULL return 0

第5章 常用函数

第7章 存储引擎

MySQL默认引擎在5.5后更改为InnoDB。

第8章 合适的数据类型

对于InnoDB，内部行存储合适未区分可变长度和固定长度。故推荐使用VARCHAR，其平均空间占用更小。

编程与存储时，精确计算需使用定点数(Decimal，Java中也有此类)，浮点数会有误差。 对于BLOB和TEXT，删除和修改操作后均需使用OPTIMIZE TABLE对表进行碎片整理。

第9章 字符集

字符集选择原则

  1. 多语言支持或主要处理英文字符，UTF8。
  2. 只需要支持中文，GBK，因其较小，每个中文占用2个字节，UTF8为3个。
  3. 需要做大量的字符计算，排序，比较等，建议定长字符。
  4. 考虑客户端的字符集。

show character set或查看information_schema.character_set显示所有的字符集(MySQL字符串的存储方式)和该字符集对应的校对规则(比较字符串的方式)，其为一对多的关系。 show collation like ***或者查看information_schema.COLLATIONS查看相关字符集的校对规则。 校对规则结尾命名:

  1. _ic: 大小写不敏感。
  2. _cs: 大小写敏感。
  3. _bin: 比较字符编码值。

show variables like ‘character_set_{scope}’; show variables like ‘collation_{scope}’; 查看当前scope(server: 服务器, database: 数据库)的字符集和校对规则。

第10章 索引

索引列的数据越分散，筛选结果集越小，作用越大，比如对于性别列作用不大。 使用短索引。 利用最左前缀。 普通索引均会保存主键键值，故主键尽可能选择较短的数据类型。 HASH索引于BTREE索引:

  1. 只能使用=和<=>进行匹配。
  2. 无法进行排序优化。

第11章 视图

不可更新的视图:

  1. 包含聚合函数(SUM, MIN, MAX, COUNT等)，DISTINCT, GROUP BY, HAVING, UNION, UNION ALL语句。
  2. 常量视图。
  3. SELECT包含子查询。
  4. FROM一个不能更新的视图。
  5. WHERE中的子查询引用了FROM中的表。

WITH CASCADED | LOCAL CHECK OPTION: 满足该视图即可更新 | 满足所有相关视图才可更新。

SHOW TABLE STATUS FROM DB LIKE PATTERN. SHOW CREATE VIEW VIEW_NAME information_schema.views

第12章 存储过程和函数

创建二者需要CREATE ROUTINE权限，修改需要ALTER ROUTINE权限，执行需要EXECUTE权限。 DELIMITER ?可修改语句结束标示。 查看方式与VIEW的查看方式类似。

  1. 事件调度器
  2. 流程控制(IF, CASE, REPEAT等)
  3. 光标
  4. 条件和处理
  5. 变量

第13章 触发器

第14章 事务控制与锁定语句

(UN)LOCK TABLE. 事务控制中SAVEPOINT的使用。 分布式事务命令XA，prepare状态下出现异常，则 binlog不会存在记录，会使数据不一致。数据完整性要求不高时可使用。

第15章 SQL中的安全问题

防止SQL注入:

  1. 对用户输入进行转义，一般的ORM框架可实现。
  2. 使用数据库提供的转换函数，quote等。
  3. 代码敏感字符拦截。

第16章 SQL MODE

不同严格程度SQL语法和数据校验。

SQL_MODE的常见功能:

  1. ANSI和TRADITIONAL: 允许和不允许插入非法时间值，MOD(X, 0)等。
  2. NO_BACKSLASH_ESCAPES: 视 \ 为普通字符。
  3. PIPES_AS_CONCAT: 视 || 为字符串连接符使其不报错。

常用SQL_MODE:

  1. ANSI: 宽松模式。
  2. STRICT_TRANS_TABLE: 严格模式。
  3. TRADITIONAL: 严格中的严格模式。

第16章 SQL分区

SHOW VARIABLES LIKE ‘partition’ 查看是否支持分区。 创建表时使用partition关键词设置分区。 若有主键或唯一键，则分区关键词必须是其中之一。 分区类型:

  1. RANGE: 按照取值范围进行分区。NULL值做最小值处理。
  2. LIST: 利用数组值包含关系进行分区。NULL值如果被允许则必须在候选项中。
  3. COLUMNS: 多列般的RANGE AND LIST(RANGE COLUMNS AND LIST COLUMNS关键词)。
  4. HASH: 常规HASH, 列关键词 % 分区数量确定分区号。当需要增加分区时，已有数据迁移代价太大。LINER HASH, 使用更加复杂的算法进行分区使上述得到优化，但使数据分布不均匀。二者均只支持整数分区(非整数列需要使用函数转换)。NULL被视为0值。
  5. KEY: 支持除BLOB和TEXT外的所有类型列作为分区键。可不指定分区键，默认使用主键或唯一索引列(非空才不会报错)。支持多分区键，同样也可以使用LINIRE KEY。NULL被视为0值。

子分区(复合分区)

可对每个分区(RANGE AND LIST)进行再分区(HASH AND SET)。

分区管理

第18章 SQL优化

  1. SHOW (global | session) STATUS LIKE (Com_% | Innodb_rows% | Connections | Uptime | Slow_queries) 查看命令执行DASHBOARD。
  2. —log-slow-queries=file_name 记录慢查询。
  3. Using explain or desc to analytic sql.
  • select_type: SIMPLE 简单表，PRIMARY: 外层查询，UNION: UNION中的第二个或后面的查询，SUBQUERY: 子查询的第一个SELECT。
  • type:访问类型，性能由高到低为: * NULL: 无需访问即可得出答案。 * const/system: 仅一个匹配行，如单表查询中使用主键和唯一键进行查询。 * eq_ref: 多表连接中使用主键或唯一键进行关键。 * ref: 使用非唯一索引或唯一索引的前缀匹配。 * ref_or_null: 查询条件中包含对NULL的查询。. * index_merge: 索引合并优化，使用多个单列索引且均为使用range访问类型进行查询。含有多列索引时不会存在。 * unique_subquery: in后面是一个查询主键字段的子查询。 * index_subquery: in后面是一个针对非唯一索引字段的子查询。 * range: 索引范围扫描，如对于索引列的大于小于between操作。 * index: 索引全扫描，遍历整个索引查询匹配到的行。如查询一个被索引的列。 * ALL: 全表扫描。

无证据表明：使用多个单列索引进行查询时，若查询中所有相关列均使用范围查找且相关索引列均为NOT NULL将不使用任何索引。另在查询时IS NOT NULL属于范围索引但IS NULL不属于。

  1. explain extended and then show warnings to show the actual SQL that has been executed after SQL optimization. EXPLAIN PARTITIONS 可用于查看语句将操作的分区。
  2. SHOW PROFILE(S):
  • SELECT @@have_profiling 查看是否支持profile。
  • SELECT @@profiling 查看是否开启此功能(默认关闭)。
  • SET profiling=1 开启此功能。
  • SHOW PROFILES查看历史SQL及耗时。
  • SHOW PROFILE (ALL | CPU | block io | source/源文件 etc.) ROR QUERY x 查看某个SQL的各阶段耗时。
    • sending data 状态表示开始访问数据行并把结果返回，往往需要大量磁盘IO，故往往是最耗时的。
  • INFORMATION_SCHEMA.PROFILING 存有profiling信息。
  1. trace: 查看优化依据。
  • SET OPTIMIZER_TTACE=“enabled=on”, END_MARKER_IN_JSON=on;
  • SET OPTIMZER_TRACE_MAX_MEM_SIZE=10000000
  • INFORMATION_SCHEMA.OPTIMIZER_TRACE

索引

  • B-Tree: 广泛使用。B代表balanced，其非二叉树。
  • HASH: 仅Memory引擎支持。无法用在范围查找。
  • R-Tree: 空间索引，MyISAM的一个特殊索引类型，主要用于地理空间数据类型。
  • Full-text: 全文索引。

前缀索引在Order By和Group By时无法使用。且无法实现覆盖索引扫描。

Using index: 覆盖索引扫描。所选择的字段即为索引，无需额外访问。 Using where: 根据索引回表访问数据。需要额外数据访问或筛选。 Using index condition: 使用ICP(Index Condition Pushdown)的特征，某些情况下无需访问数据后筛选。

无法使用索引:

  1. LIKE “%xx”。
  2. 存在数据类型隐式转换。如last_name=1
  3. 复合索引不包含最左边列，即不满足最左原则。
  4. MySQL估计使用索引比全表扫描更慢。比如last_name LIKE “T%”。使用trace可查看依据。
  5. OR左侧有索引，右侧无索引，涉及的索引则均不会被使用。(反正都要全表扫描，没必要多一次索引扫描增加IO访问)

SHOW STATUS LIKE “Handler_read%”: key表一个行被索引值读的次数，越高越好。rnd_next表数据文件中读下一行的请求数，越低越好。

定期检查表: (analyze | check) table payment. 定期优化表: 若删除了表的一大部分(另可使用alter table payment engine=innodb回收不用的空间)，对含有可变长度行(VARCHAR, TEXT, BLOB等)的表进行了很多更改，应使用optimize table payment

常用优化

  • 大批量插曲数据

    • 文本数据采用load data infile 方式导入。
    • SET (UNIQUE_CHECKS | AUTOCOMMIT) =0。

  • 优化ORDER BY Using index: 有序索引顺序扫描直接返回有序结果。select if from a order by id; Using filesort: 对返回的数据进行排序。不是通过索引直接返回排序结果的排序都叫filrsort，即时使用索引列排序依然会出现Using filesort. select * from a order by id; more puzzles...

    where and order by 使用相同的索引并且order by的顺序与索引顺序相同且排序均为升序或降序。
sort_buffer_size: 线程独占。
max_length_for_sort_data: 若读取的字段总大小小于此值，则一次读取满足条件的所有行，然后在排序区sort buffer中排序后直接输出结果，内存消耗较大但效率高。否则仅取出排序字段和指针进行上述操作，若sort buffer不够，则在临时表中存储排序结果。排序后根据指针回表读取记录。

  • GROUP BY

    • 在其后主动添加ORDER BY NULL可取消其默认的排序行为，增加效率。

  • 嵌套查询

    • 没有连接查询快

  • OR

  • 分页 * 根据索引查出满足条件的所有行的某个字段后连表。

  • 使用SQL提示 * SELECT SQL_BUFFER_RESULTS FROM table_name: 生成临时结果集，可释放锁慢慢将数据回传给客户端。 * use index: 指定希望MySQL去参考的索引。 * ignore index: 忽略某些索引。 * force index:

ORDER BY RAND()实现随机排序。 WITH ROLLUP与ORDER BY互斥。 BIT_AND()，BIT_OR()聚合函数进行位运算。

第19章 优化数据库对象

SELECT * FROM table PROCEDURE ANALYSE(): 让MySQL根据已存在的数据特征给出优化建议。 数据拆分: 垂直拆分，不常用列单独存储。水平拆分，不同时期数据于不同表或分区存储。 逆规范化: 增加冗余列。组新表。 维护数据完整性: 定时任务，触发器。 使用中间表提高统计效率。

第20章 锁

MyISAM, MEMORY: 表级锁。开销小，加锁快，粒度大。适合以查询为主，只有少量按索引条件更新数据的应用，如WEB。 InnoDB: 行级锁，也支持表级锁。开销大，加锁慢，粒度小。并发度高。适合有大量按索引条件并发进行少量数据更新，同时又有并发查询需求的应用如在在线事务系统。 DBD: 页级锁，也支持表级。介于上二者之间。

表锁(MyISAM)

SHOW STATUS LIKE ‘table%’查看表锁争用情况。 读锁可存在多个，有且只有一个写锁，写锁与读锁不共存。 LOCK TABLE(S) table (as t) WRITE给表加锁。表使用别名时需要为不同的别名都加锁。 UNLOCK TABLES释放锁。

在自动加锁的情况下，MyISAM总是直接获取SQL所需要的所有锁，所以MyISAM不会出现死锁。 系统变量 concurrent_insert:

  1. 0: 不允许并发插曲
  2. 1: 如果MyISAM表中间没有空洞，即没有删除行，允许读锁存在时另一进程在表尾插入记录。MySQL的默认值，OPTIMIZE TABLE可整理空间碎片。
  3. 2: 不管有无空洞均允许表尾插入。

锁调度机制: 等待队列中有写锁请求页给予最高优先级而与入列顺序无关。这也是MyISAM不适合有大量更新和查询操作的应用的原因。各类操作优先级可设定。maz_write_lock_count可设定当读请求超过一定值后降低写操作的优先级。

InnoDB锁问题

于MyISAM: 有了事务和行级锁。

并发事务带来的问题:

  1. 更新丢失
  2. 赃读
  3. 不可重复读: 二次读时数据被更改或删除。
  4. 幻读: 二次读时有新的满足查询条件的结果。

SELECT @@tx_isolation 事务隔离级别:

  1. 未提交读: 读未提交不读已损坏。
  2. 已提交读: 读已提交。
  3. 可重复读:
  4. 可序列化

SHOW STATUS LIKE “innodb_row_lock%”查看行锁争用情况。 SELECT * FROM information_schema.innodb_locks查看锁等待情况。 通过InnoDB Monitors观察锁冲突情况。(show engine innodb status)。 select * from information_schema.innodb_trx查看所有当前事务信息。

InnoDB加锁方法

SELECT ... LOCK IN SHARE MODE. SELECT ... FOR UPDATE.

行锁情形:

  1. record lock: 对索引项加锁
  2. gap lock: 对记录之间的间隙加锁，主要作用于范围筛选。可解决幻读问题。对于不存在的记录，也会使用此锁，故不存在的记录也会存在冲突。
  3. next-key lock: 对记录及其前后间隙加锁。

死锁:

  • 如果一个事务先对某行加共享锁(SSLECT)后又需要更新该行数据(UPDATE等)，高并发环境下很可能会造成死锁。
  • InnoDB的行锁时通过给索引上的索引项加锁实现的，如果没有索引，将通过隐藏的聚簇索引来对记录加锁。如果不通过索引条件检索数据，InnoDB将对所有记录加锁(表锁)。
  • InnoDB是针对索引项加锁，故如果使用同一索引键操作不同行的记录，也会出现行冲突。
  • 通过不同索引键获取同一记录的锁也会冲突。
  • 是否使用索引是MySQL通过计算不同执行计划的代价决定的，所以分析锁冲突时，有时需要检查SQL的执行计划。

BINLOG, 执行Log.

set innodb_locks_unsafe_for_binlog=‘on’: 在UPDATE INTO target ... SELECT ...source和CREATE TABLE target ... SELECT ... source时不给source加锁。这会造成并发更新source相关字段时(不会被阻塞)，binlog中日志顺序出现错误，数据恢复和复制会出现主从不一致的情况。这些SQL称为不确定的SQL(unsafe SQL)。默认为off，但对于复杂查询可能会有严重的性能问题，故可以使用导出文件再加载文件的方式。

复制模式

SET SESSION ISOLATION LEVEL SERIALIZABLE: 动态改变隔离级别。

使用表锁的情形:

  1. 更新大部分数据且表特大。
  2. 事务涉及多张表，比较复杂。

InnoDB中，给表加锁需要设置AUTOCOMMIT为0。UNLOCK TABLES隐含提交操作，反之不成立。

InnoDB锁是逐步获取的，MyISAM是运行初就获取所有有关表的锁。

涉及外部锁和表锁的情况时，InnoDB并不能自动检测死锁，可设置innodb_lock_wait_timeout最大锁等待时间。

避免死锁:

  1. 不同程序并发操作多张表，应尽量约定一直的表操作顺序。
  2. 不同程序并发操作同一张表，应当约定访问的行的顺序。
  3. 不要先对某记录申请共享锁，再申请排它锁。应一开始就申请排它锁。
  4. REPEATABLE READ隔离级别下，两个线程同时用SELECT ... FOR UPDATE对不存在的数据加排它锁，两个进程均会成功。使用READ COMMITTED可解决由此带来的死锁问题。

SHOW INNODB STATUS查看最后死锁原因。

第21章优化MySQL Server

shoe engine innodb status 查看MySQL各线程的状态。

内存管理及优化

[To Be Continued]

avatar
文章
阅读
粉丝