MySQL 性能优化学习笔记_13055 mysql-优快云博客

本文链接：https://blog.youkuaiyun.com/ashkit/article/details/118826216

本文详细探讨了MySQL数据库性能优化的关键因素，包括合理设计数据库结构（遵循范式）、选择合适的存储引擎（InnoDB与MyISAM对比）、优化SQL语句、调整数据库参数、服务器硬件与操作系统配合，以及分库分表、主从复制和硬件调优技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL性能的主要因素

1.数据库结构设计

2.数据库存储引擎

3.SQL语句的书写

4.数据库参数配置

5.服务器操作系统与硬件

第一章、MySQL底层设计调优

⑴数据库设计三范式

1、第一范式：

①数据库所有字段都只有单一属性

②单一属性是由基本数据类型构成

③数据库的表都是二维的(行与列)

错误例子：

学生信息

张三，23,180,2019-04-01

李四，26,167,2019-04-01

王五，23,182,2019-04-01

：每一行可以拆分为：‘姓名’，‘年龄’，

‘身高’，‘出生日期’四个字段，每个字段有各自对应的数据类型。例子中对应‘字符’，‘数字’，‘数字’，‘日期’类型；

正确例子：

注意：此时数据存在大量重复的数据，比如：张莉莉出现了多次，同时这张表出现了新增异常，修改异常，删除异常的问题

插入异常：假设三月份学校学校开了系名，但是到九月份才开始招生，这时候只有一部分有数据，只有系名却没有学生姓名与分数等信息，这叫做插入异常

修改异常：加入李小明同学需要从经济系转到法律系，此时系主任也要从王强改为刘玲才行，这叫做修改异常、

删除异常：加入高芳芳同学需要转学，此时删除高芳芳同学会把法律系也被删除，此时学校没有了法律系了，这种情况叫做删除异常

此时需要引入第二范式

2、第二范式：

①要求符合第一范式

②表必须有一个主键（一列或多列）

③其他字段可由主键确定

④二范式目的是通过拆表减少数据冗余

二范式正确例子

二范式不能解决删除异常，插入异常，修改异常的问题，此时需要第三范式

3、第三范式：

①要求符合第二范式

②字段要求直接依赖主键，不允许间接依赖

③第三范式目的在于拆分实体（完善主从表）

三范式正确例子

数据三范式各自的目的：

第一范式：每个字段的数据不能再被拆分

第二范式：通过超表的方式减少数据冗余

第三范式：通过分析实体的关系来形成主表与从表

采用一些反范式的设计可以减少SQL的复杂度，提高SQL执行效率

如果我要提取，省，市，县需要这样写：

SELECT

a.* , p.province , c.city , t.town

FROM

address a, province p, city c, town t

WHERE

a.town_id = t.town_id and

t.city = c.city and

c.prov_id = p.prov_id

如果采用反范式设计可以这样设计：

SELECT a.* FROM address a

反范式就是有意识地增加一些冗余数据，来降低SQL语句的复杂度，使得维护更加轻松

反范式优缺点：

①单表查询易于优化，易于管理

②SQL语句简单，有利于程序开发，团队协作

③存在数据冗余，写操作需要额外更新从表数据

（例如上图中长安区改成天河区，需要改两张表才能保证数据的一致性）

④不合理的反范式设计会让表变得臃肿不堪

⑶实体关系分析

⒈实体关系是指系统事物之间的联系

⒉实体关系需要双向分析

⒊实体关系决定表

实体之间的关系

①一对一

②一对多

③多对多

表关系设计

①一对一，通过主键关联

②一对多，在多的一方设置外键

③多对多，增加中间表，持有双方外键

⑷自然主键与代理主键

①自然主键是指事物属性中的自然唯一标识

②代理主键是指与业务无关的，无意义的数字序列值（数据库中的自增数字）

③在表设计时，优先推荐代理主键，不推荐自然主键

⑸数据类型的选择

字段类型优先级

①数字类型

②日期类型与二进制类型

③字符串类型

整数类型

列类型	存储空间	取值范围
tinyint	1字节	-128~127
smallint	2字节
mediumint	3字节
int	4字节
bigint	8字节

实数类型

