Mysql调优

作者：llybjd@163.com

https://gitee.com/seven_hundred_and_eightysix/sharding-jdbc.git

一、mysql安装及配置

1. 下载linux版本的mysql

下载安装包：链接：https://pan.baidu.com/s/1gbK6BuTdObhnUeP_k9UQMg
提取码：wkdv

2. 安装mysql

  • 卸载mariadb: yum -y remove mariadb-libs

    说明：MariaDB数据库是MySQL的一个分支，主要由开源社区在维护，centos使用这个分支的原因是：甲骨文公司收购了MySQL后，有将MySQL闭源的可能，采用分支的方式来避开法律纠纷（在centos纯净版本上安装，如果安装了docker,mysql服务可能启动有问题）

  • 解压

    • 创建/opt/software/mysql和/opt/module/mysql两个目录:
      • mkdir -p /opt/software/mysql
      • mkdir -p /opt/module/mysql
    • 上传安装文件到software的mysql中：alt+p,cd /opt/software/mysql
    • tar -zxvf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar -C /opt/module/mysql

  • 安装：

    • cd /opt/module/mysql
    • rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm
    • rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
    • rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm
    • rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm

  • 查看安装的版本

    mysql -V

3. 基本配置

3.1 启动服务

  • 启动 service mysqld start
  • 重启 service mysqld restart

3.2 修改密码

  • 查看临时密码 vim /var/log/mysqld.log ,搜索temporary password找到临时密码，临时密码每次启动都会变更

  • 用临时密码登录：mysql -uroot -p回车--->输入上面的临时密码,注意，密码是不显示的

  • 设置密码 set password=password("Llybjd999&");---Llybjd999&

  • 查看密码策略： show variables like 'validate_pass%';

  • 如果要设置简章密码，如123，则要侯密码强度为低

    • set global validate_password_policy=low; ---安全策略
    • set global validate_password_mixed_case_count=0; ---1为必须强制大小写，0是关闭这种限制
    • set global validate_password_number_count=0; ---是否必须包含数字，0为关闭。
    • set global validate_password_special_char_count=0; ---是否必须包含特殊字符,如&，0为关闭
    • set global validate_password_length=1; ---最小长度,这里不能为0，否则默认会改为4

  • 修改为简单密码： set password=password("123");

3.3 修改数据库默认编码

  • vim /etc/my.cnf
  • 最后一行添加: character_set_server=utf8
  • service mysqld restart

3.4 远程连接

  • 修改为允许远程访问

    • 进入mysql

    • use mysql

    • 因为默认只允许localhost登录,修改为允许任何人使用root访问：

      update user set host='%' where user='root';

    • 刷新修改： flush privileges;

    • 关闭防火墙：

      • 查看状态: systemctl status firewalld.service ,出现active说明是开启的。

      • 临时关闭: systemctl stop firewalld.service

      • 禁用防火墙: systemctl disable firewalld.service

        这样下次开机时就不会再开防火墙了

  • 测试： mysql -uroot -p123 -h192.168.188.107

4. mysql逻辑架构

  • 连接层：用户名及密码认证、及授权。

  • 服务层：DML/DDL/事务/优化器及缓存等业务处理

  • 引擎层：数据的存储及读取方案。

  • 存储层：将数据存储在文件系统上,数据的存储路径为/var/lib/mysql/数据库名

二、数据库调优

优化总体原则：不可过度的优化，如何优化通常是要根据真实生产的数据执行情况来分析

1. 慢查询语句

a. 查询日志是否开启

show variables like '%slow_query_log%';

b. 开启慢查询的日志

set global slow_query_log=1; 开启开关,就会自动记录查询慢的sql语句

c. 如何定义慢查询语句

  • show variables like '%long_query_time%'; 查询慢sql的阀值，默认10秒

  • set long_query_time=5; 局部修改阀值，只在本连接有效，关闭就失效。

  • set global long_query_time=5; 全局修改

