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%';

阿里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语句

2.字段分析

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

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 （效率非常差）

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

● Using Where 表明使用了where过滤

● Using join buffer 使用了连接缓存

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

● select tables optimized away

eg分析

两表索引优化

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

三表索引优化

JOIN语句的优化：

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

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

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

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

2.引擎的介绍

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

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UTAlBNB2-1639059053135)(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

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

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

5.索引

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

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

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

6.索引的分类

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

• 1.单值索引 一个索引包含单个列，一个表可以有多个单列索引
• 2.唯一索引 索引列的值必须唯一，但允许有空值
• 3.复合索引 一个索引包含多个列
• 4.基本语法
  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0azzEhUa-1639059053142)(2E15563D8F6D406387056B68D825B551)]
• 5.更改索引
  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-x30Aj1sd-1639059053144)(5DCDEB319BFB4A3CB7182B76F736793C)]

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-NkLjkdP2-1639059053145)(CF33C662A42D438191EEC5502EA0C628)]

2.字段分析

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HbLN1dSZ-1639059053147)(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-spZyiR9V-1639059053150)(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-dBFZfP1J-1639059053151)(51295C12C1CF4665AFAF92192D7D8942)]

两表索引优化

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

三表索引优化

JOIN语句的优化：

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

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

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

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