一、MySQL中的索引 二、MySQL中的函数 三、MySQL数据库的备份和恢复 四、数据库设计和优化(重点)

时间:2023-11-21 12:21:20

一、MySQL中的索引
###<1>索引的概念
索引就是一种数据结构(高效获取数据),在mysql中以文件的方式存在。存储建立了索引列的地址或者指向。

文件 :(以某种数据 结构存放) 存放地址 指向数据

类似一本书的目录,能够提高检索效率。

###<2>特点
提高数据的获取效率,也是数据库优化的一部分。
在多表连接时,作为条件加速连接的速度 。
在分组和排序查询时,作为条件,能够减少分组和排序所消耗的时间。

创建和维护索引 会消耗时间,随着数据量的增加消耗的时间也增加
索引文件会占用物理空间
数据表中的数据变化时,索引也需要动态的维护

###<3>分类
3.1 普通索引
1. 创建的语法格式
CREATE INDEX index_name ON table_name(column列表);

ALTER TABLE table_name ADD INDEX index_name(column列表);

例如:
create table tt1(
id int primary key auto_increment,
name varchar(20) not null
)

create index index_name on tt1(name);
alter table tt1 add index index_name(name)

2. 查看创建的索引
show index from 表名;
show create table 表名;
show keys from 表名;

3. 删除索引
DROP INDEX index_name ON tablename;

3.2 唯一索引
3.3 聚焦索引(主键索引)

3.4 全文索引
1. 创建的语法格式
Create Fulltext Index fullindex_name On table_name(字段列表);
Alter Table table_name Add Fulltext [Index] fullindex_index(字段列表);

例如:create table tt2(
id int,
name varchar(100) ,
//fulltext key fulltext_name_index(name)
) engine=myisam;

Alter Table tt2 Add Fulltext fullindex_index(name);

2. 使用方式
match(索引列表) against("内容" in boolean mode)

insert tt2 value(1,'我');
insert tt2 value(1,'我是');
insert tt2 value(1,'我是好');
insert tt2 value(1,'我是好人');
insert tt2 value(1,'我是一个大好人');

select * from tt2 where match(name) against('我' in boolean mode);
select * from tt2 where match(name) against('我是' in boolean mode);

###<4>应用场景
建议使用:
主键自动建立索引
查询中统计或者分组字段
排序字段
与其他表关联的字段
频繁的作为查询条件的字段

不建议使用:
表的记录较少
频繁的更新字段
where条件表达式中不常用的字段

# 二、MySQL中的函数
###1. 加密函数
password(str):通常用来给用户密码进行加密
select password('123');

md5(str):使用散列的方式对数据进行加密
select md5('123');

###2. 流程控制的函数
* if(expr,r1,r2):
判断expr是否成立,结果为true返回r1的值,否则返回r2的值
select if(1>0,"大于","小于");

* Case value When compare-value Then result
When compare-value Then result
...
Else result
End
select case 666 when 777 then "one"
when 777 then "one"
when 666 then "two"
else "three" end

* ifnull(v1,v2)
判断v1的值是不是null,如果不为null,返回v1的值,否则返回v2的值
ifnull(null,0)

* nullif(e1,e2)
判断e1=e2是否成立,成立返回null,不成立返回e1
select nullif(1+1,2-1);

###3. 数学相关的函数
* rand()
返回0-1的随机数

* round(num)
返回num的近似值

* abs(num)
取num的绝对值

###4. 日期相关的函数
* curdate()/current_date()
返回当前日期
select curdate(),current_date()

* curtime()/current_time()
返回当前时间
select curtime(),current_time()

* now()/current_timestamp()/localtime()/sysdate()/localtimestamp()
返回当前日期+时间

###5. 字符处理相关的函数
* char_length(str)
返回当前str字符串的长度
select char_length('你好123');

* length(str)
返回当前str字符串的长度
select length('你好123');