d. 查看慢查询语句

  • select sleep(6);
  • show global status like '%Slow_queries%'; 查看当前有几条sql语句是慢的
  • vim /var/lib/mysql/192-slow.log 查看慢查询日志文件，如果没有指定文件，文件会自动创建。

2. 存储引擎

a. 引擎是什么？

引擎是数据库底层组件，数据库系统使用引擎进行增删改查。不同的存储引擎提供不同的存储机制、索引技巧，还可以获得特定的功能。

b. Myisam与InnoDb区别：重点

  • 查看支持哪些引擎
    • show engines; 表格方式查看
    • show engines \G 注意没有分号";" ，每一行都单独显示
  • 区别
功能 Myisam Innodb
外键 No Yes
事务 No Yes
锁 仅支持表锁 支持行锁表锁
缓存 只对索引缓存 对索引和数据都会缓存
性能 强调读写效率高 强调数据安全
主键索引 非聚集索引 聚集索引
数据文件结构 每张表有三个文件,frm文件描述该表的元数据，myd存储表的具体数据，myi存储该表的索引 每张表有两个文件，frm也是表的元数据，ibd存储数据和索引数据

3. 索引

3.1 简介

a. 概念

索引是基于数据库表创建的，它包含一个表中某些列的值以及记录对应的数值(具体是什么数值，要看引擎和索引的种类).

b. 作用

在存储数据时会把数据组织成某种数据结构(通常是B+树，也可是hash结构，这种结构不支持范围查找，所以很少用)，查询时可以利用该数据结构的特性提高查询速度。

c.索引的种类

  • 主键索引：

    由主键约束所产生的索引.

  • 唯一索引：由唯一约束所产生的索引。unique

  • 聚集索引（innodb）：聚集索引通常就是主键索引，如果没有定义主键索引，但有唯一索引且非空的一个字段或字段集（多字段唯一），则使用该唯一索引作为聚集索引，如果没有这样的唯一索引，则会自动产生一个隐藏的索引为gen_clust_index

  • 单值索引：用create index 索引名字 on 表名（字段）。

  • 复合索引：基于某些列建立索引。create index 索引名 on 表名(字段1，字段2);

  • 全文索引：类似于solr，把一篇篇文章存储在某一列，搜索时使用全文索引。很少用，>=5.6版本的mysql才支持。

  • 哈希索引：采用哈希算法，存储引擎为memery才使用，把索引列数据换算成哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快，但不适合范围查找和排序。

image

3.2 索引维护

a. 创建索引

  • 建表时创建

    create table a(
id int auto_increment,
name char(32),
sal double,
age int,
phone char(32),
primary key(id)
);

    索引名PRIMARY，主键和唯一约束默认会自动创建索引。

  • 建表后创建

    • 单值索引：create index age_INDX on a(age);
    • 复合索引：create index name_sal_age_INDX on a(name,sal,age);

b. 查看索引

show index from a;

c. 删除索引

drop index 索引名 on a;
不能删除主键索引

d. 修改索引

MySQL中并没有提供修改索引的直接指令,但可以通过删除原索引，再根据需要创建一个同名的索引，从而实现修改索引

3.3 索引的查询原理

a. B+树是什么？

索引的数据结构就是一个B+Tree, 是一种自动排序的平衡树，节点默认大小16k,也就是说一个节点上存放的数据量非常大，数据库加载时，一般都会把非叶子节点全加载到内存中，所有数据都放在叶子节点上。

算法研究参考下面网址

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

b. 索引的查询原理

  • innodb引擎：

    • 主键索引：每个叶子节点包含某一行所有列（注意是所有）数据和数据所在行的主键值(也叫索引值)，上层的分枝节点上只包含部分主键值；这种主键索引中，把数据和索引合在一起的存储方式的叫聚集索引，整个表就是一个主键索引,也就是说表在磁盘上的存储结构就是树状结构。myisam把数据和索引分开存储，所以其主键索引不是聚集索引。

    • 非主键索引:每个叶子节点包含的是该行的主键值和这一索引所在列的数据库数据，上层分枝节点上只包含部分数据。

    • 如何查找？

      在查找时，如果不是用主键索引查找，则会先从这个普通索引上查找到数据所在行的主键值，再回表。何为回表？就是拿到主键值后，再用主键索引重新获取数据。

  • myisam引擎：

    • 主键索引：

      每个叶子节点包含的是主键值和该行所在的物理地址。而非叶子节点包含的仅是主键值。

    • 非主键索引：跟innodb相同

    • 如何查找？

      跟innodb不同的是回表时获取的不是数据，是数据行所在地址。

  • 为什么推荐使用自动增长主键？

    因为在插入数据时，主键值是持续增长，在插入数据时，不需要修改b+树中已经排好序的节点，效率高。

