文章目录
小问题
编码问题
从一个报错开始:
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 编码是如何使用的,包括三步:
- 字符编码,确定字符码值
其中的 utf8,意思就是使用 unicode 编码 - 存储传输规则的设定(character set)
编码确定了但是不知道怎么存储传输,所以需要 utf8(其他还有 utf16,utf32 等)
其中 utf8 和 utf8mb4 的区别:
utf8 为早期版本,最长字节为 3 ,实际上也就是 utf8mb3
utf8mb4 兼容了 4 字节的字符,比如 😊,😭,🚗 等
- 排序规则(collation)
前两者都确定了之后,数据库还要确定排序规则
utf8_general_ciutf8mb4_0900_ai_ci
要详细了解可以查看 cc 的另一篇文章 《mysql 的字符集、比较规则和服务端、客户端的相关配置》
大写表名找不到表
- 服务端加上配置
-
注意:mysql 服务配置位置为:
/etc/mysql/mysql.conf.d/mysqld.cnflower_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 指定用户名和密码
- –databases 指定数据库,可以多个库
-
示例
- 备份所有库
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_type,SESSION表示只影响当前的连接
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(1) 有什么区别?哪个性能最好?
- 效率
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 是否为 yesshow variables like '%partition%' -
MySQL 5.6.1 之后
可以查看一个参数名为 partition 的参数 status 是否为 activeSHOW 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>刷新表的统计数据

被折叠的 条评论
为什么被折叠?



