MySQL操作

1.1.2 text与blob
一些注意事项:

1)执行大量的删除或更新操作,删除数据后会产生很大的“空洞”,为提升性能,需定期使用OPTIMIZE TABLE进行碎片整理

2)使用合成的索引,可根据其他列的内容建立散列值,并单独存放在数据列中,仅用于精确匹配的查询,可用MD5函数生成散列值,或者SHA1或CRC32,注意尾部带有空格生成算法的不能存储在CHAR或VARCAHR列中,会受到尾部去除的影响

3)避免检索大量的blob和text值,减少在网络上传输大量的数据

4)把blob与text分离到单表,可将原表的数据列转换成定长的数据行格式,减少主表的碎片,获得性能优势

1.1.3 浮点数与定点数
浮点数:float、double

定点数:decimal

注:

1、浮点数会存在误差问题

2、货币等敏感数据应使用定点数存储

3、避免使用浮点数进行比较

4、注意浮点数一些特殊值的处理

1.2 字符集
1.2.1 支持的字符集
MySQL支持多种字符集,可在同一台服务器、同一个数据库,甚至同一个表的不同字段指定不同的字符集,有较强的灵活性

MySQL字符集包括了字符集(CHARACTER)和校对规则(COLLATION)两个概念,字符集定义存储字符串的方式,校对规则定义比较字符串的方式,字符集和校对规则是一对多的关系

1.2.2 Unicode规范
Unicode是一种编码规范,类似ASCII码,由国际组织设计,可容纳全世界所有语言文字的编码方案,Unicode有两套标准,UCS-2和USC-4,前者2个字节表示一个字符,后者4个字节表示一个字符

1.2.3 字符集设置
可以在 my.cnf 中设置:
[mysqld]
default-character-set=utf8
或者在启动选项中指定:
mysqld --default-character-set=utf8
或者在编译的时候指定:
./configure --with-charset=utf8

不设置默认会使用latin1作为服务器字符集,不指定校对规则会使用默认的校对规则

字符集的校对规则以字符集名称开头,以_ci(不区分大小写)_cs(区分大小写)或_bin(二进制文件)结尾

2 数据库DDL操作
DDL(Data Definition Language):数据定义语言DDL用来创建数据库中的各种对象——-表、视图、索引、同义词、聚簇等。关键字主要包括CREATE、DROP、ALTER(/VIEW/INDEX/SYN/CLUSTER)等。
DDL操作是隐性提交的,不能rollback

2.1 库相关
2.1.1 建库
mysql> CREATE DATABASE IF NOT EXISTS TEST DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;
1
2.1.2 删除库
mysql> DROP DATABASE IF EXISTS TEST;
1
2.2 表相关
2.2.1 表创建
mysql> CREATE TABLE IF NOT EXISTS `tb`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )

2.2.2 表修改
修改表名
mysql> ALTER TABLE tb RENAME tb_new;

增加字段
mysql> ALTER TABLE tb ADD (additional VARCHAR(50) NOT NULL DEFAULT 'NO');

修改字段数据类型
mysql> ALTER TABLE tb MODIFY additional VARCHAR(50) NOT NULL DEFAULT 'NO';

修改字段名称
mysql> ALTER TABLE tb CHANGE old new VARCHAR(50) NOT NULL DEFAULT 'NO';

修改字段注释
mysql> ALTER TABLE tb MODIFY COLUMN new VARCHAR(50) COMMENT '这是注释';

2.2.3 表删除
mysql> DROP TABLE IF EXISTS tb;
1
2.3 视图相关
2.3.1 视图创建
mysql> create [algorithm = {undefined | merge | temptable}] 
    view 视图名 [{属性清单}]
    as select 语句
    [with [cascaded|local] check option];
    -- algorithm:选择的算法
    -- with check option:表示更新视图时要保证在该视图的权限范围之内
    -- undfined:表示MySQL自动选择所需使用的算法
    -- merge:表示将视图的语句与视图的定义合并,使得视图定义的某一部分取代语句的对应部分(查询的时候把视图转换为语句合并到查询语句中去)
    -- temptable:将视图的结果存入临时表(将视图转换为子查询,当做临时表来查)
    -- cascaded:表示更新视图时要满足所有相关视图和表的条件
    -- local:表示更新视图时,要满足该视图本身的定义的条件即可

2.3.2 视图修改
通过create or replace view修改,无则创建,有则修改

可通过alter修改,仅修改

mysql> alter view view1
        as select name
        from department

2.3.3 视图删除
mysql> drop view [if exists] view1;
1
3 表碎片整理
3.1 简介
InnoDB表数据存储在页中,每个页存放多条记录,这些记录以树形结构组织,称为B+树

聚集索引的叶子节点包含行中所有字段的值,辅助索引的叶子节点包含索引列和主键列

在InnoDB中,删除行行为只是被标记成已删除,MySQL会通过Purge线程异步清理未用的索引键与行,但不会把释放出来的空间继续交由操作系统分配,导致页面存在很多空洞

