> [关于数据的思考(百度脑图)](http://naotu.baidu.com/file/ace201847fdb8e85369a5424bec08ad4) > [叶金荣 - 专注 mysql 技术](http://imysql.com) > date: 2016-03-22 19:49 ## 书籍 & 教程推荐 - [高性能mysql](book/high_performance_mysql.md) - [mysql alter 语句用法,添加、修改、删除字段等](http://blog.csdn.net/wyswlp/article/details/8881103) - [mysql 主从复制](http://369369.blog.51cto.com/319630/790921/) - [mysql按字段取最大值记录](http://blog.sina.com.cn/s/blog_8155e74d0101g1pl.html) - [分组取前N条](http://www.cnblogs.com/netserver/p/4518995.html) - [mysql 行列转换](http://www.cnblogs.com/emanlee/p/3550149.html) - [mysql 允许外网访问](http://www.cnblogs.com/live41/archive/2013/04/02/2995178.html):其实就是修改 `mysql.user` 表的 host 字段 - [百度百科: 数据库死锁](http://baike.baidu.com/link?url=SBUAHTyBkX-OBvlIFU6MCTjShbAWb8r1h96z0CJMAk9M5AsQEB-pe6XIk6bty4ZqJuHDZiRBd_qK3KH9D6N0oa):基础讲解, 理论知识 - [csdn: 数据库死锁的解决办法](http://www.cnblogs.com/jacklondon/archive/2012/03/21/2409666.html):感觉作者经验很丰富, 其中的表的设计相关的尤其赞成 - [mysql性能管理&架构设计](https://segmentfault.com/a/1190000013672421) - [数据库并发控制](https://mp.weixin.qq.com/s?__biz=MzI0NzAxMTgxNg==&mid=2456160597&idx=1&sn=76f27b632a17830dd1c27dea260f5efe) - 云数据库最佳实践 https://solution.cloudcare.cn/?p=285 - https://mp.weixin.qq.com/s/EjZObG9aWRhPIZ9H5KhrFw ## 工具 - mysql cli - [mycli - 用来替代 mysql client cli 的选择](http://mycli.net/):代码补全、自动提示 - Navicat:window 下,比 workbench 好用 ### Navicat 需要掌握的技能 - 新建 mysql 连接的时候,设置使用 ssh 跳板 - 新建 mysql 数据库,设置 utf8 字符集、排序规则 - 新建、修改 表结构 - 工具 - 数据传输、数据同步、结构同步 - 导入向导、导出向导(推荐导出为 xls,csv 长数字显示问题) - 导入导出连接 Navicat 快捷键: - C-q:新建查询窗口 - C-w:关闭窗口 - f6:新建命令行 - C-r:运行 - C-S-r:运行选中 - f2:查看单元格 ## mysql delete 磁盘容量没有减少 -> OPTIMIZE TABLE innoDB 锁表 一个月一次 mysql: prepare/execute 减少每次执行SQL的语法分析, 只需要每次修改变量值, 同样可以防止SQL注入 ``` /* */ # 块注释 -- # 行注释 mysql -h127.0.0.1 -uroot -proot -p 2207 -D test # host user passwd port Database # 修改密码 mysqladmin -uroot -p password "123456" # change password tail -f /var/log/mysql/error.log vim /etc/mysql/my.cnf # bind-address chown -R mysql:mysql /var/run/mysqld service mysql stop mysqld_safe --skill-grant-tables UPDATE mysql.`user` set `Password`= PASSWORD('shiyanlou') WHERE `User`='root'; FLUSH PRIVILEGES; # basic SHOW CREATE TABLE xx; # 表结构 DESC xx; CREATE TABLE yy LIKE xx; # 这样才能保持 表结构 + 索引 INSERT yy SELECT * FROM xx ORDER BY col_xx; # 多进程插入时防死锁,主要 ORDER BY 字段要唯一,不然还是可能死锁 ALTER TABLE xx ADD COLUMN xx_col bigint(20) NOT NULL DEFAULT 0 COMMENT 'xx' AFTER yy_col; ALTER TABLE xx MODIFY COLUMN xx_col bigint(20) NOT NULL DEFAULT 0 COMMENT 'xx' AFTER yy_col; ALTER TABLE xx DROP COLUMN xx_col; ALTER TABLE xx RENAME TO yy; # advanced INSERT IGNORE xx ON DUPLICATE KEY UPDATE # INSERT 要注意主键;IGNORE 重复时忽略;on duplicate key update 重复时更新; REPLACE 先删除后更新 SHOW PROCESSLIST; # 查看连接到 mysql 的进程 KILL id # kill process, 推荐使用 pt-kill select concat('KILL ',id,';') from information_schema.processlist where user='root'; EXPLAIN sql # sql 分析 show index from tbl analyze/checksum/optimize/check/repair table tbl # function concat() # 其中一个为 null, 那么结果就是 null;拼接非字符串时, 需要配合 convert(num, char) left() substring() if(col1=1,col1,0) as int_1 # 行列转换 FROM_UNIXTIME() UNIX_TIMESTAMP() DATE_FORMAT() NOW() # 自定义函数 CREATE FUNCTION vipLevel (amount INT) RETURNS int(10) BEGIN DECLARE vip int(10); IF amount >=1000 THEN SET vip=2; ELSEIF amount>=600 THEN SET vip=1; ELSE SET vip=0; END IF; RETURN vip; END; -- unicode 转 utf-8:https://gist.github.com/joni/2956080 DROP FUNCTION STRINGDECODE; CREATE FUNCTION STRINGDECODE(str TEXT CHARSET utf8) RETURNS text CHARSET utf8 DETERMINISTIC BEGIN declare pos int; declare escape char(6) charset utf8; declare unescape char(3) charset utf8; set pos = locate('\u', str); while pos > 0 do set escape = substring(str, pos, 5); set unescape = char(conv(substring(escape,2),16,10) using ucs2); set str = replace(str, escape, unescape); set pos = locate('\u', str, pos+1); end while; return str; END; SELECT STRINGDECODE("\u9648\u5fd7\u6797") # 导入、导出 mysql -uroot -proot < /path/to/xxx.sql # 导入数据 mysql> source /path/to/xxx.sql mysqldump /*login*/ database > xxx.sql # 导出 mysqldump /*login*/ database < xxx.sql # 导入 # mysql 允许外网访问 bind-address # 修改 my.cnf update user set host='%' where user='root' # 修改 mysql.user 表 GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.102' IDENTIFIED BY 'mypwd' WITH GRANT OPTION # 设定特定的连接 flush privileges # 如果不执行这句, 需要重启 mysqld # mysql limit,offset: http://blog.csdn.net/yejr/article/details/70039403 select * from test where val=4 limit 300000,5; select * from test a inner join (select id from test where val=4 limit 300000,5) b on a.id=b.id; ``` ## mysql 的日常 - 如果有自增 id,多考虑使用自增 id,比如使用 id 查询最后几条数据 - 增删改应要当心,切记是否需要备份;能不删除尽量不删除,有种做法是表中添加 `soft_delete` 字段标识记录是否删除 - 交换2列的值: join 一下自己 - `tinyint(1)`:表示一个字节, 数据范围是 0-255(`unsigned`), 所以用来表示数据类型完全够用, 基本不太可能会出现超出 255 种类型的 - 枚举类型: 缺点很明显, 添加新字段的时候需要修改表结构, 所以建议使用 `varchar()` 来替换, 当然如果程序写的不好, 添加新的类型的时候, 还是会很麻烦; 忽略大小写; 输入不在范围的值默认使用第一个 - 如果数字的小数位数是固定的,可以考虑放大后使用 int 来存储,比如金额精确到分以元为单位就是固定 2 位小数 - `signed`: 需要计算,并且结果可能为负数,大部分场景都是用 `unsigned` - sql执行时只能使用一个索引 - 索引最左原则: 只有最左边的元素出现在查询中, 索引才会被用到, 所以应该把 where 中使用最多的一列放到最左边 - **union all/or/in 的效率分析**: 有索引时union all相对会快一点, 取决于索引查询的时间, 没有时都是全表扫描, all/or 要快一些 - 减少需要执行 sql 的数量, 尽量一次取出尽可能多的数据 - like / regexp 效率: like 在左端不固定的场景下, 不会使用到索引, 所以我喜欢使用 **regexp** - orderby / groupby: sql 慢的时候考虑先 排序/分组, 再 join - 优化:1. 减少sql数量, 一个sql取得多个需要的数据;2. 索引是否得当(explain 走起) - 先修改 DB,再更新代码 - 修改线上库:确认是否需要备份、至少审核一遍 sql、本地可以测试一定要本地测试一遍 - 插入相同数据到数据库中: 加锁 / unique index ### 统计相关 - 需要合并多列数据到一行时,可以考虑 `设置字段默认值 + unique key + insert + insert on duplicate update` - mysql 按照 日、周、月 统计,周的周期从 周五-周四 group by DATE_FORMAT(DATE_ADD(created_time,INTERVAL 3 DAY),'%Y%u') -> 注意偏移量 - 按照人来去重,比如一个人下了 5 单,一单支付成功,转化率就是 100%,需要使用 case-when 将用户路径量化,然后 分组取最大值 # DBA review > 知数堂-公开课18: SQL review > date: 2016-07-30 18:08 why: 加强sql及各方面的规范, 让系统能跑得更稳定 表字段/索引设计优化: 字段类型 注释标准度 分区表约束 sql编写规范: DML编写规范 子查询约束 函数使用 ## schema review 目标: 功能实现为主 节省资源(全是varchar) 平衡业务/技术各个方面(取舍) 让数据库做自己擅长的事: 不要在DB里计算 减少复杂操作 字段数: <=20~50 数据评估: int<=1000w char<=800w 非核心表另议 反范式设计: 适当冗余, 减少join 核心表: 尽可能精简 日志表: 水平分表 InnoDB vs TokuDB(log表) ### 字段 主键: InnoDB 使用主键排序存储IOT表, 尽量使用短/自增 列作为索引 int/bigint; uuid_short() 提到 uuid() tinyint 做大表主键可能 mysql crashed; 类型转换导致查询效率低 Emoji 使用 utf8mb4 字符 -> 数字, ip `inet_aton()/inet_ntoa()` 日期 -> 数字, `from_unixtime()/unix_timestamp()` null/not null 有什么坑: `default not null default ''`, null 会导致索引失效, not null 会导致无插入值时报错 案例: bigint 比 int 多 40% 存储 ### 索引 所有表必须有显示主键 视使用情况给其他字段添加索引: 好处&坏处; 减少对索引列更新 Innodb 普通索引存储后会包含主键 复合索引注意排序问题 explain 去确认 ### 工具 pt-mysql-summary: 指定db分析 pt-duplicate-key-checker: 指定DB, 查看重复索引 慢日志 ## sql review 避免线上系统出现大操作, 比如一次操作 10w 行 全面使用索引 优化join 去除无意义逻辑 避免 `select *`, 方便调整字段顺序, 避免不要的 I/O 读 insert 要对字段写入 整个sql要用explain确认 ### 注意where条件 除了select, 没有where条件的可以直接拒掉, 避免全表操作导致的故障 where 条件/区别度高 字段 -> 索引 like -> 不要使用 % 开头的查询 子查询的sql, 要注意mysql 版本, 一定要 explain 确认是否可以用到索引 ### 去除无意义的操作 很多sql是生成的, 如: ibatis, hibernate 其他框架生成的sql 复杂类的sql中无意义逻辑去除 不必要的括号也可以去除, 如: 子查询 where in -> join ### 优化join 控制层数<=3, 建议2个以下 小表驱动大表 控制join后面where条件选择的行数<=1000 union all 代替 union: 有其他更快的方式过滤重复数据 减少临时表出现 ### 避免线上大操作 分批多次操作 大事务 -> 多个 小事务 频繁查询 -> cache text/blob -> 适当拆分 ## 线上 schema 分析技巧 了解每个表的大小, 每天执行一次, 方便历史分析 ```mysql select table_name, engine, concat(round(table_rows/1000000,2), ‘M’) rows, concat(round(data_length/1024/1024/1024),2), ‘G’) data, concat(round(index_length/1024/1024/1024),2), ‘G’) idx, concat(round((data_length+index_length)/1024/1024/1024,2),’G’) total_size, round(index_length/data_length,2) idx frac from information_schema.tables where table_schema in (‘wubx’) group by table_name, table_schema order by data_length+index_length desc ; ``` mysql5.7 sys ## 线上 sql 分析技巧 pt-query-diget: 慢日志分析 box/Anemometer: 慢日志分析, 有web端查看 pt-kill: 干掉执行炒作50s的sql, 避免数据库被hang住 脚本迁移: 渠道日志 已迁移 数据分析-日报 待迁移 索引修改: 优化: 查询数据量太大的, 需要分页 循环查询数据时, 可以记录下 id 下次查询时使用