MySQL---数据用户管理和索引

DDL：CTEATE DROP ALTER

dml：对数据进行管理

update insert into delete truncate

dpl：查询语句 select

dcl：权限控制语句 grant revoke

数据库用户管理

创建用户

修改用户权限

删除用户

grant要在终端执行

创建用户

create user 'ky32'@'localhost' identified by '123456'; #创建用户的语句
create user：创建用户的开头
'ky32'@'localhost'：ky32表示用户名。localhost表示新建的用户ky32可以在哪些主机上登录
'ky32'@'localhost'：只能从本机登录
'ky32'@'192.168.233.22'：ky32在这个ip上可以登录
'ky32'@'192.168.233.0/24'：ky32在这个网段可以登录
'ky32'@'%'：ky32在所有主机都可以登录

select PASSWORD('abc123') 
#加密密码
create user 'ky33'@'localhost' identified by '*6691484EA6B50DDDE1926A220DA01FA9E575C18A'; #加密的方式创建用户

给用户赋权

grant all privileges on * . * to 'ky32'@'localhost' identified by '123456'; 
#给用户赋权（.没有空格） 
grant：赋权开头语句 
all privileges：赋予所有权限 
on *.* ：所有库都有操作权限 
on 库名.*：只能对指定的库进行操作
to 'ky32'@'localhost'：赋权给哪个用户。创建用户时候是什么赋权就要是什么上下必须一致 identified by '123456'：使用哪个密码进行登录。创建用户时候不写密码，密码就默认为空。

show grants for 'ky32'@'localhost'; 
#查看指定用户

flush privileges; 
#刷新用户权限

revoke all privileges on kgc. * from 'test1'@'20.0.0.50'; 
revoke：删除权限。也需要一致给哪个库权限就得删除哪个库权限。

对权限进行控制

grant select on kgc.* to 'test1'@'20.0.0.50' identified by '123456'; 
#给指定用户查询权限。

revoke select on kgc.* from 'test1'@'20.0.0.50'; 
#删除指定权限。删除select查询权限。

给一个用户赋予多个权限

多个权限之间用逗号隔开

grant select,insert,drop on kgc.* to 'test1'@'20.0.0.50' identified by '123456'; 
#给一个用户多个权限。每个权限都需要单独赋权

revoke drop,update on kgc.* from 'test1'@'20.0.0.50'; 
#删除多个权限。每个权限用逗号隔开。

revoke all privileges on kgc.* from 'test1'@'20.0.0.50'; 
#也可也使用 revoke all privileges 删除多个权限。

给用户重命名

rename user 'test1'@'20.0.0.50' to 'guoqi'@'20.0.0.50'; 
#给用户重命名。也必须前后一致

删除用户

drop user 'ky33'@'localhost'; 
#删除指定用户。必须前后一致

修改用户密码

set password = password('abc123'); 
#修改当前用户的密码。必须在终端中执行

set password for 'guoqi'@'20.0.0.50' = password('abc123'); 
#给其他用户修改密码

恢复root密码

vim /etc/my.cnf

skip-grant-tables 
#添加免密登录

systemctl restart mysqld 
#重启mysql服务

重写进入mysql不用输入密码直接进入

use mysql;

show user;

select * from user;

select user,authentication_string,host from user;

updates mysql.user set

update mysql.user set authentication_string = '123456' where user = 'root'; 
#重置root密码

update user set authentication_string=password('123456') where Host='localhost' and User='root'; 
#修改密码之后需要加密。慎用。

update user set authentication_string = password('123456') where host='%' 
#加密密码

用户权限管理的总结

create user '用户名'@'20.0.0.50' identified by '123456';：创建用户

grant all select ,insert,drop：赋权

revoke：删除权限

rename：修改用户名

drop user:删除用户

set password = passwd('123456')：修改登录用户的密码

set password for '用户名'@'20.0.0.50' = password('abc123'):修改其他用户密码

show grants for '用户名'@'localhost'：查看用户权限 flush privileges：刷新权限

用户权限管理的总结

create user '用户名'@'20.0.0.50' identified by '123456';：创建用户

grant all select ,insert,drop：赋权

revoke：删除权限

rename：修改用户名

drop user:删除用户

