mysql数据库查询优化

电玩女神 2022-04-03 08:27 919阅读 0赞

**总结：**  
尽可能减少扫表的行数，达到目的  
**查询执行的基础**  
一.基础  
当希望mysql能够以更高的性能运行查询时，最好的办法就是弄清楚mysql是如何优化和执行查询的，基本可以从以下3点开始了解:  
1.客户端和mysql服务器的交互过程  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTEzMTEyOTE_size_16_color_FFFFFF_t_70]  
(1)客户端发送一条查询给服务器  
(2)服务器先检查查询缓存(第二次查询的SQL和第一次查询的SQL完全相同)，如果命中缓存，则立即返回存储在缓存中结果，否则进入下一阶段  
(3)服务器进行SQL解析，预处理，再由优化器生成对应的执行计划  
(4)Mysql根据优化器生成执行计划，调用存储引擎的API来执行查询  
(5)将结果返回客户端  
2.SQL语句关键字的执行顺序  
从一条复杂语句看

执行顺序		SQL语句
    (7)     	SELECT
    (8)     	DISTINCT <select_list>
    (1)     	FROM <left_table>
    (3)     	<join_type> JOIN <right_table>
    (2)     	ON <join_condition>
    (4)     	WHERE <where_condition>
    (5)     	GROUP BY <group_by_list>
    (6)     	HAVING <having_condition>
    (9)     	ORDER BY <order_by_condition>
    (10)    	LIMIT <limit_number>

3.where条件执行顺序  
例如: film 表中有50000条信息  
优化前:  
select \* from film where film\_name regexp ‘J’ and date<=2018-12-20 and date>=2018-01-01 //正则regexp 'J’作用于50000行数据  
优化后:  
select \* from film date<=2018-12-20 and date>=2018-01-01 and film\_name regexp ‘J’ //只有匹配上了时间才会进行正则校验

**索引**  
是数据库存储引擎用于快速找到记录的一种数据结构，良好的索引可以很好的提升查询速率，不恰当的索引会导致性能急剧下降，不同存储引擎的索引工作方式不一样，也不是所有的存储引擎都支持所有类型的索引，即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。

一.B-Tree索引  
一般说的索引，基本说的就是B-Tree索引,以下是B-Tree索引结构:  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTEzMTEyOTE_size_16_color_FFFFFF_t_70 1]  
该索引对如下类型查询有效:  
1.全值匹配  
指和索引中的所有列进行匹配，例如查找姓名Cuba Allen,出生于1960-01-01的人  
2.匹配最左前缀  
例如可以用于查找姓为Allen的人，只使用索引的第一列  
3.匹配列前缀  
也可以值匹配索引某一列的值的开头部分，例如查找所有以J开头为姓的人。  
4.匹配范围值  
例如查找姓在Allen和Barrymore之间的人.  
5.精确匹配某一列并范围匹配另外一列  
例如可以查找姓为Allen,并且名字为字母K开头的人  
6.只访问索引查询

B-Tree索引的限制：  
1.如果不是按照索引最左开始匹配，则无法使用索引，也就是无法跳过索引中列  
比如查找名为Bill的人(这里没有使用姓，所以无法使用索引)  
2.如果索引查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查询  
比如查询 where last\_name=‘Smith’ AND first Like ‘J%’ AND dob=‘1976-12-23’,那么索引只作用于前2个。  
所以有时候可以使用相同的列但顺序不同的索引来满足不同类型的查询要求。

二.哈希索引  
基于哈希表实现，只有精确匹配索引(=,In(),<=>符号)所有列才有效  
哈希表的数据结构：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTEzMTEyOTE_size_16_color_FFFFFF_t_70 2]  
例如 select \* from testhash where fname=‘Peter’  
因为哈希f(‘Peter’)=8784,则直接找到第三行

