MySQL知识总结

一条大鱼

已于 2024-01-05 15:39:15 修改

阅读量956

点赞数 21

文章标签： mysql

于 2024-01-05 15:29:37 首次发布

本文链接：https://blog.csdn.net/weixin_47053593/article/details/135410822

版权

⼀、 MySQL 索引篇

索引介绍

索引是什么

官⽅介绍索引是帮助 MySQL ⾼效获取数据 的 数据结构 。更通俗的说，数据库索引好⽐是⼀本书前

⾯的⽬录，能 加快数据库的查询速度 。

⽅便查找 --- 检索

索引查询内容 --- 覆盖索引

排序

⼀般来说索引本身也很⼤，不可能全部存储在内存中，因此 索引往往是存储在磁盘上的⽂件中的

（可能存储在单独的索引⽂件中，也可能和数据⼀起存储在数据⽂件中）。

我们通常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯⼀索引等，没有特别说

明，默认都是使⽤ B+ 树结构组织（多路搜索树，并不⼀定是⼆叉的）的索引。

索引的优势和劣势

优势：

可以提⾼数据检索的效率，降低数据库的 IO 成本，类似于书的⽬录。 -- 检索通过 索引列对数据进⾏排序 ，降低数据排序的成本，降低了 CPU 的消耗。 -- 排序

被索引的列会⾃动进⾏排序，包括【单列索引】和【组合索引】，只是组合索引的排序要复

杂⼀些。

如果按照索引列的顺序进⾏排序，对应 order by 语句来说，效率就会提⾼很多。

where 索引列在存储引擎层处理

覆盖索引，不需要回表查询

劣势：

索引会占据磁盘空间

索引虽然会提⾼查询效率，但是会降低更新表的效率 。⽐如每次对表进⾏增删改操作， MySQL 不

仅要保存数据，还有保存或者更新对应的索引⽂件。

索引的分类

单列索引

普通索引： MySQL 中基本索引类型，没有什么限制，允许在定义索引的列中插⼊重复值和空值，

纯粹为了查询数据更快⼀点。 add index

唯⼀索引：索引列中的值必须是唯⼀的，但是允许为空值 . add unique index

主键索引：是⼀种特殊的唯⼀索引，不允许有空值。 pk

组合索引

在表中的 多个字段组合上创建的索引 add index(col1,col2..)

组合索引的使⽤，需要遵循 最左前缀原则（最左匹配原则，后⾯⾼级篇讲解） 。

⼀般情况下， 建议使⽤组合索引代替单列索引 （主键索引除外，具体原因后⾯知识点讲解）。

全⽂索引

只有在 MyISAM 引擎、 InnoDB （ 5.6 以后）上才能使⽤，⽽且只能在 CHAR,VARCHAR,TEXT 类型字段上

使⽤全⽂索引。 fulltext

优先级最⾼先执⾏不会执⾏其他索引

存储引擎决定执⾏⼀个索引

空间索引

不做介绍，⼀般使⽤不到。

索引的使⽤

创建索引

单列索引之普通索引单列索引之唯⼀索引

单列索引之全⽂索引

组合索引

删除索引

查看索引

索引原理分析

索引的存储结构

索引存储结构

索引是在 存储引擎中实现 的，也就是说不同的存储引擎，会使⽤不同的索引

MyISAM 和 InnoDB 存储引擎 ：只⽀持 B+ TREE 索引，也就是说 默认使⽤ BTREE ，不能够更换

MEMORY/HEAP 存储引擎：⽀持 HASH 和 BTREE 索引

B 树和 B+ 树

数据结构示例⽹站：

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

B 树图示

CREATE INDEX index_name ON table ( column (length)) ;

ALTER TABLE table_name ADD INDEX index_name ( column (length)) ;

CREATE UNIQUE INDEX index_name ON table ( column (length)) ;

alter table table_name add unique index index_name( column );

CREATE FULLTEXT INDEX index_name ON table ( column (length)) ;

alter table table_name add fulltext index_name( column )

ALTER TABLE article ADD INDEX index_titme_time (title( 50 ), time ( 10 )) ;

DROP INDEX index_name ON table

SHOW INDEX FROM table_name \G B 树是为了磁盘或其它存储设备⽽设计的⼀种多叉（下⾯你会看到，相对于⼆叉， B 树每个内结点有多个

分⽀，即多叉）平衡查找树。多叉平衡

B 树的⾼度⼀般都是在 2-4 这个⾼度，树的⾼度直接影响 IO 读写的次数。

如果是三层树结构 --- ⽀撑的数据可以达到 20G ，如果是四层树结构 --- ⽀撑的数据可以达到⼏⼗ T

B 树和 B+ 树的区别

B 树和 B+ 树的最⼤区别在于 ⾮叶⼦节点是否存储数据 的问题。

⾮聚集索引（ MyISAM ）

B+ 树叶⼦节点只会存储数据⾏（数据⽂件）的指针，简单来说 数据和索引不在⼀起 ，就是⾮聚集

索引。

⾮聚集索引包含主键索引和辅助索引都会存储指针的值

主键索引

- B 树是⾮叶⼦节点和叶⼦节点都会存储数据。

- B+ 树只有叶⼦节点才会存储数据，⽽且存储的数据都是在⼀⾏上，⽽且这些数据都是有指针指向的，也

就是有顺序的。索引列 order by 这⾥设表⼀共有三列 , 假设我们以 Col1 为主键 , 则上图是⼀个 MyISAM 表的主索引 (Primary key) 示意。

可以看出 MyISAM 的索引⽂件仅仅保存数据记录的地址。

辅助索引（次要索引）

在 MyISAM 中 , 主索引和辅助索引 (Secondary key) 在结构上没有任何区别 , 只是主索引要求 key 是唯⼀的 ,

⽽辅助索引的 key 可以重复。如果我们在 Col2 上建⽴⼀个辅助索引 , 则此索引的结构如下图所示 同样也是⼀颗 B+Tree,data 域保存数据记录的地址。 因此 ,MyISAM 中索引检索的算法为⾸先按照

B+Tree 搜索算法搜索索引 , 如果指定的 Key 存在 , 则取出其 data 域的值 , 然后以 data 域的值为地址 , 读取

相应数据记录。

聚集索引（ InnoDB ）

主键索引（聚集索引）的叶⼦节点会存储数据⾏， 也就是说数据和索引是在⼀起，这就是聚集索

引。

辅助索引只会存储主键值

如果没有没有主键，则使⽤唯⼀索引建⽴聚集索引；如果没有唯⼀索引， MySQL 会按照⼀定规则

创建聚集索引。

主键索引

1.InnoDB 要求表必须有主键 (MyISAM 可以没有 ), 如果没有显式指定 , 则 MySQL 系统会⾃动选择⼀个可以

唯⼀标识数据记录的列作为主键 , 如果不存在这种列 , 则 MySQL ⾃动为 InnoDB 表⽣成⼀个隐含字段作为

主键 , 类型为⻓整形。上图是 InnoDB 主索引 ( 同时也是数据⽂件 ) 的示意图 , 可以看到叶节点包含了完整的数据记录。这种索引

叫做聚集索引。因为 InnoDB 的数据⽂件本身要按主键聚集 ,

辅助索引（次要索引）

2. 第⼆个与 MyISAM 索引的不同是 InnoDB 的辅助索引 data 域存储相应记录主键的值⽽不是地址。换

句话说 ,InnoDB 的所有辅助索引都引⽤主键作为 data 域。

聚集索引这种实现⽅式使得按主键的搜索⼗分⾼效 , 但是辅助索引搜索需要检索两遍索引 : ⾸先检索辅助

索引获得主键 , 然后⽤主键到主索引中检索获得记录。

select * from user where name='Alice' 回表查询检索两次⾮主键索引 --- pk--- 索引 ---> 数据

select id,name from user where name='Alice' 不需要回表在辅助索引树上就可以查询到了覆盖索引

( 多⽤组合索引 ) 引申 : 为什么不建议使⽤过⻓的字段作为主键 ?

因为所有辅助索引都引⽤主索引 , 过⻓的主索引会令辅助索引变得过⼤。

同时 , 请尽量在 InnoDB 上采⽤⾃增字段做表的主键。

MyISAM 和 InnoDB 的存储结构图示

为了更形象说明这两种索引的区别 , 我们假想⼀个表如下图存储了 4 ⾏数据。其中 Id 作为主索引 ,

Name 作为辅助索引。 图示清晰的显示了聚簇索引和⾮聚簇索引的差异： 课堂主题

Mysql 组合索引、索引失效分析、表级锁介绍

课堂⽬标 课堂⽬标

掌握索引使⽤场景

理解组合索引的结构和掌握使⽤原则

使⽤ explain 查看 sql 执⾏计划

掌握 select_type 、 type 、 extra 等参数意义

理解索引失效⼝诀

编写使⽤索引的 sql

掌握 MySQL 的锁的分类

理解表级锁和元数据锁

知识要点

哪些情况需要创建索引

1. 主键⾃动建⽴唯⼀索引

2. 频繁作为查询条件的字段应该创建索引

3. 多表关联查询中，关联字段应该创建索引 on 两边都要创建索引

4. 查询中排序的字段，应该创建索引

5. 频繁查找字段覆盖索引

6. 查询中统计或者分组字段，应该创建索引 group by

