Mysql性能优化

索引及优化

一、Mysql性能优化之影响性能的因素

1.商业需求的影响

不合理的需求造成的资源投入产出，这里就用一个看上去很简单的功能分析。
需求：一个论坛帖子的总量统计，附加要求：实时更新。从功能上看来是非常容易实现的，执行一条select count（*）from表名就可以得到结果，但是如果我们采用的不是myisam存储引擎，而是用的innodb的存储引擎，如果存放帖子的表中已经有了上千万的帖子的时候，执行这条语句需要很大的成本。恐怕都不可能在10秒之内完成一次查询。

2.系统架构及实现的影响
所有数据都是适合在数据库存放吗？数据库为我们提供了太多的功能，反而让很多并不是台了解数据库的人错误的使用了数据库的很多并不是太擅长或者对性能影响很大的功能，最后却都怪到了数据库上。以下几类数据都是不适合在数据库中存放的

(1)二进制多媒体的数据包括（图片，视频，音频）和其他一些相关的二进制文件，将二进制多媒体数据存放在数据库中，一个问题是数据库空间资源耗用非常的严重，另外一个问题是这些数据库的存储很消耗数据库主机的cpu等资源，这些数据的处理本不是数据库的优势
(2)超大的文本数据
对于5.0.3之前的mysql版本来说，varchar类型的数据最长智能存放225个字节，如果需要存储更长的文本数据到一个字段，我们就必须使用text类型最大可以存放64K的字段，甚至更大的longtext类型最大4G从5.0.3版本开始varchar类型的最大长度被调整到了64KB了，所以超大文本数据存放在数据库中不仅会带来性能低下的问题，还好带来空间占用浪费的问题
(3)查询语句对性能的影响
Sql语句优劣是对性能有影响的，每一个sql语句在优化之前和优化之后的性能差异也是各个不同。
首先先插入两万行数据测试，之后会用的到，脚本如下：

#!/bin/bash  
HOSTNAME="localhost"  
PORT="3306"  
USERNAME="root"  
PASSWORD="pwd123"  
DBNAME="test1"  
TABLENAME="tb1"  
#create database  
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "drop database if exists ${DBNAME}"  
create_db_sql="create database if not exists ${DBNAME}"  
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e"${create_db_sql}"  
#create table  
create_table_sql="create table if not exists ${TABLENAME}(stuid int not null primary key,stuname varchar(20) not null,stusex char(1)   
not null,cardid varchar(20) not null,birthday datetime,entertime datetime,address varchar(100)default null)"  
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e"${create_table_sql}"  
#insert data to table  
i="1"  
while [ $i -le 20000 ]  
do  
insert_sql="insert into ${TABLENAME}  values($i,'zhangsan','1','21276387261874682','1999-10-10','2017-10-24','beijingchangpingqu')"  
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e"${insert_sql}"  
let i++  
done  
#select data  
select_sql="select count(*)from${TABLENAME}"  
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e"${select_sql}"

Mysql性能优化

运行过程及运行结束如下：

Mysql性能优化

以上图中其中的以下字段意思是：MySQL：[警告]在命令行界面使用密码可能是不安全的。

mysql: [Warning] Using a password on the command line interface can be insecure.

插入两万行数据后，可以这么查看

mysql> select count(*) from test1.tb1;  
+----------+  
| count(*) |  
+----------+  
|    20000 |  
+----------+  
1 row in set (0.01 sec)

3.执行sql语句时可以用explain来查看执行计划：

mysql> explain select stuid,stuname,cardid from test1.tb1 where stuid between 3000 and 5000 order by stuid desc limit 20\G

Mysql性能优化

还可以打开mysql的profiping功能，来查看sql的实际执行计划

mysql> set profiling=1;  
select stuid,stuname,cardid from test1.tb1 where stuid between 3000 and 5000 order by stuid desc limit 5\G

Mysql性能优化

4、数据库schema设计对性能的影响
5、硬件选择对性能的影响

二、mysql性能优化之-索引
如果正确的合理设计并且使用索引的mysql是一架飞机，那么没有设计和使用索引的mysql就是一辆自行车，对于没有索引的表，单表查询可能几十万数据就是瓶颈，而通常大型网站一天就会产生几十万甚至几百万的数据，没有设置索引会非常的慢。

1.为了测试我们给tb1表中插入一条不相同的数据。

mysql> insert into test1.tb1 values(20001,'admin','0','12322112123332','1999-1-1','2019-9-1','ppppppppppp');  
Query OK, 1 row affected (0.00 sec)

例1：stuname上没有创建索引的情况

mysql> explain select stuid,stuname,stusex,cardid,entertime from test1.tb1 where stuname='admin'\G

Mysql性能优化

例2：我们给stuname上创建索引再次查看。

mysql> create index index_stuname on test1.tb1(stuname);

Mysql性能优化

在查找stuname=”admin”的记录的时候，如果stuname上已经建立了索引，mysql无须任何扫描全表，即准确可找到该记录，相反mysql会扫描所有的记录，所以在数据库表中，对字段建立索引可以大大的提高查询的速度

索引的类型
1）普通索引这是最基本的索引，它没有任何的限制

Create index indexname on 表（xxx）

