数据库调优（Mysql）

凌木LSJ

已于 2023-11-28 16:57:53 修改

阅读量331

点赞数 1

分类专栏： JavaWeb 文章标签： 1024程序员节 java mysql 数据库

于 2023-10-27 11:12:00 首次发布

本文链接：https://blog.csdn.net/xlsj228/article/details/133983593

版权

JavaWeb 专栏收录该内容

19 篇文章 1 订阅

订阅专栏

基础知识

1）MySQL 架构的总览图

SQL 查询的时候，首先需要去缓存查询是否存在，如果不存在才进行命令解析，查询优化器优化之后才生成执行计划；
我们的 SQL 语句类型会分为 DML（select/insert/update/delete）、DDL（创建数据库、创建表等）等，其中核心的是 DML 里面的 select，它是比较核心的，MySQL 优化器会对 select 语句进行调整，让其查询效率最优；
底层存储引擎，主要分为 InnoDB、MyISAM、Memory，MySQL 默认是使用 InnoDB，它是有事务的、数据安全的存储引擎；
InnoDB 存储引擎模式的数据结构是 B+Tree。

2）SQL执行效率监控

主要有以下两个步骤：

第一：开启 MySQL 的慢查询日志，设置一个时间阀值，超过这个时间的 sql 将会被记录下来windows 的 my.ini 文件，Linux 的 my.cnf 文件

#在[mysqld]一行下面加入两个配置参数
slow_query_log = on #开启慢查询
log-slow-queries=/data/mysqldata/slow-query.log #慢查询日志保存路径
long_query_time=5 #设置慢查询时间5s
log-queries-not-using-indexes=on #记录没有使用索引查询语句

第二：EXPLAIN 来解析 SQL，定位慢的原因是什么

select_type：表示查询类型，分别有 SIMPLE、PRIMARY、UNION 三种
- SIMPLE：表示简单查询，其中不包含连接查询和子查询
- PRIMARY：表示主查询，或者是最外面的查询语句
- UNION：表示连接查询的第 2 个或后面的查询语句

type：表示连接类型

以下的连接类型的顺序是从最佳类型到最差类型，以下是在 innodb 存储引擎基础上进行分析

类型	说明
const	通常情况下，如果将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量
eq_ref	ref_eq 与 ref 相比牛的地方是，它知道这种类型的查找结果集只有一个，使用了主键或者唯一性索引进行查找的情况
ref	出现该连接类型的条件是：查找条件列使用了索引而且不为主键和 unique。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描
range	指的是有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于 index；between，and 以及’>’,’<'外，in 和 or 也是索引范围扫描
index	这种连接类型只是另外一种形式的全表扫描，只不过它的扫描顺序是按照索引的顺序
ALL	意味着你的sql语句处于一种最原生的状态，有很大的优化空间

possible_keys：表示指出 MySQL 能使用哪个索引在该表中找到行，如果该列为 NULL，说明没有使用索引，可以对该列创建索引来提高性能
key：显示 MySQL 实际决定使用的键（索引）。如果没有选择索引,键是NULL
key_len：显示 MySQL 决定使用的键长度。如果键是 NULL，则长度为 NULL
ref：显示使用哪个列或常数与 key 一起从表中选择行
rows：显示 MySQL 认为它执行查询时必须检查的行数
Extra：表示是否是覆盖索引（Using Index）

1 索引查询

索引是帮助数据库高效查询的一种数据结构，索引就是类似我们字典里面的目录，可以提高查找效率：

一个表如果添加多个索引（一个字段一个索引），那么底层会根据每个索引创建一个索引文件，它也是存储在服务器磁盘里面的。它是通过某种数据结构来存储，可以提高查询效率；
我们知道常用的数据库结构有数组、链表、二叉树等等，然后 MySQL 的 InnoDB 存储引擎使用的是 B+Tree 这种数据结构，也称平衡二叉树。

查询语句：select * from t where t.Col2 =89;

不加索引进行数据库查询时，每次都需要将所有数据遍历一次，直到找到符合目标的数据。

加上索引之后，可以根据数据结构不同，减少查询的次数，进而优化。

例如，以下语句创建一个新表，其索引由两列c2和c3组成。
CREATE TABLE t(
   c1 INT PRIMARY KEY,
   c2 INT NOT NULL,
   c3 INT NOT NULL,
   c4 VARCHAR(10),
   INDEX (c2,c3) 
); 

