编辑

MySql知识点

2019-09-25 刚子 db mysql mysql

---

• 安装
  • centos7通过yum方式安装mysql5.7
• 基础知识点
  • 存储引擎
  • 索引
    • 索引的种类
    • 聚集索引、非聚集索引
    • 索引的设计原则
    • 使用索引时的注意事项
    • 哪些sql会导致全表扫描
    • B+树
      • 为什么MySql使用B+树作为索引存储结构
      • B(-/+)树结构
    • 索引覆盖
    • 索引下推
  • 事务隔离级别
    • 事务的特性
    • 多个事务并发访问产生的问题
    • 隔离级别
  • 锁
    • MySql锁的介绍
    • 行锁
    • 表锁
    • 间隙锁（Next-Key锁）
    • mysql锁的总结
  • 字符集、排序规则
    • 介绍
    • 继承关系
    • 修改默认的字符集和排序规则
  • 常用函数
• 性能优化
  • SQL语句优化
  • 查询缓存（mysql8已废弃）
    • 缓存失效和清理
    • 无法使用查询缓存的场景
  • 分区
    • 分区介绍
    • 分区类型
    • 管理分区
    • INSERT… ON DUPLICATE KEY UPDATE
• 分库分表
  • 分库分表介绍
  • 分库分表带来的问题
    • 事务问题及解决办法
    • 跨节点join问题及解决方法
    • 跨节点分页、排序、函数问题
    • 主键避重问题
  • 分库分表原则
  • 分片策略
    • 数据Range分片
    • ID取模分片
    • 一致性Hash算法
  • 分库分表中间件
  • 平滑数据迁移方案
    • 双倍扩容法
    • 一致性hash算法
• 高可用
• 参考

总结MySql的知识点

安装

centos7通过yum方式安装mysql5.7

# 下载安装mysql官方yum源
wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
yum -y install mysql57-community-release-el7-10.noarch.rpm
# 安装mysql
yum -y install mysql-community-server

# 启动
systemctl start mysqld.service
systemctl status mysqld.service

# 从日志查询初始root密码
grep "password" /var/log/mysqld.log
# 使用root账号登陆
mysql -uroot -p

# 默认情况下重置root密码是有密码强度校验规则的，为了测试，我们先禁用密码强度、长度校验
set global validate_password_policy=0;
set global validate_password_length=1;

# 重置root账号密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
# 查看密码强度校验设置
SHOW VARIABLES LIKE 'validate_password%';

# 允许外网访问本数据库
grant all privileges on *.* to 'root'@'%' identified by 'root';

基础知识点

存储引擎

-- 查看mysql提供的所有存储引擎
show engines;
-- 查看数据库的存储引擎，默认InnoDB
show variables like '%storage_engine%';
-- 查看表的存储引擎
show table status like "table_name";

mysql5.5版本开始默认的存储引擎是InnoDB，之前是MyISAM，MyISAM和InnoDB的主要区别：

• InnoDB支持表级锁、行级锁，默认为行级锁（命中索引才会生效），MyISAM仅支持表级锁。

  大量update和insert操作的时候InnoDB操作效率高，而MyISAM更适合查多写少的情况。

• InnoDB支持事务、支持崩溃后安全恢复，MyISAM不支持
• InnoDB支持外键，MyISAM不支持外键

索引

索引的种类

• 唯一索引

添加唯一索引的列不可以有重复值，如果是组合索引那组合索引必须唯一，唯一索引列允许null值。

# 单列唯一索引
ALTER TABLE table_name ADD UNIQUE (column);
# 组合唯一索引
ALTER TABLE table_name ADD UNIQUE (column1,column2);

• 主键索引

主键索引是特殊的唯一索引，不允许null值，一个表只能有一个。

# 创建表的时候创建主键
CREATE TABLE table_name ( [...], PRIMARY KEY (column) );
# 给已存在的表添加主键索引
ALTER TABLE table_name ADD PRIMARY KEY (column);

• 普通索引

# 单列索引
ALTER TABLE table_name ADD INDEX index_name (column);
# 组合索引
ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);

• 全文索引

全文索引可以在VARCHAR或者TEXT类型的列上创建。

ALTER TABLE table_name ADD FULLTEXT (column);

索引不能修改，只能删除重建：

SHOW INDEX FROM table_name;
DROP INDEX index_name ON table_name;

聚集索引、非聚集索引

• 聚集索引指的是索引的逻辑顺序和数据物理存放地址的顺序是一致的

一个表有主键的话，主键索引就是聚集索引，如果没有主键索引，该表的第一个唯一非空索引会被作为聚集索引，如果既没有主键也没有唯一非空索引，InnoDB会生成一个隐藏主键作为聚集索引，InnoDB会为每个表都增加一个聚集索引。

• 非聚集索引指的是索引的逻辑顺序与数据物理存放地址的顺序不一致

除去聚集索引以外的索引都是非聚集索引。

索引的设计原则

使用索引时的注意事项

