mysql优化探究

mysql优化探究

测试环境：linux ,mysql ，mysqlslap工具 ，docker

---

---

前言

提示：用mysqlslap工具对数据库常用查询进行压力测试，根据压测结果总结优化注意点，在部署docker容器中测试。测试结果取的是多次测试择优对比。

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、多表联查的原理？

1.join的算法 — Simple Nested-Loop Join

解释： 简单的嵌套循环连接，类似编程语言的for嵌套遍历。
java实现伪算法：

		ArrayList<Article> articleList = new ArrayList<>();
        ArrayList<Category> categoryList = new ArrayList<>();
        for (Article ar: articleList) {
            for (Category ca: categoryList) {
                if(ar.getCategoryId == ca.getCategoryId){
                    //返回结果 组装结果集
                }
            }
        }

2.Index Nested-Loop Join

解释：在 Simple Nested-Loop Join 基础之上使用索引实现嵌套循环连接。链表关联的字段是索引或者主键时会用到。

这里需要注意主键索引，单列索引和组合索引的区别：
对于单列索引和组合索引对于主键索引要回标查询。

思考：
个人根据数据库存储数据的机制（B+树）分析：
利用B+树的查询数据的规则，索引嵌套连接I/O的优点利用了索引的排序，所以计算公式：驱动表1W*索引的高度。
不管关联表有没有设置索引，在查询表的方式都会是在磁盘中每次去读取部分数据在内存中，来对比返回需要的数据；
MYSQL 存储默认 B+树，计算机文件系统的最小单元大小是每个块是4K大小 ，所以MYSQL的单元4K的倍数 16K（一页）；
在JOIN没有创建索引的关联表时，读取依次读取所有节点的数据，应为数据库每个记录节点的大小都有限制（4KB的倍数），没有索引的情况下，每个节点记录的数据很少（对于有索引的情况），所以在一定的内存中，读取的数据就会有限制，相对的读取磁盘的次数就相对的比较多。
在JOIN有索引的关联表时，数据有对于主键和索引字段单独的叶子节点，同样的16K对于没有索引的表，保存的数据量要多。相对的全局扫描所有的叶子节点IO的次数就少。
总结：用索引关联表时要比没有索引的性能高。

3.Block Nested-Loop Join

解释：缓存块嵌套循环连接。把关联查询的字段保存到join buffer中，再对比关联表。
如：驱动表 1W行数据 每次缓存100行，用这100行记录去遍历被驱动表（1W）的返回结果集。那么1W/100*1W 次，对于简单的嵌套连接就少了90%的I/O。

总结：
在选择Join算法时，会有优先级：
Index Nested-LoopJoin > Block Nested-Loop Join > Simple Nested-Loop Join

二、Myisam与Innodb的区别

锁：Innodb支持行级锁，若不能确定要扫描的范围会用到表级锁，Myisam不支持。 数据索引存储结构：Innodb的data数据保存在非叶子节点，myusam的非叶子节点中只保存了data的地址；myisam的索引保存在 .frm文件中。 事务：innodb支持事务，MVCC ( Multi-Version Concurrency Control )多版本并发控制 索引：InnoDB（索引组织表）使用的聚簇索引、索引包含数据，顺序存储，因此能缓存索引，也能缓存数据，MyISAM（堆组织表）使用的是非聚簇索引、索引和文件分开，随机存储，只能缓存索引 并发：myisam 读写互相阻塞，不仅会在写入的时候阻塞读取，MyISAM还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读；innodb 支持行级锁，可以针对行，读写阻塞与事务隔离级别相关 缓存：innodb要缓存数据和索引，MyISAM只缓存索引块； 寻址：innodb寻址要映射到块，再到行，MyISAM记录的直接是文件的OFFSET，定位比INNODB要快

三、数据库压力测试

1.1W级别