要为列或一组列添加索引，请使用以下CREATE INDEX语句：
CREATE INDEX index_name ON table_name (column_list) 

要从表中删除现有索引，请使用以下DROP INDEX语句：
DROP INDEX index_name ON table_name

二叉树

如果以二叉树的形式存储：key存储地址，value存储值。查询从跟节点开始的话，很快便能查找到。

二叉树的缺点是弱存储Col1数据，最终相当于存了一个链表。

红黑树

要求：

节点是红色或黑色，根是黑色。
所有叶子都是黑色。
从每个叶子到根的所有路径上不能有两个连续的红色节点。

从任一节点到叶子节点的所有路径都包含相同数目的黑色节点。（黑高相同，叶子节点其实是哪些null节点）

也称为二叉平衡树，他会平衡左右分支的高度。（插入时都做为红色节点插入，出现不符合红黑树要求时就进行调整。）

B-Tree

一颗m阶的B树定义如下：

1）每个结点最多有m-1个关键字。

2）根结点最少可以只有1个关键字。

3）非根结点至少有Math.ceil(m/2)-1个关键字。

4）每个结点中的关键字都按照从小到大的顺序排列，每个关键字的左子树中的所有关键字都小于它，而右子树中的所有关键字都大于它。

5）所有叶子结点都位于同一层，或者说根结点到每个叶子结点的长度都相同。

B+ Tree

MySQL索引底层：B+树

非叶子节点不存储data，只存储索引，可以放更多的索引
叶子节点包含所有索引字段
叶子节点用指针连接，提高区间访问的性能

B+树与B树的区别：

B+树在内部节点上不包含数据信息，因此在内存页中能够存放更多的key。

B+树的叶子结点都是相链的，因此对整棵树的便利只需要一次线性遍历叶子结点即可。

HASH

HASH查找的效率很高，但不支持范围查找（age > 10）。

2 存储引擎

查询当前数据库支持的引擎，默认是MyISAM

MyISAM存储引擎

使用这个存储引擎，每个MyISAM在磁盘上存储成三个文件。

（1）frm文件：存储表的定义数据

（2）MYD文件：存放表具体记录的数据

（3）MYI文件：存储索引

MyISAM拥有较高的插入、查询速度，但不支持事物。使用B+树进行存储，叶子节点存储地址。

支持数据的类型也有三种：

（1）静态固定长度表

（2）动态可变长表

（3）压缩表

InnoDB存储引擎

跟MyISAM一样也是B+树索引，不过InnoDB叶子节点存储的是完整列数据——聚集索引，所以InnoDB表创建需要创建主键，同时最好使用整型自动递增auto_increment。

InnoDB支持事务,存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度。

3 联合索引

MySQL可以在多个列上建立索引，这种索引叫做复合（联合）索引，是一种非聚簇索引。MySQL允许您创建一个最多包含16列的复合索引。

联合索引的特点

最左前缀原则： SQL 语句中用到了联合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配，值得注意的是，当遇到范围查询(>、<、between、like)就会停止匹配。

最左匹配例子说明

create index table_name on test(a,b,c,d)

那么根据联合索引的最左匹配原则我们进行如下查询是会走索引的：

select * from table_name where a = '1';
select * from table_name where a = '1' and b = '2';
select * from table_name where a = '1' and b = '2' and c = '3';
select * from table_name where a = '1' and b = '2' and c = '3' and d = '4';

顺序可以随意，这种也是可以走联合索引的，Mysql有优化器会自动调整查询条件的顺讯跟索引顺序一致。比如：

select * from table_name where b = '1' and c = '2' and a = '3' and d = '4';

但是没有a，剩下的几个字段是都不会走索引！

但是，如果你在中间加入了模糊查询例如：

select * from table_name where a = '1' and b = '2' and c > '3' and d = '4';

那么就只有a，b，c会走索引，因为c用了模糊查询d是不会走联合索引的

实例演示

4个字段id name age sex，:

create table user(
	id int(2) primary key AUTO_INCREMENT,
	`name` char(8) not null,
	age int(2) not null,
	sex char(2) not null,
	index(`name`,age)
)ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

给索引起个别名

create table user(
	id int(2) primary key AUTO_INCREMENT,
	`name` char(8) not null,
	age int(2) not null,
	sex char(2) not null,
	key name_index (`name`),
	key name_age_index (`name`,age)
)ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

explain select * from user;

不使用索引，查询结果如下

