mysql-运维sql
查询表的大小
SELECT table_name,
CONCAT(FORMAT(data_length / 1024 / 1024, 2), 'M') AS dbdata_size,
CONCAT(FORMAT(index_length / 1024 / 1024, 2), 'M') AS dbindex_size,
CONCAT( FORMAT((data_length + index_length) / 1024 / 1024 / 1024,2), 'G') AS `db_size(G)`, AVG_ROW_LENGTH,
table_rows,
update_time
FROM
information_schema.tables
WHERE table_name = 'admin';
查询表的索引
SELECT * FROM information_schema.STATISTICS
WHERE
TABLE_SCHEMA="test" AND table_name="admin";TABLE_SCHEMA 为库名
查询视图
SELECT * FROM information_schema.VIEWS;查询触发器
SELECT * FROM information_schema.TRIGGERS;查询存储过程和函数的信息
SELECT * FROM information_schema.ROUTINES WHERE routine_schema="test";routine_schema 为库名
查询正在执行的事务
SELECT * FROM information_schema.INNODB_TRX ORDER BY trx_query;
查询正在执行的线程
# 查看当前数据库中哪些线程正在执行
(SHOW PROCESSLIST;只列出前100条，SHOW FULL PROCESSLIST;列出全部。)
SHOW FULL PROCESSLIST ;# 查询正在执行的线程，按照时间倒序
SELECT * FROM information_schema.`PROCESSLIST`WHERE command != "Sleep" ORDER BY time DESC;# 查询正在执行的线程（先根据info统计，再排序）
SELECT info , count(*) FROM information_schema.`PROCESSLIST`
WHERE command != "Sleep" GROUP BY info ORDER BY info ;
该方法可以排查哪些业务sql引起CPU升高（业务高峰期时，会有很多的insert，update操作，每次执行时，如果同样的sql过多，要考虑是否是正常的用户业务引起。。如果不是，则可以考虑是否延时执行。。如，业务高峰期时，从第三方同步大量的商品数据，insert会导致cpu飙升，此时可以把该时段的任务关闭）
sql分析
sql分析常用的配置参数
排序相关参数配置
order by排序原理
## 排序
SHOW VARIABLES like "sort_buffer_size%"; # 默认2M大小 2097152字节#设置buffer_size
SET sort_buffer_size=16*1024;## 查看sort相关的配置 Sort_merge_passes 归并排序的次数（文件排序）
show STATUS like "sort%";## 每行最大的长度（每行数据小于该长度，使用全字段排序；否则使用rowId排序，然后回表再查询）
show VARIABLES LIKE "max_length_for_sort_data";## 查询创建了多少个的临时文件表
SHOW STATUS LIKE 'Created_tmp%';optimizer_trace方式
## sql分析
/* 打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; /* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = 'Innodb_rows_read';/* 执行语句 */
SELECT id,username,`password`,extra FROM admin ORDER BY username ASC LIMIT 1000;/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`;/* @b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';/* 计算Innodb_rows_read差值 扫描行 */
select @b-@a;案例执行结果：
{"steps": [{"join_preparation": {"select#": 1,"steps": [{"expanded_query": "/* select#1 */ select `admin`.`id` AS `id`,`admin`.`username` AS `username`,`admin`.`password` AS `password`,`admin`.`extra` AS `extra` from `admin` order by `admin`.`username` limit 1000"}]}},{"join_optimization": {"select#": 1,"steps": [{"substitute_generated_columns": {}},{"table_dependencies": [{"table": "`admin`","row_may_be_null": false,"map_bit": 0,"depends_on_map_bits": []}]},{"rows_estimation": [{"table": "`admin`","table_scan": {"rows": 100025,"cost": 72.25}}]},{"considered_execution_plans": [{"plan_prefix": [],"table": "`admin`","best_access_path": {"considered_access_paths": [{"rows_to_scan": 100025,"access_type": "scan","resulting_rows": 100025,"cost": 10074.8,"chosen": true}]},"condition_filtering_pct": 100,"rows_for_plan": 100025,"cost_for_plan": 10074.8,"chosen": true}]},{"attaching_conditions_to_tables": {"original_condition": null,"attached_conditions_computation": [],"attached_conditions_summary": [{"table": "`admin`","attached": null}]}},{"optimizing_distinct_group_by_order_by": {"simplifying_order_by": {"original_clause": "`admin`.`username`","items": [{"item": "`admin`.`username`"}],"resulting_clause_is_simple": true,"resulting_clause": "`admin`.`username`"}}},{"finalizing_table_conditions": []},{"refine_plan": [{"table": "`admin`"}]},{"considering_tmp_tables": [{"adding_sort_to_table": "admin"}]}]}},{"join_execution": {"select#": 1,"steps": [{"sorting_table": "admin","filesort_information": [{"direction": "asc","expression": "`admin`.`username`"}],"filesort_priority_queue_optimization": {"limit": 1000,"chosen": true},"filesort_execution": [],"filesort_summary": {"memory_available": 2097152,"key_size": 514,"row_size": 514,"max_rows_per_buffer": 1001,"num_rows_estimate": 100025,"num_rows_found": 100000,"num_initial_chunks_spilled_to_disk": 0,"peak_memory_used": 522522,"sort_algorithm": "std::stable_sort","unpacked_addon_fields": "using_priority_queue","sort_mode": "<fixed_sort_key, rowid>"}}]}}]
}EXPLAIN
EXPLAIN
SELECT id,username,`password`,extra FROM admin
ORDER BY username ASC LIMIT 1000;
EXPLAIN FORMAT=JSON
EXPLAIN FORMAT=JSON
SELECT id,username,`password`,extra FROM admin
ORDER BY username ASC LIMIT 1000;
{"query_block": {"select_id": 1,"cost_info": {"query_cost": "10074.75" -- 查询的预计成本，这个成本是一个相对值，用于比较不同查询计划的开销},"ordering_operation": {"using_filesort": true,"table": {"table_name": "admin","access_type": "ALL", -- MySQL访问表的方式，all全表扫描，index使用索引 等等"rows_examined_per_scan": 100025, -- 需要扫描的行"rows_produced_per_join": 100025, "filtered": "100.00","cost_info": {"read_cost": "72.25","eval_cost": "10002.50","prefix_cost": "10074.75","data_read_per_join": "196M"},"used_columns": ["id","username","password","extra"]}}}
}