3.4 性能监控工具

a. 执行计划

  • 是什么

    执行计划explain:可以模拟优化器执行SQL查询语句，从而让开发人员知道sql执行的信息,分为了哪几步，有没有用到索引，是否有一些可优化的地方等.

  • 示例

    explain select id from a where age=100

  • 执行计划字段说明：

  • type

    • const：表示通过索引一次就找到了，用于比较primary key 或者 unique索引。因为只需匹配一行数据，很快。

      示例：explain select name from a where id=1;

    • ref：非唯一性索引扫描，返回匹配某个值的所有行，可能会找到多个符合条件的行

      示例：explain select id from a where age=1;

    • range：只检索给定范围的行，一般就是在where语句中出现了bettween、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点，结束于另一个点，不用扫描全部索引

      示例：explain select * from a where age between 1 and 30;

    • index：从整个索引中读取数据，不需要回表。

      示例： explain select id from a;

    • ALL：Full Table Scan，遍历全表以找到匹配的行，读磁盘，而index则是读索引。

      表示本次查询，优化器选择不使用索引，选择的是全表扫描，认为全表扫描效率高.

      示例：delete from a;--->insert into a (age) values(23),(12),(34);－－＞create index age_index on a(age);－－＞c－－＞发现type 为all

  • possible_keys：分析器根据查询语句，认为可能会命中的索引，但不一定会成功命中，因为会有多种原因导致索引失效。

  • key:查询中实际命中的索引

  • len:使用索引的长度，int型索引的列长度为4，char长度：该字段长度*3，如32*3，double为8，但如果一列是可以为null的，则索引的长度加1.

  • rows:优化器预估要扫描的行数。

  • extra:

    • Using filesort表示优化器无法利用索引完成排序操作，而采用cpu进行比较排序。

      示例：explain select * from a where age=1 order by name;

    • Using index:索引覆盖

    • Using index condition条件下推(后面讲)

    • Using where：使用where进行过滤

b. profiling的使用

  • 作用

用于分析一条sql 的执行时间是多少。

  • 开启和关闭 profiling

    • 开启 set profiling=1;
    • 关闭 set profiling=0;

    在开启 Query Profiler 功能之后，MySQL 就会自动统计所有sql语句 的执行时间信息。

  • 执行任意一些sql语句

  • 查询在此期间的所有sql语句 show profiles;

3.5 索引优化

1. 索引的最左原则

  • 最左原则是什么

    是对于组合索引来讲的，如果基于name、sal、age三列建立的复合索引create index name_sal_age on a(name,sal,age);相当于产生了三个索引:

    • name
    • name,sal
    • name,sal,age.

  • 为什么？

    因为索引的内容为name sal age三个字段，值为这一行的主键值。排序是按照name sal age的优先级依次比较，得出一个顺序放在b+树中。

  • 执行计划

explain select * from a where name ='a1';---命中
explain select * from a where name ='a1' and sal=12;---命中
explain select * from a where name ='a1' and sal=12 and age=10;---命中
explain select * from a where sal=12 and age=10;---失败

2. 索引覆盖

在命中索引后，无需回表就能获取数据，这种查询叫索引覆盖。

explain select id from a where name='hello';和explain select sal from a where name='hello'要获取的数据是id,而在name索引中就可以直接得到id值，不需要回表到主键索引中得到数据。这种行为叫索引覆盖。。在执行计划上看到 Using index，说明使用了索引覆盖。

3. 索引(条件)下推

