• 1. MySQL 设计应用
  • 2. 内容概要设计规范设计规范 SQL规范 附录schema设计SQL语句 数据类型 表设计(主键、引擎) 索引设计应用 设计简单分布式扩展 全局唯一主键 不要过度Sharding应用实现计数器COUNT() LIMIT分页应用实例
  • 3. 为什么要规范化?积极方面 提升性能? 开发效率 易于维护消极方面 浪费时间?要自由还是要规范? 《Joe Celko's SQL Programming Style》
  • 4. 命名规范 结构规范 字 符 集 SQL 规范 操作规范规范概要
  • 5. 命名规范-约定风格DB(库) — 库名小写,以db_为前缀,以实际项目名为库名,总长度超过30个字符则 使用缩略词,缩略词要大于3个字符,清晰易懂; Table(表) — 表明小写,没有固定前后缀,以实际模块名为表名,位于同一子模块的 一类表尽量以子模块名(或部分单词)为前缀或后缀,总长度超过30个字 符则使用缩略词,缩略词要大于3个字符,清晰易懂; Column(字段) — 字段名小写,没有固定前后缀,以实体属性名为字段名,总长度超过30 个字符则使用缩略词,缩略词要大于3个字符,清晰易懂; Index(索引) — 索引名小写,以idx_为前缀,后跟字段名;联合索引则以下划线连接各 字段名,注意顺序一致,总长度超过30个字符则截取字段名前几个字符替 代字段名; Stored programs(存储程序) — 存储程序名小写,以asp_为前缀; Views(视图) — 视图名小写,以vi_为前缀,后接来源表名; Triggers(触发器) — 触发器名小写,以tg_为前缀; TEMPORARY(临时表) — 临时表名小写,以tmp_为前缀,后接来源表名; 以上 Stored programs、Views 等命名长度均不得超过30个字符。 以上命名名称可以使用下划线适当分割词组。
  • 6. 命名规范-扩展风格 避免使用特殊字符(大小写字母、数字以外的字符),可以使用 _(下划线)作为连字符; 避免使用MySQL保留字命名,避免使用界定标识符(MySQL为``); MySQL保留字列表; http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html 可以在执行SQL时去掉``界定标识符(键盘1前面字符,反引号),这样如果无意中使用了保留字会抛出ERROR; 使用 COMMENT 给字段添加注释,最好所有字段都有注释,而标志位字段一定要添加注释;
  • 7. 结构规范-约定风格 PRIMARY KEY 在最前; 表结构中字段排列顺序 A 很少更新的非变长列 B 很少变更的变长列 C 经常变更的非变长列 D 经常变更的变长列 联合主键中整数、存储字节少的字段在前; 注:非变长列如INT,CHAR等, 变长列如VARCHAR;
  • 8. 字符集规范 DB、Table、Column 等统一使用UTF8字符集(需要明确指定); 校对字符集(Collation)使用utf8_general_ci(不需要明确指定,默认既是);
  • 9. SQL语句书写风格SQL语法词组大写,保留字大写; 在SQL中,别名应该用 AS 指定,例 SELECT user_id AS bbs_user_id FROM bbs_user AS u; 在 WHERE条件中,字段顺序统一,按照匹配程度从左到右排列, PRIMARY KEY 或 UNIQUE KEY 的等值匹配在最左; 例 SELECT title FROM bbs_user WHERE user_id=1 AND u_name='name' AND age=100; 在 WHERE条件中,字段永远放在左面,静态值或表达式放在右面(紧凑格式); 例 SELECT title FROM bbs_user WHERE age < YEAR(CURDATE()) - '1980'; 需要换行则保留字在最左面,其他字符缩进后放在右面; SELECT user_id, nickname, create_date, title AS bbs_title, articles AS bbs_articles FROM bbs_user WHERE update_date >= ‘2011-10-20 00:00:00' AND update_date < ‘2011-10-21 00:00:00' ORDER BY user_id DESC LIMIT 0, 10; SELECT user_id, nickname, create_date, title AS bbs_title, articles AS bbs_articles FROM bbs_user WHERE update_date >= ‘2011-10-20 00:00:00' AND update_date < ‘2011-10-21 00:00:00' ORDER BY user_id DESC LIMIT 0, 10; SELECT USER_ID, NICKNAME, CREATE_DATE, TITLE AS BBS_TITLE, ARTICLES AS BBS_ARTICLES FROM BBS_USER WHERE UPDATE_DATE >= ‘2011-10-20 00:00:00' AND UPDATE_DATE < ‘2011-10-21 00:00:00' ORDER BY USER_ID DESC LIMIT 0, 10;
  • 10. 设计规范规范:设计规范,SQL规范; 对象设计规范:数据类型,表设计,约束使用。 SQL编写规范:绑定变量与替代变量,数据类型转换,表连接,分页查询等。 不要在条件里写不必要的条件。(经常见到,where 主键 + 其他条件)
  • 11. 附录A - 字段后缀参考 _id 唯一标识符,PRIMARY KEY _date/_time 日期时间维度 _nbr / _num 标签号 _name 名称(alphabetic name) _code 编码 _size 尺寸/单位属性 _tot 总和 _seq 顺序编号 _tally 一组值的计数,绝对尺寸 _cat 类别 _class 内部编码 _type 类型 _status 状态标识 _addr 地址或位置 _img 图像数据类型 _log 日志
  • 12. 附录B — 权值表示法表 1. 查询条件中操作符的权值 上面表格中的权值数没有实际意义,只需要了解它们的排序即可。 权值表示法参考(简单的SQL书写优化): http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-1002limh/操作符 权值 =10 > 5 >= 5 < 5 <= 5 LIKE 3 <> 0
  • 13. 附录B - 权值表示法表 2. 查询条件中操作数的权值 操作数 权值 仅常量字符10 仅有列名5 仅有参数5 多操作数表达式3 精确数值类型2 其它数值类型1 时间数据类型1 字符数据类型0 NULL 0
  • 14. Schema 设计决定数据分布 决定索引结构 决定访问模式 决定扩展方案 最终决定性能
  • 15. SQL 语句 SQL复杂度:复杂SQL —> 简单SQL 简单SQL缓存命中率更高; 一条SQL只能使用一个CPU运算 锁定时间短,锁等待时间短,锁竞争减少 —> 并发提高; 例: OR —> UNION ALL —> WHERE IN(……) Subquery —> JOIN —> WHERE IN(……) 只取需要的数据,不用 SELECT * 模式 消耗更多 CPU、内存、IO、网络带宽; 更安全的设计,减少表变化带来的影响 避免全表扫面 避免 LIKE % 前缀模糊查询; 避免取反查询:NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE 等; 多使用 LIMIT 运算移到应用端CPU 减少查询的次数 <> SQL复杂度
  • 16. 数据类型选择优化数据类型 相关数据类型特征
  • 17. 选择优化数据类型最小化数据类型 仅使用能够正确存储数据的最小类型 选择尽可能占用最少字节的类型,节省 磁盘空间、IO、内存、CPU 等资源; 简单的数据类型 sort int—> sort string 比较整数的代价小于比较字符串,字符集和排序规则使字符比较更复杂; 如使用 MySQL内置的类型保存日期和时间,而不是字符串; 如使用整数保存IP地址(INET_ATON 和 INET_NTOA); 尽量避免 NULL值 定义: NOT NULL,因为 NULL是默认值; 影响: 需要更多的索引空间,NULL列被索引的时候,每条记录都需要一个额外的字节; 会导致 MyISAM中固定大小的索引(如一个整数列上的索引)变为可变大小的索引; 难以优化 NULL 列的查询,使索引、索引统计更复杂,导致含 NULL列的复合索引无效;
  • 18. 整数类型整数类型: BIGINT —> INT —> MEDIUMINT —> SMALLINT —> TINYINT 选择存储长度 - INT(1)、INT(5)、INT(19) INT(1) —> TINYINT INT(5) —> SMALLINT INT(19) —> BIGINT 存储、计算完全相同,实际插入值和存储字节没有任何改变或影响; 仅客户端显示字符宽度(个数)不同。 无符号(非负数) – UNSIGNED 如果一个整数列指定为zerofill,则自动添加UNSIGNED属性。 能够把正整数上限提高一倍,对存储空间、性能没有影响。 强调: 该使用TINYINT、SMALLINT、MEDIUMINT的时候不能用int(n)代替,避免浪费存储空间、增加IO负担;
  • 19. 实数类型 浮点数和定点数 FLOAT —> DOUBLE —> DECIMAL 精确: DECIMAL 非精确:FLOAT、DOUBLE 定点数实际以字符串形式存放,可以自己定义小数位。 而 FLOAT和 DOUBLE使用标准的浮点运算进行近似计算。 浮点数和定点类型同样可以指定为 UNSIGNED属性,但与整数类型不同的是,列值的范 围保持不变。 使用原则 浮点数存在误差问题,应尽量避免做浮点数比较; 对货币等精度敏感的数据,应使用定点数存储。
  • 20. 日期和时间类型 尽量选择精确的存储类型, 比如 记录年份,用1个字节类型的 YEAR来存储,而不是用4个字节的DATE类型存储; 时区属性 TIMESTAMP 有时区属性 DATETIME 无时区属性 日期和时间类型中,只有 TIMESTAMP和实际的时区相对应,即会转换为读取用户所在地的时区; 所以 TIMESTAMP显示的值依赖于时区, MySQL服务器、操作系统、连接的客户端都有时区设置的选项。 注: TIMESTAMP 在 MySQL各版本间会有变化,升级后需要 SHOW CREATE TABLE tb_name 看一下; 类型(同义词)存储长度最小值(无符号)最大值(无符号)DATETIME81001-01-01 00:00:009999-12-31 23:59:59TIMESTAMP41970-01-01 00:00:002038-01-19 03:14:07
  • 21. 字符串类型 CHAR & VARCHAR 存储长度短,固定长度,频繁更新的列适合用 CHAR 类型。 当最大长度远大于平均长度,并且更新很少的情况下适合使用 VARCHAR类型,这样不会有碎片问题。 VARCHAR: 最大长度小于或等于255,用1个字节来保存存储长度,大于255使用两个字节。 例如对于单字节字符集(latinl字符集),VARCHAR(100) - 101字节,VARCHAR(300) - 302字节。 注:更新频繁会产生碎片,如100字节更新为200字节,则原来的位置已经不在适合存储,MyISAM会拆分,InnoDB会分页;所以更新频繁适合用CHAR类型。 并非所有的存储引擎都会按照相同的方式来处理定长和可变长度的字符串,如Memory 存储引擎使用了固定长度的行,对可变字段同样分配可能的最大存储空间。 例外:BINARY 和 VARBINARY还是按照传统的字节存储,即二进制字符串,排序也是按照字节进行比较,因此不区分大小写,这比按照字符集排序效率高些。 BLOB & TEXT BLOB 二进制数据,没有字符集、没有排序规则; TEXT 都有; 字符串值可预知,且个数有限: VARCHAR —> CHAR —> ENUM VARCHAR —> CHAR —> SET 与 CHAR 和 VARCHAR列的联接操作慢; 联接速度: ENUM join ENUM —> VARCHAR join VARCHAR —> VARCHAR join ENUM。 随着数据增长、 ENUM值增长,维护开销曾大 ALTER TABLE tb_name 因此变更频繁的列,不适合使用 ENUM类型。 比较时需要加 '' 单引号(即使是数值)
  • 22. 字符串类型BLOB 和 TEXT: BLOB 二进制数据,没有字符集、排序规则; TEXT 都有; MySQL对BLOB、TEXT列的排序和其他类型不同,不会按照完整长度进行排序,只会按照 max_sort_length参数设定的前N个字节进行排序;也可以使用ORDER BY SUBSTRING(column, length) 临时指定排序长度。 MySQL不能索引这些数据的完整长度,也不能为排序使用索引,因此要谨慎使用BLOB和TEXT 类型。 对于Memory,不支持BLOB和TEXT类型; 使用了BLOB、TEXT列并且需要隐式临时表的查询只能使用磁盘上的MyISAM引擎临时表,性 能下降很严重。 可以使用ORDER BY SUBSTRING(column, length)把这些值转换为字符串,让他们使用内存临时 表,但是要保证SUBSTRING子字符串足够短,不能超过max_heap_size 或 tmp_table_size 的最大值。 如果解析器(explain)中的Extra列显示"Using Temporary",就说明使用了隐式临时表。 ENUM: 有时可以使用ENUM代替字符串类型, ENUM最大可以存储65535个不同的字符串,在内部 ENUM类型把值存储为整数,以表示值在表中的位置,同时保留一份查找表"Lookup Table",映射 整数和字符串的关系。 MySQL会以非常紧凑的方式保存ENUM值,根据列表中的数量,MySQL会压缩到1-2个字节中, 因此ENUM 类型的存储空间非常节约。 ENUM在做整数和字符串转换时有一些开销,做CHAR 和VARCHAR 列的联接操作时会慢一些: 联接速度: ENUM join ENUM > VARCHAR join VARCHAR > VARCHAR join ENUM。 因为ENUM类型在内部把值存储为整数,所以不要用数字定义ENUM 常量,这种双重性非常 迷惑人。 另外一个特性是ENUM 类型在内部是按照数字顺序进行排序的,而不是字符串顺序,解决办 法是按照想要的顺序来定义ENUM 字段,或使用FIELD()显示地规定排序顺序,但这个函数无法所 使用索引:ORDER BY FIELD(‘a’,‘b’,‘c’); 对于ENUM 字段,字符串列表是固定的,添加或删除字符串需要使用ALTER TABLE操作,随着 数据的增长,维护开销也会急剧增长,因此对于未来可能会频繁改变的字符串,不适合使用 ENUM 类型。
  • 23. 字符串类型SET: 和ENUM类似,也是一个字符串对象,但是 可以包含多个值,以,逗号分隔,最多64个成员,1-8个 成员占1字节,33-64各成员占用8个字节。 可以一次选取多个值,用FIND_IN_SET()或FIELD()等 函数,而ENUM每次只能选取一个。 另外SET类型同ENUM 类型一样,改变列定义需要 ALTER TABLE操作,维护代价高昂。
  • 24. 问题 (1)问题: 使用VARCHAR(5) 和 VARCHAR(100) 保存‘hello’占用的存储空间是一样的,那有什么区别吗? 其实区别是巨大的,较大的列会使用更多的内存,因为MySQL通常会分配 固定大小的内存块来保存值,对排序、基于内存的临时表、文件排序、基于磁盘的临时表也会放生这样的情况。 最好的策略就是只分配真正需要的空间。
  • 25. 标识位选择合适的类型 1 - 整数类型 - 最佳选择,速度最快; 2 - ENUM、SET - 如性别、状态值 或 相关类别; 3 - 字符串 - 尽量避免使用字符串; 因为字符串通常占用空间较大,排序较慢; 特别注意在MyISAM引擎上使用字符串做标识列会很慢,因为MyISAM默认情况下为字符串使用压缩索引(Packed Index),查找更为缓慢; 要考虑存储类型、以及MySQL如何对它们进行比较、计算;如MySQL 在内部把ENUM和SET保存 为整数,但是比较时会转换为字符串; 1> 确定数据类型后,尽量保证关联表中使用相同的数据类型,因为有可能进行(JOIN)联接操作; 2> 即使相同的数据类型,也要精确匹配,主要是类型的具体属性;如 UNSIGEND这样的属性; 因为比较时可能导致隐式的类型转换,或产生性能问题和导致难以察觉的错误。 3> 选择最小的数据类型能表明所需值的范围,如TINYINT 代替INT 类型;
  • 26. 主键选择 主键选择原则 尽量选择整型,定义 AUTO_INCREMENT属性; 主键字段类型尽可能小,能用 SMALLINT就不用 INT,能用 INT就不用 BIGINT; 业务主键/逻辑主键,推荐使用逻辑主键,避免更新问题; 外键 有额外开销 逐行操作 由程序保证约束 对于InnoDB表,如果不显式定义主键,会导致每次对新数据行进行排序,从而损害性能。
  • 27. 存储引擎选择 要求数据安全、高可用为前提:InnoDB 要求高并发性能为前提:InnoDB 要求事务支持为前提:InnoDB 大数据量存储,并发更新少:MyISAM 数据不重要,无业务关联影响,读写为主:MyISAM 数据不重要,无业务关联影响,资源紧张:MyISAM 其他情况:InnoDB
  • 28. 表的容量表容量字段数: 索引数: 记录数: 数据文件大小: 索引文件大小: 唯一索引数量: 维护成本 数据恢复,数据修复,索引修复,事务回滚,ALTER TABLE 所有一切要看 SQL 复杂度! 提前预估每天 —> 每周 —> 每月 —> 一年 一年内的单表数据量预估: 纯INT不超过1000W 含CHAR不超过500W 建议单库不超过300-400个表。 单表1G体积500W行评估: 顺序读1G文件需N秒 单行不超过200Byte 单表不超过50个纯INT字段 单表不超过20个CHAR(10)字段 单表字段数上限控制在20~50个
  • 29. 索引设计索引对性能的影响 索引类型 索引设计原则 高性能索引策略
  • 30. 索引对性能的影响 影响更新性能,特别是并发更新; 和简单的触发器大致相当; 索引越多,更新数据越慢,尤其 InnoDB 比 MyISAM 引擎更严重一些; 问题2:其实对 SELECT的影响也很大,比如 InnoDB 的 COUNT()。 索引数:3/10 索引量:1/2 严重影响查询(优化)分析器性能 查询(优化)分析器生成执行计划时,由于索引过多,会花费更长的时间,也会造成误判,生成不合理的执行计划; 用 show profile 可以分析查询(优化)分析器;
  • 31. 索引类型B-Tree / B+Tree 索引 Hash Index 哈希索引 R-Tree(Spatial Index) 空间索引 全文索引
  • 32. 索引设计原则索引设计高效原则(就一条) 最少的索引适用于最多的 SQL Query(select/update/delete); 具体应用:复合索引; 综合评估数据密度和数据分布; 索引命中核心原则(就一条): 符合最左前缀原则(Leftmost Prefix); 索引命中性能原则(未完待续) 主键/唯一键值等值匹配 整数索引等值匹配 字符索引等值匹配 范围查找
  • 33. 高性能索引策略不能使用索引的查询类型: 不符合最左前缀原则; 范围查询,且区间过大(2/8原则); OR 操作中有一列不在索引中,则全部列不会使用索引; 如果有类型转换,不会使用索引,如字符列没有用''引用起来; 如果有 构造列(AS) 或 函数列 不会使用索引; LIKE 搜索使用左面匹配; column_name IN NOT NULL; 参考 频繁更新列不适合建立索引 不在索引列进行数学或函数运算 索引次序和索引中列的次序一样重要 索引设计一个是不断尝试和权衡的过程 聚合索引 覆盖索引
  • 34. 应用设计 简单分布式扩展 全局唯一主键 不要过度Sharding
  • 35. 应用设计大的方面:应用需求,访问方式,OLAP/OLTP 分离; 一、关系数据库设计范式(基本三范式,一般数据库只需满足第三范式(3NF)): 1、对数据库范式的态度,基本呈现两个极端: 1> 严格遵守 2> 散乱设计 2、非规范化 1> 单列存储多个值 2> 冗余字段 3> 伪主键 应该怎样做: 要从遵守数据库范式的设计出发; 当需要违反数据库范式的时候在去做反范式处理;
  • 36. 应用设计适度冗余。 被频繁引用且只能通过 Join 2张(或者更多)大表的方式才能得到的独立小字段。 这样的场景由于每次Join仅仅只是为了取得某个小字段的值,Join到的记录又大,会造成大量不必要的 IO,完全可以通过空间换取时间的方式来优化。 不过,冗余的同时需要确保数据的一致性不会遭到破坏,确保更新的同时冗余字段也被更新 规范化/逆规范化: 为了表满足业务需求,首先肯定需要将表规范化,这个大家应该知道就是按照数据库理论中的范式对表进行优化设计,已达到表数据的冗余量小、性能好。一般表设计满足第三范式即可满足 要求。 逆规范化:本来是满足第三范式要求表,为了满足应用程序、性能、架构设计的要求,将表回退至第二范式。用空间换时间的典型实例。 表A、表B:以满足第三范式要,有个使用频率特别高的查询需要关联A\B表的b列访问这时候,可能需要将d冗余存储到表A中,提高查询访问的性能 多表的相同列,类型、长度保持一致 冗余列 为了避免联表查询,有时候可以适当的数据冗余,比如邮箱、姓名这些不容易更改的数据。
  • 37. 应用设计二、分布式扩展 MySQL 的分布式—以扩展为前提的系统设计; MySQL 的replication功能; master/slave的特征—对参照系进行扩展,更新类不扩展; MySQL 的横向扩展和partitioning; MySQL 的横向扩展策略; 以partitioning为前提的设计; 避免join—利用where…in…; 分库分表 表分区 适度冗余 - 让Query尽两减少Join 通过表字段的冗余来实现。 大字段垂直分拆-text字段 大表水平分拆- 统计表 - 准实时优化
  • 38. 应用设计TEXT/BLOB字段分开单表存储与原表主键一一对应。 经常查询的字段与不经常查询的字段分开存储,用主键一对应,例如帖子标题和内容。 频繁进行统计的SQL,可以转化为表存储,将查询压力分散到更新时。 频繁的对表进行count也可以转化为统计表存储,《计数表的技巧》。 什么样的数据适合通过Cache技术来提高系统性能: 系统各种配置及规则数据; 活跃用户的基本信息数据; 活跃用户的个性化定制信息数据; 准实时的统计信息数据; 其他一些访问频繁但变更较少的数据; Scale-out 优先级最高 避免XA 减少对DB的依赖 异步数据流 恰当使用Cache Search 空间换时间—BASE CAP 限制列的数量 分离到不同的表 频繁更新列 text blob列
  • 39. 简单分布式扩展合理分表: userid/date/area 同一个实例: db_name.tb_name_1 db_name.tb_name_2 db_name.tb_name_3 不同的实例(方便连接): Port_3306: db_name.tb_name Port_3307: db_name.tb_name Port_3308: db_name.tb_name 不同的实例(方便迁移): Port_3306: db_name_1.tb_name Port_3307: db_name_2.tb_name Port_3308: db_name_3.tb_name 不同的实例(方便迁移): Port_3306: db_name_1.tb_name_1 Port_3307: db_name_2.tb_name_2 Port_3308: db_name_3.tb_name_3 SELECT 111 % 100 = 11, CRC32(111) % 100 = 81 固定分配,简单、开销低,应用里面可以固定住。 缺点:数据热点问题,移动数据困难,需要重新取模运算;
  • 40. 全局唯一主键分库分表(sharding)主键全局唯一性解决方案 SELECT UUID(); 不适合做主键,唯一键值也不合适; SELECT UUID_SHORT(); 需要 BIGINT 类型存储,需要基于行复制; 重启动MySQL期间不要修改系统时间,对 server_id 的设置也需要规划; 生成算法: (server_id & 255) << 56 + (server_startup_time_in_seconds << 24) + incremented_variable++; AUTO_INCREMENT AUTO_INCREMENT 区间 auto_increment_offset / auto_increment_increment
  • 41. 全局唯一主键CREATE TABLE global_id ( id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, static_name CHAR(1) NOT NULL DEFAULT '', PRIMARY KEY (id), UNIQUE KEY static_name(static_name ) ) ENGINE=MYISAM; 需要获得全局唯一ID时,执行下面的SQL语句: REPLACE INTO global_id (static_name) VALUES ('a'); SELECT LAST_INSERT_ID(); 第一次执行这个语句后, global_id 表将包含以下数据: +--------+-------------+ | id | static_name | +--------+-------------+ | 1 | a | +--------+-------------+ 继续REPLACE操作,这个表将一直只有一行数据。
  • 42. 全局唯一主键为多个表提供全局唯一主键: CREATE TABLE global_id ( static_name CHAR(50) NOT NULL, `id` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0', PRIMARY KEY (static_name) ) ENGINE=INNODB; 注意,id字段不是自增主键,在使用前,需要先初始化数据(表名): INSERT INTO global_id (static_name) VALUES (‘bbs_user'),(‘bbs_photos'),(‘bbs_comment'); 接下来,通过下面的SQL语句获得新的 BBS 图片ID: UPDATE global_id SET id = LAST_INSERT_ID(id + 1) WHERE static_name = 'bbs_photos'; SELECT LAST_INSERT_ID(); 执行更新操作,将id字段增加1,并将增加后的值传递到 LAST_INSERT_ID 函数, 从而指定了 LAST_INSERT_ID 的返回值。 改进: INSERT INTO global_id (static_name) VALUES('bbs_content') ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id + 1); SELECT LAST_INSERT_ID(); 采用 INSERT … ON DUPLICATE KEY UPDATE MySQL扩展, 这个扩展的功能和 INSERT 一样插入一行新的记录, 但是当新插入的行的主键或唯一键(UNIQUE Key)和已有的行重复时, 会对已有行进行UPDATE操作。 需要注意的是通过这种方式获取的序列起始值为0,而不是1。 因为第一次执行时,没有执行UPDATE, 所以也没有为 LAST_INSERT_ID 传递值,也没有自增字段, 所以 SELECT LAST_INSERT_ID() 将返回0。
  • 43. 不要过度Sharding急于 Sharding,分区键考虑不充分,影响业务发展 Sharding 本身是一个需要慎重对待的事情,尤其是分区键的选择。好的分区键会让整个系统基本上对应用没有影响,但是如果选择了一个不合适的分区键,就可能给应用程序带来巨大的麻烦,甚至让有些功能变得几乎不可实现。因为不合适的分区键可能会让本来需要 分组/排序/连接 的数据被拆分分到不同的物理节点,迫使很多原本在一个 Instance 内部的简单处理都无法完成,不得不让应用程序进行大批量IO运算,反而增大的处理成本。应用程序的开发成本上升了,开发效率下降了,业务发展可能就受阻了。 过于 Sharding,单机性能过低,造成节点数量过大,维护成本倍增 但事实真的如此吗?可能并不是这么简单。首先,在很多人的意识中,扩展成本只是一个节点的购买成本,而忽略了该节点所占用的IDC资源(机架/电力/布线…)。如果从 IDC 资源角度来考虑,单机性能越高,成本也越低。也就是说节点数越少,IDC 资源消耗也越少(曾今就由朋友说笑:我们使用一台大型机搞定所有业务,成本一定是最低的)。 除了显性的财务支持成本, Sharding 同时也给运维工作带来了维护成本的增加。节点数多了,需要维护的机器也就增多了,一个节点发生故障的概率也增加了。维护变更的重复工作量增大了,操作失误的可能性也就增大了。所以我们又不得不考虑平衡节点数和单机性能。 这时候我们就不得不考虑,到底我们该 Sharding 到何种程度,才能既有效的提升了Scale Out能力,又能控制总体成本尽可能低。既能让维护成本可控,又能分散可用性故障点。 为了 Sharding 而 Sharding
  • 44. 应用实现 计数器COUNT() LIMIT分页 创建自己的HASH索引
  • 45. 应用实现应用实现:分页,分库分表,表分区; 非user_id,是推的发表时间,fan out架构; 应用程序实现JOIN: 如果需要跨主机进行JOIN,跨应用进行JOIN,或者数据库不能获得较好的执行计划,都可以自己通过程序来实现JOIN。 例如:SELECT a.*,b.* FROM a,b WHERE a.col1=b.col1 AND a.col2> 10 ORDER BY a.col2; 可以利用程序实现,先SELECT * FROM a WHERE a.col2>10 ORDER BY a.col2;--(1) 利用(1)的结果集,做循环,SELECT * FROM b WHERE b.col1=a.col1; 这样可以避免排序,可以在程序里控制执行的速度,有效降低数据库压力,也可以实现跨主机的JOIN。 《内连接实现例子》 《外连接实现例子》 拆分策略 按 hash 拆分 多维度,如关系 多级索引,如 user_timeline index
  • 46. 应用实现IP存储:INET_ATON(expr),将IP转换为整数。 INET_NTOA(expr),将整数转换为IP。 mysql> SELECT INET_ATON('209.207.224.40'); -> 3520061480 mysql> SELECT INET_NTOA(3520061480); -> '209.207.224.40‘ 判断IP段:INET_ATON('209.207.224.1') <= IP AND IP <= INET_ATON('209.207.224.255'); VARCHAR存储:(3*4+3)+1=16位,UTF8字符集15*3+1=46字节 INT存储:4字节 小心翼翼的使用举例 - rand 的问题 举例:select * from online_user order by rand() limit 100 应该: select * from online_user where page = $rand_page select * from online_user where id > $rand_pos limit 100 select * from online_user + memcached …
  • 47. 计数器COUNT()新定义 聚合函数,两种方式,统计值的数量和统计行数; 值是一个非空的表达式,NOT NULL,在指定列名或是其他表达式的时候隐含就是 NOT NULL; 行数统计, COUNT(*) 或COUNT(任意数字) 错误用法:统计行或值用反了 (*) 和 (col_name); 对于存储引擎: MyISAM:没有 WHERE 条件的 COUNT(*) 非常快; InnoDB:没有 WHERE 条件的 COUNT(*) 非常慢; 有 WHERE 条件下,不要对比MyISAM 和 InnoDB,取决于记录数、索引、条件、数据文件分布等等因素; 但 InnoDB 引擎的 COUNT(*) 确实比较慢。 下面例子有问题,重新测试: mysql> SELECT COUNT(resource_id = 20) is_not_null_20, COUNT(resource_id = 20 OR NULL) AS is_null_20, COUNT(resource_id = 21) is_not_null_21, COUNT(resource_id = 21 OR NULL) AS is_null_21 FROM cfo_ad_stat_src_area; +----------------+------------+----------------+------------+ | is_not_null_20 | is_null_20 | is_not_null_21 | is_null_21 | +----------------+------------+----------------+------------+ | 9965115 | 605818 | 9965115 | 604990 | +----------------+------------+----------------+------------+ 1 row in set (4.88 sec)
  • 48. 计数器COUNT()针对 MyISAM 的 COUNT() 优化: mysql> SELECT SQL_NO_CACHE COUNT(*) FROM cfo_ad_stat_src_area; +----------+ | COUNT(*) | +----------+ | 9965115 | +----------+ 1 row in set (0.00 sec) mysql> SELECT SQL_NO_CACHE COUNT(*) FROM cfo_ad_stat_src_area WHERE id > 10 ; +----------+ | COUNT(*) | +----------+ | 9965105 | +----------+ 1 row in set (4.31 sec) mysql> SELECT SQL_NO_CACHE (SELECT COUNT(*) FROM cfo_ad_stat_src_area) - COUNT(*) AS id_cnt FROM cfo_ad_stat_src_area WHERE id <= 10; +---------+ | id_cnt | +---------+ | 9965105 | +---------+ 1 row in set (0.03 sec)
  • 49. 计数器COUNT()InnoDB 怎么办? 使用计数器表 mysql> SELECT COUNT(*) FROM cfo_ad_stat_src_area WHERE id > 10; +----------+ | COUNT(*) | +----------+ | 9965105 | +----------+ 1 row in set (36.31 sec) CREATE TABLE cnt_tbl ( tbl CHAR(20) NOT NULL PRIMARY KEY COMMENT '表名', cnt INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '计数' ); INSERT: UPDATE cnt_tbl SET cnt = cnt+1 WHERE tbl = 'A'; DELETE:UPDATE cnt_tbl SET cnt = cnt-1 WHERE tbl = 'A'; CREATE TABLE cnt_tbl ( tbl VARCHAR(20) NOT NULL COMMENT '表名', cnt_time DATE NOT NULL DEFAULT '0000-00-00' COMMENT 'WHERE条件', cnt INT NOT NULL DEFAULT 0 COMMENT '计数', PRIMARY KEY (tbl, cnt_time, slt), KEY (time) ); INSERT INTO cnt_tbl(tbl, cnt_time, cnt) VALUES('A', CURRENT_DATE(), 1) ON DUPLICATE KEY UPDATE cnt = cnt+1; INSERT INTO cnt_tbl(tbl, cnt_time, cnt) VALUES('A', CURRENT_DATE(), 1) ON DUPLICATE KEY UPDATE cnt = cnt-1; 转化为 MyISAM 统计 Master InnoDB - Slave MyISAM
  • 50. 计数器COUNT()针对 InnoDB 的优化 走覆盖索引 砍掉多余的索引 其他方案 memcached,redis 快速、准确、简单 只能三选二。
  • 51. 计数器COUNT()实时排名策略优化 背景: 用户提交游戏积分,显示实时排名。 原方案: 提交积分是插入记录,略, select count(*) from jifen where gameid=$gameid and fenshu>$fenshu 问题与挑战 即便索引是 gameid+fenshu 复合索引,涉及count操作,当分数较低时,影响结果集巨大,查询效率缓慢,高峰期会导致连接过多。 优化思路 减少影响结果集,又要取得实时数据,单纯从SQL上考虑,不太有方法。 将游戏积分预定义分成数个积分断点,然后分成积分区间,原始状态,每个区间设置一个统计数字项,初始为0。 每次积分提交时,先确定该分数属于哪两个区间之间,这个操作非常简单,因为区间是预定义的,而且数量很少,只需遍历即可,找到最该分数符合的区间, 该区间的统计数字项(独立字段,可用内存处理,异步回写数据库或文件)+1。 记录该区间上边界数字为$duandian。 SQL: select count(*) from jifen where gameid=$gameid and fenshu>$fenshu and fenshu<$duandian,如果处于第一区间,则无需$duandian,这样因为第一区间本身也是最好的成绩,影响结果集不会很多。 通过该SQL获得其在该区间的名次。 获取前面区间的总数总和。(该数字是直接从上述提到的区间统计数字获取,不需要进行count操作)将区间内名次+前区间的统计数字和,获得总名次。 该方法关键在于,积分区间需要合理定义,保证积分提交成绩能平均散落在不同区间。 如涉及较多其他条件,如日排行,总排行,以及其他独立用户去重等,请按照影响结果集思路自行发挥。 Redis方案 Redis数据结构包括String,list,dict和Zset四种,在本案例中是非常好的替代数据库的方案,本文档只做简介,不做额外扩展。 String 哈希索引,key-value结构,主键查询效率极高,不支持排序,比较查询。 List 队列结构,在数据异步写入处理中可以替代memcache。 Dict 数组结构,存储结构化,序列化内容,可以针对数组中的特定列进行操作。 Zset 有序数组结构,分两个子结构,第一是多层树形的存储结构,第二是每个树形节点的计数器,这样类似于前面的分段方式,可以理解为多层分段方式,所以查询效率更高,缺点是更新效率有所增加。
  • 52. LIMIT分页 传统分页: select * from tb_name limit 10000,10; 性能:偏移量越大则越慢 推荐分页: select * from tb_name WHERE id>=23423 limit 11; #10+1 (每页10条) select * from tb_name WHERE id>=23434 limit 11; 分页方式二: select * from tb_name WHERE id >= (select id from tb_name limit 10000,1) limit 10; 分页方式三: SELECT * FROM tb_name INNER JOIN (SELECT id FROM tb_name LIMIT 10000,10) USING (id); 分页方式四: 程序取ID:select id from tb_name limit 10000, 10; SELECT * from tb_name WHERE id in(123,456…); 可能需按场景分析并重组索引
  • 53. LIMIT分页 排序: SELECT from_id, description FROM tb_post ORDER BY title LIMIT 1000, 5; SELECT post.from_id, post.description FROM INNER JOIN (SELECT from_id FROM tb_post ORDER BY title LIMIT 1000, 5) AS post USING(from_id); 把 LIMIT 转换为位置查询,类似排名计算,预先计算并索引一个表示位置的列: SELECT from_id, description FROM tb_post WHERE pos BETWEEN 1000 AND 1009 ORDER BY pos;
  • 54. LIMIT分页 论坛翻页优化 背景,常见论坛帖子页 SQL: select * from post where tagid=$tagid order by lastpost limit $start, $end 翻页 。索引为 tagid+lastpost 复合索引 挑战, 超级热帖,几万回帖,用户频频翻到末页,limit 25770,30 一个操作下来,影响结果集巨大(25770+30),查询缓慢。 解决方法: 只涉及上下翻页情况 每次查询的时候将该页查询结果中最大的 $lastpost和最小的分别记录为 $minlastpost 和 $maxlastpost ,上翻页查询为 select * from post where tagid=$tagid and lastpost<$minlastpost order by lastpost desc limit 30; 下翻页为 select * from post where tagid=$tagid and lastpost>$maxlastpost order by lastpost limit 30; 使用这种方式,影响结果集只有30条,效率极大提升。 涉及跳转到任意页 互联网上常见的一个优化方案可以这样表述,select * from post where tagid=$tagid and lastpost>=(select lastpost from post where tagid=$tagid order by lastpost limit $start,1) order by lastpost limit 30; 或者 select * from post where pid in (select pid from post where tagid=$tagid order by lastpost limit $start,30); (第2条S语法在新的mysql版本已经不支持,新版本mysql in的子语句不再支持limit条件,但可以分解为两条SQL实现,原理不变,不做赘述) 以上思路在于,子查询的影响结果集仍然是$start +30,但是数据获取的过程(Sending data状态)发生在索引文件中,而不是数据表文件,这样所需要的系统开销就比前一种普通的查询低一个数量级,而主查询的影响结果集只有30条,几乎无开销。但是切记,这里仍然涉及了太多的影响结果集操作。 延伸问题: 来自于uchome典型查询 SELECT * FROM uchome_thread WHERE tagid='73820' ORDER BY displayorder DESC, lastpost DESC LIMIT $start,30; 如果换用 如上方法,上翻页代码 SELECT * FROM uchome_thread WHERE tagid='73820' and lastpost<$minlastpost ORDER BY displayorder DESC,lastpost DESC LIMIT 0,30; 下翻页代码SELECT * FROM uchome_thread WHERE tagid='73820' and lastpost>$maxlastpost ORDER BY displayorder DESC, lastpost ASC LIMIT 0,30; 这里涉及一个order by 索引可用性问题,当order by中 复合索引的字段,一个是ASC,一个是DESC 时,其排序无法在索引中完成。 所以只有上翻页可以正确使用索引,影响结果集为30。下翻页无法在排序中正确使用索引,会命中所有索引内容然后排序,效率低下。
  • 55. 创建自己的HASH索引适用字符串字段完全等值查找; 字符串值比较大,而且频繁查询; 能够极大的提升SELECT的性能; 降低INSERT/UPDATE/DELETE性能; 实现:在标准B-Tree索引上创建一个伪HASH索引,其实还是使用B-Tree索引查找; 使用场景: CREATE TABLE tb_url ( id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, url CHAR(255) NOT NULL DEFAULT '', PRIMARY KEY (id), KEY `url` (url(150)) ) ENGINE MYISAM DEFAULT CHARSET=utf8; SELECT id FROM tb_url WHERE url = 'http://i.jrj.com.cn/main?uid=110920010038776090'
  • 56. 创建自己的HASH索引CREATE TABLE tb_url_crc ( id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, url CHAR(255) NOT NULL DEFAULT '', url_crc INT(11) UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (id), KEY url_crc (url_crc) ) ENGINE MYISAM DEFAULT CHARSET=utf8; SELECT id FROM tb_url_crc WHERE url_crc = CRC32('http://i.jrj.com.cn/main?uid=110920010038776090') AND url = 'http://i.jrj.com.cn/main?uid=110920010038776090' CRC32函数: 循环冗余校验值,有碰撞的 32-bit unsigned 值,正好4个字节。 不要用SHA1()或MD5()这样的强加密哈希函数。 大概100,000值以上开始产生碰撞。碰撞率过高可以使用64-bit的整数函数。
  • 57. (本页无文本内容)