explain select id from user where name = 'lihua' and age > 20;

依据mysql索引最左匹配原则，两个索引都匹配上，查询结果如下：

explain select * from user where id=4 and name = 'lihua';

查询结果如下：

explain select * from user where age = 22;

索引失效，最左匹配不生效，因为复合索引(name ,age)只有name确定时才会生效；

type列：

4 索引规约

注意：

① 创建联合索引的时候，查询的时候要带上第一个字段，否则索引失效

② 不要使用函数、表达式、计算，索引无效
select * from t_test where left(name, 4) = 'zwy';

select * from t_test where age+1=5;
③ like 查询的时候，xxx%索引有效；%xxx索引无效；%xxx%索引无效

④ not in 和 <>、!= 无法使用索引

⑤ <，<=，=，>，>=，BETWEEN，IN 会使用索引

⑥ IS NULL 或者 IS NOT NULL 无法使用索引

⑦ order by ,group by 可以用到索引

⑧ 索引字段如果是字符串，查询时则必须单引号括起来，否则会自动转义

⑨ 如果条件中有 or，即使其中有条件带索引也不会使用；但是注意的是，只有 OR 关键字，且 OR 前后的两个条件中的列都是索引时，索引才会生效，否则，索引不生效。

【强制】业务上具有唯一特性的字段，即使是组合字段，也必须建唯一索引。

说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。

【强制】超过三个表禁止 join。需要join 的字段，数据类型保持绝对一致;多表关联查询时保证被关联的字段需要有索引。

【强制】在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。

说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90%以上，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。

【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

说明：索引文件具有 B+Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引，全模糊也会失效。

【推荐】如果有 order by 的场景，请注意利用索引的有序性。

order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后。

【推荐】利用覆盖索引来进行查询操作，避免回表。

覆盖索引（covering index ，或称为索引覆盖）即从非主键索引中就能查到的记录，而不需要查询主键索引中的记录，避免了回表的产生减少了树的搜索次数，显著提升性能。

【推荐】利用延迟关联或者子查询优化超多分页场景。

通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据，尤其在大分页查询的场景下，可以提高查询效率。如下面第二条的执行速度要远远快于第一条SQL：

select * from table where xxx limit a,b;

select * from table where id in (select id from table where xxx limit a,b);

【推荐】 SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts最好。

说明：

全表扫描（All）：这是性能最低的级别。当查询没有使用索引或涉及大部分表数据时，通常会出现全表扫描。全表扫描意味着数据库系统需要遍历整个表来寻找匹配的行，这通常会导致性能问题。
范围扫描（Range）：范围扫描表示查询使用了索引，并且索引能够减少查询的数据量。这是性能提升的第一步，但仍需要扫描多个行来找到满足条件的数据。
索引引用（Ref）：当查询可以通过索引直接定位到唯一一行数据时，这被认为是性能更好的情况。通常，这需要在WHERE条件中使用主键或唯一索引，以便直接引用所需的数据行。
常数查找（Consts）：这是性能的最高级别。当查询可以直接定位到一个特定的数据行，而不需要进行任何扫描或比较时，性能最佳。这通常发生在使用主键或唯一索引来精确查找特定数据行的情况下。

【推荐】建组合索引的时候，区分度最高的在最左边。

【推荐】防止因字段类型不同造成的隐式转换，导致索引失效。

在执行SQL查询或操作时，MySQL会自动将数据类型转换为适当的类型，以便执行操作或比较。

比较操作符的隐式转换：将一个整数和一个字符串进行比较时，MySQL可能会将字符串转换为整数以进行比较。
数据类型优先级： MySQL有一套规则来确定数据类型的优先级，用于隐式转换。通常，数值类型的优先级高于字符串类型。这意味着，如果涉及数值和字符串类型的比较，字符串类型通常会被转换为数值类型。
显式类型转换：为避免隐式转换引发问题，应该尽量避免混合不同数据类型的比较。如果需要进行比较，可以使用显式的类型转换函数，如CAST()或CONVERT()，将数据类型显式转换为相同的类型。

SELECT id FROM sample WHERE id = CAST('1' AS SIGNED); -- 返回结果，显式将'1'转换为整数进行比较
SELECT id FROM sample WHERE name = CAST(5 AS CHAR); -- 返回结果，显式将5转换为字符串进行比较

其他一些常见的需要注意的写法归纳：

① 使用 union 代替 or

