mysql 杂记

小问题

编码问题

从一个报错开始:

SELECT count(0) FROM ms_city WHERE city_name LIKE concat('%', '😊', '%')
# 报错
# 1267 - Illegal mix of collations (utf8_general_ci,IMPLICIT) 
# 	and (utf8mb4_0900_ai_ci,COERCIBLE) for operation 'like', Time: 0.006000s

使用 😊 作为筛选条件去查询记录的时候,数据库报错,看起来是编码的问题
两种编码不兼容:
utf8_general_ci utf8mb4_0900_ai_ci
解答
先要明白 mysql 编码是如何使用的,包括三步:

  1. 字符编码,确定字符码值
    其中的 utf8,意思就是使用 unicode 编码
  2. 存储传输规则的设定(character set)
    编码确定了但是不知道怎么存储传输,所以需要 utf8(其他还有 utf16,utf32 等)
    其中 utf8 和 utf8mb4 的区别:

utf8 为早期版本,最长字节为 3 ,实际上也就是 utf8mb3
utf8mb4 兼容了 4 字节的字符,比如 😊,😭,🚗 等

  1. 排序规则(collation)
    前两者都确定了之后,数据库还要确定排序规则
    utf8_general_ci utf8mb4_0900_ai_ci

要详细了解可以查看 cc 的另一篇文章 《mysql 的字符集、比较规则和服务端、客户端的相关配置》

大写表名找不到表

  • 服务端加上配置
    • 注意:mysql 服务配置位置为: /etc/mysql/mysql.conf.d/mysqld.cnf

      lower_case_table_names=1
      
  • 重启 mysql 服务 service mysql restart

order by 排序不稳定

  • 对不唯一的字段排序的时候排序不稳定,导致分页问题
  • order by 后额外添加唯一字段

小知识

插入或更新

  • ignore
  • on duplicate key update
  • insert…select…where not exists
  • replace into

备份和还原

mysqldump 命令
备份
  • 参数

    • –databases 指定数据库,可以多个库 --databases db1 db2
    • –all-databases 备份所有数据库
    • –tables 指定表
    • –ignore-table 忽略一个表,可以多次使用本参数指定
    • –where 筛选一个表,和 sql 语句的 where 类似 --where='id=1 and name like concat("%","123","%")'
    • –no-data 不备份数据,只备份结构
    • –host --port 指定数据库的 host 和 port
    • -u -p 指定用户名和密码
  • 示例

    • 备份所有库
    mysqldump -uroot -p --all-databases > dump.db
    
    • 备份一个,多个库
    # 备份一个库
    mysqldump -uroot -p dbname > dump.db
    # 备份多个库
    mysqldump -uroot -p --databases dbname1 dbname2 > dump.db
    
    • 排除库中的一些表
    mysqldump -uroot -p dbname --ignore-table=dbname.table --ignore-table=dbname.table2 > dump.db
    
    • 备份一个库中指定的表
    mysqldump -uroot -p dbname table1 table2 > dump.db
    
    • 只备份一个库的结构
    mysqldump -uroot -pHello.001 --databases portable_gis_pd --no-data > dump.db
    
    • 远程执行数据库备份命令,将内容写到本地文件中
    ssh root@192.168.11.181 'mysqldump -uroot -p databasename --ignore-table=databasename.tablename1 --ignore-table=databasename.tablename2' > inverter.db
    
还原

如果没有指定的库需要手动创建

  • mysql 命令来还原
# 利用 mysqladmin 命令来创建,也可以先登录再创建
mysqladmin -uroot -p create db_name 
mysql -uroot -p  db_name < /backup/mysqldump/db_name.db
  • mysql 中的 source 来还原,即先登录 mysql,然后用 source 命令
use dbname;
source /root/dump.db
物理备份和还原
  • 源库表的加锁的解锁,加锁后使 tb1 只能读,不能写,保证数据一致性
# 加锁
flush table tb1 for export;
# 解锁
unlock tables;
  • 目标库建表