列类型	存储空间	是否精确类型
FLOAT	4个字节	否
DOUBLE	8个字节	否
DECIMAL	每4个字节存9个数字，小数点占一个字节	是

VARCHAR类型

VARCHAR变体长度，根据实际内容保存数据

⒈使用最小的符合需求的长度

⒉VARCHAR（255）以下使用额外一个字节保存长度

⒊VARCHAR（255）以上使用额外两个字节保存长度

⒋VARCHAR（5）与VARCHAR（200）内存占用不同

⒌VARCHAR变更长度时会出现锁表

VARCHAR的使用场景

①VARCHAR适合存储长度波动大的数据（如博客文章）

②字符串很少更新的场景

③VARCHAR适合保存多字节字符

CHAR类型

①CHAR属于定长数据

②CHAR最大长度255

③CHAR类型会自动删除末尾的空格

④CHAR检索效率比VARCHAR高

CHAR的使用场景

①CHAR适合存储长度波动不大的数据，如MD5摘要

②CHAR适合存储短字符串

③CHAR类型适合存储经常更新的字符串

DATETIME日期类型

DATETIME日期时间类型，占用8个字节

①与时区无关

②可保存到毫秒

③可保存时间范围大

PS：不要使用字符串存储日期类型

TIMESTAMP时间戳

TIMESTAMP时间戳，占用4个字节

①时间范围1970-01-01~2038-01-19

②精确到秒

③采用整型时区

④依赖于时区

⑤自动更新TIMESTAMP列的值

第二章、MySQL存储引擎选择与调优

（7）MySQL体系结构

（8）InnoDB 介绍与存储特性

介绍：

①MySQL5.5.8后InnoDB是默认存储引擎

②InnoDB采用‘表空间’保存文件（表空间也是一个文件）

③InnoDB支持事务处理

特性：

InnoDB表空间有两种形式

①使用系统表空间ibdataN

②独立表空间：tablename.ibd（推荐使用）

设置Innodb_file_per_table决定表空间模式

为什么推荐独立表空间？

①系统表空间 all in one 不利于管理（所有数据都在一起）

②系统表空间会产生IO瓶颈（在文件层面上有锁的概念，同一时间只有一个线程读取数据）

③系统表空间很难回收存储空间（删除某一张表的数据不能回收表空间的数据）

④独立表空间使用optimize table 命令回收存储空间

（9）InnoDB预防锁表

InnoDB的事务特性：

①InnoDB支持事务

②InnoDB默认使用行级锁

③InnoDB具备良好的高并发特性

MySQL的锁

职责分类	粒度分类
共享锁-读锁（这个数据被所有进程访问）	行级锁
独占锁（排他锁）-写锁（当某个线程获得了某一条数据，其他所有线程都要等待前面的线程释放锁才能访问数据）	表级锁

ps：在InnoDB中只有利用索引的更新，删除操作，才可以使用行级锁

不能使用索引的索引写操作则是表锁，所以在实际开发遇到写操作的时候where 条件一定要用到索引才可以使用行级锁

（10）MyISAM存储引擎

MyISAMySQL特点：

①不支持事务

②支持全文检索，支持text支持前缀索引

③支持数据压缩

④紧密存储，顺序读性能很好

⑤表级锁，混合读写性能不佳，并发性能差

MyISAM应用场景：

①非事务应用例如：保存日志

②只读类应用例如：报表数据，字典数据，像邮政编码这些适合使用MyISAM来存储

③空间类应用：开发GIS系统（5.7版本以前）

④系统临时表，SQL查询，分组的临时表引擎

（11）Memory存储引擎

Memory特点：

①不支持事务

②内存读写，临时存储

③超高的读写效率，比MyISAM高一个量级

④表级锁，并发性差

Memory应用场景:（要额外创建一张InnoDB或MyISAM表来持久化）

①读多写少的静态数据，例如省市县的对应表

②充当缓存使用，保存高频访问的静态数据

③系统临时表

Memory关键参数：

①设置max_heap_table_size控制内存表大小（字节）

②设置tmp_table_size设置内存临时表最大值（字节）超过这个值就会持久化到磁盘

内存最大长度又业务和硬件来决定，数据超过上限就会报错

set global 重启后会失效（只对当前实例生效）

修改配置文件(永久生效)