/*不建议*/
select id from t_user where age=10 or age=20;

/*建议*/
select id from t_user where age=10 union all select id from t_user where age=20;

② 连续区间

/*不建议*/
select *  from t_user where age in(1,2,3,4,5);

/*建议*/
select * from t_user where age between 1 and 3;

③ 用 exists 代替 in

/*不推荐*/
select * from t_table1 where id in (select uid from t_table2);

/*推荐*/
select * from t_table1 t1 where exists(
	select uid from t_table2 t2 where t1.id=t2.uid
);

④ 少用 select *，尽量把具体的字段列举出来

/*不推荐*/
select * from t_user;
/*推荐*/
select id,name,age from t_user;

⑤ count(1) 比 count(*) 更有效率

/*不推荐*/
select count(*) from t_user;
/*推荐*/
select count(1) from t_user;

5 事物

为了解决多事务并发问题，数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制、日志机制，用一整套机制来解决多事务并发问题。

5.1 事物特性

数据库中的事务（Transaction）是一组数据库操作，它被视为一个不可分割的工作单元，这些操作要么全部成功执行，要么全部失败，具有以下四个关键特性，通常被称为 ACID 特性：

原子性（Atomicity）：事务是原子的，它要么全部执行成功，要么全部执行失败，没有部分执行的情况。如果事务的任何一部分失败，整个事务都将被回滚（撤销）到初始状态。
一致性（Consistency）：事务将数据库从一个一致的状态转移到另一个一致的状态。这意味着事务执行后，数据库必须满足一组事先定义的规则和完整性约束，以保持数据的一致性。
隔离性（Isolation）：隔离性确保并发执行的事务不会互相干扰。每个事务在看待数据时，就像它是唯一正在运行的事务一样，不会受到其他事务的影响。隔离性通常通过锁定或多版本控制等机制来实现。
持久性（Durability）：一旦事务成功提交，其对数据库的更改是永久性的，即使系统崩溃或断电，数据库的状态也不会受到影响。持久性通过将事务更改写入持久存储介质（如磁盘）来实现。

数据库中的事物分为隐式事物（数据库自动提交）和显示事物（需要手动设置）：

隐式事物（数据库自动提交）：

SELECT查询：一般情况下，SELECT查询语句通常是自动提交事务的，它们不会显式地启动或提交事务。
INSERT、UPDATE和DELETE：这些写操作通常是自动提交事务的。

显示事物（需要手动设置）：

需要确保一系列SQL操作要么全部成功，要么全部失败，你需要手动设置事务。例如多个INSERT、UPDATE、DELETE语句的组合，以及可能的其他操作。

可以使用BEGIN TRANSACTION或START TRANSACTION语句来明确开始一个事务。
在开始事务后，执行你需要在同一事务中执行的SQL操作。
如果所有操作都成功，你可以使用COMMIT语句来提交事务，使更改永久生效。
如果任何操作失败或发生错误，你可以使用ROLLBACK语句来回滚事务，取消操作并恢复到事务开始前的状态。

5.2 事物隔离级别

并发事务处理往往会带来一些问题，如：

脏写或更新丢失(Lost Update)

当两个或多个事务选择同一行数据修改，有可能发生更新丢失问题，即最后的更新覆盖了由其他事务所做的更新。

脏读（Dirty Reads）

事务A读取到了事务B已经修改但尚未提交的数据。

不可重复读（Non_Repeatable Reads）

事务A内部的相同查询语句在不同时刻读出的结果不一致。

幻读（Phantom Reads）

事务A读取到了事务B提交的新增数据，在同一个事务中进行相同查询两次，但第二次查询返回的结果集比第一次查询时不一样。

解决并发事物带来的问题，可以使用隔离级别。数据库管理系统（DBMS）通常支持多个隔离级别，如读未提交（Read Uncommitted）、读已提交（Read Committed）、可重复读（Repeatable Read）、串行化（Serializable）等。

查看当前数据库的事务隔离级别:

show variables like 'tx_isolation';
设置事务隔离级别：

set tx_isolation = 'REPEATABLE-READ';

6 存储过程

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来调用存储过程。

-- 创建存储过程 
create procedure mypro(in a int,in b int,out sum int) 
begin 
    set sum = a+b; 
    select sum;
end;

call mypro(1,2,@s);-- 调用存储过程 
select @s;-- 显示过程输出结果


