MySQL性能优化之基于SQLadvisor工具一次优化尝试

[root@SQLAdvisor ~]# getenforce 
Disabled
[root@SQLAdvisor ~]# yum -y install cmake libaio-devel libffi-devel glib2 glib2-devel

$ yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

$ yum install Percona-Server-shared-56

[root@xjfw3 ~]# cmake --version
cmake version 3.10.0-rc4
CMake suite maintained and supported by Kitware (kitware.com/cmake).

[root@SQLAdvisor ~]# ln -s usr/lib64/libperconaserverclient_r.so.18 usr/lib64/libperconaserverclient_r.so

[root@SQLAdvisor ~]# cd SQLAdvisor/
[root@SQLAdvisor SQLAdvisor]# cmake -DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=debug -DCMAKE_INSTALL_PREFIX=/usr/local/sqlparser ./
[root@SQLAdvisor SQLAdvisor]# make && make install

[root@SQLAdvisor SQLAdvisor]# cd sqladvisor/
[root@SQLAdvisor sqladvisor]# cmake -DCMAKE_BUILD_TYPE=debug ./
[root@SQLAdvisor sqladvisor]# make
[root@SQLAdvisor sqladvisor]# ./sqladvisor --help

mysql> create database test1 character set utf8mb4;
Query OK, 1 row affected (0.00 sec)


mysql> create table user(
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(64) NOT NULL,
-> age int,
-> sex int
-> )ENGINE=INNODB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.13 sec)


mysql> desc user;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(64) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)


* 生成测试数据
mysql> insert into user(name,age, sex) select 'lisea', 25, 1;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
生产测试数据
insert into user(name,age, sex) select concat(name, '1'), age+1, sex+1 from user;
insert into user(name,age, sex) select concat(name, '2'), age+2, sex from user;
insert into user(name,age, sex) select concat(name, '3'), age+2, sex from user;
insert into user(name,age, sex) select concat(name, '10'), age+2, sex from user;
insert into user(name,age, sex) select concat(name, '11'), age+4, sex from user;
--执行两次


结果
[mysql@mysql sqladvisor]$ ./sqladvisor -h 192.168.226.131 -P 16063 -u root -p '6yhn^YHN' -d test1 -q "select * from user where name = 'lisea'" -v 1
2019-10-24 07:36:20 35965 [Note] 第1步: 对SQL解析优化之后得到的SQL:select `*` AS `*` from `test1`.`user` where (`name` = 'lisea') 
2019-10-24 07:36:20 35965 [Note] 第2步：开始解析where中的条件:(`name` = 'lisea') 
2019-10-24 07:36:20 35965 [Note] show index from user 
2019-10-24 07:36:20 35965 [Note] show table status like 'user' 
2019-10-24 07:36:20 35965 [Note] select count(*) from ( select `name` from `user` FORCE INDEX( PRIMARY ) order by id DESC limit 10000) `user` where (`name` = 'lisea') 
2019-10-24 07:36:20 35965 [Note] 第3步：表user的行数:1045485,limit行数:10000,得到where条件中(`name` = 'lisea')的选择度:10000 
2019-10-24 07:36:20 35965 [Note] 第4步：开始验证 字段name是不是主键。表名:user
2019-10-24 07:36:20 35965 [Note] show index from user where Key_name = 'PRIMARY' and Column_name ='name' and Seq_in_index = 1 
2019-10-24 07:36:20 35965 [Note] 第5步：字段name不是主键。表名:user
2019-10-24 07:36:20 35965 [Note] 第6步：开始验证 字段name是不是主键。表名:user
2019-10-24 07:36:20 35965 [Note] show index from user where Key_name = 'PRIMARY' and Column_name ='name' and Seq_in_index = 1 
2019-10-24 07:36:20 35965 [Note] 第7步：字段name不是主键。表名:user
2019-10-24 07:36:20 35965 [Note] 第8步：开始验证表中是否已存在相关索引。表名:user, 字段名:name, 在索引中的位置:1
2019-10-24 07:36:20 35965 [Note] show index from user where Column_name ='name' and Seq_in_index =1 
2019-10-24 07:36:20 35965 [Note] 第9步：开始输出表user索引优化建议: 
2019-10-24 07:36:20 35965 [Note] Create_Index_SQL：alter table user add index idx_name(name) 
2019-10-24 07:36:20 35965 [Note] 第10步: SQLAdvisor结束!


也可配置文件传参调用    
[root@SQLAdvisor sqladvisor]# cat sql.cnf 
[sqladvisor]
username=root
password=123
host=127.0.0.1
port=3306
dbname=test1
sqls=select * from user where name = 'lisea'
[root@SQLAdvisor sqladvisor]# ./sqladvisor -f sql.cnf -v 1