MySQL04：索引、用户管理、数据备份及设计

Hu说编程 2021-07-22

333

7、索引

MySQL中索引官方定义：索引（index）是帮助MySQL高效获取数据的数据结构
索引本质：数据结构

7.1、索引分类

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

主键索引（primary key）

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

唯一索引（unique key）

避免重复列出现。唯一索引可以重复，多个列都可以标识唯一索引

常规索引（key/index）

默认索引。key/index作为关键字来设置

全文索引

在特定数据库引擎下才有，MyISAM
能快速定位数据

基础语法

 -- 索引的使用
 -- 1、在创建表的时候给字段添加索引
 -- 2、表创建完毕后，增加索引
 
 -- 显示所有的索引信息
 USE school
 SHOW INDEX FROM student
 
 -- 增加一个全文索引 索引名(字段名)
 ALTER TABLE school.`student` ADD FULLTEXT INDEX studentname(`studentname`)
 -- 分析SQL
 EXPLAIN SELECT * FROM student -- 非全文索引
 EXPLAIN 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 '', 
     `email` VARCHAR(50) NOT NULL, 
     `phone` VARCHAR(20) DEFAULT '', 
     `gender` TINYINT(4) UNSIGNED DEFAULT 0,
     `password` VARCHAR(100) NOT NULL, 
     `age` TINYINT(4) DEFAULT 0, 
     `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP, 
     `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE                   CURRENT_TIMESTAMP, 
     PRIMARY KEY (`id`) 
 ) ENGINE=INNODB DEFAULT CHARSET=utf8; 
 
 -- 在表中插入100万数据
 DELIMITER $$ -- 写函数之前必须要写，标志
 CREATE FUNCTION mock_data()
 RETURNS INT DETERMINISTIC
 
 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` = '用户10000' -- 2.446 sec
 SELECT * FROM app_user WHERE `name` = '用户10005' -- 2.436 sec
 SELECT * FROM app_user WHERE `name` = '用户10000' -- 执行耗时   : 2.424 sec
 
 EXPLAIN SELECT * FROM app_user WHERE `name` = '用户10000'
 
 -- id _ 表名 _ 字段名
 -- create index 索引名 on 表（字段）
 CREATE INDEX id_app_user_name ON app_user(`name`)
 -- 添加索引大幅度提高查询效率
 EXPLAIN SELECT * FROM app_user WHERE `name` = '用户10000' -- 0.005 sec

创建索引之前查看查找语句执行情况：无索引查询1983756行

 EXPLAIN SELECT * FROM app_user WHERE `name` = '用户10000'

创建索引之后查看查找语句执行情况：根据索引只查询两行，索引在数据量大的情况下十分有用

 EXPLAIN SELECT * FROM app_user WHERE `name` = '用户10000'

7.3、索引原则

索引并非越多越好
不要对进程变动数据加索引
小数据量的表中不需要加索引
索引一般加在常用来查询的字段上

索引的数据结构

hash类型的索引

Btree：InnoDB默认数据结构

8、权限管理和数据库备份

8.1、用户管理

SQLyog可视化界面

SQL命令

 -- 创建用户 create user 用户名 identified by '密码'
 CREATE USER zpr IDENTIFIED BY '123456'
 
 -- 修改指定用户密码
 SET PASSWORD FOR zpr = PASSWORD('111111')
 
 -- 重命名 RENAME USER 原名字 TO 新名字
 RENAME USER zpr1 TO zpr
 
 -- 用户授权（所有权限） ALL PRIVILEGES ON *.* TO zpr
 GRANT ALL PRIVILEGES ON *.* TO zpr
 
 -- 查询权限
 SHOW GRANTS FOR zpr -- 指定用户
 SHOW GRANTS FOR root@localhost -- root
 -- root用户权限 GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
 
 -- 撤销权限
 REVOKE ALL PRIVILEGES ON *.* FROM zpr
 
 -- 删除用户
 DROP USER zpr

8.2、MySQL备份

为什么要备份：

保证重要的数据不丢失
数据转移

MySQL数据库备份的方式

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

在想要导出的表或库中。右键，选择备份或导出

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

 # 导出一张表
 # mysqldump -h主机 -u用户名 -p密码 数据库 表名 >路径/文件名
 mysqldump -hlocalhost -uroot -p123456 school student >D:/student.sql
 
 # 导出多张表
 # mysqldump -h主机 -u用户名 -p密码 数据库 表1 表2 表3 >路径/文件名
 mysqldump -hlocalhost -uroot -p123456 school student grade >D:/student.sql
 
 # mysqldump -h主机 -u用户名 -p密码 数据库 >路径/文件名
 mysqldump -hlocalhost -uroot -p123456 school  >D:/student.sql
 
 # 导入
 # 登录mysql的情况下，切换到指定数据库
 # source 备份文件
 source D:/student.sql
 
 mysql -u用户名 -p密码 库名 < 备份文件