哪些情况不需要创建索引

1. 表记录太少

2. 经常进⾏增删改操作的表

3. 频繁更新的字段

4. where 条件⾥使⽤频率不⾼的字段

为什么使⽤组合索引

mysql 创建组合索引的规则是⾸先会对组合索引的最左边的，也就是第⼀个 name 字段的数据进⾏排

序，在第⼀个字段的排序基础上，然后再对后⾯第⼆个的 cid 字段进⾏排序。其实就相当于实现了类似

order by name cid 这样⼀种排序规则。

为了节省 mysql 索引存储空间以及提升搜索性能 ，可建⽴组合索引（ 能使⽤组合索引就不使⽤单列索

引）

例如：

创建组合索引（相当于建⽴了 col1,col1 col2,col1 col2 col3 三个索引）：

ALTER TABLE 'table_name' ADD INDEX index_name( 'col1' , 'col2' , 'col3' ）⼀颗索引树上创建 3 个索引：省空间

三颗索引树上分别创建 1 个索引

更容易实现覆盖索引

使⽤遵循最左前缀原则

1 、前缀索引 like a%

2 、从左向右匹配直到遇到范围查询 > < between like

建⽴组合索引 (a,b,c,d)

where a=1 and b=1 and c>3 and d=1

到 c>3 停⽌了所以 d ⽤不到索引了

怎么办？

（ a,b,d,c ）

案例

索引失效

查看执⾏计划

介绍

MySQL 提供了⼀个 EXPLAIN 命令 , 它可以对 SELECT 语句的执⾏计划进⾏分析 , 并输出 SELECT 执⾏的

详细信息 , 以供开发⼈员针对性优化 .

使⽤ explain 这个命令来查看⼀个这些 SQL 语句的执⾏计划，查看该 SQL 语句有没有使⽤上了索引，有没

有做全表扫描，这都可以通过 explain 命令来查看。

可以通过 explain 命令深⼊了解 MySQL 的基于开销的优化器，还可以获得很多可能被优化器考虑到的访

问策略的细节，以及当运⾏ SQL 语句时哪种策略预计会被优化器采⽤。

EXPLAIN 命令⽤法⼗分简单 , 在 SELECT 语句前加上 explain 就可以了 , 例如 :

mysql> create table t1 (id int primary key ,a int ,b int,c int,d int);

mysql> alter table t1 add index idx_com(a,b,c,d);

mysql> drop index idx_com on t1;

mysql> create index idx_com on t1(a,b,d,c); 参数说明

expain 出来的信息有 10 列，分别是

案例表

id 、 select_type 、 table 、 type 、 possible_keys 、 key 、 key_len 、 ref 、 rows 、 Extra

-- ⽤户表

create table tuser(

id int primary key,

loginname varchar ( 100 ),

name varchar ( 100 ),

age int ,

sex char ( 1 ),

dep int ,

address varchar ( 100 )

);

-- 部⻔表

create table tdep(

id int primary key,

name varchar ( 100 )

);

-- 地址表

create table taddr(

id int primary key,

addr varchar ( 100 )

);

-- 创建普通索引

mysql> alter table tuser add index idx_dep(dep);

-- 创建唯⼀索引

mysql> alter table tuser add unique index idx_loginname(loginname);

-- 创建组合索引

mysql> alter table tuser add index idx_name_age_sex(name,age,sex);

-- 创建全⽂索引

mysql> alter table taddr add fulltext ft_addr(addr); id

每个 SELECT 语句都会⾃动分配的⼀个唯⼀标识符 .

表示查询中操作表的顺序，有三种情况：

id 相同：执⾏顺序由上到下

id 不同：如果是⼦查询， id 号会⾃增， id 越⼤，优先级越⾼ 。

id 相同的不同的同时存在

id 列为 null 的就表示这是⼀个结果集，不需要使⽤它来进⾏查询。

select_type （重要）

查询类型 ，主要⽤于区别 普通查询、联合查询 (union 、 union all) 、⼦查询等复杂查询 。

simple

表示不需要 union 操作或者不包含⼦查询的简单 select 查询。有连接查询时，外层的查询为 simple ，且

只有⼀个

primary

⼀个需要 union 操作或者含有⼦查询的 select ，位于最外层的单位查询的 select_type 即为 primary 。且只

有⼀个

mysql> explain select * from tuser;

+----+-------------+-------+------+---------------+------+---------+------+---

---+-------+

rows | Extra |

+----+-------------+-------+------+---------------+------+---------+------+---

---+-------+

1 | NULL |

+----+-------------+-------+------+---------------+------+---------+------+---

---+-------+ subquery

除了 from 字句中包含的⼦查询外，其他地⽅出现的⼦查询都可能是 subquery

dependent subquery

与 dependent union 类似，表示这个 subquery 的查询要受到外部表查询的影响

mysql> explain select ( select name from tuser) from tuser ;

+----+-------------+-------+-------+---------------+------------------+-------

--+------+------+-------------+

key_len | ref | rows | Extra |

+----+-------------+-------+-------+---------------+------------------+-------

--+------+------+-------------+

| NULL | 1 | Using index |

| NULL | 1 | Using index |

+----+-------------+-------+-------+---------------+------------------+-------

--+------+------+-------------+

mysql> explain select * from tuser where id = (select max(id) from tuser);

+----+-------------+---

----+-------+---------------+---------+---------+-------+------+--------------

----------------+

| rows | Extra |

+----+-------------+-------+-------+---------------+---------+---------+------

-+------+------------------------------+

| 1 | NULL |

| NULL | Select tables optimized away |

+----+-------------+-------+-------+---------------+---------+---------+------

-+------+------------------------------+ union

union 连接的两个 select 查询，第⼀个查询是 PRIMARY ，除了第⼀个表外，第⼆个以后的表 select_type

都是 union

dependent union

与 union ⼀样，出现在 union 或 union all 语句中，但是这个查询要受到外部查询的影响

mysql> explain select id,name,( select name from tdep a where a .id =b .dep ) from

tuser b;

+----+--------------------+-------+--------+---------------+---------+--------

-+-------------+------+-------+

| ref | rows | Extra |

+----+--------------------+-------+--------+---------------+---------+--------

-+-------------+------+-------+

| 1 | PRIMARY | b | ALL | NULL | NULL | NULL

| NULL | 2 | NULL |

| 2 | DEPENDENT SUBQUERY | a | eq_ref | PRIMARY | PRIMARY | 4

| demo1 .b.dep | 1 | NULL |

+----+--------------------+-------+--------+---------------+---------+--------

-+-------------+------+-------+

mysql> explain select * from tuser where sex= '1' union select * from tuser

where sex= '2' ;

+----+--------------+------------+------+---------------+------+---------+----

--+------+-----------------+

| rows | Extra |

+----+--------------+------------+------+---------------+------+---------+----

--+------+-----------------+

NULL | 2 | Using where |

NULL | 2 | Using where |

NULL | NULL | Using temporary |

+----+--------------+------------+------+---------------+------+---------+----

--+------+-----------------+ union result

包含 union 的结果集，在 union 和 union all 语句中 , 因为它不需要参与查询，所以 id 字段为 null

derived

from 字句中出现的⼦查询，也叫做派⽣表，其他数据库中可能叫做内联视图或嵌套 select

table

显示的查询表名，如果查询使⽤了别名，那么这⾥显示的是别名

如果不涉及对数据表的操作，那么这显示为 null

如果显示为尖括号括起来的就表示这个是临时表，后边的 N 就是执⾏计划中的 id ，表示结果来⾃于

这个查询产⽣。

mysql> explain select * from tuser where sex in ( select sex from tuser where

sex= '1' union select sex from tuser where sex= '2' );

+----+--------------------+------------+-------+---------------+--------------

----+---------+------+------+--------------------------+

+----+--------------------+------------+-------+---------------+--------------

----+---------+------+------+--------------------------+

+----+--------------------+------------+-------+---------------+--------------

----+---------+------+------+--------------------------+

mysql> explain select * from ( select * from tuser where sex= '1' ) b;

+----+-------------+------------+------+---------------+------+---------+-----

-+------+-------------+

| rows | Extra |

+----+-------------+------------+------+---------------+------+---------+-----

-+------+-------------+

| 2 | NULL |

| 2 | Using where |

+----+-------------+------------+------+---------------+------+---------+-----

-+------+-------------+ 如果是尖括号括起来的 <union M,N> ，与类似，也是⼀个临时表，表示这个结果来⾃于 union 查询

的 id 为 M,N 的结果集。

type （重要）

依次从好到差：

除了 all 之外，其他的 type 都可以使⽤到索引，除了 index_merge 之外，其他的 type 只可以⽤到⼀个索

引

注意事项：

system

表中只有⼀⾏数据或者是空表。

const （重要）

使⽤ 唯⼀索引或者主键 ，返回记录⼀定是 1 ⾏记录的等值 where 条件时，通常 type 是 const 。其他数据库

也叫做唯⼀索引扫描

system ， const ， eq_ref ， ref ， fulltext ， ref_or_null ， unique_subquery ，

index_subquery ， range ， index_merge ， index ， ALL

最少要索引使⽤到 range 级别。

mysql> explain select * from ( select * from tuser where id= 1 ) a;

+----+-------------+------------+--------+---------------+---------+---------

+-------+------+-------+

ref | rows | Extra |

+----+-------------+------------+--------+---------------+---------+---------

+-------+------+-------+

NULL | 1 | NULL |

const | 1 | NULL |

+----+-------------+------------+--------+---------------+---------+---------

+-------+------+-------+ eq_ref （重要）

关键字 : 连接字段 主键或者唯⼀性索引 。

此类型通常出现在多表的 join 查询 , 表示对于前表的每⼀个结果 , 都只能匹配到后表的⼀⾏结果 . 并且查

询的⽐较操作通常是 '=', 查询效率较⾼ .

mysql> explain select * from tuser where id= 1 ;

+----+-------------+-------+-------+---------------+---------+---------+------

-+------+-------+

| rows | Extra |

+----+-------------+-------+-------+---------------+---------+---------+------

-+------+-------+

| 1 | NULL |

+----+-------------+-------+-------+---------------+---------+---------+------

-+------+-------+

mysql> explain select * from tuser where loginname = 'zy' ;

+----+-------------+-------+-------+---------------+---------------+---------

+-------+------+-------+

ref | rows | Extra |

+----+-------------+-------+-------+---------------+---------------+---------

+-------+------+-------+

const | 1 | NULL |

+----+-------------+-------+-------+---------------+---------------+---------

+-------+------+-------+

mysql> explain select a .id from tuser a left join tdep b on a .dep =b .id ;

+----+-------------+-------+--------+---------------+---------+---------+-----

--------+------+-------------+

| rows | Extra |

+----+-------------+-------+--------+---------------+---------+---------+-----

--------+------+-------------+

| 2 | Using index |

| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 |

demo1 .a.dep | 1 | Using index |

+----+-------------+-------+--------+---------------+---------+---------+-----

--------+------+-------------+ ref （重要）

针对⾮唯⼀性索引 ，使⽤ 等值（ = ）查询 ⾮主键。或者是使⽤了 最左前缀规则索引的查询 。

fulltext

-- ⾮唯⼀索引

mysql> explain select * from tuser where dep= 1 ;

+----+-------------+-------+------+---------------+---------+---------+-------

+------+-------+

| rows | Extra |

+----+-------------+-------+------+---------------+---------+---------+-------

+------+-------+

| 1 | NULL |

+----+-------------+-------+------+---------------+---------+---------+-------

+------+-------+

-- 等值⾮主键连接

mysql> explain select a .id from tuser a left join tdep b on a .name =b .name ;

+----+-------------+-------+-------+---------------+------------------+-------

--+--------------+------+--------------------------+

key_len | ref | rows | Extra |

+----+-------------+-------+-------+---------------+------------------+-------

--+--------------+------+--------------------------+

| 1 | SIMPLE | a | index | NULL | idx_name_age_sex | 312

| NULL | 2 | Using index |

| 1 | SIMPLE | b | ref | ind_name | ind_name | 72

| demo1 .a.name | 1 | Using where ; Using index |

+----+-------------+-------+-------+---------------+------------------+-------

--+--------------+------+--------------------------+

-- 最左前缀

mysql> explain select * from tuser where name = 'zhaoyun' ;

+----+-------------+-------+------+------------------+------------------+-----

----+-------+------+-----------------------+

key_len | ref | rows | Extra |

+----+-------------+-------+------+------------------+------------------+-----

----+-------+------+-----------------------+

| 1 | SIMPLE | tuser | ref | idx_name_age_sex | idx_name_age_sex | 303

| const | 1 | Using index condition |

+----+-------------+-------+------+------------------+------------------+-----

----+-------+------+-----------------------+

思考： explain select * from tuser where sex = '1' ; 全⽂索引检索，要注意，全⽂索引的优先级很⾼，若全⽂索引和普通索引同时存在时， mysql 不管代

价，优先选择使⽤全⽂索引

ref_or_null

与 ref ⽅法类似，只是增加了 null 值的⽐较。实际⽤的不多。

unique_subquery

⽤于 where 中的 in 形式⼦查询，⼦查询返回不重复值唯⼀值

index_subquery

⽤于 in 形式⼦查询使⽤到了辅助索引或者 in 常数列表，⼦查询可能返回重复值，可以使⽤索引将⼦查询

去重。

range （重要）

索引范围扫描 ，常⻅于使⽤ >,<,is null,between ,in ,like 等运算符的查询中。

mysql> explain select * from taddr where match(addr) against( 'bei' );

+----+-------------+-------+----------+---------------+---------+---------+---

---+------+-------------+

ref | rows | Extra |

+----+-------------+-------+----------+---------------+---------+---------+---

---+------+-------------+

NULL | 1 | Using where |

+----+-------------+-------+----------+---------------+---------+---------+---

---+------+-------------+

mysql> explain select id from tuser where id> 1 ;

+----+-------------+-------+-------+---------------+---------+---------+------

+------+-------------+

| rows | Extra |

+----+-------------+-------+-------+---------------+---------+---------+------

+------+-------------+

| 1 | Using where |

+----+-------------+-------+-------+---------------+---------+---------+------

+------+-------------+

--like 前缀索引

mysql> explain select * from tuser where name like 'zhao%' ;

+----+-------------+-------+-------+------------------+------------------+----

-----+------+------+-----------------------+

key_len | ref | rows | Extra | index_merge

表示查询使⽤了两个以上的索引，最后取交集或者并集，常⻅ and ， or 的条件使⽤了不同的索引，官⽅

排序这个在 ref_or_null 之后，但是实际上由于要读取所个索引，性能可能⼤部分时间都不如 range

index （重要）

关键字：条件是出现在索引树中的节点的。可能没有完全匹配索引。

索引全表扫描 ，把索引从头到尾扫⼀遍，常⻅于使⽤索引列就可以处理不需要读取数据⽂件的查询、可

以使⽤索引排序或者分组的查询。

+----+-------------+-------+-------+------------------+------------------+----

-----+------+------+-----------------------+

| NULL | 1 | Using index condition |

+----+-------------+-------+-------+------------------+------------------+----

-----+------+------+-----------------------+

注： like '%z' 不使⽤索引

-- 单索引

mysql> explain select loginname from tuser;

+----+-------------+-------+-------+---------------+---------------+---------

+------+------+-------------+

ref | rows | Extra |

+----+-------------+-------+-------+---------------+---------------+---------

+------+------+-------------+

NULL | 2 | Using index |

+----+-------------+-------+-------+---------------+---------------+---------

+------+------+-------------+

-- 组合索引

mysql> explain select age from tuser;

+----+-------------+-------+-------+---------------+------------------+-------

--+------+------+-------------+

key_len | ref | rows | Extra |

+----+-------------+-------+-------+---------------+------------------+-------

--+------+------+-------------+

| NULL | 2 | Using index |

+----+-------------+-------+-------+---------------+------------------+-------

--+------+------+-------------+

思考： explain select loginname,age from tuser; all （重要）

这个就是全表扫描数据⽂件，然后再在 server 层进⾏过滤 返回符合要求的记录。

possible_keys

此次查询中可能选⽤的索引，⼀个或多个

key

查询真正使⽤到的索引， select_type 为 index_merge 时，这⾥可能出现两个以上的索引，其他的

select_type 这⾥只会出现⼀个。

key_len

⽤于处理查询的索引⻓度，如果是单列索引，那就整个索引⻓度算进去，如果是多列索引，那么查

询不⼀定都能使⽤到所有的列，具体使⽤到了多少个列的索引，这⾥就会计算进去，没有使⽤到的

列，这⾥不会计算进去。

留意下这个列的值，算⼀下你的多列索引总⻓度就知道有没有使⽤到所有的列了。

另外， key_len 只计算 where 条件⽤到的索引⻓度，⽽排序和分组就算⽤到了索引，也不会计算到

key_len 中。

ref

如果是使⽤的常数等值查询，这⾥会显示 const

如果是连接查询，被驱动表的执⾏计划这⾥会显示驱动表的关联字段

如果是条件使⽤了表达式或者函数，或者条件列发⽣了内部隐式转换，这⾥可能显示为 func

rows

覆盖索引

mysql> explain select * from tuser;

+----+-------------+-------+------+---------------+------+---------+------+---

---+-------+

rows | Extra |

+----+-------------+-------+------+---------------+------+---------+------+---

---+-------+

2 | NULL |

+----+-------------+-------+------+---------------+------+---------+------+---

---+-------+

回表查询这⾥是执⾏计划中估算的扫描⾏数，不是精确值（ InnoDB 不是精确的值， MyISAM 是精确的值，主要原

因是 InnoDB ⾥⾯使⽤了 MVCC 并发机制）

extra （重要）

这个列包含不适合在其他列中显示单⼗分重要的额外的信息，这个列可以显示的信息⾮常多，有⼏⼗

种，常⽤的有

using temporary

表示使⽤了临时表存储中间结果。

MySQL 在对查询结果 order by 和 group by 时使⽤临时表

临时表可以是内存临时表和磁盘临时表，执⾏计划中看不出来，需要查看 status 变量，

used_tmp_table ， used_tmp_disk_table 才能看出来。

no tables used

不带 from 字句的查询或者 From dual 查询

使⽤ not in() 形式⼦查询或 not exists 运算符的连接查询，这种叫做反连接

即，⼀般连接查询是先查询内表，再查询外表，反连接就是先查询外表，再查询内表。

using filesort （重要）

排序时⽆法使⽤到索引时，就会出现这个。常⻅于 order by 和 group by 语句中

mysql> explain select distinct a.id from tuser a,tdep b where a.dep=b.id;

+----+-------------+-------+--------+-------------------------------------

-----------+---------+---------+------------+------+----------------------

---------------------+

+----+-------------+-------+--------+-------------------------------------

-----------+---------+---------+------------+------+----------------------

---------------------+

| 1 | SIMPLE | a | index |

PRIMARY,idx_loginname,idx_name_age_sex,idx_dep | idx_dep | 5 | NULL

| 2 | Using where; Using index; Using temporary |

+----+-------------+-------+--------+-------------------------------------

-----------+---------+---------+------------+------+----------------------

---------------------+ 说明 MySQL 会使⽤⼀个外部的索引排序，⽽不是按照索引顺序进⾏读取。

MySQL 中⽆法利⽤索引完成的排序操作称为 “ ⽂件排序 ”

using index （重要）

查询时 不需要回表查询 ，直接通过索引就可以获取查询的数据。

表示相应的 SELECT 查询中使⽤到了 覆盖索引（ Covering Index ），避免访问表的数据⾏，效率不

错！

如果同时出现 Using Where ，说明索引被⽤来执⾏查找索引键值

如果没有同时出现 Using Where ，表明索引⽤来读取数据⽽⾮执⾏查找动作。

using where （重要）

表示存储引擎返回的记录并不是所有的都满⾜查询条件，需要在 server 层进⾏过滤。

mysql> explain select * from tuser order by address;

+----+-------------+-------+------+---------------+------+---------+------+---

---+----------------+

rows | Extra |

+----+-------------+-------+------+---------------+------+---------+------+---

---+----------------+

2 | Using filesort |

+----+-------------+-------+------+---------------+------+---------+------+---

---+----------------+

mysql> explain select name,age,sex from tuser ;

+----+-------------+-------+-------+---------------+------------------+-------

--+------+------+-------------+

key_len | ref | rows | Extra |

+----+-------------+-------+-------+---------------+------------------+-------

--+------+------+-------------+

| NULL | 2 | Using index |

+----+-------------+-------+-------+---------------+------------------+-------

--+------+------+-------------+

全值匹配覆盖索引

-- 查询条件⽆索引

mysql> explain select * from tuser where address= 'beijing' ; +----+-------------+-------+------+---------------+------+---------+------+---

---+-------------+

rows | Extra |

+----+-------------+-------+------+---------------+------+---------+------+---

---+-------------+

2 | Using where |

+----+-------------+-------+------+---------------+------+---------+------+---

---+-------------+

-- 索引失效

mysql> explain select * from tuser where age= 1 ;

+----+-------------+-------+------+---------------+------+---------+------+---

---+-------------+

rows | Extra |

+----+-------------+-------+------+---------------+------+---------+------+---

---+-------------+

2 | Using where |

+----+-------------+-------+------+---------------+------+---------+------+---

---+-------------+

mysql> explain select * from tuser where id in ( 1 , 2 );

+----+-------------+-------+-------+---------------+---------+---------+------

+------+-------------+

| rows | Extra |

+----+-------------+-------+-------+---------------+---------+---------+------

+------+-------------+

| 2 | Using where |

+----+-------------+-------+-------+---------------+---------+---------+------

+------+-------------+

查询条件中分为限制条件和检查条件， 5.6 之前，存储引擎只能根据限制条件扫描数据并返回，然

后 server 层根据检查条件进⾏过滤再返回真正符合查询的数据。 5.6.x 之后⽀持 ICP 特性，可以把检

查条件也下推到存储引擎层，不符合检查条件和限制条件的数据，直接不读取，这样就⼤⼤减少了

存储引擎扫描的记录数量。 extra 列显示 using index condition firstmatch(tb_name)

5.6.x 开始引⼊的优化⼦查询的新特性之⼀，常⻅于 where 字句含有 in() 类型的⼦查询。如果内表的数据

量⽐较⼤，就可能出现这个

loosescan(m..n)

5.6.x 之后引⼊的优化⼦查询的新特性之⼀，在 in() 类型的⼦查询中，⼦查询返回的可能有重复记录时，

就可能出现这个

除了这些之外，还有很多查询数据字典库，执⾏计划过程中就发现不可能存在结果的⼀些提示信息

filtered

使⽤ explain extended 时会出现这个列， 5.7 之后的版本默认就有这个字段，不需要使⽤ explain

extended 了。这个字段表示存储引擎返回的数据在 server 层过滤后，剩下多少满⾜查询的记录数量的⽐

例，注意是百分⽐，不是具体记录数。

参考⽹站

https://segmentfault.com/a/1190000008131735

https://blog.csdn.net/rewiner120/article/details/70598797

⼆、 MySQL 锁篇

MySQL 锁介绍

按照锁的粒度来说， MySQL 主要包含三种类型（级别）的锁定机制：

mysql> explain select * from tuser where name= 'asd' ;

+----+-------------+-------+------+------------------+------------------+-----

----+-------+------+-----------------------+

key_len | ref | rows | Extra |

+----+-------------+-------+------+------------------+------------------+-----

----+-------+------+-----------------------+

| 1 | SIMPLE | tuser | ref | idx_name_age_sex | idx_name_age_sex | 303

| const | 1 | Using index condition |

+----+-------------+-------+------+------------------+------------------+-----

----+-------+------+-----------------------+ 按照锁的功能来说分为： 共享读锁 和 排他写锁 。

按照锁的实现⽅式分为： 悲观锁 和 乐观锁 （使⽤某⼀版本列或者唯⼀列进⾏逻辑控制）

表级锁和⾏级锁的区别：

MySQL 表级锁

表级锁介绍

由 MySQL SQL layer 层实现

MySQL 的表级锁有两种：

MySQL 实现的表级锁定的争⽤状态变量：

- 全局锁：锁的是整个 database 。由 MySQL 的 SQL layer 层实现的

- 表级锁：锁的是某个 table 。由 MySQL 的 SQL layer 层实现的

- ⾏级锁：锁的是某⾏数据，也可能锁定⾏之间的间隙。由某些存储引擎实现，⽐如 InnoDB 。

表级锁：开销⼩，加锁快；不会出现死锁；锁定粒度⼤，发⽣锁冲突的概率最⾼，并发度最低；

⾏级锁：开销⼤，加锁慢；会出现死锁；锁定粒度最⼩，发⽣锁冲突的概率最低，并发度也最⾼；

⼀种是表锁。

⼀种是元数据锁（ meta data lock ， MDL) 。

