MySQL 7.索引 8. 权限管理和备份 9.规范数据库设计

7 索引

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构。

提取句子主干，就可以得到索引的本质：索引是数据结构

7.1 索引的分类

在一个表中，主键索引只能有一个，唯一索引可以有多个

• 主键索引 primary key

                 唯一的标识，主键不可重复，只能有一个列作为主键

• 唯一索引 unique key

                 避免重复的列出现，唯一索引可以重复，多个列都可以标识为 unique key

• 常规索引 key或者index

                 默认的，index或key来设置

• 全文索引 fullText

                 在特定的数据库引擎（MyISAM，现在innodb好像也可以了）下才可以使用

索引的使用 

-- 索引的使用
-- 1.在创建表的时候给字段增加索引
-- 2.创建完毕后，增加索引

-- 显示所有的索引信息
SHOW INDEX FROM student

-- 增加一个全文索引  索引名 列名
ALTER TABLE school.`student` ADD FULLTEXT INDEX`studentname`(`studentname`)

-- EXPLAIN 分析sql执行的状况
EXPLAIN SELECT* FROM student; -- 非全文索引

SELECT* FROM student WHERE MATCH(studentname)AGAINST('刘')

7.2 测试索引

CREATE TABLE `app_user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT '' COMMENT '用户昵称',
`email` varchar(50) NOT NULL COMMENT '用户邮箱',
`phone` varchar(20) DEFAULT '' COMMENT '手机号',
`gender` tinyint(4) unsigned DEFAULT '0' COMMENT '性别（0:男；1：女）',
`password` varchar(100) NOT NULL COMMENT '密码',
`age` tinyint(4) DEFAULT '0' COMMENT '年龄',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表' 

DROP FUNCTION IF EXISTS mock_data;
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i < num DO
  INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)
   VALUES(CONCAT('用户', i), '24736743@qq.com', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
  SET i = i + 1;
END WHILE;
RETURN i;
END;
SELECT mock_data();

SELECT * FROM app_user WHERE `name`='用户9999';  -- 0.42s

EXPLAIN SELECT*FROM app_user WHERE `name`='用户9999'; -- row=几万

-- id_表名_字段名 
-- create index 索引名 on 表（字段）
CREATE INDEX id_app_user_name ON app_user(`name`);

SELECT * FROM app_user WHERE `name`='用户9999';  -- 0.002s
EXPLAIN SELECT*FROM app_user WHERE `name`='用户9999';  -- rows=1

索引在小数据量的时候，用户不大，但是在大数据的时候，区别十分明显

7.3 索引原则

索引不是越多越好

不要对进程变动数据加索引

小数据量的表不需要加索引

索引一般加在常用来查询的字段上

索引的数据结构

 默认的是BTREE，严格来说是B+树----innodb默认的数据结构

8 权限管理和备份

8.1 用户管理

sqlyog可视化管理

SQL 命令操作 

 用户表：mysql.user

 本质：对这张表进行增删改查

-- 创建用户 CREATE USER 用户名 IDENTIFIED BY 密码

CREATE USER fuyi IDENTIFIED BY'123456'

-- 修改密码(修改当前用户密码)
SET PASSWORD=PASSWORD('1111111')


-- 修改密码（修改指定用户密码）
SET PASSWORD FOR fuyi =PASSWORD('1111111')

-- 重命名
RENAME USER fuyi TO fuyi2

-- 用户授权 all privileges 全部权限，库，表
-- all privileges 除了授权，其他权限都有
GRANT ALL PRIVILEGES ON *.*TO fuyi2

-- 查询权限
SHOW GRANTS FOR fuyi2 -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost -- 查看管理员权限
-- root 用户的权限
-- GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION 

-- 撤销权限 revoke 哪些权限，哪个库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM fuyi2

-- 删除用户
DROP USER fuyi2

 8.2 MySQL 备份

为什么要备份？

• 保证重要的数据不丢失
• 数据转移 a---b

MySQL数据库备份的方式

• 直接拷贝物理文件 
• sqlyog等可视化工具中手动导出

              右键，选择备份或导出，选择

• 使用命令行导出 mysqldump  命令行使用 

--  mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student > C:/a.sql


--  mysqldump -h 主机 -u 用户名 -p 密码 数据库  > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school  > C:/a.sql


--导入
mysql -uroot -p123456
use school
source C:/a.sql

--或者
mysql -uroot -p123456 school <C:/a.sql

 保存数据库防止数据丢失

寄数据库给别人

9 规范数据库设计

9.1 为什么需要设计

当数据库比较复杂的时候，我们就需要设计了

糟糕的数据库设计：

• 数据冗余，浪费空间
• 数据库插入和删除都会很麻烦，造成异常[屏蔽使用物理外键]
• 程序的性能差

良好的数据库设计：

• 节省内存空间
• 保证数据库的完整性
• 方便开发系统

软件开发中，关于数据库的设计

1. 分析需求：分析业务和需要处理数据库的需求
2. 概要设计：设计关系图 E-R图

设计数据库的步骤：（以个人博客为例子）

• 收集信息，分析需求
• ----用户表（用户登录注销，用户个人信息，写博客，创建分类）
• ----分类表（文章分类，谁创建的）
• ----文章表（文章的信息）
• ----评论表
• ----友链表（友链的信息）
• ----自定义表（系统信息，某个关键的字，或者一些主字段） key: value
• ----说说表（发表心情）
• 标识实体（把需求落地到每个字段）
• 标识实体之间的关系
• ----写博客：user -->blog
• ----创建分类：user -->categoty
• ----关注：user -->user
• ----友链：links
• ----评论：user-->user_blog

9.2 三大范式 

为什么需要数据规范化？

• 防止信息重复
• 防止更新异常
• 防止插入异常

           无法正常显示信息

• 防止删除异常

           丢失有效信息

三大范式

第一范式 1NF：

要求数据库表的每一列都是不可分割的原子数据项

错误示范：

第二范式 2NF：

前提：满足第一范式

数据库表中的每一列都和主键相关，而不是只与主键的某一部分相关

第三范式 3NF:

前提：满足第一，第二范式

数据表中的每一列数据都和主键直接相关，而不是简介相关

错误：

规范性 和 性能的问题

关联查询的表不得超过三张表

• 考虑商业化的需求和目标（成本，用户体验），数据库的性能更加重要
• 在规范性能问题的时候，需要适当考虑规范性
• 故意增加一些冗余字段（从多表查询变为单表查询）订单--商品id
• 故意增加一些计算列（从大数据量降低为小数据量的查询：索引）