每天进步一点点—SQL优化

时间:2021-05-15 05:14:07

一、           SQL优化

1.   通过show status 命令了解各种SQL的运行频率

mysql>show status like 'Com_%';

+---------------------------+-------+

| Variable_name             | Value |

+---------------------------+-------+

| Com_admin_commands        | 0    |

| Com_assign_to_keycache    | 0    |

| Com_alter_db              | 0     |

| Com_alter_db_upgrade      | 0    |

| Com_alter_event           | 0     |

| Com_alter_function        | 0    |

| Com_alter_procedure       | 0    |

| Com_alter_server          | 0     |

| Com_alter_table           | 0     |

| Com_alter_tablespace      | 0    |

| Com_alter_user            | 0     |

| Com_analyze               | 0     |

| Com_begin                 | 0     |

| Com_binlog                | 0     |

| Com_call_procedure        | 0    |

| Com_change_db             | 4     |

| Com_change_master         | 0    |

| Com_check                 | 0     |

| Com_checksum              | 0     |

| Com_commit                | 30   |

| Com_create_db             | 1     |

| Com_create_event          | 0     |

| Com_create_function       | 3    |

| Com_create_index          | 0     |

| Com_create_procedure      | 3    |

| Com_create_server         | 0    |

| Com_create_table          | 16    |

| Com_create_trigger        | 6    |

| Com_create_udf            | 0     |

| Com_create_user           | 0     |

| Com_create_view           | 7     |

| Com_dealloc_sql           | 0     |

| Com_delete                | 0     |  ——运行delete操作的次数

| Com_delete_multi          | 0     |

| Com_do                    | 0     |

| Com_drop_db               | 1     |

| Com_drop_event            | 0     |

| Com_drop_function         | 0    |

| Com_drop_index            | 0     |

| Com_drop_procedure        | 0    |

| Com_drop_server           | 0     |

| Com_drop_table            | 0     |

| Com_drop_trigger          | 0     |

| Com_drop_user             | 0     |

| Com_drop_view             | 0     |

| Com_empty_query           | 0     |

| Com_execute_sql           | 0     |

| Com_flush                 | 0     |

| Com_get_diagnostics       | 0    |

| Com_grant                 | 0     |

| Com_ha_close              | 0     |

| Com_ha_open               | 0     |

| Com_ha_read               | 0    |

| Com_help                  | 0     |

| Com_insert                | 1017  |   ——运行insert操作次数。批量插入insert仅仅加1

| Com_insert_select         | 0    |

| Com_install_plugin        | 0    |

| Com_kill                  | 0     |

| Com_load                  | 0    |

| Com_lock_tables           | 0     |

| Com_optimize              | 0     |

| Com_preload_keys          | 0     |

| Com_prepare_sql           | 0     |

| Com_purge                 | 0     |

| Com_purge_before_date     | 0    |

| Com_release_savepoint     | 0    |

| Com_rename_table          | 0     |

| Com_rename_user           | 0     |

| Com_repair                | 0     |

| Com_replace               | 0     |

| Com_replace_select        | 0    |

| Com_reset                 | 0     |

| Com_resignal              | 0     |

| Com_revoke                | 0     |

| Com_revoke_all            | 0     |

| Com_rollback              | 0     |

| Com_rollback_to_savepoint | 0     |

| Com_savepoint             | 0     |

| Com_select                | 7    |            ——运行SELECT操作的次数。每次加1

| Com_set_option            | 48    |

| Com_signal                | 0     |

| Com_show_binlog_events    | 0    |

| Com_show_binlogs          | 0     |

| Com_show_charsets         | 0    |

| Com_show_collations       | 0    |

| Com_show_create_db        | 0    |

| Com_show_create_event     | 0    |

| Com_show_create_func      | 0    |

| Com_show_create_proc      | 0    |

| Com_show_create_table     | 0    |

| Com_show_create_trigger   | 0    |

| Com_show_databases        |2     |

| Com_show_engine_logs      | 0    |

| Com_show_engine_mutex     | 0    |

| Com_show_engine_status    | 0    |

| Com_show_events           | 0     |

| Com_show_errors           | 0     |

| Com_show_fields           | 0     |

| Com_show_function_code    | 0    |

| Com_show_function_status  | 0    |

| Com_show_grants           | 0     |

| Com_show_keys             | 0     |

| Com_show_master_status    | 0    |

| Com_show_open_tables      | 0    |

| Com_show_plugins          | 0     |

| Com_show_privileges       | 0    |

| Com_show_procedure_code   | 0    |

| Com_show_procedure_status | 0     |

| Com_show_processlist      | 0    |

| Com_show_profile          | 0     |

| Com_show_profiles         | 0    |