2）唯一索引:它和前面的普通索引相似，不同的就是索引列的值必须唯一，但允许空值，空值指的就是null如果是组合索引，组合列的值必须是唯一的。

Create unique index indexname on 表（xxx）

3）组合索引：为了进一步的提升mysql的效率，就要考虑建立组合索引
列如创建一个表，包含如下字段

Create table mytable（id int not null，uname varchar（16）not null,city varchar(50) not ntll,age int not null）;

将uname,city,age建在一个索引里

Create index uname_city_age on mytable（uname，ctiy，age）

4、全文索引：只用于myisam表对文本进行索引，字段包括char varchar text不过切记对大容量的数据表，生成全文索引的是一个非常消耗时间硬盘的做法

Create fulltext index indexname on tablename（column）

查看索引

mysql> show index from test1.tb1;

Mysql性能优化

mysql> show keys from test1.tb1;

Mysql性能优化

三、mysql性能优化-，慢查询分析、优化索引和配置
1)性能瓶颈定位
show 命令  慢查询日志 explain分析查询 profiling分析查询
2）索引及查询优化
3）配置优化
mysql数据库最常见的两个瓶颈的CPU和I/O的瓶颈，cpu在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候，磁盘I/O瓶颈发生在装入数据远大于内存容量的时候，如果应用分布在网络上，那么查询量相当大的时候那么瓶颈就会出现在网络上，我们可以用mpstat，iostat，sar和vmstat来查看系统的性能状态
使用mpstat来查看系统的性能状态如下：

Mysql性能优化

上图我写的是mpstat 3 6这个意思是3秒运行1次 一共6次 然后红方框的idle显示的是空闲度，从这个图中可以看出此时很空闲，那么我们来给它点压力来测试。（我这边插入10万行的数据，然后再次看它的变化）

Mysql性能优化

Mysql性能优化

以上图中正在运行插入10万行的数据！

4）show命令可以查看mysql状态及变量，找到系统的瓶颈

mysql> show variables;  
mysql> show global status;

Mysql性能优化

同时也可以这么查看，都是一样的

[root@yankerp ~]# mysqladmin variables -uroot -ppwd123  
[root@yankerp ~]# mysqladmin extended-status -uroot -ppwd123

更多的show命令可以通过mysql>help show来查看

开启慢查询日志
在配置文件中my.cnf下加入3个配置参数，并重启mysql服务
查看慢查询日志是否开启

mysql> show global variables like "%slow_query_log%"

Mysql性能优化

开启慢查询日志，打开主配置文件，添加如下：

Mysql性能优化

重启mysql服务再次查看慢查询日志

mysql> show global variables like "%slow_query_log%"

Mysql性能优化

查看查询的时间以及设置查询的时间

mysql> show global variables like "%long%";

Mysql性能优化

这里我们设置时间为0.001秒，修改主配置文件添加如下：

Mysql性能优化

修改完成后重启mysql服务再次查看查询时间

mysql> show global variables like "%long%";

Mysql性能优化

我们可以通过打开log文件查看得知哪些sql执行效率低下
我们做以下操作，查库，查表，然后在看log文件

Mysql性能优化

查看log文件

Mysql性能优化

以下显示的就一条慢查询，如何优化呢

Mysql性能优化

1、在entertime列上创建索引优化查询

mysql> create index index_entertime on test1.tb1(entertime);

Mysql性能优化

2、explain分析查询
使用explain关键字可以模拟优化器执行sql查询语句，从而知道mysql是如何处理你的sql语句的，这可以帮你分析你的查询语句或是表结构性能瓶颈，通过explain命令可以得到

mysql> explain select * from test1.tb1 where stuname='yankerp'\G

Mysql性能优化

explain字段：
table：显示这一行数据是关于那张表的
type：这是最重要的字段之一，显示查询使用了何种类型。从最好的到最差的连接类型位system、const、eq_reg、ref、range、index和all
possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引
key：实际使用的索引，如果为null，则没有使用索引。
key_len：使用的索引长度，在不损失的情况下，长度越短越好
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
rows：mysql认为不许检索的用来返回请求数据的行数
extra：关于mysql如何解析查询的额外信息
从上面的explain模拟优化器中执行sql语句来看是由索引查询的。
3profiling分析查询
通过慢日志查询可以知道那些sql语句执行效率低下，通过explain我可以得知sql语句的具体执行情况，索引使用等，还可以结合show命令查看执行状态，可以通过profiling命令得到更准确的sql执行消耗系统资源的信息。
profiling默认是关闭的。可以通过以下语句查看

mysql> show variables like '%profiling%';  //off代表未开启

Mysql性能优化

打开profiling功能：mysql>set profiling=1；执行需要测试的sql语句：

mysql> select @@profiling;

Mysql性能优化

4、执行要测试的sql语句

mysql> select * from test1.tb1 where stuname='admin' and entertime='2019-09-01';  
mysql> show profiles\G   //可以得到被执行语句的id

Mysql性能优化

mysql> show profile for query 6; //得到对应执行sql语句的详细信息

Mysql性能优化

测试完成后，记得关闭调试功能，以免影响到数据库的正常使用

mysql> set profiling=0;  
Query OK, 0 rows affected, 1 warning (0.00 sec)