Mysql #
数据类型 #
- 整型：TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 分别使用 8, 16, 24, 32, 64 位存储空间
- 浮点数：FLOAT 和 DOUBLE 为浮点类型，DECIMAL 为高精度小数类型
- 字符串： CHAR（定长） 和 VARCHAR（变长的）
- 时间日期：date、datetime、timestamp（比DATETIME 空间效率更高）
存储引擎：myisam和innodb的区别是什么 #
答者：狸追
- innodb 支持事务和外键，最小锁粒度是行级锁，myisam 不支持事务和外键，最小锁粒度是表级锁，间歇锁
- innodb 的索引如果是聚簇索引，叶子节点上保存的是数据和索引，非聚簇索引，节点上保存的是id，而myisam保存的是数据的地址（相当于一个指针）
- myisam 的表可以没有索引，innodb一定要有索引
- myisam 会保存总行数，innodb是全表扫描
- 总结：对于大量更新、插入、删除，innodb性能上更好，因为他具备的事务、行级锁、B+树等特点，更安全，因为回滚和崩溃恢复更适合大型应用
- 经过测试在单进程读的情况下myisam执行速度比innodb更快，但是多进程读的时候就失去优势了
- mysql5.5版本之后默认innodb
展开说
- MyISAM：
- 拥有较高的插入，查询速度
- 不支持事务
- 支持表级锁
- 不支持MVCC
- 不支持外键
- 支持全文索引
- 内部维护了一个计数器，selectcount更快
- InnoDB ：插入缓冲（insert buffer)、二次写(double write)、自适应哈希索引(ahi)、预读(read ahead)
- 5.5版本后Mysql的默认数据库
- 支持ACID事务
- 支持行级锁定
- 支持MVCC
- 支持外键
- 不支持全文索引
- 不建议使用过长的字段作为主键：因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。
- 不建议用非单调的字段作为主键：因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。
- 特殊的功能“自适应哈希索引”：当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引（B+Tree 索引具有哈希索引的一些优点）
主从复制 #
- 主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中
- 从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进自己的relay log中
- 从：sql执行线程——执行relay log中的语句
索引类型 #
- 普通索引
- 唯一索引：索引列的值必须唯一，但允许有空值
- 主键索引：特殊的唯一索引，一个表只能有一个主键，不允许有空值
- 组合索引：在查询条件中使用了创建索引时的第一个字段，索引才会被使用。遵循最左前缀集合
- 全文索引：主要用来查找文本中的关键字（MATCH AGAINST）
- Mysql8新特性降序索引
聚集索引（主键索引）和非聚集索引 #
索引按照数据结构来说主要包含B+树和Hash索引。
MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址- 聚簇索引：innodb中，表数据文件本身就是按
B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引，也就是说节点只包含id索引，叶子节点同时保存索引和数据，这种数据和索引在一起的方式就是聚簇索引有主键使用主键，没有主键就用唯一非空索引代替，如果没有会隐式定义一个主键，一张表只能有一个聚簇索引 - 非聚集索引：innodb的非聚集索引的叶子节点上的
data是主键。（为什么存放的主键，而不是记录所在地址呢，理由相当简单，因为记录所在地址并不能保证一定不会变，但主键可以保证）
索引数据结构 #
B+Tree 索引 #
- MySQL 存储引擎的默认索引类型
- BTREE 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问
- B-tree 索引可以用于使用 =, >, >=, <, <= 或者 BETWEEN 运算符的列比较
- B+ Tree 的有序性，可以用于排序和分组
- InnoDB 的 B+Tree 索引分为主索引和辅助索引
- 主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引（一个表只有一个：数据行不能存放个）
- 辅助索引的叶子节点的 data 域记录着主键的值。使用辅助索引进行查找，先查找主键值，再到主索引中进行查找
- MyISAM的 B+Tree 索引分为主索引和辅助索引
- 和InnoDB不同，data域保存数据记录的地址
- 主索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复
Hash 索引 #
- 检索效率非常高，索引的检索可以一次定位
- 仅仅能满足"=",“IN"和”<=>“查询，不能使用范围查询。
- 无法用于排序与分组
- Hash 索引不能利用部分索引键查询。
- Hash 索引在任何时候都不能避免表扫描。
- Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。
索引优化 #
- 独立的列：索引列不能是表达式的一部分，也不能是函数的参数
- 多列索引：在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好
- 索引列的顺序：让选择性（不重复的索引值和记录总数的比值）最强的索引列放在前面
- 前缀索引：对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符
- 覆盖索引：索引包含所有需要查询的字段的值
索引高度有多高 #
事务的实现 #
- 事务的原子性是通过 undo log（回滚日志） 来实现的
- 事务的持久性是通过 redo log（重做日志） 来实现的
- 事务的隔离性是通过 (读写锁+MVCC)来实现的
- 事务的一致性是通过原子性，持久性，隔离性来实现的
AUTOCOMMIT #
MySQL 默认采用自动提交模式。也就是说，如果不显式使用START TRANSACTION语句来开始一个事务，那么每个查询操作都会被当做一个事务并自动提交。
mysql锁技术 #
- 读写锁
- 共享锁(shared lock),又叫做"读锁
- 排他锁(exclusive lock),又叫做"写锁”
MVCC基础 #
MVCC (MultiVersion Concurrency Control) 叫做多版本并发控制
InnoDB的 MVCC ，是通过在每行记录的后面保存两个隐藏的列来实现的。这两个列， 一个保存了行的创建时间，一个保存了行的过期时间， 当然存储的并不是实际的时间值，而是系统版本号。
通过数据多版本来做到读写分离。从而实现不加锁读进而做到读写并行
并发一致性问题 #
- 丢失修改：一个事务的更新操作被另外一个事务的更新操作替换
- 脏读：在不同的事务下，当前事务可以读到另外事务未提交的数据
- 不可重复读：一个事务内多次读取同一数据集合
- 幻读：针对插入语句，一个update，一个insert，update之后发现有未更新数据
事务的隔离级别（级别由低到高） #
- READ UNCOMMITED (未提交读) ：事务中的修改，即使没有提交，对其它事务也是可见的（读的过程中写，脏读，读写并行）
- READ COMMITED (提交读)：一个事务只能读取已经提交的事务所做的修改（排它锁，读写分离机制，产生不可重读以及幻读问题）
- REPEATABLE READ (可重复读)（默认级别）：保证在同一个事务中多次读取同一数据的结果是一样的（读写锁实现orMVCC实现）
- SERIALIZABLE (串行)
查询性能优化 #
- 使用 Explain分析SELECT语句
- select_type : 查询类型，有简单查询、联合查询、子查询等
- key : 使用的索引
- rows : 扫描的行数
- 优化数据访问
- 减少请求的数据量
- 只返回必要的列：最好不要使用 SELECT * 语句。
- 只返回必要的行：使用 LIMIT 语句来限制返回的数据。
- 缓存重复查询的数据
- 减少服务器端扫描的行数：索引
- 减少请求的数据量
- 重构查询方式
- 切分大查询
- 分解大连接查询：将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联
高并发架构 #
关键字：分布式、高可用、集群、负载均衡、正/反代理
演进 #
- 应用与数据库分离：增加服务器资源，提高性能
- 本地缓存和分布式缓存：使用memcached作为本地缓存，使用Redis作为分布式缓存，减小数据库的压力
- 反向代理（Nginx）实现负载均衡
- 数据库的读写分离
- 数据分库（按业务）
- 把大表拆分为小表
- 使用LVS或F5来使多个Nginx负载均衡(四层的负载均衡解决方案)
- 通过DNS轮询实现机房间的负载均衡
- 引入NoSQL数据库和搜索引擎等技术
- 大应用拆分为小应用
- 复用的功能抽离成微服务
- 引入企业服务总线ESB屏蔽服务接口的访问差异
- 引入容器化技术实现运行环境隔离与动态服务管理
- 以云平台承载系统
数据库分库分表 #
为什么要分库分表？ #
- 数据量达到几千万时查询时间变多，无论表级锁还是行级锁，联合查询大概率阻塞严重(索引膨胀、查询超时)
- 对于大表要进行表结构DDL几乎不可能
- 从Innodb本身来讲数据文件的Btree上只有两个锁, 叶子节点锁和子节点锁, 可以想而知道, 当发生页拆分或是添加新叶时都会造成表里不能写入数据
方式 #
- 垂直分表（大表拆小表）：将不经常使用或者长度较大的字段拆分出去放到“扩展表”中（设计阶段考虑）
- 垂直分库：按照业务模块来划分出不同的数据库
- 水平分表（横向分表）：降低单表数据量，优化查询性能（主键hash或取模）
- 水平分库分表：水平分表+分库
多久分一个表 #
- 大数据量并且访问频繁的表，应该拆分成多个表
- 经测试在单表1000万条记录以下，写入读取性能是比较好的，再留点buffer, 那么单表全是数据字型的保持在800万条记录以下, 有字符型的单表保持在500万以下。
- 最好是提前规划预估表数据量，方便做分页查询，减少数据清洗
多久分一个库？ #
- 单台数据库服务器存储空间、cpu、内存、网络io等因素无法支撑，做水平拆分（分库）
用什么做分库分表？ #
使用mycat做分库分表，现在金融级别的分布式数据库可以考虑使用tidb，对水平伸缩和数据一致性有保证
分表策略（水平拆分） #
- 查询切分（不推荐），把ID和库的Mapping关系记录在一个单独的库中，又会引入新的单点压力问题
- 范围切分，按照id范围切分，单表大小可控，但集中写入会频繁操作单表
- Hash切分，易于水平扩展, 例如：mod 32, 拆分32个库，每个库 div 32 mod 32 拆分32张表，就是
32*32=1024张表，更多参考(32*2^n)*(32⁄2^n) - 其他业务相关：地理位置、时间
产生的问题和解决思路 #
- 垂直分库->跨库Join
- 全局表
- 字段冗余（数据一致性的问题）
- 数据同步
- 系统层组装（把不同的表放在不同的库，不同的库放在不同服务器上，但是联合查询无法从数据库层面做到）
- 水平分库
- 分布式全局唯一ID
- 分片规则
- 随机分片和连续分片
- 数据迁移，容量规划，扩容等问题
- 跨分片的排序分页（分别排序之后汇总再排序）
- 跨分片的函数处理
- 夸分片join
怎么跨表查询，还有排序和翻页怎么做的？ #
issue
分库分表有什么缺点？怎么解决？ #
- 联合查询困难，关联的表可能不在同一数据库中
- 避免在同一个事务中修改不同库中的表，操作复杂，效率也会有影响
- 尽量把同一数据放在同一db服务器上，单点故障时不会影响其他数据
select count(*) conunt(1)和count(字段)执行的效率有何不同？ #
- count(primary key)。遍历整个表，把主键值拿出来，累加；
- count(1)。遍历整个表，但是不取值，累加；
- count(非空字段)。遍历整个表，读出这个字段，累加；
- count(可以为空的字段)。遍历整个表，读出这个字段，判断不为null累加；
- count(*)。遍历整个表，做了优化，不取值，累加。
来自 胡慢慢滚雪球
什么是乐观锁和悲观锁 #
通常mysql中使用的都是悲观锁，分为共享锁和排他锁两种，共享锁也就是读锁，可以多个线程同时读，不能修改；排他锁是写锁，未获得锁的线程需要阻塞
乐观锁：假设数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则返回给用户错误的信息，让用户决定如何去做。乐观锁适用于读操作多的场景，这样可以提高程序的吞吐量。一般有两种实现方式CAS和基于版本号
引用： 什么是乐观锁，什么是悲观锁
CAS与ABA问题 #
CAS是乐观锁的实现方式之一，CAS操作包含三个操作数—— 内存位置的值（V）、预期原值（A）和新值（B），在更新的时候做检查，内存值必须与期望值相同。举个例子，内存值V、期望值A、更新值B，当V == A的时候将V更新为B。
ABA问题，某个值中间被改动又被改回来，A-B-A，用CAS是无法识别的，考虑通过控制变量值的版本号来保证CAS的正确性。具体解决思路就是在变量前追加上版本号，每次变量更新的时候把版本号加一，那么A - B - A就会变成1A - 2B - 3A。
引用： 深入理解CAS
最后 #
如果文中有误，欢迎提pr或者issue，一旦合并或采纳作为贡献奖励可以联系我直接无门槛加入 技术交流群
我是小熊，关注我，知道更多不知道的技术
