MYSQL SQL优化
mysql> show session 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 | 0 |
| Com_create_db | 0 |
| Com_create_event | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_procedure | 0 |
| Com_create_server | 0 |
| Com_create_table | 3 |
| Com_create_trigger | 0 |
| Com_create_udf | 0 |
| Com_create_user | 0 |
| Com_create_view | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 0 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_event | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_procedure | 0 |
| Com_drop_server | 0 |
| Com_drop_table | 3 |
| 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 | 10 |
| 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 | 13 |
| Com_set_option | 0 |
| 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 | 4 |
| Com_show_engine_logs | 0 |
| Com_show_engine_mutex | 0 |
| Com_show_engine_status | 0 |
| Com_show_events | 1 |
| Com_show_errors | 0 |
| Com_show_fields | 116 |
| 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 | 3 |
| Com_show_storage_engines | 0 |
| Com_show_table_status | 0 |
| Com_show_tables | 6 |
| Com_show_triggers | 1 |
| Com_show_variables | 1 |
| 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 | 1 |
| Com_uninstall_plugin | 0 |
| Com_unlock_tables | 0 |
| Com_update | 1 |
| 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 session status like 'com_up%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Com_update | 1 |
| Com_update_multi | 0 |
+------------------+-------+
2 rows in set (0.00 sec)
mysql> show global status;
+-----------------------------------------------+-------------+
| Variable_name | Value |
+-----------------------------------------------+-------------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 0 |
| Bytes_received | 7708 |
| Bytes_sent | 155985 |
| Com_admin_commands | 70 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
| Com_alter_event | 0 |
mysql> show processlist;
+----+------+---------------------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+---------------------+------+---------+------+-------+------------------+
| 1 | root | localhost | test | Query | 0 | init | show processlist |
| 2 | root | 196.128.1.123:50661 | test | Sleep | 1052 | | NULL |
| 3 | root | 196.128.1.123:50663 | test | Sleep | 993 | | NULL |
+----+------+---------------------+------+---------+------+-------+------------------+
3 rows in set (0.00 sec)
mysql> explain select * from t1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
mysql> explain select * from t1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
mysql> explain extended select * from t1;
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+---------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `test`.`t1`.`name` AS `name` from `test`.`t1` |
+-------+------+---------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> explain partitions select * from t1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES |
+------------------+
1 row in set, 1 warning (0.00 sec)
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
| 524288 |
+----------+
1 row in set (0.16 sec)
mysql> show profiles;
+----------+------------+------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------+
| 1 | 0.16138325 | select count(*) from t |
+----------+------------+------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> show profile for query 1;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000071 |
| checking permissions | 0.000008 |
| Opening tables | 0.000023 |
| init | 0.000019 |
| System lock | 0.000011 |
| optimizing | 0.000007 |
| statistics | 0.000018 |
| preparing | 0.000016 |
| executing | 0.000003 |
| Sending data | 0.161023 |
| end | 0.000023 |
| query end | 0.000007 |
| closing tables | 0.000014 |
| freeing items | 0.000131 |
| cleaning up | 0.000013 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)
如果正在进行大量的表扫描，Handler_read_rnd_next的值会较高：
mysql> show status like 'Handler_read%';
+-----------------------+---------+
| Variable_name | Value |
+-----------------------+---------+
| Handler_read_first | 30 |
| Handler_read_key | 31 |
| Handler_read_last | 0 |
| Handler_read_next | 1048576 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 1782872 |
+-----------------------+---------+
7 rows in set (0.00 sec)
mysql> analyze table t;
+--------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------+---------+----------+----------+
| test.t | analyze | status | OK |
+--------+---------+----------+----------+
1 row in set (0.02 sec)
mysql> check table t;
+--------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------+-------+----------+----------+
| test.t | check | status | OK |
+--------+-------+----------+----------+
1 row in set (0.39 sec)
mysql> optimize table t;
+--------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+--------+----------+----------+-------------------------------------------------------------------+
| test.t | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| test.t | optimize | status | OK |
+--------+----------+----------+-------------------------------------------------------------------+
2 rows in set (3.50 sec)
mysql> show variables like 'innodb_file_per%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)
大批量插入：
MyISAM引擎：
alter table t disable key;
load data infile '/root/t1.txt' into table t;
alter table t disable key;
mysql> load data infile '/var/lib/mysql/t1.txt' into table t;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from t;
+----+------+
| id | name |
+----+------+
| 1 | aaa |
| 2 | bbb |
+----+------+
2 rows in set (0.00 sec)
[root@dba mysql]# cat t1.txt
1 aaa
2 bbb
产生随机数：
mysql> select * from t where id<20 order by rand();
+----+------+
| id | name |
+----+------+
| 8 | aaa |
| 7 | bbb |
| 2 | bbb |
| 18 | bbb |
| 16 | bbb |
| 6 | aaa |
| 3 | aaa |
| 19 | aaa |
| 15 | aaa |
| 17 | aaa |
| 13 | aaa |
| 4 | bbb |
| 9 | bbb |
| 1 | aaa |
| 14 | bbb |
+----+------+
15 rows in set (0.00 sec)
mysql> show variables like 'lower_case_tab%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_table_names | 0 |
+------------------------+-------+
1 row in set (0.00 sec)
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29313086/viewspace-1742114/ ，如需转载，请注明出处，否则将追究法律责任。
