MySQL大表优化

SQL语句优化

一、DDL(Data Definition Language 数据定义语言)

用于操作对象和对象的属性，这种对象包括数据库本身，以及数据库对象，像：表、视图等等，DDL 对这些对象和属性的管理和定义具体表现在 create、drop 和 alter 上。特别注意：DDL 操作的“对象”的概念，”对象“包括对象及对象的属性，而且对象最小也比记录大个层次。以表举例：create 创建数据表，alter 可以更改该表的字段，drop 可以删除这个表。由此得知，DDL 不涉及具体的数据。所有的 DDL 均将被隐式提交，无法 ROLLBACK。

1️⃣DDL的主要操作

1. 【create】
   可以创建数据库和数据库的一些对象。
2. 【drop】

• drop 是 DDL，会隐式提交，所以不能回滚，不会触发触发器。
• drop 删除表结构及所有数据，并将表所占用的空间全部释放。
• drop 将删除表的结构，所依赖的约束，触发器，索引以及数据表的权限等，依赖于该表的存储过程/函数将保留，但是变为 invalid 状态。

3. 【truncate】
   truncate table tabname一次性地从表中删除所有的数据页并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

• truncate 是 DDL，会隐式提交，所以不能回滚，不会触发触发器。
• truncate 会删除表中所有记录，并且将重新设置高水线和所有的索引，缺省情况下将空间释放到 minextents 个 extent，除非使用 reuse storage。不会记录日志，所以执行速度很快，但不能通过 rollback 撤消操作(如果一不小心把一个表 truncate 掉，也是可以恢复的，只是不能通过 rollback 来恢复)。
• 对于外键(foreign key)约束引用的表，不能使用 truncate table，而应使用不带 where 子句的 delete 语句。
• truncate table不能用于参与了索引视图的表。

4. 【alter】
   修改数据表定义及属性。

2️⃣DDL的操作对象(表)

1. 表的概念
   表的创建就是用来存放数据用的，由于存放的数据的不同，所以需要定义些数据类型，以方便管理。

2. 表的属性

【主键属性】
主键就是主键约束，主键的起名偏向于虚的(就是描述这件事)，主键约束起名偏向于实得(就是描述操作的实施)，描述的都是同一件事，主键约束就是表中的一个属性。在一个表中最多可以有一个主键，一个主键可以定义在一个或多个字段。主键使一个或多个字段的值必须唯一且不为空，这样做可以通过该字段或该组字段中的值唯一的代表一条记录。

【唯一属性】
一个表中只能有一个主键属性，为了方表用户，提出唯一约束。唯一约束可以定义在一个或多个字段上。唯一约束使该字段或该组字段中的值唯一，可以为空，但是不能重复。

【外键属性】
又叫外键、外键约束，跟主键和主键约束的关系是一样的。外键约束针对的两个表，如果表A的主关键字是表B中的字段，则该字段称为表B的外键，表A称为主表，表B称为从表。但要注意，必须要系统知道是这种关系。

【核查、Null 和缺省属性】
核查属性又叫核查约束，Null 属性又叫 Null 约束，缺省属性又叫缺省约束。这些名称是描述一件事，描述一种情况，这件事或这张情况。当然可以人为的那样特意做(输入数据时注意就行)，但本意是实现自动化，也就是让计算机做这件事。

二、DML(Data Manipulation Language 数据操控语言)

用于操作数据库对象中包含的数据，也就是说操作的单位是记录。

1️⃣DML的主要语句操作

1. 【insert】
   向数据表张插入一条记录。
2. 【delete】
   删除数据表中的一条或多条记录，也可以删除数据表中的所有记录。但是，它的操作对象仍是记录。

• delete 是DML，执行时，每次从表中删除一行，并且同时将该行的删除操作记录在 redo 和 undo 表空间中以便进行回滚(rollback)和重做操作，但要注意表空间要足够大，需要手动提交(commit)操作才能生效，可以通过 rollback 撤消操作。

• delete 可根据条件删除表中满足条件的数据，如果不指定 where 子句，那么删除表中所有记录。

• delete 不影响表所占用的 extent，高水线(high watermark)保持原位置不变。

3. 【update】
   用于修改已存在表中的记录的内容。

2️⃣DML的操作对象——记录

三、DCL(Data Control Language 数据控制语句)