mysql> show status like 'table%'; 表锁介绍

表锁有两种表现形式：

⼿动增加表锁

查看表锁情况

删除表锁

表锁演示

环境准备

- table_locks_immediate ：产⽣表级锁定的次数；

- table_locks_waited ：出现表级锁定争⽤⽽发⽣等待的次数；

表共享读锁（ Table Read Lock ）

表独占写锁（ Table Write Lock ）

lock table 表名称 read ( write ), 表名称 2 read ( write ) ，其他 ;

show open tables ;

unlock tables ; 读锁演示

1 、表读锁

session1 （ Navicat ）、 session2 （ mysql ）

-- 新建表

CREATE TABLE mylock (

id int ( 11 ) NOT NULL AUTO_INCREMENT,

NAME varchar ( 20 ) DEFAULT NULL ,

PRIMARY KEY (id)

);

INSERT INTO mylock (id,NAME) VALUES ( 1 , 'a' );

INSERT INTO mylock (id,NAME) VALUES ( 2 , 'b' );

INSERT INTO mylock (id,NAME) VALUES ( 3 , 'c' );

INSERT INTO mylock (id,NAME) VALUES ( 4 , 'd' ); 1 、 session1: lock table mylock read; -- 给 mylock 表加读锁

2 、 session1: select * from mylock; -- 可以查询

