MySQL 常见性能问题

1270
发表时间:2019-11-27 13:45

MySQL 常见性能问题:

一、条件字段函数操作:

如果对字段做了函数计算,该字段就用不上索引了;

对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能;

注意:


优化器并不是要放弃使用这个索引,而是放弃了树搜索功能,优化器可以选择遍历主键索引,也可以选择遍历二级索引,只是遍历二级索引的时,会导致全表扫描,根本起不到索引的作用;

二、隐式类型转换:

若表中tradeid字段上,建立了索引,数据类型是varchar(32)类型,而你执行语句,会发现会走全表扫描,因为你输入的参数是整型,所以需做类型转换:


select * from table where tradeid=12345;

1

对于优化器来说,上述语句相当于:


select * from table where CAST(tradid AS signed int ) = 12345;

1

简单说:对索引字段做了函数操作,优化器会放弃走树搜索功能;

1.数据类型转换的规则怎么使用?

看select “10” > 9 的结果:


mysql> select 10 >"9";


+---------+

| 10 >"9" |

+---------+

|       1 |

+---------+


若规则是"将字符串转成数字",那么就是做数字比较,结果是1;

若规则是"将数字转成字符串",那么就是做字符串比较,结果是0;

select “10” > 9 返回的是1,所以能确认MySQL里转换规则了,在MySQL中,字符串和数字做比较的话,是将字符串转换成数字;

总结:在MySQL中,字符串和数字做比较的话,是将字符串转换成数字;


三、隐式字符编码转换:

做表连接查询的时候,两个表的字符集要是不同,会导致表连接查询的时候用不上关联字段的索引;

连接过程中要求在被驱动表的索引字段上加函数操作,是直接导致对被驱动表做全表扫描的原因;

1.为什么字符集不同就用不上索引呢?

当字符集是utf8mb4是utf8的超集,所以当两个类型的字符串在做比较时,MySQL内部的操作是,先把utf8字符串转成utf8mb4字符集,再做比较;


因此在执行语句时,需要将被驱动数据表里的字段一个个地转换成utf8mb4,在进行做比较


select * from table where CONVERT(traideid USING utf8mb4) = $L2.tradeid.value;


CONVERT()函数,在这里就是把输入的字符串转换成utf8mb4字符集


再次触发,对索引字段做函数操作,优化器会放弃走树搜索功能;


总结:


如果驱动表的字符集比被驱动表的字符集小,关联列就能用到索引,如果更大,需要发生隐式编码转换,则不能使用索引;

latin < gbk < utf8 < utf8mb4

四、为什么查一句语句,执行也是很慢?

第一种原因:


MySQL数据库本身被堵住,比如:系统或网络资源不够;


数据库本身就有很大压力,导致数据库服务器CPU占有率很高或IO利用率很高


第二种原因:


SQL语句被堵住,比如:表锁,行锁等,导致存储引擎不同执行的对于SQL语句;


使用show processlist, 查看当前所有的执行进程;


使用查询sys.cahema_table_lock_waits 找出造成阻塞的process id,把这个连接用kill命令断开;


select blocking_pid from sys.schema_table_lock_waits;


若有线程正要对表t做flush操作:


flush tables t with read lock; 仅关闭表t


flush tables with read lock; 关闭所有打开的表


查看谁占的写锁(版本:5.7),可通过sys.innodb_lock_waits;


select * from sys.innodb_lock_waits where locked_table = '`库名`.`表名`';\G

1

第三种原因:


索引使用不当,上述的说明;


第四种原因:


表中数据的特点导致,走了索引,但回表次数庞大;


比如定义字段b的数据类型varchar(10),执行SQL语句:


select * from table where b='1234567890abcd';

1

在传给引擎执行的时,做了字符截断。因为引擎里这个行只定义了长度10,所以只截了前10个字节,就是"123456789"进去匹配;

若满足条件数据,b字段’123456789’有10万行数据;

因为是select * ,所以要做10万次回表;

但是每次回表以后查出整行,到server层判断,b的值都不是"1234567890abcd";

返回结果是空;

————————————————

版权声明:本文为CSDN博主「Fe_cow丿」的原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接及本声明。

原文链接:https://blog.csdn.net/Fe_cow/article/details/103252040


文章分类: 技术论坛Java
热门文章

这篇文章很长,但绝对是精华,相信我,读完以后,你会知道学历不好的解决方案,记得帮我点赞哦。先说结论,无论赞不赞同,...

Java最常用的工具类,这些就够了

1、搜索引擎1.1、秘迹搜索一款无敌有良心、无敌安全的搜索引擎,不会收集私人信息,保护私隐,没有Cookie,并且...

就在昨天,一位叫小菜的读者微信我说了上面这段话。我当时看到这条微信的第一感觉是:小菜你也太菜了吧,这都不知道为啥啊...

包含的模块:本文分为十九个模块,分别是: Java 基础、容器、多线程、反射、对象拷贝、Java Web 、异常、...

官方公众号
打造IT教学生态圈,服务好每一位IT学员
官方微博
关于沃沃
与沃合作
核心课程
14天免费学习申请
UI设计表单
姓名
QQ号
手机号
提交