explain select * from a where name='hello' and sal>10 and age=1; 在有建立组合索引的前提下，这条语句的执行有下面两个方案：

  • 从复合索引中获取name为hello且sal>10的所有主键值--->回表查出对应的数据－－＞从这些数据中过滤age=1的记录。－－－这种情况要回表的数据量会比较多。

  • 直接从复合索引name=hello和sal>10中过滤age=1的主键值，再回表得到数据。－－－这种方案就是索引下推，mysql默认启用索引下推。可通过下面命令来查看和关闭：

select @@optimizer_switch;
SET optimizer_switch = 'index_condition_pushdown=off'; ---建议不要关闭

存储函数和触发器无法使用索引下推。

4. 前缀索引

基于某列的前面几个字符建立索引，用来减少索引的存储空间，理论上说，尽量每个索引值都不相同，此时效率最高，所以选多少位要看具体数据。
缺点是ORDER BY和GROUP BY无法命中索引。
create index p_i on a(phone(2));
explain select * from a where phone='1';

3.6 索引在什么情况下会失效？--重点

explain select * from a where name='hello';----命中

explain select * from a where trim(name)='hello';----没命中，索引列有函数处理或隐式转换，不走索引
select * from a where name = 100;---没命中，因为字符串如果不用''必失效。因为优化器会给name字段进行隐式转换，从而导致失效。
explain select * from a where name='hello' and sal <10;----命中

explain select * from a where sal <10 and name='hello';----命中，跟where中的顺序无关.

explain select * from a where sal <10 and age=20;----没命中复合索引。

explain select * from a where name='hello' and sal <10 and age=20;----命中

explain select * from a where name='hello' or sal <10;----没命中索引，or导致索引失效，可使用union all来使用索引。
explain select * from a where name='hello' union all select * from a where sal <10;
但sal如果没有索引，sal字段也不命中。

explain select * from a where name='hello' and sal <10 or age=20;----没命中，因为or会导致索引失效，但
explain select name,sal,age from a where name='hello' and sal <10 or age=20;就能命中，因为这里走的是索引覆盖。

explain select * from a where name='hello' and sal <10 and age=20;----命中，但只命中(name,sal)两个字段，因为范围查询会导致右侧(age)失效。

explain select * from a where name > 'a1';---没命中，<>原因.

explain select * from a where name like '%a';----'%'和'_'开头会导致索引失效，但并不是说模糊查询就必失效，比如'a%'就不会失效。字符串比较大小的规则跟java一样。

explain select * from a order by name,sal,age;---没使用索引，是全表扫描，因为优化器认为这样的查询和排序还要多一次回表，性能不高。
explain select sal from a order by name,sal,age;---使用name　sal和age索引，因为要的数据就在索引数据中，不需要回表，使用索引性能好。

explain select * from a where name= 'a1' order by age;---命中,使用name
explain select * from a where name in('a1','a2'); 无法命中，因为in会导致索引失效

  • 在过滤字段使用函数：explain select * from a where trim(name)='a1';不会命中

  • in会失效：里面如果只有一个值name in('a1')，就会优化为name='a1',但name in（'a1','a2')就失效。解决办法：

    1. 索引覆盖
    2. union all

  • 范围查询（>,<）会导致右侧(age)失效，如：

    explain select * from a where name ='a1' and sal>1 and age=10; ----age失败，使用name和sal

  • <>和!=会导致失效，解决办法

    >=xxx union all <=

  • or可能会失效，解决办法：

    1. 索引覆盖：如果是索引覆盖就能命中。
    2. union all也能命中

  • like通配符放左边会失效。

    1. 如查询%com会失效，可在存储数据hy.com时用com.hy，则查询时用like com%查出来，得到数据后再用字符串处理把其还原成hy.com,

    2. 索引覆盖

      select后面的字段被索引覆盖，explain select name from a where name like '%a1';---命中

  • 对字段加隐式转换会失效。