DCL的操作是数据库对象的权限，这些操作的确定使数据更加的安全。

1️⃣DCL的主要语句操作

1. 【grant】
   允许对象的创建者给某用户或某组或所有用户(PUBLIC)某些特定的权限。
2. 【revoke】
   可以废除某用户或某组或所有用户访问权限

2️⃣DCL的操作对象(用户)：
此时的用户指的是数据库用户。

四、MySQL 相关基本语句

1️⃣插入记录

insert into tab (field1,field2) values (value1,value2);

2️⃣删除记录

delete from tab where conditions;

3️⃣更新记录

update tab set field1=value1,field2=value2 where conditions;

MySQL 给一个字段递增赋值：
①首先设置一个变量，初始值为0：set @r:=0;
②然后更新表中对应的ID列：update tab set id=(@r:=@r+1)
③如果是插入，那就找一个记录多的表 t1：
set @r:=0;
insert into t select @r:=@r+1 from t1 limit 0, 2000

4️⃣查询数据

select * from tab where conditions order by field desc;(精准查询)
asc升序【默认】 / desc降序
select * from tab where field like '%value%'(模糊查询)

order by id desc,time desc-----id 降序排列优先；id 一样的话，再按 time 降序排列(前提是满足 id 降序排列)。后面再加第三列的话，同理。
中文排序异常解决(第十点)

5️⃣提交数据/回滚数据

commit;//提交数据
rollback;//回滚数据

6️⃣总数

select count(*) from tab;

7️⃣求和/求平均值

select sum(field) as sumvalue from tab;//求和
select avg(field) as avgvalue from tab;//求平均值

8️⃣最大/最小

select max(field) as maxvalue from tab;//最大
select min(field) as minvalue from tab;//最小

9️⃣分组

grounp by：一张表，一旦分组完成后，查询只能得到组相关的信息。

🔟between限制查询数据范围时包括了边界值。

select * from tab where time between time1 and time2;
select a,b,c from tab where a not between num1 and num2;

1️⃣1️⃣in的用法

select * from tab where a [not] in ('值1','值2','值3','值4')

1️⃣2️⃣两张关联表，删除主表中已经在副表中没有的信息

delete from tab1 where not exists 
(select * from tab2 where tab1.field1=tab2.field2)

1️⃣3️⃣四表联查

select * from a 
left inner join b on a.a=b.b 
right inner join c on a.a=c.c 
inner join d on a.a=d.d 
where conditions

1️⃣4️⃣查询相同条目并记录重复次数(大于2)

select a,count(a) from tab group by a having count(*) > 2

1️⃣5️⃣创建数据库/删除数据库

create database database_name;//创建数据库
drop database database_name;## 删除数据库

1️⃣6️⃣创建新表/删除表

## 创建新表
create table dept(
    id  int(11)  not null  auto_increment,
    name  varchar(255)  default null,
    primary  key(dept_id)
 );
## 删除表
drop table tab;

1️⃣7️⃣根据已有的表创建新表

create table ntab like otab;(使用旧表B创建新表A)  (MySQL)

备注：此种方式在将表B复制到A时候，会将表B完整的字段结构和索引复制到表A中来。但不会复制数据。

create table ntab as select col1,col2... from otab definition only;      

备注：此种方式只会将B表的字段结构复制到表A中来，但不会复制表B中的索引到表A中来。这种方式比较灵活可以在复制原来表结构的同时指定要复制哪些字段，并且自身复制表也可以根据需要增加字段结构。

1️⃣8️⃣新增表的别名

comment on tab col is '测试';

1️⃣9️⃣更改表名

alter table tab_oldName rename to tab_newName;

2️⃣0️⃣增加一个列

alter table tab add  column_name type;

alter table 表名 ADD 字段名称 字段类型(字段长短-选填)  
NOT NULL[是否为null] default 0 comment '字段备注';

2️⃣1️⃣添加主键/删除主键

alter table tab add primary key(col);//添加主键
alter table tab drop primary key(col);//删除主键

一个数据表只可以有一个主键，所以不存在删除某一列的主键。

2️⃣2️⃣创建索引/删除索引索引不可以更改，想更改必须删除重新建。

//创建索引
create [unique] index idxname on tab(col...);
//删除索引
drop index idxname;
drop index idxname on table;

