MySQL架构设计及性能优化

mysql变量

Variable_name   Value
auto_increment_increment    1
auto_increment_offset   1
autocommit  ON
automatic_sp_privileges ON
avoid_temporal_upgrade  OFF
back_log    90
basedir /usr/local/mysql
big_tables  OFF
bind_address    0.0.0.0
binlog_cache_size   32768
binlog_checksum CRC32
binlog_direct_non_transactional_updates OFF
binlog_error_action IGNORE_ERROR
binlog_format   ROW
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
block_encryption_mode   aes-128-ecb
bulk_insert_buffer_size 8388608
character_set_client    utf8
character_set_connection    utf8
character_set_database  utf8
character_set_filesystem    binary
character_set_results   utf8
character_set_server    utf8
character_set_system    utf8
character_sets_dir  /home/imooc/mysql5625/share/charsets/
collation_connection    utf8_general_ci
collation_database  utf8_general_ci
collation_server    utf8_general_ci
completion_type NO_CHAIN
concurrent_insert   AUTO
connect_timeout 10
core_file   OFF
datadir /usr/local/mysql/data/
date_format %Y-%m-%d
datetime_format %Y-%m-%d %H:%i:%s
default_storage_engine  InnoDB
default_tmp_storage_engine  InnoDB
default_week_format 0
delay_key_write ON
delayed_insert_limit    100
delayed_insert_timeout  300
delayed_queue_size  1000
disconnect_on_expired_password  ON
div_precision_increment 4
end_markers_in_json OFF
enforce_gtid_consistency    ON
eq_range_index_dive_limit   10
event_scheduler OFF
expire_logs_days    7
explicit_defaults_for_timestamp OFF
flush   OFF
flush_time  0
foreign_key_checks  ON
ft_boolean_syntax   + -><()~*:""&|
ft_max_word_len 84
ft_min_word_len 4
ft_query_expansion_limit    20
ft_stopword_file    (built-in)
general_log OFF
general_log_file    /usr/local/mysql/data/localhost.log
group_concat_max_len    1024
gtid_executed   98c4c03e-d495-11e5-b7c0-080027ca20fd:1-15156
gtid_mode   ON
gtid_owned  
gtid_purged 
have_compress   YES
have_crypt  YES
have_dynamic_loading    YES
have_geometry   YES
have_openssl    DISABLED
have_profiling  YES
have_query_cache    YES
have_rtree_keys YES
have_ssl    DISABLED
have_symlink    YES
host_cache_size 328
hostname    localhost.localdomain
ignore_builtin_innodb   OFF
ignore_db_dirs  
init_connect    
init_file   
init_slave  
innodb_adaptive_flushing    ON
innodb_adaptive_flushing_lwm    10
innodb_adaptive_hash_index  ON
innodb_adaptive_max_sleep_delay 150000
innodb_additional_mem_pool_size 8388608
innodb_api_bk_commit_interval   5
innodb_api_disable_rowlock  OFF
innodb_api_enable_binlog    OFF
innodb_api_enable_mdl   OFF
innodb_api_trx_level    0
innodb_autoextend_increment 64
innodb_autoinc_lock_mode    1
innodb_buffer_pool_dump_at_shutdown OFF
innodb_buffer_pool_dump_now OFF
innodb_buffer_pool_filename ib_buffer_pool
innodb_buffer_pool_instances    8
innodb_buffer_pool_load_abort   OFF
innodb_buffer_pool_load_at_startup  OFF
innodb_buffer_pool_load_now OFF
innodb_buffer_pool_size 134217728
innodb_change_buffer_max_size   25
innodb_change_buffering all
innodb_checksum_algorithm   innodb
innodb_checksums    ON
innodb_cmp_per_index_enabled    OFF
innodb_commit_concurrency   0
innodb_compression_failure_threshold_pct    5
innodb_compression_level    6
innodb_compression_pad_pct_max  50
innodb_concurrency_tickets  5000
innodb_data_file_path   ibdata1:128M:autoextend
innodb_data_home_dir    
innodb_disable_sort_file_cache  OFF
innodb_doublewrite  ON
innodb_fast_shutdown    1
innodb_file_format  Antelope
innodb_file_format_check    ON
innodb_file_format_max  Antelope
innodb_file_per_table   ON
innodb_flush_log_at_timeout 1
innodb_flush_log_at_trx_commit  2
innodb_flush_method O_DIRECT
innodb_flush_neighbors  1
innodb_flushing_avg_loops   30
innodb_force_load_corrupted OFF
innodb_force_recovery   0
innodb_ft_aux_table 
innodb_ft_cache_size    8000000
innodb_ft_enable_diag_print OFF
innodb_ft_enable_stopword   ON
innodb_ft_max_token_size    84
innodb_ft_min_token_size    3
innodb_ft_num_word_optimize 2000
innodb_ft_result_cache_limit    2000000000
innodb_ft_server_stopword_table 
innodb_ft_sort_pll_degree   2
innodb_ft_total_cache_size  640000000
innodb_ft_user_stopword_table   
innodb_io_capacity  20000
innodb_io_capacity_max  40000
innodb_large_prefix OFF
innodb_lock_wait_timeout    60
innodb_locks_unsafe_for_binlog  OFF
innodb_log_buffer_size  16777216
innodb_log_compressed_pages ON
innodb_log_file_size    134217728
innodb_log_files_in_group   2
innodb_log_group_home_dir   ./
innodb_lru_scan_depth   1024
innodb_max_dirty_pages_pct  75
innodb_max_dirty_pages_pct_lwm  0
innodb_max_purge_lag    0
innodb_max_purge_lag_delay  0
innodb_mirrored_log_groups  1
innodb_monitor_disable  
innodb_monitor_enable   
innodb_monitor_reset    
innodb_monitor_reset_all    
innodb_old_blocks_pct   37
innodb_old_blocks_time  1000
innodb_online_alter_log_max_size    134217728
innodb_open_files   8192
innodb_optimize_fulltext_only   OFF
innodb_page_size    16384
innodb_print_all_deadlocks  OFF
innodb_purge_batch_size 300
innodb_purge_threads    1
innodb_random_read_ahead    OFF
innodb_read_ahead_threshold 56
innodb_read_io_threads  8
innodb_read_only    OFF
innodb_replication_delay    0
innodb_rollback_on_timeout  OFF
innodb_rollback_segments    128
innodb_sort_buffer_size 1048576
innodb_spin_wait_delay  6
innodb_stats_auto_recalc    ON
innodb_stats_method nulls_equal
innodb_stats_on_metadata    OFF
innodb_stats_persistent ON
innodb_stats_persistent_sample_pages    20
innodb_stats_sample_pages   8
innodb_stats_transient_sample_pages 8
innodb_status_output    OFF
innodb_status_output_locks  OFF
innodb_strict_mode  OFF
innodb_support_xa   ON
innodb_sync_array_size  1
innodb_sync_spin_loops  30
innodb_table_locks  ON
innodb_thread_concurrency   0
innodb_thread_sleep_delay   10000
innodb_undo_directory   .
innodb_undo_logs    128
innodb_undo_tablespaces 0
innodb_use_native_aio   OFF
innodb_use_sys_malloc   ON
innodb_version  5.6.25
innodb_write_io_threads 24
interactive_timeout 18800
join_buffer_size    1048576
keep_files_on_create    OFF
key_buffer_size 33554432
key_cache_age_threshold 300
key_cache_block_size    1024
key_cache_division_limit    100
large_files_support ON
large_page_size 0
large_pages OFF
lc_messages en_US
lc_messages_dir /home/imooc/mysql5625/share/
lc_time_names   en_US
license GPL
local_infile    ON
lock_wait_timeout   31536000
locked_in_memory    OFF
log_bin ON
log_bin_basename    /usr/local/mysql/sql_log/mysql-bin
log_bin_index   /usr/local/mysql/sql_log/mysql-bin.index
log_bin_trust_function_creators OFF
log_bin_use_v1_row_events   OFF
log_error   /usr/local/mysql/sql_log/mysql-error.log
log_output  FILE
log_queries_not_using_indexes   ON
log_slave_updates   ON
log_slow_admin_statements   OFF
log_slow_slave_statements   OFF
log_throttle_queries_not_using_indexes  0
log_warnings    2
long_query_time 10.000000
low_priority_updates    OFF
lower_case_file_system  OFF
lower_case_table_names  0
master_info_repository  TABLE
master_verify_checksum  OFF
max_allowed_packet  104857600
max_binlog_cache_size   18446744073709547520
max_binlog_size 1048576000
max_binlog_stmt_cache_size  18446744073709547520
max_connect_errors  1000000
max_connections 200
max_delayed_threads 20
max_digest_length   1024
max_error_count 64
max_heap_table_size 33554432
max_insert_delayed_threads  20
max_join_size   18446744073709551615
max_length_for_sort_data    16384
max_prepared_stmt_count 16382
max_relay_log_size  0
max_seeks_for_key   18446744073709551615
max_sort_length 1024
max_sp_recursion_depth  0
max_tmp_tables  32
max_user_connections    0
max_write_lock_count    18446744073709551615
metadata_locks_cache_size   1024
metadata_locks_hash_instances   8
min_examined_row_limit  0
multi_range_count   256
myisam_data_pointer_size    6
myisam_max_sort_file_size   9223372036853727232
myisam_mmap_size    18446744073709551615
myisam_recover_options  BACKUP,FORCE
myisam_repair_threads   1
myisam_sort_buffer_size 8388608
myisam_stats_method nulls_unequal
myisam_use_mmap OFF
net_buffer_length   16384
net_read_timeout    30
net_retry_count 10
net_write_timeout   60
new OFF
old OFF
old_alter_table OFF
old_passwords   0
open_files_limit    65535
optimizer_prune_level   1
optimizer_search_depth  62
optimizer_switch    index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
optimizer_trace enabled=off,one_line=off
optimizer_trace_features    greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
optimizer_trace_limit   1
optimizer_trace_max_mem_size    16384
optimizer_trace_offset  -1
performance_schema  ON
performance_schema_accounts_size    100
performance_schema_digests_size 10000
performance_schema_events_stages_history_long_size  10000
performance_schema_events_stages_history_size   10
performance_schema_events_statements_history_long_size  10000
performance_schema_events_statements_history_size   10
performance_schema_events_waits_history_long_size   10000
performance_schema_events_waits_history_size    10
performance_schema_hosts_size   100
performance_schema_max_cond_classes 80
performance_schema_max_cond_instances   9092
performance_schema_max_file_classes 50
performance_schema_max_file_handles 32768
performance_schema_max_file_instances   100824
performance_schema_max_mutex_classes    200
performance_schema_max_mutex_instances  43160
performance_schema_max_rwlock_classes   40
performance_schema_max_rwlock_instances 25376
performance_schema_max_socket_classes   10
performance_schema_max_socket_instances 420
performance_schema_max_stage_classes    150
performance_schema_max_statement_classes    168
performance_schema_max_table_handles    8192
performance_schema_max_table_instances  12500
performance_schema_max_thread_classes   50
performance_schema_max_thread_instances 500
performance_schema_session_connect_attrs_size   512
performance_schema_setup_actors_size    100
performance_schema_setup_objects_size   100
performance_schema_users_size   100
pid_file    /usr/local/mysql/data/mysqld.pid
plugin_dir  /usr/local/mysql/lib/plugin/
port    3306
preload_buffer_size 32768
profiling   OFF
profiling_history_size  15
protocol_version    10
query_alloc_block_size  8192
query_cache_limit   1048576
query_cache_min_res_unit    4096
query_cache_size    0
query_cache_type    OFF
query_cache_wlock_invalidate    OFF
query_prealloc_size 8192
range_alloc_block_size  4096
read_buffer_size    2097152
read_only   OFF
read_rnd_buffer_size    2097152
relay_log   /usr/local/mysql/sql_log/mysqld-relay-bin
relay_log_basename  /usr/local/mysql/sql_log/mysqld-relay-bin
relay_log_index /usr/local/mysql/sql_log/mysqld-relay-bin.index
relay_log_info_file relay-log.info
relay_log_info_repository   TABLE
relay_log_purge ON
relay_log_recovery  OFF
relay_log_space_limit   0
report_host 
report_password 
report_port 3306
report_user 
rpl_stop_slave_timeout  31536000
secure_auth ON
secure_file_priv    
server_id   101
server_id_bits  32
server_uuid 98c4c03e-d495-11e5-b7c0-080027ca20fd
show_old_temporals  OFF
simplified_binlog_gtid_recovery OFF
skip_external_locking   ON
skip_name_resolve   ON
skip_networking OFF
skip_show_database  OFF
slave_allow_batching    OFF
slave_checkpoint_group  512
slave_checkpoint_period 300
slave_compressed_protocol   OFF
slave_exec_mode STRICT
slave_load_tmpdir   /tmp
slave_max_allowed_packet    1073741824
slave_net_timeout   3600
slave_parallel_workers  0
slave_pending_jobs_size_max 16777216
slave_rows_search_algorithms    TABLE_SCAN,INDEX_SCAN
slave_skip_errors   OFF
slave_sql_verify_checksum   ON
slave_transaction_retries   10
slave_type_conversions  
slow_launch_time    2
slow_query_log  OFF
slow_query_log_file /usr/local/mysql/sql_log/slow-mysql.log
socket  /usr/local/mysql/data/mysql.sock
sort_buffer_size    2097152
sql_auto_is_null    OFF
sql_big_selects ON
sql_buffer_result   OFF
sql_log_bin ON
sql_log_off OFF
sql_mode    STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
sql_notes   ON
sql_quote_show_create   ON
sql_safe_updates    OFF
sql_select_limit    18446744073709551615
sql_slave_skip_counter  0
sql_warnings    OFF
ssl_ca  
ssl_capath  
ssl_cert    
ssl_cipher  
ssl_crl 
ssl_crlpath 
ssl_key 
storage_engine  InnoDB
stored_program_cache    256
sync_binlog 1
sync_frm    ON
sync_master_info    10000
sync_relay_log  10000
sync_relay_log_info 10000
system_time_zone    CST
table_definition_cache  4096
table_open_cache    4096
table_open_cache_instances  1
thread_cache_size   2048
thread_concurrency  24
thread_handling one-thread-per-connection
thread_stack    524288
time_format %H:%i:%s
time_zone   SYSTEM
timed_mutexes   OFF
tmp_table_size  33554432
tmpdir  /tmp
transaction_alloc_block_size    8192
transaction_prealloc_size   4096
tx_isolation    REPEATABLE-READ
tx_read_only    OFF
unique_checks   ON
updatable_views_with_limit  YES
version 5.6.25-log
version_comment Source distribution
version_compile_machine x86_64
version_compile_os  Linux
wait_timeout    18800

