MySQL 开发

MySQL 可以存储文件：

可以适用 BLOB(binar large object), 用来存储二进制大对象的字段类型

• TinyBlob 255 值的长度加上用于记录长度的1个字节(8位)
• Blob 65K 值的长度加上用于记录长度的 2个字节(16位)
• MediumBlob 16M 值的长度加上用于记录长度的 3个字节(24位)
• LongBlob 4G 值的长度加上用于记录长度的 4个字节(32位)

大文件存储条件：

• 存： 需要高效查询并且文件很小的时候
• 不存： 文件比较大，数据量多或变更频繁的时候

存储大文件遇到问题:

• 上传数据过大 SQL 执行失败， 调整max_allowed_packet
• 主从同步数据时比较慢
• 应用线程阻塞
• 占用网络带宽
• 高频访问的图片无法使用浏览器缓存

Emoji 乱码:

使用 utf8mb4, MySQL 在 5.5.3 之后增加了这个 utf8mb4的编码， mb4 就是 most bytes 4的意思，专门用来兼容四字节的 unicode。 好在 utf8mb4 是 utf8 的超集，除了将编码改为 utf8mb4 外不需要做其他转换。当然，一般情况下使用 utf8 也就足够了

如何存储 IP 地址：

1. 使用字符串
2. 使用无符号整型

• 4个字节即可解决问题
• 可以支持返回查询  IPV4 INET_ATON() 和 INET_NT0A , IPV6 使用 INET6_ATON() 和 INET6_NT0A()

大段文本如何设计表结构：

• 将大段文本同时存储到搜索引擎
• 分表存储
• 分表后多段存储

大段文本查找时如何建立索引:

• 全文检索，模糊匹配最好存储到搜索引擎中
• 指定索引长度
• 分段存储后创建索引

char 与 varchar 的区别:

• char 的优点是存储空间固定(最大255)，没有碎片，尤其更新比较频繁的时候，方便数据文件指针的操作，所以存储读取速度快。缺点是空间冗余，对于数据量大的表，非固定长度属性使用 char 字段，空间浪费
• varchar 字段，存储的空间根据存储的内容变化，空间长度为 L + size, 存储内容长度加描述存储存储内容长度信息，优点就是空间节约，缺点就是读取和存储时候，需要读取信息计算下标，才能获取完整内容

decimal、float、double:

• float: 浮点型，4字节，32 bit.
• doule: 双精度实型，8字节，64 bit.
• decimal: 数字型，128 bit, 不存在精度损失

对于声明语法 DECIMAL(M,D), 自变量的值范围如下:

• M 是最大位数(精度)，范围是 1到 65.可不指定，默认值是10
• D 是小数点右边的位数(小数位)。范围是0到30，并且不能大于M，可不指定，默认值是0

浮点数类型如何选型：

• 需要不丢失精度计算使用 DECIMAL
• 仅用于展示没有计算的小数存储可以使用字符串存储
• 低价值数据允许计算后丢失精度可以使用 float double
• 整型记录不会出现小数的不要使用浮点类型

预编译 SQL 有什么好处：

• 预编译 SQL 会被 MySQL 缓存下来
• 作用域是每个 session, 对其他 session 无效，重新连接也会失效
• 提高安全性防止 SQL 注入
• 编译语句有可能被重复调用，也就是说 SQL 相同参数不同在同一 session 中重复查询执行效率明显比较高
• MySQL 以后 支持服务器端的预编译

如何优化过多join 查询关联：

• 适当使用冗余字段减少多表关联查询 
• 驱动表和被驱动表(小表 join 大表)
• 业务允许的话，尽量使用 inner join 让系统帮忙自动选择驱动表
• 关联字段一定创建索引
• 调整 JOIN BUFFER 大小

分库分表实现思路:

• 伪装成 MySQL 服务器，代理用户请求转发到真实服务器
• 基于本地 AOP 实现，拦截 SQL，改写，路由和结果归集处理

分库分表后问题：

• 执行效率明显低
• 表结构很难再次调整
• 引发分布式ID问题
• 产生跨库 Join
• 代理类中间件网络 I/O 成为瓶颈

视图：

视图定义:

• 视图是一个虚表，是从一个或几个基本表(或视图)导出的表
• 只存放视图的定义，不存放视图对应的数据
• 基表中的数据发生变化，从视图中查询出的数据也随之变化

视图作用:

• 视图能够简化用户的操作
• 视图使用户能以多种角度看待同一数据
• 视图对重构数据库提供了一定程度的逻辑独立性
• 视图能够对机密数据提供安全保护
• 适当的利用视图可以更清晰的表达查询

ProcessList:

关键的是 state 列

