MySQL 常见性能问题

MySQL 常见性能问题：

一、条件字段函数操作：

1. 如果对字段做了函数计算，该字段就用不上索引了；
2. 对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能；

注意：

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

二、隐式类型转换：

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

select * from table where tradeid=12345;

对于优化器来说，上述语句相当于：

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

• 简单说：对索引字段做了函数操作，优化器会放弃走树搜索功能；

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

看select “10” > 9 的结果：

mysql> select 10 >"9";

+---------+
| 10 >"9" |
+---------+
|       1 |
+---------+

1. 若规则是"将字符串转成数字"，那么就是做数字比较，结果是1;
2. 若规则是"将数字转成字符串"，那么就是做字符串比较，结果是0;
3. select “10” > 9 返回的是1，所以能确认MySQL里转换规则了，在MySQL中，字符串和数字做比较的话，是将字符串转换成数字；

总结：在MySQL中，字符串和数字做比较的话，是将字符串转换成数字；

三、隐式字符编码转换：

1. 做表连接查询的时候，两个表的字符集要是不同，会导致表连接查询的时候用不上关联字段的索引；
2. 连接过程中要求在被驱动表的索引字段上加函数操作，是直接导致对被驱动表做全表扫描的原因；

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

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

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

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

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

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

总结：

1. 如果驱动表的字符集比被驱动表的字符集小，关联列就能用到索引，如果更大，需要发生隐式编码转换，则不能使用索引；
2. 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
  

第三种原因：

索引使用不当，上述的说明；

第四种原因：

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

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

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

  1. 在传给引擎执行的时，做了字符截断。因为引擎里这个行只定义了长度10，所以只截了前10个字节，就是"123456789"进去匹配；
  2. 若满足条件数据，b字段’123456789’有10万行数据；
  3. 因为是select * ，所以要做10万次回表；
  4. 但是每次回表以后查出整行，到server层判断，b的值都不是"1234567890abcd"；
  5. 返回结果是空；