MySQL

易忽略点

• MySQL中数值类型指定长度，不影响存储大小

1.数值类型长度和范围是两码事，不能混为一谈
2.MySQL中数值的范围是根据位确定的如int([(-2^31)~(2^31)-1] [其中有0] 最高位为符号位)
3.在创建表的过程中可以根据这个数值的方位来选择类型tinyint[-128~127]、smallint[(-2^15)~(2^15)-1]、mediumint、int[(-2^31)~(2^31)-1]、bigint[(-2^63)~(2^63)-1]等

常用函数

• 字符串拼接

-- 连接两个字符串的内容
concat(str1,str2)
-- 使用连接符来连接字符串 中间带有连接符
concat_ws("连接符",str1,str2)

参考SQL

sql编写规范

• 禁止使用属性隐式转换

-- 会导致全表扫描，而不能命中phone索引
-- 全表扫描
select uid from t_user where phone = 13812345678;
-- 会命中索引
select uid from t_user where phone = '13812345678';

• 禁止在where条件的属性上使用函数或者表达式

-- 导致全表扫描
select uid from t_user where from_unixtime(day)>='2017-02-15';
-- 修改为
select uid from t_user where day>= unix_timestamp('2017-02-15 00:00:00’)

• 没有查询条件的时候count(*)会很快,[没有条件时利用存储引擎的特性直接获取该值]

1. count(主键id) 从引擎返回id会涉及解析数据行,以及拷贝字段值的操作

2. count(可控字段) 扫描全表,判断子弹可空,拿出该字段所有值,判断每一个值是否为空不空则累加

3. 性能对比 count(*)≈count(1)>count(主键id)=count(非空字段)>count(可空字段)

• 利用索引特性总数减去少的数据

-- 需要结合index使用 例如 获取小于指定日期的数据量
select((select count(*) from word.city) - count(*)) as total from world.city where id <=5;

mysql耗时调试

https://blog.csdn.net/xiao__jia__jia/article/details/79427058

select sum(if(color = ‘blue’,1,0) as blue ),sum (if(color=‘red’,1,0) as red) from items;

非精确数值可以用explain 预执行【并不会真真的执行】
using等价于on 【两张表的字段必须相同】

优化查询

• 确保on或者using字句中的列上有索引。在创建索引的时候要考虑到关联的顺序。当表A和表B用列c关联的时候,如果优化器的关联顺序是B、A,那么就不需要在B表的对应列上建上索引。没有用到的索引只会带来额外的负担。一般来说,除非有其他理由,否则只需要在关联顺序中的第二个表的相应列上创建索引

• 确保任何的group by 和order by中的表达式只涉及到一个表中列,这样MySQL才有可能使用索引来优化这个过程

• 当升级MySQL的时候需要注意：关联语法、运算符优先级等其他可能会发生变化的地方。因为以前是普通关联的地方可能会变成笛卡尔积，不同类型的关联可能会生成不同的结果等

1. 优化group by 和order by
2. 优化limit分页

-- 覆盖索引的方式
select * from user a join (select id from user where sex = 1 limit 100, 10) b on a.id=b.id;

-- 条件过滤 借助偏移量 且uid列必须有索引 而且有序
select * from user where uid >=( select uid from user order by uid limit 10000,1 ) limit 10;

• 延迟查询

-- 原
select film_id,description from sakila.film order by title limit 50,5;
-- 优化 {film_id上有索引}
select film.film_id,film.description from sakila.film inner join( select film_id from sakila.film order by title limit 50,5) as lim using(film_id);
-- 他让MySQL扫描尽可能少的页面，获取需要访问的记录再根据关联列回原表查询需要的所有列

• union注意点

1. union将2调或者多条sql语句的查询结果,合并成一个结果集[默认去除重复行]

2. {sql1 n行 } {sql2 m 行} 【sql1 union sql2 ——> n+m行(总数小于或等于n+m的数量)】

   union all (不会去除重复行)

• 用户自定义变量

1. 使用自定义变量查询,无法使用查询缓存
2. 不能在使用常量或者标识符的地方使用自定义变量,列如表明、列名、limit字句中
3. 用户自定义变乱给的生命周期是在一个连接中有效,所以不能用它们做连接的通信

设计表规范

相关规范

• 必须使用InnoDB存储引擎

• 表字符集默认使用utf8(3字节，存储4字节会0xffff)万国码,无需转码,节省空间,必要时候使用utf8mb4,但是一般都能容忍多消耗一些空间使用utf8mb4

• 禁止使用存储过程、视图、触发器、Event高并发大数据的互联网业务，架构设计思路是"解放数据库CPU,将计算转移到服务层",并发量打的情况下，这些功能很可能将数据库拖死,业务逻辑放到服务层具备更好的扩展性，能够轻易实现"增加机器加性能"。数据库擅长存储与索引,CPU计算上移

• 禁止存储大文件或者大照片,大文件和照片存储在文件系统(oss)

• 控制单表数据量,单表记录控制在千万级

• 平很范式与冗余,为提高效率可以牺牲范式设计,冗余数据

命名规范

• 表名t_xxx

• 非唯一索引 ids_xxx

• 唯一索引 uniq_xxx

表设计规范

• 单表实例个数必须控制在2000个以内[表数量过多会影响性能类似一个目录下文件数量过多]

• 单表列数必须小于30 [同上]

• 表必须有主键,例如自增主键,推荐使用unsigned整数为主键

1. 主键递增,数据行写入可以提高性能,可以避免page分裂,减少表碎片,提升空间和内存的使用

2. 主键要选择较短的数据类型,InnoDB引擎普通索引都会保存主键的值,较短的类型可以有效的减少索引的磁盘空间,提高索引的缓存效率

3. 无主键的表删除,在row模式的主从架构,会导致备份库夯住

• 禁止外键 [在代码里面进行控制]

• 默认将大字段，访问频率低的字段拆分到单独的表中存储，分离冷热数据

字段设计规范

• 必须把字段定义为not null 并且提供默认值

1. null的列使用索引/索引统计/值比较都更加复杂,对MySQL来说更难优化

2. null这种类型MySQL内部需要进行特殊处理,增加数据库的处理性能会降低很多

3. null值需要更多的存储空间,无论是表还是索引中每行中的null都需要额外的空间标识

4. 对null的处理,只能采用 is null 或 is not null,而不能采用 = 、in、<、<>、!=、not in 这些操作符号。如 where name != 'shanghai',如果存在name为null的记录,查询结果就不会包含name为null值得记录

• 禁止使用小数存储货币

  使用整数,小数容易导致钱对不上

• 必须使用varchar(20)存储手机号

  设计到区号或者国家代号,可能出现+-()

• 根据业务区分使用datetime/timestamp

1. 前者占5个字节，后者站4个字节

2. timestamp存储大于[2038-01-19 03:14:07]会产生异常

3. timestamp可以无视时区问题。数据库实际上会保存 UTC 时间戳，写入的时候先按 Session 时区转成 UTC 时间，读出的时候再按 Session 时区转成当前时区的时间，这些转换都是透明的

索引设计规范

• 单表索引建议控制在5个以内

1. 互联网高并发业务,太多索引会影响性能

2. 生成执行计划时,如果索引太多,将降低性能,并可能导致MySQL选择不到最优索引

• 异常复杂的查询需求，可以选择ES等更为合适的方式存储

• 建立组合索引,必须把区分度高的字段放在前面,能够有效的过滤数据

• 非必要不要进行join查询,如果要进行join查询,被查询的字段必须类型相同,并建立索引

• 理解组合索引最左前缀原则,避免重复建立索引,如果建立(a,b,c),相当于建立了(a),(a,b),(a,b,c)

扩展信息

MySQL查询缓存开关场景

• 判断是否命中缓存

1. 缓存存放在一张引用表中,通过一个哈希值引用,这个哈希值包括了如下因素,即查询本身、当前要查询的数据库、客户端协议版本等一些其他可能会影响返回结果的信息
2. 任何字符不同,例如空格、注释的不同都会导致缓存的不命中[建议统一编码]

• 打开查询缓存对读和写操作都会带来额外的消耗

1. 读查询在开始之前必须先检查是否命中当前缓存
2. 如果这个读查询可以被缓存,那么当前完成执行后,MySQL若发现查询缓存中没有这个查询,会将其结果存入查询缓存中,这个会带来额外的系统消耗
3. 这个写操作也会有影响,因为像某个表写入数据的时候,MySQL必须将对应表的所有缓存都设置失效。如果查询缓存非常大或者碎片很多,这个操作就可能会带来很大系统消耗(设置了很多的内存给查询缓存用的时候)
4. 查询缓存操作是一个加锁排它操作,这个消耗很大
5. InnoDB的多版本特性会暂时将这个修改对其他事务屏蔽。在这个事务提交之前,这个表的相关查询是无法被缓存的,所以所有这个表上面的查询(内部和外部)的事务都只能在该事务提交后被缓存。因此,长时间运行的事务,会大大降低查询缓存的命中率
6. 查询缓存内容过大的时候,缓存失效操作就可能成为非常严重的问题

• 查询缓存是如何使用内存的

1. 查询缓存是完全存储在内存中的
2. 在收缩第一个查询结果使用缓存空间时,就会在第二个查询结果之间留下一个"空隙"——一个非常小的空闲空间我们称为'碎片'这在内存管理、文件系统管理上都是经典问题。有很多情况都会导致碎片,列如缓存失效时,可能导致留下太小的数据块无法在后续缓存中使用
3. 对于那些需要消耗大量资源的查询通常都是非常适合缓存的。例如一些汇总计算查询,如count()等。对复杂的select语句都可以使用查询缓存,例如多表的join后需要做排序

• 适合分区的场景和原因

1. 表非常大以至于无法全部都放在内存中,或者只在表的最后部分有点数据,其他均是历史数据
2. 分区表的数据更容易维护。例如,想批量删除大量数据可以使用清除整个分区的方式。另外,还可以对一个独立分区进行优化、检查、修复等操作
3. 分区表的数据还可以分布在不同的物理设备上,从而高效地利用多个硬件设备
4. 可以使用分区表来避免某些特殊瓶颈,例如InnoDB的单个索引互斥访问、ext3文件系统的inode锁竞争等
5. 如果需要,还可以备份和回复独立分区,这在非常大的数据集的场景下效果非常好

• 注意点

1. 一个表最多只能有2014个分区
2. 可以用列来进行分区
3. 如果分区字段中有主键或者唯一索引的列,那么所有主键列都必须包含进来

• 数据库复制

1. 基于行复制
2. 基于语句额复制

• Explain

id列	标号,标识select所属的行
select_type列	标识是简单还是复杂查询 Simple值意味查询 不包含子查询和union 查询有任何复杂的子部份，则最外层标记为Primary,其他标记为Subquery
type列	MySQL决定采用那种方式查询数据[地到高] ALL 全表扫描 index 避免了排序(类似全表扫描) range 限制的索引扫描 ref 索引访问 (索引查找)

数据库高可用特性

• 双主同步方式保证高可用

存在数据一致性问题 auto-increment-id

a.一个是双主使用不同的初始值,相同的步长来生成id,一个库从0开始一个从1开始,步长都为2
b.另一个方式是不适用数据库的auto-increment-id,而又业务层保证生成的id不冲突

读性能

1. 增加从库,从库越多,写的性能越慢,同步的时间越长,不一致的可能性越高
2. 增加缓存

传统的cache玩法在一种异常时许下,会英法严重的一致性问题
1.先来了一个写请求,淘汰了cache,写了数据库
2.又来了一个写请求,读了cache,chache miss了,然后读从库,此时写请求还没有同步到从库于是读了一个脏数据,接着脏数据入缓存
3.最后主从同步完成
这时时序会导致脏数据一直在缓存中没有办法被淘汰掉,数据库和缓存中的数据严重不一致

解决方案

1. 引入中间件

业务层不直接访问数据库,而是通过中间件访问数据库,这个中间件会记录拿一些key发生了写请求,在数据主从同步时间窗口之内,如果key上有出了读请求,九江这个请求也路由到主库上去

2. 数据库和缓存的不一致

缓存双淘汰

为所有item设定超时时间