字节面试官的mysql优化夺命连环问：
1、（索引优化，避免索引失效）先看看有没有使用到索引，通过explain可以看到sql的执行计划，
mysql> explain select * from emp where empno = 4999999; +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 5000000 | 10.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
如果没有用到索引，就要看看对索引列是不是进行了函数或者表达式计算，有没有出现隐式类型转换，联合索引是否违反了最左匹配原则等等这些都会导致索引失效。
mysql> select * from emp where empno + 1 = 4999999; +---------+--------+----------+-----+------------+---------+--------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +---------+--------+----------+-----+------------+---------+--------+--------+ | 4999998 | hQLgzb | SALESMAN | 1 | 2021-09-18 | 2000.00 | 400.00 | 337 | +---------+--------+----------+-----+------------+---------+--------+--------+ 1 row in set (2.24 sec)
不在索引列上做任何操作（计算、函数、自动或手动类型转换），会导致索引失效而转向全表扫描。
索引失效参考：https://www.cnblogs.com/zwh0910/p/15316211.html
2、如果发现用到了索引还是很慢，这种情况下大概率是数据量太大的原因，可以考虑数据是不是可以归档，将一部分不需要访问的历史数据归档到文件或者hive里面进行存储，减少整个数据量。
3、（redis缓存）如果业务说这些数据还要用，不能归档，那可以考虑加一层redis缓存，加快热点数据的查询，如果查询是某些固定的维度，其实也可以考虑单独去建一张维度表，查询时直接查询维度表。
4、（读写分离）刚才考虑的都是读的场景，如果写的场景也遇到瓶颈了该怎么办？这个时候就要从数据库架构上考虑了，从单点架构变成一主多从的架构，提高并发读写的能力（读写分离）。
搭建mysql主从复制（一主一从）参考：https://www.cnblogs.com/zwh0910/p/16511041.html
mysql主从复制（一主三从）参考：https://www.cnblogs.com/zwh0910/p/17247296.html
5、（数据库集群）如果数据库变成了一主多从的架构后，还是发现很慢，那该怎么办呢？可以做分库分表，将一张表的数据分到不同的库，拆分成多张表进行存储，业务上通过设置的sharding key进行流量分发。
慢查询，顾名思义，就是比较慢的查询，那什么是算是慢查询呢？不同的公司对于慢查询的定义不尽相同。一般情况下，我们把查询时间超过1s的查询称为慢查询。
网站中，只要查询快了，网站就快了。增删改查：增删改只占10%，查询占了90%。打开网页不需要增删改，只是查询。
mysql的查询流程
① 客户端将查询发送到服务器；
② 服务器检查查询缓存，如果找到了，就从缓存中返回结果，否则进行下一步。（查询缓存在mysql8中被删除）
③ 服务器解析，预处理。
④ 查询优化器优化查询
⑤ 生成执行计划，执行引擎调用存储引擎API执行查询
⑥服务器将结果发送回客户端。
一、创建数据库和表
压力测试脚本如下：
#创建表DEPT CREATE TABLE dept( /*部门表*/ deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, dname VARCHAR(20) NOT NULL DEFAULT "", loc VARCHAR(13) NOT NULL DEFAULT "" ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ; #创建表EMP雇员 CREATE TABLE emp (empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/ ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/ job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/ mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/ hiredate DATE NOT NULL,/*入职时间*/ sal DECIMAL(7,2) NOT NULL,/*薪水*/ comm DECIMAL(7,2) NOT NULL,/*红利*/ deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/ )ENGINE=MyISAM DEFAULT CHARSET=utf8 ; #工资级别表 CREATE TABLE salgrade ( grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, losal DECIMAL(17,2) NOT NULL, hisal DECIMAL(17,2) NOT NULL )ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO salgrade VALUES (1,700,1200); INSERT INTO salgrade VALUES (2,1201,1400); INSERT INTO salgrade VALUES (3,1401,2000); INSERT INTO salgrade VALUES (4,2001,3000); INSERT INTO salgrade VALUES (5,3001,9999); # 随机产生字符串 #定义一个新的命令结束符合 delimiter $$ #删除自定的函数 drop function rand_string $$ #这里我创建了一个函数. #rand_string(n INT) rand_string 是函数名 (n INT) //该函数接收一个整数 create function rand_string(n INT) returns varchar(255) #该函数会返回一个字符串 begin #chars_str定义一个变量 chars_str,类型是 varchar(100),默认值'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; declare return_str varchar(255) default ''; declare i int default 0; while i < n do set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1)); set i = i + 1; end while; return return_str; end $$ delimiter ; select rand_string(6); # 随机产生部门编号 delimiter $$ drop function rand_num $$ #这里我们又自定了一个函数 create function rand_num( ) returns int(5) begin declare i int default 0; set i = floor(10+rand()*500); return i; end $$ delimiter ; select rand_num(); #****************************************** #向emp表中插入500万记录(海量的数据) delimiter $$ drop procedure insert_emp $$ #随即添加雇员[光标] 400w create procedure insert_emp(in start int(10),in max_num int(10)) begin declare i int default 0; #set autocommit =0 把autocommit设置成0 set autocommit = 0; repeat set i = i + 1; insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num()); until i = max_num end repeat; commit; end $$ delimiter ; #调用刚刚写好的函数, 1800000条记录,从100001号开始 call insert_emp(1,5000000); #************************************************************** # 向dept表中插入10条记录 delimiter $$ drop procedure insert_dept $$ create procedure insert_dept(in start int(10),in max_num int(10)) begin declare i int default 0; set autocommit = 0; repeat set i = i + 1; insert into dept values ((start+i) ,rand_string(10),rand_string(8)); until i = max_num end repeat; commit; end $$ delimiter ; call insert_dept(100,10); #------------------------------------------------ #向salgrade 表插入5条数据 delimiter $$ drop procedure insert_salgrade $$ create procedure insert_salgrade(in start int(10),in max_num int(10)) begin declare i int default 0; set autocommit = 0; ALTER TABLE emp DISABLE KEYS; repeat set i = i + 1; insert into salgrade values ((start+i) ,(start+i),(start+i)); until i = max_num end repeat; commit; end $$ delimiter ; #测试不需要了 #call insert_salgrade(10000,1000000);
通过存储过程向emp表插入500万数据，存储过程需要用到两个函数：rand_String(6)和rand_num.插入500万数据为什么不能写一个循环来实现呢？因为在做压力测试的时候，数据的每个值要都不一样，通过函数rand_String（6）从'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'中随机取6个字符，保证每个数据不一样，这样压力测试才能模拟真实的情况，我们要测试从500万的表中查一条记录要花多久时间。
emp表（500万条数据）如下：
dept表（10条）
salgrade表（5条）
打开cmd进行操作
mysql -u root -p
#密码输入123456
show databases; # 查看有哪些数据库
create database data1; # 创建data数据库
use data1; # 进入数据库
# 将压力测试脚本全部拷贝到cmd中执行，执行完成需要时间，请耐心等待
select count(*) from emp; # 查看emp表是否有500万条数据
select * from emp where empno = 4999999; #测试查询一条记录需要多少时间
执行过程中如下所示：
执行结束后如下所示：
注意：在navicat中给三个表设置主键。
结果如下：
mysql> select * from emp where empno=4999999; +---------+--------+----------+-----+------------+---------+--------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +---------+--------+----------+-----+------------+---------+--------+--------+ | 4999999 | dUqVFn | SALESMAN | 1 | 2023-03-22 | 2000.00 | 400.00 | 254 | +---------+--------+----------+-----+------------+---------+--------+--------+ 1 row in set (28.50 sec) mysql> select * from emp where empno=4999999; +---------+--------+----------+-----+------------+---------+--------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +---------+--------+----------+-----+------------+---------+--------+--------+ | 4999999 | dUqVFn | SALESMAN | 1 | 2023-03-22 | 2000.00 | 400.00 | 254 | +---------+--------+----------+-----+------------+---------+--------+--------+ 1 row in set (27.91 sec) mysql>
两次测试显示花了27秒。
二、查看数据库的状态
Mysql中有show status指令，显示关于数据库的一些状态，
mysql> show status;
结果：
mysql> show 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 | 2827 | | Bytes_sent | 1333 | | 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_instance | 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 | 2 | | Com_change_db | 1 | | Com_change_master | 0 | | Com_change_repl_filter | 0 | | Com_check | 0 | | Com_checksum | 0 | | Com_commit | 2 | | Com_create_db | 1 | | Com_create_event | 0 | | Com_create_function | 2 | | Com_create_index | 0 | | Com_create_procedure | 3 | | 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 | 2 | | Com_drop_index | 0 | | Com_drop_procedure | 3 | | 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_explain_other | 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 | 5000015 | | 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 | 5 | | Com_set_option | 2 | | 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 | 0 | | 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 | 0 | | Com_show_triggers | 0 | | Com_show_variables | 0 | | Com_show_warnings | 0 | | Com_show_create_user | 0 | | Com_shutdown | 0 | | Com_slave_start | 0 | | Com_slave_stop | 0 | | Com_group_replication_start | 0 | | Com_group_replication_stop | 0 | | Com_stmt_execute | 0 | | Com_stmt_close | 0 | | Com_stmt_fetch | 0 | | Com_stmt_prepare | 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 | | 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 | | Com_stmt_reprepare | 0 | | Compression | OFF | | Connection_errors_accept | 0 | | Connection_errors_internal | 0 | | Connection_errors_max_connections | 0 | | Connection_errors_peer_address | 0 | | Connection_errors_select | 0 | | Connection_errors_tcpwrap | 0 | | Connections | 8 | | Created_tmp_disk_tables | 0 | | Created_tmp_files | 5 | | Created_tmp_tables | 0 | | Delayed_errors | 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Flush_commands | 1 | | Handler_commit | 0 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_external_lock | 10000066 | | Handler_mrr_init | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 12 | | Handler_read_last | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 5000001 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 5000020 | | Innodb_buffer_pool_dump_status | Dumping of buffer pool not started | | Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 210918 9:09:45 | | Innodb_buffer_pool_resize_status | | | Innodb_buffer_pool_pages_data | 256 | | Innodb_buffer_pool_bytes_data | 4194304 | | Innodb_buffer_pool_pages_dirty | 0 | | Innodb_buffer_pool_bytes_dirty | 0 | | Innodb_buffer_pool_pages_flushed | 115 | | Innodb_buffer_pool_pages_free | 252 | | Innodb_buffer_pool_pages_misc | 4 | | Innodb_buffer_pool_pages_total | 512 | | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 0 | | Innodb_buffer_pool_read_ahead_evicted | 0 | | Innodb_buffer_pool_read_requests | 7260 | | Innodb_buffer_pool_reads | 456 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 2399 | | Innodb_data_fsyncs | 7 | | Innodb_data_pending_fsyncs | 0 | | Innodb_data_pending_reads | 0 | | Innodb_data_pending_writes | 0 | | Innodb_data_read | 7541248 | | Innodb_data_reads | 488 | | Innodb_data_writes | 132 | | Innodb_data_written | 1918976 | | Innodb_dblwr_pages_written | 2 | | Innodb_dblwr_writes | 1 | | Innodb_log_waits | 0 | | Innodb_log_write_requests | 0 | | Innodb_log_writes | 2 | | Innodb_os_log_fsyncs | 4 | | Innodb_os_log_pending_fsyncs | 0 | | Innodb_os_log_pending_writes | 0 | | Innodb_os_log_written | 1024 | | Innodb_page_size | 16384 | | Innodb_pages_created | 58 | | Innodb_pages_read | 455 | | Innodb_pages_written | 115 | | 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 | | Innodb_rows_inserted | 755 | | Innodb_rows_read | 919 | | Innodb_rows_updated | 0 | | Innodb_num_open_files | 24 | | Innodb_truncated_status_writes | 0 | | Innodb_available_undo_logs | 128 | | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 6685 | | Key_blocks_used | 13 | | Key_read_requests | 148 | | Key_reads | 9 | | Key_write_requests | 14 | | Key_writes | 14 | | Last_query_cost | 1048830.124000 | | Last_query_partial_plans | 1 | | Locked_connects | 0 | | Max_execution_time_exceeded | 0 | | Max_execution_time_set | 0 | | Max_execution_time_set_failed | 0 | | Max_used_connections | 4 | | Max_used_connections_time | 2021-09-18 11:15:56 | | Not_flushed_delayed_rows | 0 | | Ongoing_anonymous_transaction_count | 0 | | Open_files | 41 | | Open_streams | 0 | | Open_table_definitions | 564 | | Open_tables | 123 | | Opened_files | 794 | | Opened_table_definitions | 5 | | Opened_tables | 4 | | Performance_schema_accounts_lost | 0 | | Performance_schema_cond_classes_lost | 0 | | Performance_schema_cond_instances_lost | 0 | | Performance_schema_digest_lost | 0 | | Performance_schema_file_classes_lost | 0 | | Performance_schema_file_handles_lost | 0 | | Performance_schema_file_instances_lost | 0 | | Performance_schema_hosts_lost | 0 | | Performance_schema_index_stat_lost | 0 | | Performance_schema_locker_lost | 0 | | Performance_schema_memory_classes_lost | 0 | | Performance_schema_metadata_lock_lost | 0 | | Performance_schema_mutex_classes_lost | 0 | | Performance_schema_mutex_instances_lost | 0 | | Performance_schema_nested_statement_lost | 0 | | Performance_schema_prepared_statements_lost | 0 | | Performance_schema_program_lost | 0 | | Performance_schema_rwlock_classes_lost | 0 | | Performance_schema_rwlock_instances_lost | 0 | | Performance_schema_session_connect_attrs_lost | 0 | | Performance_schema_socket_classes_lost | 0 | | Performance_schema_socket_instances_lost | 0 | | Performance_schema_stage_classes_lost | 0 | | Performance_schema_statement_classes_lost | 0 | | Performance_schema_table_handles_lost | 0 | | Performance_schema_table_instances_lost | 0 | | Performance_schema_table_lock_stat_lost | 0 | | Performance_schema_thread_classes_lost | 0 | | Performance_schema_thread_instances_lost | 0 | | Performance_schema_users_lost | 0 | | Prepared_stmt_count | 0 | | Qcache_free_blocks | 0 | | Qcache_free_memory | 0 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 0 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 0 | | Queries | 145000835 | | Questions | 28 | | Select_full_join | 0 | | Select_full_range_join | 0 | | Select_range | 0 | | Select_range_check | 0 | | Select_scan | 1 | | Slave_open_temp_tables | 0 | | Slow_launch_threads | 0 | | Slow_queries | 1 | | Sort_merge_passes | 0 | | Sort_range | 0 | | Sort_rows | 0 | | Sort_scan | 0 | | Ssl_accept_renegotiates | 0 | | Ssl_accepts | 0 | | Ssl_callback_cache_hits | 0 | | Ssl_cipher | | | Ssl_cipher_list | | | Ssl_client_connects | 0 | | Ssl_connect_renegotiates | 0 | | Ssl_ctx_verify_depth | 0 | | Ssl_ctx_verify_mode | 0 | | Ssl_default_timeout | 0 | | Ssl_finished_accepts | 0 | | Ssl_finished_connects | 0 | | Ssl_server_not_after | | | Ssl_server_not_before | | | Ssl_session_cache_hits | 0 | | Ssl_session_cache_misses | 0 | | Ssl_session_cache_mode | NONE | | Ssl_session_cache_overflows | 0 | | Ssl_session_cache_size | 0 | | Ssl_session_cache_timeouts | 0 | | Ssl_sessions_reused | 0 | | Ssl_used_session_cache_entries | 0 | | Ssl_verify_depth | 0 | | Ssl_verify_mode | 0 | | Ssl_version | | | Table_locks_immediate | 5000163 | | Table_locks_waited | 0 | | Table_open_cache_hits | 5000029 | | Table_open_cache_misses | 4 | | Table_open_cache_overflows | 0 | | Tc_log_max_pages_used | 0 | | Tc_log_page_size | 0 | | Tc_log_page_waits | 0 | | Threads_cached | 0 | | Threads_connected | 4 | | Threads_created | 4 | | Threads_running | 1 | | Uptime | 7738 | | Uptime_since_flush_status | 7738 | +-----------------------------------------------+--------------------------------------------------+ 356 rows in set (0.00 sec)
Mysql数据库一些关于状态的查询：
show status : 查询mysql数据库的一些运行状态
show status like 'com_insert' : 显示执行了多少次插入
show status like 'com_update' : 显示执行了多少次更新
show status like 'com_delete' : 显示执行了多少次删除
show status like 'com_select' : 显示执行了多少次查询语句
mysql> show status like 'com_insert';
结果：
mysql> show status like 'com_insert'; +---------------+---------+ | Variable_name | Value | +---------------+---------+ | Com_insert | 5000015 | +---------------+---------+ 1 row in set (0.00 sec)
如果关闭cmd，再次进入mysql，输入show status like “com_insert”;发现原来显示的500多万条，现在只有0条。因为Session是会话级的，global是全局的，如果不写session或global，默认是会话级的（session），只要一关闭cmd，以前的就不算了。session 是默认参数（默认为show session status like ....） 是当前连接的统计结果， global 是自数据库上次启动到今的统计结果。
mysql> show status like 'com_insert'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Com_insert | 0 | +---------------+-------+ 1 row in set (0.00 sec)
使用global
mysql> show global status like 'com_insert'; +---------------+---------+ | Variable_name | Value | +---------------+---------+ | Com_insert | 5000015 | +---------------+---------+ 1 row in set (0.01 sec)
查看MySQL本次启动后的运行时间：show status like “uptime”,如果时间很长了，数据库的存储引擎又是MyISAM的，这时候就要注意整理碎片。
mysql> show status like 'uptime'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Uptime | 8711 | +---------------+-------+ 1 row in set (0.00 sec)
三、显示慢查询
Mysql默认超过10s才显示慢查询，输入show variables like “long_query_time”;
mysql> show variables like 'long_query_time'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 1 row in set, 1 warning (0.00 sec)
将慢查询的时间设置为0.5秒，超过0.5秒都算慢查询。
命令：set long_query_time=0.5；
mysql> set long_query_time=0.5; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'long_query_time'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 0.500000 | +-----------------+----------+ 1 row in set, 1 warning (0.01 sec) mysql>
查询慢查询的条数：show status like “slow_queries”;
mysql> show status like 'slow_queries'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Slow_queries | 2 | +---------------+-------+ 1 row in set (0.00 sec)
以上方法已经显示慢查询，下面如何来定位慢查询呢？
四、定位慢查询（开启慢查询的日志）
mysql数据库找不到my.ini的解决方法
首先，打开我的电脑，点击最上面的“查看”按钮, 在打开的“查看对话框”中，找到“隐藏的项目”，把它勾选上。这样，隐藏的文件夹就显示出来了。然后回到C盘，会发现C盘多了一个programdata，打开它, 然后找到mysql文件夹，打开它,找到mysql server 5.7文件夹，打开它. 这个时候，我们就找到了my.ini。可以用记事本打开它对它进行编辑 。
慢查询日志在datadir=C:/ProgramData/MySQL/MySQL Server 5.7\Data_oldSystem中，
在默认的情况下，mysql是不会记录慢查询的，
查看是否开启慢查询日志：show variables like ‘%slow_query_log%’;
mysql> show variables like 'slow_query_log'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | slow_query_log | ON | +----------------+-------+ 1 row in set, 1 warning (0.01 sec)
如何开启慢查询日志：set global slow_query_log=1。使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，如果Mysql重启后会失效。
mysql> show variables like '%slow_query_log%'; +---------------------+--------------------------+ | Variable_name | Value | +---------------------+--------------------------+ | slow_query_log | ON | | slow_query_log_file | DESKTOP-1CJMG8B-slow.log | +---------------------+--------------------------+ 2 rows in set, 1 warning (0.01 sec)
如果要永久生效，就必须修改配置文件my.ini，这样服务器重启不影响
修改my.ini文件（C:\ProgramData\MySQL\MySQL Server 5.7），在[mysqld]的下方加入添加如下内容：
slow-query-log=1 slow_query_log_file="DESKTOP-1CJMG8B-slow.log"
关于慢查询的参数slow_query_log_file，它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件host_name-slow.log。（如果没有指定参数slow_query_log_file的话）
那么开启了慢查询日志后，什么样的sql才会记录到慢查询日志里面呢？
这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒，
命令：show variables like "long_query_time%";
mysql> show variables like 'long_query_time'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 1 row in set, 1 warning (0.00 sec)
这个参数可以使用命令修改，也可以在my.ini文件中修改。
假如运行时间正好等于long_query_time的情况，并不会被记录下来，也就是说，在mysql源码里是判断大于long_query_time，而非大于等于。
设置慢的阈值时间：set global long_query_time=0.5；
修改了变量long_query_time，但是查询变量long_query_time的值还是10，莫非修改无效？
答：需要重新连接或新开一个会话才能看到修改值。
select sleep（1）；假设这是一条复杂的sql,它的执行时间是1秒。
mysql> select sleep(1); +----------+ | sleep(1) | +----------+ | 0 | +----------+ 1 row in set (1.02 sec)
文件的位置C:\ProgramData\MySQL\MySQL Server 5.7\Data_oldSystem\DESKTOP-1CJMG8B-slow.log，在慢查询日志的最下面
Time Id Command Argument # Time: 2021-09-19T02:12:29.336417Z # User@Host: root[root] @ localhost [::1] Id: 5 # Query_time: 1.020182 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 SET timestamp=1632017549; select sleep(1);
除了使用set global long_query_time=0.5；进行配置外，还可以在my.ini中进行配置，这样才能永久生效。
log-output=FILE general-log=0 general_log_file="DESKTOP-1CJMG8B.log" slow-query-log=1 slow_query_log_file="DESKTOP-1CJMG8B-slow.log" long_query_time=0.5
五、Explain使用简介
Explain是什么？查看执行计划。
使用Explain关键字可以模拟优化器执行SQL查询语句，从而知道mysql是如何处理你的SQL语句的，分析你的查询语句或表结构的性能瓶颈。
Explain能干什么？
mysql> explain select * from emp where empno = 4999999; +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 5000000 | 10.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.01 sec)
最重要的5个参数：id、type、key、rows、Extra。
1、Id（重要参数）:
select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。
1）、id相同，表示查询优化器加载表的顺序是从上到下。
Id都为1，即id相同，table栏从上到下为t1、t3、t2,即mysql的执行和加载顺序是t1、t3、t2。
项目中的sql:
explain SELECT count( 0 ) FROM t_variety_original vo INNER JOIN t_original o ON ( vo.original_id = o.id AND o.del_flag = 0 ) INNER JOIN t_variety v ON ( vo.variety_id = v.id AND v.del_flag = 0 ) WHERE vo.del_flag = 0
执行结果如下：
Id都为1，即id相同，table栏从上到下为vo、v、o，即mysql的执行和加载顺序是t_variety_original、t_variety、t_original。
2）、id全不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。
Subquery是子查询的意思。这条sql的执行顺序为t3、t1、t2.
3)、id相同不同，同时存在。即id既有相同的，也有不同的。永远是数字大的优先级最高，数字相同时从上往下顺序执行。即执行顺序为t3、derived2、t2.
Derived：衍生。Derived2中的2表示id为2的表。
id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行。
2、select_type：
表示SELECT语句的查询类型。主要是用于区别普通查询、联合查询、子查询等的复杂查询。Simple最简单，
1. simple:简单select查询（查询中不包含union或子查询）。
explain SELECT v.enterprise_id AS enterpriseId, e.enterprise_name AS enterpriseName, v.id AS varietyId, v.variety_name AS varietyName, o.id AS originalId, o.original_name AS originalName, o.remark AS remark, o.create_time AS createTime, o.update_time AS updateTime FROM t_variety_original vo INNER JOIN t_original o ON ( vo.original_id = o.id AND o.del_flag = 0 ) INNER JOIN t_variety v ON ( vo.variety_id = v.id AND v.del_flag = 0 ) LEFT JOIN sys_enterprise e ON e.id = v.enterprise_id WHERE vo.del_flag = 0 AND v.enterprise_id = 2883 ORDER BY vo.create_time DESC LIMIT 20
结果：
2. primary:最外层的select。表示该select查询包含子查询。
3. union:union中的第二个或后面的select语句。若第二个select出现在union之后，则被标记为union，若union包含在from子句的子查询中，外层select将被标记为derived。
4. dependent union:union中的第二个或后面的select语句，取决于外面的查询。
5. union result:union的结果。从union表获取结果的select。两个select合并的结果集。
6. subquery:子查询中的第一个select。即在select或where列表中包含了子查询。
7. dependent subquery:子查询中的第一个select，取决于外面的查询。
8. derived:导出表的select（from子句的子查询）。在from列表中包含的子查询被标记为derived（衍生），mysql会递归执行这些子查询，把结果放在临时表里。临时表有时候会增加系统的负担，但不得不用。
3、Type（重要参数）：
访问类型排列。区间索引，这是重要的列，显示查询使用了何种类型。从最好到最差的连接类型为：
system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery >range > index > ALL
一般来说，得保证查询至少达到range级别,最好能达到ref。
只要type是All，且数据量是百万以上的，请一定优化，因为All是最差的，表明是全表扫描。其实最好的程序是不优化。如果数据量有上百万了，且type为All,就需要跟经理DBA协商是否需要建索引。
1. system：一张表仅有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计。
2. const：表示通过索引一次就找到了（只需要查索引），const用于比较primary key（主键索引）或者unique索引，数据表最多只有一个匹配行，因为只匹配一行数据，所以很快。如将主键置于where列表中，mysql就能将该查询转换为一个常量。
如上面的sys_enterprise表中根据id（id为主键，即主键索引）查询记录时，type为const。
explain select * from sys_enterprise where id = 2883;
结果：
3. eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键索引或唯一索引扫描。mysql手册是这样说的:"对于每个来自于前面的表的行组合，从该表中读取一行。除了const类型，这可能是最好的联接类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY"。eq_ref可以用于使用=比较带索引的列。
explain select count(0) from emp t1 inner join dept t2 on t1.deptno = t2.deptno;
结果：
注意：这里涉及两个表，且查询条件是唯一性索引。
eq_ref用到了索引，但是查出来只有一条记录。
唯一性索引扫描：查询条件是唯一性索引，只会读取一行数据。
员工表的外键是deptId，部门表的主键是id。deptId作为外键需要建索引。总裁办也是公司的一个部门，现在这个部门有且仅有一条记录，即只有CEO，CEO也在员工表中，员工表的deptId为95，95即时总裁办的主键id，刚好只有一条记录匹配。
4. ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个复合条件的行，所以他应该属于查找和扫描的混合体。查询条件索引既不是UNIQUE也不是PRIMARY KEY（非空且唯一）的情况。ref可用于=或<或>操作符的带索引的列。
复合索引有两种情况：
1、 索引全部使用，即按照两个索引列来进行查询，
2、 索引部分使用，只用一个索引进行查询。
在现实工作中，你能到ref已经是很牛逼了。
非唯一性索引：查询条件为非唯一性索引，会查询到多行记录。
5. ref_or_null：该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。
6. index_merge：该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。
7. unique_subquery：该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_keyFROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
8. index_subquery：该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
9. range：范围索引扫描。只检索给定范围的行,使用一个索引来选择行。Key列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in、like等的查询。这种范围索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全表索引。
范围索引扫描：查询条件是索引，即使用索引来选择行。
并不是说使用了between、<、>、in等就一定会进行全表扫描，如果是使用索引来选择行，则不会全表扫描。
10. index：Full index scan全索引扫描，index与all区别为index类型只遍历索引树，该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。也就是说虽然all和index都是读全表，但index是从索引中读取的（可以少一些IO），而all是从硬盘中读取的。
一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。部分情况下是从内存中加载索引。
我们要从t1表获取id的信息，刚好id是索引，是全索引扫描。
全索引扫描：查询的列是索引，即将所有的索引查出来。
11. ALL：Full table scan全表扫描。对于每个来自于先前的表的行组合,进行完整的表扫描。（性能最差）
mysql> explain select * from emp where ename = 'iuxcXE'; +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 5000000 | 10.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
全表扫描：where后面的查询条件不是索引列。
总结：百万级别的数据量出现了All，没有特殊原因的话建议采取优化策略，但是现实而言，由于系统的复杂性，一般而言到range,最好到ref。
Possible_keys和key的作用:1,你是否使用到了索引，即判断是否索引失效。2、在多个索引竞争的前提下，最后mysql到底用到了哪一个索引
eq_ref：唯一性索引扫描>ref：非唯一性索引（普通索引）扫描>range:范围索引扫描>index：全索引扫描>All:全表扫描。
4、possible_keys：
显示可能应用到这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出。但不一定被查询实际使用。指出MySQL能使用哪个索引在该表中找到行。如果是空的，没有相关的索引。这时要提高性能，可通过检验WHERE子句，看是否引用某些字段，或者检查字段不是适合索引。
explain SELECT count(*) FROM t_variety_original vo INNER JOIN t_original o ON ( vo.original_id = o.id AND o.del_flag = 0 ) INNER JOIN t_variety v ON ( vo.variety_id = v.id AND v.del_flag = 0 ) LEFT JOIN sys_enterprise e ON e.id = v.enterprise_id WHERE vo.del_flag = 0 AND v.enterprise_id = 2883 ORDER BY vo.create_time DESC LIMIT 20
结果：
5、Key（重要参数）：
实际使用到的索引。如果为NULL，则没有使用索引。如果为primary的话，表示使用了主键。查询中若使用了覆盖索引，则该索引仅出现在key列表中。
有一种情况是：理论上用不到，实际上用到了，
覆盖索引（type=index）：
要查询的字段和索引键的个数的字段刚好吻合。简单来说就是：select后面查询的字段和建复合索引的个数和顺序一一对应，这时候就可以从索引上取，而不用全表扫描了，故type为index,即全索引扫描。
原来用select *，而*可能表示10个或20个字段，而现在是col1、col2两个，而我建的索引也是col1、col2两个，顺序也是一样的。
理解方式一：就是select的数据列只用从索引中就能够取得，不必读取数据行。mysql可以利用索引返回select列表中的字段，二不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。
查询的列要被所建的索引覆盖，或者说你建的是col1、col2的复合索引，刚好你查的也是col1、col2，或者部分满足。
理解方式二：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据。因此它不必读取整个行，毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做索引覆盖。
还用一种情况是：理论上应该用到了，实际上失效了。
6、key_len：
表示索引中使用的字节数，可以通过该列计算查询中使用的索引的长度。最长的索引宽度。如果键是NULL，长度就是NULL。在不损失精确性的情况下，长度越短越好。
Key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
条件越多，结果越精确，付出的代价也要多一些。同样的查询结果的前提下，精度越小越好。
7、ref：
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。显示使用哪个列或常数（const）与key一起从表中选择行。
explain SELECT count(*) FROM t_variety_original vo INNER JOIN t_original o ON ( vo.original_id = o.id AND o.del_flag = 0 ) INNER JOIN t_variety v ON ( vo.variety_id = v.id AND v.del_flag = 0 ) LEFT JOIN sys_enterprise e ON e.id = v.enterprise_id WHERE vo.del_flag = 0 AND v.enterprise_id = 2883 ORDER BY vo.create_time DESC LIMIT 20
结果：
t1表先加载，t1.other_column字段的值为常量。test.t1.ID表示数据库test中t1表的ID字段。
由key_len可知t1表的id_col1_col2被充分使用，col1匹配t2表的col1,col2匹配了一个常量。
8、Rows：
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。显示MySQL认为它执行查询时必须检查的行数。越小越好。
建索引之前，rows为641，可以使用主键索引，但是实际上没有用到；建了复合索引之后，rows为143，理论上可以用到两索引，实际上只用到idx_col1_col2；
9、Extra：
包含不适合在其他列中显示但十分重要的额外信息。执行状态说明，该列包含MySQL解决查询的详细信息。Using filesort、using temporary、using index这三个是Extra最重要的性能指标。
1）、Distinct:MySQL 发现第1个匹配行后,停止为当前的行组合搜索更多的行。
2）、Not exists:MySQL 能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
3）、range checked for each record (index map: #):MySQL 没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。
4）、Using filesort:说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，mysql中无法利用索引完成的排序操作称为“文件排序”。MySQL 需要额外的一次传递,以找出如何按排序顺序检索行。
Explain+sql :横排表示。Explain+sql\G:竖排表示。
建了三个字段的复合索引col1、col2、col3，其中using where：我们确实用到了where，using index：查的就是col1，所以用到了索引。using filesort：产生了文件排序，表示你建的索引在检索时部分使用到了，但是排序时没有用到表内的索引顺序。前面讲过，排序的字段要建立索引，排序的字段若通过索引去访问将大大提高排序的速度。即在排序的时候最好遵守你所建索引的顺序和个数。建索引优化的条件是：建好索引以后，排序的时候也按索引的顺序。而现在是col1和col3，col2没了。
后者的性能高于前者，同样的查询，后者排序的字段通过索引（按顺序）去访问，但前者在排序时没有按照索引的顺序，而是另起炉灶，自己多产生了一次排序。出现using filesort不好，如果可以尽快优化。使用到using filesort是九死一生。Using temporary是十死无生。Using filesort只是排了个序，using temporary新建了一个内部的临时表，也就是说处理数据的时候，先将数据拷贝到临时表再使用，使用完然后把临时表删除。更加凶险。
5）、Using index: 表示相应的select操作使用了索引覆盖（covering index），避免了访问表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找。如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。
如果你建的索引的字段刚好是你查的字段，而且个数和顺序刚好一致，这是最好的。
上面，你建的复合索引是col1、col2,而刚好查询的是col2，部分跟索引重合匹配，表明索引被用来执行索引键值的查找。
你建的复合索引是col1、col2,而刚好查询的是col1、col2，完全跟索引匹配。
6)、Using temporary: 使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by.也就是说，如果你的sql经常写order by或group by,这时要小心，因为这两个是拖慢你sql的元凶。为了解决查询,MySQL需要创建一个临时表来容纳结果。
用到了两个字段的复合索引（col1、col2），in是一种范围，所以type为range,但是groupby时，只使用了col2，所以产生了using temporary和using filesort。这种sql虽然正确，但是速度慢得无比。如果数据是百万级别的，你的系统会被拖慢。
建的索引的顺序是col1、col2、col3，group by要么别建索引，要建索引的话，group by一定尽量跟索引的个数和顺序来，否则特别容易产生文件排序。临时表的创建是很伤系统性能的。
7）、Using where:表明使用了where过滤。WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。
8）、Using sort_union(...), Using union(...), Using intersect(...): 这些函数说明如何为index_merge联接类型合并索引扫描。
9）、Using index for group-by: 类似于访问表的Using index方式,Using index for group-by表示
10）、Using join buffer：使用了连接缓存。
11）、Impossible where：where子句的值总是false，不能用来获取任何记录。如下所示，名字不可能是两个。
MySQL发现了一个索引,可以用来查询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。
六、Explain怎么玩？
Explain+SQL语句。
热身Case：
第一条执行：select name,id from t2
第二条执行:(select id,name from t1 where other_column = '') d1
第三条执行：(select id from t3) d2
第四条执行：select d1.name,(select id from t3) d2 from (select id,name from t1 where other_column = '') d1
最终优化的效果是：把不合适的索引删除，逐渐建索引，建成最符合我们系统的。
七、单表查询和两表查询的索引优化
将emp表的empno字段设置为主键
alter table emp add primary key (empno);
单表查询
select * from emp where empno = 4999999;
结果：
mysql> select * from emp where empno = 4999999; +---------+--------+----------+-----+------------+---------+--------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +---------+--------+----------+-----+------------+---------+--------+--------+ | 4999999 | lgXsYP | SALESMAN | 1 | 2021-09-18 | 2000.00 | 400.00 | 37 | +---------+--------+----------+-----+------------+---------+--------+--------+ 1 row in set (0.00 sec)
explain分析:
mysql> explain select * from emp where empno = 4999999; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | emp | NULL | const | PRIMARY | PRIMARY | 3 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
耗时0秒，速度比以前快多了。
不在索引列上做任何操作（计算、函数、自动或手动类型转换），会导致索引失效而转向全表扫描。
mysql> select * from emp where empno + 1 = 4999999; +---------+--------+----------+-----+------------+---------+--------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +---------+--------+----------+-----+------------+---------+--------+--------+ | 4999998 | hQLgzb | SALESMAN | 1 | 2021-09-18 | 2000.00 | 400.00 | 337 | +---------+--------+----------+-----+------------+---------+--------+--------+ 1 row in set (2.24 sec)
explain分析
mysql> explain select * from emp where empno + 1 = 4999999; +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 5000000 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
两表查询：
select * from emp left join dept on emp.deptno = dept.deptno where ename = 'Lomsdo';
explain分析：
mysql> explain select * from emp left join dept on emp.deptno = dept.deptno where ename = 'Lomsdo'; +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------------------------------------------+ | 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 5000000 | 10.00 | Using where | | 1 | SIMPLE | dept | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------------------------------------------+ 2 rows in set, 1 warning (0.00 sec)
左连接，左表全表扫，索引加在右表；右连接，右表全表扫，索引加在左表。
给右表即dept表的deptno建立主键索引
alter table dept add primary key (deptno);
再次查询
mysql> explain select * from emp left join dept on emp.deptno = dept.deptno where ename = 'Lomsdo'; +----+-------------+-------+------------+--------+---------------+---------+---------+------------------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+------------------+---------+----------+-------------+ | 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 5000000 | 10.00 | Using where | | 1 | SIMPLE | dept | NULL | eq_ref | PRIMARY | PRIMARY | 3 | data1.emp.deptno | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+------------------+---------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)
发现右表dept,type变为了eq_ref，ref变为了data1.emp.deptno,rows变为了1.
八、缓存优化
1、查询缓存
query_cache_size：作用于整个 MySQL，主要用来缓存MySQL中的ResultSet，也就是一条SQL语句执行的结果集，所以仅仅只能针对select语句。查询缓存从MySQL 5.7.20开始已被弃用，并在MySQL 8.0中被删除。
当我们打开了 Query Cache功能，MySQL在接受到一条select语句的请求后，如果该语句满足QueryCache的要求，MySQL会直接根据预先设定好的HASH算法将接受到的select语句以字符串方式进行hash，然后到Query Cache中直接查找是否已经缓存。如果已经在缓存中，该select请求就会直接将数据返回，从而省略了后面所有的步骤(如SQL语句的解析，优化器优化以及向存储引擎请求数据等)，极大的提高性能。
当然，Query Cache也有一个致命的缺陷，那就是当某个表的数据有任何任何变化，都会导致所有引用了该表的select语句在Query Cache中的缓存数据失效。所以，当我们的数据变化非常频繁的情况下，使用Query Cache可能会得不偿失。
如果缓存命中率非常高的话，有测试表明在极端情况下可以提高效率238%，而在糟糕时，QC会降低系统13%的处理能力。
通过以下命令查看缓存相关变量
show variables like '%query_cache%';
have_query_cache ：表示此版本mysql是否支持缓存
query_cache_limit ：缓存最大值
query_cache_size ：缓存大小
query_cache_type ：off 表示不缓存，on表示缓存所有结果。
在mysql8.0中查询，结果如下：
mysql> show variables like '%query_cache%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | have_query_cache | NO | +------------------+-------+ 1 row in set, 1 warning (0.00 sec)
2、全局缓存
数据库属于IO密集型的应用程序，其主职责就是数据的管理及存储工作。而我们知道，从内存中读取一个数据库的时间是微秒级别，而从一块普通硬盘上读取一个 IO是在毫秒级别，二者相差3个数量级。所以，要优化数据库，首先第一步需要优化的就是IO，尽可能将磁盘IO转化为内存IO,也就是使用缓存
启动MySQL时就要分配并且总是存在的全局缓存，可以在MySQL的my.conf或者my.ini文件的[mysqld]组中配置。查询缓存属于全局缓存。
目前有：
key_buffer_size(默认值：402653184,即384M)、
innodb_buffer_pool_size(默认值：134217728即：128M)、
innodb_additional_mem_pool_size（默认值：8388608即：8M）、
innodb_log_buffer_size(默认值：8388608即：8M)、
query_cache_size(默认值：33554432即：32M)
1） key_buffer_size
用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，对MyISAM表性能影响最大的一个参数。如果你使它太大，系统将开始换页并且真的变慢了。
2） innodb_buffer_pool_size
主要针对InnoDB表性能影响最大的一个参数。功能与Key_buffer_size一样。InnoDB占用的内存，除innodb_buffer_pool_size用于存储页面缓存数据外，另外正常情况下还有大约8%的开销，主要用在每个缓存页帧的描述、adaptive hash等数据结构，如果不是安全关闭，启动时还要恢复的话，还要另开大约12%的内存用于恢复，两者相加就有差不多21%的开销。
3） innodb_additional_mem_pool_size
设置了InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，所以当我们一个MySQL Instance中的数据库对象非常多的时候，是需要适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率的。
4） innodb_log_buffer_size
这是InnoDB存储引擎的事务日志所使用的缓冲区。类似于Binlog Buffer。InnoDB在写事务日志的时候，为了提高性能，也是先将信息写入Innofb Log Buffer中，当满足innodb_flush_log_trx_commit参数所设置的相应条件(或者日志缓冲区写满)之后，才会将日志写到文件(或者同步到磁盘)中。可以通过innodb_log_buffer_size 参数设置其可以使用的最大内存空间。
InnoDB 将日志写入日志磁盘文件前的缓冲大小。理想值为 1M 至 8M。大的日志缓冲允许事务运行时不需要将日志保存入磁盘而只到事务被提交(commit)。因此，如果有大的事务处理，设置大的日志缓冲可以减少磁盘I/O。这个参数实际上还和另外的flush参数相关。一般来说不建议超过32MB。
3、局部缓存
除了全局缓冲，MySql还会为每个连接发放连接缓冲。每个连接到MySQL服务器的线程都需要有自己的缓冲。大概需要立刻分配256K，甚至在线程空闲时，它们使用默认的线程堆栈，网络缓存等。事务开始之后，则需要增加更多的空间。运行较小的查询可能仅给指定的线程增加少量的内存消耗，然而如果对数据表做复杂的操作例如扫描、排序或者需要临时表，则需分配大约read_buffer_size，sort_buffer_size，read_rnd_buffer_size，tmp_table_size大小的内存空间. 不过它们只是在需要的时候才分配，并且在那些操作做完之后就释放了。
1） read_buffer_size
是MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能.
2） sort_buffer_size
是MySql执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能，可以尝试增加sort_buffer_size变量的大小
3） read_rnd_buffer_size
是MySql的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。
4） tmp_table_size
是 MySql的heap （堆积）表缓冲大小。所有联合在一个DML指令内完成，并且大多数联合甚至可以不用临时表即可以完成。大多数临时表是基于内存的(HEAP)表。具有大的记录长度的临时表 (所有列的长度的和)或包含BLOB列的表存储在硬盘上。
如果某个内部heap（堆积）表大小超过tmp_table_size，MySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表。还可以通过设置tmp_table_size选项来增加临时表的大小。也就是说，如果调高该值，MySql同时将增加heap表的大小，可达到提高联接查询速度的效果。
5） record_buffer:
record_buffer每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。
4、其它缓存
1） table_cache
TABLE_CACHE(5.1.3及以后版本又名TABLE_OPEN_CACHE)，table_cache指定表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。
不能盲目地把table_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。
2） thread_cache_size
服务器线程缓存，默认的thread_cache_size=8，,这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能.通过比较Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。
