mysql优化

于 2021-04-16 09:05:02 发布
阅读量167 收藏 1
点赞数
分类专栏： mysql 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/C18854805113/article/details/115748018
版权

安装

添加mysql组和用户

  • npm

http://repo.mysql.com/
https://blog.csdn.net/liumm0000/article/details/18841197

  • tar

https://repo.huaweicloud.com/mysql/Downloads/MySQL-5.7/
https://blog.csdn.net/z13615480737/article/details/80019881

逻辑架构

https://blog.csdn.net/wangfeijiu/article/details/112454405

存储引擎

show engines;

show variables like “%storange_engine%”;

MyISAM和InnoDB对比

对比MyISAMInnoDB
主外键不支持支持
事务不支持支持
行表锁表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作行锁，操作时只锁定某一行，不对其他行有影响，适合高并发的操作
缓存只缓存索引，不缓存真实数据不仅缓存索引还要缓存真实数据，对内存的要求较高，而且内存大小对性能有决定性影响
表空间小大
关注点性能事务
默认安装YY

索引优化

一文搞懂MySQL索引（清晰明了）

sql joins

SQL JOINS

/* 1 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key;

/* 2 */
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key;

/* 3 */
SELECT <select_list> FROM TableA A INNER JOIN TableB B ON A.Key = B.Key;

/* 4 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL;

/* 5 */
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL;

/* 6 */
SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key;
/* MySQL不支持FULL OUTER JOIN这种语法 可以改成 1+2 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key
UNION
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key;

/* 7 */
SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL OR B.Key IS NULL;
/* MySQL不支持FULL OUTER JOIN这种语法 可以改成 4+5 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL;
UNION
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL;

索引

定义

索引是帮助MySQL高效获取数据的数据结构

索引是排好序的快速查找数据结构

索引会影响到MySQL查找(WHERE的查询条件)和排序(ORDER BY)两大功能！

除了数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

我们平时所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种数据结构的索引之外，还有哈希索引（Hash Index）等。

优劣势

优势
  • 提高数据检索的效率，降低数据库的IO成本
  • 通过索引列对数据进行排序，降低数据排序的成本降低了cpu的消耗
劣势
  • 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占空间的
  • 虽然索引极大地提高了查询速度，却同时会降低更新表的速度，如对表进行insert，update和delete。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引的字段，都会调整因为更新所带来的的键值变化后的索引信息
  • 索引知识提高效率的一个因素，如果你的MySQL有大量数据量的表，就需要花时间去研究建立最优秀的索引，或优化查询

索引分类

单值索引

即一个索引只包含单个列，一个表可以有多个单列索引

唯一索引

索引列的值必须唯一，但允许为空

符合索引

即一个索引包含多个列

基本语法

/* 基本语法 */

/* 1、创建索引 [UNIQUE]可以省略*/
/* 如果只写一个字段就是单值索引，写多个字段就是复合索引 */
CREATE [UNIQUE] INDEX indexName ON tabName(columnName(length));
/* alter创建 */
ALTER tabName ADD [UNIQUE] INDEX [indexName] ON (columnName(length))  
/* 2、删除索引 */
DROP INDEX [indexName] ON tabName;

/* 3、查看索引 */
/* 加上\G就可以以列的形式查看了 不加\G就是以表的形式查看 */
SHOW INDEX FROM tabName \G;

/* 1、该语句添加一个主键，这意味着索引值必须是唯一的，并且不能为NULL */
ALTER TABLE tabName ADD PRIMARY KEY(column_list);

/* 2、该语句创建索引的键值必须是唯一的(除了NULL之外，NULL可能会出现多次) */
ALTER TABLE tabName ADD UNIQUE indexName(column_list);

/* 3、该语句创建普通索引，索引值可以出现多次 */
ALTER TABLE tabName ADD INDEX indexName(column_list);

/* 4、该语句指定了索引为FULLTEXT，用于全文检索 */
ALTER TABLE tabName ADD FULLTEXT indexName(column_list);

索引结构

深入理解MySQL索引底层原理

https://zhuanlan.zhihu.com/p/113917726

索引创建准则

  • 主键自动创建唯一索引
  • 频繁作为查询条件的字段应该创建索引
  • 查询中与其他关联的字段，外键关系建立索引
  • 频繁更新的字段不适合创建索引： 因为每次更新不单单是更新了记录还会更新索引
  • where条件用不到的字段不创建索引
  • 查询中排序的字段，排序的字段若是通过索引去访问将大大提高排序速度
  • 查询中统计或分组字段