create table tb1 ...;
  • 丢弃目标库的表空间,会关闭表空间,并删除数据文件
alter table tb1 discard tablespace;
  • 从源库拷贝表文件:ibd,frm,需要修改权限归属人
  • 导入表空间并验证
alter table tb1 import tablespace;
check table tb1;
select count(*) from tb1;

escape 关键词指定转译字符,默认为 \

  • 指定字符替代 like 查询中的转译字符 \,可以指定多个
  • % 匹配若干个字符
  • _ 匹配单个字符
  • 使用 a 来作为转译字符
SELECT * FROM user WHERE name LIKE "a%_" ESCAPE "a"
# 和以下查询相同
SELECT * FROM user WHERE name LIKE "\%_"

时间戳和时间互转

-- 日期字符串转时间戳,10位,数据库存储的是13位
select UNIX_TIMESTAMP('2019-09-01 00:00:00');
-- 时间戳转日期
select FROM_UNIXTIME(1567267200, '%Y-%m-%d %H:%i:%s')

禁止使用缓存

  • 使用 SQL_NO_CACHE,>=5.7
select SQL_NO_CACHE * from t_c_user;
  • 在查询中使用一个用户变量,使之无法使用缓存
SELECT t.* FROM thetable t, (SELECT @a:=NULL) as init;
  • 设置 query_cache_size 为0,可以在配置文件中配置
  • 关闭缓存 query_cache_typeSESSION 表示只影响当前的连接
SHOW VARIABLES LIKE 'query_cache_type';
SET SESSION query_cache_type = OFF;
SHOW VARIABLES LIKE 'query_cache_type';
  • 重置缓存 RESET QUERY CACHE

count(*)、count(1)、count(id) 和 count(字段) 的效率

count(*) 会优化成 count(0),所以 count(*)=count(1)

  • 对于 count(*),count(1),count(id),如果存在非聚簇索引,那会使用 key_len 最小的非聚簇索引,因为非聚簇索引叶子结点数据是主键,没有其他数据(当然也是有的,如事务 id、用于事务和 MVCC 的回流指针以及所有的剩余列,这里的意思是和聚簇索引比较),体积小,加载快,优于聚簇索引,而 count(字段) 必须要读取字段值,判断值是否是 NULL,速度很慢
  • 但是对于 count(id) 来说需要读取主键的值,而 count(*) 和 count(1) 不需要
  • 对于 count(id) 为什么需要读取主键的值,因为虽然主键 not null,但是需要考虑多种情况,如 left join,右侧的 id 就可能为空 (此处为 cc 自己的想法,没有找到资料依据)
  • 对于 InnDB 是使用遍历来计数的,而 MyISAM 直接读取行数的记录值 row_count,而 InnDB 由于事务无法实现,因为在可重复度事务等级下,A 无法读取 B 未提交的事务,这样 A 和 B 统计所得的行数就会不同
  • 优化
    • 可以读取近似值

      • SHOW TABLE STATUS [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]
      show table status from test where name='test3'
      -- 或
      show table status from test like 'test3'
      
      • 使用 explain,和上面的值是一样的:explain select count(*) from test3
    • 自己手动维护 row_count 值

索引覆盖Using index的时候可以通过扫描二级索引来直接获取查询结果,不用回表,所以无论是 %xx 右模糊匹配,还是不符合最左原则的查询,都可以使用索引 index

表分区

介绍
  • 将一个表分区存储,删除,查询都会比较快
  • 只有在创建表的时候可以添加分区
  • 如果一个表一开始没有添加分区,就不能添加了
  • 如果一开始添加了某一个类型的分区,则后期可以进行添加分区或删除分区