因为hash可能会产生撞词，所以为了提升查询效率和精确，可以使用以下语句  
比如数据库存有URL,和URL的CRC32的hashcode as url\_crc。则有如下:  
select \* from url where url\_crc=CRC32(‘http://www.mysql.com’) and url=“http://www.mysql.com”  
这样就会先匹配hashcode,然后相同的再字符逐一比较  
CRC32为32位，也可以扩展使用64位FNV64()

三.空间数据索引(R-Tree)  
可以用做地理数据存储

四.全文索引  
全文索引是一种特殊类型的索引，它查找的是文本中的关键字，而不是直接比较索引中的值，全文索引更类似于搜索引擎做的事，适用于Match Against,而不是简单的where条件语句。  
使用全文索引  
1.查看ft\_min\_word\_len最小分词长度是否为1  
SHOW GLOBAL VARIABLES LIKE ‘%ft\_%’;  
2.修改/添加最小分词长度，需要修改配置文件my.cnf(linux),my.ini(windows)  
ft\_min\_word\_len=1  
3.更新表结构  
repair table tablename quick

五.高性能索引策略  
1.索引列不能是表达式的一部分,始终将索引列单独放在比较符的一边。  
比如select \* from actor where actorid+1 = 5，//导致mysql不会使用索引  
应为:select \* from actor where actorid = 5-1  
2.使用前缀索引  
有时候索引的列是很长的字符串，这会让索引变得大且慢，一个解决办法是使用哈希索引，但有时还不够，  
这样通常可以使用前缀几个字符作为索引。那如何选择前缀几个字符作为索引呢？  
可以通过计算索引选择性值，索引选择性值越高，表明查询效率就越高。  
索引选择性值 = 不重复的索引值个数/数据表的记录总数，0-1之间，1表示查询效率最好  
比如：使用城市名列作为索引，可以使用城市名的前3个字符作为索引。  
3.多列索引  
很多人对多列索引的理解不够，一个常见的错误就是为每个列创建索引，或者按照错误的顺序创建多列索引。  
在创建索引时，尽量遵循“3星系统”或者创建一个全覆盖索引。  
“3星系统”,评价一个索引是否合适某个查询:  
1.索引将相关记录放在一起则获得一星  
2.索引中的数据顺序和查询中的排列顺序一致获得二星  
3.索引中的列包含了查询中需要的全部列则获得三星  
全覆盖索引:  
一个索引包含所有需要查询字段的值  
比如:有一个多列索引(store\_id,film\_id),执行语句select store\_id,film\_id from film,这样就可以使用  
索引获取数据，不需要读取表中的数据行。

**其它查询性能优化**  
1.不要使用select \*语句，因为会让优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的IO,内存，CPU消耗。  
2.使用索引可以减少数据库扫描数据的行数  
比如:select film\_name from film where film\_id=1,film\_id为索引，则使用EXPLAIN查看，发现扫描的行数为10行，删掉索引，发现扫描的行数为5073行  
3.如果在sql中用到where条件，则好坏依次为:  
(1)在索引中使用where条件来过滤不匹配的记录，这是在存储引擎层完成  
(2)使用索引覆盖扫描来返回记录，直接从索引中过滤不需要的记录并返回命中结果，这是在MySql服务器层来完成。  
(3)从数据表返回数据，然后过滤不满足条件的记录，在mysql服务器层完成，mysql需要先从数据库表读出记录然后过滤。  
4.切分查询  
比如:将一次性删除一张10W数据量的表，可以分为10次每次删除1W数据量的表，可以降低对服务器的影响，还可以减少删除时锁的持有时间  
5.分解关联查询  
优点:可以让缓存的效率的更高，有时也可以适当减少扫描的数据量。  
6.排序优化  
如果不能通过索引生成排序结果，那么如果排序的数据量小于“排序缓冲区”，那么mysql使用内存进行排序，如果内存不够  
排序，那么会进行文件排序，那么mysql会先将数据分块，对每个独立的块使用“快速排序”进行排序。然后将各块排序结果存放磁盘上，然后将各个排好序的块进行合并，最后返回结果。  
7.在关联查询和关联子查询，关联子查询未必性能就差，可以先测试，再选择使用哪个。  
8.如果查询数据时只想获取前20项，那么这个limit的位置可以先考量后再放在最佳位置，可以提升效率  
9.可以使用哈希关联，MariaDB支持  
10.查找最大MAX()值和最小值MIN()，mysql会进行全表扫描，可以使用比如:select \* from film use index(primary) where name=‘penelope’ limit 1  
11.count()有两种不同的作用  
(1)它可以统计某个列值的数量，在统计时要求列值为非空(不统计NULL)  
(2)count(*)，统计行数，通配符*并不会像我们猜想的那样扩展成所有列，而是会忽略所有列而直接统计所有行数。  
12.使用近似值，有些时候某些值并不需要多么的精确，比如在线人数，可以使用EXPLAIN来代替，它不会真正去执行SQL查询，所以成本很低.  
13.group by，order by，如果没有通过order by显示的指定排序列，当查询使用使用Group by的时候，结果集会自动按照分组的字段进行排序，如果不关心结果集的排序，而这种默认排序又导致了需要文件排序。则可以使用Order by NULL。

有些低版本的mysql创建索引需要更新一下存储结构才能生效:  
repair table tablename quick

**分析查询慢原因**  
1.分析pt-query-digest日志文件  
2.剖析单条查询  
使用show profile  
使用show status返回一些计数器  
3.诊断间歇性问题  
(1)使用show global status查看是否是单条语句的效率问题  
(2)使用show processlist来观察是否又大量显出处于不正常的状态

myisam  
可以使用show open table  
表锁  
假设有两个session  
分别为session1，session2

1.如果session1，lock table read  
则此时：  
session1可读，不可写，不可读其它没有锁定的表  
session2可读，写会阻塞，直到session1释放锁

后再进行update，update会发生阻塞

2.如果session1，lock table write  
则此时：  
session1可读，可写，不可读其它没有锁定的表  
session2读写都阻塞，

所以说myisam不合适做写为主表的引擎，如果读也多，会造成大量的堵塞，所以作为偏读表引擎会好很多

InnoDB  
特点:行锁  
开销大，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也最高  
InnoDB和myisam最大不同有两点:  
1.支持事物(transaction)  
2.采用了行锁

虽然行锁，  
但是有时候在特定场景发生索引失效由行锁变表锁  
还有间隙锁的风险，也就是由数据行id=1,3,4，没有2，而session1操作id<4的数据行，其它session 中update有id=2，则会放生阻塞，InnoDB会连着一起锁

查看行锁花的时间  
show status like ‘innodb\_row\_lock%’

花费的单元  
随机硬盘中读取一个数据页

主要的花费操作  
IO花费  
1.从table中读取数据页  
2.从索引中读取数据页

CPU花费  
1.执行查询条件  
2.匹配keys(conditipon?)和数据记录  
3.排序keys

主要的花费内容  
1.从table中读取数据页 1.0  
2.从缓存中读取数据页 1.0  
3.执行查询条件 0.2  
4.比较 key/record 0.2

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTEzMTEyOTE_size_16_color_FFFFFF_t_70]: /images/20220403/04cedb0126ce478bb11142af6359a4c8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTEzMTEyOTE_size_16_color_FFFFFF_t_70 1]: /images/20220403/96fae0d0ce65424980059c8b100950d3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTEzMTEyOTE_size_16_color_FFFFFF_t_70 2]: /images/20220403/08ae64e0f28648de904984b6a9432047.png