3.7 什么情况下创建索引？--重点

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

  • 主键和唯一约束会自动建立索引

  • 查询中与其它表关联的字段和外键,且用来作为被驱动的表，则建立索引

    • select dept_name from emp e,dept d where d.id=e.deptid;

  • 经常用来作为排序或分组的字段

  • 保证被驱动表(辅助表)的条件字段建立索引

  • 外连接时，选择小表为驱动表（因为驱动表中所有行都会被遍历，索引也用不上）,用大表作被驱动表

  • 尽量不用子查询，如果用，子查询尽量不要作被驱动表（子查询不能建索引）
    select * from (select name,age from a)tmp;

     create table t_emp(
 id int,
 name char(32),
 dept_id int
 );
 create table dept(id int,dname char(32));
 select e.name,d.name from t_emp e,dept d where e.dept_id=d.id;----此处，e为驱动表
 select e.name,d.name from t_emp e,dept d where d.id=e.dept_id;

3.8 什么情况不创建索引--重点

  • 数据量少

  • 经常增删改

  • where条件中用不到的字段

  • 过滤性不好的字段，如性别字段

4. 查询优化

1. 减少查询结果的数据量

客户端与服务器间通信是半双工，也就是同一时刻，要么客户端发送数据给服务端，要么服务端给客户端响应数据，不能同一时刻双向传送数据，所以要尽量控制响应的数据量。

  • 避免使用select *

  • 尽量使用limit

2.limit优化

limit a,b:a值越大查询就越慢

  1. 原因

    随着页数的增加查询速度会越慢，为什么？因为每次查询都会查询出a+b条，并过滤掉前a条，b不变，但a会随着页数的增加而快速增加，从而导致所要查询的条数越来越多。

  2. 解决办法--重点

    1. 所要查询的字段，要被所索引覆盖

    2. 覆盖索引+ 连接查询:如果要查询的字段很多，则无法使用索引覆盖

      select *
from employees e,
(select emp_no from employees limit 300000,10) t
on e.emp_no = t.emp_no

    3. 范围查询 + limit:如果可以得到上一页返回数据主键的最大值

      select *
from employees
where emp_no > 10010
limit 10;

2. NOT NULL

索引会对NULL列产生额外的空间来保存，要占用更多的空间,所以尽可能把所有列定义为NOT NULL,

3. 减少表的索引数

限制每张表上的索引数量，建议单张表索引不超过5个。为什么？

  • 索引可以增加查询效率，但同样也会降低增删改的效率。

  • mysql优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加mysql优化器生成执行计划的时间，同样会降低查询性能。

4. 数据库表中数据量过大，如何提高查询效率？

a)保证被驱动表(辅助表)的条件字段建立索引
b)外连接时，选择小表为驱动表（因为驱动表中所有行都会被遍历，索引也用不上）,用大表作被驱动表
c)尽量不用子查询，如果用，子查询尽量不要作被驱动表（子查询不能建索引）

三、分库分表

1 数据库表设计

a 三范式

范式1:数据库中每一行的每一列只能有一个值。
范式2:表中每一行数据要可以被唯一的区分，通常是通过主键满足。
范式3：一个表中不能包含另一张表中的信息，通常是通过外键关联满足这一范式。

b 数据类型优化

  • 尽量使用小的数据类型--－节省存储空间。（整型：tinyint(8位),smallint(16位),mediumint(24位),int(32位),bigint(64位)）

  • 如果可以的话，尽量把列定义为不可为空，原因：可null的例，字段长度会多一位，用来记录本行数据是否为null,从而导致产生索引和比较索引都变的复杂。对于not null的字符串列，如果必须给空值，则可给一个空字符串。

  • 合理使用varchar类型

    • 特点：会根据实际长度保存数据，相对char类型节省空间，但会使用一到两个字节（长度小于等于255时用一个字节，大于255时用两字节）保存数据的实际字符数。
    • 应用场景：a.数据长度波动较大时. b.数据很少更新时。因为每次更新后都会重新统计数据的字符个数并保存这个len值。

2. 多数据库主从复制

1. 什么是主从复制

将主数据库中的DDL和DML操作通过二进制日志传输到从数据库上，然后将这些日志重新执行（重做）；从而使得从数据库的数据与主数据库保持一致。

