首页 > 代码库 > mysql优化之sakila测试数据库
mysql优化之sakila测试数据库
下载地址，选择相应的版本来进行安装测试
http://dev.mysql.com/doc/index-other.html
相关说明
http://dev.mysql.com/doc/sakila/en/sakila-installation.html
outous-MacBook-Pro:~ wangtao$ mysql -uroot -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 16Server version: 5.6.20 MySQL Community Server (GPL)Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> select @@version;+-----------+| @@version |+-----------+| 5.6.20 |+-----------+1 row in set (0.00 sec)mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || apphomebiz || mysql || performance_schema || test |+--------------------+6 rows in set (0.01 sec)mysql> source /Users/wangtao/Downloads/sakila-db/sakila-schema.sqlQuery OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.01 sec)Query OK, 1 row affected (0.00 sec)Database changedQuery OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.02 sec)Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> show tables;+----------------------------+| Tables_in_sakila |+----------------------------+| actor || actor_info || address || category || city || country || customer || customer_list || film || film_actor || film_category || film_list || film_text || inventory || language || nicer_but_slower_film_list || payment || rental || sales_by_film_category || sales_by_store || staff || staff_list || store |+----------------------------+23 rows in set (0.00 sec)mysql> source /Users/wangtao/Downloads/sakila-db/sakila-data.sqlQuery OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Database changedQuery OK, 0 rows affected (0.00 sec)Query OK, 200 rows affected (0.00 sec)Records: 200 Duplicates: 0 Warnings: 0Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 603 rows affected (0.00 sec)Records: 603 Duplicates: 0 Warnings: 0Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 16 rows affected (0.00 sec)Records: 16 Duplicates: 0 Warnings: 0Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 600 rows affected (0.00 sec)Records: 600 Duplicates: 0 Warnings: 0Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 109 rows affected (0.00 sec)Records: 109 Duplicates: 0 Warnings: 0Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 599 rows affected (0.02 sec)Records: 599 Duplicates: 0 Warnings: 0Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 1000 rows affected (0.13 sec)Records: 1000 Duplicates: 0 Warnings: 0Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 5462 rows affected (0.04 sec)Records: 5462 Duplicates: 0 Warnings: 0Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 1000 rows affected (0.01 sec)Records: 1000 Duplicates: 0 Warnings: 0Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 4581 rows affected (0.04 sec)Records: 4581 Duplicates: 0 Warnings: 0Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.01 sec)Query OK, 6 rows affected (0.00 sec)Records: 6 Duplicates: 0 Warnings: 0Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 14885 rows affected (0.24 sec)Records: 14885 Duplicates: 0 Warnings: 0Query OK, 1164 rows affected (0.01 sec)Records: 1164 Duplicates: 0 Warnings: 0Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 12377 rows affected (0.21 sec)Records: 12377 Duplicates: 0 Warnings: 0Query OK, 3667 rows affected (0.05 sec)Records: 3667 Duplicates: 0 Warnings: 0Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.02 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 2 rows affected (0.00 sec)Records: 2 Duplicates: 0 Warnings: 0Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 2 rows affected (0.00 sec)Records: 2 Duplicates: 0 Warnings: 0Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> use sakila;Database changedmysql> show tables;+----------------------------+| Tables_in_sakila |+----------------------------+| actor || actor_info || address || category || city || country || customer || customer_list || film || film_actor || film_category || film_list || film_text || inventory || language || nicer_but_slower_film_list || payment || rental || sales_by_film_category || sales_by_store || staff || staff_list || store |+----------------------------+23 rows in set (0.00 sec)mysql> select * from store;+----------+------------------+------------+---------------------+| store_id | manager_staff_id | address_id | last_update |+----------+------------------+------------+---------------------+| 1 | 1 | 1 | 2006-02-15 04:57:12 || 2 | 2 | 2 | 2006-02-15 04:57:12 |+----------+------------------+------------+---------------------+2 rows in set (0.00 sec)mysql> select count(*) from film;+----------+| count(*) |+----------+| 1000 |+----------+1 row in set (0.00 sec)mysql> select count(*) from film_text;+----------+| count(*) |+----------+| 1000 |+----------+1 row in set (0.00 sec)mysql> show variables like ‘slow_query_log‘;+----------------+-------+| Variable_name | Value |+----------------+-------+| slow_query_log | OFF |+----------------+-------+1 row in set (0.01 sec)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_format | STATEMENT || 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 | 0 || general_log | OFF || general_log_file | /usr/local/mysql/data/outous-MacBook-Pro.log || innodb_api_enable_binlog | OFF || innodb_flush_log_at_timeout | 1 || innodb_flush_log_at_trx_commit | 1 || 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 | OFF || log_bin_basename | || log_bin_index | || log_bin_trust_function_creators | OFF || log_bin_use_v1_row_events | OFF || log_error | /usr/local/mysql/data/outous-MacBook-Pro.local.err || 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 || slow_query_log | OFF || slow_query_log_file | /usr/local/mysql/data/outous-MacBook-Pro-slow.log || sql_log_bin | ON || sql_log_off | OFF || sync_binlog | 0 || sync_relay_log | 10000 || sync_relay_log_info | 10000 |+-----------------------------------------+----------------------------------------------------+58 rows in set (0.00 sec)mysql> set global log_queries_not_using_indexes=on;Query OK, 0 rows affected (0.00 sec)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_format | STATEMENT || 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 | 0 || general_log | OFF || general_log_file | /usr/local/mysql/data/outous-MacBook-Pro.log || innodb_api_enable_binlog | OFF || innodb_flush_log_at_timeout | 1 || innodb_flush_log_at_trx_commit | 1 || 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 | OFF || log_bin_basename | || log_bin_index | || log_bin_trust_function_creators | OFF || log_bin_use_v1_row_events | OFF || log_error | /usr/local/mysql/data/outous-MacBook-Pro.local.err || log_output | FILE || log_queries_not_using_indexes | ON || 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 || slow_query_log | OFF || slow_query_log_file | /usr/local/mysql/data/outous-MacBook-Pro-slow.log || sql_log_bin | ON || sql_log_off | OFF || sync_binlog | 0 || sync_relay_log | 10000 || sync_relay_log_info | 10000 |+-----------------------------------------+----------------------------------------------------+58 rows in set (0.00 sec)
#生产环境中，这个时间设置的过短的话，短时间内会产生大量的日志文件，可能造成磁盘写满mysql> show variables like ‘long_query_time‘;+-----------------+-----------+| Variable_name | Value |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+1 row in set (0.00 sec)mysql> set global slow_query_log=on;Query OK, 0 rows affected (0.01 sec)mysql> use sakila;Database changedmysql> select * from store limit 10;+----------+------------------+------------+---------------------+| store_id | manager_staff_id | address_id | last_update |+----------+------------------+------------+---------------------+| 1 | 1 | 1 | 2006-02-15 04:57:12 || 2 | 2 | 2 | 2006-02-15 04:57:12 |+----------+------------------+------------+---------------------+2 rows in set (0.01 sec)mysql> show variables linke "slow%";ERROR 1064 (42000): 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 ‘linke "slow%"‘ at line 1mysql> show variables like "slow%";+---------------------+---------------------------------------------------+| Variable_name | Value |+---------------------+---------------------------------------------------+| slow_launch_time | 2 || slow_query_log | ON || slow_query_log_file | /usr/local/mysql/data/outous-MacBook-Pro-slow.log |+---------------------+---------------------------------------------------+3 rows in set (0.00 sec)mysql>
查看保存的日志
/usr/local/mysql/data/outous-MacBook-Pro-slow.log
outous-MacBook-Pro:~ wangtao$ sudo cat /usr/local/mysql/data/outous-MacBook-Pro-slow.logPassword:/usr/local/mysql/bin/mysqld, Version: 5.6.20 (MySQL Community Server (GPL)). started with:Tcp port: 3306 Unix socket: /tmp/mysql.sockTime Id Command Argument# Time: 141217 22:41:51
#执行sql的主机信息
# User@Host: root[root] @ localhost [] Id: 16
#sql的执行信息，扫描的行数，发送的行数
# Query_time: 0.000181 Lock_time: 0.000072 Rows_sent: 2 Rows_examined: 2use sakila;
#sql执行时间，时间戳格式
SET timestamp=1418827311;
#sql内容select * from store limit 10;outous-MacBook-Pro:~ wangtao$
mysql优化之sakila测试数据库
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。