3 、 session1 ： select * from tdep; -- 不能访问⾮锁定表

4 、 session2 ： select * from mylock; -- 可以查询没有锁

5 、 session2 ： update mylock set name= 'x' where id= 2 ; -- 修改阻塞 , ⾃动加⾏写锁

6 、 session1 ： unlock tables; -- 释放表锁

7 、 session2 ： Rows matched: 1 Changed: 1 Warnings: 0 -- 修改执⾏完成

8 、 session1 ： select * from tdep; -- 可以访问

2 、表写锁

session1 （ Navicat ）、 session2 （ mysql ） 课堂主题

Mysql 元数据锁、⾏锁、 MySQL 事务介绍、 InnoDB 架构

课堂⽬标

理解 MySQL 元数据锁的意义和使⽤场景

理解 MySQL ⾏锁的意义和使⽤场景

掌握 MySQL 记录锁和间隙锁的使⽤区别

掌握死锁的原理和死锁场景

理解事务的概念和四⼤特征（ ACID ）

掌握 InnoDB 的架构和组件作⽤

理解预写机制、双写机制、 RedoLog 和 UndoLog 的作⽤和⽇志落盘

知识要点

元数据锁介绍

MDL 不需要显式使⽤，在访问⼀个表的时候会被⾃动加上 。 MDL 的作⽤是，保证读写的正确性。你可

以想象⼀下，如果⼀个查询正在遍历⼀个表中的数据，⽽执⾏期间另⼀个线程对这个表结构做变更，删

