1.1.2text与blob一些注意事项：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

2.1库相关2.1.1建库mysql>CREATEDATABASEIFNOTEXISTSTESTDEFAULTCHARACTERSETUTF8COLLATEUTF8_GENERAL_CI;12.1.2删除库mysql>DROPDATABASEIFEXISTSTEST;12.2表相关2.2.1表创建mysql>CREATETABLEIFNOTEXISTS`tb`(??`runoob_id`INTUNSIGNEDAUTO_INCREMENT,??`runoob_title`VARCHAR(100)NOTNULL,??`runoob_author`VARCHAR(40)NOTNULL,??`submission_date`DATE,??PRIMARYKEY(`runoob_id`)

2.2.2表修改修改表名mysql>ALTERTABLEtbRENAMEtb_new;

增加字段mysql>ALTERTABLEtbADD(additionalVARCHAR(50)NOTNULLDEFAULT'NO');

修改字段数据类型mysql>ALTERTABLEtbMODIFYadditionalVARCHAR(50)NOTNULLDEFAULT'NO';

修改字段名称mysql>ALTERTABLEtbCHANGEoldnewVARCHAR(50)NOTNULLDEFAULT'NO';

修改字段注释mysql>ALTERTABLEtbMODIFYCOLUMNnewVARCHAR(50)COMMENT'这是注释';

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

mysql>alterviewview1asselectnamefromdepartment

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

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

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

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

3.2查看使用showtablestatuslike'表名';，查看表的使用状态

数据总大小=data_length+index_length

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

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

mysql>ALTERTABLEtable_nameENGINE=Innodb;1InnoDB还会将数据缓存到InnoDB缓存中，为保证预期，需释放系统缓存

[root@db-test~]#echo3>/proc/sys/vm/drop_caches1MyISAM：

mysql>OPTIMIZETABLEtable_name;1注：

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

***************************1.row***************************??Name:tt?Engine:InnoDBVersion:10???Row_format:Dynamic??Rows:2?Avg_row_length:8192??Data_length:16384Max_data_length:0??Index_length:0???Data_free:0?Auto_increment:NULL??Create_time:2022-05-0521:37:32??Update_time:NULL???Check_time:NULL???Collation:utf8mb4_general_ciChecksum:NULL?Create_options:?Comment:?1rowinset(0.00sec)4.2数据库使用情况mysql>SELECT?TABLE_SCHEMAASdatabase_name,SUM(ROUND((DATA_LENGTH+INDEX_LENGTH+DATA_FREE)/1024.0/1024,2))ASTotal_MBFROMinformation_schema.`TABLES`AST1WHERET1.`TABLE_SCHEMA`NOTIN('performance_schema','mysql','information_schema','sys')GROUPBYT1.`TABLE_SCHEMA`ORDERBYSUM(ROUND((DATA_LENGTH+INDEX_LENGTH+DATA_FREE)/1024.0/1024,2))DESCLIMIT10;

+---------------+----------+|database_name|Total_MB|+---------------+----------+|db??|??0.07||sys?|??0.02||testdb|??NULL|+---------------+----------+3rowsinset,1warning(0.32sec)

4.3查询InnoDB存储引擎表mysql>SELECT?TABLE_SCHEMAASdatabase_name,TABLE_NAMEAStable_name,TABLE_ROWSAStable_rows,ENGINEAStable_engine,ROUND((DATA_LENGTH)/1024.0/1024,2)ASData_MB,ROUND((INDEX_LENGTH)/1024.0/1024,2)ASIndex_MB,ROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024,2)ASTotal_MB,ROUND((DATA_FREE)/1024.0/1024,2)ASFree_MBFROMinformation_schema.`TABLES`AST1WHERET1.`TABLE_SCHEMA`NOTIN('performance_schema','mysql','information_schema','sys')ANDT1.`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|+---------------+------------+------------+--------------+---------+----------+----------+---------+3rowsinset,1warning(0.01sec)

4.4查看较大的表#查看数据表较大的表mysql>SELECT?TABLE_SCHEMAASdatabase_name,TABLE_NAMEAStable_name,TABLE_ROWSAStable_rows,ENGINEAStable_engine,ROUND((DATA_LENGTH)/1024.0/1024,2)ASData_MB,ROUND((INDEX_LENGTH)/1024.0/1024,2)ASIndex_MB,ROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024,2)ASTotal_MB,ROUND((DATA_FREE)/1024.0/1024,2)ASFree_MBFROMinformation_schema.`TABLES`AST1WHERET1.`TABLE_SCHEMA`NOTIN('performance_schema','mysql','information_schema','sys')ORDERBYT1.`TABLE_ROWS`DESCLIMIT10;

#查看数据表空间较大的表mysql>SELECT?TABLE_SCHEMAASdatabase_name,TABLE_NAMEAStable_name,TABLE_ROWSAStable_rows,ENGINEAStable_engine,ROUND((DATA_LENGTH)/1024.0/1024,2)ASData_MB,ROUND((INDEX_LENGTH)/1024.0/1024,2)ASIndex_MB,ROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024,2)ASTotal_MB,ROUND((DATA_FREE)/1024.0/1024,2)ASFree_MBFROMinformation_schema.`TABLES`AST1WHERET1.`TABLE_SCHEMA`NOTIN('performance_schema','mysql','information_schema','sys')ORDERBYROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024,2)DESCLIMIT10;

+---------------+-------------+------------+--------------+---------+----------+----------+---------+|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|+---------------+-------------+------------+--------------+---------+----------+----------+---------+10rowsinset,1warning(0.02sec)

+---------------+-------------+------------+--------------+---------+----------+----------+---------+|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|+---------------+-------------+------------+--------------+---------+----------+----------+---------+10rowsinset,1warning(0.02sec)

4.5查看碎片较多的表mysql>SELECT?TABLE_SCHEMAASdatabase_name,TABLE_NAMEAStable_name,TABLE_ROWSAStable_rows,ENGINEAStable_engine,ROUND((DATA_LENGTH)/1024.0/1024,2)ASData_MB,ROUND((INDEX_LENGTH)/1024.0/1024,2)ASIndex_MB,ROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024,2)ASTotal_MB,ROUND((DATA_FREE)/1024.0/1024,2)ASFree_MB,ROUND(ROUND((DATA_FREE)/1024.0/1024,2)/ROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024,2)*100,2)ASFree_PercentFROMinformation_schema.`TABLES`AST1WHERET1.`TABLE_SCHEMA`NOTIN('performance_schema','mysql','information_schema',)ANDROUND(ROUND((DATA_FREE)/1024.0/1024,2)/ROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024,2)*100,2)>10ANDROUND((DATA_FREE)/1024.0/1024,2)>100ORDERBYROUND(ROUND((DATA_FREE)/1024.0/1024,2)/ROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024,2)*100,2)DESCLIMIT10;

4.6查看表当前自增值mysql>SELECT?T2.TABLE_SCHEMA,T2.TABLE_NAME,?T1.COLUMN_NAME,T1.COLUMN_TYPE,T2.AUTO_INCREMENTFROMinformation_schema.columnsAST1INNERJOINinformation_schema.tablesAST2ONT1.TABLE_SCHEMA=T2.TABLE_SCHEMAANDT1.TABLE_NAME=T2.TABLE_NAMEWHERET1.EXTRA='auto_increment'ANDT1.DATA_TYPENOTLIKE'%bigint%'ORDERBYT2.AUTO_INCREMENTDESCLIMIT100;

144.7查看无主键的表mysql>SELECTTABLE_SCHEMAASdatabase_name,TABLE_NAMEAStable_name,TABLE_ROWSAStable_rows,ENGINEAStable_engine,ROUND((DATA_LENGTH)/1024.0/1024,2)ASData_MB,ROUND((INDEX_LENGTH)/1024.0/1024,2)ASIndex_MB,ROUND((DATA_LENGTH+INDEX_LENGTH)/1024.0/1024,2)ASTotal_MB,ROUND((DATA_FREE)/1024.0/1024,2)ASFree_MBFROMinformation_schema.tablesWHERE(table_schema,table_name)NOTIN(SELECTDISTINCTtable_schema,table_nameFROMinformation_schema.columnsWHERECOLUMN_KEY='PRI')ANDtable_schemaNOTIN('sys','mysql','information_schema','performance_schema');