1. MySQL只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE。在使用关键字‘like’查询时：like ‘n%’ 走索引；但是like ‘%n%’不走索引，即有且仅有后面带上%时走索引。

2. 使用函数时索引不生效。

3. where id+1=?建议改成where id=?-1保证索引字段独立出现才会使用索引

4. or两边关键字都建立索引才会使用索引，否则全表扫描

5. 状态值，例如性别字段，使用索引会比全表扫描效率还低

6. 最左前缀（Leftmost Prefixing）原则：在创建组合索引时（例如col1，col2，col3），搜索条件具有这些情形的都会使用该组合索引（col1，col2，col3或col1，col2或col1），相当于是创建了三个索引（col1，col2，col3或col1，col2或col1）。

7. 使用EXPLAIN命令可以分析SQL索引

   EXPLAIN select * from user where first_name like 'a%'
   

8. 索引会占用磁盘空间，索引会降低数据更新操作的速度

哪些sql会导致全表扫描

1. 使用null做为判断条件

如：select account from member where nickname = null;
建议在设计字段时尽量将字段的默认值设为0，改为select account where nickname = 0;

2. 左模糊查询Like %XXX%

如：select account from member where nickname like ‘%XXX%’ 或者 select account from member where nickname like ‘%XXX’

建议使用select account from member where nickname like ‘XXX%’，如果必须要用到左查询，需要评估对当前表全表扫描造成的后果；

3. 使用or做为连接条件

如：select account from member where id = 1 or id = 2;

建议使用union all,改为 select account from member where id = 1 union all select account from member where id = 2;

4. 使用in时(not in)

如：select account from member where id in (1,2,3)

如果是连续数据，可以改为select account where id between 1 and 3;当数据较少时也可以参考union用法；

或者：select account from member where id in (select accountid from department where id = 3 )，可以改为select account from member where id exsits (select accountid from department where id = 3)

not in 可以对应 not exists;

5.使用not in时

如select account where id not in (1,2,3)

6.使用！=或<>时

建议使用 ,>=,between等；

7.对字段有操作时也会引起权标索引

如select account where salary * 0.8 = 1000 或者 select account where sustring(nickname,1,3) = ‘zhangxiaolong’;

8.使用count(*)时

如select count(*) from member；

建议使用select count(1) from member;

9.使用参数做为查询条件时

如select account from member where nickname = @name

由于SQL语句在编译执行时并不确定参数，这将无法通过索引进行数据查询，所以尽量避免

B+树

为什么MySql使用B+树作为索引存储结构

查找复杂度、I/O次数决定一个索引算法的优劣

• 顺序存储，查找复杂度O(n)，效率低
• hash索引，无法排序，无法满足范围查找
• 二叉树，查找复杂度O(log2n)好一些，但是节点固定且非平衡，树的高度会很高，导致查找I/O次数多
• 平衡树，红黑树，树高度很高，临近节点物理存储上可能距离很远，无法利用局部性，范围查找效率低
• B树（B-树），查找复杂度O(logdn)效率更好一些，每个节点都存储索引key以及数据[key,data]
• B+树是B树的变种，进行了优化，内部节点只存储[key]，只有叶子节点才存储[key,data]，这样内部节点的扇出就变高（单个节点少了data之后可以存储的key变多），也就是d变大，所以树的高度不变（也就是I/O次数不变）的情况下可以存储更多的数据。MySql在B+树的基础上进一步做了优化，叶子节点的数据顺序存储的同时，节点之间保存了下个节点的地址，提高了范围查找的效率

1. InnoDB存储引擎的最小存储单元是页，页可以用于存放数据也可以用于存放键值+指针，在B+树中叶子节点存放数据，非叶子节点存放键值+指针。

2. 索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而在去数据页中查找到需要的数据；

B(-/+)树结构

关于树，有一些基本概念需要了解：

1. 深度：从上往下数，根节点为1（有的从0开始算，不管）
2. 高度：从下往上数，最底下的叶子节点为1（对于一棵树来讲，深度和高度是相等的，但是对于树中的某个节点来讲会不相等）
3. 度（也叫阶）：指的是树中节点的最多只能有几个子节点，例如度为3的树，也就是3阶树，每个节点最多有3个孩子节点，二叉树的度为2，对于每个节点来讲，度可能的值有0,1,2这三种
4. 层：根节点的层数为1，往下依次递增

关于B树，有一些特点需要了解：

1. 如果一棵树度（或阶）为m，则每个节点最多有m-1个[key,data]，根节点最少有1个[key,data]，非根节点最少有m/2（向下取整）个[key,data]

   根节点： 1 <= k <= m-1

   非根节点： m/2 <= k <= m-1

   向B(-/+)树插入元素[key,data]时，如果节点超过m-1个元素则从中间元素开始进行分裂

   删除元素时，如果节点<m/2个元素时需要与兄弟节点进行合并，分裂和合并的过程参考这里

2. 每个节点中的[key,data]都是按照key从小到大排序的

索引覆盖

mysql覆盖索引与回表

索引下推

Mysql性能优化：什么是索引下推？