删除数据会导致页page出现空白空间,大量随机的Delete操作必然在数据文件中造成不连续的空白空间,插入数据时,空白空间会被利用起来,造成了数据物理存储顺序与逻辑顺序不同,称之数据碎片

3.2 查看
使用show table status like '表名';,查看表的使用状态

 

数据总大小 = data_length + index_length

实际表空间文件大小 = rows * avg_row_length

碎片大小 = (数据总大小 — 实际表空间文件大小 )/1024/1024 = xxx MB

3.3 整理
存储引擎:

InnoDB:

mysql> ALTER TABLE table_name ENGINE = Innodb;
1
InnoDB还会将数据缓存到InnoDB缓存中,为保证预期,需释放系统缓存

[root@db-test ~]# echo 3 > /proc/sys/vm/drop_caches
1
MyISAM:

mysql> OPTIMIZE TABLE table_name;
1
注:

MySQL官方建议不要经常(每小时或每天)进行碎片整理,一般根据实际情况,只需要每周或者每月整理一次即可
在OPTIMIZE TABLE运行过程中,MySQL会锁定表
4 表统计信息
4.1 数据表状态查看
使用show table status like '表名'\G

*************************** 1. row ***************************
           Name: tt
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 2
 Avg_row_length: 8192
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2022-05-05 21:37:32
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)4.2 数据库使用情况
mysql> SELECT 
TABLE_SCHEMA AS database_name,
SUM(ROUND((DATA_LENGTH+INDEX_LENGTH+DATA_FREE)/1024.0/1024, 2)) AS Total_MB
FROM information_schema.`TABLES` AS T1
WHERE T1.`TABLE_SCHEMA` NOT IN('performance_schema','mysql','information_schema','sys')
GROUP BY T1.`TABLE_SCHEMA`
ORDER BY SUM(ROUND((DATA_LENGTH+INDEX_LENGTH+DATA_FREE)/1024.0/1024, 2)) DESC
LIMIT 10;

+---------------+----------+
| database_name | Total_MB |
+---------------+----------+
| db            |     0.07 |
| sys           |     0.02 |
| testdb        |     NULL |
+---------------+----------+
3 rows in set, 1 warning (0.32 sec)

4.3 查询InnoDB存储引擎表
mysql> SELECT 
TABLE_SCHEMA AS database_name,
TABLE_NAME AS table_name,
TABLE_ROWS AS table_rows,
ENGINE AS table_engine,
ROUND((DATA_LENGTH)/1024.0/1024, 2) AS Data_MB,
ROUND((INDEX_LENGTH)/1024.0/1024, 2) AS Index_MB,
ROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024, 2) AS Total_MB,
ROUND((DATA_FREE)/1024.0/1024, 2) AS Free_MB
FROM information_schema.`TABLES` AS T1
WHERE T1.`TABLE_SCHEMA` NOT IN('performance_schema','mysql','information_schema','sys')
AND T1.`ENGINE` IN ('innodb'); #可调整参数

12
+---------------+------------+------------+--------------+---------+----------+----------+---------+
| database_name | table_name | table_rows | table_engine | Data_MB | Index_MB | Total_MB | Free_MB |
+---------------+------------+------------+--------------+---------+----------+----------+---------+
| db            | tt         |          2 | InnoDB       |    0.02 |     0.00 |     0.02 |    0.00 |
| db            | ty         |          3 | InnoDB       |    0.03 |     0.00 |     0.03 |    0.00 |
| db            | yy         |          0 | InnoDB       |    0.02 |     0.00 |     0.02 |    0.00 |
+---------------+------------+------------+--------------+---------+----------+----------+---------+
3 rows in set, 1 warning (0.01 sec)

4.4 查看较大的表
#查看数据表较大的表
mysql> SELECT 
TABLE_SCHEMA AS database_name,
TABLE_NAME AS table_name,
TABLE_ROWS AS table_rows,
ENGINE AS table_engine,
ROUND((DATA_LENGTH)/1024.0/1024, 2) AS Data_MB,
ROUND((INDEX_LENGTH)/1024.0/1024, 2) AS Index_MB,
ROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024, 2) AS Total_MB,
ROUND((DATA_FREE)/1024.0/1024, 2) AS Free_MB
FROM information_schema.`TABLES` AS T1
WHERE T1.`TABLE_SCHEMA` NOT IN('performance_schema','mysql','information_schema','sys')
ORDER BY T1.`TABLE_ROWS` DESC
LIMIT 10;

#查看数据表空间较大的表
mysql> SELECT 
TABLE_SCHEMA AS database_name,
TABLE_NAME AS table_name,
TABLE_ROWS AS table_rows,
ENGINE AS table_engine,
ROUND((DATA_LENGTH)/1024.0/1024, 2) AS Data_MB,
ROUND((INDEX_LENGTH)/1024.0/1024, 2) AS Index_MB,
ROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024, 2) AS Total_MB,
ROUND((DATA_FREE)/1024.0/1024, 2) AS Free_MB
FROM information_schema.`TABLES` AS T1
WHERE T1.`TABLE_SCHEMA` NOT IN('performance_schema','mysql','information_schema','sys')
ORDER BY
ROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024, 2)
DESC LIMIT 10;

