MySQL exercise 文档

 

show status like 'innodb_row_lock%';
select table_name,table_rows from information_schema.tables where table_schema='courier' order by table_rows desc;
show table status from mysql where comment='view';



*_bin binary case sensitive collation
*_cs  case sensitive collation
*_ci  case insensitive collation

# index
alter table p add [primary|unique] index [index_name] (columns); 都可不加索引名,primary key加索引名无效
create [unique] index index_name on tbl_name (columns);


# privileges
replication client  可使用show master status show slave status show binary logs;
replication slave

hY7Rji5IIKoaciSb


# replication
start slave [sql_thread] until sql_after_mts_gaps


# InnoDB
查看InnoDB表空间 mysql.ibd innodb_temporary innodb_undo_001 innodb_undo_002 sys/sys_config.ibd hoist/b.ibd(表)
select * from information_schema.innodb_tablespaces\G  



# binlog
binlog_cache_size     binlog_cache => binlog_file
show global status like '%binlog_cache%';   binlog_cache_use(内存中使用binlog_cache次数) binlog_cache_disk_use(使用了磁盘)



# tmp_table
show global status like 'created_tmp%';  
create_tmp_disk_tables 磁盘临时表,created_tmp_files内存临时文件,created_tmp_tables内存临时表


# proxysql
select hostgroup,schemaname,username,digest_text,count_star from stats_mysql_query_digest;
select * from mysql_replication_hostgroups;

# monitor
update global_variables set variable_value='monitor' where variable_name='mysql-monitor_username';
update global_variables set variable_value='monitor' where variable_name='mysql-monitor_password';
update global_variables set variable_value='2000' where variable_name in ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
select * from global_variables where variable_name like '%mysql-monitor%';
select * from monitor.mysql_server_connect_log order by time_start_us desc limit 10;
select * from monitor.mysql_server_ping_log order by time_start_us desc limit 10;
# proxysql checks the value of read_only for servers configured in hostgroups that are configured in mysql_replication_hostgroups
select * from monitor.mysql_server_read_only_log order by time_start_us desc limit 10;

select * from stats.stats_mysql_connection_pool;
select * from stats.stats_mysql_commands_counters where total_cnt;
select * from stats.stats_mysql_query_digest order by sum_time desc;
select hostgroup hg,sum_time,count_star,digest_text from stats.stats_mysql_query_digest order by sum_time desc;

# mysql_query_rules
insert into mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply)
values
(1,1,'proxysql','^select c from hoist where id=\?$',200,1);
insert into mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply)
values
(2,1,'proxysql','distinct c from hoist',300,1);

insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,log,apply)values(1,1,'^UPDATE',1,1,1);
insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,log,apply)values(2,1,'^SELECT',2,1,1);

insert into mysql_query_rules(rule_id,active,apply,destination_hostgroup,match_pattern,replace_pattern)
values
(1,1,1,20,"^(select.*?from) it_db\.(.*?) where zhuanye=['""](.*?)['""] (.*)$","\1 \3.\2 where 1=1 \4");



select match_digest,match_pattern,destination_hostgroup from mysql_query_rules where active=1 and username='proxysql' order by rule_id;
# reset stats_mysql_query_digest
select * from stats_mysql_query_digest_reset limit 1;
select hostgroup hg,sum_time,count_star,digest_text from stats_mysql_query_digest order by sum_time desc;
select hostgroup hg,sum(sum_time),sum(count_star),digest_text from stats_mysql_query_digest order by sum_time desc;

#  Query Caching
update mysql_query_rules set cache_ttl=5000 where active=1 and destination_hostgroup=300;
load mysql query rules from memory;
select rule_id_active,match_digest,match_pattern,destination_hostgroup,cache_ttl from runtime_mysql_query_rules;

# stats
select rule_id,hits from stats.stats_mysql_query_rules;




# InnoDB

innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:5G  # ibtmp1 非压缩的innodb临时表空间,



MySQL
表优化
1: 尽量将表字段定义为not null,MySQL中含有null的列难以进行查询优化,null会使索引以及索引的统计信息变得复杂,可使用0或者 空串代替
2: 数值类型比较比字符串高效很多,字段类型尽量使用最小,最简单数据类型

表拆分
1、垂直拆分
垂直拆分按照字段进行拆分，其实就是把组成一行的多个列分开放到不同的表中，这些表具有不同的结构，拆分后的表具有更少的列，例如用户表中的一些字段可能经常访问，可以把这些字段放进一张表里。另外一些不经常使用的信息就可以放进另外一张表里。插入的时候使用事务，也可以保证两表的数据一致。缺点也很明显，由于拆分出来的两张表存在1：1的关系，需要使用冗余字段，而且需要join操作，我们在使用的时候可以分别取两次，这样的来说既可以避免join操作，又可以提高效率。

2、水平拆分
水平拆分按照行进行拆分，常见的就是分库分表。以用户表为例，可以取用户ID,截取其中的第一个字符，将用户均匀的分配进这 0-9 、a-f 这16个表中。查找的时候也按照这种规则，又快又方便。当然类似的规则很多，也可以使用求余法，按照余数将数据分发进不同的表中。有些表业务关联比较强，那么可以使用按时间划分的。以我公司的某业务为例，每天都要新建一张表。这种业务类型就是需要高速插入，但是对于查询的效率不太关心。表越大，插入数据所需要索引维护的时间也就越长


create table tbl_v (
id int not null auto_increment,
user varchar(20) collate utf8mb4_general_ci default '',
createTime datetime default now(),
primary key (id)
)engine=innodb auto_increment=5 default charset=utf8mb4 collate=utf8mb4_general_ci;