MySQL 10：索引、视图、用户管理

1. 索引

索引：提高数据库的性能，索引是物美价廉的东西了。不用加内存，不用改程序，不用调sql，只要执行正确的create_index，查询速度就可能提高成百上千倍。但是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的IO。所以它的价值，在于提高一个海量数据的检索速度。

常见索引分为：
主键索引(primary key)
唯一索引(unique)
普通索引(index)

1.1 创建主键索引

第一种方式：

在创建表的时候，直接在字段名后指定primary key。

create table user1 (id int primary key,name varchar(30));

 第二种方式：

在创建表的最后，指定某列或某几列为主键索引。

create table user2(id int,name varchar(30),primary key(id));

第三种方式：

 create table user3(id int,name varchar(30));
创建表以后再添加主键。

alter table user3 add primary key(id);

 主键索引的特点：

1. 一个表中，最多有一个主键索引，当然可以使符合主键主键索引的效率高。（主键不可重复）。
2. 创建主键索引的列，它的值不能为null，且不能重复。
3. 主键索引的列基本上是int。

1.2 唯一索引的创建

第一种方式：

在表定义时，在某列后直接指定unique唯一属性。

create table user4(id int primary key,name varchar(30) unique);

第二种方式：

创建表时，在表的后面指定某列或某几列为unique。

create table user5(id int primary key,name varchar(30),unique(name));

第三种方式：

create table user6(id int primary key,name varchar(30));
创建表以后再添加唯一索引。

alter table user6 add unique(name);

唯一索引的特点：

1. 一个表中，可以有多个唯一索引。
2. 查询效率高。
3. 如果在某一列建立唯一索引，必须保证这列不能有重复数据如果一个唯一索引上指定
   not null，等价于主键索引。

 1.3 普通索引

第一种方式：

在表的定义最后，指定某列为索引。

create table user8(id int primary key,name varchar(20),email varchar(30),index(name));

第二种方式：

create table user9(id int primary key,name varchar(20),email varchar(30));
创建完表以后指定某列为普通索引。

alter table user9 add index(name);

第三种方式：

create table user10(id int primary key,name varchar(20),email varchar(30));
创建表以后再添加唯一索引。

create index idx_name on user10(name);

普通索引的特点：

1. 一个表中可以有多个普通索引，普通索引在实际开发中用的比较多。
2. 如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引。

1.4 全文索引的创建

当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。MySQL提供全文索引机制，但是有要求，要求表的存储引擎必须是MyISAM，而且默认的全文索引支持英文，不支持中文。如果对中文进行全文检索，可以使用sphinx的中文版(coreseek)。

CREATE TABLE articles(id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY  KEY,title VARCHAR(200),body TEXT,FULLTEXT(title,body))engine=MyISAM;

 使用全文索引：

SELECT * FROM articles WHERE MATCH(title,body) AGAINST ('data');

1.5 查询索引、删除索引

1.5.1 查询索引

第一种方法：

show keys from 表名;

第二种方法：

show index from 表名;

第三种方法（信息比较简略）：

 desc 表名；

1.5.2 删除索引 

第一种方法--删除主键索引：

alter table 表名 drop primary key;

第二种方法--其他索引的删除：

 alter table 表名 drop index 索引名;

索引名就是show keys from表名中的Key_name字段。

第三种方法：

drop index 索引名 on 表名;

索引创建原则:

1. 比较频繁作为查询条件的字段应该创建索引。
2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件更新非常频繁的字段不适合作创建索引。
3. 不会出现在where子句中的字段不该创建索引。

2. 视图

视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。视图的数据变化会影响到基表，基表的数据变化也会影响到视图。

创建视图：

create view 视图名 as select语句;

2.1 视图规则和限制

1. 与表一样，必须唯一命名（不能出现同名视图或表名）
2. 创建视图数目无限制，但要考虑复杂查询创建为视图之后的性能影响视图不能添加索引，也不能有关联的触发器或者默认值
3. 视图可以提高安全性，必须具有足够的访问权限
4.  order by可以用在视图中，但是如果从该视图检索数据select中也含有order by,那么该视图中的order by将被覆盖
5. 视图可以和表一起使用

3. 用户管理

3.1 用户信息

MySQL中的用户，都存储在系统数据库mysql的user表中

use mysql;
select host,user,authentication_string from user;

字段解释：

host：表示这个用户可以从哪个主机登陆，如果是localhost，表示只能从本机登陆。
user：用户名。
authentication_string：用户密码通过password函数加密后的。
*_priv：用户拥有的权限。

3.2 创建用户

语法：

create user '用户名'@'登陆主机/ip' identified by '密码';

3.3 删除用户

drop user '用户名'@'主机名';

3.4 修改用户密码

语法：

自己改自己密码：

 set password=password('新的密码');

root用户修改指定用户的密码:

set password for'用户名'@'主机名'=password('新的密码');

3.5 数据库权限

MySQL数据库提供的权限列表：

权限	列	上下文
CREATE	Create_priv	数据库、表或索引
DROP	Drop_priv	数据库或表
GRANT OPTION	Grant_priv	数据库、表或保存的程序
REFERENCES	References_priv	数据库或表
ALTER	Alter_priv	视图
DELETE	Delete_priv	视图
INDEX	Index_priv	表
INSERT	Insert_priv	表
SELECT	Select_priv	表
UPDATE	Update_priv	表
CREATE VIEW	Create_view_priv	表
SHOW VIEW	Show_view_priv	表
ALTER ROUTINE	Alter_routine_priv	视图
CREATE ROUTINE	Create_routine_priv	视图
EXECUTE	Execute_priv	保存的程序
FILE	File_priv	保存的程序
CREATE TEMPORARY TABLES	Create_tmp_table_priv	保存的程序
LOCK TABLES	Lock_tables_priv	服务器主机上的文件访问
CREATE USER	Create_user_priv	服务器管理
PROCESS	Process_priv	服务器管理
RELOAD	Reload_priv	服务器管理
REPLICATION CLIENT	Repl_client_priv	服务器管理
REPLICATION SLAVE	Repl_slave_priv	服务器管理
SHOW DATABASES	Show_db_priv	服务器管理
SHUTDOWN	Shut_dowm_priv	服务器管理
SUPER	Super_priv	服务器管理

3.5.1 给用户授权

刚创建的用户没有任何权限。需要给用户授权。

语法：

grant 权限列表 on 库.对象名 to '用户名'@'登陆位置' [identified by '密码'];

 说明：

1. 权限列表，多个权限用逗号分开。
2. *.*：代表本系统中的所有数据库的所有对象（表，视图，存储过程等）。
3. 库.*：表示某个数据库中的所有数据对象(表，视图，存储过程等)identified by可选。如果用户存在，赋予权限的同时修改密码,如果该用户不存在，就是创建用户。

注意：如果发现赋权限后，没有生效，执行如下指令：

flush privileges;

3.5.2 回收权限

语法：

 revoke 权限列表 on 库.对象名 from '用户名'@'登陆位置';