vi /etc/my.cnf

[mysqld]

max_heap_table_size = 2048m

（12）CSV存储引擎

CSV特点：

①纯文本保存

②不支持事务

③不支持索引

CSV 应用场景：

①数据交换/数据迁移

MySQL ----导出----->CSV<------SqlLoader----- Oracle

②不依赖MySQL环境

MySQL -----导出 ----> CSV <-----Commons----- Web 应用

第三章、MySQL索引与执行计划详解

（13）BTree索引原理介绍

数据表索引的目的:

①索引就是为表建立的“目录”

②索引的目的就是防止全表扫描（Full Scan）

③索引的存储形式是由存储引擎决定

数据表索引分类：

①从存储结构上划分：BTree索引（B+Tree或B-Tree索引，采用树的形式存储数据），Hash索引（为每一行数据添加一个hash值，通过hash值快速找到数据），full-index全文索引，R-Tree索引

②从应用层次划分：普通索引（就是一个普通的索引），唯一索引（所有类型中最高的），复合索引（多列索引，常用语多条件组织查询例如：姓名，年龄组合筛选）

③根据数据中的物理顺序与键值的逻辑（索引）顺序关系：聚集索引（就是数据在磁盘中的存储顺序与索引的顺序是一致的，执行效率高），非聚集索引（没有按照与索引的顺序一致的索引）

MySQL常用的索引：

①B+Tree索引 ---适用于范围查找

②Hash索引 --- 适用于精确匹配

B+Tree：

①：MySQL中InnoDB与MyISAM采用的是B+Tree索引

②：B+Tree索引采用树形链表结构建立数据“目录”

案例讲解：

如果我要获取主键 2~4的数据，则需要从主键1找到主键7才能确定我们要找的数据，这是最差的情况，使用全表扫描

为了避免全表扫描：可以在id上创建B+Tree索引，那么表在InnoDB引擎中的存储结构如下：

到这里就知道了为什么B+Tree适合范围查询了，是因为在底层B+Tree对数据进行的聚集排列，按照数据的顺序进行了组织，这样一来我们查找2~4的数据就可以快速找到

如果在一个表中建立了多个索引，在索引中是如何做到的？

首先以id主键作为数据的物理组织形式，按照主键的顺序对数据进行前后排列。假设在name名称上建立索引

为什么官方推荐使用自增主键作为索引？

因为自增长主键每次添加的值都是最大的，所以直接在最后添加数据，如果使用其他字段作为索引，每次插入数据都要重新组织索引，非常的麻烦

MyISAM引擎B+Tree索引

MyISAM存储引擎中叫做‘非聚集索引’,也就是说实际存储数据的顺序与索引顺序是不一致的。在InnoDB中数据采用id的顺序对数据进行组织，而MyISAM是以磁盘的物理地址进行组织

B+Tree索引使用技巧

基本使用办法：

drop index idx_uid on t_content;

create index idx_uid on t_content(uid);

#精确匹配，允许使用BTree索引

explain select * from t_content where uid = 162344；

#范围匹配，允许使用BTree

explain select * from t_content where uid > 10065 and uid < 10070；

#查询优化器会自动进行数据类型转换，但仍然建议使用与定义相符的类型

EXPLAIN SELECT * FROM t_content WHERE uid = '17076710';

Btree的坑：

drop index idx_share_url on t_content;

create index idx_share_url on t_content(share_url);

#字符串字段BTree索引允许进行“前缀查询”

explain select * from t_content where share_url like ‘1342422%’

符复合索引：

drop index idx_uid_sid on t_content;

create index idx_uid_sid on t_content(uid,source_id);创建复合索引的时候要把选择性高的放在前面选择性高可以理解为数据中的重复性比较低比如：一列数据中只有男和女那么这个列的选择性就低；

#复合索引查询条件必须包含左侧列(下面这个SQL中，uid是左侧列，source_id是右侧列，一定要把左侧列放在前面)

explain select * from t_content where uid = 14206985；

#直接书写右侧列将导致数据无法查询

explain select * from t_content where source_id = 13054;

#<>与not 会导致不使用索引；这两个都是排除的意思

explain select * from t_content where source_id <>13054;

#可以将这个SQL写成：

