MySQL—04—MySQL的索引；用户管理；可视化工具Navicat

一、MySQL 中的其他对象

1. 索引

MySQL 索引的建立对于 MySQL 的高效运行是很重要的，索引可以大大提高 MySQL

的检索速度。

1.1MySQL 中的索引类型

• 普通索引

• 唯一索引

• 主键索引

• 组合索引

• 全文索引

1.2普通索引

是最基本的索引，它没有任何限制。在创建索引时，可以指定索引长度。length 为可选参数，表示索引的长度（索引长度表示数组的元素个数），只有字符串类型的字段才能指定索引长度，如果是 BLOB 和 TEXT 类型，必须指定 length。

创建索引时需要注意：

如果指定单列索引长度，length 必须小于这个字段所允许的最大字符个数。
查询索引：SHOW INDEX FROM table_name

1.2.1直接创建索引

CREATE INDEX index_name ON table(column(length))

示例
为 emp3 表中的 name 创建一个索引，索引名为 emp3_name_index
create index emp3_name_index ON emp3(name)

1.2.2修改表添加索引

ALTER TABLE table_name ADD INDEX index_name (column(length))

示例
修改 emp3 表，为 addrees 列添加索引，索引名为 emp3_address_index
alter table emp3 add index emp3_address_index(address)

1.2.3创建表时指定索引列

CREATE TABLE `table` (COLUMN TYPE ,PRIMARY KEY (`id`),INDEX index_name (column(length)))

示例
创建 emp4 表，包含 emp_id,name,address 列，同时为 name 列创建索引。索引名为 emp4_name_index
create table emp4(emp_id int primary key auto_increment,name varchar(30),address varchar(50),index emp4_name_index(name))

1.2.4删除索引

DROP INDEX index_name ON table

示例
删除 mep3 表中索引名为 emp3_address_index 的索引
drop index emp3_address_index on emp3

1.3唯一索引

唯一索引与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。它有以下几种创建方式：

1.3.1创建唯一索引

CREATE UNIQUE INDEX indexName ON table(column(length))

示例
为 emp 表中的 name 创建一个唯一索引，索引名为 emp_name_index
create unique index emp_name_index on emp(name)

1.3.2修改表添加唯一索引

ALTER TABLE table_name ADD UNIQUE indexName (column(length))

示例
修改 emp 表，为 salary 列添加唯一索引，索引名为 emp_salary_index
alter table emp add unique emp_salary_index(salary)

1.3.3创建表时指定唯一索引

CREATE TABLE `table` (

COLUMN TYPE ,

PRIMARY KEY (`id`),

UNIQUE index_name (column(length)))

示例
创建 emp5 表，包含 emp_id,name,address 列，同时为 name 列创建唯一索引。索引名为emp5_name_index
create table emp5(emp_id int primary key ,name varchar(30),address varchar(30),unique emp5_name_index(name))

1.4主键索引

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。

我们只需要给表添加主键约束,MySQL就会自动给列添加主键索引;

1.4.1修改表添加主键索引

ALTER TABLE 表名 ADD PRIMARY KEY(列名)

示例
修改 emp 表为 employee_id 添加主键索引
alter table emp add primary key(employee_id)

1.4.2创建表时指定主键索引

CREATE TABLE `table` (

COLUMN TYPE ,

PRIMARY KEY(column))

示例
创建 emp6 表，包含 emp_id,name,address 列，同时为 emp_id 列创建主键索引
create table emp6(employee_id int primary key auto_increment,name varchar(20),addressvarchar(50))

1.5组合索引

组合索引是指使用多个字段创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用(最左前缀原则)。

1.5.1最左前缀原则

就是最左优先。

如：我们使用表中的 name，address，salary 创建组合索引，那么想要组合索引生效，

我们只能使用如下组合：

name/address/salary

name/address

name/

如果使用 addrees/salary 或者是 salary 则索引不会生效。

1.5.2修改添加组合索引

ALTER TABLE table_name ADD INDEX index_name (column(length),column(length))

