MySQL—04—MySQL的其他对象

妖狐艹你老母 2023-10-09 10:29 10阅读 0赞

--------------------

# **一、****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'     (注意:这个表示,可以查询所有数据库下的所有表;)

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

1.  % 匹配所有主机
2.  localhost localhost 不会被解析成 IP 地址，直接通过 UNIXsocket 连接
3.  127.0.0.1 会通过 TCP/IP 协议连接，并且只能在本机访问；
4.  ::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: 创建数据库和表。

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

DROP: 删除数据库和表。

INDEX: 创建或删除索引。

INSERT: 向表中插入新行。

SELECT: 检索表中的记录。

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

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

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

SHUTDOWN: 关闭服务器。