mysql

最新推荐文章于 2024-10-10 21:13:48 发布

weixin_45627802

最新推荐文章于 2024-10-10 21:13:48 发布

阅读量108

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/weixin_45627802/article/details/101078422

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

IP存储
INET_ATON(str) address to number
INET_NTOA(number) number to address

decimal不会损失精度，存储空间会随数据的增大而增大，可超过int，bigint存储范围的数
double占用固定空间，较大的数的存储会损失精度。

尽可能用not null,因此通常使用特殊的数据进行占位，比如0，’’。

第一范式，字段原子性，字段不可再分。
第二范式，消除对主键的部分依赖，即在表中加上一个与业务逻辑无关的字段作为主键。
第三范式，消除对主键的传递依赖。

	MyISAM	Innodb
文件格式	数据和索引分别存储在.MYD和.MYI,表结构存在.frm	数据和索引集中存储在.ibd,表结构存储在.frm
能否移动	能，一张表对应自己的.frm,.MYD,.MYI	否，还有一些关联信息存在data文件夹下
记录存储顺序	按记录插入顺序保存	按主键大小有序插入
空间碎片	产生	不产生
外键	不支持	支持
锁	表级锁	行级锁，表级锁
选择依据	以读写插入为主，比如博客系统	更新，删除频繁，并发量高，OA自动化办公系统

普通索引，唯一索引，主键索引，全文索引。使用场景where,order by,join,select(后只写必要的查询字段)。字段要独立出现，like不能以通配符开头，复合索引只对第一个字段有效，状态值不容易使用到索引。

select sql_cache * from user;
reset query cache;

分区算法，hash(整型)，key(字符串),range(大小范围),list(in(值列表))；

grant replication slave on *.* to 'testtest'@'%' identified by 'testtest';

create table t_range(
 id int(11),
 name varchar(50),
 date datetime
)partition by range(year(date))(
 partition p2007 values less than (2008),
 partition p2008 values less than (2009),
 partition p2009 values less than (2010),
 partition p2010 values less than maxvalue
);
alter table t_range drop partition p2007;
RANGE 的分区方式在加分区的时候，只能从最大值后面加，而最大值前面不可以添加；
 alter table t_range reorganize partition p2010 into(partition p2010_1 values less than (2011),partition p2010_2 values less than(2012),partition p2010_3 values less than maxvalue);
 
create table t_list(
id int(11),
name varchar(50),
sex tinyint(4)
)partition by list(sex)(
partition p0 values in (0),
partition p1 values in (1)
);

create table t_hash(
id int(11),
name varchar(50),
zipcode int(10)
)partition by hash(zipcode)
partitions 4;

create table t_key(
id int(11),
name varchar(50),
zipcode int(10)
)partition by linear key(zipcode)
partitions 4;

InnoDB自增值是通过其本身的自增长计数器来获取值。
如果InnoDB表的数据写入顺序能和B+树索引的叶子节点顺序一致的话，这时候存取效率是最高的，也就是下面这几种情况的存取效率最高：
a、使用自增列(INT/BIGINT类型)做主键，这时候写入顺序是自增的，和B+数叶子节点分裂顺序一致；

b、该表不指定自增列做主键，同时也没有可以被选为主键的唯一索引(上面的条件)，这时候InnoDB会选择内置的ROWID作为主键，写入顺序和ROWID增长顺序一致；

c、如果一个InnoDB表又没有显示主键，又有可以被选择为主键的唯一索引，但该唯一索引可能不是递增关系时(例如字符串、UUID、多字段联合唯一索引的情况)，该表的存取效率就会比较差。

如果字段id被定义为AUTO_INCREMENT，在插入一行数据的时候，自增值的行为如下：

1.如果插入数据时id字段指定为0、null或未指定值，那么就把这个表当前的AUTO_INCREMENT值填到自增字段

2.如果插入数据时id字段指定了具体的值，就直接使用语句里指定的值

假设，某次要插入的值是X，当前的自增值是Y

1.如果X<Y，那么这个表的自增值不变

2.如果X>=Y，就需要把当前自增值修改为新的自增值

主从服务器可以使用不同的存储引擎。Master上使用InnoDB,利用事务、行级锁等高级特性,Slave上使用MyISAM，读性能更好，节省内存，容易备份。

MySQL 复制的基本过程如下：

2.1．Slave 上面的IO线程连接上 Master，并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容；

2.2. Master 接收到来自 Slave 的 IO 线程的请求后，通过负责复制的 IO 线程根据请求信息读取指定日志指定位置之后的日志信息，返回给 Slave 端的 IO 线程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息在 Master 端的 Binary Log 文件的名称以及在 Binary Log 中的位置；

2.3. Slave 的 IO 线程接收到信息后，将接收到的日志内容依次写入到 Slave 端的Relay Log文件(mysql-relay-bin.xxxxxx)的最末端，并将读取到的Master端的bin-log的文件名和位置记录到master- info文件中，以便在下一次读取的时候能够清楚的高速Master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”

2.4. Slave 的 SQL 线程检测到 Relay Log 中新增加了内容后，会马上解析该 Log 文件中的内容成为在 Master 端真实执行时候的那些可执行的 Query 语句，并在自身执行这些 Query。这样，实际上就是在 Master 端和 Slave 端执行了同样的 Query，所以两端的数据是完全一样的。
binlog_format="ROW"
RBR 的优点：

任何情况都可以被复制，这对复制来说是最安全可靠的

和其他大多数数据库系统的复制技能一样

多数情况下，从服务器上的表如果有主键的话，复制就会快了很多

复制以下几种语句时的行锁更少：

* INSERT … SELECT

* 包含 AUTO_INCREMENT 字段的 INSERT

* 没有附带条件或者并没有修改很多记录的 UPDATE 或 DELETE 语句

执行 INSERT，UPDATE，DELETE 语句时锁更少

从服务器上采用多线程来执行复制成为可能

RBR 的缺点：

binlog 大了很多

复杂的回滚时 binlog 中会包含大量的数据