1 存储引擎

myIsam引擎
- 非事务型应用
- 只读类应用
- 空间类应用

.MYD是MyISAM表的数据文件的扩展名
.MYI是MyISAM表的索引的扩展名

Innodb引擎
使用表空间存储数据
innoDB_file_per_table
on: 独立表空间 name.ibd
off: 系统表空间 iddata1

show variables like 'innodb_file_per_table'

/*当你删除数据时，mysql并不会回收，被已删除数据的占据的存储空间，以及索引位。而是空在那里，而是等待新的数据来弥补这个空缺，这样就有一个缺少，如果一时半会，没有数据来填补这个空缺，那这样就太浪费资源了。所以对于写比较频烦的表，要定期进行optimize，一个月一次，看实际情况而定了*/
mysql> optimize table ad_visit_history;      

系统表空间和独立表空间如何选择

• 系统表空间无法简单的收缩文件大小，系统表空间会产生IO瓶颈
• 独立表空间可以利用optimize table命令收缩文件大小，多个文件可以同时刷新数据

建议：对Innodb使用独立表空间

表转移的步骤

1. mysqldump导出数据库表数据
2. 停止MySQL服务，修改参数，并删除Innodb相关文件
3. 重启MYSQL服务，重建系统表空间
4. 重新导入数据