explain select * from t_content where source_id <= 13503 or source_id =>13055

但是这个SQL执行完之后同样是进行全表扫描，是因为条件source_id 的值太大了所以索引的选择性是非常差的，MySQL优化器发现还不如全表扫描

Hash索引

①Hash索引（hash索引）基于哈希表实现

②精确匹配索引所有列的查询才有效

③Hash索引会为每条数据生成一个HashCode（这个HashCode就比如这条数据的一个摘要）

Create table testtable （ f('Arjen') = 2323

fname VARCHAR（50）Not null， f('Baron') = 7437

lname VARCHAR（50）not null， f('Arjen') = 8784

key using hash（fname） f('Arjen') = 2358

）engine = memory；

槽（Slot）值（value）

2323 指向第1行的指针

SELECT lname from testhash where fname = 'peter'; 2458 指向第4行的指针

7437 指向第2行的指针

8784 指向第3行的指针

fname	lname
Arjen	Lentz
Baron	Schwartz
Peter	Zaitsev
Vadim	Tkachenko

Hash索引的特点：

①Hash索引只包含哈希和行指针

②只支持精准匹配，不支持范围查询，模糊查询及排序

③ Hash取值速度非常快，但索引选择性很低时不建议使用

④MySQL目前只有Memory显示支持Hash索引

InnoDB中的Hash索引

①InnoDB存储引擎只支持显示创建BTree索引

②数据精确匹配是MySQL会自动生成HashCode，存入缓存

索引优点：

①索引大幅提升了数据的检索效率

②索引把随机IO，变成了顺序IO

索引是不是越多越好？

①降低了写入数据的效率

② 太多的索引增加了查询优化器的选择时间

③不合理的使用索引，会大幅占用磁盘空间

索引的优化策略

什么情况下不会用到索引？

①索引选择性太差（数据重复性太高）

②<>/not in 无法使用索引

③is null 会使用索引，is not null不会使用索引

④where 子句跳过左侧索引列，直接查询右侧索引字段

⑤对索引列进行计算或者使用函数

使用索引优化排序

当Order by 字段与索引字段顺序/排序方向相同时索引可优化排序速度

#当排序出现了索引左侧列，则允许使用索引排序

#左侧字段单字段排序时，所以支持升降序

explain select * from t_content where uid < 14206986 order by uid desc;

explain select * from t_content where uid < 14206986 order by source_id;

#在多字段情况下，左侧字段必须是升序，且顺序不允许打乱

explain select * from t_content where uid < 14206986 order by uid，source_id desc;

索引的优化策略2

#索引重新统计

analyze table t_content；

#optimize 优化表空间，释放表空间

#执行这个命令会锁表，一定要在维护期间，否则会造成IO阻塞

optimize table t_content；

关联查询优化

多表关联优化

EXPLAIN SELECT gc.*,g.title

FROM t_goods g,t_goods_cover gc

WHERE g.goods_id = gc.goods_id AND g.category_id = 44;

执行结果：

id select_type TABLE TYPE possible_keys KEY key_len ref ROWS Extra

1 SIMPLE gc ALL \N \N \N \N 9454 \N

1 SIMPLE g eq_ref PRIMARY PRIMARY 4 babytun.gc.goods_id 1 USING where

#explain type 值的含义

#ALL 全表扫描

#eq_ref 联表查询的情况，按联表的主键或唯一健联合查询。

#ref 非主键或唯一索引的等值检索

#explain 默认第一行出现的表就是驱动表，由查询优化器自动选择

#关联查询优化要点

#1. 外键加上索引

create index idx_goods_id on t_goods_cover(goods_id);

#2. 查询条件上加索引

create index idx_category_id on t_goods(category_id);

加上这两句后执行的结果：

id select_type TABLE TYPE possible_keys KEY key_len ref ROWS Extra

1 SIMPLE g ref PRIMARY,idx_category_id idx_category_id 4 const 74 \N

1 SIMPLE gc ref idx_goods_id idx_goods_id 4 babytun.g.goods_id 2 \N

慢SQL日志

MySQL执行超过设定时间的SQL记录日志

#开启慢SQL日志功能

SET GLOBAL slow_query_log = ON;

SET GLOBAL long_query_time=0.001;#单位为秒，不建议设置超过300毫秒

