效果 SQL语句及索引 > 表结构涉及 > 系统配置 > 硬件升级 成本 硬件升级 > 系统配置 > 表结构涉及 > SQL语句及索引

剖析MySQL查询

参考《高性能MySQL》

show profile

查看SQL执行时间 SHOW PROFILE Statement 

  1. ###默认是关闭状态
  2. mysql>  show variables like "profil%";
  3. +------------------------+-------+
  4. | Variable_name          | Value |
  5. +------------------------+-------+
  6. | profiling              | OFF   |
  7. | profiling_history_size | 15    |
  8. +------------------------+-------+
  9. 2 rows in set, 1 warning (0.00 sec)
  11. mysql> set profiling = on;
  12. Query OK, 0 rows affected, 1 warning (0.00 sec)
  14. mysql>  show variables like "profil%";
  15. +------------------------+-------+
  16. | Variable_name          | Value |
  17. +------------------------+-------+
  18. | profiling              | ON    |
  19. | profiling_history_size | 15    |
  20. +------------------------+-------+
  21. 2 rows in set, 1 warning (0.00 sec)
  23. #-----------
  24.     执行SQL语句
  25. #-----------
  27. mysql> show profiles;
  28. +----------+------------+-----------------------------------------------------------------------------------------------------------------------------------+
  29. | Query_ID | Duration   | Query                                                                                                                             |
  30. +----------+------------+-----------------------------------------------------------------------------------------------------------------------------------+
  31. |        1 | 0.00138000 | show variables like "profil%"                                                                                                     |
  32. |        2 | 0.00082625 | select * from sakila.film where film_id in(select film_id from sakila.film_actor where actor_id=1)                                |
  33. |        3 | 0.00850675 | select * from sakila.film where exists( select * from sakila.film_actor where actor_id=1
  34.     and film_actor.film_id=film.film_id) |
  35. |        4 | 0.00077300 | select film.* from sakila.film inner join sakila.film_actor using (film_id) where actor_id = 1                                    |
  36. +----------+------------+-----------------------------------------------------------------------------------------------------------------------------------+
  37. 4 rows in set, 1 warning (0.00 sec)
  40. mysql> show profile for query 2; //查看第二个SQL的执行情况
  41. +----------------------+----------+
  42. | Status               | Duration |
  43. +----------------------+----------+
  44. | starting             | 0.000083 |
  45. | checking permissions | 0.000008 |
  46. | checking permissions | 0.000004 |
  47. | Opening tables       | 0.000019 |
  48. | init                 | 0.000125 |
  49. | System lock          | 0.000015 |
  50. | optimizing           | 0.000014 |
  51. | statistics           | 0.000076 |
  52. | preparing            | 0.000014 |
  53. | executing            | 0.000002 |
  54. | Sending data         | 0.000384 |
  55. | end                  | 0.000005 |
  56. | query end            | 0.000008 |
  57. | closing tables       | 0.000007 |
  58. | freeing items        | 0.000055 |
  59. | cleaning up          | 0.000010 |
  60. +----------------------+----------+
  61. 16 rows in set, 1 warning (0.00 sec)
  63. ##查看资源消耗情况比如CPU，IO等
  64. mysql> show profile cpu, block io, memory,swaps,context switches,source for query 2;
  66. ##查看全部情况
  67. mysql> show profile all for query 2;

Performance Schema

参考 Performance Schema

mysql> show databases;
---->  PERFORMANCE_SCHEMA

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql>

show processlist

https://dev.mysql.com/doc/refman/5.7/en/show-processlist.html

字段类型的优化

参考 《高性能MySQL》chapter4 参考 MySQL数据类型

同一类型的字段选择最小的

应该尽量使用可以正确存储数据的最小数据类型，更小的数据类型通常更快，因为它们占用更少的磁盘、内存
和CPU缓存，并且处理时需要的CPU周期更少，但是要确保没有低估需要存储的值的范围，
如果无法确认哪个数据类型，就选择你认为不会超过范围的最小类型

选择正确的数据类型

整形比字符型操作代价低，因为字符集和校对规则，字符比整型更复杂
使用MySQL自建类型标识日期和实践，而不是字符串
用IP转为整型存储inet_aton(ip)  inet_ntoa('数值')
https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_inet-ntoa

尽量避免使用null

如果查询中包含可为NULL的列，对mysql来说很难优化，
因为可为null的列使得索引、索引统计和值比较都更加复杂，
坦白来说，通常情况下null的列改为not null带来的性能提升比较小，
所有没有必要将所有的表的schema进行修改，但是应该尽量避免设计成可为null的列

案例一：on或using子句中的列上有索引

sakila示例数据库，查询Penelope Guiness(actor_id为1）参演过的所有影片信息 表关系通过datagrip 查看

SQL1 where in子句

explain 
    select * from sakila.film where film_id in(
        select film_id from sakila.film_actor where actor_id=1
    );

#分为以下两步

#step1
select group_concat(film_id) from film_actor where actor_id = 1;

#step2
select * from sakila.film where film_id in (1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980);

SQL2 where exists子句

explain 
    select * from sakila.film where exists( 
        select * from sakila.film_actor where actor_id=1
            and film_actor.film_id=film.film_id);

#-------------
1,PRIMARY,film,,ALL,,,,,1000,100,Using where
2,DEPENDENT SUBQUERY,film_actor,,eq_ref,"PRIMARY,idx_fk_film_id",PRIMARY,4,"const,sakila.film.film_id",1,100,Using index

结果分析:
1，先对film进行全表扫描   -----如果外层是一个非常大的表，则会导致查询性能非常低下
2，根据返回的film_id逐个进行子查询

SQL3 inner join

explain
    select film.* from sakila.film
        inner join sakila.film_actor using (film_id)
    where actor_id = 1;

####查询方式同SQL1 ，执行时间差不多    
1,SIMPLE,film_actor,,ref,"PRIMARY,idx_fk_film_id",PRIMARY,2,const,19,100,Using index
1,SIMPLE,film,,eq_ref,PRIMARY,PRIMARY,2,sakila.film_actor.film_id,1,100,


select * from film inner join film_actor using(film_id) where film_actor.actor_id = 1;