mysql性能优化
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版本，当前比较稳定的版本）
mysql安装-rpm
-
检查服务器msyql安装情况，有就先卸载自带的mysql【Centos7 web服务版系统 默认安装mariadb】
rpm -qa|grep 软件名字 【检查命令】
rpm -e --nodeps 软件包名 【卸载命令】
yum remove 软件包名 【卸载命令】
rpm -qa|grep mariadb //卸载方式一 rpm -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 软件名字 【安装命令】
安装过程中可能会出现缺少依赖或者依赖冲突的问题，只要对依赖进行相应的安装和卸载即可
rpm -ivh MySQL-server-5.5.49-1.linux2.6.x86_64.rpm rpm -ivh MySQL-client-5.5.49-1.linux2.6.x86_64.rpm -
验证是否安装成功【查看mysql版本】
mysqladmin --version
mysql常用命令
- 常用命令
启动： service msyql start 关闭： service mysql stop 重启： service mysql restart 设置开机自启： chkconfig mysql on 检查开机自启是否成功： ntsysv / chkconfig --list mysql清屏： ctrl+L , system clear mysql安装成功启动时，肯会报 “var/lib/mysql/mysql.sock不存在”的错误，是 因为mysql服务没有启动，需要先手动启动服务： /etc/init.d/msyql start
msyql常用配置
-
设置密码
/usr/bin/mysqladmin -u root password 'new-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
cp /usr/share/mysql/my-huge.cnf /etc/my.cnf -
mysql字符编码
统一编码为utf8
注意：修改编码只对修改之后创建的数据库生效
/*查看编码*/ mysql> show variables like '%char%';#修改编码 vi /etc/my.cnf [msyql] default-character-set=utf8 [client] default-character-set=utf8 [msyqld] character_set_server=utf8 character_set_client=utf8 collation_server=utf8_general_ci
开放远程连接
-
设置root用户远程连接权限和密码
mysql> grant all privileges on *.* to 'root' @'%' identified by 'remote-password'; mysql> flush privileges; -
开放端口
firewall-cmd --zone=public --add-port=3306/tcp --permanent firewall-cmd --reload
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之间，组成一个事务去提交。
-
MyISAM只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据
InnoDB：支持行级锁和事务，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在有索引时是有效的，无索引或索引失效都会锁全表的
-
InnoDB支持外键，MyISAM不支持。
-
InnoDB的主键范围更大，最大是MyISAM的2倍。
-
InnoDB不支持全文索引，而MyISAM支持。全文索引是指对char、varchar和text中的每个词（停用词除外）建立倒排序索引。MyISAM的全文索引其实没啥用，因为它不支持中文分词，必须由使用者分词后加入空格再写到数据表里，而且少于4个汉字的词会和停用词一样被忽略掉。
-
MyISAM支持GIS数据，InnoDB不支持。即MyISAM支持以下空间数据对象：Point,Line,Polygon,Surface等。
-
没有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可以存放上百万条数据
-
常用命令
查询支持的引擎
mysql> show engines;查看当前使用的引擎
mysql> show variables like '%storage_engine%';指定表使用的引擎 engine
mysql> create table tb( id int(4) auto_increment, name varchar(5), dept varchar(5), primary key(id) )engine=MyISAM DEFAULT CHARSET=utf8 ;
解析过程
-
sql编写过程：
dinstinct 去重
select dinstinct.. from.. join.. on.. where.. group by.. having.. order by.. limit.. -
sql解析过程：
from.. on.. join.. where.. group by.. having.. select dinstinct.. order by.. limit..
4.索引,执行计划
索引
-
什么是索引
索引：相当于书的目录，是帮助mysql高效获取数据的数据结构
-
索引类型
普通索引 ： index idx_ 字段名
唯一索引： unique uk_字段名
主键索引： primary key pk_字段名
复合索引： 多个字段组成的索引（name,age） (按索引顺序字段名命名 idx_name_age)
复合索引并不一定所有索引字段都会用到。当name已经查询出结果是，不会再去查询age索引
但为了性能考虑建议在sql语句中用到所有的索引字段
-
创建索引
方式一 ： create 索引类型 索引名 on 表(字段);
方式二 ： alter table 表名 add constraint 索引名 索引类型(字段)；
//简单索引 mysql> create index idx_dept on tb(dept); //唯一索引 mysql> create unique index uk_name on tb(name); //复合索引 mysql> create index idx_dept_name on tb(dept,name); //主键索引 mysql> alter table tb add constraint pk_id primary key(id); -
删除索引
drop index 索引名 on 表名；
mysql> drop index uk_name on tb; mysql> drop index idx_dept on tb; mysql> drop index idx_dept_name on tb; -
查询索引
show index from 表名；
mysql> show index from tb; -
不适合创建索引的字段
索引本身需要的存储空间很大的字段
频繁需要修改的字段
很少使用的列（sql语句中用不到的列）
重复值多的列
执行计划
-
数据准备
mysql> create table course( cid int(3), cname varchar(20), tid int(3) ); create table teacher( tid int(3), tname varchar(20) ); create table teacherCard( tcid int(3), tid int(3), tcdesc varchar(200) ); mysql> insert into course values (1,'java',1),(2,'html',1),(3,'sql',2),(4,'web',3); insert into teacher values (1,'zhangsan'),(2,'lishi'),(3,'wangwu'); insert into teacherCard values (1,1,'zhangsan card'),(2,2,'lisi card'),(3,3,'wangwu card'); -
语法
explain sql语句；
explain select * from tb; -
explain信息详解 id
id：sql语句执行的编号
id值相同，从上往下顺序执行。这个顺序受表数据量的大小影响，先查数据量小的，后查数据量大的
id值不同：先执行id值大的
#查询课程编号为2 或 教师证编号为3 的老师信息 explain select t.tname from teacher t, course c, teacherCard tc where t.tid = c.tid and t.tcid = tc.tcid and (c.cid = 2 or tc.tcid = 3);#查询教授sql课程老师的描述信息 explain select tc.tcdesc from teacherCard tc where tc.tid = (select c.tid from course c where c.cname = 'sql' ); -
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条)。
explain select t.tname from teacher t,teacherCard tc where t.tid = tc.tid;ref : 索引查询返回匹配所有行（0条，多条）
explain select t.tname from teacher t where t.tname = 'ta';range : 检索指定范围的行，where后面是一个范围查询（between , in , > , < 等 其中in可能会导致索引失效而变成 all）
explain select tc.tcdesc from teacherCard tc where tc.tid between 1 and 2;index : 查询全部索引的数据
explain select t.tid from teacher t;all ：查询全部表的数据(sql 将表的所有数据都查了一遍) ，没有用到索引时常出现
explain select c.cname from course c; -
explain信息详解 possible_keys
可供选择的索引
-
explain信息详解key
实际用到的索引
-
explain信息详解key_len
实际使用到索引的长度（utf8 1个字符3个字节，）
-
explain信息详解ref
表之间的引用
指明当前表所参照的字段
const : 判断条件中用到了常量
或者显示用到了其他表的那些字段
-
explain信息详解rows
估计查询了表中的数据行数，（MySQL认为必须检查以执行查询的行数）
-
explain信息详解extra
准备工作
create table t ( a1 char(3), a2 char(3), a3 char(3), index idx_a1(a1), index idx_a2(a2), index idx_a3(a3) );using filesort : 性能损耗大，需要额外的查询(排序) ，常见于 order by 语句中
#当排序和查找不是同一个字段就会出现using filesort explain select t.a1,t.a2,t.a3 from t where t.a1 = '' order by a2; //反例 explain select t.a1,t.a2,t.a3 from t where t.a1 = '' order by a1; //正例 #复合索引不能跨列（最佳左前缀）否则会出现using filesort create index idx_a1_a2_a3 on t(a1,a2,a3); explain select t.a1,t.a2,t.a3 from t where t.a1 = '' order by a3; //反例(跨了 a2) explain select t.a1,t.a2,t.a3 from t where t.a2 = '' order by a3; //反例(跨了 a1) explain select t.a1,t.a2,t.a3 from t where t.a1 = '' order by a2; //正例using temporary:性能损耗大，用到了临时表，一般出现与 group by 语句中
explain select t.a1,t.a2,t.a3 from t where a1 in ('1','2','3') group by a2; //反例 explain select t.a1,t.a2,t.a3 from t where a1 in ('1','2','3') group by a1; //正例using index: 性能提升，只从索引中查询数据，不需要回表查询
using where: 进行了回表查询
drop index idx_a1_a2_a3 on t； create index idx_a1_a2 on t(a1,a2); explain select a1,a2 from t where a1='' and a2 = ''; //正例 explain select a1,a3 from t where a1=''; //反例impossible where : where 查询条件永远为fasle
explain select a1 from t where a1='a' and a1 = 'b';Using join buffer : MySQL引擎使用了连接缓存，表示sql语句太烂，性能低下
优化总结
-
优化一般不能一次就优化到最佳效果，需要在开发过程中根据使用情况多次逐步优化
-
复合索引保证最佳左前缀原则
-
小表驱动大表
-
索引建立在经常查询的字段上
-
复合索引，尽量使用全索引匹配（说明：假设使用了三个字段建立了一个复合索引，在sql查询中尽量让三个索引都用到）
-
不要在索引上进行任何操作（计算、函数、类型转换）,否则索引失效
-
复合索引不能使用不等于（ != , <> ）和 is null, is not null ,否则索引失效
-
like 尽量以 ‘常量’ 开头，不要使用 ‘%x%’ ，否则索引失效
-
尽量不要使用or ，否则索引失效
-
如果必须使用到索引失效的情况，尽量使用索引覆盖（using index）,可能会使索引生效，达到性能优化
-
将含有in的范围查询放到where条件的最后面，防止索引失效（尽量不使用in）
-
连接查询 a.t = b.t 的情况下，将表数据量小的放在左边，表数据量大的放在右边会提高性能
-
连接查询 a.t = b.t 的情况下，将 a 表 t 字段加索引会提高性能
-
对于左外连接给左表加索引，右外连接给右表加索引
-
exist 和 in，如果主查询的数据集大，则使用in，如果子查询的数据集大,使用exist
-
提高 order by 查询的策略
a、选择使用单路、双路；调整buffer的容量大小
b、避免使用 select * …
c、保证排序字段的 排序一致性
-
常用sql语句
-
查看数据库
mysql> show databases; -
选中数据库
mysql> use test; -
查看表
mysql> show tables; -
查看表结构信息
mysql> desc tb; -
删除表
mysql> drop table tb;
5.慢查询
检查是否开启了慢查询
mysql> show variables like '%slow_query_log%';
开启慢查询
//临时开启 - mysql重启后失效
mysql> set global slow_query_log = 1;
//永久开启 - 修改my.cnf 文件
vim /etc/my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/localhost-slow.log
慢查询阈值
//查看
mysql> show variables like '%long_query_time%';
//临时设置 (单位秒) - 重新登陆起效
mysql> set global long_query_time = 2;
//永久设置 - 修改my.cnf 文件
vim /etc/my.cnf
[mysqld]
long_query_time = 2
查看超过慢查询阈值的sql次数
mysql> show global status like '%slow_queries%';
查看超过慢查询阈值具体的sql信息
- 查看slow_query_log_file日志文件
- 使用mysqldumpslow工具
mysqldumpslow --help
-s : 排序方式 (r 逆序)
-l ：锁定时间
-t ：查询多少条
-g : 正则表达式
//获取返回记录最多的3个SQL
mysqldumpslow -s r -t 3 /var/lib/mysql/localhost-slow.log
//获取访问次数最多的3个SQL
mysqldumpslow -s c -t 3 /var/lib/mysql/localhost-slow.log
//按照时间排序，查询前10条包含left join查询语句的sql
mysqldumpslow -s t -t 10 -g 'left join' /var/lib/mysql/localhost-slow.log
6.创建，分析海量数据
-
创建海量数据
测试用表
create database testdata; use testdata; #部门表 create table dept( dno int(5) primary key default 0, dname varchar(20) not null default '', loc varchar(30) default '' )engine=innodb default charset=utf8; #员工表 create table emp( eid int(5) primary key, ename varchar(20) not null default '', job varchar(20) not null default '', deptno int(5) not null default 0 )engine=innodb default charset=utf8;创建存储函数 ，用于插入海量数据
#修改MySQL 结束分割符 delimiter $ #产生一个随机字符串 create function randstring(n int) returns varchar(255) begin declare all_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTVUWXYZ'; declare return_str varchar(255) default ''; declare i int default 0; while i<n do set return_str = concat(return_str,substring(all_str,floor(1+rand()*52),1)); set i=i+1; end while; return return_str; end $ #尝试一个随机整数 create function ran_num() returns int(5) begin declare i int default 0; set i = floor(rand()*30 + 1); return i; end $ #向emp表插入数据-存储过程 #autocommit = 0 关闭自动提交 create procedure insert_emp(in data_times int(10)) begin declare i int default 0; set autocommit = 0; repeat insert into emp (ename,job,deptno) values(randstring(5),randstring(10),ran_num()); set i= i+1; until i = data_times end repeat; commit; end $ #向dept表插入数据 - 存储过程 create procedure insert_dept(in data_times int(10)) begin declare i int default 0; set autocommit = 0; repeat insert into dept (dname,loc) values(randstring(5),randstring(20)); set i = i+1; until i = data_times end repeat; commit; end $使用存储过程/函数，插入数据
#创建30个部门 call insert_dept(30); #创建80万个员工 call insert_emp(800000);想要慢查询和存储过程/函数 同时启用，需要开启 log_bin_trust_function_creators
#查询 log_bin_trust_function_creators 状态 mysql> show variables like '%log_bin_trust_function_creators%'; #设置 log_bin_trust_function_creators mysql> set global log_bin_trust_function_creators = 1; -
分析海量数据
profiling
#查询 profiling 状态 mysql> show variables like '%profiling%'; #开启profiling mysql> set profiling = on; #查看被记录的sql #profiling 会记录开启之后的 所有执行的sql, #精确分析： show profile all for query 查询记录id; mysql> show profiles; mysql> show profile all for query 3; mysql> show profile cpu,block io for query 3;全局日志
全局日志表 mysql.general_log
开启全局日志后所有的sql执行都会被记录到 mysql 库中的 general_log 表中
#查询全局日志 状态 mysql> show variables like '%general_log%'; #开启全局日志 mysql> set global general_log = 1; mysql> set global log_output = 'table'; //将sql记录在表中，设置后将不会写入日志文件而是写入表中
7.主从复制
-
原理
- master将改变的数据 记录在本地的二进制文件中（binary log）,该过程称为: 二进制日志事件
- slave将master的binary log拷贝到自己的 realay log 中
- slave从 realay log 中将数据读取到自己的数据库中
-
主数据库配置
修改配置文件 my.cnf
vi /etc/my.cnf [mysqld] server-id = 1 #数据库id log-bin = /var/lib/mysql/mysql-bin #二进制日志文件地址 log-error = /var/lib/mysql/mysql-error #错误日志文件地址 binlog-ignore-db = mysql #忽略同步的数据库 binlog-do-db = test #指定需要同步的数据库 - 可选授权从计算机
mysql> GRANT REPLICATION slave,reload,super ON *.* TO 'root'@'192.168.0.%' IDENTIFIED BY 'root'; mysql> flush privileges;查看主数据库状态 - 并记录 File，postion 对应的值
mysql> show master status; -
从数据库配置
修改配置文件 my.cnf
server-id 有时不起作用，将会默认为1 ，导致冲突，需要通过命令的方式手动设置
vi /etc/my.cnf [mysqld] server-id = 2 #数据库id relay-log = slave-relay-bin #二进制日志文件 replicate-do-db = test #指定需要同步的数据库授权主计算机
mysql> CHANGE MASTER TO MASTER_HOST = '192.168.0.77', MASTER_USER = 'root', MASTER_PASSWORD = 'root', MASTER_PORT = 3306, master_log_file = 'mysql-bin.000011', master_log_pos = 345;开启主从同步
mysql> start slave;检查主从同步状态
确保 Slave_IO_Runing , Slave_SQL_Runing 都为 yes
mysql> show slave status \G -
常用命令
查看server-id
mysql> show variables like 'server_id';手动设置server-id
mysql> set global server_id = 2;停止主从同步
mysql> stop slave;