Lin Hong's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

MySQL 8.0 Study 008 Tips

2023-02-03
MySQL
 

MySQL 8.0 Study 008 Tips

学习系列

  • MySQL max_allowed_packet
  • MySQL 执行计划 type 说明
  • MySQL 一些语句
    • 查看mysql.user将用户名和白名单拼接显示
    • 修改存储引擎语句
    • 单库单表方式备份业务库中表

MySQL max_allowed_packet

MySQ导出数据报错

mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `aaa` at row: 28112

原因： MySQL根据配置文件会限制Server接受的数据包大小。有时候大的插入和更新会受 max_allowed_packet 参数限制，导致大数据写入或者更新失败。

修改方法1(配置文件持久化修改)： 注意：修改配置文件以后，需要重启mysql服务才能生效

vi /etc/my.cnf
[mysqld]
max_allowed_packet = 521M

临时解决方案,修改该值: 1.命令行修改时，不能用M、G，只能这算换成字节数设置。配置文件修改才允许设置M、G单位。 2.命令行修改之后，需要退出当前回话(关闭当前mysql server链接)，然后重新登录才能查看修改后的值。通过命令行修改只能临时生效，下次数据库重启后又复原了。 3.max_allowed_packet 最大值是1G(1073741824)，如果设置超过1G，查看最终生效结果也只有1G。

show variables like '%max_allow%';
set global max_allowed_packet = 512 * 1024 * 1024;

mysql [localhost:8032] {msandbox} ((none)) > set global max_allowed_packet=1073741825;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql [localhost:8032] {msandbox} ((none)) > show warnings;
+---------+------+------------------------------------------------------------+
| Level   | Code | Message                                                    |
+---------+------+------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect max_allowed_packet value: '1073741825' |
+---------+------+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql [localhost:8032] {msandbox} ((none)) > set global max_allowed_packet=1073741824;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8032] {msandbox} ((none)) >

MySQL 执行计划 type 说明

执行计划 type的说明

typ 有: system, const, eq_ref, ref, fulltext, ref_or_null, index_merge, unique_subquery, index_subquery, range, index, allowed

表示访问表是通过什么方式访问

system:

满足下面条件时候 type是 system.

  • 存储引擎: MyISAM/MEMORY
  • 记录只有一条

如果是innodb存储引擎，这种情况是ALL

