网摘 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.log
perl: 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.log
Count: 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)
创立 SQL
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))
) 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;
执行查问 sql
mysql> 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=1
show 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: 16384
Max_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/…