===================================================================== 
# 并发200  1W级别    
# 普通join 
mysqlslap -a -c 200 -i 100  -uroot -p123456  –query=" SELECT small_title,title,te_category.name as ca_name FROM te_article INNER JOIN  te_category  on te_category.category_id = te_article.category_id where te_category.category_id = 10 AND te_article.title like 'EI7oUB%'; "
# 结果 0.534 - 0.597 seconds  
# Average number of seconds to run all queries: 0.569 seconds
#	Minimum number of seconds to run all queries: 0.498 seconds
#	Maximum number of seconds to run all queries: 0.718 seconds
#	Number of clients running queries: 200 

# 优化join  
mysqlslap -a -c 200 -i 100  -uroot -p123456  –query=" SELECT small_title,title,t1.name as ca_name FROM te_article INNER JOIN  (SELECT category_id , name FROM te_category WHERE category_id = 10 ) as t1 on t1.category_id = te_article.category_id where  te_article.title like 'EI7oUB%' ; "
# 结果 0.504 - 0.573 seconds  
# Average number of seconds to run all queries: 0.588 seconds
#	Minimum number of seconds to run all queries: 0.519 seconds
#	Maximum number of seconds to run all queries: 0.687 seconds
#	Number of clients running queries: 200

# join 子查询    
mysqlslap -a -c 200 -i 100  -uroot -p123456  –query=" SELECT small_title,title FROM te_article   where  te_article.title like 'EI7oUB%' AND category_id IN (SELECT category_id  FROM te_category WHERE category_id = 10); "
# 结果 0.504 - 0.573 seconds  
# Average number of seconds to run all queries: 0.616 seconds
#	Minimum number of seconds to run all queries: 0.521 seconds
#	Maximum number of seconds to run all queries: 0.767 seconds
#	Number of clients running queries: 200

总结 ：
普通join的效果比较优秀

1.100W级别

=====================================================================
# 无索引

# 并发10  两张表联查   100W级别    无索引 命中主键
mysqlslap -a -c 200 -i 100  -uroot -p123456  –query=" SELECT small_title,title,te_category.name as ca_name FROM te_article INNER JOIN  te_category  on te_category.category_id = te_article.category_id where te_category.category_id = 10 AND te_article.title like 'EI7oUB%';"
# 结果 0.515 - 0.606 seconds  
# Average number of seconds to run all queries: 0.577 seconds
#	Minimum number of seconds to run all queries: 0.500 seconds
#	Maximum number of seconds to run all queries: 0.675 seconds
#	Number of clients running queries: 200 

# 并发10  两张表联查   100W级别    无索引 命中主键 子查询 
mysqlslap -a -c 200 -i 100  -uroot -p123456  –query=" SELECT small_title,title,t1.name as ca_name FROM te_article INNER JOIN  (SELECT category_id , name FROM te_category WHERE category_id = 10 ) as t1 on t1.category_id = te_article.category_id where  te_article.title like 'EI7oUB%' ;"
# 结果 0.523 - 0.612 seconds  
# Average number of seconds to run all queries: 0.566 seconds
#	Minimum number of seconds to run all queries: 0.495 seconds
#	Maximum number of seconds to run all queries: 0.660 seconds
#	Number of clients running queries: 200 

=====================================================================
# 并发200  两张表联查   100W级别 增加查询字段的单索引（不设置组合索引）

# 普通join     
mysqlslap -a -c 200 -i 100  -uroot -p123456  –query=" SELECT small_title,title,te_category.name as ca_name FROM te_article INNER JOIN  te_category  on te_category.category_id = te_article.category_id where te_category.category_id = 10 AND te_article.title like 'EI7oUB%';"
# 结果 0.517 - 0.618 seconds  
# Average number of seconds to run all queries: 0.587 seconds
#	Minimum number of seconds to run all queries: 0.503 seconds
#	Maximum number of seconds to run all queries: 0.726 seconds
#	Number of clients running queries: 200 