2️⃣3️⃣查看某个表的索引

show index from table_name

2️⃣4️⃣创建视图/删除视图

//创建视图
create view viewname as select statement;
//删除视图
drop view viewname;

五、pro

select * from dba_blockers;查询锁

select * from dba_waiters;查询被阻塞的会话

select column_name from tab_old intersect select column_name from tab_new;显示两表的相同数据

select @@version; 或 select version();查看数据库版本

select database();查看当前数据库

select user();查看当前用户

show tables;查看所有表

show columns from table;查看表中的列的基本信息

desc table; 或 describe table; 表名后加字段名，查看该字段基本信息

select CHARACTER_LENGTH(col) from table;查询该字段值的长度

六、Oracle 相关基本语句

1️⃣查询表名

select table_name,tablespace_name,temporary 
from user_tables [where table_name=upper('表名')];

1. table_name：表名(varchar2(30))
2. tablespace_name：存储表名的表空间(varchar2(30))
3. temporary：是否为临时表(varchar2(1))

2️⃣查询表列名

select column_name,data_type,data_length,data_precision,data_scale 
from user_tab_columns [where table_name=upper('表名')];

select table_name,column_name
from user_tab_columns
where column_name like '%xxx%' and table_name like '%xxx%';

1. column_name：列名(varchar2(30))
2. data_type：列的数据类型(varchar2(106))
3. data_length：列的长度(number)

另外，也可以通过 all_tab_columns来获取相关表的数据。

select * from all_tab_columns [where table_name='表名'];

3️⃣根据表名，查询表的索引

select * from user_indexes [where table_name=upper('表名')];
select* from all_indexes where table_name=upper('表名');
select * from user_ind_columns where table_name=upper('表名');

4️⃣根据索引名，查询表的索引字段

select * from user_ind_columns [where index_name=('索引名')];

5️⃣根据索引名，查询创建索引的语句

select dbms_metadata.get_ddl('INDEX','索引名', ['用户名']) from dual ;
-- ['用户名']可省，默认为登录用户。

6️⃣查询数据库版本

select * from v$version;

select * from product_component_version  
where SUBSTR(PRODUCT,1,6)='Oracle';

select version from v$instance;

七、drop、truncate 和 delete 的区别

1️⃣truncate 和 delete 只删除数据， drop 则删除整个表(结构和数据)。

2️⃣️truncate 只能对 table。delete 可以是 table 和 view。

3️⃣一般速度上，drop > truncate > delete。delete 操作不会减少表或索引所占用的空间，truncate 会将该表和索引所占用的空间会恢复到初始大小，drop 将表所占用的空间全释放掉。

4️⃣truncate 与不带 where 的 delete ：只删除数据，而不删除表的结构(定义)。drop 语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index)，依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。

5️⃣delete 执行的过程是每次从表中删除一行，同时将该删除操作作为事务记录在日志中保存以便进行回滚操作。该操作是 DML 会被放到 rollback segment 中，事务提交后才生效。如果有相应的 tigger，执行的时候将被触发。

truncate table 一次性删除表所有的数据并不记录日志，删除行不可恢复。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。truncate、drop 是 DDL 操作立即生效，原数据不放到 rollback segment 中，不能回滚。

6️⃣truncate table tabname速度快，效率高。truncate table 在功能上与不带 WHERE 子句的 delete 语句相同：二者均删除表中的全部行。但 truncate table 比 delete 速度快，且使用的系统和事务日志资源少。delete 语句每次删除一行，并在事务日志中为所删除的每行记录一项。truncate table 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

7️⃣truncate table 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 delete。如果要删除表定义及其数据，请使用 drop table 语句。

8️⃣对于由 foreign key 约束引用的表，不能使用 truncate table，而应使用不带 where 子句的 delete 语句。由于 truncate table 不记录在日志中，所以它不能激活触发器。

9️⃣总结

1. 在速度上，一般来说，drop> truncate > delete。

2. 在使用 drop 和 truncate 时一定要注意，虽然可以恢复，但为了减少麻烦，还是要慎重。

3. 如果想删除部分数据用 delete，注意带上 where 子句，回滚段要足够大；如果想删除表，当然用 drop；如果想保留表而将所有数据删除，如果和事务无关，用 truncate 即可；如果和事务有关，或者想触发 trigger，还是用 delete；如果是整理表内部的碎片，可以用 truncate 跟上 reuse stroage，再重新导入/插入数据。