不适合创建：

  • 表记录太少

  • 集成增删改的表

  • 数据重复且分布平均的字段

    索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高。

性能分析

常见瓶颈

  • CPU：Cpu在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时
  • IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
  • 服务器硬件的性能瓶颈：top，free，iostat和vmstat来查看系统的性能状态

EXPLAIN

SQL的执行计划，使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的。

  • id：表的读取顺序。小标驱动大表
  • select_type：数据读取操作的操作类型。
  • possible_keys：哪些索引可以使用。
  • key：哪些索引被实际使用。
  • ref：表之间的引用。
  • rows：每张表有多少行被优化器查询。

  1. id

    • id相同，执行顺序由上至下。
    • id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。
    • id相同不同，同时存在。永远是id大的优先级最高，id相等的时候顺序执行。

  2. select_type

    • SIMPLE：简单的SELECT查询，查询中不包含子查询或者UNION。
    • PRIMARY：查询中如果包含任何复杂的子部分，最外层查询则被标记为PRIMARY。
    • SUBQUERY：在SELECT或者WHERE子句中包含了子查询。
    • DERIVED：在FROM子句中包含的子查询被标记为DERIVED(衍生)，MySQL会递归执行这些子查询，把结果放在临时表中。
    • UNION：如果第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVED。
    • UNION RESULT：从UNION表获取结果的SELECT。

  3. table

    数据是关于那张表的

  4. type

    访问类型排列
    从最好到最差依次是：system>const>eq_ref>ref>range>index>ALL

    一般来说，得保证查询至少达到range级别，最好达到ref

    • system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计。
    • const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转化为一个常量。
    • eq_ref：唯一性索引扫描，读取本表中和关联表表中的每行组合成的一行，查出来只有一条记录。除 了 system 和const 类型之外, 这是最好的联接类型。
    • ref：非唯一性索引扫描，返回本表和关联表某个值匹配的所有行，查出来有多条记录。
    • range：只检索给定范围的行，一般就是在WHERE语句中出现了BETWEEN、< >、in等的查询。这种范围扫描索引比全表扫描要好，因为它只需要开始于索引树的某一点，而结束于另一点，不用扫描全部索引。
    • index：Full Index Scan，全索引扫描，index和ALL的区别为index类型只遍历索引树。也就是说虽然ALL和index都是读全表，但是index是从索引中读的，ALL是从磁盘中读取的。
    • ALL：Full Table Scan，没有用到索引，全表扫描。

  5. possible_keys

    显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段若存在索引，则改索引将被列出，但不一定被查询实际使用

  6. key

    实际使用的索引，如果为null，则没有使用索引，查询中若使用了覆盖索引，则改索引进出现在key列表中

  7. key_len

    表示索引中使用的字节数，可通过该列计算查询中使用的索引长度。在不损失精确性的情况下，长度越短越好

    key_len显示的值为索引字段的最大可能长度，并非实际长度，即key_len是根据表定义计算而得，不是通过表内检索出的

  8. ref

    显示索引的那一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

  9. rows

    根据表统计信息及索引选用情况，大致估算出找到所需的记录需要读取的行数。

  10. Extra

    包含不适合在其他列中显示但十分重要的额外信息。

    • Using filesort：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作成为"文件内排序"。
    • Using temporary：使用了临时表保存中间结果，MySQL在対查询结果排序时使用了临时表。常见于排序order by和分组查询group by。临时表対系统性能损耗很大。
    • Using index：表示相应的SELECT操作中使用了覆盖索引，避免访问了表的数据行，效率不错！如果同时出现Using where，表示索引被用来执行索引键值的查找；如果没有同时出现Using where，表明索引用来读取数据而非执行查找动作。
    • Using where：表明使用了WHERE过滤。
    • Using join buffer：使用了连接缓存。
    • impossible where：WHERE子句的值总是false，不能用来获取任何元组。

单表

表

CREATE TABLE IF NOT EXISTS `article`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT (10) UNSIGNED NOT NULL,
`category_id` INT(10) UNSIGNED NOT NULL , 
`views` INT(10) UNSIGNED NOT NULL , 
`comments` INT(10) UNSIGNED NOT NULL,
`title` VARBINARY(255) NOT NULL,
`content` TEXT NOT NULL
);