set password = passwd('123456')：修改登录用户的密码

set password for '用户名'@'20.0.0.50' = password('abc123'):修改其他用户密码

show grants for '用户名'@'localhost'：查看用户权限 flush privileges：刷新权限

mysql的索引、事务和存储引擎

索引

索引是一个排序的列表，列表当中存储的是索引的值和包含这个值的数据所在行的物理地址

索引的作用

索引的核心作用是加快查询速度。实现快速查找

1、 利用索引数据库可以快速定位，大大加快查询速度，主要作用

2、 表的数据很多，查询需要关联多个表，这个时候索引也可也提高查询速度

3、 加快表与表之间的连接速度

4、 使用分组和排序时，可以大大减少时间

5、 可以提高数据库，恢复数据时的速度

索引创建的原则

1、 如果有索引，数据库会先进行索引查询，然后定位数据。索引使用不当，反而会增加数据库的负担。

2、 主键、外键必须有索引。创建好的主键和外键，自动就是索引不需要额外声明了。

3、 如果说有一个表超过了300行必须要有索引，否则数据库会遍历表的所有数据，查询速度会很慢。

4、 互相之间有关联的表，在这个关联字段上一定要设置索引。

5、 唯一性太差的字段，不适合做索引。

6、 更新太频繁的字段，不适合做索引。

7、 经常被where条件匹配的字段，尤其是表数据比较多的。应该创建索引。

8、 经常进行group by(分组) order by(排序) 这种语句的字段要建立索引。

9、 索引的字段越小越好，长文本的字段，不适合建立索引。

索引的类型

常用类型：

B-树索引：BTREE 树型结构的索引，也是大部分数据库的默认索引类型。
根节点：树的最顶端的分枝节点
分枝节点：指向索引里其他的分枝节点，也可以是叶子节点
叶子节点：直接指向表里的数据行

创建BTREE索引的方式

create index name_index on test (name); 
#创建BTREE索引

show index from test; 
#查看表的索引和引擎

哈希索引：散列缩影 把任意长度的输入，通过散列算法变换成固定长度的输出。散列的值----分别对饮数据里的列和行

mysql的默认引擎：INNODB 默认引擎的索引类型就是Btree

MEMORY引擎可以支持hash，也是他的默认索引。

先算散列值，然后再对应，速度比较慢，比BTREE慢。

hash的索引匹配：= in（）<=>

show index from test;

show create table test; 
#查看表的索引和引擎

alter table test engine=memory;

alter table test drop primary key;

select * from test where sex = '豪车';

create index idx_hash_column on test (sex) using hash; 
#创建hash索引的方式

创建索引

普通索引

create table member (
id int(4) primary key,
name varchar(10),
card_id int(18),
phone int(11),
index name_index (name)
);

show index from member;
#查看表索引

alter table member add index cradid_index (card_id);
#外部添加索引。添加card_id为索引，索引名可以随意编辑。

唯一索引

唯一索引和unique相关。与普通索引是类似的。区别在于唯一索引的每个值都是唯一。而且唯一索引是允许空值的，只有添加唯一键才会创建唯一索引。如果约定了唯一值，尽量不要为空。如果空值太多，索引也可能会失效。

create unique index phone_index on member (phone);
#创建唯一索引。索引名：phone_index 跟上列名phone。

alter table member add column address varchar(40) not null;
#外部添加列名。

desc member;

alter table member add unique address_index(address);
#外部添加索引

show index from member;
#查看索引值

0代表不能重复

1代表可以重复

只有不重复的时候索引值才会生效

create table test (
id int(4) primary key,
name varchar(10),
card_id int(18) not null,
phone int(11) not null,
unique cardid_index (card_id),
unique phone_index (phone)
);
#虽然没有给这两个列(card_id和phone)做约束，但是通过唯一索引的方式说明了这两个列(card_id和phone)输入的数据不能相同。否则数据无法提交保存。

show index from test;
#查看索引值

两个列（card_id和phone）的数据相同会报错，因为使用了unique创建唯一索引声明了不能重复

能在建表时创建好的条件，尽量在创建时把条件约束好，不要创完之后再添加。

主键索引

创建表的时候指定的主键就是索引。添加主键自动就是主键索引。