示例
修改 emp6 表，为 name，address 列创建组合索引
alter table emp6 add index emp6_index_n_a(name,address)

1.5.3创建表时创建组合索引

CREATE TABLE `table` (

COLUMN TYPE ,

INDEX index_name (column(length),column(length))

)

示例
创建 emp7 表，包含 emp_id,name,address 列，同时为 name,address 列创建组合索引。
create table emp7(emp_id int primary key auto_increment ,name varchar(20),address varchar(30),index emp7_index_n_a(name,address))

1.6全文索引

全文索引(FULLTEXT INDEX)主要用来查找文本中的关键字，而不是直接与索引中的值相比较。FULLTEXT 索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的 where

语句的参数匹配。

FULLTEXT 索引配合 match against 操作使用，而不是一般的 where 语句加 like。

全文索引可以从 CHAR、VARCHAR 或 TEXT 列中作为 CREATE TABLE 语句的一部分被创建，或是随后使用 ALTER TABLE 添加。不过切记对于大容量的数据表，生成全文索

引是一个非常消耗时间非常消耗硬盘空间的做法。

1.6.1修改添加全文索引

ALTER TABLE table_name ADD FULLTEXT index_name(clumn_name)

示例一
修改 emp7 表添加 content 列类型为 TEXT
alter table emp7 add COLUMN content text;

示例二
修改 emp7 表，为 content 列创建全文索引
alter table emp7 add fulltext emp7_content_fullindex(content)

1.6.2创建表时创建全文索引

CREATE TABLE `table` (COLUMN TYPE ,FULLTEXT index_name (column))

示例
创 建 emp8 表 包 含 emp_id 列 ， content 列 该 列 类 型 为 text ， 并为该列添加名为emp8_content_fulltext 的全文索引
create table emp8(emp_id int primary key auto_increment,content text,fulltext emp8_content_fullindex(content))

1.6.3删除全文索引

DROP INDEX index_name ON table

ALTER TABLE table_name DROP INDEX index_name

示例
删除 emp8 表中名为 emp8_content_full 的索引。
drop index emp8_content_fullindex on emp8

1.7使用全文索引

全文索引的使用与其他索引不同。在查询语句中需要使用 match(column) against(‘content’) 来检索数据。

1.7.1全文解析器

全文索引中基本单位是”词”。分词，全文索引是以词为基础的，MySQL 默认的分词是所有非字母和数字的特殊符号都是分词符。

在检索数据时我们给定的检索条件也是词。

MySQL 中默认的全文解析器不支持中文分词。如果数据含有中文需要更换全文解析器

NGRAM。

1.7.2使用全文索引

SELECT 投影列 FROM 表名 WHERE MATCH(全文索引列名) AGAINST(‘搜索内容’)

示例一
修改 emp8 表，为 content 列创建名为 emp8_content_full 的全文索引
alter table emp8 add fulltext emp8_content_full(content)

示例二
向 emp8 表中插入一条数据 content 的值为”hello,bjsxt”
insert into emp8 values(default ,'hello bjsxt')

示例三
查询 emp8 表中内容包含 bjsxt 的数据。
select * from emp8 where match(content) AGAINST('bjsxt')

1.7.3更换全文解析器

在创建全文索引时可以指定 ngram 解析器

ALTER TABLE table_name ADD FULLTEXT index_content(content) WITH PARSER NGRAM

示例一
删除 emp8 表中的 emp8_content_full 全文索引
drop index emp8_content_full on emp8

示例二
修改 emp8 表，为 content 列添加名称为 emp8_content_full 的全文索引，并指定 ngram 全文解析器。
alter table emp8 add fulltext emp8_content_full(content) with parser ngram

示例三
向 emp8 表中添加一条数据 content 值为”你好，TJPU”
insert into emp8 values(default ,'你好，TJPU')

示例四
查询 emp8 表中内容包含”TJPU”的数据
select * from emp8 where match(content) AGAINST('TJPU')

2. MySQL 中的用户管理

MySQL 是一个多用户的数据库系统，按权限，用户可以分为两种：root 用户，超级管

