索引
17、索引
17.1 、索引原理
索引被用来快速找出在一个列上用一特定值的行。没有索引，MySQL 不得不首先以第一条记录开始，然后读完整个表直到它找出相关的行。表越大，花费时间越多。对于一个有序字段，可以运用二分查找（Binary Search），这就是为什么性能能得到本质上的提高。MYISAM 和 INNODB 都是用 B+Tree 作为索引结构
（主键，unique 都会默认的添加索引）
17.2 、索引的应用
17.2.1 、创建索引
如果未使用索引，我们查询 工资大于 1500 的会执行全表扫描
什么时候需要给字段添加索引：
-表中该字段中的数据量庞大
-经常被检索，经常出现在where 子句中的字段
-经常被 DML 操作的字段不建议添加索引
索引等同于一本书的目录
主键会自动添加索引，所以尽量根据主键查询效率较高。
如经常根据 sal 进行查询，并且遇到了性能瓶颈，首先查看程序是否存算法问题，再考虑对 sal 建立索引，建立索引如下：
1 、create unique index 索引名 on 表名(列名);
create unique indexu_ename on emp(ename);
2 、alter table 表名 add unique index 索引名 (列名);
| create index test_index on emp (sal); |
17.2.2 、查看索引
| show index from emp; |
17.2.3 、使用索引
|
注意一定不可以用select * … 可以看到 type!=all 了，说明使用了索引条件中的 sal 使用了索引
如下图：假如我们要查找 sal 大于 1500 的所有行，那么可以扫描索引，索引时排序的，结果得出 7 行， 我们知道不会再有匹配的记录，可以退出了。如果查找一个值，它在索引表中某个中间点以前不会出现，那么也有找到其第一个匹配索引项的定位算法，而不用进行表的顺序扫描（ 如二分查找法）。
这样，可以快速定位到第一个匹配的值， 以节省大量搜索时间。数据库利用了各种各样的快速定位索引值的技术，通常这些技术都属于 DBA 的工作。
17.2.4 、删除索引
|
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name ALTER TABLE table_name DROP PRIMARY KEY 其中，前两条语句是等价的，删除掉table_name 中的索引index_name。
第 3 条语句只在删除PRIMARY KEY 索引时使用，因为一个表只可能有一个 PRIMARY KEY 索引，
mysql> ALTER TABLE EMP DROP INDEX test_index;
删除后就不再使用索引了，查询会执行全表扫描。 |
18、视图
18.1 、什么是视图
• 视图是一种根据查询（也就是SELECT 表达式）定义的数据库对象，用于获取想要看到和使用的局部数据。
• 视图有时也被成为“虚拟表”。
• 视图可以被用来从常规表（称为“基表”）或其他视图中查询数据。
• 相对于从基表中直接获取数据，视图有以下好处：
– 访问数据变得简单
– 可被用来对不同用户显示不同的表的内容用来协助适配表的结构以适应前端现有的应用程序
视图作用：
- 提高检索效率
- 隐藏表的实现细节【面向视图检索】
18.2 、创建视图
如下示例：查询员工的姓名，部门，工资入职信息等信息。
| select ename,dname,sal,hiredate,e.deptno from emp e,dept d where e.deptno = e.deptno and e.deptno = 10; |
为什么使用视图？因为需求决定以上语句需要在多个地方使用，如果频繁的拷贝以上代码，会给维护带来成本，视图可以解决这个问题
| create view v_dept_emp as select ename,dname,sal,hiredate,e.deptno from emp e,dept d where e.deptno = e.deptno and e.deptno = 10; |
| create view v_dept_avg_sal_grade as select a.deptno, a.avg_sal, b.grade from (select deptno, avg(sal) avg_sal from emp group by deptno) a, salgrade b where a.avg_sal between b.losal and b.hisal; /*注意 mysql 不支持子查询创建视图*/ |
18.3 、修改视图
| alter view v_dept_emp as select ename,dname,sal,hiredate,e.deptno from e mp e,dept d where e.deptno = 20; |
|
|
18.4 、删除视图
| drop view if exists v_dept_emp; |
19、DBA 命令
19.1 、新建用户
| CREATE USER username IDENTIFIED BY 'password'; 说明:username——你将创建的用户名, password——该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器. 例如： create user p361 identified by '123'; --可以登录但是只可以看见一个库 information_schema |
19.2 、授权
| 命令详解 mysql> grant all privileges on dbname.tbname to 'username'@'login ip' identified by 'password' with grant option;
1) dbname=*表示所有数据库
2) tbname=*表示所有表
3) login ip=%表示任何 ip
4) password 为空，表示不需要密码即可登录
5) with grant option; 表示该用户还可以授权给其他用户
l 细粒度授权 首先以 root 用户进入 mysql，然后键入命令：grant select,insert,update,delete on *.* to p361 @localhost Identified by "123"; 如果希望该用户能够在任何机器上登陆 mysql，则将 localhost 改为"%" 。
l 粗粒度授权 我们测试用户一般使用该命令授权， GRANT ALL PRIVILEGES ON *.* TO 'p361'@'%' Identified by "123";
注意:用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令:
GRANT ALL PRIVILEGES ON *.* TO 'p361'@'%' Identified by "123" WITH GRANT OPTION;
privileges 包括： 1) alter：修改数据库的表 |
| 2) create：创建新的数据库或表 3) delete：删除表数据 4) drop：删除数据库/表 5) index：创建/删除索引 6) insert：添加表数据 7) select：查询表数据 8) update：更新表数据 9) all：允许任何操作 10) usage：只允许登录 |
- 、回收权限
| 命令详解 revoke privileges on dbname[.tbname] from username; revoke all privileges on *.* from p361;
use mysql select * from user 进入 mysql 库中修改密码; update user set password = password('qwe') where user = 'p646'; 刷新权限; flush privileges |
- 、导出导入
19.4.1 、导出
- 、导出整个数据库
在 windows 的dos 命令窗口中执行：mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p123
19.4.1.2 、导出指定库下的指定表
在 windows 的dos 命令窗口中执行：mysqldump bjpowernode emp> D:\ bjpowernode.sql -uroot –p123
19.4.2 、导入
登录 MYSQL 数据库管理系统之后执行：source D:\ bjpowernode.sql
- 点赞
- 收藏
- 关注作者
评论（0）