赞
踩
1)下载安装wget命令
yum -y install wget
2)在线下载mysql安装包
mkdir mysql
cd mysql
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.32-1.el7.x86_64.rpm-bundle.tar
3)解压mysql安装包:
tar -xvf mysql-5.7.32-1.el7.x86_64.rpm-bundle.tar
安装包介绍:
安装包名称 | 简介 |
---|---|
mysql-community-client | MySQL客户端应用程序和工具 |
mysql-community-common | 服务器和客户端库的通用文件 |
mysql-community-devel | MySQL数据库客户端应用程序的开发头文件和库 |
mysql-community-embedded-compat | MySQL服务器作为嵌入式库,与使用库版本18的应用程序兼容 |
mysql-community-libs | MySQL数据库客户端应用程序的共享库 |
mysql-community-libs-compat | 以前的MySQL安装的共享兼容性库 |
mysql-community-server | 数据库服务器和相关工具 |
mysql-community-server-debug | 调试服务器和插件二进制文件 |
mysql-community-test | MySQL服务器的测试套件 |
mysql-community | RPM的源代码看起来类似于mysql-community-8.0.24-1.el7.src.rpm,具体取决于所选的OS |
4)安装mysql服务:
rpm -ivh mysql-community-server-5.7.32-1.el7.x86_64.rpm
出现如下错误:
安装server被其他模块依赖;我们必须安装顺序来安装
5)安装如下顺序进行安装:
rpm -ivh mysql-community-common-5.7.32-1.el7.x86_64.rpm --nodeps --force
rpm -ivh mysql-community-libs-5.7.32-1.el7.x86_64.rpm --nodeps --force
rpm -ivh mysql-community-client-5.7.32-1.el7.x86_64.rpm --nodeps --force
rpm -ivh mysql-community-server-5.7.32-1.el7.x86_64.rpm --nodeps --force
6)启动MySQL
systemctl start mysqld
1)获取临时密码
grep 'temporary password' /var/log/mysqld.log
2)登录MySQL
mysql -uroot -p
然后输入刚刚复制的密码
3)将MySQL的密码校验强度改为低风险
set global validate_password_policy=LOW;
4)修改MySQL的密码长度
set global validate_password_length=5;
5)修改MySQL密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'admin';
1)首先要关闭Cenots的防火墙
sudo systemctl disable firewalld
2)切换到mysql数据库
use mysql;
3)查看user表
select Host,User from user;
4)发现root用户只运行localhost访问,修改为允许任何地址访问
update user set Host='%' where User='root';
5)刷新权限
flush privileges;
6)使用Navicat连接工具测试
7)设置不区分大小写
Linux版本的MySQL默认是区分大小写了,如果想要使MySQL不区分大小写,可以在MySQL的配置文件(/etc/my.cnf)中设置:
lower_case_table-names=1
8)重启服务器
systemctl restart mysqld
CREATE DATABASE `db01` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `db01`; DROP TABLE IF EXISTS `class`; CREATE TABLE `class` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; insert into `class`(`id`,`name`) values (1,'Java'), (2,'UI'), (3,'产品'); DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `class_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; /*Data for the table `student` */ insert into `student`(`id`,`name`,`class_id`) values (1,'张三',1), (2,'李四',1), (3,'王五',2), (4,'赵刘',1), (5,'钱七',3);
MySQL 数据库是单进程多线程的架构,和 SQL Server 类似,和 Oracle 不一样,Oracle 是多进程架构。
MySQL官网(5.7版本):https://dev.mysql.com/doc/refman/5.7/en/
MySQL主要有如下组件构成:
1)用户发送SQL语句来到MySQL服务端
2)首先查询缓存,如果缓存有数据直接响应,如果没有则进行下一步
3)解析器进行解析SQL语句、预处理、经过优化器优化SQL语句等一系列操作,调用存储引擎
4)存储引擎去磁盘上读取数据,最终将数据以MySQL的方式(即行和表的方式)响应给客户端,并且存入查询缓存一份。
一条SQL来到MySQL服务端如果没有命中缓存则接下来要被解析器(parser)进行语法的解析,首先将一段完整的SQL语句拆分成若干的词语(关键字、表名、列名、查询条件等),之后根据语法中的关键字进行关键字提取,生成一颗SQL解析树,之后进行SQL语句的预处理,例如检查列名是否存在,表是否存在,语法是否正确等;
SQL语句在经过解析器得到语法树之后,MySQL内部提供有优化器对SQL语句进行优化处理,优化器会根据一系列成本计算规则来最终得出一个最优方案的SQL语句,例如自动选择小表驱动大表、max()、min()、count()等函数的优化、索引列的选择、覆盖索引优化等优化;
在我们执行SQL语句时并不是我写什么SQL语句MySQL就真正执行什么SQL语句,因为MySQL内部有优化器对其进行优化,可能最终执行的不是我们原来的SQL语句,但不管怎么优化,SQL语句最终查询的结果肯定是一致的;
优化器对SQL语句优化之后,会产生一个"优化清单",这个"优化清单"就是执行计划,执行计划中可以反映SQL语句是如何进行优化的,如:表的执行顺序、表的查询情况(子查询还是关联查询还是union等等…)、命中表分区情况、是否命中索引、使用了什么索引、扫描的行数、过滤的比例、是否全表扫描、是否覆盖索引、是否索引下推等等。
有了这份"优化清单"(执行计划),我们就可以根据执行计划反馈的信息来针对性的优化SQL语句,执行计划是我们SQL调优的一个重要参考信息;
MySQL缓存机制就是缓存SQL文本及缓存结果,用KV形式保存在服务器内存中,如果运行相同的sql,服务器直接从缓存中去获取结果,不需要在再去解析、优化、执行sql。 如果这个表修改了(有修改的数据),那么使用这个表中的所有缓存将不再有效,查询缓存值得相关条目将被清空。表中得任何改变是指表中任何数据或者是结构的改变,包括insert、update、delete、truncate、alter table、drop table或者是drop database 包括那些映射到改变了的表的使用merge表的查询,显然,对于频繁更新的表,查询缓存不合适,对于一些不变的数据且有大量相同sql查询的表,查询缓存会节省很大的性能。
缓存存在一个hash表中,通过查询SQL,查询数据库,客户端协议等作为key,在判断命中前,mysql不会解析SQL,而是使用SQL去查询缓存,SQL上的任何字符的不同,如空格,注释,都会导致缓存不命中。如果查询有不确定的数据current_date(),那么查询完成后结果者不会被缓存,包含不确定的数的是不会放置到缓存中。
1)服务器接收SQL,以SQL和一些其他条件为key查找缓存。
2)如果找到了缓存,则直接返回缓存。
3)如果没有找到缓存,则执行SQL查询,包括原来的SQL解析,优化等。
4)执行完SQL查询结果以后,将SQL查询结果放入查询缓存。
MySQL默认是将查询缓存关闭的,我们需要在配置文件中打开。
查询当前数据库缓存是否开启:
mysql> select @@query_cache_type;
+--------------------+
| @@query_cache_type |
+--------------------+
| OFF |
+--------------------+
1 row in set, 1 warning (0.00 sec)
修改配置文件:linux的是/etc/my.conf,Windows的是C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
在mysqld组下面增加:
query_cache_type=1
重启MySQL服务:
systemctl restart mysqld
再次查看:
mysql> select @@query_cache_type;
+--------------------+
| @@query_cache_type |
+--------------------+
| on |
+--------------------+
1 row in set, 1 warning (0.00 sec)
输入如下命令查询缓存相关参数
show variables like "%query_cache%";
select SQL_CACHE * from t_goods; -- 将查询结果放入缓存(前提缓存是开启的)
select SQL_NO_CACHE * from t_goods; -- 不缓存查询结果
输入如下命令查询全局缓存状态:
show global status like '%Qcache%';
create database test;
use test;
CREATE TABLE `goods` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`price` decimal(10, 2) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `test`.`goods`(`id`, `title`, `name`, `price`) VALUES (1, '华为4G全面屏游戏手机', '华为手机', 5299.00);
INSERT INTO `test`.`goods`(`id`, `title`, `name`, `price`) VALUES (2, '神舟战神游戏本', '神舟笔记本', 4599.00);
INSERT INTO `test`.`goods`(`id`, `title`, `name`, `price`) VALUES (3, '小米5G全面屏手机', '小米手机', 2899.00);
INSERT INTO `test`.`goods`(`id`, `title`, `name`, `price`) VALUES (4, '小米4G游戏全面屏拍照手机', '小米手机', 1899.00);
-- 首先执行一次查询语句
select * from goods;
-- 查看是否有写入到缓存
show global status like '%Qcache%';
发现缓存写入次数+1
我们再次执行SQL语句,查看缓存使用情况:
select * from goods;
show global status like '%Qcache%';
缓存命中次数+1
-- 写入缓存+1 select * from goods g; -- 注意仔细看SQL语句(取了个别名) -- 写入缓存+1 select * from goods where id=1; -- 写入缓存+1 select * from goods where id=2; -- 写入缓存+1 select * from goods g where id=1; -- 命中缓存+1 select * from goods g; -- 命中缓存+1 select * from goods where id=1; -- 命中缓存+1 select * from goods where id=2;
我们先计算一遍:
缓存写入数(Qcache_inserts)为1(原来的一次)+4=5
缓存命中数(Qcache_hits)为1(原来的一次)+3=4
show global status like '%Qcache%';
-- 即使查询不到结果集,也会写入缓存
select * from goods where id=10;
-- 即使查询不到结果集也会命中缓存
select * from goods where id=10;
show global status like '%Qcache%';
发现即使有的SQL没有查询到结果集,也会写入缓存,并且再次查询也会命中缓存
我们上提到过,对表的任何DML操作都会导致缓存清空,包括insert,update,delete,truncate,alter table,drop table等。
为了方便测试,我重启MySQL服务器(缓存信息全部清空):
systemctl restart mysqld
mysql> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from goods where id=1; # 写入缓存 +----+-------------------------------+--------------+---------+ | id | title | name | price | +----+-------------------------------+--------------+---------+ | 1 | 华为4G全面屏游戏手机 | 华为手机 | 5399.00 | +----+-------------------------------+--------------+---------+ 1 row in set (0.00 sec) mysql> show global status like '%Qcache%'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 1030296 | | Qcache_hits | 0 | | Qcache_inserts | 1 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 2 | | Qcache_queries_in_cache | 1 | # 当前缓存了一条SQL | Qcache_total_blocks | 4 | +-------------------------+---------+ 8 rows in set (0.00 sec) mysql> select * from goods where id=1; # 命中缓存 +----+-------------------------------+--------------+---------+ | id | title | name | price | +----+-------------------------------+--------------+---------+ | 1 | 华为4G全面屏游戏手机 | 华为手机 | 5399.00 | +----+-------------------------------+--------------+---------+ 1 row in set (0.00 sec) mysql> show global status like '%Qcache%'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 1030296 | | Qcache_hits | 1 | | Qcache_inserts | 1 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 2 | | Qcache_queries_in_cache | 1 | # 当前缓存了一条SQL | Qcache_total_blocks | 4 | +-------------------------+---------+ 8 rows in set (0.00 sec) mysql> update goods set price=8999 where id=1; # 清空缓存 Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> show global status like '%Qcache%'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 1031832 | | Qcache_hits | 1 | | Qcache_inserts | 1 | # 注意:这里显示的是你当前写入了多少次缓存 | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 2 | | Qcache_queries_in_cache | 0 | # 当前缓存了0条SQL | Qcache_total_blocks | 1 | +-------------------------+---------+ 8 rows in set (0.01 sec) mysql> select * from goods where id=1; # 写入缓存 +----+-------------------------------+--------------+---------+ | id | title | name | price | +----+-------------------------------+--------------+---------+ | 1 | 华为4G全面屏游戏手机 | 华为手机 | 8999.00 | +----+-------------------------------+--------------+---------+ 1 row in set (0.00 sec) mysql> show global status like '%Qcache%'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 1030296 | | Qcache_hits | 1 | | Qcache_inserts | 2 | # 发现缓存写入次数增加 | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 2 | | Qcache_queries_in_cache | 1 | # 当前缓存了一条SQL | Qcache_total_blocks | 4 | +-------------------------+---------+ 8 rows in set (0.00 sec) mysql>
通过观察Qcache_queries_in_cache参数可以发现,执行完update语句之后,有关于这张表的缓存全部清空,当再次执行SQL语句的时候,会重新写入缓存。
对MySQL表的任意DML操作都会导致有关于这张表的所有缓存全部清空。
我们知道MySQL的查询缓存一旦开启,会将本次SQL语句的结果集全部放入缓存中,这样其实是非常不友好的,因为我们知道,对于表的任何DML操作都会导致这张表的缓存全部清空。因此我们可以指定哪些SQL语句存入缓存,哪些不存。
执行如下SQL语句,分析缓存执行情况:
-- 缓存写入次数0
show global status like '%Qcache%';
-- 存入缓存
select * from goods;
-- 缓存写入次数1
show global status like '%Qcache%';
-- 不存入缓存
select SQL_NO_CACHE * from goods g; -- 注意:取了个别名
-- 缓存写入次数还是1
show global status like '%Qcache%';
我们已经明白缓存何时写入、何时清空,何时命中,接下来我们插入300W数据,来执行SQL语句,体验缓存给我们带来性能上的提升。
CREATE TABLE `userinfo` (
`id` int(10) NOT NULL COMMENT '用户id',
`username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名',
`age` int(3) NULL DEFAULT NULL COMMENT '年龄',
`phone` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '手机号',
`gender` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别: ‘0’-男 ‘1’-女',
`desc` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '自我介绍',
`register_time` datetime(0) NULL DEFAULT NULL COMMENT '注册时间',
`login_time` datetime(0) NULL DEFAULT NULL COMMENT '上一次登录时间',
`pic` varchar(250) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '头像地址',
`look` int(10) NULL DEFAULT NULL COMMENT '查看数',
PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
create procedure test_insert(count int) begin declare i int default 1; while i<=count do INSERT INTO userinfo values( i, -- id uuid(), -- username CEILING(RAND()*90+10), -- age FLOOR(RAND()*100000000000), -- phone round(FORMAT(rand(),1)), -- gender uuid(), -- desc now(), -- register_time now(), -- login_time uuid(), -- pic CEILING(RAND()*90+10) -- look ); set i=i+1; end while; end;
-- 关闭唯一性校验,提高批量插入速度
set unique_checks=0;
-- 控制在一个事务,避免频繁开启/提交事务
start transaction;
call test_insert(3000000); -- 模拟300W的数据
commit;
-- 第一次查询,将结果存入缓存(2.18)
select * from userinfo where username='4fa62d59-a19b-11ea-967c-000c29a7676a';
-- 走缓存,效率快(0.00s)
select * from userinfo where username='4fa62d59-a19b-11ea-967c-000c29a7676a';
-- 对表进行修改,关于这张表的缓存全部清空
update userinfo set username='1' where id=1;
-- 再次查询,发现效率低,但又存入缓存了(2.51s)
select * from userinfo where username='4fa62d59-a19b-11ea-967c-000c29a7676a';
-- 再次查询,走缓存,效率高(0.00s)
select * from userinfo where username='4fa62d59-a19b-11ea-967c-000c29a7676a';
再进行多次测试:
-- 写入缓存(2.45s) select * from userinfo where age=1; -- 命中缓存(0.00s) select * from userinfo where age=1; -- 写入缓存(2.25s) select * from userinfo where phone='1'; -- 命中缓存(0.00s) select * from userinfo where phone='1'; -- 命中缓存(0.00s) select * from userinfo where phone='1'; -- 写入缓存(1.99s) select * from userinfo where look=1; -- 命中缓存(0.00s) select * from userinfo where look=1; -- 命中缓存(0.00s) select * from userinfo where look=1;
发现命中缓存确实比没有命中缓存查询效率高多了。
场景举例:
select * from xxx age > 18 and age <25;
-- 在大量数据中,筛选出几条数据(这样的SQL语句最适合存入查询缓存)
select * from xxx hobby in (1,2,3,4);
select * from xxx limit 1000000,100;
-- 在大量数据中,做分组操作(这样的SQL语句最适合存入查询缓存)
select avg(age) from xxx group dept_id;
尽量缓存结果集不会太大的SQL语句(建立缓存时间短,就算缓存失效对我影响也不大),但是表中数据量大(表中数据量大,意味着查询速度慢),这样的SQL语句下次查询缓存速度上就能得到明显的提升。
什么情况下不适合使用查询缓存?
场景举例:
-- 整表缓存
select SQL_NO_CACHE * from xxx;
-- 多表缓存
select SQL_NO_CACHE * from xxx x1 inner join xxx x2 on x1.x=x2.x;
-- 大范围缓存
select SQL_NO_CACHE * from xxx where age>18;
缓存命中率 = Qcache_hits(缓存命中次数) / Com_select(查询次数,命中缓存时这个参数不会加)
缓存写入率 = Qcache_inserts(缓存写入次数) / Com_select(查询次数)
示例:
show status like 'com_select'; -- 显示当前会话的查询次数
show status like '%Qcache%'; -- 显示当前会话缓存使用情况
命中率:9 / 20 = 0.27 = 45%
写入率:13 / 20 = 0.18 = 65%
如果当前的MySQL缓存了过多的SQL语句没有及时清除,我们可以使用命令将缓存清空,来是否缓存:
reset query cache;
我们在分析缓存参的时候知道,MySQL并不是一下子分配query_cache_size内存作为缓存,而是将内存分为若干个query_cache_min_res_unit小内存,即使本次缓存没有达到一个缓存块大小也需要占用一个缓存块大小。但query_cache_min_res_unit也不能设置太小,设置太小会造成MySQL不断的分配很多个内存块来缓存本次SQL语句的结果集。
MySQL官网上已经说明,在MySQL8.0及以上版本,MySQL的缓存功能已经被删除了
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。