#指定慢SQL文件slow-SQL，慢日志保存在MySQL/data目录下

SET GLOBAL slow_query_log_file = "slow-sql.log";

#是否记录没有使用索引的SQL语句

SET GLOBAL log_queries_not_using_indexes=ON;

慢SQL日志组成信息如下：

# Time: 210408 14:44:18 - 执行时间

# User@Host: root[root] @ localhost [127.0.0.1] Id: 20 - 环境信息

# Query_time: 0.022937 - 查询时间 *****

#Lock_time: 0.000000 - 资源锁定时间

#Rows_sent: 9539 - 查询结果总行数

#Rows_examined: 19078 - 扫描总行数 *****

#SET timestamp=1617864258; - 时间戳

#=========SQL=============

#SELECT gc.*,g.title,g.*

#FROM t_goods g,t_goods_cover gc

#WHERE g.goods_id = gc.goods_id AND g.goods_id >60;

Explain 执行计划1

EXPLAIN SELECT (SELECT 1 FROM actor WHERE id = 1)

FROM (SELECT * FROM film WHERE id = 1) der;

id select_type TABLE TYPE possible_keys KEY key_len ref ROWS Extra

1 PRIMARY <derived3> system \N \N \N \N 1 \N

3 DERIVED film const PRIMARY PRIMARY 4 const 1 \N

2 SUBQUERY actor const PRIMARY PRIMARY 4 const 1 USING INDEX

#id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的

#出现关联关系的时候，id=1 是驱动表

#select_type 查询类型，说明查询的种类

# simple：简单查询。查询不包含子查询和union

EXPLAIN SELECT * FROM film WHERE id = 2;

id select_type TABLE TYPE possible_keys KEY key_len ref ROWS Extra

1 SIMPLE film const PRIMARY PRIMARY 4 const 1 \N

EXPLAIN SELECT (SELECT 1 FROM actor WHERE id = 1)

FROM

(SELECT * FROM film WHERE id = 1

UNION

SELECT * FROM film WHERE id = 1

)der;

执行结果如下：

id select_type TABLE TYPE possible_keys KEY key_len ref ROWS Extra

1 PRIMARY <derived3> ALL \N \N \N \N 2 \N

3 DERIVED film const PRIMARY PRIMARY 4 const 1 \N

4 UNION film const PRIMARY PRIMARY 4 const 1 \N

\N UNION RESULT <union3,4> ALL \N \N \N \N \N USING TEMPORARY

2 SUBQUERY actor const PRIMARY PRIMARY 4 const 1 USING INDEX

各个字段分析：

#1. 列select_type中的primary: 复杂查询中最外层的 select

# DERIVED：包含在from字句中的子查询。MySQL 会将结果存放在一个临时表中，也称为派生表（derived的英文含义）

# union：在union 中的第二个随后的 select

# union result：从 union 临时表检索结果的 select（在实际开发中尽量不要用union，因为使用union会产生临时表，而临时表没有索引，数据量大会很慢，而是用union all 进行简单关联）

# SUBQUERY：包含在 select 中的子查询

#2. 列table中的值：表示 explain 的一行正访问那个表

#3. 列type中的值：这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行

#执行效率：随着数字变大，执行效率依次降低

#① system

#② const（常量引用，主键筛选） *: MySQL能对查询的某部分进行优化并将其转化成一个常量。用于primary key 或者unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。

EXPLAIN SELECT * FROM (SELECT * FROM film WHERE id = 1)tmp;

#③ eq_ref（用于关联查询） * :primary key 或 unique key 索引的所有部分被连接使用，最多只会返回一条符合条件的记录。这可能是在const之外最好的联接类型了，简单的 select 不会出现这种type

EXPLAIN SELECT * FROM film_actor LEFT JOIN film ON film_actor.film_id = film.id;

#④ ref（非主键或非唯一性索引的数据检索） * :相比eq_ref，不使用唯一索引，而是使用普通索引或者唯一索引的部分前缀，索引要和谋个值比较，可能会找到个符合条件的行。

EXPLAIN SELECT * FROM film WHERE NAME = 'film1';

#⑤ fulltext