了⼀列，那么查询线程拿到的结果跟表结构对不上，肯定是不⾏的。

因此，在 MySQL 5.5 版本中引⼊了 MDL ， 当对⼀个表做增删改查操作的时候，加 MDL 读锁； 当要对

表做结构变更操作的时候，加 MDL 写锁。

读锁之间不互斥，因此你可以有多个线程同时对⼀张表增删改查。

读写锁之间、写锁之间是互斥的，⽤来保证变更表结构操作的安全性。因此，如果有两个线程要同

时给⼀个表加字段，其中⼀个要等另⼀个执⾏完才能开始执⾏。

元数据锁演示

1 、 session1: lock table mylock write; -- 给 mylock 表加写锁

2 、 session1: select * from mylock; -- 可以查询

3 、 session1 ： select * from tdep; -- 不能访问⾮锁定表

4 、 session1 ： update mylock set name= 'y' where id= 2 ; -- 可以执⾏

5 、 session2 ： select * from mylock; -- 查询阻塞

6 、 session1 ： unlock tables; -- 释放表锁

7 、 session2 ： 4 rows in set ( 22.57 sec) -- 查询执⾏完成

8 、 session1 ： select * from tdep; -- 可以访问 session1 （ Navicat ）、 session2 （ mysql ）

1 、 session1: begin ; -- 开启事务

select * from mylock; -- 加 MDL 读锁

2 、 session2: alter table mylock add f int ; -- 修改阻塞

3 、 session1 ： commit; -- 提交事务或者 rollback 释放读锁

4 、 session2 ： Query OK, 0 rows affected ( 38.67 sec) -- 修改完成

Records: 0 Duplicates: 0 Warnings: 0

我们可以看到 session A 先启动，这时候会对表 t 加⼀个 MDL 读锁。由于 session B 需要的也是 MDL

读锁，因此可以正常执⾏。

之后 session C 会被 blocked ，是因为 session A 的 MDL 读锁还没有释放，⽽ session C 需要 MDL 写

锁，因此只能被阻塞。

如果只有 session C ⾃⼰被阻塞还没什么关系，但是之后所有要在表 t 上新申请 MDL 读锁的请求也会

被 session C 阻塞。前⾯我们说了，所有对表的增删改查操作都需要先申请 MDL 读锁，就都被锁住，

等于这个表现在完全不可读写了。你现在应该知道了，事务中的 MDL 锁，在语句执⾏开始时申请，但是语句结束后并不会⻢上释放，⽽

会等到整个事务提交后再释放。

MySQL ⾏级锁

⾏级锁介绍

MySQL 的⾏级锁，是由存储引擎来实现的，利⽤存储引擎锁住索引项来实现的。这⾥我们主要讲解

InnoDB 的⾏级锁。

InnoDB 的⾏级锁，按照锁定范围来说，分为三种：

InnoDB 的⾏级锁，按照功能来说，分为两种： RR

对于 UPDATE 、 DELETE 和 INSERT 语句， InnoDB 会⾃动给涉及数据集加排他锁（ X) ；

对于普通 SELECT 语句， InnoDB 不会加任何锁，事务可以通过以下语句显示给记录集加共享锁或排他

锁。 ⼿动添加共享锁（ S ）：

⼿动添加排他锁（ x ）：

InnoDB 也实现了表级锁，也就是意向锁，意向锁是 mysql 内部使⽤的，不需要⽤户⼲预。

- 记录锁（ Record Locks ） : 锁定索引中⼀条记录。 id=1

- 间隙锁（ Gap Locks ） : 要么锁住索引记录中间的值，要么锁住第⼀个索引记录前⾯的值或者最后⼀个索

引记录后⾯的值。

- Next-Key Locks: 是索引记录上的记录锁和在索引记录之前的间隙锁的组合。

- 共享锁（ S ）：允许⼀个事务去读⼀⾏，阻⽌其他事务获得相同数据集的排他锁。

- 排他锁（ X ）：允许获得排他锁的事务更新数据，阻⽌其他事务取得相同数据集的共享读锁（不是读）

和排他写锁。

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

SELECT * FROM table_name WHERE ... FOR UPDATE 共享锁

（ S ）

排他锁

（ X ）

意向共享锁

（ IS ）

意向排他锁

（ IX ）

共享锁（ S ）

兼容

冲突

兼容

冲突

排他锁（ X ）

冲突

意向共享锁（ IS ）

兼容

冲突

兼容

意向排他锁

（ IX ）

冲突

兼容

意向锁和⾏锁可以共存，意向锁的主要作⽤是为了【全表更新数据】时的性能提升。否则在全表更

新数据时，需要先检索该表是否某些记录上⾯有⾏锁。

InnoDB ⾏锁是通过给索引上的 索引项加锁来实现的 ，因此 InnoDB 这种⾏锁实现特点意味着：只

有通过索引条件检索的数据， InnoDB 才使⽤⾏级锁，否则， InnoDB 将使⽤表锁！

Innodb 所使⽤的 ⾏级锁定 争⽤状态查看：

- 意向共享锁（ IS ）：事务打算给数据⾏加⾏共享锁，事务在给⼀个数据⾏加共享锁前必须先取得该表的

IS 锁。

- 意向排他锁（ IX ）：事务打算给数据⾏加⾏排他锁，事务在给⼀个数据⾏加排他锁前必须先取得该表的

IX 锁。

mysql> show status like 'innodb_row_lock%' ; 对于这 5 个状态变量，⽐较重要的主要是：

尤其是当等待次数很⾼，⽽且每次等待时⻓也不⼩的时候，我们就需要分析系统中为什么会有如此多的

等待，然后根据分析结果着⼿指定优化计划。

两阶段锁

传统 RDBMS 加锁的⼀个原则，就是 2PL (Two-Phase Locking ，⼆阶段锁 ) 。相对⽽⾔， 2PL ⽐较容易理

解，说的是锁操作分为两个阶段：加锁阶段与解锁阶段，并且保证加锁阶段与解锁阶段不相交。下⾯，

仍旧以 MySQL 为例，来简单看看 2PL 在 MySQL 中的实现。

- Innodb_row_lock_current_waits ：当前正在等待锁定的数量；

- Innodb_row_lock_time ：从系统启动到现在锁定总时间⻓度；

- Innodb_row_lock_time_avg ：每次等待所花平均时间；

- Innodb_row_lock_time_max ：从系统启动到现在等待最常的⼀次所花的时间；

- Innodb_row_lock_waits ：系统启动后到现在总共等待的次数；

- Innodb_row_lock_time_avg （等待平均时⻓）

- Innodb_row_lock_waits （等待总次数）

- Innodb_row_lock_time （等待总时⻓）这三项。从上图可以看出， 2PL 就是将加锁 / 解锁分为两个完全不相交的阶段。

InnoDB ⾏锁演示

⾏读锁

session1 （ Navicat ）、 session2 （ mysql ）

加锁阶段：只加锁，不放锁。

解锁阶段：只放锁，不加锁。 ⾏读锁升级为表锁

session1 （ Navicat ）、 session2 （ mysql ）

⾏写锁

session1 （ Navicat ）、 session2 （ mysql ）

查看⾏锁状态 show STATUS like 'innodb_row_lock%' ;

1 、 session1: begin ; -- 开启事务未提交

select * from mylock where ID= 1 lock in share mode; -- ⼿动加 id=1 的⾏

读锁 , 使⽤索引

2 、 session2 ： update mylock set name= 'y' where id= 2 ; -- 未锁定该⾏可以修改

3 、 session2 ： update mylock set name= 'y' where id= 1 ; -- 锁定该⾏修改阻塞

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

-- 锁定超时

4 、 session1: commit; -- 提交事务或者 rollback 释放读锁

5 、 session2 ： update mylock set name= 'y' where id= 1 ; -- 修改成功

