mysql性能优化

文章目录

1.为什么要进行sql优化

* 2.Mysql安装启动配置(CentOS7)

mysql版本与性能

* mysql安装-rpm

* mysql常用命令

* msyql常用配置

* 开放远程连接

* .逻辑分层,存储引擎,解析过程

逻辑分层

* 存储引擎

* 解析过程

* .索引,执行计划

索引

* 执行计划

* 优化总结

* 常用sql语句

* .慢查询 * .创建，分析海量数据 * .主从复制

1.为什么要进行sql优化

因为没有进行sql优化的语句执行性能低下。而性能低下的原因:sql语句欠佳，索引失效，服务器参数设置不合理（缓冲、线程数）
整个优化过程主要是围绕索引进行
详情参考官网： https://dev.mysql.com/doc/refman/5.5/en/optimization.html

2.Mysql安装启动配置(CentOS7)

mysql版本与性能

版本介绍与选择

目前主流版本 5.x：
5.0-5.1：相当于4.x版本的延续，升级维护
5.4 -5.x: Mysql整合了三方公司的新存储引擎（推荐使用5.5版本，当前比较稳定的版本）

2、硬件与性能

mysql安装-rpm

检查服务器msyql安装情况，有就先卸载自带的mysql【Centos7 web服务版系统默认安装mariadb】

rpm -qa|grep 软件名字【检查命令】
rpm -e --nodeps 软件包名【卸载命令】
yum remove 软件包名【卸载命令】

-qa mariadb //卸载方式一 -e --nodeps mariadb-libs-5.5.44-2.el7.centos.x86_64 //卸载方式二建议使用yum卸载,可自动处理依赖关系 yum remove mariadb-libs-5.5.44-2.el7.centos.x86_64

安装，先安装服务端，再安装客户端

rpm -ivh 软件名字【安装命令】
安装过程中可能会出现缺少依赖或者依赖冲突的问题，只要对依赖进行相应的安装和卸载即可

-ivh MySQL-server-5.5.49-1.linux2.6.x86_64.rpm -ivh MySQL-client-5.5.49-1.linux2.6.x86_64.rpm

验证是否安装成功【查看mysql版本】

mysqladmin --version

mysql常用命令

常用命令

启动： msyql start 关闭： mysql stop 重启： mysql restart 设置开机自启： mysql on 检查开机自启是否成功： ntsysv / --list mysql清屏： ctrl+L , system mysql安装成功启动时，肯会报 “var/lib/mysql/mysql.sock不存在”的错误，是因为mysql服务没有启动，需要先手动启动服务： /etc/init.d/msyql start

msyql常用配置

设置密码

/usr/bin/mysqladmin -u root password

mysql -u root -p

mysql核心目录

数据库目录： datadir=/var/lib/mysql
pid文件目录：–pid-fire= /var/lib/mysql/bigdata01.pid
配置文件： /usr/share/msyql
命令目录： /usr/bin 【msyqladmin,mysqldump等命令】
mysql启动脚本: /etc/init.d/mysql

mysql配置文件（/var/share/msyql目录下）

my-huge.cnf 高端服务器 1-2G内存就算是高端服务器可以使用
my-large.cnf 中等规模
my-medium.cnf 一般
my-smqll.cnf 较小
但是，MySQL默认使用的配置文件是 /etc/my.cnf想要采用my-huge.cnf,复制替换即可
注意：MySQL版本不同，默认配置文件名称不同，如：mysql5.6 默认配置为叫 /etc/mysql-default.cnf

/usr/share/mysql/my-huge.cnf /etc/my.cnf

mysql字符编码

统一编码为utf8
注意：修改编码只对修改之后创建的数据库生效

* .索引,执行计划

开放远程连接

设置root用户远程连接权限和密码

* .索引,执行计划

开放端口

* .索引,执行计划

3.逻辑分层,存储引擎,解析过程

逻辑分层

服务层 - 1.提供各种用户使用的接口(增删改查，等) 2.提供sql优化器（mysql query optimizer）

引擎层 - 提供了各种存储数据的方式（InnoDB,MyISAM）