事务隔离级别

-- 两种方式查看当前数据库的事务隔离级别
SHOW VARIABLES LIKE "%tx_isolation%"
select @@tx_isolation;

事务的特性

事务的ACID属性：atomicity,consistent,isolation,durable

• 原子性atomicity

事务是一个原子操作单元，事务中包含的所有操作要么都做，要么都不做，没有第三种情况。

• 一致性consistent

事务操作前和操作后都必须满足业务规则约束，比如说A向B转账，转账前和转账后AB两个账户的总金额必须是一致的。

• 隔离性isolation

隔离性是数据库允许多个并发事务同时对数据进行读写的能力，隔离性可以防止事务并发执行时由于交叉执行导致数据不一致的问题。

• 持久性durable

事务完成后，对数据的修改是永久的，即使出现系统故障也不会丢失。

多个事务并发访问产生的问题

• 更新丢失

当两个事务选择同一行，然后更新数据，由于每个事务都不知道其他事务的存在，就会发生丢失更新的问题，（你我同时读取同一行数据，进行修改，你commit之后我也commit，那么我的结果将会覆盖掉你的结果）。

• 脏读

一个事务正在对一条记录做修改，在这个事务提交之前，别的事务读取到了这个事务修改之后的数据，也就是说，一个事务读取到了其他事务还没有提交的数据，就叫做脏读。

• 不可重复读

一个事务读某条数据读两遍，读到的是不一样的数据，也就是说，一个事务在进行中读取到了其他事务对旧数据的修改结果，（比如说 我开一个事务 修改某条数据 先查后改 执行修改动作的时候发现这条数据已经被别的事务删掉了）

• 幻读

一个事务中，读取到了其他事务新增的数据，仿佛出现了幻象。（幻读与不可重复读类似，不可重复读是读到了其他事务update/delete的结果，幻读是读到了其他事务insert的结果）

隔离级别

• read-uncommitted（生产不能使用这个级别）

在一个事务中，可以读取到其他事务未提交的数据变化，这种读取其他会话还没提交的事务，叫做脏读现象，在生产环境中切勿使用。

• read-committed（一般生产用这个级别就可以了）

在一个事务中，可以读取到其他事务已经提交的数据变化，这种读取也就叫做不可重复读，因为两次同样的查询可能会得到不一样的结果。

• repetable-read（默认级别）

MySQL默认隔离级别，在一个事务中，直到事务结束前，都可以反复读取到事务刚开始时看到的数据，并一直不会发生变化，避免了脏读、不可重复读现象，但是它还是无法解决幻读问题。

Mysql中RR隔离级别是默认事务隔离级别，也是Mysql的强项之一，在RR隔离级别下事务有最大的吞吐量，而且不会出现幻读问题。Next-Key Lock 就是为了解决这个问题，简单讲 record lock+gap lock 就是 Next-Key Lock。

• serializable

这是最高的隔离级别，它强制事务串行执行，避免了前面说的幻读现象，简单来说，它会在读取的每一行数据上都加锁，所以可能会导致大量的超时和锁争用问题。

隔离级别	读数据一致性	脏读	不可重复读	幻读
未提交读	最低级别，只保证不读取物理上损坏的数据 任何数据都不加锁	有	有	有
已提交读	语句级，读数据不加锁(每次都读取到最新数据)，但是写数据加锁	无	有	有
可重复读	事务级，读数据就加锁(共享锁)，写数据也加锁	无	无	有
可串行化	最高级别，事务级，直接锁表，解决了幻读问题	无	无	无

数据库实现事务隔离的方式，基本可以分为以下两种:

• 一种是在读取数据前，对其加锁，阻止其他事务对数据进行修改。
• 另一种是不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot），并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（MultiVersion Concurrency Control，简称MVCC或MCC），也经常称为多版本数据库。

MVCC中读操作分为两种：

• 快照读 (snapshot read)，读取的是记录的可见版本(有可能是历史版本)，不用加锁，普通的select操作是快照读

select * from table where ?;

• 当前读 (current read)，读取的是记录的最新版本，并且当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录

select * from table where ? lock in share mode;--添加S（共享）锁
select * from table where ? for update;--添加X（排它）锁
insert into table values (…);--添加X（排它）锁
update table set ? where ?;--添加X（排它）锁
delete from table where ?;--添加X（排它）锁

锁

• 悲观锁

总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁（共享资源每次只给一个线程使用，其它线程阻塞，用完后再把资源转让给其它线程）。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。Java中synchronized和ReentrantLock等独占锁就是悲观锁思想的实现。

多写的场景下用悲观锁就比较合适。

• 乐观锁

总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号机制和CAS算法实现。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库提供的类似于write_condition机制，其实都是提供的乐观锁。在Java中java.util.concurrent.atomic包下面的原子变量类就是使用了乐观锁的一种实现方式CAS实现的。

乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。乐观锁一般会使用版本号机制或CAS算法实现。

1. 版本号机制(MVCC)

