[系统运维] mysql高级第一弹

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 系统运维 -> mysql高级第一弹 -> 正文阅读

[系统运维]mysql高级第一弹

一、安装

检查系统是否安装mysql(rpm 检查)

rpm -qa | grep -i mysql
安装过会显示软件名，没装过为空

带进度条安装

rpm -ivh 软件名.rpm

查看服务信息

ps -ef | grep mysql

查看linux的用户组

cat /etc/passwd | grep mysql
cat /etc/group | grep mysql

查看mysql版本号

mysqladmin --version

启动mysql关闭

service mysql start/stop

修改mysql的密码

/usr/bin/mysqladmin -u root password 123456

设置开机自启动mysql

chkconfig mysql on

查看开机自启动的服务

chkconfig --list | grep mysql

修改配置文件

拷贝配置文件

5.5版本
cp

修改字符集

show variables like 'character%'
show variables like '%char%'

主要配置文件

windows下配置文件 my.ini
Linux下配置文件 /etc/my.cnf


log-bin = D:/devsift/mysql/data/mysqlbin
log-error  默认关闭，记录严重的警告错误信息，每次启动关闭和启动的详细信息

数据文件

frm文件 存放表结构
myd文件 存放表数据
myi文件 存放的表索引

二、mysql的架构索引

1.分层

连接层，服务层，引擎层，存储层-

连接层——>分析器——>优化器——>执行器——>存储层

一、安装

检查系统是否安装mysql(rpm 检查)

rpm -qa | grep -i mysql
安装过会显示软件名，没装过为空

带进度条安装

rpm -ivh 软件名.rpm

查看服务信息

ps -ef | grep mysql

查看linux的用户组

cat /etc/passwd | grep mysql
cat /etc/group | grep mysql

查看mysql版本号

mysqladmin --version

启动mysql关闭

service mysql start/stop

修改mysql的密码

/usr/bin/mysqladmin -u root password 123456

设置开机自启动mysql

chkconfig mysql on

查看开机自启动的服务

chkconfig --list | grep mysql

修改配置文件

拷贝配置文件

5.5版本
cp

修改字符集

show variables like 'character%'
show variables like '%char%'

主要配置文件

windows下配置文件 my.ini
Linux下配置文件 /etc/my.cnf


log-bin = D:/devsift/mysql/data/mysqlbin
log-error  默认关闭，记录严重的警告错误信息，每次启动关闭和启动的详细信息

数据文件

frm文件 存放表结构
myd文件 存放表数据
myi文件 存放的表索引

二、mysql的架构索引

1.分层

连接层，服务层，引擎层，存储层-

连接层——>分析器——>优化器——>执行器——>存储层

在这里插入图片描述

2.引擎的介绍

查看mysql已经提供那些引擎
show engines;

查看当前myql默认引擎
show variables like '%storage_engine%';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tF97OGOe-1639059145784)(DB709D19F8FA4CC4933979A68E48BDA7)]

阿里mysql使用 percona的原型加以修改

Percona为MySQL数据库服务器进行了改进，在功能和性能上较MySQL有着很显著的提升。该版本提升了在高负载情况下的InnoDB的性能、为DBA提供一些非常有用的性能诊断工具;另外有更多的参数和命令来控制服务器行为。

3.索引优化

性能下降的原因：

1.查询语句写的烂

2.索引失效（单值索引，复合索引）

建立索引单值索引
在user表中name字段建立索引名字为idx_user_name
create index idx_user_name on user(name)

建立复合索引
create index idx_user_name on user(name, email)

3.关联查询join（设计缺陷或不得已的需求）

4.服务器调优以及各个参数的设置（缓冲，线程数等）

4.SQL的执行顺序

机读先读 from——>on——>join——>group by——>having ——>select——order by——>limit

在这里插入图片描述

5.索引