存储层 - 存储数据

存储引擎

常用引擎（InnoDB， MyISAM）

MyISAM：ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写，它是存储记录和文件的标准方法。不是事务安全的，而且不支持外键，如果执行大量的select，insert MyISAM比较适合
InnoDB：支持事务安全的引擎，支持外键、行锁、事务是他的最大特点。如果有大量的update和insert，建议使用InnoDB，特别是针对多个并发和QPS较高的情况

区别

MyISAM不支持事务，而InnoDB支持。InnoDB的AUTOCOMMIT默认是打开的，即每条SQL语句会默认被封装成一个事务，自动提交，这样会影响速度，所以最好是把多条SQL语句显示放在begin和commit之间，组成一个事务去提交。
  * .索引,执行计划4
MyISAM只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据
InnoDB：支持行级锁和事务，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在有索引时是有效的，无索引或索引失效都会锁全表的
  * .索引,执行计划4
InnoDB支持外键，MyISAM不支持。
  * .索引,执行计划4
InnoDB的主键范围更大，最大是MyISAM的2倍。
  * .索引,执行计划4
InnoDB不支持全文索引，而MyISAM支持。全文索引是指对char、varchar和text中的每个词（停用词除外）建立倒排序索引。MyISAM的全文索引其实没啥用，因为它不支持中文分词，必须由使用者分词后加入空格再写到数据表里，而且少于4个汉字的词会和停用词一样被忽略掉。
  * .索引,执行计划4
MyISAM支持GIS数据，InnoDB不支持。即MyISAM支持以下空间数据对象：Point,Line,Polygon,Surface等。
  * .索引,执行计划4
没有where的count()使用MyISAM要比InnoDB快得多。因为MyISAM内置了一个计数器，count()时它直接从计数器中读，而InnoDB必须扫描全表。所以在InnoDB上执行count()时一般要伴随where，且where中要包含主键以外的索引列。为什么这里特别强调“主键以外”？因为InnoDB中primary index是和raw data存放在一起的，而secondary index则是单独存放，然后有个指针指向primary key。所以只是count()的话使用secondary index扫描更快，而primary key则主要在扫描索引同时要返回raw data时的作用较大。

存储结构

InnoDB 和 Myisam 都是用 B+Tree 来存储数据的。
一般情况3层B+Tree可以存放上百万条数据

常用命令

查询支持的引擎

* .慢查询 * .创建，分析海量数据 * .主从复制

查看当前使用的引擎

* .慢查询 * .创建，分析海量数据 * .主从复制

指定表使用的引擎 engine

* .慢查询 * .创建，分析海量数据 * .主从复制

解析过程

sql编写过程：

dinstinct 去重

* .慢查询 * .创建，分析海量数据 * .主从复制

sql解析过程：

详解可参考 https://www.cnblogs.com/annsshadow/p/5037667.html

* .慢查询 * .创建，分析海量数据 * .主从复制

4.索引,执行计划

索引

什么是索引

索引：相当于书的目录，是帮助mysql高效获取数据的数据结构

索引类型

普通索引： index idx_ 字段名
唯一索引： unique uk_字段名
主键索引： primary key pk_字段名
复合索引：多个字段组成的索引（name,age） (按索引顺序字段名命名 idx_name_age)
复合索引并不一定所有索引字段都会用到。当name已经查询出结果是，不会再去查询age索引
但为了性能考虑建议在sql语句中用到所有的索引字段

创建索引

方式一： create 索引类型索引名 on 表(字段);
方式二： alter table 表名 add constraint 索引名索引类型(字段)；

* .慢查询 * .创建，分析海量数据 * .主从复制

删除索引

drop index 索引名 on 表名；

* .慢查询 * .创建，分析海量数据 * .主从复制

查询索引

show index from 表名；

* .慢查询 * .创建，分析海量数据 * .主从复制

不适合创建索引的字段

索引本身需要的存储空间很大的字段
频繁需要修改的字段
很少使用的列（sql语句中用不到的列）
重复值多的列

执行计划

数据准备