Query OK, 1 row affected ( 0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

注：使⽤索引加⾏锁，未锁定的⾏可以访问

1 、 session1: begin ; -- 开启事务未提交

-- ⼿动加 name='c' 的⾏读锁 , 未使⽤索引

select * from mylock where name= 'c' lock in share mode;

2 、 session2 ： update mylock set name= 'y' where id= 2 ; -- 修改阻塞未⽤索引⾏锁升级为表

锁

3 、 session1: commit; -- 提交事务或者 rollback 释放读锁

4 、 session2 ： update mylock set name= 'y' where id= 2 ; -- 修改成功

Query OK, 1 row affected ( 0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

注：未使⽤索引⾏锁升级为表锁 间隙锁

间隙锁有两种情况

1 、防⽌插⼊间隙内的数据

2 、防⽌已有数据更新为间隙内的数据

1 、 session1: begin ; -- 开启事务未提交

-- ⼿动加 id=1 的⾏写锁 ,

select * from mylock where id= 1 for update ;

2 、 session2 ： select * from mylock where id= 2 ; -- 可以访问

3 、 session2: select * from mylock where id= 1 ; -- 可以读不加锁

4 、 session2: select * from mylock where id= 1 lock in share mode ; -- 加读

锁被阻塞

5 、 session1 ： commit; -- 提交事务或者 rollback 释放写锁

5 、 session2 ：执⾏成功

主键索引产⽣记录锁 session1 （ Navicat ）、 session2 （ mysql ）

案例演示：

mysql> create table news (id int , number int ,primary key (id));

mysql> insert into news values ( 1 , 2 );

......

-- 加⾮唯⼀索引

mysql> alter table news add index idx_num(number);

session 1 :

start transaction ;

select * from news where number= 4 for update ;

session 2 :

start transaction ;

insert into news value( 2 , 4 );# （阻塞）

insert into news value( 2 , 2 );# （阻塞）

insert into news value( 4 , 4 );# （阻塞）

insert into news value( 4 , 5 );# （阻塞）

insert into news value( 7 , 5 );# （执⾏成功）

insert into news value( 9 , 5 );# （执⾏成功）

insert into news value( 11 , 5 );# （执⾏成功）

````

注： id 和 number 都在间隙内则阻塞。

session 1 :

start transaction ;

select * from news where number= 13 for update ;

（ select * from news where id> 1 and id < 8 for update ; ）

session 2 :

start transaction ;

insert into news value( 11 , 5 );#( 执⾏成功 )

insert into news value( 12 , 11 );#( 执⾏成功 )

insert into news value( 14 , 11 );#( 阻塞 )

insert into news value( 15 , 12 );#( 阻塞 )

检索条件 number= 13 , 向左取得最靠近的值 11 作为左区间，向右由于没有记录因此取得⽆穷⼤作为右区间，

因此， session 1 的间隙锁的范围（ 11 ，⽆穷⼤）

注：⾮主键索引产⽣间隙锁，主键范围

三、 MySQL 分库分表篇 三、 MySQL 分库分表篇

传统项⽬结构

数据库性能瓶颈：

1 、数据库连接数有限

MySQL 数据库默认 100 个连接、单机最⼤ 1500 连接。

2 、表数据量

1 、表数量多，成百上千

2 、单表数据，千万级别

3 、索引，命中率问题，索引存磁盘，占空间

3 、硬件问题

性能指标：单表 QPS 、 TPS

数据库性能优化

1 、参数优化

2 、缓存、索引

3 、读写分离 4 、分库分表

分库分表介绍

使⽤背景

当【表的数量】达到了⼏百上千张表时，众多的业务模块都访问这个数据库，压⼒会⽐较⼤，考虑

对其进⾏分库。

当【表的数据】达到了⼏千万级别，在做很多操作都⽐较吃⼒ , 所以，考虑对其进⾏分库或者分表

数据切分（ sharding ）⽅案

数据的切分（ Sharding ）根据其切分规则的类型，可以分为两种切分模式：

垂直切分： 按照业务模块进⾏切分，将不同模块的表切分到不同的数据库中。

⽔平切分： 将⼀张⼤表按照⼀定的切分规则，按照⾏切分成不同的表或者切分到不同的库中。

⽔平切分规则

常⽤的切分规则有以下⼏种：

按照 ID 取模： 对 ID 进⾏取模，余数决定该⾏数据切分到哪个表或者库中

按照⽇期： 按照年⽉⽇，将数据切分到不同的表或者库中

按照范围： 可以对某⼀列按照范围进⾏切分，不同的范围切分到不同的表或者数据库中。

切分原则

第⼀原则： 能不切分尽量不要切分。 第⼆原则： 如果要切分⼀定要选择合适的切分规则，提前规划好。

第三原则： 数据切分尽量通过数据冗余或表分组（ Table Group ）来 降低跨库 Join 的可能。

第四原则： 由于数据库中间件对数据 Join 实现的优劣难以把握，⽽且实现⾼性能难度极⼤，业务

读取尽量少使⽤多表 Join 。

分库分表需要解决的问题

分布式事务问题

强⼀致性事务（同步）

最终⼀致性事务（异步思想）利⽤记录⽇志等⽅式

分布式主键 ID 问题

redis incr 命令

数据库（⽣成主键）

UUID

snowflake 算法（ https://www.sohu.com/a/232008315_453160 ）

跨库 join 问题

通过业务分析，将不同库的 join 查询拆分成多个 select

建⽴全局表（每个库都有⼀个相同的表）

冗余字段（不符合数据库三范式）

E-R 分⽚（将有 ER 关系的记录都存储到⼀个库中）

最多⽀持跨两张表跨库的 join

跨库 count 、 order by 、 group by 问题

分库分表实现技术

阿⾥的 TDDL 、 Cobar

基于阿⾥ Cobar 开发的 Mycat

当当⽹的 sharding-jdbc

课堂主题

MyCat 介绍、分⽚规则、案例展示

课堂⽬标

理解 MyCat 的架构和原理

本地事务： ACID

分布式事务：根据百度百科的定义， CAP 定理⼜称 CAP 原则，指的是在⼀个分布式系统中，

Consistency （⼀致性）、 Availability （可⽤性）、 Partition tolerance （分区容错性）。⼀

致性是强⼀致性。 CAP 理论最多只能同时满⾜两个。

BASE ：基本可⽤ + 软状态 + 最终⼀致性会安装和启动 MyCat

会配置 MyCat

熟悉常⻅分⽚规则和优劣

掌握 mycat 分库分表的⽅法

Mycat 介绍

什么是 Mycat ？

官⽅⽹站： http://www.mycat.org.cn/ http://www.mycat.io/

db proxy Mycat

Mycat 架构

Mycat 核⼼概念 Schema ：由它指定逻辑数据库（相当于 MySQL 的 database 数据库）

Table ：逻辑表（相当于 MySQL 的 table 表）

DataNode ：真正存储数据的物理节点

DataHost ：存储节点所在的数据库主机（指定 MySQL 数据库的连接信息）

User ： MyCat 的⽤户（类似于 MySQL 的⽤户，⽀持多⽤户）

Mycat 主要解决的问题

海量数据存储

查询优化

Mycat 对多数据库的⽀持

Mycat 分⽚策略

MyCAT ⽀持⽔平分⽚与垂直分⽚：

⽔平分⽚：⼀个表格的数据分割到多个节点上，按照⾏分隔。

垂直分⽚：⼀个数据库中多个表格 A ， B ， C ， A 存储到节点 1 上， B 存储到节点 2 上， C 存储到节点 3

上。 MyCAT 通过定义表的分⽚规则来实现分⽚，每个表格可以捆绑⼀个分⽚规则，每个分⽚规则指定⼀个分

⽚字段并绑定⼀个函数，来实现动态分⽚算法。

1. Schema ：逻辑库，与 MySQL 中的 Database （数据库）对应，⼀个逻辑库中定义了所包括的 Table 。

2. Table ：表，即物理数据库中存储的某⼀张表，与传统数据库不同，这⾥的表格需要声明其所存储的

逻辑数据节点 DataNode 。 在此可以指定表的分⽚规则。 3. DataNode ： MyCAT 的逻辑数据节点，是存放 table 的具体物理节点，也称之为分⽚节点，通过

DataHost 来关联到后端某个具体数据库上

4. DataHost ：定义某个物理库的访问地址，⽤于捆绑到 Datanode 上

Mycat 安装

注意：需要先安装 jdk （操作系统如果是 64 位，必须安装 64 位的 JDK ）

第⼀步：下载 MyCat

第⼆步：解压缩，得到 mycat ⽬录

第三步：进⼊ mycat/bin ，启动 MyCat

第四步：访问 Mycat

Mycat 分⽚

配置 schema.xml

schema.xml 介绍

schema.xml 作为 Mycat 中重要的配置⽂件之⼀， 管理着 Mycat 的逻辑库、表、分⽚规则、 DataNode

以及 DataHost 之间的映射关系。 弄懂这些配置，是正确使⽤ Mycat 的前提。

schema 标签⽤于定义 MyCat 实例中的逻辑库

Table 标签定义了 MyCat 中的逻辑表

dataNode 标签定义了 MyCat 中的数据节点，也就是我们通常说所的数据分⽚。

dataHost 标签在 mycat 逻辑库中也是作为最底层的标签存在，直接定义了具体的数据库实例、读写

分离配置和⼼跳语句。

schema.xml 配置

wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-

linux.tar.gz

tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

- 启动命令： ./mycat start

- 停⽌命令： ./mycat stop

- 重启命令： ./mycat restart

- 查看状态： ./mycat status

使⽤ mysql 的客户端直接连接 mycat 服务。默认服务端⼝为【 8066 】

mysql -uroot -p123456 -h127.0.0.1 -P8066 配置 Server.xml

server.xml 介绍

server.xml ⼏乎保存了所有 mycat 需要的系统配置信息。最常⽤的是在此配置⽤户名、密码及权限。

server.xml 配置

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat = "http://io.mycat/" >

<!--

schema : 逻辑库 name: 逻辑库名称

sqlMaxLimit ：⼀次取多少条数据要超过⽤ limit xxx

table: 逻辑表

dataNode: 数据节点对应 datanode 标签

rule ：分⽚规则，对应 rule.xml

subTables: ⼦表

primaryKey ：分⽚主键可缓存

-->

<table name = "item" dataNode = "dn1,dn2,dn3" rule = "mod-long"

primaryKey = "ID" />

</schema>

<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"

/> -->

<!--

dataHost : 数据主机（节点主机）

balance ： 1 ：读写分离 0 ：读写不分离

writeType ： 0 第⼀个 writeHost 写， 1 随机 writeHost 写

dbDriver ：数据库驱动 native ： MySQL JDBC ： Oracle 、 SQLServer

switchType ：是否主动读

1 、主从⾃动切换 -1 不切换 2 当从机延时超过 slaveThreshold 值时切换为主读

-->

<dataHost name = "localhost1" maxCon = "1000" minCon = "10" balance = "0"

writeType = "0" dbType = "mysql" dbDriver = "native" switchType = "1"

slaveThreshold = "100" >

<heartbeat> select user() </heartbeat>

<writeHost host = "hostM1" url = "192.168.24.129:3306" user = "root"

password = "root" >

</writeHost>

</dataHost>

</mycat:schema> 配置 rule.xml

rule.xml ⾥⾯就定义了我们对表进⾏拆分所涉及到的规则定义。我们可以灵活的对表使⽤不同的分⽚算

法，或者对表使⽤相同的算法但具体的参数不同。这个⽂件⾥⾯主要有 tableRule 和 function 这两个标

签。在具体使⽤过程中可以按照需求添加 tableRule 和 function 。

此配置⽂件可以不⽤修改，使⽤默认即可。

tableRule 标签配置说明：

name 属性指定唯⼀的名字，⽤于标识不同的表规则

rule 标签则指定对物理表中的哪⼀列进⾏拆分和使⽤什么路由算法。

columns 内指定要拆分的列名字。

algorithm 使⽤ function 标签中的 name 属性。连接表规则和具体路由算法。当然，多个表规则

可以连接到同⼀个路由算法上。 table 标签内使⽤。让逻辑表使⽤这个规则进⾏分⽚。

function 标签配置说明：

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE mycat:server SYSTEM "server.dtd">

<mycat:server xmlns:mycat = "http://io.mycat/" >

<property name = "defaultSqlParser" > druidparser </property>

</system>

<property name = "password" > mycat </property>

<property name = "schemas" > TESTDB </property>

</user>

</mycat:server>

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE mycat:rule SYSTEM "rule.dtd">

<mycat:rule xmlns:mycat = ”http://io.mycat/“ >

<rule>

<columns> sharding_id </columns>

</rule>

</tableRule>

<function name = "hash-int"

class = "io.mycat.route.function.PartitionByFileMap" >

<property name = "mapFile" > partition-hash-int.txt </property>

</function>

</mycat:rule> name 指定算法的名字。

class 制定路由算法具体的类名字。

property 为具体算法需要⽤到的⼀些属性。

路由算法的配置可以查看算法章节。

⼗个常⽤的分⽚规则

连续分⽚

⼀、⽇期列分区法

<rule>

<columns> create_time </columns>

<algorithm> sharding-by-date </algorithm>

</rule>

</tableRule>

<function name = "sharding-by-date"

class = "io.mycat.route.function..PartitionByDate" >

</function>

<rule>

<columns> create_time </columns>

<algorithm> sharding-by-month </algorithm>

</rule>

</tableRule>

<function name = "sharding-by-month"

class = "io.mycat.route.function..PartitionByMonth" >

</function>

<!--

按单⽉⼩时分⽚

适合做⽇志，每⽉末，⼿⼯清理

-->

<rule>

<columns> create_time </columns>

<algorithm> sharding-by-hour </algorithm>

</rule>

</tableRule>

<function name = "sharding-by-hour"

class = "io.mycat.route.function..LastestMonthPartition" >

<property name = "splitOneDay" > 24 </property> 配置说明：

tableRule 标签：

columns ：标识将要分⽚的表字段

algorithm ：指定分⽚函数

function 标签：

dateFormat ：⽇期格式

sBeginDate ：开始⽇期

sPartionDay ：分区天数，即默认从开始⽇期算起，分隔 10 天⼀个分区

⼆、范围约定

配置说明：

tableRule 标签：

columns ：标识将要分⽚的表字段

algorithm ：指定分⽚函数

function 标签：

mapFile ：指定分⽚函数需要的配置⽂件名称

autopartition-long.txt ⽂件内容：

所有的节点配置都是从 0 开始，及 0 代表节点 1 ，此配置⾮常简单，即预先制定可能的 id 范围对应某个分

⽚

</function>

<rule>

</rule>

</tableRule>

<function name = "rang-long"

class = "io.mycat.route.function.AutoPartitionByLong" >

<property name = "mapFile" > autopartition-long.txt </property>

</function> 优势：扩容⽆需迁移数据

缺点：热点数据，并发受限

离散分⽚

⼀、枚举法

配置说明：

tableRule 标签：

columns ：标识将要分⽚的表字段

algorithm ：指定分⽚函数

function 标签：

mapFile ：指定分⽚函数需要的配置⽂件名称

type ：默认值为 0 ， 0 表示 Integer ，⾮零表示 String

defaultNode ：指定默认节点，⼩于 0 表示不设置默认节点，⼤于等于 0 表示设置默认节点， 0 代表节

点 1 。

默认节点的作⽤：枚举分⽚时，如果碰到不识别的枚举值，就让它路由到默认节点。

如果不配置默认节点（ defaultNode 值⼩于 0 表示不配置默认节点），碰到不识别的枚举值就

# range start-end ,data node index

# K=1000,M=10000.

0-500M=0 0-100 0

500M-1000M=1 101-200 1

201-300 2

1000M-1500M=2

default=0

# 或以下写法

# 0-10000000=0

# 10000001-20000000=1

<rule>

</rule>

</tableRule>

<function name = "hash-int"

class = "io.mycat.route.function.PartitionByFileMap" >

<property name = "mapFile" > partition-hash-int.txt </property>

</function> 会报错：

can't find datanode for sharding column:column_name val:ffffffff

partition-hash-int.txt 配置：

⼆、求模法

此种配置⾮常明确，即根据 id 与 count （你的结点数）进⾏求模运算，相⽐⽅式 1 ，此种在批量插⼊时需

要切换数据源， id 不连续

配置说明：

tableRule 标签：

columns ：标识将要分⽚的表字段

algorithm ：指定分⽚函数

function 标签：

count ：节点数量

三、字符串拆分 hash 解析

10000=0 列等于 10000 放第⼀个分⽚

10010=1

男 =0

⼥ =1

beijing=0

tianjin=1

zhanghai=2

<rule>

</rule>

</tableRule>

<function name = "mod-long"

class = "io.mycat.route.function.PartitionByMod" >

</function> <tableRule name = "sharding-by-stringhash" >

<rule>

<algorithm> sharding-by-stringhash </algorithm>

</rule>

</tableRule>

<function name = "sharding-by-stringhash"

class = "io.mycat.route.function.PartitionByString" >

</function>

配置说明：

tableRule 标签：

columns ：标识将要分⽚的表字段

algorithm ：指定分⽚函数

function 标签：

length ：代表字符串 hash 求模基数

count ：分区数

hashSlice ： hash 预算位，即根据⼦字符串 hash 运算

"2" -> (0,2)

"1:2" -> (1,2

"1:" -> (1,0)

"-1:" -> (-1,0)

":-1" -> (0,-1)

":" -> (0,0)

public class PartitionByStringTest {

@Test

public void test () {

PartitionByString rule = new PartitionByString ();

String idVal = null ;

rule . setPartitionLength ( "512" );

rule . setPartitionCount ( "2" );

rule . init (); 四、固定分⽚ hash 算法

配置说明：

tableRule 标签：

columns ：标识将要分⽚的表字段

algorithm ：指定分⽚函数

function 标签：

partitionCount ：指定分⽚个数列表

partitionLength ：分⽚范围列表，分区⻓度 : 默认为最⼤ 2^n=1024 , 即最⼤⽀持 1024 分区

rule . setHashSlice ( "0:2" );

// idVal = "0";

// Assert.assertEquals(true, 0 == rule.calculate(idVal));

// idVal = "45a";

// Assert.assertEquals(true, 1 == rule.calculate(idVal));

//last 4

rule = new PartitionByString ();

rule . setPartitionLength ( "512" );

rule . setPartitionCount ( "2" );

rule . init ();

//last 4 characters

rule . setHashSlice ( "-4:0" );

idVal = "aaaabbb0000" ;

Assert . assertEquals ( true , 0 == rule . calculate ( idVal ));

idVal = "aaaabbb2359" ;

Assert . assertEquals ( true , 0 == rule . calculate ( idVal ));

}

<rule>

</rule>

</tableRule>

<function name = "func1"

class = "io.mycat.route.function.PartitionByLong" >

</function> 约束：

count,length 两个数组的⻓度必须是⼀致的。 1024 = sum((count[i]*length[i]))

⽤法例⼦：

如果需要平均分配设置：平均分为 4 分⽚， partitionCount*partitionLength=1024

五、⼀致性 hash

@Test

public void testPartition () {

// 本例的分区策略：希望将数据⽔平分成 3 份，前两份各占 25% ，第三份占 50% 。（故本例⾮均匀分

区）

// |<---------------------1024------------------------>|

// |<----256--->|<----256--->|<----------512---------->|

// | partition0 | partition1 | partition2 |

// | 共 2 份 , 故 count[0]=2 | 共 1 份，故 count[1]=1 |

int [] count = new int [] { 2 , 1 };

int [] length = new int [] { 256 , 512 };

PartitionUtil pu = new PartitionUtil ( count , length );

// 下⾯代码演示分别以 offerId 字段或 memberId 字段根据上述分区策略拆分的分配结果

int DEFAULT_STR_HEAD_LEN = 8 ; // cobar 默认会配置为此值

long offerId = 12345 ;

String memberId = "qiushuo" ;

// 若根据 offerId 分配， partNo1 将等于 0 ，即按照上述分区策略， offerId 为 12345 时将会被分

配到 partition0 中

int partNo1 = pu . partition ( offerId );

// 若根据 memberId 分配， partNo2 将等于 2 ，即按照上述分区策略， memberId 为 qiushuo 时将会

被分到 partition2 中

int partNo2 = pu . partition ( memberId , 0 , DEFAULT_STR_HEAD_LEN );

Assert . assertEquals ( 0 , partNo1 );

Assert . assertEquals ( 2 , partNo2 );

}

<function name = "func1"

class = "org.opencloudb.route.function.PartitionByLong" >

</function>

<rule> ⼀致性 hash 预算有效解决了分布式数据的扩容问题，前 1-9 中 id 规则都多少存在数据扩容难题，⽽ 10 规

则解决了数据扩容难点

六、编程指定

<algorithm> murmur </algorithm>

</rule>

</tableRule>

<function name = "murmur"

class = "io.mycat.route.function.PartitionByMurmurHash" >

<!-- ⼀个实际的数据库节点被映射为这么多虚拟节点，默认是 160 倍，也就是虚拟节点数是物理节点

数的 160 倍 -->

<!-- <property name="weightMapFile">weightMapFile</property> 节点的权重，没有指

定权重的节点默认是 1 。以 properties ⽂件的格式填写，以从 0 开始到 count-1 的整数值也就是节点索引

为 key ，以节点权重值为值。所有权重值必须是正整数，否则以 1 代替 -->

<!-- <property name="bucketMapPath">/etc/mycat/bucketMapPath</property>

⽤于测试时观察各物理节点与虚拟节点的分布情况，如果指定了这个属性，会把虚拟节点的 murmur

hash 值与物理节点的映射按⾏输出到这个⽂件，没有默认值，如果不指定，就不会输出任何东⻄ -->

</function> 配置说明：

tableRule 标签：

columns ：标识将要分⽚的表字段

algorithm ：指定分⽚函数

function 标签：

startIndex ：字符串截取的起始索引位置

size ：截取的位数

partitionCount ：分区数量

defaultPartition ：默认分区

11010419800101

此⽅法为直接根据字符⼦串（必须是数字）计算分区号（由应⽤传递参数，显式指定分区号）。

例如 id=05-100000002

在此配置中代表根据 id 中从 startIndex=0 ，开始，截取 siz=2 位数字即 05 ， 05 就是获取的分区，如果没传

默认分配到 defaultPartition

优点：数据分布均匀，并发能⼒强

缺点：移植性差、扩容性差

综合分⽚

⼀、通配取模

<rule>

<algorithm> sharding-by-substring </algorithm>

</rule>

</tableRule>

<function name = "sharding-by-substring"

class = "io.mycat.route.function.PartitionDirectBySubString" >

</function> 配置说明：

tableRule 标签：

columns ：标识将要分⽚的表字段

algorithm ：指定分⽚函数

function 标签：

patternValue ：求模基数

defaultNode ：默认节点，如果不配置了默认，则默认是 0 即第⼀个结点

mapFile ：配置⽂件路径

partition-pattern.txt ⽂件内容 :

配置⽂件中， 1-32 即代表 id%256 后分布的范围，如果在 1-32 则在分区 1 ，其他类推，如果 id ⾮数字数

据，则会分配在 defaultNode 默认节点

⼆、 ASCII 码求模通配

<rule>

<algorithm> sharding-by-pattern </algorithm>

</rule>

</tableRule>

<function name = "sharding-by-pattern"

class = "io.mycat.route.function.PartitionByPattern" >

<property name = "mapFile" > partition-pattern.txt </property>

</function>

# id partition range start-end ,data node index

###### first host configuration

1-32=0

33-64=1

65-96=2

97-128=3

######## second host configuration

129-160=4

161-192=5

193-224=6

225-256=7

0-0=7 <tableRule name = "sharding-by-prefixpattern" >

<rule>

<algorithm> sharding-by-prefixpattern </algorithm>

</rule>

</tableRule>

<function name = "sharding-by-pattern"

class = "io.mycat.route.function.PartitionByPrefixPattern" >

<property name = "mapFile" > partition-pattern.txt </property>

</function>

配置说明：

tableRule 标签：

columns ：标识将要分⽚的表字段

algorithm ：指定分⽚函数

function 标签：

patternValue ：求模基数

prefixLength ： ASCII 截取的位数

mapFile ：配置⽂件路径

partition-pattern.txt ⽂件内容：

配置⽂件中， 1-32 即代表 id%256 后分布的范围，如果在 1-32 则在分区 1 ，其他类推

此种⽅式类似⽅式 6 ，只不过采取的是将列中前 prefixLength 位所有 ASCII 码的和与 patternValue 进⾏求

模，即 sum%patternValue , 获取的值在通配范围内的，即分⽚数。

ASCII 编码：

48-57=0-9 阿拉伯数字

64 、 65-90=@ 、 A-Z

97-122=a-z

# range start-end ,data node index

# ASCII

# 48-57=0-9

# 64 、 65-90=@ 、 A-Z

# 97-122=a-z

###### first host configuration

1-4=0

5-8=1

9-12=2

13-16=3

###### second host configuration 测试分⽚

需求

把商品表分⽚存储到三个数据节点上。

创建表

配置完毕后，重新启动 mycat 。使⽤ mysql 客户端连接 mycat ，创建表。

17-20=4

21-24=5

25-28=6

29-32=7

0-0=7

CREATE TABLE item (

id int ( 11 ) NOT NULL ,

name varchar ( 20 ) DEFAULT NULL ,

PRIMARY KEY (id)

) ENGINE = InnoDB DEFAULT CHARSET =utf8; 分⽚测试

分⽚策略指定为 “auto-sharding-long”

分⽚规则指定为 “mod-long”

Mycat 读写分离

MyCat 的读写分离是建⽴在 MySQL 主从复制基础 之上实现的，所以必须先搭建 MySQL 的主从复制。

数据库读写分离对于⼤型系统或者访问量很⾼的互联⽹应⽤来说，是必不可少的⼀个重要功能。对于

MySQL 来说，标准的读写分离是主从模式，⼀个写节点 Master 后⾯跟着多个读节点，读节点的数量取

决于系统的压⼒，通常是 1-3 个读节点的配置

Mycat 实现的读写分离和⾃动切换机制，需要 mysql 的主从复制机制配合。

Mycat 配置

Mycat 1.4 ⽀持 MySQL 主从复制状态绑定的读写分离机制，让读更加安全可靠，配置如下：

<dataHost name = "localhost1" maxCon = "1000" minCon = "10" balance = "1"

writeType = "0" dbType = "mysql" dbDriver = "native" switchType = "2"

slaveThreshold = "100" >

<heartbeat> show slave status </heartbeat>

<writeHost host = "hostM" url = "192.168.25.134:3306" user = "root"

password = "root" >

<readHost host = "hostS" url = "192.168.25.166:3306" user = "root"

password = "root" />

</writeHost>

</dataHost> (1) 设置 balance="1" 与 writeType="0"

Balance 参数设置：

1. balance=“0”, 所有读操作都发送到当前可⽤的 writeHost 上。

2. balance=“1” ，所有读操作都随机的发送到 readHost 。

3. balance=“2” ，所有读操作都随机的在 writeHost 、 readhost 上分发

WriteType 参数设置：

1. writeType=“0”, 所有写操作都发送到可⽤的 writeHost 上。

2. writeType=“1” ，所有写操作都随机的发送到 readHost 。

3. writeType=“2” ，所有写操作都随机的在 writeHost 、 readhost 分上发。

“readHost 是从属于 writeHost 的，即意味着它从那个 writeHost 获取同步数据，因此，当它所属的

writeHost 宕机了，则它也不会再参与到读写分离中来，即 “ 不⼯作了 ” ，这是因为此时，它的数据已

经 “ 不可靠 ” 了。基于这个考虑，⽬前 mycat 1.3 和 1.4 版本中，若想⽀持 MySQL ⼀主⼀从的标准配置，并

且在主节点宕机的情况下，从节点还能读取数据，则需要在 Mycat ⾥配置为两个 writeHost 并设置

banlance=1 。 ”

(2) 设置 switchType="2" 与 slaveThreshold="100"

switchType ⽬前有三种选择：

-1 ：表示不⾃动切换

1 ：默认值，⾃动切换

2 ：基于 MySQL 主从同步的状态决定是否切换

Mycat ⼼跳检查语句配置为 show slave status ， dataHost 上定义两个新属性： switchType="2" 与

slaveThreshold="100" ，此时意味着开启 MySQL 主从复制状态绑定的读写分离与切换机制。 Mycat ⼼

跳机制通过检测 show slave status 中的 "Seconds_Behind_Master", "Slave_IO_Running",

"Slave_SQL_Running" 三个字段来确定当前主从同步的状态以及 Seconds_Behind_Master 主从复制时

延。

一条大鱼

关注

21
点赞
踩
24

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL知识总结

mysql
复制链接

扫一扫

MySQL知识总结

“相关推荐”对你有帮助么？