| Com_show_relaylog_events  | 0    |

| Com_show_slave_hosts      | 0    |

| Com_show_slave_status     | 0    |

| Com_show_status           | 1     |

| Com_show_storage_engines  | 0    |

| Com_show_table_status     | 0    |

| Com_show_tables           | 2     |

| Com_show_triggers         | 0    |

| Com_show_variables        | 0    |

| Com_show_warnings         | 0    |

| Com_slave_start           | 0     |

| Com_slave_stop            | 0     |

| Com_stmt_close            | 0     |

| Com_stmt_execute          | 0     |

| Com_stmt_fetch            | 0     |

| Com_stmt_prepare          | 0     |

| Com_stmt_reprepare        | 0    |

| Com_stmt_reset            | 0     |

| Com_stmt_send_long_data   | 0    |

| Com_truncate              | 0     |

| Com_uninstall_plugin      | 0    |

| Com_unlock_tables         | 0    |

| Com_update                | 0     | ——运行update操作次数

| Com_update_multi          | 0     |

| Com_xa_commit             | 0     |

| Com_xa_end                | 0     |

| Com_xa_prepare            | 0     |

| Com_xa_recover            | 0     |

| Com_xa_rollback           | 0     |

| Com_xa_start              | 0     |

| Compression               | OFF   |

+---------------------------+-------+

142 rows in set (0.00 sec)

 

mysql>show status like 'Innodb_%';

+---------------------------------------+-------------+

| Variable_name                         | Value       |

+---------------------------------------+-------------+

| Innodb_buffer_pool_dump_status        | not started |

| Innodb_buffer_pool_load_status        |not started |

| Innodb_buffer_pool_pages_data         | 4436        |

| Innodb_buffer_pool_bytes_data         | 72679424    |

| Innodb_buffer_pool_pages_dirty        | 0           |

| Innodb_buffer_pool_bytes_dirty        | 0           |

| Innodb_buffer_pool_pages_flushed      | 2188        |

| Innodb_buffer_pool_pages_free         | 3744        |

| Innodb_buffer_pool_pages_misc         | 11          |

| Innodb_buffer_pool_pages_total        | 8191        |

| Innodb_buffer_pool_read_ahead_rnd     | 0           |

| Innodb_buffer_pool_read_ahead         | 3328        |

| Innodb_buffer_pool_read_ahead_evicted |0           |

| Innodb_buffer_pool_read_requests      | 2182160     |

| Innodb_buffer_pool_reads              | 552         |

| Innodb_buffer_pool_wait_free          | 0           |

| Innodb_buffer_pool_write_requests     | 237898      |

| Innodb_data_fsyncs                    | 1721        |

| Innodb_data_pending_fsyncs            | 0           |

| Innodb_data_pending_reads             | 0           |

| Innodb_data_pending_writes            | 0           |

| Innodb_data_read                      | 65753088    |

| Innodb_data_reads                     | 3910        |

| Innodb_data_writes                    | 3630        |

| Innodb_data_written                   | 79650304    |

| Innodb_dblwr_pages_written            | 2188        |

| Innodb_dblwr_writes                   | 76          |

| Innodb_have_atomic_builtins           | ON          |

| Innodb_log_waits                      | 0           |

| Innodb_log_write_requests             | 15742       |

| Innodb_log_writes                     | 897         |

| Innodb_os_log_fsyncs                  | 980         |

| Innodb_os_log_pending_fsyncs          | 0           |

| Innodb_os_log_pending_writes          | 0           |

| Innodb_os_log_written                 | 7911424     |

| Innodb_page_size                      | 16384       |

| Innodb_pages_created                  | 557         |

| Innodb_pages_read                     | 3879        |

| Innodb_pages_written                  | 2188        |

| Innodb_row_lock_current_waits         | 0           |

| Innodb_row_lock_time                  | 0           |

| Innodb_row_lock_time_avg              | 0           |

| Innodb_row_lock_time_max              | 0           |

| Innodb_row_lock_waits                 | 0           |

| Innodb_rows_deleted                   | 0           |——运行delete删除的行数

| Innodb_rows_inserted                  | 46350       |——运行insert操作插入的行数

| Innodb_rows_read                      | 1733860     |        ——select查询返回的行数。

| Innodb_rows_updated                   | 0           |——运行update更新的行数

| Innodb_num_open_files                 | 70          |

| Innodb_truncated_status_writes        | 0           |

| Innodb_available_undo_logs            | 128         |

+---------------------------------------+-------------+

51 rows in set (0.00 sec)

 

mysql>show status like 'connections';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Connections   | 36   |    ——试图连接MYSQLserver的次数

+---------------+-------+