一般是在数据表中加上一个数据版本号version字段，表示数据被修改的次数，当数据被修改时，version值会加一。当线程A要更新数据值时，在读取数据的同时也会读取version值，在提交更新时，若刚才读取到的version值为当前数据库中的version值相等时才更新，否则重试更新操作，直到更新成功。

1. CAS(compare and swap)

无锁编程，即不使用锁的情况下实现多线程之间的变量同步，也就是在没有线程被阻塞的情况下实现变量的同步，所以也叫非阻塞同步（Non-blocking Synchronization）。CAS算法涉及到三个操作数：

当前的内存值 V

旧的预期值 A

拟写入的新值 B

当且仅当 V 的值等于 A时，CAS通过原子方式用新值B来更新V的值，否则不会执行任何操作（比较和替换是一个原子操作）。一般情况下是一个自旋操作，即不断的重试。

CAS的ABA问题

如果变量V初次读取的时候是A，并且在准备赋值的时候检查到它仍然是A，那能说明它的值没有被其他线程修改过了吗？如果在这段期间曾经被改成B，然后又改回A，那CAS操作就会误认为它从来没有被修改过。针对这种情况，java并发包中提供了一个带有标记的原子引用类AtomicStampedReference，它可以通过控制变量值的版本来保证CAS的正确性。

MySql锁的介绍

-- 获取InnoDB行锁争用情况
show status like 'innodb_row_lock%';

行锁

InnoDB行锁是通过给索引上的索引项加锁来实现的，这就意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

• 共享锁S（也叫读锁）

SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

• 排它锁X（也叫写锁）

SELECT * FROM table_name WHERE ... FOR UPDATE

排他锁指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁。mysql InnoDB引擎默认的修改数据语句：update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

（1）在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。

create table tab_no_index(id int,name varchar(10)) engine=innodb;
insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

create table tab_with_index(id int,name varchar(10)) engine=innodb;
alter table tab_with_index add index id(id);

（2）由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。

--id字段有索引，name字段没有索引
alter table tab_with_index drop index name;
insert into tab_with_index  values(1,'4');
select * from tab_with_index where id = 1;

（3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。

alter table tab_with_index add index name(name);

（4）即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决 定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突 时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。 比如，在tab_with_index表里的name字段有索引，但是name字段是varchar类型的，检索值的数据类型与索引字段不同，虽然MySQL能够进行数据类型转换，但却不会使用索引，从而导致InnoDB使用表锁。通过用explain检查两条SQL的执行计划，我们可以清楚地看到了这一点。

explain select * from tab_with_index where name = 1
explain select * from tab_with_index where name = '1'

表锁

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。

• 意向共享锁（IS）：事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
• 意向排他锁（IX）：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

间隙锁（Next-Key锁）

--假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101
Select * from  emp where empid > 100 for update;
--InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁

• 防止其它事务插入数据出现幻读
• 满足恢复和复制的需要

MySQL间隙锁、Next-Key Lock主要知识点

mysql锁的总结

了解InnoDB锁特性后，用户可以通过设计和SQL调整等措施减少锁冲突和死锁，包括：

• 尽量使用较低的隔离级别； 精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会；
• 选择合理的事务大小，小事务发生锁冲突的几率也更小；
• 给记录集显式加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁；
• 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会；
• 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响； 不要申请超过实际需要的锁级别；除非必须，查询时不要显示加锁；
• 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。

字符集、排序规则

--查看字符集，一般为utf8
show variables like 'character\_set\_%';
--查看排序规则，一般为utf8_general_ci
show variables like 'collation\_%';

介绍

• 字符集：字符在数据库中的编码集合

  一般普通表设置为utf8即可，如果需要存储emoji等表情符号，需要设置字符集为 utf8mb4

  升级utf8至utf8mb4的方法：

  
  -- 修改数据库
  ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci; 
  -- 修改表
  ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;  
  -- 修改表字段
  ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  

  # 修改服务器my.ini配置
  default-character-set = utf8mb4 
  default-character-set = utf8mb4    
  character-set-client-handshake = FALSE  
  character-set-server = utf8mb4  
  collation-server = utf8mb4_unicode_ci  
  init_connect='SET NAMES utf8mb4'
  

  -- 检查是否生效
  SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
  

• 排序规则：定义字符排序和比较的规则

  utf8_unicode_ci和utf8_general_ci对中、英文来说没有实质的差别。utf8_unicode_ci比较准确，utf8_general_ci速度比较快。

继承关系

1. 当创建一个数据库的时候，他从服务器继承了character_set_server设置；

2. 当创建表的时候，他从数据库继承字符集；

3. 当创建列时，它从表继承字符集；

修改默认的字符集和排序规则

在mysql安装目录下 my.ini文件中添加、修改：

character-set-server=utf8
collation_server=utf8_general_ci

常用函数

• 计算2个经纬度之间的距离

  select ST_Distance_Sphere( point(-87.6770458, 41.9631174), point(-73.9898293, 40.7628267) )
  