# 三、MySQL数据库的备份和恢复
###<1>数据库的备份
语法格式:使用mysqldump命令可以生成指定的数据库脚本文件
mysqldump -u用户名 -p密码 数据库名称 > 生成的脚本文件的路径

例如:mysqldump -uroot -p123 db1 >C:\mydb1.sql

###<2>数据库的恢复
恢复的语法格式1:
source 数据库脚本文件的路径

例如:在mysql数据库中
mysql> source c:\mydb1.sql

注意:必须登陆到mysql数据库中,先选择数据库

恢复的语法格式1:
mysql -uroot -p123 db2 <C:\mydb1.sql

#四、数据库的设计和优化
###<1>数据库的设计:小型的电子商务网站
1.1 需求分析
1.1.1 核心的模块
用户模块:
作用:存储用户的信息
属性:用户名,密码,联系方式...
唯一标识:用户编号,电话号码,用户的身份证号码等等
特点:随着系统上线时间增长,用户模块的数据需要永久存储

商品模块:
作用:存储商品的信息
属性:商品名,价格,商品的类型...
唯一标识:商品编号...
特点:对于下线的商品进行归档存储

购物车模块:

订单模块:
作用:存储用户购买的商品信息
属性:订单号,用户名,用户的联系方式,商品编号,订单状态,价格,数量...
唯一标识:订单号...
特点:随着系统上线时间增长,订单模块的数据需要永久存储

供应商模块:
作用:存储商品供应商的信息
属性:供应商的编号,名称,联系人,电话,营业执照,法人代表,地址...
唯一标识:供应商的编号...
特点:随着系统上线时间增长,供应商模块的数据需要永久存储

... ... 模块

1.1.2 关系
![](1.png)

1.2 逻辑设计
1. 将需求转化为逻辑模型
2. 通过ER图的方式来展示逻辑模型
ER描述现实世界的概念模型

矩形:实体
菱形:关系,联系
椭圆:属性
线条:连接属性和实体
![](2.png)

1.3 设计表,遵循设计规范(三大范式)
第一范式:对属性的原子性约束,要求属性具有原子性,不可在分割。
第二范式:对记录的唯一性约束,要求每一条数据具有唯一标识
第三范式:对字段的冗余性的约束,要求字段不能出现冗余

1.4 物理设计
建立数据库,表和字段
选择合适的数据库软件
操作系统的选择
配置数据库的参数
innodb_buffer_pool_size
innodb_file_per_table

###<2>数据库的优化
良好的数据库设计和操作能够提高使用数据库的效率。

1. 选择合适的存储引擎

2. 选择合适的数据类型
少的使用text,blob类型
尽量使用简单的数据类型,int enum date varchar()
多使用not null 定义字段(innodb存储引擎)
建议使用enum

存储时间的函数:
from_unixtime()和unix_timestramp()函数可以对时间类型进行转换

insert test(timestr) value(unix_timestramp('2018-01-01 01:01:01'));
select from_unixtime(timestr) from test;

3. 创建合适的索引
字段越小越好
字段在where从句,group by从句,order by从句中 等等
字段作为连接关系

4. 尽量避免select *
只查询需要的数据

5. 反范式化
订单表:订单id , 用户id , 商品id, 状态, 价格 , [商品名称 , 用户名称 ,联系方式]

6. 表的拆分
垂直拆分:
解决表的宽度问题
<1>把不常用的字段单独放置到一张表中
<2>把字段类型大的字段单独放置到一张表中
<3>把常用的字段放置到一张表中

水平拆分:
解决表的数据量过大带来的问题,将一张表拆成多份

7. explain :查询SQL的执行计划
select_type:查询的类型
type: 显示使用了何种类型
ALL:全表扫描
index:全索引扫描
range:基于索引的范围查找
const:常数查找
key:此次查询中使用到的索引,没有用到,显示null

rows:显示此次查询一共扫描了多少行,原则上扫描越少越好

extra:额外的信息
using filesort:需要优化