MySql性能优化（五）优化细节

最新推荐文章于 2024-10-02 21:06:30 发布

我本半山人

最新推荐文章于 2024-10-02 21:06:30 发布

阅读量380

点赞数 1

分类专栏：数据库文章标签： mysql 数据库性能优化

本文链接：https://blog.csdn.net/qq_38895905/article/details/128406472

版权

数据库专栏收录该内容

11 篇文章 0 订阅

订阅专栏

优化细节

当使用数据库列进行查询的时候尽量不要使用表达式，把计算结果放到业务层而不是数据层
尽量使用主键索引，而不是其他索引，因此主键索引不会触发回表查询
使用前缀索引
有的时候需要索引很长的字符串，这会让索引变的大且慢，通常情况下可以使用某个列开始的部分字符串，这样可以大大的节约索引空间，从而提高索引效率，但这会降低索引的选择性，索引的选择性是指不重复的索引数值和数据表记录的总数的壁纸，范围从 1/X到1之间。索引的选择性越高则查询效率越高，因为选择性更高的索引可以让mysql查询的时候过滤掉更多的行。
一般情况下某个列前缀的选择性也是足够高的，足以满足查询的性能，但是对应BLOB,TEXT,VARCHAR类型的列，必须要使用前缀索引，因为mysql不允许索引这些列的完整长度，使用该方法的诀窍在于要选择足够长的前缀以保证较高的选择性，通过又不能太长。
示例：

**--创建数据表

create table citydemo(city varchar(50) not null);insert into citydemo(city) select city from city;

--重复执行5次下面的sql语句

insert into citydemo(city) select city from citydemo;

--更新城市表的名称

update citydemo set city=(select city from city order by rand() limit 1);

--查找最常见的城市列表，发现每个值都出现45-65次，

select count(*) as cnt,city from citydemo group by city order by cnt desc limit 10;

--查找最频繁出现的城市前缀，先从3个前缀字母开始，发现比原来出现的次数更多，可以分别截取多个字符查看城市出现的次数

select count(*) as cnt,left(city,3) as pref 
	from citydemo group by pref order by cnt desc limit 10;

select count(*) as cnt,left(city,7) as pref 
	from citydemo group by pref order by cnt desc limit 10;

--此时前缀的选择性接近于完整列的选择性--还可以通过另外一种方式来计算完整列的选择性，可以看到当前缀长度到达7之后，再增加前缀长度，选择性提升的幅度已经很小了

select  count(distinct left(city,3))/count(*) as sel3,
			count(distinct left(city,4))/count(*) as sel4,
			count(distinct left(city,5))/count(*) as sel5,
			count(distinct left(city,6))/count(*) as sel6,
			count(distinct left(city,7))/count(*) as sel7,
			count(distinct left(city,8))/count(*) as sel8 from citydemo;
			
--计算完成之后可以创建前缀索引

alter table citydemo add key(city(7));

--注意：前缀索引是一种能使索引更小更快的有效方法，但是也包含缺点：mysql无法使用前缀索引做order by 和 group by。 **

使用索引扫描来排序
mysql有两种方式可以生成有序的结果：通过排序操作或者按索引顺序扫描，如果explain出来的type列的值为index,则说明mysqlf使用了索扫描来做排序。扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那么就不得不每扫描一条索引记录就得回表查询一次对应的行，这基本都是随机O,因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢。 Mysq可以使用同一个索引即满足排序，又用于查找行，如果可能的话，设计索时应该尽可能地同时满足这两种任务。只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方式都一样时，mysq才能够使用索引来对结果进行排序，如果查询需要关联多张表，则只有当orderby子句引用的字段全部为第一张表时，才能使用索引做排序。order by子句和查找型查询的限制是一样的，需要满足索引的最左前缀的要求，否则，mysql都需要执行顺序操作，而无法利用索引排序。
```
 using filesort 无法使用索引排序
```
```
 走索引排序
```
union all ,in ,or 都能使用索引，但是推荐使用 in

union 和 union all，尽量使用 union all，union有个去重之类的过程。
范围列可以用到索引，但范围列后面的无法使用到索引
强制类型转化会扫描全表
类型不对，mysql做类型转换，会强制扫描全表

更新十分频繁，数据区分度不高的字段上不建议加索引

 1 更新会变动B+树，更新频繁会大大降低数据库的性能
 2 类似于性别，是否删除这种区分度不高的字段，建立索引是没有意义的
 3 一般区分度再80%以上就可以建立索引，区分度可以使用count(distinct(列名)/count(*))来计算

创建索引的列，不允许为null，否则可能会得到不符合预期的结果
当需要进行表连接时，最好不要超过三张表；需要join的字段，数据类型最好一致
官方文档-Nested-Loop Join Algorthims
1.Simple Nested-Loop Join
简单嵌套循环连接实际上就是简单粗暴的嵌套循环，如果table1有1万条数据，table2有1万条数据，那么数据比较的次数=1万 * 1万 =1亿次，这种查询效率会非常慢。

2.Index Nested-Loop Join(索引嵌套循环连接)
索引嵌套循环是使用索引减少扫描的次数来提高效率的，所以要求非驱动表上必须有索引才行。
在查询的时候，驱动表会根据关联字段的索引进行查询，当索引上找到符合的值，才会进行回表查询。如果非驱动表的关联字段是主键的话，查询效率会非常高(主键索引结构的叶子结点包含了完整的行数据(InnoDB))，如果不是主键，每次匹配到索引后都需要进行一次回表查询(根据二级索引(非主键索引)的主键ID进行回表查询)，性能肯定弱于主键的查询。

3.Block Nested-Loop Join(缓存块嵌套循环连接)
如果存在索引，那么会使用index的方式进行join，如果join的列没有索引，被驱动表要扫描的次数太多了，每次访问被驱动表，其表中的记录都会被加载到内存中，然后再从驱动表中取一条与其匹配，匹配结束后清除内存，然后再从驱动表中加载一条记录然后把被驱动表的记录在加载到内存匹配，这样周而复始，大大增加了IO的次数。为了减少被驱动表的IO次数，就出现了Block Nested-Loop Join的方式。
不再是逐条获取驱动表的数据，而是一块一块的获取，引入了join buffer缓冲区，将驱动表join相关的部分数据列(大小是join buffer的限制)缓存到join buffer中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和join buffer中的所有驱动表记录进行匹配（内存中操作），将简单嵌套循环中的多次比较合并成一次，降低了非驱动表的访问频率。