• 使用int类型字段存储ip地址

  create table t_ip (ip int unsigned, name char(1));
  insert into t_ip values(inet_aton('192.168.1.200'), 'A'), (inet_aton('200.100.30.241'), 'B');
    
  select * from t_ip where ip = inet_aton('192.168.1.200');
  select inet_ntoa(ip) from t_ip;
  select inet_ntoa(ip) from t_ip where ip between inet_aton('192.168.1.0') and inet_aton('192.168.1.255');
  

性能优化

SQL语句优化

• exists与in的区别
• 查询单条数据添加limit 1

查询缓存（mysql8已废弃）

--查看查询缓存相关变量，服务器是否开启查询缓存等
show variables like '%query_cache%';

• query_cache_type：0不使用缓存（推荐），1始终使用缓存，2按需使用查询缓存

值为1但是又不想使用查询缓存，则SQL中添加关键字SQL_NO_CACHE

` SELECT SQL_NO_CACHE * FROM my_table WHERE condition; `

值为2但是需要使用缓存的话，SQL中添加关键字SQL_CACHE

` SELECT SQL_CACHE * FROM my_table WHERE condition; `

• query_cache_size ：值为0则无法使用查询缓存

` SET GLOBAL query_cache_size = 134217728; `

# 开启查询缓存
show variables like "%char%"; # 查看mysql安装路径
vi /etc/my.cnf # windows上修改my.ini
[mysqld] # 添加配置如下：
query_cache_size=20M
query_cache_type=ON
# 重启mysql
service mysql restart

--查看缓存使用情况
show status like 'qcache%';

• Qcache_free_blocks：缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。
• Qcache_free_memory：缓存中的空闲内存。
• Qcache_hits：每次查询在缓存中命中时就增大。
• Qcache_inserts：每次插入一个查询时就增大。命中次数除以插入次数就是不中比率。
• Qcache_lowmem_prunes：缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看;如果这个 数字在不断增长，就表示可能碎片非常严重，或者内存很少。(上面的 free_blocks和free_memory可以告诉您属于哪种情况)
• Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。
• Qcache_queries_in_cache：当前缓存的查询(和响应)的数量。
• Qcache_total_blocks：缓存中块的数量。

缓存失效和清理

在表的结构或数据发生改变时，查询缓存中的数据不再有效。有这些INSERT、UPDATE、 DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE会导致缓存数据失效。所以查询缓存适合有大量相同查询的应用，不适合有大量数据更新的应用。对于数据库压力大的情况缓存的命中率很低。

FLUSH QUERY CACHE; -- 清理查询缓存内存碎片。
RESET QUERY CACHE; -- 从查询缓存中移出所有查询。
FLUSH TABLES; -- 关闭所有打开的表，同时该操作将会清空查询缓存中的内容。
show status like 'qcache%'; --执行之后再次查看Qcache_queries_in_cache已经变成0

无法使用查询缓存的场景

• 查询语句中加了SQL_NO_CACHE参数；

• 查询语句中含有获得值的函数，包涵自定义函数，如：CURDATE()、GET_LOCK()、RAND()、CONVERT_TZ等；

• 对系统数据库的查询：mysql、information_schema

• 查询语句中使用SESSION级别变量或存储过程中的局部变量

• 查询语句中使用了LOCK IN SHARE MODE、FOR UPDATE的语句

• 查询语句中类似SELECT …INTO导出数据的语句

• 事务隔离级别为Serializable情况下，所有查询语句都不能缓存

• 对临时表的查询操作

• 存在警告信息的查询语句

• 不涉及任何表或视图的查询语句

• 某用户只有列级别权限的查询语句

分区

分区介绍

注意：实际的互联网公司很少用到分区表，绝大多数用的都是分库分表。分库分表可空性更高。

分区优点：

1，分区可以分在多个磁盘，存储更大一点

2，根据查找条件，也就是where后面的条件，查找只查找相应的分区不用全部查找了

3，进行大数据搜索时可以进行并行处理，跨多个磁盘来分散数据查询，来获得更大的查询吞吐量，count(),sum()聚合查询速度更快。

4，历史数据单独分区，很容易清除，直接删除掉分区即可

分区的缺点：

1，分区列必须作为查询条件，否则会扫描全部分区

2，所有分区的存储引擎必须相同，某些存储引擎不支持分区（MERGE、CSV、FEDERATED）

3，一张表最多只能有1024个分区

4，分区表无法使用外键

5，分区表无法对非分区列创建唯一索引

6，分区的字段，必须是表上所有的唯一索引（或者主键索引）包含的字段的子集

分区类型

无论创建何种类型的分区，如果表中存在主键或唯一索引的列，则分区列必须是主键或唯一索引的一部分。索引列可以是null值。在没有主键和唯一索引的表中可以指定任意列为索引列。表中只能最多有一个唯一索引，即primary key 和unique key不能同时存在，primary key包含在unique key中时除外。

分区分为以下几种：