主键的特点

1 主键的值唯一

2 一个表只能有一个主键

3 不允许有空值

4 创建主键，自动主键索引

全文索引

一般用于text（长文本文件）

全文索引适合在进行模糊查询的时候使用，可以在一篇文章中检索文本信息。

create table test2 (
id int(4) primary key,
name varchar(10),
card_id int(18) not null,
phone int(11) not null,
notes text,
unique cardid_index (card_id),
unique phone_index (phone)
);

create fulltext index notes_index on test2 (notes);
#创建全文索引

show index from test2;

在建表语句中添加

create table test3 (
id int(4) primary key,
name varchar(10),
card_id int(18) not null,
phone int(11) not null,
notes text,
unique cardid_index (card_id),
unique phone_index (phone),
fulltext notes_index (notes)
);
#建表时创建

show index from test3;

drop index notes_index on test3;
#删除索引。索引名称为设置的 索引名_index

select * from test3 where like '%'
#模糊查询。where like：表示模糊查询

alter table test3 add fulltext notes_index (notes);
#外部添加全文索引。

联合索引

指定一个索引名下包含多个列名

desc test3;

create index index_union on test3 (card_id,phone);
#创建联合索引。

show index from test3;

explain select * from test3 where card_id like '3%';

create table test4 (
id int(4) primary key,
name varchar(10),
cardid int(18) not null,
phone int(11) not null,
unique index name_cardid_phone (name,cardid,phone)
);

select * from test4 where name = 'test' and cardid = 1111 and phone = '222';

show index from test4;


explain select * from test4 where name = 'test' and cardid = 1111 and phone = '222';

explain select * from test4 where cardid = 1111 and id = 1;

#mysql机制：默认会找最短的索引列。最优索引选择

#联合索引，从左到右测开始，不能跳过索引，否则索引会失效。
#范围查询，有可能右侧的索引会失效。

EXPLAIN select * from test5 where age > 12 and address = '分割'
#会选择一个最优索引
#使用范围查找，可能会有一侧失效

EXPLAIN select * from test5 where address = '111'
#如果使用索引搜索时没有加''引号可能会导致一侧索引失效
#如果索引是字符串，但是没加引号，索引也会失效

EXPLAIN select * from test5 where id=18 or address = '111'
#使用or语句索引一定失效。使用or作为条件，mysql无法同时使用多个索引。

is null
is not null
#使用这两个条件的时候，有时候索引会失效。 
#使用 where is null 当数据的绝大多数都是空值时，索引一定失效。
#使用 where is not null 当数据多数不为null，索引一定失效。

in not in 
#in ages:索引生效
#not in age：索引会失效

总结

索引的作用：

1. 利用索引快速定位，实现快速查找。加快查找速度

2. 加快表与表之间的连接

3. 表书很大时，可以提高查询速度

4. 提高数据库效率

   索引创建的原则：

   1. 表超过300行必须要有索引，否则会遍历所有。导致速度很慢

   2. 唯一性太差的字段，不适合做索引

   3. 更新太频繁的字段，不适合做索引

索引的副作用：索引也需要占用额外的磁盘空间 innodb表数据文件本身也是索引。 myisam：索引和数据文件是分离的。 更新一个包含索引的表，要比更新一个没有索引的表花费的时间更多。更新值了值。也就是更新索引。

创建表的时候需要考虑的因素：

1. 关联的程度 3张表，选好关联字段

2. 每个字段的长度，也要考虑

3. 涉及合理的索引列

4. 表数据，要控制在合理的范围内，可以在牺牲一定性能的条件下，满足请求。5秒以上就要考虑优化了。10秒以上一般是出问题了.（缓存失效、缓存击穿、缓存雪崩。）

索引类型：

BTREE：MySQL orcal 默认都是树结构的索引

hash：散列值，只能 = in() <=> hash索引不能排序。排序时索引是失效的。

创建索引：普通索引、主键索引、唯一索引、全文索引、联合索引

创建索引时注意索引失效的情况。

可以使用explain加在查询语句的前面，可以查看索引的使用情况

MySQL的事务机制