数据库支持
  • MySQL数据库在 5.1 版本及以上时添加了对分区的支持
  • 查看是否支持
    • MySQL 5.6.1 之前
      查看 have_partitioning 参数的 value 是否为 yes

      show variables like '%partition%'
      
    • MySQL 5.6.1 之后
      可以查看一个参数名为 partition 的参数 status 是否为 active

      SHOW PLUGINS;
      
      • 方法二
      SELECT PLUGIN_NAME    AS NAME,
      PLUGIN_VERSION AS VERSION,
      PLUGIN_STATUS  AS STATUS
      FROM INFORMATION_SCHEMA.PLUGINS A
      WHERE PLUGIN_TYPE = 'STORAGE ENGINE'
      AND A.PLUGIN_NAME = 'partition';
      
      • 查看 partition 的 status 是否为 yes
分区类型

分区的列都需要是主键的一部分,否则会创建失败

Range
  • 利用取值范围来分区,范围要连续且不能重叠
//exp可以为列名或者表达式,比如to_date(created_date)
partition by range(exp)( 
partition p0 values less than(num)
)
  • 例子
# 如果是以下语句的话,无法插入 store_id > 20 的数据
# 为了能正常插入所有数值,可以使用 maxvalue
# 在最后添加 
# 	partition p2 values less than maxvalue
mysql> create table emp(
-> id INT NOT null,
-> store_id int not null
-> )
-> partition by range(store_id)(
->   partition p0 values less than(10),
->   partition p1 values less than(20)
-> );
by range(store_id)(
->   partition p0 values less than(10),
->   partition p1 values less than(20)
-> );
List
  • 用 in (1,2,3…) 来分区,如果插入的数据不在任何的区中则会插入失败
partition by list(exp)( //exp为列名或者表达式
partition p0 values in (3,5)  //值为3和5的在p0分区
)
  • 例子
mysql> create table emp1(
->   id int not null,
->   store_id int not null
-> )
-> partition by list(store_id)(
->   partition p0 values in (3,5),
->   partition p1 values in (2,6,7,9)
-> );
Hash
  • 可以分散热点读,让数据平均分布在不同分区中
  • 普通 Hash 分区
  • 取模确定分区位置,在重新分区的时候会有大量数据需要重新分区
// 对 store_id 来对 4 取模(store_id%4)确定分区位置,所以 4 也是分区个数
partition by hash(store_id) partitions 4;
  • 线性 Hash 分区
  • 解决修改分区需要重新散列数据的问题
partition by LINER hash(store_id) partitions 4;
Key
  • 和 Hash 类似
  • 例子
-- 指定主键字段进行分区
CREATE TABLE tm1 (
s1 CHAR(32) PRIMARY KEY
)
PARTITION BY KEY(s1)
PARTITIONS 10; -- 创建10个分区
Columns
  • Columns 分区就是由范围分区和列表分区封装得来的
  • 没有数据类型的限制
子分区

在分区之下再进行分区

  • 例子
-- 表中有3个分区,每个分区上有2个子分区,所以加起来一共有6个分区
CREATE TABLE ts (id INT, purchased DATE)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) ) SUBPARTITIONS 2 
(
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN MAXVALUE
);

delete 后磁盘空间不回收

  • trancate 和 drop 是直接删除,不用担心回收问题
  • delete from 表之后,mysql 只是对记录打删除标志,而不会真正对表进行删除,硬盘空间自然也不能回收
  • delete from 表之后,可以运行 alter table <table_name> engine='innodb' 来让表重新整理索引,以对磁盘空间进行回收

客户端查询结果竖排显示

mysql> show table status like 'single%';
+--------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name         | Engine | Version | Row_format | Rows  | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment |
+--------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| single_table | InnoDB |      10 | Dynamic    | 99500 |            110 |    11026432 |               0 |     18972672 |   4194304 |         100003 | 2022-07-06 07:15:07 | 2022-07-06 08:00:21 | NULL       | utf8_general_ci |     NULL |                |         |
+--------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)
  • 用 G 结尾,而不是 ;,修改为竖排显示
mysql> show table status like 'single%'\G
*************************** 1. row ***************************
Name: single_table
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 99500
Avg_row_length: 110
Data_length: 11026432
Max_data_length: 0
Index_length: 18972672
Data_free: 4194304
Auto_increment: 100003
Create_time: 2022-07-06 07:15:07
Update_time: 2022-07-06 08:00:21
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

