Skip to content

Latest commit

 

History

History
272 lines (171 loc) · 8.24 KB

nav.md

File metadata and controls

272 lines (171 loc) · 8.24 KB
Raw

MYSQL

索引

B+ Tree原理

所有叶子节点位于同一层

批注 2020-03-10 192507

操作

  • 查找

首先在根节点进行二分查找，找到一个key的指针，接下来递归地不断向其非叶子节点查找，到了叶子节点，再进行二分查找，找出key所对应的data

  • 修改操作会破坏平衡性，所以修改之后会进行分裂、合并、旋转

vs红黑树

  • 红黑树的出度为2，B树的出度要大很多，所以B树的查找次数更少
  • B+ Tree能更好地利用磁盘的预读特性

MYSQL索引

B+ Tree索引

  • 是大多数 MySQL 存储引擎的默认索引类型
  • 除了用于查找，还可以用于排序和分组
  • 适用于全键值、键值范围和键前缀查找

哈希索引

  • 无法用于排序与分组
  • 只支持精确查找，无法用于部分查找和范围查找

全文索引

  • MyISAM 存储引擎支持
  • 用于查找文本中的关键词
  • 查找条件使用 MATCH AGAINST
  • 使用倒排索引

空间数据索引

间数据索引（R-Tree），可以用于地理数据存储

索引优化

独立的列

进行查询时，索引列不能是表达式的一部分，也不能是函数的参数

SELECT a FROM B WHERE a+3 = 6; -- a不能作为索引

多列索引

多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好

索引列的顺序

让选择性最强的索引列放在前面

一个列比另外一个列更越能确定一条数据，则前者选择性更强

前缀索引

BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符

覆盖索引

索引包含所有需要查询的字段的值

  • 只读取索引能大大减少数据访问量
  • 一些存储引擎只缓存索引

索引的优点

  • 减少了服务器需要扫描的数据行数
  • 帮助服务器避免进行排序和分组，以及避免创建临时表
  • 将随机 I/O 变为顺序 I/O

索引使用条件

  • 小表全表扫描效率优于索引
  • 索引适合中大型表
  • 特大型表，建立和维护索引的代价将会随之增长

存储引擎

MyISAM存储引擎

1、大文件（达到63位文件长度）在支持大文件的文件系统和操作系统上被支持。 2、当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块自动完成。 3、每个MyISAM表最大索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16 4、NULL被允许在索引的列中，这个值占每个键的0~1个字节 5、可以把数据文件和索引文件放在不同目录（InnoDB是放在一个目录里面的）

InnoDB存储引擎

MySQL 默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎

1、InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来，甚至在同一个查询中也可以混合 2、InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的 3、InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘文件）。这与MyISAM表不同，比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上 4、InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键

MEMORY存储引擎

1、MEMORY表的每个表可以有多达32个索引，每个索引16列，以及500字节的最大键长度 2、MEMORY存储引擎执行HASH和BTREE缩影 3、可以在一个MEMORY表中有非唯一键值 4、MEMORY表使用一个固定的记录长度格式 5、MEMORY不支持BLOB或TEXT列 6、MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引 7、MEMORY表在所由客户端之间共享（就像其他任何非TEMPORARY表） 8、MEMORY表内存被存储在内存中，内存是MEMORY表和服务器在查询处理时的空闲中，创建的内部表共享 9、当不再需要MEMORY表的内容时，要释放被MEMORY表使用的内存，应该执行DELETE FROM或TRUNCATE TABLE，或者删除整个表（使用DROP TABLE）

对比

功能 MYISAM Memory InnoDB Archive
存储限制 256TB RAM 64TB None
支持事务 No No Yes No
支持全文索引 Yes No No No
支持数索引 Yes Yes Yes No
支持哈希索引 No Yes No No
支持数据缓存 No N/A Yes No
支持外键 No No Yes No

数据类型

整型

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 分别使用 8, 16, 24, 32, 64 位存储空间，一般情况下越小的列越好

浮点数

FLOAT 和 DOUBLE 为浮点类型，DECIMAL 为高精度小数类型，DECIMAL 的计算比浮点类型需要更高的代价

字符串

一种是定长的，一种是变长的。 变长类型能够节省空间，因为只需要存储必要的内容，但当变长类型发生UPDATE操作后，需要执行额外的操作 存储和检索时，VARCHAR 末尾的空格会保留下来，而会 CHAR 末尾的空格会被删除

时间和日期

  • DATETIME

能够保存从 1000 年到 9999 年的日期和时间，精度为秒 时区无关

  • TIMESTAMP

和 UNIX 时间戳相同 应该尽量使用 TIMESTAMP，因为它比 DATETIME 空间效率更高

用户及权限管理

  • 创建一个能在主机登录的用户
create user 'user2'@'%' identified by '123';
  • 授予权限
grant all on *.* to 'user2'@'%';

复制

主从复制

2020310201955

  • binlog线程：将master服务器上的数据写入binlog
  • io线程：读取master的binlog到replica的relay log（中继日志）
  • sql线程：读取中继日志，将数据写入到replica

读写分离

主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作

读写分离提高性能的原因：

  • 缓解了锁的争用
  • 从服务器只做读，资源利用率更高
  • 增加冗余数据，提高可用性

实现

常用代理方式实现，代理服务器根据传进来的请求，决定将请求转发到哪台服务器

202031020242

设计规范

命名规范

数据库

  • [a-z ][0-9] _
  • 不超过30字符
  • 备份数据库可以加自然数

表

  • [a-z ][0-9] _
  • 相同关系的表可以加相同的前缀

字段

  • [a-z ][0-9] _
  • 多个单词使用下划线分割
  • 每个表必须有自增主键（默认系统时间）
  • 关联字段名尽可能相同

字段类型规范

  • 使用较少的空间来存储
  • ip最好使用int
  • 固定长度的类型使用char
  • 最好给默认值

索引规范

  • 加一个index后缀
  • 为每个表创建主键索引
  • 符合索引慎重

范式规范

  • 必须满足第二范式
  • 尽量满足第三范式

MYSQL设计原则

核心原则

  • 不在数据库做运算
  • 控制列数量（20以内）
  • 平衡范式与冗余
  • 禁止大SQL
  • 禁止大事务
  • 禁止大批量

字段原则

  • 用好数据类型节约空间
  • 字符转为数字
  • 避免使用NULL
  • 少用text

索引原则

  • 不在索引列做运算
  • innodb主键使用自增
  • 不用外键

SQL原则

  • 尽可能简单
  • 简单事务
  • 避免使用触发器，函数
  • 不使用select *
  • OR改写成IN或UNION
  • 避免前%
  • 慎用count（*）
  • limit高效分页
  • 少用连接join
  • group by
  • 使用同类型比较
  • 打散批量更新