#⑥ ref_or_null（包含null情况的索引检索） * :类似ref，但是可以搜索值为null的行

EXPLAIN SELECT * FROM film WHERE NAME = 'film1' OR NAME IS NULL;

#⑦ index_merge

#⑧ unique_subquery

#⑨ index_subquery

#⑩ range（范围检索） *:范围扫描通常出现在 in（），between，>,<,>= 等操作中。使用一个索引来检索给定范围的行

EXPLAIN SELECT * FROM film WHERE id >2;

#⑪ index（通过索引做的全表扫描） * :和all一样，不同就是MySQL 只需要扫描索引树，这通常比all快一些

EXPLAIN SELECT COUNT(*) FROM film;

#⑫ all（纯粹的全表扫描） *：即全表扫描，意味着MySQL 需要从头到尾去查找所需要的的行。通常情况下这需要增加索引来进行优化了

EXPLAIN SELECT * FROM actor;

#possible_keys 这一列显示查询可能使用那些索引来查找

#key 这一列显示MySQL实际采用哪个索引来优化对该表的访问

#key_len 这一列显示MySQL 在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

#ref 这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：count（常量），func，null，字段名（例如 :film.id）

#row 这一列是MySQL估计要读取并检验的行数，注意这个不是结果集中的行数

#filetered 列，是一个百分比的值，代表（rows * filetered）/ 100,这个结果将与前表产生交互

#Extra 这一列展示的是额外信息。

#distinct：一旦MySQL找到了与行相联合匹配的就行，就在搜索了

explain select distinct name from film left join film_actor on film.id = film_actor.film_id

#using index: 这发生在对表的请求列都是同一索引的部分的时候，返回列数据只使用了索引中的信息而没有再去访问表中的行记录。是高性能的表现，using index 也叫做索引覆盖

#using where ：MySQL 服务器将在存储引擎检索行后再进行过滤，就是先读取整行数据，再按where 条件进行检查，符合就留下，不符合就丢弃

explain select * from film where id > 1;

#using temporary：MySQL 需要创建一张临时表来处理查询，出现这种情况一般要进行优化的，首先是想到用索引来优化

explain select distinct name from actor；

create index idx_name on actor（name）；

第四章、分库分表/主从/读写分离

分区表partition

什么是分区表？

①分区表就是把大表按条件单独存储到不同的“物理小表”中，在构建出完整“逻辑表”

分区表的优点

①更少的数据检索范围

②拆分超级大的表，将部分数据加载至内存

③分区表的数据更容易维护

④分区表数据文件可以分布在不同的硬盘上，并发IO

⑤减少锁的范围，避免大表锁表

⑥可独立备份，恢复分区数据

创建分区表的语法：

DROP TABLE test_partition;

CREATE TABLE test_partition(

id INT(11) NOT NULL,

create_time DATETIME NOT NULL,

cyear INT,

PRIMARY KEY (id,create_time,cyear)

)ENGINE = INNODB DEFAULT CHARSET = utf8

PARTITION BY RANGE (cyear)

(

PARTITION y14before VALUES LESS THAN(2014),

PARTITION y14_15 VALUES LESS THAN(2015),

PARTITION y15_16 VALUES LESS THAN(2016),

PARTITION y16_17 VALUES LESS THAN(2017),

PARTITION y17_18 VALUES LESS THAN(2018),

PARTITION y18_19 VALUES LESS THAN(2019),

PARTITION y19_20 VALUES LESS THAN(2020),

PARTITION y20after VALUES LESS THAN maxvalue ENGINE = INNODB);

分区表的使用限制：

①查询必须包含分区列，不允许对分区列进行计算

②分区列必须是数字类型

③分区表不支持建立外键索引

④建表时主键必须包含所有的列

⑤最多1024个分区

分库分表介绍

①将数据库存放在多台MySQL服务器

②缺点：数据分布不均匀，未能根本解决海量数据存储问题

数据分表：将一张大数据量的表分解成几张表，每张表的结构都一样，通过一定算法来

分库分表中间件：

	支持分库	支持分表	中间层	开元	数据库支持	支持语言	跟新频率
