管理MySQL数据库对象

创建于管理表

MySQL表的基本操作

创建一张表test1：

create table test1(id int,name varchar(32),age int);

查看创建表的语句：

show create table test1

查看表结构：

desc test1;
# 或 show columns from test1;

在表中添加字段：

alter table test1 add gender varchar(10) default 'M';

修改表：

alter table test1 modify gender varchar(10) default 'Female';

删除表中字段：

alter table test1 drop column gender;

删除表：

drop table test1;

数据的约束条件

“约束”是指对表中数据的一种限制条件，能够确保数据库中数据的准确性和有效性。

1.MySQL中的约束类型

主键约束、外键约束、唯一约束、检查约束、非空约束、默认值约束。

约束类型	关键字	说明
主键约束	primary key	主键是表里的一个特殊字段，这个字段能够唯一标识该表中的每条信息。 一张表只能定义一个主键，如果一个字段被定义成了主键，该列的值不允许为null，也不允许重复。
外键约束	foreign key	外键通常会和主键约束一起使用。 相关联字段中主键所在的表为主表，外键所在的表为从表。 当从表的某个字段被定义为外键时，该列上的值必须在主表中存在或为null值。
唯一约束	unique	如果一个字段被定义了唯一约束，那么该字段的值不允许重复，但允许为null。
检查约束	check	MySQL提供了检查约束来指定某列可取值的范围，它通过限制输入到列中的值来强制域的完整性。
非空约束	not null	用于确保该字段的值不能为空。
默认约束	default	用于指定某列的默认值。

在查询时使用索引

MySQL索引的基本知识

索引是帮助MySQL高效获取数据的数据结构，索引是一种数据结构。MySQL默认的索引类型是B+树索引。

查看MySQL中的索引

show indexes from test1;

创建索引

普通索引：最基本的索引，没有任何限制，用于加速查询。

# 基于表test1创建一张新的表
create table indextable as select * from test1;
# 在新表name上创建普通索引
create index index1 on indextable(name);

唯一索引：索引的列值必须唯一，但允许有空值。

# 在表test1的主键id上创建唯一索引
create unique index index1 on testt1(id);

主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般在创建表的时候创建主键索引。

create table test1(id int auto_increment,name varchar(20),primary key(id));

组合索引：是指在多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。

# 在表test1的name和age上添加组合索引
alter table test1 add index index1(name,age);

全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。全文索引跟其他索引不大相同，它更像是一个搜索引擎。全文索引需要配合“match against”操作使用，而不是“where”语句。

# 创建表的同时创建全文索引
create table test1(id int auto_increment,contents text not null,primary key(id),fulltext(contents));

哈希索引：哈希索引基于哈希算法实现，只有精确匹配索引的所有列的查询才有效。

MySQL索引的优化

使用ICP优化索引

ICP是MySQL使用索引从表中检索行数据的一种优化方式，如果where条件允许使用索引，MySQL会把这部分过滤操作放到存储引擎层，存储引擎通过索引过滤，把满足的行从表中读取出来。ICP能减少引擎层访问基表的次数和访问存储引擎的次数。

# 查看是否开启了ICP的索引优化
show variables like '%optimizer_switch%';
# ICP默认是开启的，ICP开启语句
set optimizer_switch="index_condition_pushdown=on";

使用MRR优化索引

MRR是使用优化器将随机I/O转化为顺序I/O以降低查询过程中I/O开销的一种手段。对于I/O密集型的SQL语句性能将带来极大的提升，适用于range和ref eq_ref类型的查询。

MRR的核心思想就是把普通索引对应的主键结合存储到read_rnd_buffer中，然后再读取该buffer中的主键值进行排序。

set optimizer_switch='mrr=on,mrr_cost_based=off'

使用BKA优化索引

BKA主要用于提高表join性能。它的基本思想是，当被Join的表能够使用索引时，就优先排好序，然后再去检索被Join的表。

MySQL的事件

事件的定义

事件（Event）是MySQL数据库中的时间触发器，在某一特定的时间点，Event会自动由MySQL调用从而触发相关的SQL语句或存储过程。使用MySQL的事件需要将参数“event_scheduler”设置为“ON”。