# 优化join    
mysqlslap -a -c 200 -i 100  -uroot -p123456  –query="  SELECT small_title,title,t1.name as ca_name FROM te_article INNER JOIN  (SELECT category_id , name FROM te_category WHERE category_id = 10 ) as t1 on t1.category_id = te_article.category_id where  te_article.title like 'EI7oUB%';"
# 结果 0.485 - 0.560 seconds  
# Average number of seconds to run all queries: 0.577 seconds
#	Minimum number of seconds to run all queries: 0.515 seconds
#	Maximum number of seconds to run all queries: 0.688 seconds
#	Number of clients running queries: 200 

#  join 子查询    
mysqlslap -a -c 200 -i 100  -uroot -p123456  –query=" SELECT small_title,title FROM te_article   where  te_article.title like 'EI7oUB%' AND category_id IN (SELECT category_id  FROM te_category WHERE category_id = 10); "
# 结果 0.485 - 0.560 seconds  
# Average number of seconds to run all queries: 0.566 seconds
#	Minimum number of seconds to run all queries: 0.493 seconds
#	Maximum number of seconds to run all queries: 0.668 seconds
#	Number of clients running queries: 200 


=====================================================================
# 字符串查询对比 增加子查询关联的组合索引（单索引和组合索引同时存在）

# 并发10  两张表联查   100W级别     
mysqlslap -a -c 200 -i 100  -uroot -p123456  –query=" SELECT small_title,title,te_category.name as ca_name FROM te_article INNER JOIN  te_category  on te_category.category_id = te_article.category_id where te_category.name = '6noYaC1oRfHIBin8qhnO2thviMf5omXZ';"
# 结果 0.517 - 0.618 seconds  
# Average number of seconds to run all queries: 0.599 seconds
#	Minimum number of seconds to run all queries: 0.524 seconds
#	Maximum number of seconds to run all queries: 0.699 seconds
#	Number of clients running queries: 200 

# 并发10  两张表联查   100W级别     
mysqlslap -a -c 200 -i 100  -uroot -p123456  –query="  SELECT small_title,title,t1.name as ca_name FROM te_article INNER JOIN (SELECT category_id,name FROM te_category WHERE name = '6noYaC1oRfHIBin8qhnO2thviMf5omXZ' ) as t1  on t1.category_id = te_article.category_id ;"
# 结果 0.485 - 0.560 seconds  
# Average number of seconds to run all queries: 0.637 seconds
#	Minimum number of seconds to run all queries: 0.548 seconds
#	Maximum number of seconds to run all queries: 0.822 seconds
#	Number of clients running queries: 200

=====================================================================
# 字符串查询对比 增加子查询关联的组合索引（只存在主键和组合索引）

# 并发10  两张表联查   100W级别     
mysqlslap -a -c 200 -i 100  -uroot -p123456  –query=" SELECT small_title,title,te_category.name as ca_name FROM te_article INNER JOIN  te_category  on te_category.category_id = te_article.category_id where te_category.name = '6noYaC1oRfHIBin8qhnO2thviMf5omXZ';"
# 结果 0.517 - 0.618 seconds  
# Average number of seconds to run all queries: 0.583 seconds
#	Minimum number of seconds to run all queries: 0.516 seconds
#	Maximum number of seconds to run all queries: 0.702 seconds
#	Number of clients running queries: 200 

# 并发10  两张表联查   100W级别     
mysqlslap -a -c 200 -i 100  -uroot -p123456  –query="  SELECT small_title,title,t1.name as ca_name FROM te_article INNER JOIN (SELECT category_id,name FROM te_category WHERE name = '6noYaC1oRfHIBin8qhnO2thviMf5omXZ' ) as t1  on t1.category_id = te_article.category_id ;"
# 结果 0.485 - 0.560 seconds  
# Average number of seconds to run all queries: 0.574 seconds
#	Minimum number of seconds to run all queries: 0.495 seconds
#	Maximum number of seconds to run all queries: 0.671 seconds
#	Number of clients running queries: 200

总结 ：
100W级别数据下，子查询表现优秀。原因是传统JOIN的开销大于创建临时表的开销。

---

总结

数据基数比较小的，使用传统的join链表。基数大的表可以使用子查询。