其他存储引擎

/*使用archive,其中arz存储数据，frm存储表结构，不支持删除更新操作*/
/*使用场景，日志和数据采集，不适合oltp*/
create table myarchive(id int auto_increment not null,c1 varchar(10),key(id)) engine=archive;

/*memory存储引擎，数据存在内存中，表结构存在磁盘中，IO效率很高，支持HASH和Btree，不支持blob，使用表级锁*/
create table mymemory(id int,c1 varchar(10),c2 char(10)) engine=memory;

[e-mail]:/var/lib/mysql/sakila# ls -lh mymemory*
-rw-r----- 1 mysql mysql 8.5K Dec 21 20:52 mymemory.frm

mysql> create table mymemory(id int,c1 varchar(10),c2 char(10)) engine=memory;
Query OK, 0 rows affected (0.01 sec)


mysql> create index idx_c2 using btree on mymemory(c2);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from mymemory\G;
*************************** 1. row ***************************
        Table: mymemory
   Non_unique: 1
     Key_name: idx_c1
 Seq_in_index: 1
  Column_name: c1
    Collation: NULL
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: HASH
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: mymemory
   Non_unique: 1
     Key_name: idx_c2
 Seq_in_index: 1
  Column_name: c2
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
2 rows in set (0.00 sec)
/*下图可知，memory存储引擎的表都是固定长度*/
mysql> show table status like 'mymemory'\G;
*************************** 1. row ***************************
           Name: mymemory
         Engine: MEMORY
        Version: 10
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 26
    Data_length: 0
Max_data_length: 4406116
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2017-12-21 20:56:43
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.01 sec)
/*用于查找映射表，保存数据分析的中间表，缓存周期性聚合数据的结果表*/

/*Federated存储引擎，本地不保存数据，数据保存在远程服务器，本地保存表结构和远程服务器的连接信息，默认禁止*/
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.01 sec)


/*编辑cnf文件，加入federated引擎支持*/
federated=1

grant select,update,insert,delete on yuejuan.tiankongti to [e-mail]'120.95.132.228' indetified by '123456'

/*创建同名表*/
create table `name`() engine=federated connection ='mysql://user_name:[e-mail]:3306/db/table'

/*使用场景*/
并不适合生产环境，偶尔的统计分析或者实验条件

事务支持：Innodb
备份：Innodb可以在线热备份，崩溃恢复更好
mysqldump为逻辑备份，并且会加锁，故不是热备份方案
存储引擎在一个数据库最好统一。

MySQL服务器参数

[e-mail]:/var/lib# mysqld --help --verbose|grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf 

内存相关参数