八、MySQL 和 Oracle 的区别

MySQL 和 Oracle 都是流行的关系数据库管理系统(RDBMS)，在世界各地广泛使用。大多数数据库以类似的方式工作。两个数据库的特性是不同的。

1️⃣本质的区别

1. Oracle 数据库是一个对象关系数据库管理系统(ORDBMS)。它通常被成为 OracleRDBMS 或简称为 Oracle，是一个收费的数据库。
2. MySQL 是一个开源的关系数据库管理系统(RDBMS)。它是世界上使用最多的 RDBMS(Relational Database Management System，关系数据库管理系统)，作为服务器运行，提供多个数据库的多用户访问。它是一个免费的数据库。

2️⃣数据库安全性

1. MySQL 使用三个参数来验证用户，即用户名、密码和位置。
2. Oracle 使用了许多安全功能，如用户名，密码，配置文件，本地身份验证，外部身份验证，高级安全增强功能等。

3️⃣SQL语法的区别

Oracle 的 SQL 语法与 MySQL 有很大不同。Oracle 为称为 PL/SQL 的编程语言提供了更大的灵活性。Oracle 的 SQL*Plus 工具提供了比 MySQL 更多的命令，用于生成报表输出和变量定义。

4️⃣存储上的区别

与 Oracle 相比，MySQL 没有表空间、角色管理、快照、同义词和包以及自动存储管理。

5️⃣对象名称的区别

虽然某些模式对象名称在 Oracle 和 MySQL 中都不区分大小写，例如列、存储过程和索引等。但在某些情况下，两个数据库之间的区别大小写是不同的：

1. Oracle 对所有对象名称都不区分大小写。
2. 某些 MySQL 对象名称(如数据库和表)区分大小写(取决于底层操作系统)。

6️⃣运行程序和外部程序支持

• Oracle 数据库支持从数据库内部编写，编译和执行的几种编程语言。此外，为了传输数据，Oracle 数据库使用 XML。
• MySQL 不支持在系统内执行其他语言，也不支持 XML。

7️⃣MySQL和Oracle的字符数据类型比较

两个数据库中支持的字符类型存在一些差异。

1. 对于字符类型，MySQL 具有 CHAR 和 VARCHAR，最大长度允许为65535字节(CHAR 最多可以为255字节，VARCHAR 为65535字节)。
2. 而 Oracle 支持四种字符类型，即 CHAR、NCHAR、VARCHAR2和NVARCHAR2。所有四种字符类型都需要至少1个字节长；CHAR 和 NCAHR 最大可以是2000个字节，VARCHAR2 和 NVARCHAR2 的最大限制是4000个字节。可能会在最新版本中进行扩展。

8️⃣MySQL 和 Oracle 的额外功能比较

1. MySQL 数据库不支持其服务器上的任何功能，如 Audit Vault。
2. Oracle支持其数据库服务器上的几个扩展和程序，例如 Active Data Guard，Audit Vault，Partitioning 和 Data Mining 等。

9️⃣临时表的区别：Oracle 和 MySQL 以不同方式处理临时表。

1. 在 MySQL 中，临时表是仅对当前用户会话可见的数据库对象，并且一旦会话结束，这些表将自动删除。
2. Oracle 中临时表的定义与 MySQL 略有不同，因为临时表一旦创建就会存在，直到它们被显式删除，并且对具有适当权限的所有会话都可见。但是，临时表中的数据仅对数据插入表中的用户会话可见，并且数据可能在事务或用户会话期间持续存在。

🔟MySQL 和 Oracle 中的备份类型

1. Oracle 提供不同类型的备份工具，如冷备份，热备份，导出，导入，数据泵。Oracle 提供了最流行的称为 RecoveryManager(RMAN)的备份实用程序，可以使用极少的命令或存储脚本自动化备份调度和恢复数据库。
2. MySQL 有 mysqldump 和 mysqlhotcopy 备份工具。在 MySQL 中没有像 RMAN 这样的实用程序。

1️⃣1️⃣Oracle 和 MySQL 的数据库管理

在数据库管理部分，Oracle DBA 比 MySQL DBA 更有收益。与 MySQL 相比，Oracle DBA 有很多可用的范围。