事务是一种机制，一个操作序列。包含了一组数据库的操作命令，所有命令都是一个整体。向系统提交或者撤销的操作。要么都执行，要么都不执行。是一个不可分割的单位。

事务的特点：ACID： A：原子性。最小单位，事务里的所有条件都是一个整体，不可分割。要么都成功，要么都失败。

C：一致性。事务开始之前和事务结束之后，数据库的完整性约束，没有被破坏。 事务完成时数据必须处于一致状态。 事务开始前，数据库中的存储数据处于一致状态。 进行中的事务，数据可能处于一致状态 在事务最终完成时，必须再次回到已知的一致状态。

I：隔离性。只在并发环境中，当不同事务，同时操纵相同的数据时，每个事务都有各自完整的数据空间。对数据进行修改的所有并发事务，是彼此隔离的。表明事务必须是独立的

修改数据的事务可以在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同事务结束之后访问这些数据。

MySQL支持的四种隔离界别

1、 为提交读：允许脏读。允许一个事务可以看到其他事务未提交的修改。

2、 提交读：事务只能查看已经提交的修改，未提交的修改，是不可见的。防止脏读。orcale、sql server 使用的就是提交读

3、 可重复读：是MySQL的默认隔离级别，确保如果在一个事务中执行两次，相同的select语句时，都能得到相同的结果。不管其他事务是否提交这些修改。可以防止脏读以及不可重复读。

4、 串行读：锁表。完全串行化，每一个事务都隔离，读写都会阻塞。会降低数据库的效率（效率最低，已被淘汰）

脏读：另外一个事务能够看到另一个事务未提交的修改结果。

事务隔离级别的作用范围

1、 全局级：对所有的会话有效

2、 会话级：只对当前的会话有效

查询事务和会话

show global variables like '%isolation';
#查询全局事务的隔离级别

select @@global.tx_isolation;
#也是查询全局事务的隔离级别

show session variables like '%isolation';
#查看会话级别

select @@session.tx_isolation;
#查看会话级别

修改全局事务和修改会话

set global transaction isolation level read uncommitted; 
#设置全局事务的隔离级别。全局修改

set @@global.tx_isolation='read uncommitted'; 
#设置全局事务的隔离级别。临时修改。重启及失效

set session transaction isolation level read uncommitted; 
#修改会话。全局修改。

set @@session.tx_isolation='read uncommitted'; 
#修改会话，临时修改。重启及失效

脏读

内部终端：

create table test (
id int(4) primary key,
money int(10)
);

insert into test values(1,1000);
insert into test values(2,500);
#插入数据

begin

updata test set money=money-800 where id =1;
#修改金额

select * from test;

外部未提交，内部查看金额发生了变化，但是外部事务实际上没有提交这就是脏读

不可重复读

不可重复读：一个事务内，多次读同一个数据。前一个事务还没有结束，另一个事务也访问该数据。 在一个事务之内，两次查询结果不一致。读不到相同的数据内容。

内部外部终端都修改全局事务和会话

set session transaction isolation level read committed;
#修改会话。全局修改为提交读。

set global transaction isolation level read uncommitted;
#修改全局事务。全局修改为提交读。

show global variables like '%isolation';
#查询全局事务的隔离级别

flush privileges;

在内部终端里：

select * from test；

begin;
 
update test set money=money-200 where id =1;

回到外部终端：

select * from test;

commit;

终端1数据未发生变化

终端2事务未提交但是却查询到了提交之后的结果。

两次出现的数据不一致

幻读

一个事务对一个表中的数据进行了修改，可能会涉及到表中的全部数据。

另一个事务也修改了表中的数据。例如插入了一行新数据，前一个事务就会发现表中还有数据没有修改，类似于幻觉。

内部终端开启

begin

外部修改数据但是没有提交

在外部插入一条数据没有提交，但是内部却查看到了

同时修改提交MySQL会默认以最后修改为准

丢失更新

两个事务同时修改一条记录，A先记录，B也修改记录，B一旦提交会覆盖A的结果。

外部修改了id3的位置但是没有提交

内部修改金额出现了丢失更新

此时内部提交了结果

在外部查看

此时外部再提交结果

内部结果会以最后一次提交为准

以上这些情况如何避免

1、 权限控制。权限要分开，一个只能改，一个只能删。方便定位问题

