Day21-索引和权限管理

索引

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

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

索引的分类

• 主键索引（PRIMARY KEY）

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

• 唯一索引（UNIQUE KEY）

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

• 常规索引（KEY/INDEX）

  • 默认的 index 、key关键字来设置

• 全文索引（FullText）

  • 在特定的数据库引擎下才有，以前只有MyIASM
  • 快速定位数据

基础语法：

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

显示所有的索引

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

-- EXPLAIN 分析sql执行的状况
EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(`phone`) AGAINST('138'); -- 全文索引

测试索引

-- 插入100万数据
DELIMITER $$ -- 写函数之前必须要写，标志
CREATE FUNCTION mock_data()-- 创建一个函数
RETURNS INT-- 返回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),'123456@qq.com',CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
    -- 生成随机的9位数
    -- 性别0或1的随机
    -- pwd
    -- 年龄
    SET i = i+1;
  END WHILE;
  RETURN i;
END;

-- 执行函数,执行耗时   : 45.957 sec
SELECT mock_data();

SELECT * FROM `app_user` WHERE `name` = '用户9999' -- 执行耗时   : 1.108 sec
EXPLAIN SELECT * FROM `app_user` WHERE `name` = '用户9999' -- 执行了992720行才找到该数据，所以很慢

-- 创建索引
-- id_表名_字段名  索引名
-- CREATE INDEX 索引名 ON 表名(`字段名`)
-- 给这一百万条数据都去创建索引
CREATE INDEX id_app_user_name ON `app_user`(`name`);
-- 再次执行查询
SELECT * FROM `app_user` WHERE `name` = '用户9999' -- 执行耗时   : 0.368 sec
EXPLAIN SELECT * FROM `app_user` WHERE `name` = '用户9999' -- 就执行了一条

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

索引原则

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

索引的数据结构

Hash类型的索引

Btree ：InnoDB的默认数据结构

阅读：MySQL的底层，数据的索引

权限管理

用户管理

SQLyog 可视化管理

新建用户：给新用户选择权限

SQL命令操作

用户表：mysql.user

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

-- 创建用户
CREATE USER xiaozhao IDENTIFIED BY '123456'

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

-- 修改密码(修改指定用户密码)
SET PASSWORD FOR xiaozhao = PASSWORD('1111520')

-- 重命名  RENAME USER 原名子  TO 新名字
RENAME USER xiaozhao TO xiaogong

-- 用户授权  ALL PRIVILEGES 全部的权限， 库.表
-- ALL PRIVILEGES 除了给别人授权不行，其他都能干
GRANT ALL PRIVILEGES ON *.* TO xiaogong

-- 查询权限
-- 查看指定用户的权限
SHOW GRANTS FOR xiaogong -- GRANT ALL PRIVILEGES ON *.* TO 'xiaogong'@'%'
-- 查看root用户的权限，root必须要带上@localhost不然会报错
SHOW GRANTS FOR root@localhost 
-- root用户权限
-- GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
-- GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

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

-- 删除用户
DROP USER xiaogong