MyCat	支持	支持	是	是	MySQL	任意
Cobar	支持	不支持	是	是	MySQL	任意
Altlas	支持	支持	是	是	MySQL	任意
TDDL	支持	支持	否	是	任意	Java
OneProxy	支持	支持	是	否	MySQL	任意
Sharding SPhere	支持	支持	否	是	任意	Java

Sharding Sphere 介绍

Sharding Sphere 分库分表框架入门

Sharding Sphere结构：

①Sharding Sphere是一套开源的分布式数据库中间件解决方案组成的生态圈

②Sharding Sphere前身是ShardingJDBC

③Sharding Sphere是为数不多的国产活跃的数据库中间件

④目前最新版本4.X

Sharding-JDBC：

①定位为轻量级Java框架，在Java的JDBC层提供的额外服务

②他使用客户端直连数据库，以jar形式提供服务

③可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架

Sharding - JDBC分库分表配置

Sharding-JDBC分库分表

<sharding:inline-strategy id="databaseStrategy" sharding-column="user_id"

algorithm-expression="ds$->{user_id % 2}"/>

<sharding:inline-strategy id="orderTableStrategy" sharding-column="order_id" algorithm-expression="t_order_$->{order_id % 2}"/>

<sharding:key-generator id="orderKeyGenerator" type="SNOWFLAKE" column="order_id"/>

<sharding:data-source id="shardingDataSource">

<!--

sharding:sharding-rule定义分表规则:

data-source-names="ds0,ds1" 说明有几个数据源

sharding:table-rule 节点定义数据存储规则

logic-table="t_order" 代表逻辑表名

actual-data-nodes="ds$->{0..1}.t_order_$->{0..1}"

说明数据分布在ds0与ds1两个数据源,物理表存在t_order_0与t_order_1

database-strategy-ref="databaseStrategy" 说明order表分库策略

基于order.user_id值对2取余保存到ds0或ds1的数据库中

table-strategy-ref="orderTableStrategy"

基于order.order_id对2取余决定存储在对应数据库的 t_order_0或者t_order_1表中

实例:

insert into t_order(order_id , user_id,status) values( ? , ? , 'N')

order_id user_id

1 2 -> ds0.order_1

2 2 -> ds0.order_0

3 1 -> ds1.order_1

4 1 -> ds1.order_0

-->

<sharding:sharding-rule data-source-names="ds0,ds1">

<sharding:table-rules>

<sharding:table-rule logic-table="t_order"

actual-data-nodes="ds$->{0..1}.t_order_$->{0..1}"

database-strategy-ref="databaseStrategy"

table-strategy-ref="orderTableStrategy"

key-generator-ref="orderKeyGenerator"

</sharding:table-rules>

</sharding:sharding-rule>

</sharding:data-source>

主多从数据同步

①修改MySQL配置文件my.ini,将server_id 设置成唯一的值

②在MySQL实力中设置

主服务器配置：

#'salve'@'本机IP地址'

#salve 是用户名后面 salve 是密码

#‘IP地址’是允许访问的远程IP地址，这里写从属服务器的IP

create user 'salve'@'IP地址' IDENTIFIED by 'salve';

#为salve 用户授予主从复制的权限

grant replication salve on *.* to 'salve'@'IP地址';

#激活权限

flush privileges；

show master status；

show binlog events in ‘mysql-bin.000001’;

从服务器配置：

CHANGE MASTER TO

MASTER_HOST=‘主机服务器IP’，

MASTER_PORT=3307，

MASTER_USER=‘salve’，

MASTER_PASSWORD=‘salve’

MASTER_LOG_FILE=‘mysql-bin.000001’,

MASTER_LOG_POS=951;

start salve;

show salve status;

ShardingJDBC配置读写分离

①引入ShardingJDBC的JAR包

②配置多个数据源，主从服务器，以及ShardingJDBC读写数据库

第五章、MySQL服务器与硬件调优

设置MySQL应用参数的三种方式

①set[session]参数名=参数值；#设置当前会话（连接）参数

#set session 代表在当前会话（窗口/连接）才有效，关闭会话后自动失效

#参数设置的优先级 session > global >配置文件

②set global 参数名 = 参数值；#设置全局参数

#set global 在MySQL服务器运行过程中会一直生效，知道MySQL关闭

#值得注意的是：部分参数在set global 并不会立即生效，需要重新建立连接后才会有效