+---------------+-------------+------------+--------------+---------+----------+----------+---------+
| database_name | table_name  | table_rows | table_engine | Data_MB | Index_MB | Total_MB | Free_MB |
+---------------+-------------+------------+--------------+---------+----------+----------+---------+
| db            | ty          |          3 | InnoDB       |    0.03 |     0.00 |     0.03 |    0.00 |
| db            | tt          |          2 | InnoDB       |    0.02 |     0.00 |     0.02 |    0.00 |
| db            | yy          |          0 | InnoDB       |    0.02 |     0.00 |     0.02 |    0.00 |
+---------------+-------------+------------+--------------+---------+----------+----------+---------+
10 rows in set, 1 warning (0.02 sec)

+---------------+-------------+------------+--------------+---------+----------+----------+---------+
| database_name | table_name  | table_rows | table_engine | Data_MB | Index_MB | Total_MB | Free_MB |
+---------------+-------------+------------+--------------+---------+----------+----------+---------+
| db            | ty          |          3 | InnoDB       |    0.03 |     0.00 |     0.03 |    0.00 |
| db            | tt          |          2 | InnoDB       |    0.02 |     0.00 |     0.02 |    0.00 |
| db            | yy          |          0 | InnoDB       |    0.02 |     0.00 |     0.02 |    0.00 |
+---------------+-------------+------------+--------------+---------+----------+----------+---------+
10 rows in set, 1 warning (0.02 sec)

4.5 查看碎片较多的表
mysql> SELECT 
TABLE_SCHEMA AS database_name,
TABLE_NAME AS table_name,
TABLE_ROWS AS table_rows,
ENGINE AS table_engine,
ROUND((DATA_LENGTH)/1024.0/1024, 2) AS Data_MB,
ROUND((INDEX_LENGTH)/1024.0/1024, 2) AS Index_MB,
ROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024, 2) AS Total_MB,
ROUND((DATA_FREE)/1024.0/1024, 2) AS Free_MB,
ROUND(ROUND((DATA_FREE)/1024.0/1024, 2) /ROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024, 2)*100,2)AS Free_Percent
FROM information_schema.`TABLES` AS T1
WHERE T1.`TABLE_SCHEMA` NOT IN('performance_schema','mysql','information_schema',)
AND ROUND(ROUND((DATA_FREE)/1024.0/1024, 2) /ROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024, 2)*100,2) >10
AND ROUND((DATA_FREE)/1024.0/1024, 2)>100
ORDER BY ROUND(ROUND((DATA_FREE)/1024.0/1024, 2) /ROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024, 2)*100,2) DESC
LIMIT 10;

4.6 查看表当前自增值
mysql> SELECT 
T2.TABLE_SCHEMA,
T2.TABLE_NAME, 
T1.COLUMN_NAME,
T1.COLUMN_TYPE,
T2.AUTO_INCREMENT
FROM information_schema.columns AS T1
INNER JOIN information_schema.tables AS T2
ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA
AND T1.TABLE_NAME=T2.TABLE_NAME
WHERE T1.EXTRA='auto_increment'
AND T1.DATA_TYPE NOT LIKE '%bigint%'
ORDER BY T2.AUTO_INCREMENT DESC
LIMIT 100;

14
4.7 查看无主键的表
mysql> SELECT
TABLE_SCHEMA AS database_name,
TABLE_NAME AS table_name,
TABLE_ROWS AS table_rows,
ENGINE AS table_engine,
ROUND((DATA_LENGTH)/1024.0/1024, 2) AS Data_MB,
ROUND((INDEX_LENGTH)/1024.0/1024, 2) AS Index_MB,
ROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024, 2) AS Total_MB,
ROUND((DATA_FREE)/1024.0/1024, 2) AS Free_MB
FROM information_schema.tables
WHERE (table_schema, table_name) NOT IN (
SELECT DISTINCT table_schema, table_name
FROM information_schema.columns
WHERE COLUMN_KEY = 'PRI'
)
AND table_schema NOT IN ('sys', 'mysql', 'information_schema', 'performance_schema');

 

文章链接: https://www.mfisp.com/15546.html

文章标题:MySQL操作

文章版权:梦飞科技所发布的内容,部分为原创文章,转载请注明来源,网络转载文章如有侵权请联系我们!

声明:本站所有文章,如无特殊说明或标注,均为本站原创发布。任何个人或组织,在未征得本站同意时,禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益,可联系我们进行处理。

给TA打赏
共{{data.count}}人
人已打赏
云虚拟主机投稿分享

云服务器有多便捷

2023-1-10 14:06:29

建站教程投稿分享

Linux集群之LB

2023-1-10 16:18:45

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
客户经理
个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索

梦飞科技 - 最新云主机促销服务器租用优惠