* .慢查询 * .创建，分析海量数据 * .主从复制

语法

explain sql语句；

* .慢查询 * .创建，分析海量数据 * .主从复制

explain信息详解 id

id：sql语句执行的编号
id值相同，从上往下顺序执行。这个顺序受表数据量的大小影响，先查数据量小的，后查数据量大的
id值不同：先执行id值大的

explain信息详解 select_type

select_type 查询类型
PRIMARY : 主查询，sql中包含有子查询
SUBQUERY : 子查询
SIMPLE：普通查询（不含有子查询和union 连接查询的查询）
DERIVED: 衍生查询（使用到了临时表）
union ：使用到了union 连接查询
union result: 显示拿些表之间使用了union

explain信息详解 table

表名

explain信息详解 type

type 索引类型
常用到的类型system > const > eq_ref > ref > range > index > all
system 性能最高，all性能最低。实际项目中达到 ref > range 性能就行
system ：表只有中一条数据的主查询
const ：查询结果只有一条数据的sql ,并且索引类型必须为主键索引或者唯一索引
eq_ref ：查询结果可以有多条数据，但满足where判断条件的每一条数据必须是唯一的一条数据(不能多条也不能为0条)。
 -qa mariadb
//卸载方式一
 -e --nodeps mariadb-libs-5.5.44-2.el7.centos.x86_64
//卸载方式二建议使用yum卸载,可自动处理依赖关系
yum remove mariadb-libs-5.5.44-2.el7.centos.x86_642
ref : 索引查询返回匹配所有行（0条，多条）
 -qa mariadb
//卸载方式一
 -e --nodeps mariadb-libs-5.5.44-2.el7.centos.x86_64
//卸载方式二建议使用yum卸载,可自动处理依赖关系
yum remove mariadb-libs-5.5.44-2.el7.centos.x86_643
range : 检索指定范围的行，where后面是一个范围查询（between , in , > , < 等其中in可能会导致索引失效而变成 all）
 -qa mariadb
//卸载方式一
 -e --nodeps mariadb-libs-5.5.44-2.el7.centos.x86_64
//卸载方式二建议使用yum卸载,可自动处理依赖关系
yum remove mariadb-libs-5.5.44-2.el7.centos.x86_644
index : 查询全部索引的数据
 -qa mariadb
//卸载方式一
 -e --nodeps mariadb-libs-5.5.44-2.el7.centos.x86_64
//卸载方式二建议使用yum卸载,可自动处理依赖关系
yum remove mariadb-libs-5.5.44-2.el7.centos.x86_645
all ：查询全部表的数据(sql 将表的所有数据都查了一遍) ，没有用到索引时常出现
 -qa mariadb
//卸载方式一
 -e --nodeps mariadb-libs-5.5.44-2.el7.centos.x86_64
//卸载方式二建议使用yum卸载,可自动处理依赖关系
yum remove mariadb-libs-5.5.44-2.el7.centos.x86_646

explain信息详解 possible_keys

可供选择的索引

explain信息详解key

实际用到的索引

explain信息详解key_len

实际使用到索引的长度（utf8 1个字符3个字节，）

explain信息详解ref

表之间的引用
指明当前表所参照的字段
const : 判断条件中用到了常量
或者显示用到了其他表的那些字段

explain信息详解rows

估计查询了表中的数据行数，（MySQL认为必须检查以执行查询的行数）

explain信息详解extra

准备工作

using filesort : 性能损耗大，需要额外的查询(排序) ，常见于 order by 语句中

using temporary:性能损耗大，用到了临时表，一般出现与 group by 语句中

using index: 性能提升，只从索引中查询数据，不需要回表查询
using where: 进行了回表查询

-ivh MySQL-server-5.5.49-1.linux2.6.x86_64.rpm -ivh MySQL-client-5.5.49-1.linux2.6.x86_64.rpm

impossible where : where 查询条件永远为fasle

-ivh MySQL-server-5.5.49-1.linux2.6.x86_64.rpm -ivh MySQL-client-5.5.49-1.linux2.6.x86_64.rpm