MySQL官方对索引的定义为:索引(Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质:索引是数据结构。

有的索引本身很大，不可能存在内存上，因此存在磁盘上

索引的缺点：1.索引占用空间 2.索引大大提高查询速度，但是会降低表的更新速度。每次更新都要更新索引字段 3.如果有大量数据，需要花费大量时间研究建立最优秀的索引，或优化查询语句

6.索引的分类

一张表的索引最好不超过五个

1.单值索引一个索引包含单个列，一个表可以有多个单列索引
2.唯一索引索引列的值必须唯一，但允许有空值
3.复合索引一个索引包含多个列
4.基本语法
5.更改索引

7.那些情况需要创建索引

1.主键自动建立唯一索引
2.频繁作为查询条件的字段应该建立索引
3.查询中与其他表关联的字段，外键关系应该建立索引
4.频繁更新的字段不适合建立索引
5.where条件里用不到的字段不创建索引
6.单值/组合索引的选择问题。（高并发情况下倾向创建组合索引）
7.查询排序的字段。排序字段若通过索引去访问将大大提高排序速度
8.查询中统计或者分组的字段

8.Mysql的常见瓶颈

1.CPU在饱和的时候一般发生在数据装入内存或从磁盘读取数据的时候
2.IO：磁盘IO瓶颈发生在装入数据远大于内存容量的时候
3.服务器硬件的性能瓶颈，Top,free,iostat和vmstat来查看系统的性能状态

三、Explain

1.EXplain + SQL语句

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bcVZgUJr-1639059145790)(CF33C662A42D438191EEC5502EA0C628)]

2.字段分析

1.id id如果相同，可以认为是一组，从上往下顺序执行;在所有组中，id值越大，优先级越高，越先执行
2.select_type

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GCA75QXd-1639059145791)(137CB4710DFB448A976D72705E03D57F)]

SIMPLE - 简单的select查询，

PRIMARY:最外层查询

SUBQUERY:子查询

DERIVED:在FROM列表中包含的子查询被标记为DERIVED(衍生)MySQL会递归执行这些子查询，把结果放在临时表里。

UNION:若第二个SELECT出现在UNION之后，则被标记为UNION;
若UNION包含在FROM子句的子查询中,外层SELECT将被标记为: DEVED

3.table 显示关于那个表的数据
4.Type

类型

system-const-eq_ref-ref-range-index-ALL

system: 表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计

const:表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快如将主键置于where列表中，MySQL就能将该查询转换为一个常量

eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

ref:非唯一性索引扫描，返回匹配某个单独值的所有行.本质上也是一种索引访问，它返回所有匹配某下单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体

range:只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

index:Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。(也就是说虽然all和Index都是读全表，但index是从嗦引中读取的，而all是从硬盘中读的

ALL：全表从磁盘扫描

5.possible_keys
显示可能应用在这张表中的索引，一个或者多个。
查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
6.key
实际使用的索引。如果为NULL，则没建或没有使用索引，即索引失效。
查询中如果使用了覆盖索引，则该索引仅仅出现在key列表中。与Extra有关
7.key_len 一样的查询结果情况下查询条件限制越多越好
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。
key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。在不损失精度的情况下，长度越短越好。
8.ref:显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
9.rows:根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
10.Extra: 包含不适合在其他列中显示但十分重要的额外信息。

● Using filesort （效率差）
说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
MySQL中无法利用索引完成的排序操作称为"文件内排序"。

create table user (
    id     integer primary key auto_increment,
    name   varchar(20) not null,
    age    integer     not null,
    gender tinyint     not null
);
create index user_name_gender on user(name, gender);

# 排序没有使用索引
explain select * from user where name ='zhangsan1' order by id \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ref
possible_keys: user_name_gender
          key: user_name_gender
      key_len: 62
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index; Using filesort
1 row in set, 1 warning (0.00 sec)

#~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
# 排序使用到了索引
explain select * from user where name ='zhangsan1' order by gender \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ref
possible_keys: user_name_gender
          key: user_name_gender
      key_len: 62
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

● Using temporary （效率非常差）
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wkQErd8S-1639059145792)(59626ACEA2BB4CBF965AF4442F629F49)]

● Using index (效率很高)
表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行,效率不错!
如果同时出现using where，表明索引被用来执行索引键值的查找;
如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

● Using Where 表明使用了where过滤

● Using join buffer 使用了连接缓存

● impossible where Where子句的值总是false，不能用来获取任何元组

● select tables optimized away

eg分析

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sMTQaRdD-1639059145792)(51295C12C1CF4665AFAF92192D7D8942)]

两表索引优化

左连接索引建立右表
右连接索引建立左表

三表索引优化

JOIN语句的优化：

● 尽可能减少JOIN语句中的NestedLoop（嵌套循环）的总次数：永远都是小的结果集驱动大的结果集。

● 优先优化NestedLoop的内层循环。

● 保证JOIN语句中被驱动表上JOIN条件字段已经被索引。

● 当无法保证被驱动表的JOIN条件字段被索引且内存资源充足的前提下，不要太吝惜Join Buffer 的设置。