③设置应用配置文件：

. window 将存放到my.ini 应用程序根目录

. Linux 保存在/etc/my.conf

#在MySQL安装路径上创建my.ini文件，打开配置如下：

[mysqld]

port = 3307

max_connection = 100

重启MySQL；

Connection 连接参数

#max_connections 代表数据库同时允许的最大允许连接数

#连接有两种常见的状态：sleep / query

#sleep 代表连接处于闲置状态

#query 代表连接正处于处理任务的状态

#sleep + query 连接的总量不能超过max_connections的设置值

#否则会出现经典错误："ERROR 1040：TOO many connections"

show VARIABLES like 'max_connections'

set global max_connections = 300;

show status like 'Threads%';

#Threads_connected 代表当前已经有多少连接（sleep + query）

#Threads_created 代表历史总共创建过多少数据库连接

#Threads_running 代表有几个连接正处在“工作”状态吗，也是目前的并发数

#Threads_cached

共缓存过多少连接，如果我们在MySQL服务器配置文件中设置了thread_cache_size ，当客户端断开之后

，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁（前提是缓存数未达上限）

set global thread_cache_size = 80；#代表数据库最大允许缓存80个数据库连接，超出80个之后就是关闭了再重建

#MySQL 历史运行过程中最大连接的数量及时点

show status like ‘Max_used_connections%’;

参数back_kog：设置保存多少数据库请求到堆栈（缓冲区）中，

#也就是说，如果MySQL的连接数达到max_connections时，新来的请求将会被存到堆栈中，以等待某一连接释放资源，该堆栈的数量即

back_log，如果等待连接的数量超过back_log，将不会授予连接资源。将会报：

参数：wait_timeout和interactive_timeout

#这两个参数都是至超过一段时间后，数据库连接自动关闭（默认28800秒，即8小时）

#interactive_timeout针对交互式连接，wait_timeout针对非交互式连接。

#说得直白一点，通过MySQL 客户端连接数据库是交互式连接，通过jdbc连接数据库是非交互式连接

show variables like 'wait_timeout';

show variables like 'interactive_timeout';

show processlist；

#查看当前数据库连接的数据库信息

查询缓存（QC）参数设置

查询缓存(Query Cache)

①使用查询缓存，MySQL将查询结果存放在缓存区（内存）中

②今后对同样的select 语句（区分大小写），将直接从缓冲区中读取结果。

set global query_cache_size = 1024000000;

show status like 'Qcache%';

#Qcache_free_memory:Quert Cache

中目前剩下的内存大小。通过这个参数我们可以较为准确的观察当前系统中的Query

Cache内存大小是否足够，是需要增多还是过多了。

#Qcache_lowmen_prunes ：多少条Query 因为内存不足而被清除出Query Cache

通过 Qcache_lowmen_prunes 和 Qcache_free_memory

相互结合，能够更清楚的了解到我们系统中Query Cache 的内存大小是否真的足够，是否非常频繁地出现因为内存不足而有Query被换出。这个数字最好是长时间来看，如果这个数字在不断增长，就表示可能碎片化非常严重，或者内存很少

#Qcache_total_blocks:当前Query Cache中block的数量

#Qcache_free_blocks:缓存中相邻内存块的个数。如果该值显示过大，则说明Query

Cache中的内存中的碎片较多了

#查询缓存碎片率：QCache_free_block/Qcache_total_block*100%

#如果查询缓存碎片率超过20%，可以用flush query cache 整理碎片

#block默认是4KB，设置大小对大数据查询有好处，但是如果查询的都是小数据查询，就容易造成内存碎片和浪费。

#Qcache_hits:表示有多少次命中缓存。我们主要可以通过该值来验证我们查询能缓存的效果，

数字越大缓存效果月理想

#Qcache_inserts: 表示多少次未命中而插入，意思是新来的SQL请求缓存中未找到，不得不执行查询处理，执行查询处理后把结果inset带查询缓存中。这样的情况越多，表示查询缓存应用到的比较少，效果也就不理想。

#Qcache_queries_in_cache: 当前Query Cache 中cache的Query数量

#Qcache_queries_in_cache: 当前缓存中缓存的查询数量

#Qcache_not_cached：未进入查询的select个数