1. 确定可以使用的内存上限
2. 确定MySQL每个连接所使用的内存，sort_buffer_size join_buffer_size read_buffer_size read_rnd_buffer_size->针对线程设置的缓存大小
3. 确定需要为操作系统保留的内存
4. 如何为缓存池分配内存，Innodb_buffer_pool_size 不仅缓存索引，也会缓存数据。innodb的性能严重依赖这个参数，Innodb_buffer_pool_size=总内存-（每个线程所需要的内存*连接数）-系统保留内存。
5. key_buffer_size 主要针对myIsam,缓存索引。因为mysql系统表仍然使用MyIsam引擎，所以该size必须配置。

I/O相关配置参数

1. innodb I/O相关配置，Innodb_log_file_size 控制单个日志文件大小
2. Innodb_flush_log_at_trx_commit:2 每次事务提交，执行log数据写入到cache，每秒执行一次flush log到磁盘。好处是如果mysql进程崩溃了，设置为2时，不会丢失任何数据。只有服务器崩溃了，数据才会损失。

2 基准测试

测试目的

• 建立服务器性能基准线
• 模拟比当前系统更高的负载，以找出系统的扩展瓶颈
• 测试不同的硬件软件和操作系统配置
• 证明新的硬件设备是否配置正确

如何基准测试

• 系统入口测试
• 单独对MySQL进行基础测试
  
  • TPS
  • QPS
  • 响应时间
  • 并发量

基准测试的步骤

#!/bin/bash
INTERVAL=5
PREFIX=/home/imooc/benchmarks/$INTERVAL-sec-status
RUNFILE=/home/imooc/benchmarks/running
echo "1" > $RUNFILE
MYSQL=/usr/local/mysql/bin/mysql
$MYSQL -e "show global variables" >> mysql-variables
while test -e $RUNFILE; do
    file=$(date +%F_%I)
    sleep=$(date +%s.%N | awk '{print 5 - ($1 % 5)}')
    sleep $sleep
    ts="$(date +"TS %s.%N %F %T")"
    loadavg="$(uptime)"
    echo "$ts $loadavg" >> $PREFIX-${file}-status
    $MYSQL -e "show global status" >> $PREFIX-${file}-status &
    echo "$ts $loadavg" >> $PREFIX-${file}-innodbstatus
    $MYSQL -e "show engine innodb status" >> $PREFIX-${file}-innodbstatus &
    echo "$ts $loadavg" >> $PREFIX-${file}-processlist
    $MYSQL -e "show full processlist\G" >> $PREFIX-${file}-processlist &
    echo $ts
done
echo Exiting because $RUNFILE does not exists

常用的测试工具
MySQLslap:模拟服务器负载，输出统计信息；指定或者自动生成查询语句；

--auto-generate-sql##自动生成sql脚本测试
--auto-generate-sql-add-autoincremnet ##生成的表中增加自增ID
--auto-generate-sql-load-type  ##指定测试中使用的查询类型
--auto-generate-sql-write-number ##指定初始化数据时生成的数据量
--concurrency                    ##指定并发线程的数量
--engine                        ##指定需要测试的表的存储引擎，逗号分隔
--no-drop                       ##指定不清理数据
--iterations                    #指定测试运行的次数
--number-of-queries ##指定每一个线程执行的查询数量
--number-int-cols ##指定测试表中包含的int类型列的数量
--number-char-cols ##指定表中包含的varchar类型的数量
--create-schema   ##指定用于执行测试的数据库的名字
--query
--only-print

mysqlslap -u chuyao -p --concurrency=1,50,100,500 --iterations=3 --number-int-cols=5 --number-char-cols=5 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=myisam,innodb --number-of-queries=10 --create-schema=db14 --only-print >1.sql

MySQL文件的默认位置

/var/run/mysqld/mysqld.sock
/usr/bin                 客户端程序和脚本  
/usr/sbin                mysqld 服务器  
/var/lib/mysql           日志文件，数据库  ［重点要知道这个］  
/usr/share/doc/packages  文档  
/usr/include/mysql       包含( 头) 文件  
/usr/lib/mysql           库  
/usr/share/mysql         错误消息和字符集文件  
/usr/share/sql-bench     基准程序 

sysbench安装

apt-get install automake
apt-get install libtool
./autogen.sh
./configure --with-mysql-includes=/usr/include/mysql/ \--with-mysql-libs=/usr/lib/mysql
./configure && make && make install
make && make install

sysbench --test ##用于制定执行的测试类型，支持以下参数
fileio
cpu
memory
Oltp  测试要指定具体的lua脚本

--mysql-db 用于指定执行数据库名
--mysql-table-engine 用于指定使用的存储引擎
--oltp-table-count 执行测试的表的数量
--oltp-table-size 执行测试的每个表的数据行数
--num-threads 指定并发线程的数量
--max-time 指定最大的测试时间
prepare 用于准备测试数据
run 用于实际测试


sysbench --test=fileio --file-total-size=1G prepare

sysbench --test=./oltp.lua --mysql-table-engine=innodb --oltp-table-size=10000 --mysql-db=sysbench2 --mysql-user=sbtest --mysql-password=123456 --oltp-tables-count=10 --mysql-socket=/var/run/mysqld/mysqld.sock prepare

3 主从复制

mysql> show variables like 'binlog_format'
mysql> set session binlog_format=statement;
mysql> set session binlog_format=row;##基于row的日志可以避免主从复制不一致的情况，基于段的SQL只会记录SQL语句，基于行的会记录每一行的修改
##误操作修改了数据库时，我们可以分析二进制日志反向处理恢复数据
root@ubuntu:/var/lib/mysql# mysqlbinlog log-bin.000001   ##查看日志
mysql> set session binlog_format=MIXED;
mysql> show variables like 'binlog_row_image';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| binlog_row_image | FULL  |
+------------------+-------+

注意，启用二进制日志需要在cnf文件配置路径

log-bin=/var/lib/mysql/log-bin.log

基于SQL语名的复制（SBR）
优点：日志量少，不强制要求主从数据库表定义，相比于行复制更为灵活
缺点：不保证主从一致，从服务器需要更多的行锁

mysql复制工作方式

1. 主服务器将变更写入二进制日志
2. 从服务器读取主服务器的日志变更写入relay_log中