2、 根据情况来使用隔离级别。生产环境----最好是提交读和可重复读，根据实际情况来。测试环境---随意(只要不删除就可以)。

3、 生产环境中只能允许一个人对一个事务进行操作，其他人不允许操作。

事务级别----持久性

持久性：数据提交。事务的效果将会永久保存在数据库当中。而且不会被回滚。

事务总结

在事务管理中原子性是基础，隔离性是手段，一致性是目的，持久性是最终的结果。

事务的控制语句

begin start transaction 
#都可以作为显示的开启事务

commit commit work 
#都可以作为提交事务

rollback rollback work 
#都可以作为回滚（可以撤销正在进行的所有未提交的修改）

savepoint s1(名字自定义) 
#创建回滚点（还原点）一个事务可以又多个回滚点

rollback to s1 
#回滚到还原点

如何设置多个还原点

begin
#开启事务

update test set money=money-30 where id = 2;
#修改信息

savepoint s1;
#设置回滚点1

select * from test;

update test set money=money-20 where id = 1;
#修改信息

savepoint s2;
#设置回滚点2

rollback to s1;
#回滚到回滚点1

select * from test;


rollback to s2;
#回滚到回滚点2

select * from test;

多点还原

begin
#开启事务

update test set money=money-80 where id = 1;

savepoint s1;

select * from test;

update test set money=money-50 where id = 2;

savepoint s2;

select * from test;

insert into test values(3,40);

select * from test;

rollback to s2;

select * from test;

rollback to s1;

select * from test;

commit;

rollback to s1;

一旦提交所有还原点将全部消失

1、 多点还原到s1，s2将消失。如果提交，所有还原点将全部消失。

MySQL提交事务默认是自动提交

实验：可以用set来设置MySQL的提交方式：

show variables like 'autocommit'
#查看MySQL的提交级别on代表开启自动提交

set autocommit=0;
#关闭自动提交

flush privileges;
#刷新权限

没有使用begin开始事务

update test set money=money-100 where id =2;
#修改id2的money信息剪掉100

savepoint s1;
#设置回环点1

update test set money=money-100 where id =1;
#修改id1的money信息剪掉100

savepoint s2;
#设置回环点2

rollback to s2;
#回滚到回换点2

select * from test;
#查看表内容

rollback to s1;
#回滚到回魂点1

select * from test;
#查看表数据

然后没有commit结束事务

退出终端重新登录查看结果是否生效

此时还是不使用begin开启事务

再次更新数据。设置回滚点，手动提交事务测试

总结

索引的作用：

1. 利用索引快速定位，实现快速查找。加快查找速度

2. 加快表与表之间的连接

3. 表书很大时，可以提高查询速度

4. 提高数据库效率

   索引创建的原则：

   1. 表超过300行必须要有索引，否则会遍历所有。导致速度很慢

   2. 唯一性太差的字段，不适合做索引

   3. 更新太频繁的字段，不适合做索引

索引的副作用：索引也需要占用额外的磁盘空间 innodb表数据文件本身也是索引。 myisam：索引和数据文件是分离的。 更新一个包含索引的表，要比更新一个没有索引的表花费的时间更多。更新值了值。也就是更新索引。

创建表的时候需要考虑的因素：

1. 关联的程度 3张表，选好关联字段

2. 每个字段的长度，也要考虑

3. 涉及合理的索引列

4. 表数据，要控制在合理的范围内，可以在牺牲一定性能的条件下，满足请求。5秒以上就要考虑优化了。10秒以上一般是出问题了.（缓存失效、缓存击穿、缓存雪崩。）

索引类型：

BTREE：MySQL orcal 默认都是树结构的索引

hash：散列值，只能 = in() <=> hash索引不能排序。排序时索引是失效的。

创建索引：普通索引、主键索引、唯一索引、全文索引、联合索引

创建索引时注意索引失效的情况。

可以使用explain加在查询语句的前面，可以查看索引的使用情况

练习题

1、 创建用户，声明网段 test 网段任选 密码123456

2、 创建一个库。库名：test1

3、 在库中随意创建两个表 table1和table2

table1

id 主键

name not null

sex not null



table2

id 主键