mysql [localhost:8032] {msandbox} (test) > create table s1(id int_ engine=MyISAM;
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 'int_ engine=MyISAM' at line 1
mysql [localhost:8032] {msandbox} (test) > create table s1(id int) engine=MyISAM;
Query OK, 0 rows affected (0.06 sec)

mysql [localhost:8032] {msandbox} (test) > insert into s1 values(1);
Query OK, 1 row affected (0.04 sec)

mysql [localhost:8032] {msandbox} (test) > explain select * from s1;
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql [localhost:8032] {msandbox} (test) > create table s2(id int);
Query OK, 0 rows affected (0.13 sec)

mysql [localhost:8032] {msandbox} (test) > insert into s2 values(1);
Query OK, 1 row affected (0.01 sec)

mysql [localhost:8032] {msandbox} (test) > explain select * from s2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | s2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql [localhost:8032] {msandbox} (test) > 
mysql [localhost:8032] {msandbox} (test) > insert into s1 value(2);
Query OK, 1 row affected (0.01 sec)

mysql [localhost:8032] {msandbox} (test) > select * from s1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql [localhost:8032] {msandbox} (test) > explain select * from s1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql [localhost:8032] {msandbox} (test) >

const

通过主键或者唯一二级索引的等值常数匹配，访问单表是 const 类型

create table s1(
    col1 int not null,
    col2 int not null,
    col3 int not null,
    primary key(col1)
);

create index idx_s1_col2 on s1(col2);
create unique index idx_s1_unique_col3 on s1(col3);

mysql [localhost:8032] {msandbox} (test) > insert into s1 values(1,1,1);
Query OK, 1 row affected (0.02 sec)

mysql [localhost:8032] {msandbox} (test) > insert into s1 values(2,2,2);
Query OK, 1 row affected (0.01 sec)

mysql [localhost:8032] {msandbox} (test) > insert into s1 values(3,3,3);
Query OK, 1 row affected (0.01 sec)

mysql [localhost:8032] {msandbox} (test) > explain select * from s1 where col1=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql [localhost:8032] {msandbox} (test) > explain select * from s1 where col2=1;
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | ref  | idx_s1_col2   | idx_s1_col2 | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql [localhost:8032] {msandbox} (test) > explain select * from s1 where col3=1;
+----+-------------+-------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys      | key                | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | s1    | NULL       | const | idx_s1_unique_col3 | idx_s1_unique_col3 | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql [localhost:8032] {msandbox} (test) >

eq_ref

对被驱动表的关联字段访问方式

对s1被驱动表通过主键的等值匹配去查找

mysql [localhost:8032] {msandbox} (test) > explain select * from s1,s2 where s1.col1=s2.col1;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref          | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------+
|  1 | SIMPLE      | s2    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL         |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | s1    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.s2.col1 |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql [localhost:8032] {msandbox} (test) > 
mysql [localhost:8032] {msandbox} (test) > explain format=tree select * from s1,s2 where s1.col1=s2.col1\G
*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join  (cost=0.70 rows=1)
    -> Table scan on s2  (cost=0.35 rows=1)
    -> Single-row index lookup on s1 using PRIMARY (col1=s2.col1)  (cost=0.35 rows=1)

1 row in set (0.00 sec)

mysql [localhost:8032] {msandbox} (test) >

如果是非主键的二级索引，则是ref，如果容许null，则是ref_or_null

mysql [localhost:8032] {msandbox} (test) > explain select * from s1,s2 where s1.col2=s2.col1;
+----+-------------+-------+------------+------+---------------+-------------+---------+--------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref          | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+--------------+------+----------+-------+
|  1 | SIMPLE      | s2    | NULL       | ALL  | PRIMARY       | NULL        | NULL    | NULL         |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | s1    | NULL       | ref  | idx_s1_col2   | idx_s1_col2 | 4       | test.s2.col1 |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+--------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql [localhost:8032] {msandbox} (test) > 
mysql [localhost:8032] {msandbox} (test) > explain select * from s3 where col2='a' or col2 is null;
+----+-------------+-------+------------+-------------+---------------+-------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type        | possible_keys | key         | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------------+---------------+-------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | s3    | NULL       | ref_or_null | idx_s3_col2   | idx_s3_col2 | 43      | const |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------------+---------------+-------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql [localhost:8032] {msandbox} (test) >

MySQL 一些语句

查看mysql.user将用户名和白名单拼接显示

select concat(user,"@",host) from mysql.user;

修改存储引擎语句

select
    table_schema,
    table_name,
    engine
from
    information_schema.tables
where
    engine <> 'innodb'
    and table_schema not in (
        'sys',
        'mysql',
        'information_schema',
        'performance_schema'
    );

select
    concat(
        "alter table ",
        table_schema,
        ".",
        table_name,
        " engine=innodb;"
    ) as change_engine_list
from
    information_schema.tables
where
    engine <> 'innodb'
    and table_schema not in (
        'sys',
        'mysql',
        'information_schema',
        'performance_schema'
    );

mysql [localhost:8032] {msandbox} (test) > create table t_mysiam(id int) engine=myisam;
Query OK, 0 rows affected (0.02 sec)

mysql [localhost:8032] {msandbox} (test) > select table_schema,table_name,engine from information_schema.tables where engine<>'innodb' and table_schema not in ('sys','mysql','information_schema','performance_schema');
+--------------+------------+--------+
| TABLE_SCHEMA | TABLE_NAME | ENGINE |
+--------------+------------+--------+
| test         | t_mysiam   | MyISAM |
+--------------+------------+--------+
1 row in set (0.00 sec)

mysql [localhost:8032] {msandbox} (test) > 
mysql [localhost:8032] {msandbox} (test) > select
    ->     concat(
    ->         "alter table ",
    ->         table_schema,
    ->         ".",
    ->         table_name,
    ->         " engine=innodb;"
    ->     ) as change_engine_list
    -> from
    ->     information_schema.tables
    -> where
    ->     engine <> 'innodb'
    ->     and table_schema not in (
    ->         'sys',
    ->         'mysql',
    ->         'information_schema',
    ->         'performance_schema'
    ->     );
+------------------------------------------+
| change_engine_list                       |
+------------------------------------------+
| alter table test.t_mysiam engine=innodb; |
+------------------------------------------+
1 row in set (0.00 sec)

mysql [localhost:8032] {msandbox} (test) >

单库单表方式备份业务库中表

select concat(" mysqldump -uroot -pmysql ",table_schema," ",table_name," >/mnt/backup/",table_schema,"_",table_name,".sql") as table_dump from information_schema.tables where table_schema='testsql';

mysql [localhost:8032] {msandbox} (test) > select concat(" mysqldump -uroot -pmysql ",table_schema," ",table_name," >/mnt/backup/",table_schema,"_",table_name,".sql") as table_dump from information_schema.tables where table_schema='testsql';
+----------------------------------------------------------------------------------+
| table_dump                                                                       |
+----------------------------------------------------------------------------------+
|  mysqldump -uroot -pmysql testsql course >/mnt/backup/testsql_course.sql         |
|  mysqldump -uroot -pmysql testsql course_old >/mnt/backup/testsql_course_old.sql |
|  mysqldump -uroot -pmysql testsql score >/mnt/backup/testsql_score.sql           |
|  mysqldump -uroot -pmysql testsql student >/mnt/backup/testsql_student.sql       |
|  mysqldump -uroot -pmysql testsql t1 >/mnt/backup/testsql_t1.sql                 |
|  mysqldump -uroot -pmysql testsql teacher >/mnt/backup/testsql_teacher.sql       |
+----------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

mysql [localhost:8032] {msandbox} (test) >

MySQL 脚本

#数据库对象
select db,name,lower(type) from mysql.proc where db not in ('sys','information_schema','performance_schema','mysql') union all SELECT table_schema,table_name,'view' from information_schema.views where table_schema not in ('sys','information_schema','performance_schema','mysql') union all SELECT TRIGGER_SCHEMA,TRIGGER_NAME,'trigger' FROM information_schema.triggers where TRIGGER_SCHEMA not in ('sys','information_schema','performance_schema','mysql');

#大表
SELECT table_schema,table_name,Round(Sum(data_length + index_length) / 1024 / 1024 / 1024, 1) data_size,round(data_free/1024/1024,1) as chip_size FROM information_schema.tables GROUP BY table_schema,table_name having data_size>20;

#自增id
SELECT a.table_schema,a.table_name, a.Auto_increment,b.COLUMN_TYPE  FROM information_schema.tables a  join information_schema.columns b on a.table_schema=b.table_schema and a.table_name=b.table_name where a.TABLE_SCHEMA not in ('information_schema','performance_schema', 'mysql', 'sys') and EXTRA='auto_increment' and b.COLUMN_TYPE like 'int%)' and a.Auto_increment>1500000000 union SELECT a.table_schema,a.table_name, a.Auto_increment,b.COLUMN_TYPE  FROM information_schema.tables a  join information_schema.columns b on a.table_schema=b.table_schema and a.table_name=b.table_name where a.TABLE_SCHEMA not in ('information_schema','performance_schema', 'mysql', 'sys') and EXTRA='auto_increment' and b.COLUMN_TYPE like 'int%unsigned'  and a.Auto_increment>3500000000;

#索引过多
select database_name,table_name,count(*) from mysql.innodb_index_stats where database_name='dbmetrics' and stat_description='Number of pages in the index' group by database_name,table_name having count(*) >6;

#索引字段过多
select database_name,table_name,index_name,count(*) from mysql.innodb_index_stats where database_name='dbmetrics' and stat_name not in ('size','n_leaf_pages') group by database_name,table_name,index_name having count(*) >3;

#无主键表
select table_schema, table_name from (select c.table_schema, c.table_name, case when column_key = 'PRI' then 1 else 0 end pk_flag   from (select table_schema, table_name      from information_schema.columns      where table_schema not in('information_schema', 'mysql', 'sys', 'performance_schema')     and ordinal_position = 1      and column_key != 'PRI') t,      information_schema.columns c      where t.table_schema = c.table_schema and t.table_name = c.table_name) i      group by table_schema, table_name      having sum(pk_flag) = 0;

Referece

参考:

MySQLにおける実行計画(EXPLAIN)の見方

Have a good work&life! 2023/02 via LinHong

Similar Posts

上一篇 MySQL 8.0 Study 007 Tips

下一篇 MySQL 8.0 Study 009 Tips

Comments

Content