2. 原理

当一个从服务器连接主服务器时，它通知主机：从机在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，并在本机上执行相同的更新。然后读取线程休眠并等待主服务器通知新的更新。

image

3. 主从复制作用

  • 主数据库出现问题，可以切换到从数据库
  • 可以进行数据库层面的读写分离
  • 可在从数据库上进行日常备份
修改配置文件
1.修改主机（windows）配置文件my.ini,文件尾添加
log-bin=mysql-bin   //[必须]启用二进制日志
server-id=1      //[必须]服务器唯一ID

2.修改从机（linux）/etc/my.cnf,文件尾添加
log-bin=mysql-bin
server-id=2
read_only=1   #设置为只读，但必须是普通帐户登录才只读，如果是root，依然可读写


重启动两个mysql
1.windows
net stop mysql  ---右键cmd管理员权限
net start mysql
2.linux
service mysqld restart




主机上登录mysql,执行下面两条命令。
grant replication slave on *.* to 'a1'@'%' identified by '123' WITH GRANT OPTION;---产生用户名及密码，供从机使用，%表示从机的IP地址任意。
show master status;----查看主机日志文件名及日志文件的位置。
此后不要在主机上执行任何sql操作，免得日志文件位置发生变化。


以root帐户登录从机mysql,执行下面命令：
change master to master_host='192.168.1.161',master_user='a1',master_password='123',
master_log_file='mysql-bin.000001',master_log_pos=1603;
说明：ip地址为主机的ip,abc和123为主机授权的帐号及密码，bin.000001为主机上的日志文件名，1603为主机上的日志position值。
从机执行下面命令，启动从服务器复制功能
start slave;

从机执行下面命令检查从服务器复制功能状态：
show slave status\G
结果中出现Slave_IO_Running: Yes和Slave_SQL_Running: Yes表示从机正常运行。
quit--->以a1,123帐户重新登录从机，以实现只读功能

主从测试
1.主机创建数据库,创建表，添加数据
2.从机查询数据库，查询表，查询表中数据。

面试题：

1如何实现 MySQL 的读写分离？

只在主机写，从机只用来读

2.主从同步延时长问题如何解决？

分库，将一个主库拆分为多个主库，每个主库的写并发就减少了几倍，此时主从延迟可以忽略不计。

3.分库分表

1.简介

a. 分表

  1. 垂直拆分

    • 如何拆

      表中属性不能过多，拆成多张表，表之间用外键关联

    • 优点：

      高并发:因为如果curd分流在不同的表，一张表的行锁和表锁不会影响其它表的操作

  • 水平拆分

    一个库中创建多张表结构相同的表，但内容不同，操作时根据id或某字段进行hash来决定操作哪些表

b. 分库

  1. 垂直拆分

    根据业务划分，多张表分部在多个数据库中，某个数据库专注于处理某个业务，事务时使用分布式事务

  2. 水平分库

    创建多个结构(注意:是结构，不是内容)相同的数据库，比如id为偶数添加到0号数据库，奇数则添加到1号数据库，采用id跟数据库数量取余数来决定本次操作要到哪个数据库读写。

c.应用场景

在生产环境中，优先考虑使用缓存和索引进行优化，如果这些依然难以满足性能提升的需求，此时才考虑分库分表

d.缺点

  • 多库多表的联查问题，如果一个查询涉及多库，则要分多次数据库查询
  • 分页和排序困难
  • 多数据源的事务问题

e. 总结：

  • 垂直：拆分结构，针对结构专库专表
  • 水平：拆分数量，针对数量

2. shardin-jdbc

a.简介

  • 是什么?

    轻量级的数据库中间件框架,相对于mycat来说，它不需要额外部署服务，引入其jar包就能使用

  • 有什么用？

    简化对分库分表后的相关操作

  • 官网:

    shardingsphere.apache.org   点了解更多-->查看用户手册
posted @ 2021-05-15 11:37  白河散仙  阅读(144)  评论(0)    收藏  举报