在查询时,尽量不使用 mysql 的字符串和数值自动转化

  • 在 in 查询时,如果对于字符串字段使用数值匹配,会有匹配头字符的问题
insert into s1 (key1) values ('112abc');
select * from s1 where key1 in (11); -- 结果为空
empty set;
select * from s1 where key1 in (112); -- 结果为 1 条
key1
112abc
-- 这是因为 mysql 优先使用数值
-- 当使用数值和字符串一起做运算的时候,mysql 会把字符串转化为数值
-- 112abc -> 112,mysql 忽略了 abc,因为他们无法组成数值
-- 所以当 112,匹配的时候能匹配到数据,而 11 无法匹配成功

show 关键词

  • show engines;
    • 查看数据库所有的存储引擎
  • show engine innodb status;
    • 查看 innodb 引擎的状态
  • show databases;
    • 查看所有的库
  • show tables [from db_name] [like …];
    • 查看库中所有的表
  • show create table ;
    • 查看表的创建信息
  • show table status [from db_name] [like …];
    • 查看表的统计信息,引擎,行格式,预估数据行数等
  • show variables [like …];
    • 查看系统变量
  • show [SESSION | GLOBAL] status [like …];
    • 查看系统状态变量
  • show charset [like …];
    • 查看支持的字符集, character set 和 charset 两个同义
  • show collation [like …];
    • 查看比较规则
  • show index from <table_name> [from db_name]
    • 查看指定表的索引
    • 联合索引会表现为多行记录,通过 seq_in_index 来看联合索引
属性名描述
Table索引所属表的名称。
Non_unique索引列的值是否是唯一的聚簇索引和唯一二级索引的该列值为 0 ,普通二级索引该列值为 1 。
Key_name索引的名称。
Seq_in_index索引列在索引中的位置,从1开始计数。比如对于联合索引 idx_key_part 来说[:br]key_part1、key_part2 和 key_part3 对应的位置分别是 1、2、3。
Column_name索引列的名称。
Collation索引列中的值是按照何种排序方式存放的值为 A 时代表升序存放,为 NULL 时代表降序存放。
Cardinality索引列中不重复值的数量。后边我们会重点看这个属性的。
Sub_part对于存储字符串或者字节串的列来说有时候我们只想对这些串的前 n 个字符或字节建立索引,这个属性表示的就是那个 n 值。如果对完整的列建立索引的话,该属性的值就是 NULL 。
Packed索引列如何被压缩NULL 值表示未被压缩。这个属性我们暂时不了解,可以先忽略掉。
Null该索引列是否允许存储 NULL 值。
Index_type使用索引的类型,我们最常见的就是 BTREE ,其实也就是 B+ 树索引。
Comment索引列注释信息。
Index_comment索引注释信息。

alter 关键词

  • alter table <table_name> auto_increment= <num> ;
    • 修改自增 ID 的值,无法小于 max(id)
  • alter table <table_name> engine=‘innodb’;
    • 修改存储引擎, delete 后磁盘空间不回收
  • alter table <table_name> engine=‘innodb’, stats_persistent = (1 | 0);
    • 修改 innodb 表的统计信息是否存储到磁盘上,1 为存储到磁盘上
    • 全局配置为 innodb_stats_persistent
  • alter table <table_name> engine=‘innodb’, stats_sample_pages = <number>;
    • 修改记录数统计采集的页的数量
    • 全局配置为 innodb_stats_persistent_sample_pages

flush 关键词

  • flush optimizer_costs
    • 刷新成本模型
    • 应用 两个成本常数表 的修改
  • flush table <table_name>
    • 刷新表的相关设置,如应用 innodb_table_stats 的修改
  • ANALYZE TABLE
    • ANALYZE TABLE <table_name> 刷新表的统计数据
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

cctga

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值