MySQL基础

作记录，内容未整理

存储引擎

• Mysql 的索引技巧：MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能
• Mysql是一个单进程多线程的app，这就说明了除了执行sql以外，其它的辅助性的功能（刷新日志）也是由线程完成的。oracle是一个多进程的软件
• 常用的由MyISAM和InnoDB：

两种常用的搜索引擎对比中重要的几点

• MyISAM不支持事务，InnoDB支持事务
• 锁力度：MyISAM是表级锁，InnoDB是行级锁。所以MyISAM并发比InnoDB处理要弱
• MVCC: 快照读，InnoDB不会锁
• MyISAM没有对事务的支持，所以insert等语句比innoDB要快一些
• mysql server支持的存储引擎查询指令：
  

字段类型

整形

• INT（无论是INT, TINYINT或者是SMALLINT）后面的（）是没有实际意义的
• 设计表的时候遵从够用原则
• 定点型：decimal
• 浮点类型：float,double（总位数，小数部分位数），记录的是一个精确的值

字符类型

• CHAR/VARCHAR：VARCHAR是一个变长的，CHAR固定最大长度为4字节，VARCHAR占用字节数根据存储字节来判定。VARCHAR类型的存储中包含了一个记录长度的部分，如果VARCHAR(n), n<=255的时候，用一个字节记录长度，如果n>255，用两个字节记录长度
• utf8支持的最大编码长度为3字节，而utf8mb4大于3字节。emoji就是4字节编码

时间日期类

• DATE - ‘1000-01-01’ to ‘9999-12-31’
• DATETIME - ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’ mysql 5.6.4 后占用字节从8字节到5字节
• TIMESTAMP - ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC 时间范围比较小

一般DATETIME优点是存储范围非常的广

Master-Salve

单点问题

• 数据丢失风险大，单点成熟能力有限
• 读写无法分离，单点承受有限
• 无法快速快速进行failover
• 随着业务的需求增多，无法横向扩展
• 压力过大，无法备份数据

BinLog, RelayLog

• 意义：Mysql的异步复制就是通过这两个日志来完成的
• binLog的作用：代码执行SQL语句（增删改，DDL）按照提交的先后顺序在MySQL内部生成的日志。binlog可以作为数据库回滚及异步复制时使用
• relayLog：relay Log是通过异步复制Master中的binlog，并生成Slave的执行日志。其中内部也是按照SQL提交的先后书讯存储
• 所有的log记录都是以commit为前提的
• 主从复制过程：master执行数据库修改SQL,并存储到自己的binlog当中，slave复制binlog到relaylog当中，并按照log文件中的sql语句执行sql，完成复制

主从复制中的thread

主库

• 主库中运行binlog dump thread ：当从库链接主库的时候，会发送binlog event并持有该event的lock，发送完（IO thread 读到后） 释放该event的lock

从库

• 从库中存在了一个I/O thread ：当读到主库发送的binlog event，会生成relay log
• 同时从库当中还存在一个SQL thread：读relay log， 并在从库上执行
  

半同步的原理

• 由于传统复制的是异步，当主库出现crash的时候，会有一部分已提交的事物，无法异步复制从库，会导致数据丢失，而半同步能解决这部分问题

• 半同步复制只是在相对于异步复制来说，能够更少的避免数据丢失（一个事务）

• 为了保证数据上的完整性，会牺牲一部分性能（接收确认的TCP/IP往返时间）
• 插件式的，安装简单

GTID（global transaction identifiers）

• 个人理解的概念：本质上是一种从库到主库寻找binlog，文件便宜位置（也就是复制起始点）的技术

• 作用：GTID(Global Transaction ID)是MySQL5.6引入的功能，可以在集群全局范围标识事务，用于取代过去通过binlog文件偏移量定位复制位置的传统方式。借助GTID，在发生主备切换的情况下，MySQL的其它Slave可以自动在新主上找到正确的复制位置，这大大简化了复杂复制拓扑下集群的维护，也减少了人为设置复制位置发生误操作的风险。另外，基于GTID的复制可以忽略已经执行过的事务,减少了数据发生不一致的风险。

• 主从复制是要进行配置的

索引

• 定义：索引是存储引擎用于快速定位数据的一种数据结构
• 作用：1. 优化数据访问；2. 保持数据完整性
• InnoDB 索引使用B+Tree

B+Tree

• 聚集索引(主键索引)
• 辅助索引
• 单列索引
• 联合索引：1. 联合索引字段整体有序，左前缀字段有序；2. 联合索引可代替左前吹字段的单列索引；3. 查询条件中联合索引的左前缀为确定值，此联合索引可优化为第二个字段的索引
• 每个InnoDB表具有一个特殊的索引称为聚簇索引（也叫聚集索引，聚类索引，簇集索引）。如果表上定义有主键，该主键索引就是聚簇索引。如果未定义主键，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚簇索引。如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。表中的聚簇索引（clustered index ）就是一级索引，除此之外，表上的其他非聚簇索引都是二级索引，又叫辅助索引（secondary indexes）。

• 如果一个索引包含了所有要查询的字段的值，称为覆盖索引

• InnoDB的基础是一级索引，也就是主键索引。每一个InnoDB都会有一个主见索引，Clustered B+ Tree，当使用主键索引进行查询的时候是直接在Clustered B+ Tree上查询，如果使用辅助索引，也就是二级索引查询的时候，首先会在Secondary B+ Tree上查询主键，再在主键在Clustered B+ Tree上查询。只要是通过二级索引都会有两遍索引

• 辅助索引又包含单列索引和组合索引

• 联合索引特点：

  1. 联合索引字段整体有序，左前缀字段有序，先对第一个字段有序，当第一个字段相等，则按照第二个字段有序，以此类推
  2. 联合索引可代替左前缀字段的单列索引
  3. 查询条件中联合索引的左前缀为确定之，此联合索引可优化为第二个字段排序

• 覆盖索引：如果一个索引包含所有要查询的字段的值，就称为覆盖索引

• 主键索引的叶子节点包含了主见列表，事务ID，回滚指针，非主见列值。（所以一般情况下如果要查询所有列，都是先从辅建索引中找到id,然后通过id主键再到主键索引表中查询完整信息。
• 辅建索引：叶子节点包含索引列值一级对应的主键索引的主键列值。所以如果辅助索引包含了所需要的字段，则称为覆盖索引，当查询语句出现覆盖索引的时候，则不需要查询主键索引
• 按照这种情况，一个sql只会存在一个索引

什么sql指令用不到索引

• where中没有内容
• 否定条件： <>, not in, not exist
• join中链接字段类型不一致
• 扫描内容超过全表的20%
• where条件的字段存在函数运算
• like ‘%name’
• 出现隐式字符类型转换（函数，contact)
• 是否用到索引，根据索引的顺序来，意思是首先要匹配最左前缀

注意

• 尽量避免更新索引列
• 尽量在选择性高的字段添加索引show index from table_name， 覆盖索引效率非常高;
• order by 后面的字段也尽量加入在联合索引当中，此处where条件必须是=,才会用到索引的排序
• 如果order by id，则只需要在name字段上建立索引即可，name索引表中是包含主键的
• 建立错误的索引和无法用到的索引就等于无索引可走，当无索引可走的时候，相当于全表扫描
• 范围较大的查询，还成分段做，不分段会带来大量的IO操作
• 避免产生子查询，子查询产生的临时表是无索引可走的，只能全表扫描
• 不要在索引字段上使用函数，因为InnoDB不支持函数索引