显示存储过程
SHOW PROCEDURE STATUS;
SHOW PROCEDURE status where db = 'userdb';
显示名称中包含“my”的存储过程
SHOW PROCEDURE status where name like '%my%';
显示存储过程“mypro1”的源码
SHOW CREATE PROCEDURE mypro1;
删除存储过程“mypro1”
drop PROCEDURE mypro1;

7 主从复制

MySQL主从复制，是将数据库的DDL和DML操作通过二进制日志传输到从数据库的服务器，然后再从数据库上对这些日志重新执行，从而使得从库和主库的数据保持同步。一般情况下一台主库多台从库，但是从库也可以作为别的从库的主库，实现链状复制。

一般情况下，对于数据库来说读的压力往往会比写的压力大很多。因此需要读写分离（主库写，从库读）；多个从库实现负载均衡对外提供读的服务。

环境搭建

1）MASTER节点搭建

第一步：开启binlog日志文件，位置（Windows的是my.ini文件，Linux的是my.cnf文件）

#mysql 服务ID，保证整个集群环境中唯一的
server-id=1

#mysql binlog日志的存储路径和文件名
log-bin=/usr/local/mysql/mysqlbin

#是否只读，1只读，0读写
read-only=0

#指不需要被同步的数据库（mysql是管理的数据库）
binlog-ignore-db=mysql

#指定需要同步的数据库
#binlog-do-db=test1

第二步：重启MySQL

service mysql restart

第三步：创建同步数据的账号，并且进行授权

这个步骤可以省略，直接使用root账号进行操作也可以。

#1.创建
create user 'test'@'%' identified by '123';

#2.授权：test是账号，123是密码，ip指的是从节点的ip不是主节点ip（或者写%表示所有机器）
# replication表示：备份权限； *.*表示：所有的数据.所有的表
grant replication slave on *.* to 'test'@'192.168.1.9' identified by '123';

#3.刷新
flush privileges;

第四步：查看主节点的状态

#注意：该命令必须登录mysql进入mysql命令行去执行
show master status;

File表示binlog日志文件
Position表示从日志文件的哪个位置开始推送日志
Binlog_Ignore_DB表示不需要推送的数据库
Binlog_Do_DB表示被推送的数据库

2）Slave节点搭建

第一步：修改配置文件，位置（Windows的是my.ini文件，Linux的是my.cnf文件）

#mysql 服务ID，保证整个集群环境中唯一的
server-id=2

#mysql binlog日志的存储路径和文件名
log-bin=/usr/local/mysql/mysqlbin

第二步：重启MySQL

service mysql restart

第三步：执行命令

change master to master_host='192.168.1.8',master_user="test",master="123" master_log_file="mysqlbin.000001",master_log_pos=413;

该命令的含义是给从节点指定主节点、主节点的日志文件、日志文件的行数；
master_log_file和master_log_pos分别表示binlog日志文件名称、从第几行开始同步，登录主节点然后输入show master status;命令来查看。

第四步：开始同步及状态查看

#1.开始同步
start slave;
#2.停止同步
stop slave;
#3.查看状态
show slave status;#没有格式
show slave status\G;#有格式

确保这两个地方是yes即可

第五步：测试数据同步

往master数据库创建一个数据库，创建一个表，插入一条数据，再去slave节点看是否同步过去即可。

读写分离

读写分离常见的办法其实是有两种，第一种是基于Spring的动态数据源实现；第二种是依赖第三方中间件，比如：MyCat。

第一种方法：

第一步：项目启动的时候，根据DataSourceConfig配置类的配置来创建DynamicDataSource实例，然而DynamicDataSource内部根据配置文件创建不同的DataSource
第二步：Aop拦截了Service的所有实现类的所有方法，Service实现类方法被调用之前，首先判断该方法属于读操作还是写操作（根据方法名称判断），根据判断结果获取一个标识。
第三步：动态数据源路由根据标识获取真正的数据源，然后开启事务
第四步：才是真正执行Service的目标方法
第五步：执行完成，提交事务

1）配置文件

datasource.type=read,write

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource

#写数据库
spring.datasource.write.jdbc-url=jdbc:mysql://192.168.1.8:8066/mycat_testdb
spring.datasource.write.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.write.username=root
spring.datasource.write.password=123456
        
#读数据库
spring.datasource.read.jdbc-url=jdbc:mysql://192.168.1.9:8066/mycat_testdb
spring.datasource.read.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.read.username=root
spring.datasource.read.password=123456