• range分区：基于一个给定的连续区间范围(区间要求连续并且不能重叠)，把数据分配到不同的分区

  注意：range 对应的分区键值必须是数字值，可以使用range columns(分区字段) 对非int型做分区，如字符串，对于日期类型的可以使用year()、to_days()、to_seconds()等函数

  -- 创建分区表
  CREATE TABLE `tbl_user_part` (
     `id` int(11) NOT NULL ,
     `username` varchar(255) DEFAULT NULL,
     `email` varchar(20)     DEFAULT NULL,
     `age` tinyint(4)        DEFAULT NULL,
     `type` int(11)          DEFAULT NULL,
     `create_time` datetime  DEFAULT CURRENT_TIMESTAMP
     -- PRIMARY KEY (`id`,`age`)
  ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
  PARTITION BY RANGE (age) (
      PARTITION p0 VALUES LESS THAN (20),
      PARTITION p1 VALUES LESS THAN (40),
      PARTITION p2 VALUES LESS THAN (60),
      PARTITION p3 VALUES LESS THAN (80),
      PARTITION p4 VALUES LESS THAN MAXVALUE
  );
    
  -- 修改表添加分区
  alter table <table> partition by RANGE(id) (
  	PARTITION p0 VALUES LESS THAN (1000000),
      PARTITION p1 VALUES LESS THAN (2000000),
      PARTITION p2 VALUES LESS THAN (3000000),
      PARTITION p3 VALUES LESS THAN (4000000),
      PARTITION p4 VALUES LESS THAN MAXVALUE 
  );
  

• list分区：类似于range分区，区别在于list分区是居于枚举出的值列表分区，range是基于给定的连续区间范围分区

  -- 语法
  create table <table> (
  	// 字段
  ) ENGINE=数据库引擎  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
  partition by LIST (分区字段或者基于该字段的返回的整数值的表达式) (
    partition <分区名称> values IN (Value1，Value2, Value3),
    ...
    partition <分区名称> values IN (Value4, Value5),
  );
  

  CREATE TABLE h2 (
   c1 INT,
   c2 INT
  )
  PARTITION BY LIST(c1) (
   PARTITION p0 VALUES IN (1, 4, 7),
   PARTITION p1 VALUES IN (2, 5, 8)
  );
  
  -- 查看这个表的每个分区的行数
  select
  	partition_name,
  	partition_expression,
  	partition_description,
  	table_rows
  from information_schema.partitions
  where table_schema = schema() and table_name = 'h2';
  

  INSERT INTO h2 VALUES (3, 5); – 会报错，Table has no partition for value 3 ```

• hash分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

​```sql CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY HASH( YEAR(col3) ) PARTITIONS 4;

– MOD(YEAR(‘2021-01-01’),4) – = MOD(2021,4) – = 1

insert into t1 values(1,’2’,’2021-01-01’); insert into t1 values(1,’2’,’2022-01-01’);

– 查看这个表的每个分区的行数 select partition_name, partition_expression, partition_description, table_rows from information_schema.partitions where table_schema = schema() and table_name = ‘t1’;

* key分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

```sql
CREATE TABLE k1 (
    id INT NOT NULL,
    name VARCHAR(20),
    UNIQUE KEY (id)
)
PARTITION BY KEY()
PARTITIONS 2;

insert into k1 values(1,'zhangsan');
insert into k1 values(2,'lisi');
insert into k1 values(3,'wangwu');
insert into k1 values(4,'tiezhu');

-- 查看这个表的每个分区的行数
select
	partition_name,

	partition_expression,
	partition_description,
	table_rows
from information_schema.partitions
where table_schema = schema() and table_name = 'k1';

注意：如果存在主键/唯一键，则不能使用主键/唯一键字段之外的其它字段分区。

• columns分区，分为 range columns 和 list columns 两种，columns分区支持一个或者多个字段作为分区键，不支持表达式作为分区键，这点区别于range 和 list 分区。

COLUMNS和RANGE和LIST分区的区别：

1）针对日期字段的分区就不需要再使用函数进行转换了，例如针对date字段进行分区不需要再使用YEAR()表达式进行转换。

2）COLUMN分区支持多个字段作为分区键但是不支持表达式作为分区键。

column支持的数据类型：

1）所有的整型，float和decimal不支持

2）日期类型：date和datetime，其他不支持

3）字符类型：CHAR, VARCHAR, BINARY和VARBINARY，blob和text不支持

  -- 创建range columns分区表
  create table rc3 (
  	a int,
  	b int
  ) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
  partition by range columns(a, b) (
  	partition p01 values less than (0, 10),
  	partition p02 values less than (10, 10),
  	partition p03 values less than (10, 20),
  	partition p04 values less than (10, 35),
  	partition p05 values less than (10, maxvalue),
  	partition p06 values less than (maxvalue, maxvalue)
  );
  
  -- 插入数据
  insert into rc3(a, b) values(1, 10);
  
  -- 查看这个表的每个分区的行数
  select
  	partition_name,
  	partition_expression,
  	partition_description,
  	table_rows
  from information_schema.partitions
  where table_schema = schema() and table_name = 'rc3';

-- 单列的range column分区
CREATE TABLE `range_colum_1` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE COLUMNS(c1) (
	PARTITION p0 VALUES LESS THAN (5) ENGINE = InnoDB,
	PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB
);

-- 多列的range column分区
 CREATE TABLE `range_colum_2` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  `c3` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE  COLUMNS(c1,c3)(
	PARTITION p0 VALUES LESS THAN (5,'aaa') ENGINE = InnoDB,
	PARTITION p1 VALUES LESS THAN (10,'bbb') ENGINE = InnoDB
);

-- 单列的list column分区
CREATE TABLE `list_column` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  `c3` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY LIST COLUMNS(c3)(
	PARTITION p0 VALUES IN ('aaa') ENGINE = InnoDB,
	PARTITION p1 VALUES IN ('bbb') ENGINE = InnoDB
);

管理分区

-- 查看mysql数据库目录
show variables like '%datadir%';

• myisam引擎
  • .frm : 存储表结构
  • .MYD : 存储表数据
  • .MYI : 存储索引文件
• innodb引擎
  • .frm : 表结构
  • .ibd : 数据 + 索引

select version();
-- 查看mysql版本
select version();

-- 查看分区插件是否激活 partition active
show plugins;

-- 对于低版本的MySQL，如果InnoDB引擎要想分区成功，需要在my.conf中设置innodb_file_per_table=1 设置成独立表空间，独立表空间：每张表都有对应的.ibd文件
innodb_file_per_table=1

-- 删除list或者range分区(同时删除分区对应的数据)
alter table <table> drop partition <分区名称>;

-- 新增分区
-- range添加新分区
alter table <table> add partition(partition p4 values less than MAXVALUE);

-- list添加新分区
alter table <table> add partition(partition p4 values in (25,26,28));

-- hash重新分区
alter table <table> add partition partitions 4;

-- key重新分区
alter table <table> add partition partitions 4;

-- 子分区添加新分区，虽然我没有指定子分区，但是系统会给子分区命名的
alter table <table> add partition(partition p3 values less than MAXVALUE);

-- range重新分区
ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE);

-- list重新分区
ALTER TABLE <table> REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES in (1,2,3,4,5));

INSERT… ON DUPLICATE KEY UPDATE

一条Insert on duplicate引发的血案

Mysql deadlock caused by INSERT… ON DUPLICATE KEY UPDATE

分库分表

分库分表介绍

• 垂直切分

  • 垂直分库，将耦合度低的表分别存储在不同的服务器上的数据库中

  • 垂直分表，大表拆小表，也可以避免跨页查询问题（ MySQL底层是通过数据页存储的，一条记录占用空间过大会导致跨页，造成额外的性能开销。 ）

  垂直切分的优点：

  • 解决业务系统层面的耦合，业务清晰
  • 与微服务的治理类似，也能对不同业务的数据进行分级管理、维护、监控、扩展等
  • 高并发场景下，垂直切分一定程度的提升IO、数据库连接数、单机硬件资源的瓶颈

  缺点：

  • 部分表无法join，只能通过接口聚合方式解决，提升了开发的复杂度
  • 分布式事务处理复杂
  • 依然存在单表数据量过大的问题（需要水平切分）

• 水平切分

  • 库内分表
  • 分库分表

  水平切分的优点：

  • 不存在单库数据量过大、高并发的性能瓶颈，提升系统稳定性和负载能力
  • 应用端改造较小，不需要拆分业务模块

  缺点：

  • 跨分片的事务一致性难以保证
  • 跨库的join关联查询性能较差
  • 数据多次扩展难度和维护量极大

  水平切分的方式：

  • 根据数据范围
  • 根据数据取模

分库分表带来的问题

事务问题及解决办法

原来的单一事务就变成分布式事务了

• XA协议两阶段提交

XA需要两阶段提交: prepare 和 commit。 第一阶段为准备（prepare）阶段。即所有的参与者准备执行事务并锁住需要的资源。参与者ready时，向transaction manager报告已准备就绪。 第二阶段为提交阶段（commit）。当transaction manager确认所有参与者都ready后，向所有参与者发送commit命令。

XA的问题是性能不理想，特别是在交易下单链路，往往并发量很高，XA无法满足高并发场景。mysql对XA协议的支持不是很好。

• 最终一致性

  例如RocketMQ支持消息事务的模式就是最终一致性的一个方案：

  

  通过消息事务的方式在服务化的多个应用之间实现数据最终一致性是可以的。

• TCC编程模式

  所谓的TCC编程模式，也是两阶段提交的一个变种。TCC提供了一个编程框架，将整个业务逻辑分为三块：Try、Confirm和Cancel三个操作。以在线下单为例，Try阶段会去扣库存，Confirm阶段则是去更新订单状态，如果更新订单失败，则进入Cancel阶段，会去恢复库存。总之，TCC就是通过代码人为实现了两阶段提交，不同的业务场景所写的代码都不一样，复杂度也不一样，因此，这种模式并不能很好地被复用。

  终于有人把“TCC分布式事务”实现原理讲明白了！

跨节点join问题及解决方法

一张表切分到多个节点的库里面之后，join查询是一个大问题，尽量避免join查询

• 全局表，数据字典表，很少修改，每个数据库中都可以保存一份这样的表，避免join跨节点查询

• 冗余字段从而减少join查询

• 分多次查询然后拼接数据，减少join查询

• 将存在关联关系的表记录放在同一个分片上，这样join查询就不用跨分片

  

跨节点分页、排序、函数问题

如果分页、排序的字段就是分片字段，则根据分片规则比较容易到各个节点上取数据，如果不是，则需要将所有节点排序后的前N条数据进行拉取，然后汇总后再次进行排序，最终返回给客户端。Max()、Count()等函数也同样，需要在各个节点上执行完之后汇总后再次执行最终将结果返回。

主键避重问题

最好的方式是雪花算法

分库分表原则

• 不到万不得已不要分库分表，优先尝试升级硬件、升级网络、读写分离、索引优化等等
• 数据量实在太大，影响数据备份、DDL锁表耗时长，经常访问或更新
• 动静分离，对大表中很少变动以及经常变动的列垂直拆分

分片策略

数据Range分片

根据特定字段(比如用户ID、订单时间)的范围，值在该区间的，划分到特定节点。

优点：集群扩容后，指定新的范围落在新节点即可，无需进行数据迁移。

缺点：如果按时间划分，数据热点分布不均(历史数冷当前数据热)，导致节点负荷不均。

ID取模分片

缺点：扩容后需要迁移数据。

一致性Hash算法

优点：扩容后无需迁移数据。

####

分库分表中间件

• sharding-jdbc（当当）
• TSharding（蘑菇街）
• Atlas（奇虎360）
• Cobar（阿里巴巴）
• MyCAT（基于Cobar）
• Oceanus（58同城）
• Vitess（谷歌）

平滑数据迁移方案

双倍扩容法

采用双倍扩容策略，避免数据迁移。具体操作如下(假设已有 2 个节点 A/B，要双倍扩容至 A/A2/B/B2 这 4 个节点)：

• 无需停止应用服务器；
• 新增两个数据库 A2/B2 作为从库，设置主从同步关系为：A=>A2、B=>B2，直至主从数据同步完毕(早期数据可手工同步)；
• 调整分片规则并使之生效：
• 原 ID%2=0 => A 改为 ID%4=0 => A, ID%4=2 => A2；
• 原 ID%2=1 => B 改为 ID%4=1 => B, ID%4=3 => B2。
• 解除数据库实例的主从同步关系，并使之生效；
• 此时，四个节点的数据都已完整，只是有冗余(多存了和自己配对的节点的那部分数据)，择机清除即可(过后随时进行，不影响业务)。

一致性hash算法

通过一致性hash算法，新加入的节点复制后面节点的数据即可

高可用

从mysql高可用架构看高可用架构设计

主从+keepalived

双主+keepalived，虽然是双主，建议写入节点只有一个，另外一个做failover用(failover时存在主键id冲突问题)，MySQL高可用架构：mysql+keepalived实现，如果两个master都可以写，可以解决写库单点压力的问题，但是同时也引入了一致性问题，比如并发写入时主键id冲突(解决办法1：利用分布式id，去除自增主键id，解决方法2：两个库id步长设置不一样，扩展性较差)

MMM

MHA

参考

MySQL 隔离级别

MySQL中的锁（表锁、行锁，共享锁，排它锁，间隙锁）

MySQL锁总结

MySQL优化

MySQL性能优化(六)：分区

MySQL设计之三范式的理解

数据库分库分表思路

分布式事务XA

XA 分布式事务原理

浅析B树的分裂过程

MYSQL之B+TREE索引原理

B+树算法在mysql中能存多少行数据？

mysql b+树能存多少条数据？b+树每层有多少分支？

谈谈你对Mysql的MVCC的理解？

以交易系统为例，看分布式事务架构的五大演进

MySQL中基于XA实现的分布式事务，Java JTA实现

MySQL是怎么保证数据不丢的？redo log/binlog

MySQL redo log 与 binlog 的区别

InnoDB聚集索引普通索引，回表，索引覆盖

mysql-覆盖索引

联合索引在B+树上的结构介绍

讲真，MySQL索引优化看这篇文章就够了

**MySQL的SQL执行计划

MySQL 分库分表及其平滑扩容方案

对一致性Hash算法，Java代码实现的深入研究

Mysql 分区表-分区操作

一文看懂mysql数据库分区表概念、类型、适用场景、优缺点及原理

InnoDB 的意向锁有什么作用？

***我以为我对Mysql事务很熟，直到我遇到了阿里面试官

**MySQL的又一神器-锁

**MySQL日志系统：redo log、binlog、undo log 区别与作用

**MySQL事务实现原理之Redo Log

• 前一篇: spring-cloud-alibaba-rocketmq
• 后一篇: yapi

添加评论

文章目录