语句优化
不能把select字句写成select *
select * from t_user;
原因：
1、返回字段太多，结果集太大，会增加数据库的IO负担
2、数据库会先查询t_user表的表结构，依据表结构将select * 中的*替换成表的字段，再执行SQL语句
左原则
谨慎使用模糊查询
select name form t_user where name like '%J%'; //不会用到索引
select name form t_user where name like 'J%'; //会用到索引
联合索引
创建联合索引 KEY ute (uname,tel,email)
想用到索引必须要保证uname被用到才行，MySQL会根据左原则，即uname才能确定下一步的搜索方向，当没有uname时，MySQL只能去全纪录查找
用到索引的情况(无论uname顺序怎么样，只要用到就可以)
image.png
没有用到索引的情况
image.png
explain执行后的参数
image.png
id
select识别符。这是select的查询序列号
(1)id相同时，执行顺序由上至下
(2)如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
(3)id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
select_type
查询中每个select子句的类型
(1) SIMPLE(简单SELECT，不使用UNION或子查询等)
(2) PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)
(3) UNION(UNION中的第二个或后面的SELECT语句)
(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
(5) UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)
(6) SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)
(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)
(8) DERIVED(派生表的SELECT, FROM子句的子查询)
(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
table
显示这一步所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是别名
type
对表访问方式，表示MySQL在表中找到所需行的方式
常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）
ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
index: Full Index Scan，index与ALL区别为index类型只遍历索引树
range:只检索给定范围的行，使用一个索引来选择行
ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
possible_keys
MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）
如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX
key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）
不损失精确性的情况下，长度越短越好
ref
列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
rows
估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数
Extra
该列包含MySQL解决查询的详细信息,有以下几种情况：
Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by
Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”
对order by排序的字段设置索引
少用is null 和 is not null
//不好的写法
select name from t_user where create_time is not null;
//优化后
select name from t_user where create_time > 0;
给create_time设置了索引，由于索引是一个二叉树，而null没法排序，所以null值没法进入索引，进而导致MySQL跳过索引去执行语句。
少用 != 运算符
!=无法利用二叉树进行查询定位，会变成全表扫描
//不好的写法
select name from t_user where age != 18;
//优化后
select name from t_user where age < 18 and age > 18;
少用 or 运算符
or运算符之前的会用到索引，但是or之后的语句则会跳过索引进行全表扫描
//不好的写法
select name from t_user where age = 18 or age = 20;
//优化后
select name from t_user where age = 18;
union all
select name from t_user where age = 20;
避免条件语句中的数据类型转换
//不好的写法
select name from t_user where age = '18';
//优化后
select name from t_user where age = 18;
age 为int类型，如果等于一个字符串，数据库需要先转换成int类型，再比较，会增加MySQL的损耗，影响性能。
//若name为字符串类型
//不好的写法(不会用到索引)
select name from t_user where name = 123;
//优化后(能用到索引)
select name from t_user where name = '123';
在表达式左侧使用运算符或者函数都会让索引失效
//不好的写法
select name from t_user where age * 2 = '18';
//优化后
select name from t_user where age = 18 / 2;
//不好的写法
select name from t_user where year(birthday) >= 2019;
//优化后
select name from t_user where birthday >= '2019-01-01 00:00:00';
在表中一定要有主键 比如bigint
1、自增主键会让插入性能大大增加
2、对于二级索引来说，非常节省空间
3、没有主键索引的情况下，主从复制时，在raw格式下，从库延迟会非常大
配置参数优化
最大并发连接数
max_connections是MySQL的最大并发连接数，默认为151
MySQL允许的最大并发连接数上限是16384
//查看设置的并发连接数
show variables like 'max_connections';
//查看当前实际使用到的并发连接数
show status like 'max_used_connections';
数据库默认最大并发连接数151
优化：
设置的最大并发连接数是当前实际连接数的85%较为合适
设置：
Linux设置
max_connections=2000
image.png
image.png
image.png
MySQL为每个链接创建缓冲区，所以不要盲目上调最大连接数
1个连接数大概占用0.26M内存
优化请求堆栈
back_log存放执行请求的堆栈大小，默认为50
如果请求达到了最大并发连接数，MySQL不会拒绝后面的请求，而是会把后面的连接请求放到堆栈里，等有连接空闲了，再把堆栈中的请求分配处理。
优化
back_log设置为最大并发连接数的20%~30%
back_log=60
image.png
修改并发线程数
innodb_thread_concurrency：并发线程数，默认是0。 0为没有线程上限
线程多了，CPU会消耗去调度线程，影响性能。所以，并不是线程越多越好。
优化
并发线程数设置为CPU核心数的两倍
innodb_thread_concurrency=2
修改连接超时
连接默认时间为8小时，也就是若连接了数据库，你后面不用了，数据库也会在八小时后才自动断开连接，像这样连接长期不用却短时间内不销毁，会浪费很多资源。
优化
//单位为妙
wait_timeout=1800
修改InnoDB缓存大小
innodb_buffer_pool_size：InnoDB的缓存容量，默认是128M
优化
InnoDB的缓存容量可以设置为主机内存的70%~80%。（单独的数据库服务器）
innodb_buffer_pool_size=300M
慢查询日志
慢查询日志会把查询耗时超过规定时间的SQL语句记录下来
可以利用慢查询日志来定位分析性能瓶颈
//查看慢查询是否开启
show variables like 'slow_query%';
slow_query_log:设置慢查询日志的日志开启状态
long_query_time：设置查询的超时时间，单位秒
slow_query_log=ON
long_query_time=1
最终命令
max_connections=2000
back_log=60
innodb_thread_concurrency=2
wait_timeout=1800
innodb_buffer_pool_size=300M
slow_query_log=ON
long_query_time=1
image.png
sysbench压力测试
sysbench安装
#依赖安装
yum -y install make automake libtool pkgconfig libaio-devel
# For MySQL support, replace with mysql-devel on RHEL/CentOS 5
yum -y install mariadb-devel openssl-devel
# For PostgreSQL support
yum -y install postgresql-devel
#RHEL/CentOS
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench
命令格式
sysbench [options]... [testname] [command]
[options]连接信息参数
--mysql-host：IP地址
--mysql-port：端口号
--mysql-user：用户名
--mysql-password：密码
--oltp-test-mode：执行模式（simple:只测试查询 nontrx：无事务的增删改查测试，主要针对MyISAM引擎。complex:测试有事务的增删改查）
--oltp-tables-count：测试的表的数量
--oltp-table-size：测试表的记录数
--threads：并发的线程数
--time：测试执行的时间（秒）
--report-interval：生成报告单的时间间隔（秒）
[command] 命令参数
prepare：准备测试数据
run：执行测试
cleanup：清除测试数据
创建测试数据库
测试数据库名字只能为sbtest
image.png
准备测试数据
sysbench \
/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua \
--mysql-host=192.168.1.1 --mysql-port=3306 \
--mysql-user=root --mysql-password=root \
--oltp-tables-count=10 --oltp-table-size=100000 \
prepare
测试数据
sysbench \
/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua \
--mysql-host=192.168.1.1 --mysql-port=3306 \
--mysql-user=root --mysql-password=root \
--oltp-test-mode=complex --threads=10 \
--time=600 --report-interval=10 \
run >> /home/test_report.log
测试结果
image.png
transactions：事务操作
queries：查询操作
ignored errors：忽略的错误
网友评论