2）动态数据源

public class DynamicDataSource extends AbstractRoutingDataSource {
    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();
	public static void setDbType(String dbType) {
		contextHolder.set(dbType);
	}
	public static String getDbType() {
		return contextHolder.get();
	}
	public static void clearDbType() {
		contextHolder.remove();
	}
    
    //根据标识获取真正的数据源
	@Override
	protected Object determineCurrentLookupKey() {
		return getDbType();
	}
}

3）数据源配置

@Configuration
public class DataSourceConfig {
    @Value("${datasource.type}")
    private String datasourcetype;
    @Autowired
    private Environment env;
    
    @Bean
    @Primary
    public DynamicDataSource dynamicDataSource(){
        DynamicDataSource dds=new DynamicDataSource();
 		Map<Object, Object> map = new HashMap<>();
        
        //动态创建数据源
        String[] types=datasourcetype.split(",");
        DruidDataSource defaultDataSource=null;
        for(String type:types){
            String url="spring.datasource."+type+".jdbc-url";
            String driverclass="spring.datasource."+type+".driver-class-name";
            String username="spring.datasource."+type+".username";
            String pwd="spring.datasource."+type+".password";
            
            //创建数据源
            DruidDataSource ds= new DruidDataSource();
            ds.setDriverClassName(env.getProperty(driverclass));
            ds.setUrl(env.getProperty(url));
            ds.setUsername(env.getProperty(username));
            ds.setPassword(env.getProperty(pwd));
            
            //默认数据源
            if("write".equals(type)){
                defaultDataSource=ds;
            }
            
            map.put(type, ds);
        }		
        
        //设置数据源
		dds.setTargetDataSources(map);
        //默认数据源（如果标识为空，则使用默认数据源）
		dds.setDefaultTargetDataSource(defaultDataSource); 
        return dds;
    }
}

4）AOP类

@Aspect
@Component
@Order(0) // Order设定AOP执行顺序 使之在数据库事务上先执行
public class SwitchDataSourceAOP {
    private static List<String> lists=new ArrayList<>();
    static{
        lists.add("find");
        lists.add("get");
    }
       
    //提示：判断使用哪个数据源有两种模式
    //1）判断以常见关键字开头，比如：findXxxx、getXxxx
    //2）注解的模式
	@Before("execution(* com.micro.service.*.*(..))")
	public void process(JoinPoint joinPoint) {
		String methodName = joinPoint.getSignature().getName();
        boolean isread=false;
        for(String str:lists){
            if(methodName.startWith(str)){
                isread=true;
                break;
            }
        }        
		if (isread) {
            //提示：这里如果多个读数据库，则可以实现负载均衡算法
			DynamicDataSource.setDbType("read");
		} else {
			DynamicDataSource.setDbType("write");
		}
	}
}

8 其他

数据库优化维度有四个：硬件、系统配置、数据库表结构、SQL及索引。优化选择：

1、优化成本: 硬件>系统配置>数据库表结构>SQL及索引

2、优化效果: 硬件<系统配置<数据库表结构<SQL及索引

检查问题常用工具：

mysql
msyqladmin         mysql客户端，可进行管理操作
mysqlshow          功能强大的查看shell命令
show [SESSION | GLOBAL] variables    查看数据库参数信息
SHOW [SESSION | GLOBAL] STATUS       查看数据库的状态信息
information_schema                   获取元数据的方法
SHOW ENGINE INNODB STATUS            Innodb引擎的所有状态
SHOW PROCESSLIST                     查看当前所有连接session状态
explain                              获取查询语句的执行计划
show index 查看表的索引信息
slow-log 记录慢查询语句
mysqldumpslow                 分析slowlog文件的

不常用但好用的工具：

zabbix                  监控主机、系统、数据库（部署zabbix监控平台）
pt-query-digest         分析慢日志
mysqlslap               分析慢日志
sysbench                压力测试工具
mysql profiling         统计数据库整体状态工具
Performance Schema mysql性能状态统计的数据
workbench               管理、备份、监控、分析、优化工具（比较费资源）

凌木LSJ

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库调优（Mysql）

索引是帮助数据库高效查询的一种数据结构：查询语句：select * from t where t.Col2 =89;不加索引进行数据库查询时，每次都需要将所有数据遍历一次，直到找到符合目标的数据。加上索引之后，可以根据数据结构不同，减少查询的次数，进而优化。
复制链接

扫一扫