场景

查询category_id为1且comments大于1的情况下，views最多的article_id。

demo

当comments > 1的时候order by排序views字段索引就用不上，但是当comments = 1的时候order by排序views字段索引就可以用。所以，范围之后的索引会失效。

双表

表

CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
 
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));

场景

可以看到在右表创建索引后，type变为了red，rows也优化了

这是由左连接的特性决定的，left join 条件用于确定如何从右表搜索行，左边一定都有左连接将索引创建在右表上更合适，右连接将索引创建在左表上更合适。

三表

表

CREATE TABLE IF NOT EXISTS `phone`(
`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
)ENGINE = INNODB;

INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));

场景

尽可能减少join语句中的NestedLoop（嵌套循环）的循环次数

永远用小结果集驱动大的结果集

索引失效

  • 全值匹配

  • 最佳左前缀法则。

  • 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描。

  • 索引中范围条件右边的字段会全部失效。

  • 尽量使用覆盖索引（只访问索引的查询，索引列和查询列一致），减少SELECT *。

  • MySQL在使用!=或者<>的时候无法使用索引会导致全表扫描。

  • is null、is not null也无法使用索引。

  • like以通配符开头%abc索引失效会变成全表扫描。

    如何解决两边%索引失效 覆盖索引 ？？？

  • 字符串不加单引号索引失效。

  • 少用or，用它来连接时会索引失效。

最佳左前缀法则

最佳左前缀法则：如果索引是多字段的复合索引，要遵守最佳左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的字段。

case

假设index(a,b,c)

where索引是否被引用
where a = 3Y,使用到a
where a= 2 and b= 5Y,使用到a,b
where a= 2 and b= 5 and c = 4Y,使用到a,b,c
where a= 2 或者 b= 5 and c=4 或者 where c=4N
where a= 3 and c= 5使用a，但无法使用c，b断了
where a= 2 and b > 5 and c= 5使用到a和b，c不能在用在范围之后，b断了
where a= 2 and b like “kk%” and c = 4Y,使用到a，b，c
where a= 2 and b like “%kk” and c = 4Y,使用到a
where a= 2 and b like “%kk%” and c = 4Y,使用到a
where a= 2 and b like “k%kk%” and c = 4Y,使用到a，b，c

分析

CREATE INDEX index_test_c1234 ON `test`(`c1`,`c2`,`c3`,`c4`);

/* 1.全值匹配*/
EXPLAIN SELECT * FROM `test` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c3` = 'a3' AND `c4` = 'a4';

/* 2.用到索引c1 c2 c3 c4全字段 MySQL的查询优化器会优化SQL语句的顺序*/
EXPLAIN SELECT * FROM `test` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c4` = 'a4' AND `c3` = 'a3';

/* 3.用到索引c1 c2 c3 c4全字段 MySQL的查询优化器会优化SQL语句的顺序*/
EXPLAIN SELECT * FROM `test` WHERE `c4` = 'a4' AND `c3` = 'a3' AND `c2` = 'a2' AND `c1` = 'a1';

/* 4.用到索引c1 c2 c3字段，c4字段失效，范围之后全失效 */
EXPLAIN SELECT * FROM `test` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c3` > 'a3' AND `c4` = 'a4';

/* 5.用到索引c1 c2 c3 c4全字段 MySQL的查询优化器会优化SQL语句的顺序*/
EXPLAIN SELECT * FROM `test` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c4` > 'a4' AND `c3` = 'a3';

/* 6.用到了索引c1 c2 c3三个字段, c1和c2两个字段用于查找,  c3字段用于排序了但是没有统计到key_len中，c4字段失效
*/
EXPLAIN SELECT * FROM `test` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c4` = 'a4' ORDER BY `c3`;

/* 7.用到了索引c1 c2 c3三个字段，c1和c2两个字段用于查找, c3字段用于排序了但是没有统计到key_len中*/
EXPLAIN SELECT * FROM `test` WHERE `c1` = 'a1' AND `c2` = 'a2' ORDER BY `c3`;

/* 8.用到了索引c1 c2两个字段，c4失效，c1和c2两个字段用于查找，c4字段排序产生了Using filesort说明排序没有用到c4字段 
*/
EXPLAIN SELECT * FROM `test` WHERE `c1` = 'a1' AND `c2` = 'a2' ORDER BY `c4`;

/* 9.用到了索引c1 c2 c3三个字段，c1用于查找，c2和c3用于排序 */
EXPLAIN SELECT * FROM `test` WHERE `c1` = 'a1' AND `c5` = 'a5' ORDER BY `c2`, `c3`;

/* 10.用到了c1一个字段，c1用于查找，c3和c2两个字段索引失效，产生了Using filesort */
EXPLAIN SELECT * FROM `test` WHERE `c1` = 'a1' AND `c5` = 'a5' ORDER BY `c3`, `c2`;

/* 11.用到了c1 c2 c3三个字段，c1 c2用于查找，c2 c3用于排序 */
EXPLAIN SELECT * FROM `test` WHERE `c1` = 'a1' AND  `c2` = 'a2' ORDER BY c2, c3;

/* 12.用到了c1 c2 c3三个字段，c1 c2用于查找，c2 c3用于排序 */
EXPLAIN SELECT * FROM `test` WHERE `c1` = 'a1' AND  `c2` = 'a2' AND `c5` = 'a5' ORDER BY c2, c3;

/* 13.用到了c1 c2 c3三个字段，c1 c2用于查找，c2 c3用于排序 没有产生Using filesort 
   因为之前c2这个字段已经确定了是'a2'了，这是一个常量，再去ORDER BY c3,c2 这时候c2已经不用排序了！
   所以没有产生Using filesort 和(10)对比
*/
EXPLAIN SELECT * FROM `test` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c5` = 'a5' ORDER BY c3, c2;



/* GROUP BY 表面上是叫做分组，但是分组之前必排序。 */

/* 14.用到c1 c2 c3三个字段，c1用于查找，c2 c3用于排序，c4失效 */
EXPLAIN SELECT * FROM `test` WHERE `c1` = 'a1' AND `c4` = 'a4' GROUP BY `c2`,`c3`;

/* 15.用到c1这一个字段，c4失效，c2和c3排序失效产生了Using filesort Using temporary */
EXPLAIN SELECT * FROM `test` WHERE `c1` = 'a1' AND `c4` = 'a4' GROUP BY `c3`,`c2`;

定值、范围还是排序，一般order by 是给个范围，group by 基本上都需要进行排序，会有临时表产生

总结

  • 对于单值索引，尽量选择昂前query过滤性更好的索引
  • 在选择组合索引的时候，当前query中过滤性最好的字段在索引字段排序中，位置越靠前越好
  • 在选择组合索引的时候，尽量选择可以能够包含单签query中的where字句中更多的字段的索引
  • 尽可能的通过分析统计信息和调整query的写法来达到选择合适索引的目的

查询截取分析

如何分析慢SQL

观察，至少跑一段时间，查看生产的慢SQL情况-->开启慢查询日志，设置阈值，比如超过5秒的就是慢SQL，将其抓取-->explain+慢SQL分析-->show profile-->运维或者DBA进行sql数据库服务器的参数调优

总结

慢查询的开启并捕获-->explain+慢SQL分析-->show profile 查询在MySQL服务器里面执行细节和生命周期情况-->SQL数据库服务器的参数调优

查询优化

小数据集驱动大数据集

/*
	可以理解为建立5次连接，每次查询1000次
*/
for(int i=0;i<5;i++){
	for(int j=0;j<1000;j++){
	
	}
}
/*
	可以理解为建立1000次连接，每次查询5次
*/
for(int i=0;i<1000;i++){
	for(int j=0;j<5;j++){
	
	}
}

SELECT * FROM `A` WHERE `id` IN (SELECT `id` FROM `B`)
等价于： 
for select id from B
for select * from A where A.id = B.id
当B表的数据集小于A的数据集时，用in优于exists

-------------------------------------------

SELECT * FROM `A` WHERE EXISTS (SELECT 1 FROM `B` WHERE `B`.id = `A`.id);
等价于：
for select * from A
for select * from B where A.id = B.id
当A表的数据集小于B的数据集时，用exists优于in
  • EXISTS(subquery)子查询只返回true或者false，因此子查询中的SELECT *可以是SELECT 1 OR SELECT X，它们并没有区别。
  • EXISTS(subquery)子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担心效率问题，可进行实际检验以确定是否有效率问题。
  • EXISTS(subquery)子查询往往也可以用条件表达式，其他子查询或者JOIN替代，何种最优需要具体问题具体分析。

order by关键字优化

提高ORDER BY排序的速度：

  • ORDER BY时使用SELECT *是大忌，查什么字段就写什么字段，这点非常重要。在这里的影响是：
    • 当查询的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时，会使用单路排序算法，否则使用多路排序算法。
    • 两种排序算法的数据都有可能超出sort_buffer缓冲区的容量，超出之后，会创建tmp临时文件进行合并排序，导致多次IO，但是单路排序算法的风险会更大一些，所以要增大sort_buffer_size参数的设置。
  • 尝试提高sort_buffer_size：不管使用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的。
  • 尝试提高max_length_for_sort_data：提高这个参数，会增加用单路排序算法的概率。但是如果设置的太高，数据总容量sort_buffer_size的概率就增大，明显症状是高的磁盘IO活动和低的处理器使用率。

KEY a_b_c(a,b,c)

order by 能使用所用最左前缀

- order by a 
- order by a,b
- order by a,b,c 
- order by a desc,b desc,c desc

如果where使用索引的最左前缀定义为常量，则order by能使用索引

- where a = const order by b,c
- where a = const and b = const order by c
- where a = const and b > const order by b,c

不能使用索引进行排序

- order by a ASC,b DESC, c DESC /*排序不一致*/
- where g = const order by b,c /*丢失索引a*/
- where a = const order by c /*丢失索引b*/
- where a = const order by a,d /*d不是索部分*/
- where a in (...) order by b,c /*对于排序来说，多个相等条件也是范围查询*/

group by关键字优化

  • GROUP BY实质是先排序后进行分组，遵照索引建的最佳左前缀。
  • 当无法使用索引列时，会使用Using filesort进行排序，增大max_length_for_sort_data参数的设置和增大sort_buffer_size参数的设置，会提高性能。
  • WHERE执行顺序高于HAVING，能写在WHERE限定条件里的就不要写在HAVING中了。

慢查询日志

定义

​ MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。

默认情况下慢查询日志是关闭的，如果不是调优需要的话，一般不建议启动该参数，开启会带来一定的性能影响

show variables like “%slow_query_log%”;

当前数据库有效，重启后失效

set global slow_query_log = 1;

慢查询时间，等于不会被记录

show variables like “long_query_time%”;

设置阈值为三秒

set global long_query_time = 3;

慢sql记录条数

show global status like “%Slow_queries%”;

日志分析工具

# 得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log
 
# 得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log
 
# 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow.log

# 另外建议使用这些命令时结合|和more使用，否则出现爆屏的情况
mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log | more

批量数据插入

创建函数

# 产生随机字符串
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
    DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwsyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    DECLARE return_str VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
    SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
    SET i = i + 1;
    END WHILE;
    RETURN return_str;
END $$

# 随机部门编号
DELIMITER $$
CREATE FUNCTION rand_num() RETURNS INT(5)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET i = FLOOR(100 + RAND() * 10);
    RETURN i;
END $$

创建存储过程

DELIMITER $$
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
    SET i = i + 1;
    INSERT INTO dept(deptno,dname,loc) VALUES((START + i),rand_string(10),rand_string(8));
    UNTIL i = max_num
    END REPEAT;
    COMMIT;
END $$

DELIMITER $$
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
    SET i = i + 1;
    INSERT INTO emp(empno,ename,job,mgr,hiredata,sal,comm,deptno) VALUES((START + i),rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
    UNTIL i = max_num
    END REPEAT;
    COMMIT;
END $$

录入

DELIMITER ;
CALL insert_dept(100,10);

CALL insert_emp(100001,500000);

show profile

是MySQL提供可以用来分析当前会话语句的执行的资源消耗情况。可以用于SQL的调优的测量

默认情况下是关闭的，并保存15次的运行结果

SHOW VARIABLES LIKE ‘profiling’;

开启

SET profiling=ON;

查看最近执行的slq

SHOW PROFILES;

诊断sql

SHOW PROFILE cpu,block io FOR QUERY Query_ID;

Show Profile查询参数备注：

  • ALL：显示所有的开销信息。
  • BLOCK IO：显示块IO相关开销（通用）。
  • CONTEXT SWITCHES：上下文切换相关开销。
  • CPU：显示CPU相关开销信息（通用）。
  • IPC：显示发送和接收相关开销信息。
  • MEMORY：显示内存相关开销信息。
  • PAGE FAULTS：显示页面错误相关开销信息。
  • SOURCE：显示和Source_function。
  • SWAPS：显示交换次数相关开销的信息。

Show Profile查询列表，日常开发需要注意的结论：

  • converting HEAP to MyISAM：查询结果太大，内存都不够用了，往磁盘上搬了。
  • Creating tmp table：创建临时表（拷贝数据到临时表，用完再删除），非常耗费数据库性能。
  • Copying to tmp table on disk：把内存中的临时表复制到磁盘，危险！！！
  • locked：死锁。

全局查询日志

不可在生产环境使用

set global general_log = 1;

set global log_output = “table”;

select * from mysql.log_output;

锁理论概述

分类

  • 从对数据操作的类型（读、写）

    • 读锁（共享锁）： 针对同一份数据，多个读操作可以同时进行而不会互相影响
    • 写锁（排它锁）：当前写操作没有完成之前，它会阻断其他写锁和读锁

  • 从对数据操作的粒度

    • 表锁
    • 行锁

表锁

特点

偏向myISAM存储引擎，开销小，加锁快，无死锁，锁定粒度大，发生所冲突的概率最高，并发度最低

读锁案例

写锁案例

MyISAM在执行select前，会自动给涉及到的所有的表加读锁，在执行增删改操作之前，会自动给涉及到的表加写锁

MySQL的表级锁两种模式：

  • 表共享读锁（Table Read Lock）
  • 表独占写锁（Table Write Lock）
锁类型可否兼容读锁写锁
读锁是是否
写锁是否否
  • 对于MyISAM表的读操作（加读锁），不会阻塞其他进程对同一张表的读请求，但会阻塞对同一张表的学请求。只有当读锁释放后，才会执行凄然进程的写操作。
  • 对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当前写锁释放后，才会执行其他进程的读写操作。

表锁分析

SHOW STATUS LIKE ‘table%’;

可以通过Table_locks_immediate和Table_locks_waited状态变量来分析系统上的表锁定。具体说明如下：

Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1。

Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在较严重的表级锁争用情况。

MyISAM的读写锁调度是写优先，这也是MyISAM不适合做写为主表的引擎。因为写锁后，其他线程不能进行任何操作，大量的写操作会使查询很难得到锁，从而造成永远阻塞。

行锁

特点

  • 偏向于InnoDB引擎，开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率最低，并发度最高。

  • InnoDB与MyISAM的最大不同有两点：一是支出事务

    （TRANSACTION）；二是采用了行级锁

事务相关

事务ACID

原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全部执行，要么全都不执行

一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态，这意味着所有的数据规则都必须应用于事务的修改，以保持数据的完整新；事物结束时，所有的内部数据结构（如B树索引或双向链表）也都是必须正确的。

隔离性（Lsolation）：数据库系统提供的一套隔离机制，保证事务在不受外部并发操作的影响的“独立”环境执行，这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。

持久性（Durable）：事务完成之后，他对于数据的修改时永久性的，计时出现系统故障也能够保持。

并发事务处理带来的问题

  • 更新丢失（Lost update）

    当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题―—最后的更新覆盖了由其他事务所做的更新。

    例如，两个程序员修改同一java文件。每程序员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖前一个程序员所做的更改。

    如果在一个程序员完成并提交事务之前，另一个程序员不能访问同一文件，则可避免此问题。

  • 脏读（Dirty Reads）

    一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态;这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做”脏读”。

    一句话:事务A读取到了事务B玊修改但尚未提交的的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求。

  • 不可重复读（Non-Repeatable Reads）

    一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了!这种现象就叫做“不可重复读”。

    一句话:事务A读取到了事务B已经提交的修改数据，不符合隔离性

  • 幻毒（Phantom Reads）

    一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读“

    一句话:事务A读取到了事务B体提交的新增数据，不符合隔离性。

    幻读和脏读有点类似，脏读是事务B里面修改了数据，幻读是事务B里面新增了数据。

事务隔离级别

“脏读”、“不可重复读”、“幻读”，其实都是数据读一致性问题，必须由数据库提供一定的事务隔离机制来解决。

隔离级别读数据一致性脏读不可重复读幻读
未提交读（Read uncommitted）最低级别，只能保证不读取物理上损坏的数据是是是
已提交读（Read committed）语句级否是是
可重复读（Repeatable read）事务级否否否
可序列化（Serializable）最高级别，事务级否否否

数据库的事务隔离级别越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离本质实际上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务合理程度的要求也是不一样的，比如许多应用对“不可重复读”、”幻读“并不敏感，可能更关心数据并发访问的能力。

查看数据库事务隔离级别：

show variables like “tx_isolation”;

行锁案例

改为手动提交

SET autocommit=0;

操作同一数据和不同数据

索引失效行锁升级为表锁

行锁分析

SHOW STATUS LIKE ‘innodb_row_lock%’;

  • Innodb_row_lock_current_waits：当前正在等待锁定的数量。
  • Innodb_row_lock_time：从系统启动到现在锁定总时间长度（重要）。
  • Innodb_row_lock_time_avg：每次等待所花的平均时间（重要）。
  • Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间。
  • Innodb_row_lock_waits：系统启动后到现在总共等待的次数（重要）。

间隙锁的危害

当我们使用范围条件而不是相等条件检索数据，并请求共享或者排它锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”

InnoDB也会对这个间隙加锁，这种随机值就是所谓的间隙锁（Next-key锁）

因为query执行过程中通过范围查找的话，它会锁定整个范围内所有的索引键值，即使这个键值不存在。

间隙锁有一个比较致命的缺点，即使某些不存在的键值也会被无辜锁定，二造成锁定的时候无法插入锁定范围内的任何数据。在某些场景下这可能对性能造成很大的危害

锁定一行

select … for update 锁定某一行后，其他的操作会被阻塞，直到锁定行的会话提交commit

总结

InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现昂面所带来的的性能损耗可能比表级锁会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统的并发量较高时，InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。但是，InnoDB的行级锁同样也有脆弱的一面，当我们使用不当时，可能会让InnoDB的整体性能表现不仅不如MyISAM，甚至可能更差。

优化

  1. 尽可能的让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
  2. 合理设计索引，尽可能的缩小锁的范围
  3. 尽可能较少检索条件，避免间隙锁
  4. 尽量控制事务代销，减少锁定资源量和时间长度
  5. 尽可能低级别事务隔离

页锁

开锁和加锁时间结余表锁和行锁之间，会出现死锁，锁定粒度结余表锁和行锁之间，并发度一般

主从复制

基本原则

  • 每个slave只有一个master
  • 每个slave只能有一个唯一的服务器ID
  • 每个master可以有多个slave
docker run -p 3307:3306 --name mysql-slave1 -v /root/slave1/log:/var/log/mysql -v /root/slave1/data:/var/lib/mysql -v /root/slave1/conf:/etc/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.6.49

一主一从

# Master配置
[mysqld]
server-id=1 # 必须
log-bin=/var/lib/mysql/mysql-bin # 必须
read-only=0
binlog-ignore-db=mysql

# Slave配置
[mysqld]
server-id=2 # 必须

关于docker下mysql无法启动修改配置文件

把docker容器中的配置文件复制到主机中，然后在主机中修改，修改完成后再复制到docker容器中，这里需要使用的docker的命令：

docker cp [容器id]：docker容器中配置文件路径  主机路径

例如：

docker cp mysql:/etc/mysql/my.cnf /home/my.cnf

这样就能把容器中的文件拿到本地来，然后进行修改之后呢，再通过该命名上传到容器中去。

例如：

docker cp 主机文件路径 容器id：docker容器中配置文件路径
docker cp /home/my.cnf mysql:/etc/mysql/my.cnf

主机

创建复制账户

GRANT REPLICATION SLAVE ON . TO ‘zhangsan’@‘182.254.146.46’ IDENTIFIED BY ‘123456’;

刷新配置

FLUSH PRIVILEGES;

查看主机状态

SHOW MASTER STATUS;

从机

CHANGE MASTER TO MASTER_HOST='182.254.146.46',
MASTER_USER='zhangsan',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='file编号',
MASTER_LOG_POS=Position值;

开启

START SLAVE;

从机状态

SHOW SLAVE STATUS\G

lave_IO_Running 和 Slave_SQL_Running 必须同时为Yes 说明主从复制配置成功！

raptor.
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
网络安全实战之靶场渗透技术
zxcvbnmasdflzl的博客
12-26 414
靶机主要是从信息收集开始，发现是 flask 搭建的，然后使用 flask unsign 进行解密，暴力破解。接着使用枚举出的用户进行 FTP 登录，FTP 登录之后，下载 pdf 文件，发现管理员账号和密码规则，然后登录 FTP 管理员，接着下载压缩包，进行文件分析，发现可以使用 CVE-2021-23639 历史漏洞可以获取权限，在文件中发现存在 mysql 服务，接着进行权限提升，获取到了 root 文件。
高性能Mysql主从架构的复制原理及配置详解
weixin_30311605的博客
03-06 2036
温习《高性能MySQL》的复制篇. 1 复制概述 Mysql内建的复制功能是构建大型，高性能应用程序的基础。将Mysql的数据分布到多个系统上去，这种分布的机制，是通过将Mysql的某一台主机的数据复制到其它主机（slaves）上，并重新执行一遍来实现的。复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日...
(mysql)Packet for query is too large
heartbeaty的专栏
11-18 478
原因是返回结果内容太大，需要改设置 1.找到如下位置，修改my.ini文件 2.查找max_allowed_packet，有的文件没有，没有的话增加 [mysqld] max_allowed_packet = 20M 3.然后重启，mysql服务器运行程序，可以正常添加数据，问题就解决啦（控制面板---管理工具-----服务----mySql服务---重新启动
Raptor过程调用
热门推荐
新海说
11-30 2万+
写在前面在前面的一篇文章练习了利用raptor的输入，运算的操作，在这篇文章将学习函数过程的调用。问题：求矩形面积的过程1-1，问题描述输入矩形的长和宽，计算并输出矩形的面积。要求将求面积的功能定义为一个过程。1-2，问题分析此问题就分三个部分，输入：a，b,运算：s=a*b，输出：s变量：r，c；运算：s=a*b；至此，这个问题与上一个问题均一致，重要是如何实现对运算过程，通过过程调用来实现。1-
mysql优化！
08-30
mysql使用中的一些优化。
Mysql优化之Zabbix分区优化
09-09
主要介绍了Mysql优化中Zabbix分区优化的详细方法和优缺点分析，一起学习下。
mysql优化笔记+资料
11-18
详细介绍了mysql的优化方法，资料包里包含word文档，ppt和图片
mysql 优化
03-26
mysql 优化 慢查询 性能分析 分区分表 脏读幻读重复读
mysql优化方案
10-11
BIOS设置优化、IO子系统优化、Schema设计优化、索引设计优化、无法使用索引的场景
2h拿下MySQL优化.pdf
02-23
经典案例实例分析，通过演示教你掌握基本的mysql优化
mysql 优化日记
09-11
mysql 优化日记 使用mysql的朋友可以参考下，大家知道如果mysql优化与没优化性能会相差不少呢。
mysql优化记录文档
02-03
mysql优化记录文档
mysql优化十大技巧
04-26
mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql...
JDBC访问数据库之代码生成器Raptor-v0.1
徽峰
07-24 1693
JDBC访问数据库之代码生成器Raptor-v0.1   胡磊2005年7月22日      前言：          自从JDBC问世以来，人们在开发基于Java的B/S结构的软件的时候，在如何通过JDBC来访问数据库这个方面作了不少的工作。从最开始直接通过java.sql包中的各个对象直接执行SQL语句，到现在O/R Mapping工具的广泛使用，无不体现着人类思想的结晶。         
循环获取Query中的查询条件，过滤条件
weixin_30909575的博客
10-24 214
void getSearchingCriteria(Query _q) { int i,j,curFieldID; str curPname ; str curRangeValue; ; for (i=1; i<= _q.dataSourceCount(); i++) { for (...
(五)练习Query查询语句
Wangzx的博客
04-08 3996
1.创建数据库 2.在Query中输入如下: /* Navicat MySQL Data Transfer Source Server : forCourse Source Server Version : 50162 Source Host : localhost:3306 Source Database : forselect T...
Raptor入门练习
新海说
11-30 9436
raptor 输入，输出练习
mysql优化是什么
最新发布
12-20
MySQL优化是通过调整MySQL的参数和配置，以及优化SQL语句的编写和执行，来提高MySQL系统的性能和可用性的过程。其主要目标是尽可能快地响应用户请求，提高系统的处理能力和并发性能，同时减少资源消耗和硬件成本。...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值