3. 在从服务器上重放relay_log中的日志

   基于SQL段的日志是在从数据库重新执行记录的SQL
   基于行的日志是在从数据库上直接应用数据库行的修改

基于日志的复制、基于GTID的复制

影响主从延迟的因素

1. 主库写入二进制日志的时间
   
   1. 控制主库的事务大小，分割大事务
2. 二进制日志传输时间
   
   1. 使用mixed日志格
   2. 设置 binlog_row_image=minimal;

3. 从服务器串行
   1.使用多线程复制

   如何配置多线程复制

stop slave;
set global slave_parallel_type='logic_clock';
set global slave_parallel_workers=4;//并发线程的数量
start slave;

主从复制常见错误
主库或者从库意外宕机引起的错误：
*使用跳过二进制事件
注入空事务的方式先恢复中断的复制链路
对比主从数据*
主库上的二进制文件日志损坏

高可用架构

• 建立完善的监控和报警
• 对备份数据进行恢复测试
• 正确配置数据库环境
• 对不需要的数据归档和清理
• 避免出现单点故障
• 主从切换及故障转移

避免单点故障：DRDB磁盘复制方式

MMM架构
主主复制
监控MySQL主从复制健康状况，主服务器宕机时进行故障转移并配置其他从服务器对新主服务器的复制

架构部署步骤：

1. 配置主主复制和主从同步集群
2. 安装主从节点所需要的支持包
3. 安装及配置MMM工具集
4. 运行MMM监控服务

MMM框架的优点：

1. 开源+Perl
2. 主从复制延迟时会自动切换
3. MMM框架提供了从服务器的延迟监控
4. 提供了主服务器故障转移之后的重新同步功能

MMM框架的缺点：

1. 不支持MYSQL新的复制功能
2. 没有读负载均衡的功能
3. 存在单点故障

MHA架构
主DB不可用时，从多个服务器中选举出新的主数据库服务器

读写分离要解决的是如何在复制集群的不同角色上，执行不同的SQL语句。
读的负载均衡解决的是相同角色数据库如何共同分担相同的负载。

4 数据库索引优化

索引列上不能使用表达式或者函数

select ---from produ**ct
where to_days(out_dat**e)-to_days(current_date)<=30

/*优化策略*/
select ---- from product
where out_date<date_add(current_date,interval 30 day)

联合索引

1. 经常被使用的列优先
2. 选择性高的列优先

覆盖索引

use sakila;

mysql> explain select language_id from film where language_id=1\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
   partitions: NULL
         type: ref
possible_keys: idx_fk_language_id
          key: idx_fk_language_id
      key_len: 1
          ref: const
         rows: 1003
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

mysql> explain select *  from film where language_id=1\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
   partitions: NULL
         type: ALL
possible_keys: idx_fk_language_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

mysql数据库的innodb表会自动加入二级索引

mysql> show create table actor \G;
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8
1 row in set (0.02 sec)

ERROR: 
No query specified

mysql> explain select actor_id,last_name from actor where last_name='Joe'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ref
possible_keys: idx_actor_last_name
          key: idx_actor_last_name
      key_len: 137
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.04 sec)

使用索引扫描来优化排序

1. 索引的列顺序和order by子句的顺序完全一致
2. 索引中所有列的方向和order by子句完全一致
3. order by中的字段完全在关联表中的第一张表

模拟hash索引

