MySQL-索引与数据库管理(数据备份)

索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。详细可以查看这篇博文（原博文已失效，该博文为转载）https://blog.csdn.net/weichi7549/article/details/88315157

索引的分类

• 主键索引（PRIMAY KEY）
  • 唯一的标识，不可重复，只能有一个列作为主键
• 唯一索引（UNIQUE KEY）
  • 避免重复的列，唯一索引可以重复，多个列都可以标识为唯一索引
• 常规索引（KEY/INDEX）
  • 默认的
• 全文索引（FULLTEXT）
  • 在特定的数据引擎下才可使用（MyISAM）
  • 快速定位数据

索引的使用：

1、在创建的时候给字段增加索引

2、创建完毕之后，增加索引

-- 显示所有的索引
SHOW INDEX FROM `student`
-- 增加一个全文索引
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname`(`studentname`) -- 索引名(字段名)
-- explain 分析SQL执行的情况
EXPLAIN SELECT * FROM `student` -- rows = 12
EXPLAIN SELECT * FROM `student` WHERE MATCH(`studentname`) AGAINST('王') -- rows = 1

一百万条数据测试索引

我们可以通过大量数据来测试索引对于数据处理的作用！

-- 测试一百万条数据
-- 创建用户表
CREATE TABLE `app_user` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT '' COMMENT '用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT '用户邮箱',
`phone` VARCHAR(20) DEFAULT '' COMMENT '手机号',
`gender` TINYINT UNSIGNED DEFAULT '0' COMMENT '性别',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT DEFAULT '0' COMMENT '年龄',
`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=utf8mb4 COMMENT='app用户表'
TRUNCATE `app_user`

-- 自定义函数
DELIMITER $$ -- 写函数之前一定必须要写（标志）
CREATE FUNCTION mock_data() -- 创建函数
RETURNS INT -- 返回值
DETERMINISTIC -- 确定性
BEGIN
 DECLARE num INT DEFAULT 1000000;
 DECLARE i INT DEFAULT 0;
 WHILE i < num DO -- while循环
  -- 插入语句
  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();

当以上代码都执行完毕之后，我们可以尝试着对“用户9999”来进行查询，可以发现，查询时间一共花费了1.098sec（不同配置的电脑、多次执行查询语句可能会有一定的时间差距）。

SELECT * FROM `app_user` WHERE `name`='用户9999' -- 1.098sec

通过explain语句，也可以发现查询语句共查询了将近90万条数据（不同情况下结果可能会有差别）~

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

这仅仅是一百万数据，在大数据时代，数据的计量单位可能是以千亿、万亿来计算的，如果使用这种方式来查询，会徒增很多无用的时间成本，于是索引的用处就在这里体现了~

-- 创建索引
-- 语法：create index 索引名 on 表名(字段名)
-- 索引名的命名规范：id_表名_字段名
CREATE INDEX id_app_user_name ON `app_user`(`name`)

这时我们再次运行查询语句和explain语句，可以发现查询时间缩短至0.01sec，而explain也展示了该查询语句一共只查询了1行的数据~

经过以上的测试，我们可以得出一个简单的结论，就是设置索引可以非常有效地提高对数据的查询效率！在小数据下可能感觉并不明显，但是在庞大的数据之下，差距就变得非常明显！

索引的原则

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

Tips：索引的数据结构为Hash类型的索引，InnoDB的默认数据结构为BTree~

用户管理与权限管理

SQLyog可视化管理

SQL命令管理

首先我们先明确用户表的位置：mysql.user，SQL命令管理本质上就是读这张表并进行增删改查~

-- 创建用户 create user 用户名 identified by '密码'
CREATE USER Ayin IDENTIFIED BY '12345'
-- 修改当前用户密码
SET PASSWORD = PASSWORD('123456')
-- 修改指定用户密码
SET PASSWORD FOR Ayin = '123456'
-- 注意：在MySQL8.0之前的版本，需要在密码之前加上password(),也就是
-- SET PASSWORD FOR Ayin = PASSWORD('123456')

-- 重命名
RENAME USER Ayin TO ayin

-- 用户授权(all privileges：全部权限)
-- *.* 全部的库与表(库.表)
GRANT ALL PRIVILEGES ON *.* TO ayin
-- 注意：虽然我们将全部权限都给了ayin用户，但是该用户的权限仍然没有root大
-- 从用户管理里可以看到，ayin用户的grand权限并未勾选，说明该用户并没有给其他用户授权的权限
-- privileges：解锁除了给其他用户授权以外的所有权限

-- 查询权限
SHOW GRANTS FOR ayin
-- GRANT USAGE ON *.* TO `ayin`@`%`
SHOW GRANTS FOR root@localhost
-- GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

-- 撤销权限
REMOVE ALL PRIVILEGES ON *.* TO ayin
-- 删除用户
DROP USER ayin

数据库备份

数据库备份的作用：

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

MySQL数据库备份的方式：

• 直接拷贝物理文件

• 使用可视化用具手动备份

• 使用命令行(mysqldump)

# 导出数据库
# mysqldump -h主机名 -u用户名 -p密码 数据库 表名1 表名2... >物理磁盘位置/文件名
C:\WINDOWS\system32>mysqldump -hlocalhost -uroot -p12345 school student >D:/a.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
# 导入数据库
# 在登入的情况下，切换至指定数据库下
source D:/a.sql