网站不排名一切等于零中国推广网

网站不排名一切等于零,中国推广网,网站建设案例 星座,微网站好制作吗1.实验目的理解数据库性能概念#xff0c;练习数据库性能监视命令方法#xff0c;能够对数据库性能进行优化。2.实验内容【实验5-1】使用SHOW语句查询设备吞吐量Questions、Com_Select、Com_insert、Com_update、Com_delete几个指标值。【实验5-2】访问Performance_schema中的…1.实验目的理解数据库性能概念练习数据库性能监视命令方法能够对数据库性能进行优化。2.实验内容【实验5-1】使用SHOW语句查询设备吞吐量Questions、Com_Select、Com_insert、Com_update、Com_delete几个指标值。【实验5-2】访问Performance_schema中的events_statements_summary_by_digest表获取有关的延迟、错误和查询量信息的性能指标。【实验5-3】使用SHOW语句查询连接检查指标Threads_connected、Threads_running、Connection_errors_internal、Aborted_connects和Connection_errors_max_connections。【实验5-4】使用SHOW语句查询Innodb缓冲区指标Innodb_buffer_pool_pages_total、Innodb_buffer_pool_read_requests、Innodb_buffer_pool_reads。【实验5-5】使用SHOW语句获取与查询缓冲相关的指标Qcache_free_blocks、Qcache_free_memory、Qcache_hits、Qcache_inserts、Qcache_lowmem_prunes、Qcache_not_cached、Qcache_queries_in_cache、Qcache_total_blocks。【实验5-6】使用SHOW语句获取关于临时表的指标Created_tmp_disk_tables、Created_tmp_files、Created_tmp_tables。【实验5-7】使用SHOW语句获取访问表的数量指标Open_tables和Opened_tables。【实验5-8】使用SHOW PROCESSLIST命令查询用户正在运行的线程信息协助进行故障诊断。【实验5-9】调出慢查询日志并利用mysqldumpslow来进行日志分析。【实验5-10】使用EXPLAIN EXTENDED命令查看带有UNION子句的SELECT的执行计划。【实验5-11】使用EXPLAIN EXTENDED命令查看如下语句的执行计划SELECT * FROM t1 WHERE a110 AND (EXISTS (SELECT a2 FROM t2 WHERE t2.a25 AND t2.b21) OREXISTS (SELECT a2 FROM t2 WHERE t2.a25 AND t2.b22));【实验5-12】使用EXPLAIN EXTENDED命令查看如下语句的执行计划SELECT * FROM t1, (SELECT * FROM t2 WHERE t2.a2 10) v_t2WHERE t1.a110 AND v_t2.a220;【实验5-13】创建一个表并在适当字段上创建索引对比在大数据量情形下使用索引与不使用索引的性能。【实验5-14】使用PROCEDURE ANALYSE检查表列。【实验5-15】使用Inet_ATON将IP地址192.128.1.1转换为数字再将2130706433转换为IP地址。【实验5-16】进行简单的关联查询代替子查询的重写操作并验证其正确性和执行效率的变化。【实验5-17】查询SQL的最大连接数并修改其至合适的数值。3.实验要求1所有操作均在命令行或者MySQL Workbench中完成。2将操作过程以屏幕抓图的方式复制形成实验文档并对照本章内容写出分析报告。3将操作所使用的命令对应的参数、参数含义、返回的内容、返回内容的含义整理到分析报告中一同给出。4.分析报告本实验通过查询设备的吞吐量、查询性能、连接检查、InnoDB缓存区、查询缓存、临时表以及访问表的数量对于数据库的监视有了更深刻的理解通过explain命令查看执行计划加深了对查询过程的理解有助于查询的优化提升通过PROCEDURE ANALYSE()函数的演示了解了对数据表中的列的数据类型提出优化建议的方法。【实验5-1】使用SHOW语句查询设备吞吐量Questions、Com_Select、Com_insert、Com_update、Com_delete几个指标值。show global status like Com_insert;show global status like Questions;show global status like Com_Select;show global status like Com_update;show global status like Com_delete;【实验5-2】访问Performance_schema中的events_statements_summary_by_digest表获取有关的延迟、错误和查询量信息的性能指标。use performance_schema;select * from events_statements_summary_by_digest;【实验5-3】使用SHOW语句查询连接检查指标Threads_connected、Threads_running、Connection_errors_internal、Aborted_connects和Connection_errors_max_connections。show status like Threads_connected;show status like Threads_running;show status like Connection_errors_internal;show status like Aborted_connects;show status like Connection_errors_max_connections;【实验5-4】使用SHOW语句查询Innodb缓冲区指标Innodb_buffer_pool_pages_total、Innodb_buffer_pool_read_requests、Innodb_buffer_pool_reads。show status like Innodb_buffer_pool_pages_total;show status like Innodb_buffer_pool_read_requests;show status like Innodb_buffer_pool_reads;【实验5-5】使用SHOW语句获取与查询缓冲相关的指标Qcache_free_blocks、Qcache_free_memory、Qcache_hits、Qcache_inserts、Qcache_lowmem_prunes、Qcache_not_cached、Qcache_queries_in_cache、Qcache_total_blocks。show status like Qcache_free_blocks;show status like Qcache_free_memory;show status like Qcache_hits;show status like Qcache_inserts;show status like Qcache_lowmem_prunes;show status like Qcache_not_cached;show status like Qcache_queries_in_cache;show status like Qcache_total_blocks;【实验5-6】使用SHOW语句获取关于临时表的指标Created_tmp_disk_tables、Created_tmp_files、Created_tmp_tables。show status like Created_tmp_disk_tables;show status like Created_tmp_files;show status like Created_tmp_tables;【实验5-7】使用SHOW语句获取访问表的数量指标Open_tables和Opened_tables。show status like Open_tables;show status like Opened_tables;【实验5-8】使用SHOW PROCESSLIST命令查询用户正在运行的线程信息协助进行故障诊断。show processlist;【实验5-9】调出慢查询日志并利用mysqldumpslow来进行日志分析。show variables like %quer%【实验5-10】使用EXPLAIN EXTENDED命令查看带有UNION子句的SELECT的执行计划。select a1 from t1 union select a2 from t2;explain select a1 from t1 union select a2 from t2;【实验5-11】使用EXPLAIN EXTENDED命令查看如下语句的执行计划explain SELECT * FROM t1 WHERE a110 AND (EXISTS (SELECT a2 FROM t2 WHERE t2.a25 AND t2.b21) OREXISTS (SELECT a2 FROM t2 WHERE t2.a25 AND t2.b22));【实验5-12】使用EXPLAIN EXTENDED命令查看如下语句的执行计划explain SELECT * FROM t1, (SELECT * FROM t2 WHERE t2.a2 10) v_t2WHERE t1.a110 AND v_t2.a220;【实验5-13】创建一个表并在适当字段上创建索引对比在大数据量情形下使用索引与不使用索引的性能。select * from t3;select * from t4;create index t3_index on t3(a3);explain select a3 from t3 where a32 and a38;explain select a4 from t4 where a42 and a48;【实验5-14】使用PROCEDURE ANALYSE检查表列。select * from t1 procedure analyse();【实验5-15】使用Inet_ATON将IP地址192.128.1.1转换为数字再将2130706433转换为IP地址。select inet_aton(192.168.1.1);select inet_ntoa(2130706433);【实验5-16】进行简单的关联查询代替子查询的重写操作并验证其正确性和执行效率的变化。select * from t1 where t1.a1 in (select a2 from t2);select distinct t1.a1,t1.b1 from t1 join t2 on t1.a1t2.a2 where t1.a1t2.a2;explain select * from t1 where t1.a1 in (select a2 from t2);explain select distinct t1.a1,t1.b1 from t1 join t2 on t1.a1t2.a2 where t1.a1t2.a2;【实验5-17】查询SQL的最大连接数并修改其至合适的数值。show variables like max_connections;set global max_connections120;【实验5-1】使用SHOW语句查询设备吞吐量Questions、Com_Select、Com_insert、Com_update、Com_delete几个指标值。 show global status like Com_insert; show global status like Questions; show global status like Com_Select; show global status like Com_update; show global status like Com_delete; 【实验5-2】访问Performance_schema中的events_statements_summary_by_digest表获取有关的延迟、错误和查询量信息的性能指标。 use performance_schema; select * from events_statements_summary_by_digest; 【实验5-3】使用SHOW语句查询连接检查指标Threads_connected、Threads_running、Connection_errors_internal、Aborted_connects和Connection_errors_max_connections。 show status like Threads_connected; show status like Threads_running; show status like Connection_errors_internal; show status like Aborted_connects; show status like Connection_errors_max_connections; 【实验5-4】使用SHOW语句查询Innodb缓冲区指标Innodb_buffer_pool_pages_total、Innodb_buffer_pool_read_requests、Innodb_buffer_pool_reads。 show status like Innodb_buffer_pool_pages_total; show status like Innodb_buffer_pool_read_requests; show status like Innodb_buffer_pool_reads; 【实验5-5】使用SHOW语句获取与查询缓冲相关的指标Qcache_free_blocks、Qcache_free_memory、Qcache_hits、Qcache_inserts、Qcache_lowmem_prunes、Qcache_not_cached、Qcache_queries_in_cache、Qcache_total_blocks。 show status like Qcache_free_blocks; show status like Qcache_free_memory; show status like Qcache_hits; show status like Qcache_inserts; show status like Qcache_lowmem_prunes; show status like Qcache_not_cached; show status like Qcache_queries_in_cache; show status like Qcache_total_blocks; 【实验5-6】使用SHOW语句获取关于临时表的指标Created_tmp_disk_tables、Created_tmp_files、Created_tmp_tables。 show status like Created_tmp_disk_tables; show status like Created_tmp_files; show status like Created_tmp_tables; 【实验5-7】使用SHOW语句获取访问表的数量指标Open_tables和Opened_tables。 show status like Open_tables; show status like Opened_tables; 【实验5-8】使用SHOW PROCESSLIST命令查询用户正在运行的线程信息协助进行故障诊断。 show processlist; 【实验5-9】调出慢查询日志并利用mysqldumpslow来进行日志分析。 show variables like %quer% 【实验5-10】使用EXPLAIN EXTENDED命令查看带有UNION子句的SELECT的执行计划。 select a1 from t1 union select a2 from t2; explain select a1 from t1 union select a2 from t2; 【实验5-11】使用EXPLAIN EXTENDED命令查看如下语句的执行计划 explain SELECT * FROM t1 WHERE a110 AND ( EXISTS (SELECT a2 FROM t2 WHERE t2.a25 AND t2.b21) OR EXISTS (SELECT a2 FROM t2 WHERE t2.a25 AND t2.b22)); 【实验5-12】使用EXPLAIN EXTENDED命令查看如下语句的执行计划 explain SELECT * FROM t1, (SELECT * FROM t2 WHERE t2.a2 10) v_t2 WHERE t1.a110 AND v_t2.a220; 【实验5-13】创建一个表并在适当字段上创建索引对比在大数据量情形下使用索引与不使用索引的性能。 select * from t3; select * from t4; create index t3_index on t3(a3); explain select a3 from t3 where a32 and a38; explain select a4 from t4 where a42 and a48; 【实验5-14】使用PROCEDURE ANALYSE检查表列。 select * from t1 procedure analyse(); 【实验5-15】使用Inet_ATON将IP地址192.128.1.1转换为数字再将2130706433转换为IP地址。 select inet_aton(192.168.1.1); select inet_ntoa(2130706433); 【实验5-16】进行简单的关联查询代替子查询的重写操作并验证其正确性和执行效率的变化。 select * from t1 where t1.a1 in (select a2 from t2); select distinct t1.a1,t1.b1 from t1 join t2 on t1.a1t2.a2 where t1.a1t2.a2; explain select * from t1 where t1.a1 in (select a2 from t2); explain select distinct t1.a1,t1.b1 from t1 join t2 on t1.a1t2.a2 where t1.a1t2.a2; 【实验5-17】查询SQL的最大连接数并修改其至合适的数值。 show variables like max_connections; set global max_connections120;本文为个人学习笔记与经验总结仅供学习交流参考