数据库学习——06—Mysql高级(基准测试、影响服务器性能因素、explain语句、索引优化)
程序员文章站
2022-06-01 08:51:35
...
1、基准测试
基准测试：简化版压力测试
是一种测量和评估软件性能指标的活动用于建立某个时刻的性能基准，以便当系统发生软硬件变化时重新进行基准测试以评估变化对性能的影响
基准测试是针对系统设置的一种压力测试
基准测试特点：直接、简单、易于比较，用于评估服务器的处理能力
可能不关心业务逻辑，所使用的查询和业务的真实性可以和业务环境没关系
压力测试：采用真实的业务数据进行基准测试
目的：
1、建立MySQL服务器的性能基准线，确定当前MySQL服务器运行情况
2、模拟比当前系统更高的负载，以找出系统的扩展瓶颈 QPS：单位时间内处理的查询的数量 TPS：单位时间内处理的事务的数量
3、测试不同的硬件软件和系统配置
4、证明新的硬件设备是否配置正确
对整个系统进行基准测试的优点：
1、能够测试整个系统的性能，包括web服务器缓存，数据库等
2、能反映出系统中各个组件接口间的性能问题体现真实性能状况
缺点：设计复杂，耗费时间长
对MySQL进行基准测试的优点：
1、设计简单，耗费时间短
缺点：无法全面了解整个系统的性能基准线
MySQL基准测试的工具mysqlslap
测试工具的常用参数命名：
'''
--auto-generate-sql 由系统自动生成SQL脚本进行测试
--auto-generate-sql-add-autoincrement 在生成的表中增加自增ID
--auto-generate-sql-load-type 指定测试中使用的查询类型
--auto-generate-sql-write-number 指定初始化数据时生成的数据量
--concurrency 指定并发线程的数量
--engine 指定要测试表的存储引擎,可以用逗号分割多个存储引擎
--no-drop 指定不清理测试数据
--iterations 指定测试运行的次数
--number-of-queries 指定每一个线程执行的查询数量
--debug-info 指定输出额外的内存及CPU统计信息
--number-int-cols 指定测试表中包含的INT类型列的数量
--number-char-cols 指定测试表中包含的varchar类型的数量
--create-schema 指定了用于执行测试的数据库的名字
--query 用于指定自定义SQL的脚本
--only-print 并不运行测试脚本,而是把生成的脚本打印出来
实例：mysqlslap --concurrency=1,50,100,200 --iterations=3 --number-int-cols=5 --number-char- cols=5 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=myisam,innodb
--number-of-queries=10 --create-schema=test
'''
2、影响服务器性能因素
'''
影响服务器的性能：
1、服务器硬件
2、服务器的操作系统
3、数据库存储引擎的选择
4、数据库参数配置
5、数据库结构设计和SQL语句
SQL性能下降原因：
1、查询语句写的不好
2、索引失效
3、关联查询太多join（设计缺陷）
4、服务器调优及各个参数设置
SQL语句： SQL加载的顺序：
select distinct from <left_table>
<select_list> on <join_condition>
from <join_type> join <right_table>
<left_table> where <where_condition>
join <right_table>on<join_codition> group by <group_by_list>
where having <having_condition>
<where_condition> select distinct <select_list>
group by order by <order_by_condition>
<group_by_list> limit<limit number>
having
<having_condition>
order by
<order_by_condition>
limit<limit number>
mysql常见瓶颈
1、CPU：CPU在饱和的时候一般发生在数据装入内存活从磁盘读取数据的时候
2、IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
3、服务器硬件的性能瓶颈
'''
3、explain语句
'''
explain
使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或是表结构的性能瓶颈。
1、表的读取顺序
2、数据读取操作的操作类型
3、那些索引被实际使用
4、那些索引可以使用
5、表之间的引用
6、每张表有多少行被优化器查询
explain+SQL语句
explain select * from 表名称;
举例：
mysql> explain select * from test_innodb_2;
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | test_innodb_2 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
explain字段分析：
字段-id----表的读取顺序
id相同情况下，执行顺序由上至下
id不同情况下，id值越大越先被执行
字段-select_type--数据读取操作的操作类型
1.simple 简单的select查询,查询中不包含子查询或者union
2.primary 查询中若包含任何复杂的子部分,最外层查询则被标记。就是最后加载的那个
3.subquery 在select或where列表中包含了子查询
4.union 若第二个select出现在union之后,则被标记为union
5.union result 从union表获取结果的select
字段-table---显示关于哪张表的这一行的数据时
字段-type---中查询效率最好到最差的效果排序依次是：(重点优化对象)
system > const > eq_ref > ref > range > index > All
系统表 > 常量 > 唯一索引> 非唯一索引>范围 >字段索引>全表扫描(select *)
字段-possible_keys---显示可能应用在这张表中的索引，一个或多个（不确定是否）
示例：explain select * from 表名称;
show index from 表名称; # 查询表的索引
字段-key----实际使用的索引，如果为null，则没有索引（这个参数决定表的实际索引）（重点优化对象）
字段-key_len----表示索引中使用的子节数，可通过该列计算查询中使用的索引长度。在不损失精确性的情况下，长度越短越好
key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的
key_len索引长度的计算方法：
-varchar(24)变长字段且允许null
24*(Character Set:utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
-varchar(10)变长字段且不允许null
10*(Character Set:utf8=3,gbk=2,latin1=1)+2(变长字段)
-char(10)固定字段且允许null
10*(Character Set:utf8=3,gbk=2,latin1=1)+1(NULL)
-char(10)固定字段且不允许null
10*(Character Set:utf8=3,gbk=2,latin1=1)
字段-ref---显示索引哪一列被使用到了
字段-rows---根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要的行数
字段-Extra---包含不适合在其他列中显示但十分重要的额外信息（重点优化对象）
1、Using filesort 文件内的排序（一般出现在order by文件排序，需要优化，有问题）
2、Using temporary 使用临时表进行查询（一般出现在order by和group by排序，必须优化）
3、Using index 使用索引查询，优于全表扫描
4、Using where 使用了where语句过滤
5、Using join buffer 使用了连接缓存
6、impossible where 不可能的条件，where子句的值总是false（查询不出符合条件的数据）
'''
4、索引优化
'''
索引优化案例：
一、单表优化
1.建表
create table article(
id int unsigned not null primary key auto_increment,
author_id int unsigned not null,
cate_id int unsigned not null,
views int unsigned not null,
comments int unsigned not null,
title varchar(255),
content text not null,
)charset=utf8; -- 默认引擎是Innodb
2.插入数据
insert into article(`author_id`,`cate_id`,`views`,`comments`,`title`,`content`) --注意不是引号
values(1,1,1,1,'1','1'), --引号
(2,2,2,2,'2','2'),
(3,3,3,3,'3','3');
-- 查询cate_id为3且comments大于1的情况下，views最多的author_id
select id,author_id from article where cate_id=3 and comments > 1 order by views desc limit 1; --排序后查询一条数据
mysql> explain select id,author_id from article where cate_id=3 and comments > 1 order by views desc limit 1;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | article | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
--此时因为order by语句在Extra项目上出现了Using filesort，则需要优化。
--开始优化
explain select id,author_id from article where cate_id=3 and comments > 1 order by views desc limit 1;
create index idx_article_ccv on article(cate_id,comments,views); --三个字段创建索引
----------------------------------------------------------------------------------------------
mysql> show index from article;
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| article | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES |
| article | 1 | idx_article_ccv | 1 | cate_id | A | NULL | NULL | NULL | | BTREE | | | YES |
| article | 1 | idx_article_ccv | 2 | comments | A | NULL | NULL | NULL | | BTREE | | | YES |
| article | 1 | idx_article_ccv | 3 | views | A | NULL | NULL | NULL | | BTREE | | | YES |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
4 rows in set (0.15 sec)
mysql> explain select id,author_id from article where cate_id=3 and comments > 1 order by views desc limit 1;
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
| 1 | SIMPLE | article | NULL | range | idx_article_ccv | idx_article_ccv | 8 | NULL | 1 | 100.00 | Using index condition; Using filesort |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)
-----------------------------------------------------------------------------------------------
(type | possible_keys | key 三个字段已经优化)
--效率虽好，但是不能修改题目
explain select id,author_id from article where cate_id=3 and comments = 1 order by views desc limit 1;
mysql> explain select id,author_id from article where cate_id=3 and comments = 1 order by views desc limit 1;
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------+------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------+------+----------+----------------------------------+
| 1 | SIMPLE | article | NULL | ref | idx_article_ccv | idx_article_ccv | 8 | const,const | 1 | 100.00 | Using where; Backward index scan |
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)
--继续优化索引
drop index idx_article_ccv on article; --先删除之前的索引
create index idx_article_cv on article(cate_id,views); -创建新的索引 （如果创建views的单值索引，优化效果相同，但是type字段是index，劣于ref类型，综合考虑，）
show index from article; --优化完成
--------------------------------------------------------------------
mysql> explain select id,author_id from article where cate_id=3 and comments > 1 order by views desc limit 1;
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+----------------------------------+
| 1 | SIMPLE | article | NULL | ref | idx_article_cv | idx_article_cv | 4 | const | 1 | 33.33 | Using where; Backward index scan |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)
（Extra 字段中的Using filesort消失了，优化完成）
---------------------------------------------------------------------------------------------------
--继续优化索引
drop index idx_article_cv on article; --先删除之前的索引
create index idx_article_v on article(views); -创建views的单值索引，优化效果相同，但是type字段是index，劣于ref类型，综合考虑，选择双值索引
show index from article; --优化完成
'''
'''
二、双表优化
--创建表
create table class(
id int unsigned not null primary key auto_increment,
card int unsigned not null
)charset=utf8;
create table book(
bookid int unsigned not null primary key auto_increment,
card int unsigned not null
)charset=utf8;
--插入数据
(数据库中随机小数生成语句是 select rand(); )
(选取整数部分语句 select floor(rand()*100); )
insert into class(card) values(floor(rand()*100));
insert into book(card) values(floor(rand()*100));
--左连接 type all Extra 显示join buffer(需要优化)
explain select * from class left join book on class.card = book.card;
--开始优化
create index idx_book_card on book(card);
show index from book;
--type 变成ref Extra变为Using index
explain select * from class left join book on class.card = book.card;
drop index idx_book_card on book; ---先删除 book索引
--建立
create index idx_class_card on class(card);
show index from class;
--优化
--左连接往右表加索引
--右连接往左表加索引
--inner join
create index idx_class_card on class(card);
create index idx_book_card on book(card);
'''