Using join buffer : MySQL引擎使用了连接缓存，表示sql语句太烂，性能低下

优化总结

优化一般不能一次就优化到最佳效果，需要在开发过程中根据使用情况多次逐步优化

复合索引保证最佳左前缀原则
  * .索引,执行计划4
小表驱动大表
  * .索引,执行计划4
索引建立在经常查询的字段上
  * .索引,执行计划4
复合索引，尽量使用全索引匹配（说明：假设使用了三个字段建立了一个复合索引，在sql查询中尽量让三个索引都用到）
  * .索引,执行计划4
不要在索引上进行任何操作（计算、函数、类型转换）,否则索引失效
  * .索引,执行计划4
复合索引不能使用不等于（ != , <> ）和 is null, is not null ,否则索引失效
  * .索引,执行计划4
like 尽量以 ‘常量’ 开头，不要使用 ‘%x%’ ，否则索引失效
  * .索引,执行计划4
尽量不要使用or ，否则索引失效
  * .索引,执行计划4
如果必须使用到索引失效的情况，尽量使用索引覆盖（using index）,可能会使索引生效，达到性能优化
  * .索引,执行计划4
将含有in的范围查询放到where条件的最后面，防止索引失效（尽量不使用in）
  * .索引,执行计划4
连接查询 a.t = b.t 的情况下，将表数据量小的放在左边，表数据量大的放在右边会提高性能
  * .索引,执行计划4
连接查询 a.t = b.t 的情况下，将 a 表 t 字段加索引会提高性能
  * .索引,执行计划4
对于左外连接给左表加索引，右外连接给右表加索引
  * .索引,执行计划4
exist 和 in，如果主查询的数据集大，则使用in，如果子查询的数据集大,使用exist
  * .索引,执行计划4
提高 order by 查询的策略
a、选择使用单路、双路；调整buffer的容量大小
b、避免使用 select * …
c、保证排序字段的排序一致性

常用sql语句

查看数据库

mysqladmin --version

选中数据库

mysqladmin --version

查看表

mysqladmin --version

查看表结构信息

mysqladmin --version

删除表

* .慢查询 * .创建，分析海量数据 * .主从复制

5.慢查询

检查是否开启了慢查询

开启慢查询

慢查询阈值

查看超过慢查询阈值的sql次数

查看超过慢查询阈值具体的sql信息
查看slow_query_log_file日志文件
使用mysqldumpslow工具

6.创建，分析海量数据

创建海量数据

测试用表

创建存储函数，用于插入海量数据

使用存储过程/函数，插入数据

/usr/bin/mysqladmin -u root password

想要慢查询和存储过程/函数同时启用，需要开启 log_bin_trust_function_creators

/usr/bin/mysqladmin -u root password

分析海量数据

profiling

/usr/bin/mysqladmin -u root password

全局日志
全局日志表 mysql.general_log
开启全局日志后所有的sql执行都会被记录到 mysql 库中的 general_log 表中

/usr/bin/mysqladmin -u root password

7.主从复制

原理

master将改变的数据记录在本地的二进制文件中（binary log）,该过程称为: 二进制日志事件
slave将master的binary log拷贝到自己的 realay log 中
slave从 realay log 中将数据读取到自己的数据库中

主数据库配置

修改配置文件 my.cnf

/usr/bin/mysqladmin -u root password

授权从计算机

/usr/bin/mysqladmin -u root password

查看主数据库状态 - 并记录 File，postion 对应的值

/usr/bin/mysqladmin -u root password

从数据库配置

修改配置文件 my.cnf

/usr/bin/mysqladmin -u root password

授权主计算机

/usr/bin/mysqladmin -u root password

开启主从同步

/usr/bin/mysqladmin -u root password

检查主从同步状态
确保 Slave_IO_Runing , Slave_SQL_Runing 都为 yes
这里出现的Slave_IO_Running 为no 是因为配置文件my.cnf中server-id 没起到作用。具体什么原因导致暂时还不清楚。如果有了解的博友望告知！临时的解决方式 - 通过命令行的形式设置server-id

mysql -u root -p