address 默认地址不详

phone unique

4、 test 用户可以对test1库 只有 select和insert 两个权限

5、 把tesst的用户名修改为test_123 密码修改为abc123

6、 删除insert权限

7、 给两个表分别创建索引。table1是hash类型sex做索引 table2的索引是BTREE类型phone做索引。

create user 'test'@'20.0.0.%' identified by '123456';
#创建用户

create database test1;
#创建库

create table table1 (
id int(4) primary key,
name varchar(5) not null,
sex varchar(5) not null
);
#创建表1

desc table1;

create table table2 (
id int(4) primary key,
address varchar(50) default '地址不详',
phone varchar(20) unique
);
#创建表2

desc table2;

grant select,insert on test1.* to 'test'@'20.0.0.%' identified by '123456';
#给指定用户对应指定表。查和添加的权限

rename user 'test'@'20.0.0.%' to 'test_123'@'20.0.0.%';
#给用户改名

set password for 'test_123'@'20.0.0.%' = password('abc123');
#改用户密码

revoke insert on test1.* from 'test_123'@'20.0.0.%';
#删除指定用户对指定表添加的权限

show index from table1;
#查看表的索引和引擎

alter table table1 engine=memory;
#清空引擎

alter table table1 drop primary key;
#删除主键

select * from table1 where sex = 'man';

create index idx_hash_column on table1 (sex) using hash;
#创建索引

show index from table1;
#查看表的索引和引擎

show index from table2;
#查看表的索引和引擎

create index phone_index on table2(phone);
#创建索引

show index from table2;
#查看表的索引和引擎

创建用户

创建一个库

创建两个表

表1结构

 

表2结构

给用户权限

给用户改名

给用户修改密码

删除权限

修改索引和引擎

修改索引

现在一张表的查询速度是7.62s 你该如何解决？

答：

1、 首先是检查缓存，看看是不是直接请求到了后端数据库。如果是缓存的问题就修复缓存。

2、 如果不是缓存的问题就查看索引。查看索引，请求的列值是不是默认的索引，添加一下即可。使用 explain加在查询语句的前面，可以查看索引的使用情况。

3、 如果就一个索引查询时间还是很长，查看一下是不是查询时候后面跟的条件太多了。他会遍历所有条件。可以创建一个联合索引解决问题。

案例：商场做一个会员卡系统，这个系统有一个会员表，有一下字段：

会员编号 int 联合

会员姓名 varchar(10) 普通索引

会员身份证号码 int(18) 联合

会员电话 int(11) 主键索引

会员住址 varchar(50) 唯一索引

会员备注信息 text 全文索引

1、 创建表，vip_member

2、 根据你的选择，来创建这张表的索引。

3、 每个列都要创建索引。执行查看索引的使用情况

create table vip_member (
id int(4) not null,
name varchar(10) not null,
cardid int(18) not null,
phone int(11) not null primary key,
adress varchar(50) not null,
notes text
);

desc vip_member;

create unique index cardid_index on vip_member (cardid);
#设置身份证是唯一索引

create fulltext index notes_index on vip_member (notes);
#设置备注为全文索引

create index union_index on vip_member (id,name,adress);
#设置id、name、家庭住址为联合索引

show index from vip_member;

select * from vip_member where phone = '987';
explain select * from vip_member where phone = '0987';
#主键索引

select * from vip_member where cardid = '320';
explain select * from vip_member where cardid = '320';
#唯一索引

select * from vip_member where id = 1;
explain select * from vip_member where id = 1;
#单个联合索引

select * from vip_member where id = 3 and name = '珊迪' and adress = 3333333;
explain select * from vip_member where id = 3 and name = '珊迪' and adress = 3333333;
#多个联合索引

select * from vip_member where notes like '1%';
explain select * from vip_member where notes like '%1';
#全文索引

插入表内容

1. 创建表，vip_member

2. 根据你的选择，来创建这张表的索引。

Phone作为主键、身份证设为唯一索引

设置备注为普通索引

设置id、name、家庭住址为联合索引

每个列都要创建索引。执行查看索引的使用情况

查看主键的索引

查看唯一的索引

查看联合索引（单个）

查看联合索引（多个）

查看普通索引