网摘sql优化口诀
全值匹配我最爱，最左前缀要恪守 带头大哥不能死，两头兄弟不能断 索引列上少计算，范畴之后全生效 LIKE符号写最右，笼罩索引不写星 不等空值还有or，索引生效要少用 var引号不能丢，SQL高级也不难 分组之前必排序，肯定要上索引啊案例:千万级数据应用limit实现分页优化
https://www.jianshu.com/p/c62...
1.应用笼罩索引
CREATE TABLE `user` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(32) NOT NULL DEFAULT '' COMMENT '姓名', `age` tinyint(4) NOT NULL COMMENT '年纪', `sex` tinyint(4) NOT NULL COMMENT '性别', `email` varchar(32) NOT NULL DEFAULT '' COMMENT '邮箱', `ctime` int(11) NOT NULL COMMENT '创立工夫', PRIMARY KEY (`id`), KEY `index_email` (`email`(7)), KEY `index_id_email` (`id`,`email`)) ENGINE=InnoDB AUTO_INCREMENT=131604 DEFAULT CHARSET=utf8mb4;mysql> select SQL_NO_CACHE id,email from user limit 100000,10;+--------+-------------+| id | email |+--------+-------------+| 100001 | [email protected] || 100002 | [email protected] || 100003 | [email protected] || 100004 | [email protected] || 100005 | [email protected] || 100006 | [email protected] || 100007 | [email protected] || 100008 | [email protected] || 100009 | [email protected] || 100010 | [email protected] |+--------+-------------+10 rows in set, 1 warning (0.02 sec) mysql> select SQL_NO_CACHE id,email,name from user limit 100000,10;+--------+-------------+-------+| id | email | name |+--------+-------------+-------+| 100001 | [email protected] | 张xx || 100002 | [email protected] | 张xx || 100003 | [email protected] | 张xx || 100004 | [email protected] | 张xx || 100005 | [email protected] | 张xx || 100006 | [email protected] | 张xx || 100007 | [email protected] | 张xx || 100008 | [email protected] | 张xx || 100009 | [email protected] | 张xx || 100010 | [email protected] | 张xx |+--------+-------------+-------+10 rows in set, 1 warning (0.03 sec) mysql> explain select SQL_NO_CACHE id,email from user limit 100000,10;+----+-------------+-------+------------+-------+---------------+----------------+---------+------+--------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+----------------+---------+------+--------+----------+-------------+| 1 | SIMPLE | user | NULL | index | NULL | index_id_email | 134 | NULL | 131052 | 100.00 | Using index |+----+-------------+-------+------------+-------+---------------+----------------+---------+------+--------+----------+-------------+1 row in set, 2 warnings (0.01 sec) mysql> explain select SQL_NO_CACHE id,email,name from user limit 100000,10;+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 131052 | 100.00 | NULL |+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+1 row in set, 2 warnings (0.00 sec) Using index:应用到了笼罩索引 mysql> show profiles;+----------+------------+---------------------------------------------------------------------+| Query_ID | Duration | Query |+----------+------------+---------------------------------------------------------------------+| 204 | 0.00040800 | select SQL_NO_CACHE id,email from user limit 100,10 || 205 | 0.00039600 | select SQL_NO_CACHE id,email from user limit 100,10 || 206 | 0.00645100 | explain select SQL_NO_CACHE id,email from user limit 100,10 || 207 | 0.00036400 | explain select SQL_NO_CACHE email from user limit 100,10 || 208 | 0.00053500 | explain select SQL_NO_CACHE id,email,name from user limit 100,10 || 209 | 0.00698800 | select SQL_NO_CACHE id,email from user limit 100,10 || 210 | 0.00067400 | select SQL_NO_CACHE id,email,name from user limit 100,10 || 211 | 0.00045100 | select SQL_NO_CACHE id,email,name from user limit 100,10 || 212 | 0.00032900 | select SQL_NO_CACHE id,email from user limit 100,10 || 213 | 0.02265800 | select SQL_NO_CACHE id,email from user limit 100000,10 || 214 | 0.03214000 | select SQL_NO_CACHE id,email,name from user limit 100000,10 || 215 | 0.00046200 | explain select SQL_NO_CACHE id,email from user limit 100000,10 || 216 | 0.00040000 | explain select SQL_NO_CACHE id,email,name from user limit 100000,10 || 217 | 0.01920500 | select SQL_NO_CACHE id,email from user limit 100000,10 || 218 | 0.03084100 | select SQL_NO_CACHE id,email,name from user limit 100000,10 |+----------+------------+---------------------------------------------------------------------+察看 query_id是217和218的两条sql,就是方才执行得出的,工夫失去显著晋升,且用到了index_id_email索引,应用到笼罩索引，
数据间接从索引中就能够获取到,不须要回表从主键索引里获取数据, 笼罩索引是指，索引上的信息足够满足查问申请，不须要再回到主键索引上去取数据。因为笼罩索引能够缩小树的搜寻次数，显著晋升查问性能，所以应用笼罩索引是一个罕用的性能优化伎俩。
应用SQL_NO_CACHE强制不走mysql查问缓存
2.应用 id>=的模式:
select SQL_NO_CACHE * from user where id>=(select id from user limit 10000, 1) limit 10;
连贯（JOIN）.. 之所以更有效率一些，是因为 MySQL不须要在内存中创立长期表来实现这个逻辑上的须要两个步骤的查问工作
mysql> explain select SQL_NO_CACHE * from user where id>=(select id from user limit 10000, 1) limit 10;+----+-------------+-------+------------+-------+------------------------+-------------+---------+------+--------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+------------------------+-------------+---------+------+--------+----------+-------------+| 1 | PRIMARY | user | NULL | range | PRIMARY,index_id_email | PRIMARY | 4 | NULL | 65526 | 100.00 | Using where || 2 | SUBQUERY | user | NULL | index | NULL | index_email | 30 | NULL | 131052 | 100.00 | Using index |+----+-------------+-------+------------+-------+------------------------+-------------+---------+------+--------+----------+-------------+2 rows in set, 2 warnings (0.00 sec)从慢查问日志动手开始搞！！！
1.开启慢查问mysql> set slow_query_log=1;ERROR 1229 (HY000): Variable 'slow_query_log' is a GLOBAL variable and should be set with SET GLOBA开启全局慢查问日志mysql> set global slow_query_log=1;Query OK, 0 rows affected (0.00 sec)查看是否开启慢查问日志mysql> show variables like "slow_query_log";+----------------+-------+| Variable_name | Value |+----------------+-------+| slow_query_log | ON |+----------------+-------+1 row in set (0.00 sec)查看慢查问入日志文件地位mysql> show variables like "slow_query_log_file";+---------------------+--------------------------------------+| Variable_name | Value |+---------------------+--------------------------------------+| slow_query_log_file | /usr/local/var/mysql/itech8-slow.log |+---------------------+--------------------------------------+1 row in set (0.01 sec)查看慢查问日志的工夫设置,默认是10s内的查问则为慢查问 mysql> show variables like '%long_query_time%';+-----------------+-----------+| Variable_name | Value |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+1 row in set (0.01 sec) 设置查问工夫1s内的记录为慢查问日志mysql> set long_query_time=1;Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%long_query_time%';+-----------------+----------+| Variable_name | Value |+-----------------+----------+| long_query_time | 1.000000 |+-----------------+----------+设置日期过期天数mysql> set global expire_logs_days=30;Query OK, 0 rows affected (0.00 sec)应用mysqldumpslow工具剖析慢查问日志
失去返回记录集最多的10个SQL
mysqldumpslow –s r –t 10 /data/logs/mysql/slowquery.log
失去拜访次数最多的10个SQL
mysqldumpslow –s c –t 10 /data/logs/mysql/slowquery.log
失去依照工夫排序的前10条外面含有左连贯的查问
mysqldumpslow –s t –t 10 –g "left join" /data/logs/mysql/slowquery.log
另外倡议在应用这些命令时联合|和more应用，否则可能呈现爆破状况
mysqldumpslow –s r –t 10 /data/logs/mysql/slowquery.log|more
参数含意
s: 示意依照何种形式排序
c：拜访次数
l：锁定工夫
r：返回记录
t：查问工夫
al：均匀锁定工夫
t：返回后面多少条的数据
g：前面搭配一个正则表达式
[email protected] ~]# mysqldumpslow -s -r -t 2 /data/logs/mysql/slowquery.logperl: warning: Setting locale failed.perl: warning: Please check that your locale settings: LANGUAGE = (unset), LC_ALL = (unset), LC_CTYPE = "UTF-8", LANG = "en_US.UTF-8" are supported and installed on your system.perl: warning: Falling back to the standard locale ("C"). Reading mysql slow query log from /data/logs/mysql/slowquery.logCount: 1 Time=5.19s (5s) Lock=3.02s (3s) Rows=0.0 (0), root[root]@localhost DELETE FROM `wp_options` WHERE `option_name` = 'S' Count: 1 Time=2.82s (2s) Lock=0.20s (0s) Rows=4064.0 (4064), root[root]@localhost SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME, COLUMN_TYPE, IS_NULLABLE, EXTRA FROM INFORMATION_SCHEMA.COLUMNS where name like "jack" 2.应用show profiles剖析SQL性能 mysql> select version();+-----------+| version() |+-----------+| 5.7.22 |+-----------+1 row in set (0.01 sec) 创立SQLCREATE TABLE `user` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(32) NOT NULL DEFAULT '' COMMENT '姓名', `age` tinyint(4) NOT NULL COMMENT '年纪', `sex` tinyint(4) NOT NULL COMMENT '性别', `email` varchar(32) NOT NULL DEFAULT '' COMMENT '邮箱', `ctime` int(11) NOT NULL COMMENT '创立工夫', PRIMARY KEY (`id`), KEY `index_email` (`email`(7))) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4; INSERT INTO `user` (`id`, `name`, `age`, `sex`, `email`, `ctime`)VALUES (1, '张三', 12, 1, '[email protected]', 1554789573), (2, '李四', 34, 2, '[email protected]', 1554789573), (3, '王五', 90, 1, '[email protected]', 1554789573); mysql> show variables like "%profiling%";+------------------------+-------+| Variable_name | Value |+------------------------+-------+| have_profiling | YES || profiling | ON || profiling_history_size | 15 |+------------------------+-------+ mysql> set profiling=1; 执行查问sqlmysql> select * from user where email='[email protected]';+----+--------+-----+-----+-----------------+------------+| id | name | age | sex | email | ctime |+----+--------+-----+-----+-----------------+------------+| 1 | 张三 | 12 | 1 | [email protected] | 1554789573 |+----+--------+-----+-----+-----------------+------------+1 row in set (0.00 sec) 查看最近一条sql耗时ysql> show profile;+----------------------+----------+| Status | Duration |+----------------------+----------+| starting | 0.000124 || checking permissions | 0.000012 || Opening tables | 0.000017 || init | 0.000039 || System lock | 0.000010 || optimizing | 0.000015 || statistics | 0.000060 || preparing | 0.000016 || executing | 0.000005 || Sending data | 0.000045 || end | 0.000007 || query end | 0.000009 || closing tables | 0.000009 || freeing items | 0.000023 || cleaning up | 0.000012 |+----------------------+----------+15 rows in set, 1 warning (0.00 sec) mysql> show profiles;+----------+------------+------------------------------------------------------+| Query_ID | Duration | Query |+----------+------------+------------------------------------------------------+| 4 | 0.00786300 | show tables || 5 | 0.00459300 | show create table user || 6 | 0.00554100 | select * from user || 7 | 0.01527800 | select * from user where email='[email protected]' || 8 | 0.00891700 | show profiles cpu,block,io for query 7 || 9 | 0.00007800 | show profile cpu,block,io for query 7 || 10 | 0.00009000 | select * from user where email='[email protected]'； || 11 | 0.00051500 | select * from user where email='[email protected]' || 12 | 0.00009200 | set profiling=1show profiles || 13 | 0.00913900 | show variables like "profile" || 14 | 0.01532300 | show variables like "%profile%" || 15 | 0.00814100 | show variables like "%profiles%" || 16 | 0.00808300 | show variables like "%profiling%" || 17 | 0.00957700 | show variables like "profiling" || 18 | 0.00040300 | select * from user where email='[email protected]' |+----------+------------+------------------------------------------------------+15 rows in set, 1 warning (0.00 sec) 查看指定的一条sql,获取cpu和block io的工夫耗费mysql> show profile cpu,block io for query 18;+----------------------+----------+----------+------------+--------------+---------------+| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |+----------------------+----------+----------+------------+--------------+---------------+| starting | 0.000124 | 0.000073 | 0.000052 | 0 | 0 || checking permissions | 0.000012 | 0.000006 | 0.000005 | 0 | 0 || Opening tables | 0.000017 | 0.000015 | 0.000002 | 0 | 0 || init | 0.000039 | 0.000037 | 0.000001 | 0 | 0 || System lock | 0.000010 | 0.000009 | 0.000002 | 0 | 0 || optimizing | 0.000015 | 0.000013 | 0.000002 | 0 | 0 || statistics | 0.000060 | 0.000059 | 0.000001 | 0 | 0 || preparing | 0.000016 | 0.000013 | 0.000003 | 0 | 0 || executing | 0.000005 | 0.000003 | 0.000002 | 0 | 0 || Sending data | 0.000045 | 0.000044 | 0.000001 | 0 | 0 || end | 0.000007 | 0.000004 | 0.000003 | 0 | 0 || query end | 0.000009 | 0.000007 | 0.000001 | 0 | 0 || closing tables | 0.000009 | 0.000008 | 0.000002 | 0 | 0 || freeing items | 0.000023 | 0.000013 | 0.000010 | 0 | 0 || cleaning up | 0.000012 | 0.000011 | 0.000009 | 0 | 0 |+----------------------+----------+----------+------------+--------------+---------------+15 rows in set, 1 warning (0.00 sec) ALL --- 显示所有的开销信息BLOCK IO --- 显示块IO相干开销CONTEXT SWITCHES --- 上下文切换相干开销CPU --- 显示CPU相干开销信息IPC --- 显示发送和承受相干开销信息 MEMORY --- 显示内存相干开销信息PAGE FAULTS --- 显示页面谬误相干开销信息SOURCE --- 显示和Source_function , Source_file , Source_line相干的开销信息SWAPS --- 显示替换次数相干开销的信息 查看告警是啥?mysql> show warnings;+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message |+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+| Error | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'warning' at line 1 |+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)3.应用Explain
https://blog.csdn.net/zsx1573...
查看mysql 日志信息
mysql> show variables like "%log%";+-----------------------------------------+--------------------------------+| Variable_name | Value |+-----------------------------------------+--------------------------------+| back_log | 80 || binlog_cache_size | 32768 || binlog_checksum | CRC32 || binlog_direct_non_transactional_updates | OFF || binlog_error_action | IGNORE_ERROR || binlog_format | MIXED || binlog_gtid_simple_recovery | OFF || binlog_max_flush_queue_time | 0 || binlog_order_commits | ON || binlog_row_image | FULL || binlog_rows_query_log_events | OFF || binlog_stmt_cache_size | 32768 || binlogging_impossible_mode | IGNORE_ERROR || expire_logs_days | 30 || general_log | OFF || general_log_file | /data/mysql/iZ257v6fpmnZ.log || innodb_api_enable_binlog | OFF || innodb_flush_log_at_timeout | 1 || innodb_flush_log_at_trx_commit | 1 # 在事务提交时innodb是否同步日志从缓冲到文件中1示意事务以提交就同步不提交每隔一秒同步一次，性能会很差造成大量的磁盘I/O；定义为2示意只有在事务提交时才会同步然而可能会失落整个事务 || innodb_locks_unsafe_for_binlog | OFF || innodb_log_buffer_size | 8388608 || innodb_log_compressed_pages | ON || innodb_log_file_size | 50331648 || innodb_log_files_in_group | 2 || innodb_log_group_home_dir | ./ || innodb_mirrored_log_groups | 1 || innodb_online_alter_log_max_size | 134217728 || innodb_undo_logs | 128 || log_bin | ON || log_bin_basename | /data/mysql/mysql-bin || log_bin_index | /data/mysql/mysql-bin.index || log_bin_trust_function_creators | OFF || log_bin_use_v1_row_events | OFF || log_error | /data/logs/mysql/error.log || log_output | FILE || log_queries_not_using_indexes | OFF || log_slave_updates | OFF || log_slow_admin_statements | OFF || log_slow_slave_statements | OFF || log_throttle_queries_not_using_indexes | 0 || log_warnings | 1 || max_binlog_cache_size | 18446744073709547520 || max_binlog_size | 1073741824 || max_binlog_stmt_cache_size | 18446744073709547520 || max_relay_log_size | 0 || relay_log | || relay_log_basename | || relay_log_index | || relay_log_info_file | relay-log.info || relay_log_info_repository | FILE || relay_log_purge | ON || relay_log_recovery | OFF || relay_log_space_limit | 0 || simplified_binlog_gtid_recovery | OFF || slow_query_log | ON || slow_query_log_file | /data/logs/mysql/slowquery.log || sql_log_bin | ON || sql_log_off | OFF || sync_binlog | 0 || sync_relay_log | 10000 || sync_relay_log_info | 10000 |+-----------------------------------------+--------------------------------+mysql的server端日志
1.error_log 谬误日志 2 . binlog 二进制日志 3.general_log 全局查问日志 4. slow_query_log 慢查问日志 5.relay_log 中继日志
存储引擎端日志
以innodb为例:
mysql> show table status from test where name='user'\G*************************** 1. row *************************** Name: user Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 303 Avg_row_length: 54 Data_length: 16384Max_data_length: 0 Index_length: 16384 Data_free: 0 Auto_increment: 304 Create_time: 2019-04-13 23:13:55 Update_time: 2019-04-13 23:15:47 Check_time: NULL Collation: utf8mb4_general_ci Checksum: NULL Create_options: Comment:1 row in set (0.00 sec)事务日志
redo log/undo log
物理文件:.frm 表构造 索引和数据在一起: 第一种, .ibdata1 共享表空间的信息共享表 ,第二种, 独享表空间table.iba
https://www.cnblogs.com/clsn/...