1 row in set (0.00 sec

mysql>show status like 'uptime';

+---------------+--------+

| Variable_name | Value  |

+---------------+--------+

| Uptime        | 421477 |——数据库执行时间

 

+---------------+--------+

1 row in set (0.00 sec)

mysql>show status like 'slow_queries';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Slow_queries  | 0    |    ——慢查次数

+---------------+-------+

1 row in set (0.00 sec)

2.   定位运行效率较低的SQL

1、通过慢查日志定位哪些运行效率较低的SQL语句:--log-slow-queries=filename 启动时,mysqld写一个包括全部运行时间超过long_query_time秒的SQL语句的日志文件。

2、慢查日志在查询结束后才记录,所以应用反映运行效率出现故障的时候,查询慢查询日志并不能定位问题,能够通过show processlist命令来查看当前mysql的进行线程。包含线程状态和是否锁表等,能够时时查看SQL的运行情况,同事对锁表操作进行优化。

 

3.   通过explain分析SQL的运行计划

mysql>explain select sum(amount) from customer a,payment b where 1=1 anda.customer_id=b.customer_id and email='JANE.BENNETT@sakilacustomer.org'\G;

*************************** 1. row***************************

          id: 1

 select_type: SIMPLE

       table: a

        type: ALL

possible_keys: PRIMARY

         key: NULL

     key_len: NULL

         ref: NULL

        rows: 599

       Extra: Using where

*************************** 2. row***************************

          id: 1

 select_type: SIMPLE

       table: b

        type: ref

possible_keys: idx_fk_customer_id

         key: idx_fk_customer_id

     key_len: 2

         ref: sakila.a.customer_id

        rows: 13

       Extra: NULL

2 rows in set (0.00 sec)

 

ERROR:

No query specified

解释:

select_type:表示select的类型。常见的取值有SIMLE(简单表。不使用表连接或者子查询)

PRIMARY(主查询,即外层的查询)、UNION(UNION中第二个或者后面的查询语句)、SUBQUERY(子查询中的第一个SELECT)

table:输出结果集的表

type:表示MySQL在表中找到所需行的方式。或者叫訪问类型,常见类型例如以下:

         ALL:全表扫描,遍历全部行来找到匹配行

         index:索引全扫描,遍历整个索引来查询匹配的行

         range:索引范围扫描。常见于<、<=、>、>=、between

         ref:使用非唯一索引扫描或唯一索引的前缀扫描,返回匹配某个单独值的记录行

         eq_ref:使用的索引是唯一索引,对于每一个索引键值,表中仅仅有一条记录匹配(多表一般使用parimary key 和unique index作为关联条件)

         const/system:单表中最多有一行匹配。查询速度很迅速,所以这个匹配行中其它列的值能够被优化器在当前查询中当作常量来处理。

         NULL:mysql不用訪问表或者索引,直接就能得到结果。

         ref_or_null:与ref类似。差别在于条件中包括对NULL的查询。

         index_merge:索引合并优化

         unique_subquery:in的后面是一个查询主键字段的子查询

         index_subquery:与uniue_subquery类似,差别在于IN的后面是查询非唯一索引字段的子查询。

possible_keys:表示查询时可能使用的索引。

key:表示实际使用的索引

key_len:使用到索引字段的长度

rows:扫描行的数量

extra:运行情况的说明和描写叙述,包括不适合在其它列中显示,可是对运行计划很重要的额外信息。

4.   通过explain extended和show warnings查看优化器运行情况

mysql>explain extended select * from t99 where 1=1 and id=10414\G;

*************************** 1. row***************************

          id: 1

 select_type: SIMPLE

       table: t99

        type: const

possible_keys: PRIMARY

         key: PRIMARY

     key_len: 4

         ref: const

        rows: 1

    filtered: 100.00

       Extra: NULL

1 row in set, 1 warning (0.00 sec)

 

ERROR:

No query specified

通过showwarnings来查运行计划

mysql>show warnings\G;

*************************** 1. row***************************

 Level: Note

  Code: 1003

Message: /* select#1 */ select '10414' AS`id`,'rtkit:x:499:497:RealtimeKit:/proc:/sbin/nologin' AS `name` from`test2`.`t99` where 1

1 row in set (0.00 sec)

 

ERROR:

No query specified

能够看到运行计划会把1=1去掉,而且在explain extended多了一个filered字段。

5.   通过explain partitions来查看分区内容

mysql>explain partitions select * from emp1 where id=24088\G;

*************************** 1. row***************************

          id: 1

 select_type: SIMPLE

       table: emp1

   partitions: p0

        type: ALL

possible_keys: NULL

         key: NULL

     key_len: NULL

         ref: NULL

        rows: 69667

       Extra: Using where

1 row in set (0.00 sec)

 

ERROR:

No query specified