alter table film add title_md5 varchar(32);
Query OK, 0 rows affected (1.45 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> update film set title_md5=md5(title);
Query OK, 1003 rows affected (0.40 sec)
Rows matched: 1003  Changed: 1003  Warnings: 0

mysql> create index idx_md5 on film(title_md5);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

explain select * from film where title_md5=md5('egg igby') and title ='egg igby';

4 数据库查询优化

获取问题SQL

1. 慢查询日志
2. 实时监控

慢查询日志

slow_query_log //启动停止记录慢查日志
slow_query_log_file //指定慢查日志的存储及文件，最好将慢查日志和数据文件分在不同的分区之中
long_query_time //指定记录慢查日志SQL执行时间的阈值，默认10秒，改为0.001秒比价合适
log_queries_not_using_indexes //是否记录未使用索引的SQL

和二进制日志不同，慢查询日志会记录所有符合条件的SQL，包括查询语句和数据修改语句

使用sysbench生成慢查询日志

[e-mail]:/home/ubuntu/Desktop/軟件/sysbench-0.5/sysbench/tests/db# sysbench --test=./oltp.lua --mysql-table-engine=innodb --oltp-table-size=10000 --mysql-db=sysbench_test --mysql-user=root --mysql-password=123456 --oltp-tables-count=10 --mysql-socket=/var/run/mysqld/mysqld.sock run
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1
Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            140000
        write:                           40000
        other:                           20000
        total:                           200000
    transactions:                        10000  (213.49 per sec.)
    read/write requests:                 180000 (3842.76 per sec.)
    other operations:                    20000  (426.97 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          46.8414s
    total number of events:              10000
    total time taken by event execution: 46.8099s
    response time:
         min:                                  2.41ms
         avg:                                  4.68ms
         max:                                180.96ms
         approx.  95 percentile:               8.98ms

Threads fairness:
    events (avg/stddev):           10000.0000/0.00
    execution time (avg/stddev):   46.8099/0.00

查询慢查日志的存储路径

mysql>  show variables like "slow_query_log_file";
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log_file | /var/lib/mysql/ubuntu-slow.log |
+---------------------+--------------------------------+
1 row in set (0.00 sec)

使用mysql自带的慢查询日志查看工具查看日志：

[e-mail]:/usr/bin# mysqldumpslow -s r -t 10 /var/lib/mysql/ubuntu-slow.log

Reading mysql slow query log from /var/lib/mysql/ubuntu-slow.log
Count: 1063  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=100.0 (106300), root[root]@localhost
  SELECT c FROM sbtest10 WHERE id BETWEEN N AND N+N

Count: 1063  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=100.0 (106300), root[root]@localhost
  SELECT c FROM sbtest10 WHERE id BETWEEN N AND N+N ORDER BY c

Count: 1063  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=100.0 (106300), root[root]@localhost
  SELECT DISTINCT c FROM sbtest10 WHERE id BETWEEN N AND N+N ORDER BY c

Count: 1023  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=100.0 (102300), root[root]@localhost
  SELECT c FROM sbtest9 WHERE id BETWEEN N AND N+N

Count: 1023  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=100.0 (102300), root[root]@localhost
  SELECT c FROM sbtest9 WHERE id BETWEEN N AND N+N ORDER BY c

Count: 1023  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=100.0 (102300), root[root]@localhost
  SELECT DISTINCT c FROM sbtest9 WHERE id BETWEEN N AND N+N ORDER BY c

Count: 1019  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=100.0 (101900), root[root]@localhost
  SELECT c FROM sbtest5 WHERE id BETWEEN N AND N+N

Count: 1019  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=100.0 (101900), root[root]@localhost
  SELECT c FROM sbtest5 WHERE id BETWEEN N AND N+N ORDER BY c

Count: 1019  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=100.0 (101900), root[root]@localhost
  SELECT DISTINCT c FROM sbtest5 WHERE id BETWEEN N AND N+N ORDER BY c

Count: 1011  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=100.0 (101100), root[root]@localhost
  SELECT c FROM sbtest7 WHERE id BETWEEN N AND N+N

使用pt-query-digest查看慢查询日志

[e-mail]:/usr/bin# pt-query-digest --explain h=127.0.0.1,u=root,p=123456 /var/lib/mysql/ubuntu-slow.log 

# A software update is available:
#   * The current version for Percona::Toolkit is 3.0.5


# 17.8s user time, 480ms system time, 40.40M rss, 122.63M vsz
# Current date: Sun Jan 14 17:54:10 2018
# Hostname: ubuntu
# Files: /var/lib/mysql/ubuntu-slow.log
# Overall: 200.01k total, 23 unique, 0.05 QPS, 0.00x concurrency _________
# Time range: 2017-11-29T05:28:00 to 2018-01-15T01:53:47
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time            40s     7us   182ms   198us   881us     1ms    63us
# Lock time             4s       0     6ms    20us    35us    37us    18us
# Rows sent          2.97M       0     100   15.55   97.36   34.54    0.99
# Rows examine       6.81M       0   1.03k   35.68  299.03   78.01    0.99
# Query size        10.37M       5     242   54.35  151.03   50.78   34.95

# Profile
# Rank Query ID           Response time Calls  R/Call V/M   Item
# ==== ================== ============= ====== ====== ===== ==============
#    1 0x813031B8BBC3B329 23.5733 59.4%  10000 0.0024  0.01 COMMIT
#    2 0x558CAEF5F387E929  6.0842 15.3% 100000 0.0001  0.00 SELECT sbtest?
#    3 0x737F39F04B198EF6  2.0316  5.1%  10000 0.0002  0.00 SELECT sbtest?
#    4 0x84D1DEE77FA8D4C3  1.4714  3.7%  10000 0.0001  0.00 SELECT sbtest?
#    5 0x3821AE1F716D5205  1.3179  3.3%  10000 0.0001  0.00 SELECT sbtest?
#    6 0x6EEB1BFDCCF4EBCD  1.2368  3.1%  10000 0.0001  0.00 SELECT sbtest?
#    7 0xD30AD7E3079ABCE7  1.2326  3.1%  10000 0.0001  0.00 UPDATE sbtest?
#    8 0xEAB8A8A8BEEFF705  0.8183  2.1%  10000 0.0001  0.00 DELETE sbtest?
# MISC 0xMISC              1.9033  4.8%  30015 0.0001   0.0 <15 ITEMS>

# Query 1: 217.39 QPS, 0.51x concurrency, ID 0x813031B8BBC3B329 at byte 16950662
# Scores: V/M = 0.01
# Time range: 2018-01-15T01:38:07 to 2018-01-15T01:38:53
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          4   10000
# Exec time     59     24s   748us   182ms     2ms     6ms     5ms     2ms
# Lock time      0       0       0       0       0       0       0       0
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size     0  58.59k       6       6       6       6       0       6
# String:
# Databases    sysbench_test
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us  ##
#   1ms  ################################################################
#  10ms  #
# 100ms  #
#    1s
#  10s+
COMMIT\G

# Query 2: 2.17k QPS, 0.13x concurrency, ID 0x558CAEF5F387E929 at byte 45280241
# Scores: V/M = 0.00
# Time range: 2018-01-15T01:38:07 to 2018-01-15T01:38:53
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         49  100000
# Exec time     15      6s    33us     8ms    60us    98us    57us    52us
# Lock time     51      2s    13us     5ms    21us    33us    32us    17us
# Rows sent      3  97.66k       1       1       1       1       0       1
# Rows examine   1  97.66k       1       1       1       1       0       1
# Query size    32   3.35M      35      36   35.11   34.95       0   34.95
# String:
# Databases    sysbench_test
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us  ################################################################
# 100us  ###
#   1ms  #
#  10ms
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `sysbench_test` LIKE 'sbtest6'\G
#    SHOW CREATE TABLE `sysbench_test`.`sbtest6`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT c FROM sbtest6 WHERE id=5392\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: sbtest6
#    partitions: NULL
#          type: const
# possible_keys: PRIMARY
#           key: PRIMARY
#       key_len: 4
#           ref: const
#          rows: 1
#      filtered: 100.00
#         Extra: NULL

# Query 3: 217.39 QPS, 0.04x concurrency, ID 0x737F39F04B198EF6 at byte 47904100
# Scores: V/M = 0.00
# Time range: 2018-01-15T01:38:07 to 2018-01-15T01:38:53
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          4   10000
# Exec time      5      2s   149us     6ms   203us   273us   128us   185us
# Lock time      6   256ms    19us     4ms    25us    36us    39us    21us
# Rows sent     32 976.56k     100     100     100     100       0     100
# Rows examine  42   2.86M     300     300     300     300       0     300
# Query size     6 733.46k      75      76   75.11   72.65       0   72.65
# String:
# Databases    sysbench_test
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us  ################################################################
#   1ms  #
#  10ms
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `sysbench_test` LIKE 'sbtest8'\G
#    SHOW CREATE TABLE `sysbench_test`.`sbtest8`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT DISTINCT c FROM sbtest8 WHERE id BETWEEN 5006 AND 5006+99 ORDER BY c\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: sbtest8
#    partitions: NULL
#          type: range
# possible_keys: PRIMARY
#           key: PRIMARY
#       key_len: 4
#           ref: NULL
#          rows: 100
#      filtered: 100.00
#         Extra: Using where; Using temporary; Using filesort

# Query 4: 217.39 QPS, 0.03x concurrency, ID 0x84D1DEE77FA8D4C3 at byte 47225372
# Scores: V/M = 0.00
# Time range: 2018-01-15T01:38:07 to 2018-01-15T01:38:53
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          4   10000
# Exec time      3      1s   104us     3ms   147us   204us    49us   131us
# Lock time      5   247ms    18us   659us    24us    36us    13us    21us
# Rows sent     32 976.56k     100     100     100     100       0     100
# Rows examine  28   1.91M     200     200     200     200       0     200
# Query size     6 645.57k      66      67   66.11   65.89       0   65.89
# String:
# Databases    sysbench_test
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us  ################################################################
#   1ms  #
#  10ms
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `sysbench_test` LIKE 'sbtest3'\G
#    SHOW CREATE TABLE `sysbench_test`.`sbtest3`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT c FROM sbtest3 WHERE id BETWEEN 5035 AND 5035+99 ORDER BY c\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: sbtest3
#    partitions: NULL
#          type: range
# possible_keys: PRIMARY
#           key: PRIMARY
#       key_len: 4
#           ref: NULL
#          rows: 100
#      filtered: 100.00
#         Extra: Using where; Using filesort

# Query 5: 217.39 QPS, 0.03x concurrency, ID 0x3821AE1F716D5205 at byte 31845142
# Scores: V/M = 0.00
# Time range: 2018-01-15T01:38:07 to 2018-01-15T01:38:53
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          4   10000
# Exec time      3      1s    85us     5ms   131us   185us    67us   119us
# Lock time      5   215ms    13us   333us    21us    31us     8us    19us
# Rows sent     32 976.56k     100     100     100     100       0     100
# Rows examine  14 976.56k     100     100     100     100       0     100
# Query size     5 538.15k      55      56   55.11   54.21       0   54.21
# String:
# Databases    sysbench_test
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us  #
# 100us  ################################################################
#   1ms  #
#  10ms
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `sysbench_test` LIKE 'sbtest10'\G
#    SHOW CREATE TABLE `sysbench_test`.`sbtest10`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT c FROM sbtest10 WHERE id BETWEEN 4991 AND 4991+99\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: sbtest10
#    partitions: NULL
#          type: range
# possible_keys: PRIMARY
#           key: PRIMARY
#       key_len: 4
#           ref: NULL
#          rows: 100
#      filtered: 100.00
#         Extra: Using where

# Query 6: 217.39 QPS, 0.03x concurrency, ID 0x6EEB1BFDCCF4EBCD at byte 47234890
# Scores: V/M = 0.00
# Time range: 2018-01-15T01:38:07 to 2018-01-15T01:38:53
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          4   10000
# Exec time      3      1s    85us     7ms   123us   167us   162us   108us
# Lock time      6   282ms    20us     6ms    28us    38us    83us    23us
# Rows sent      0   9.77k       1       1       1       1       0       1
# Rows examine  14 976.56k     100     100     100     100       0     100
# Query size     5 586.98k      60      61   60.11   59.77    0.00   59.77
# String:
# Databases    sysbench_test
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us  ####
# 100us  ################################################################
#   1ms  #
#  10ms
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `sysbench_test` LIKE 'sbtest10'\G
#    SHOW CREATE TABLE `sysbench_test`.`sbtest10`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT SUM(K) FROM sbtest10 WHERE id BETWEEN 4664 AND 4664+99\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: sbtest10
#    partitions: NULL
#          type: range
# possible_keys: PRIMARY
#           key: PRIMARY
#       key_len: 4
#           ref: NULL
#          rows: 100
#      filtered: 100.00
#         Extra: Using where

# Query 7: 217.39 QPS, 0.03x concurrency, ID 0xD30AD7E3079ABCE7 at byte 5338121
# Scores: V/M = 0.00
# Time range: 2018-01-15T01:38:07 to 2018-01-15T01:38:53
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          4   10000
# Exec time      3      1s    76us     7ms   123us   185us   155us   103us
# Lock time      7   297ms    21us     6ms    29us    44us    75us    23us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0   9.77k       1       1       1       1       0       1
# Query size     3 372.13k      38      39   38.11   38.53    0.50   36.69
# String:
# Databases    sysbench_test
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us  #######################################
# 100us  ################################################################
#   1ms  #
#  10ms
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `sysbench_test` LIKE 'sbtest1'\G
#    SHOW CREATE TABLE `sysbench_test`.`sbtest1`\G
UPDATE sbtest1 SET k=k+1 WHERE id=4780\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
select  k=k+1 from sbtest1 where  id=4780\G

# Query 8: 217.39 QPS, 0.02x concurrency, ID 0xEAB8A8A8BEEFF705 at byte 40388364
# Scores: V/M = 0.00
# Time range: 2018-01-15T01:38:07 to 2018-01-15T01:38:53
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          4   10000
# Exec time      2   818ms    54us     4ms    81us   119us    54us    73us
# Lock time      4   203ms    15us   187us    20us    30us     7us    17us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0   9.77k       1       1       1       1       0       1
# Query size     3 323.30k      33      34   33.11   33.28    0.49   31.70
# String:
# Databases    sysbench_test
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us  ################################################################
# 100us  ########
#   1ms  #
#  10ms
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `sysbench_test` LIKE 'sbtest1'\G
#    SHOW CREATE TABLE `sysbench_test`.`sbtest1`\G
DELETE FROM sbtest1 WHERE id=5025\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
select * from  sbtest1 WHERE id=5025\G

实时获取性能问题SQL

mysql> select id,`user`,`host`,db,command,`time`,state,info from information_schema.processlist where time>=60\G;

查询过程

1. 客户端发送SQL请求给服务器
2. 服务器检查是否可以在查询缓存中命中该SQL
3. 服务器端进行SQL解析，预处理，再由优化器生成对应的执行计划
4. 根据执行计划，调用存储引擎API查询数据
5. 结果返回客户端

查询缓存对SQL性能的影响

• 对于一个读写频繁的系统使用查询缓存很可能会降低查询处理的效率

query_cache_type ##设置缓存可用
query_cache_size##设置缓存内存大小
query_cache_limit##设置可用存储的最大值
SQL_NO_CACHE   ##如果已知结果不会被缓存，可以在查询中加入该参数提升效率
query_cache_wlock_invalidate ##设置数据表被锁住之后是否返回缓存中的数据

query_cache_min_res_unit ##设置查询缓存分配的内存块的最小单位

SQL的解析预处理以及生成执行计划

查询优化器生成错误计划的原因：

• 统计信息不准确（比如innodb存储引擎的表行数就是估算的）
• 执行计划中的成本估算不等于实际的执行计划的成本
• MySQL基于成本模型选择最优计划
• MySQL不考虑并发查询
• MySQL有时候会基于固定规则生成执行计划
• MySQL不会考虑存储过程、用户定义的函数的成本

如何确定查询各个阶段所消耗的时间：

profile 针对session有效

set profiling=1;#使用profile
show profiles 
show profile for query N;

mysql> select count(*) from film;
+----------+
| count(*) |
+----------+
|     1003 |
+----------+
1 row in set (0.02 sec)

mysql> show profiles;
+----------+------------+---------------------------+
| Query_ID | Duration   | Query                     |
+----------+------------+---------------------------+
|        1 | 0.01680750 | select count(*) from film |
+----------+------------+---------------------------+
1 row in set, 1 warning (0.00 sec)



mysql> show profile cpu for query 1;
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting             | 0.000097 | 0.000000 |   0.000000 |
| checking permissions | 0.000008 | 0.000000 |   0.000000 |
| Opening tables       | 0.000015 | 0.000000 |   0.000000 |
| init                 | 0.016249 | 0.000000 |   0.000000 |
| System lock          | 0.000022 | 0.000000 |   0.000000 |
| optimizing           | 0.000005 | 0.000000 |   0.000000 |
| statistics           | 0.000012 | 0.000000 |   0.000000 |
| preparing            | 0.000009 | 0.000000 |   0.000000 |
| executing            | 0.000003 | 0.000000 |   0.000000 |
| Sending data         | 0.000303 | 0.000000 |   0.000000 |
| end                  | 0.000007 | 0.000000 |   0.000000 |
| query end            | 0.000028 | 0.000000 |   0.000000 |
| closing tables       | 0.000011 | 0.000000 |   0.000000 |
| freeing items        | 0.000027 | 0.000000 |   0.000000 |
| cleaning up          | 0.000013 | 0.000000 |   0.000000 |
+----------------------+----------+----------+------------+
15 rows in set, 1 warning (0.00 sec)



mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                     |
+---------+------+-------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'SHOW PROFILE' is deprecated and will be removed in a future release. Please use Performance Schema instead |
+---------+------+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

使用performance_schema针对全局有效

mysql> use performance_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


mysql> update setup_instruments set enabled='Yes',timed='yes' where name like 'stage%';
Query OK, 120 rows affected (0.03 sec)
Rows matched: 129  Changed: 120  Warnings: 0

mysql> update setup_consumers set enabled ='Yes' where name like 'events%';
Query OK, 10 rows affected (0.00 sec)
Rows matched: 12  Changed: 10  Warnings: 0

mysql> select count(*) from sakila.film;
+----------+
| count(*) |
+----------+
|     1003 |
+----------+
1 row in set (0.00 sec)

mysql> select a.thread_id,SQL_TEXT,c.EVENT_Name,(c.timer_end-c.timer_start)/1000000000 as 'Duration(MS)' from events_statements_history a join threads b on a.`thread_id`=b.`thread_id` join events_stages_history_long c on c.`thread_id`=b.`thread_id` and c.`event_id` between a.event_id and a.end_event_id order by a.thread_id,c.event_id;

大表的更新与删除

Delimiter $$
use `sysbench_test`$$
drop procedure if exists `p_delete_rows`$$
create definer=`root`@`127.0.0.1` procedure `p_delete_rows`()
begin
   declare v_rows int;
   set v_rows=1;
   while v_rows>0
   do      
         delete from sbtest1 where id>90000 and id <=190000 limit 5000;
         select row_count() into v_rows;
         select sleep(5);
    end while;
end$$
delimiter ;

call p_delete_rows;

如何修改大表结构

[e-mail]:/home/ubuntu/Desktop/軟件/sysbench-0.5/sysbench/tests/db# pt-online-schema-change --alter="modify c varchar(150) not null" --user=root --password=123456 D=sysbench_test,t=sbtest1 --execute
No slaves found.  See --recursion-method if host ubuntu has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `sysbench_test`.`sbtest1`...
Creating new table...
Created new table sysbench_test._sbtest1_new OK.
Altering new table...
Altered `sysbench_test`.`_sbtest1_new` OK.
2018-01-17T03:52:05 Creating triggers...
2018-01-17T03:52:05 Created triggers OK.
2018-01-17T03:52:05 Copying approximately 98784 rows...
2018-01-17T03:52:09 Copied rows OK.
2018-01-17T03:52:09 Analyzing new table...
2018-01-17T03:52:09 Swapping tables...
2018-01-17T03:52:09 Swapped original and new tables OK.
2018-01-17T03:52:09 Dropping old table...
2018-01-17T03:52:09 Dropped old table `sysbench_test`.`_sbtest1_old` OK.
2018-01-17T03:52:09 Dropping triggers...
2018-01-17T03:52:09 Dropped triggers OK.
Successfully altered `sysbench_test`.`sbtest1`.

使用汇总表优化查询

优化步骤：
explain -》select type-》SELECT * FROM information_schema.OPTIMIZER_TRACE\G;-》 show profile block io,cpu for query 1;

5 数据库监控

对什么监控
对数据库服务可用性监控：通过网络连接到数据库，并且确定数据库是可以对外提供服务的。
对数据库性能进行监控：QPS和TPS。
对主从复制进行监控。
对服务器资源的监控。
如何确认数据库可以通过网络连接

mysqladmin -umonitor_user -p -h ping
telnet ip db_prot

确认数据库是否可以读写

检查数据库的read_only 参数是否为off
建立监控表并对表中数据更新
执行简单的查询select @@version

如何监控数据库的连接数

show variables like 'max_connections';
show global status like 'Thread_connected'

如果连接占比过高立即报警

数据库可用性监控