理员，和由 root 用户创建的普通用户。

2.1MySQL 创建用户

CREATE USER 用户名 IDENTIFIED BY '用户密码';

2.2查看用户

SELECT USER,NOST FROM USER(该表位于 mysql 库中)

示例
创建一个 u_sxt 的用户，并查看创建是否成功。

create user u_sxt IDENTIFIED by 'sxt'
select user,host from mysql.user

2.3分配权限

新用户创建完后是无法登陆的，需要分配权限。

GRANT 权限 ON 数据库.表 TO 用户名@登录主机 IDENTIFIED BY "密码"

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password'     (注意:这个表示,可以查询所有数据库下的所有表;)

@登陆主机, 具体可以写那些：

% 匹配所有主机
localhost localhost 不会被解析成 IP 地址，直接通过 UNIXsocket 连接
127.0.0.1 会通过 TCP/IP 协议连接，并且只能在本机访问；
::1 ::1 就是兼容支持 ipv6 的，表示同 ipv4 的 127.0.0.1

(1)Unix domain socket 　　Unix domain socket,也叫IPC socket(inter-precess communication socket,也就是进程间通信套接字),用于同一台主机上的不同进程间交换数据，是Posix系统的标准组件。可以传输字节流(byte stream,SOCK_STREAM,TCP)或者数据报(datagram,SOCK_DGRAM,UDP).除了传输数据以外，还可以使用Unix domain socket传输文件描述符(file descriptor)。 (2)IP socket 　　IP socket要利用主机的传输层(tcp)，可以用于同一台主机上不同进程间的通信，也可以用于网络上不同主机间的通信。 Unix domain socket vs IP socket

　　运行在同一台机器上的两个进程需要通信，有2种实现方式:第一种是ip socket,通过本机回环地址127.0.0.1加端口实现;第二种是通过unix domain socket实现。哪一种效率更高呢?基于localhost的ip socket需要实现跨网络主机通讯的全部环节，包括建立socket连接，ACk开销，tcp流控，封装／解封，路由。在这个过程中还会有2个context switch,因为使用网络层传输数据需要调用system call,而调用system call会产生中断，导致context switch的；另外一个进程接受到来自网络层的连接请求，也会产生系统中断，导致context switch。以上过程导致2个context switch的开销，外加其它各种开销(overhead)。

2.3.1权限列表

ALTER: 修改表和索引。

CREATE: 创建数据库和表。

DROP: 删除数据库和表。

INDEX: 创建或删除索引。

INSERT: 向表中插入新行。

DELETE: 删除表中已有的记录。

SELECT: 检索表中的记录。

UPDATE: 修改现存表记录。FILE: 读或写服务器上的文件。

PROCESS: 查看服务器中执行的线程信息或杀死线程。

RELOAD: 重载授权表或清空日志、主机缓存或表缓存。

SHUTDOWN: 关闭服务器。

USAGE: 特殊的 "无权限" 权限

ALL: 所有权限，ALL PRIVILEGES 同义词。

示例
为 u_sxt 用户分配只能查询 bjsxt 库中的 employees 表，并且只能在本机登陆的权限。
grant select ON bjsxt.employees to 'u_sxt'@'localhost' IDENTIFIED by 'sxt'

2.4刷新权限

每当调整权限后，通常需要执行以下语句刷新权限

FLUSH PRIVILEGES

2.5删除用户

DROP USER username@localhost

示例
删除 u_sxt 用户
drop user 'u_sxt'@'localhost'

3. 通过 Navicat 工具管理用户

3.1创建用户

3.2分配权限

3.3删除用户

posted @ 2019-09-23 11:33 Eric-Shen 阅读(646) 评论(0) 编辑收藏举报

刷新页面返回顶部

Eric-Shen

MySQL—04—MySQL的索引；用户管理；可视化工具Navicat

一、MySQL 中的其他对象

1. 索引

1.1MySQL 中的索引类型

1.2普通索引

2. MySQL 中的用户管理

3. 通过 Navicat 工具管理用户

公告