• Checking table: 正在检查数据表(这是自动的)
• Closing tables: 正在将表中修改的数据刷新到磁盘中, 同时正在关闭已经用完的表。这是一个很快的操作，如果不这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中
• Connect Out: 复制从服务器正在连接主服务器
• Copying to tmp table on disk: 由于临时结果集大于 tmp_table_size, 正在将临时表从内存存储转为磁盘存储以此节省内存
• Creating tmp table: 正在创建临时表以存放部分查询结果
• deleting from main table:  服务器正在执行多表删除中第一部分-刚删除第一个表
• deleting from reference tables: 服务器正在执行多表删除中的第二部分，正在删除其他表的记录
• Flushing tables: 正在执行FLUSH TABLES, 等待其他线程关闭数据表
• Killed： 发送一个 kill 请求给某线程，那么这个线程将会检查 kill 标志位，同时会放弃下一个 kill 请求。 MySQL 会在每次的主循环中检查 kill标志位，不过有些情况下该线程可能会过一段才能杀死，如果该线程被其他线程锁住了，那么 kill 请求会在释放时马上生效。
• Locked： 被其他查询锁住了
• Sending data: 正在处理 Select 查询的记录，同时正在把结果发送给客户端。 Sending data 并不是单纯的发送数据，而且包括 收集 + 发送数据
• Sorting for group: 正在为 Group BY 做排序
• Sorting for order: 正在为 ORDER BY 做排序
• Opening tables: 正尝试打开一个表，这个过程会很快，除非收到其他因素的干扰。例如，在Alter TABLE 或 LOCK TABLE  语句行完之前，数据表无法被其他线程打开。
• Removing duplicates: 正在执行一个 SELECT DISTINCT 方式的查询，但是 MySQL 无法在前一个阶段优化掉哪些重复的记录。因此,MySQL 需要再次去掉重复的记录，然后再把结果发送给客户端
• Reopen table: 获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表
• Repair by sorting: 修复执行正在排序以创建索引
• Repair with keycache: 修复指令正在利用索引缓存一个一个得创建新索引，它会比 Repair by sorting 慢些
• Searching rows for update: 正在讲述符合条件的记录找出来以备更新。它必须在 Update 要修改相关的记录之前就完成了
• Sleeping: 正在等待客户端发送新请求
• System lock: 正在等待取得一个外部的系统锁。如果当前没有运行多个 mysqld 服务器同时请求同一表，那么可以通过增加--skip-external-locking 参数来禁止外部系统锁
• Upgrading lock: Insert DELAYED 正在尝试取得一个锁表以插入新记录
• Updating: 正在搜索匹配的记录，并且修改它们
• User Lock: 正在等待 GET_LOCK()
• Waiting for tables: 该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能重新打开数据表，必须等待所有其他线程关闭这个表。以下几种情况下会产生这个通知：FLUSH TABLES tbl_name, Alter TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, 或 OPTIMIZE TABLE
• waiting for handler insert: Inset DELAYED 已经处理完了所有待处理的插入操作，正在等待新的请求

某表有数千万缓存，查询比较慢，如何优化：

1.前端优化:

• 合并请求：多个请求需要的数据尽量一条SQL拿出来
• 会话保存：和用户会话相关的数据尽量一次取出重复使用
• 避免无效刷新

2.多级缓存：

• 应用层热点数据高速查询缓存(低一致性缓存)
• 高频查询大数据量镜像缓存(双写高一致性缓存)
• 入口层缓存(几乎不变的系统常量)

3.使用合适的字段类型，比如 varchar 换成 char

4.一定要高效使用索引：

• 使用 explain 深入观察索引使用情况
• 检查 select 字段最好满足索引覆盖
• 符合索引注意观察 key_len 索引使用情况
• 有分组，排序，注意 file sort， 合理配置相应的 buffer

5.检查查询是否可以分段查询，避免一次拿出过多无效数据

6.多表关联查询是否可以设置冗余字段，是否可以简化多表查询或分批查询

7.分而治之：把服务拆分更小力度的微服务

8.冷热数据分库存储

读写分离，主备集群

count(列名)和count(*)区别：

• count(*) 会统计Null的值
• count(列) 不会统计 NULL 的值 

超大分页该怎么处理:

select name from user limit 10000, 10; 在使用的时候并不是跳过 offset 行，而是取 offset + N 行，然后返回放弃前 offset行，返回 N 行

通过索引优化的方案：

• 如果主键自增可以 select name from user where id > 10000 limit 10l
• 延迟关联 select a.* from 表1 a, (select id from 表1 where 条件 limit 10000, 20) b where a.id = b.id
• 需要 order by 时

        1. 一定注意增加筛选条件，避免全表排序

        2. 减少 select 字段

        3. 优化相关参数 避免 filesort

• 一般大分页情况比较少，实际互联网业务中多数还是按顺序翻页，可以使用缓存能力提升前几页的查询效率