当前位置:Gxlcms > mysql > mysql不被人知的秘密_MySQL

mysql不被人知的秘密_MySQL

时间:2021-07-01 10:21:17 帮助过:80人阅读

bitsCN.com

先建立两张表。

1.student表

/

2.grade表

/

一:mysql的复制技术

1.表与数据的复制->>实现表结构和数据的同步

create table desttable select * from srctable;

(desttable:目标表,srctable:原表)

2.表结构的复制->>只实现表结构的同步

create table desttable select * from srctable where 0>1;

想想为什么???

select语句既产生了表结构又产生了结果集,如果后面限制条件不成立,那么结果集为空,就实现了只复制表结构。

3.全表记录的复制->>将一个表的全部记录插入另外一个表

insert into desttable select * from srctable;

4.部分字段的复制

insert into desttable(字段一,字段二.......) select (字段一,字段二.......) from srctable;

二:索引注意事项

(1):使用FULLTEXT参数可以设置索引为全文索引,全文索引只能创建在CHAR ,VARCHAR ,TEXT类型字段上。->>但只有MyISAM存储引擎支持全文索引。

(2):多列索引:在表的多列字段上建立一个索引,但只有在查询这些字段的第一个字段时,索引才会被使用。

(3):查询语句使用like关键字进行查询,如果匹配的第一个字符为”%“时,索引不会被使用

select * from student where num like '%4'; //索引不会被使用

select * from student where num like '4%'; //索引会被使用

(4):查询语句中使用or关键字时,只有or前后两个条件的列都是索引时,查询时才使用索引

(5): 学会使用explain检查索引是否被使用,我们用explain命令检验(2)的说法

先创建索引:create index index_nu_name on student(num,name);

#1. explain select * from student where num=2;

执行结果如下:

/

#2.explain select * from student where name=‘lwy’;

执行结果如下:

/

通过比较,我们发现,第二个的possible-key和key等都为空,而且Extra显示查询用where子句没有用索引。

我们关注一下rows信息,如果用where子句,则查询行数为2,如果使用索引,则查询行数为1(这张表只有2条记录,可以想象如果记录为上万条,用索引真的很快)

所以,通过实实验证明了我们(2)的说法。

三:权限管理

mysql中的权限分配是按照user表,db表,tables_priv表,columns_priv表的顺序进行分配的。数据库系统中,

先判断user表中的值是否是Y,如果是Y则就不需要检查下面的表了。如果user表的为N,则依次检查db表,tables_priv表,和columns_priv表。

简单的说,就是user表存储的是对所有数据库的权限,db表存储的是对某一数据库的权限,tables_privs存储的是对数据库表的权限,columns_privs存储的是表中列的权限。

比如我们新建一用户:

#1.create user 'test1' identified by 'admin'; //注意:用户名和密码要交引号,为什么->>字符串

注意下面的:create user 'test2'@localhost identified by 'admin';

这两句有什么区别呢?

有localhost标识的代表该用户只能在本地连接mysql数据库,而不能通过远程连接。不带localhsot的可以通过远程连接mysql数据库。

显著的区别就是在user表的Host字段一个是localhost一个是%

这时我们用test1用户连接mysql,发现成功连接,用命令show databases 发现只有information_schema这个数据库,为什么会有这个数据库呢??

information_schema数据库是MySQL自带的,它提供了访问数据库元数据的方式。什么是元数据呢?元数据是关于数据的数据,

如数据库名或表名,列的数据类型,或访问权限等。有些时候用于表述该信息的其他术语包括“数据词典”和“系统目录”。在MySQL中,

把information_schema 看作是一个数据库,确切说是信息数据库。其中保存着关于MySQL服务器所维护的所有其他数据库的信息。

如数据库名,数据库的表,表栏的数据类型与访问权限等。在information_schema中,有数个只读表。它们实际上是视图,而不是基本表,

因此,你将无法看到与之相关的任何文件。

我们用root用户查看一下发现在user表中的权限全是N,也就是说什么都做不了。

这时给用户授予权限:grant seelct ,update on *.* to test1;

## *.*:第一个*代表数据库,第二个*代表表,所以*.*就是所有数据库的所有表。

这时会发现user表的select_priv和update_priv字段的值变为了Y。

这时再用test1用户连接mysql数据库,show database ,发现所有的数据库都可以查看和更新了。

#2.这时我们再给test2授权:grant select ,update on mysql.* to test2; //只能查询和更新mysql的所有表。

这时就会发现user表中用户test2的所有权限都是N,而db表中test2用户对mysql数据库的select_priv和update_priv的值为Y。

下面tables_priv表,和columns_priv表的权限由自己分析。

经过以上分析,我们对mysql的权限有了更深一点的认识。

四:mysql日志

mysql日志有四种:

#1.二进制文件:以二进制的形式记录了数据库中的操作,但不记录查询语句

#2.错误日志:记录mysql的启动,关闭和运行错误等信息,默认开启且无法停止

#3.通用查询日志:记录用户登录和记录查询的信息

#4.慢查询日志:记录查询时间超过指定时间的操作

默认情况下,只开启了错误日志的功能,其余的根据需要管理员设置

##使用二进制日志还原数据库

因为二进制文件存储更新数据库的语句,所以可以用来还原数据库

mysqlbinlog filename.number | mysql -uroot -p //mysqlbinlog 用于打开二进制文件

使用mysqlbinlog进行还原数据库时,必须是编号(number)小的先还原。

五:性能优化

#1.性能参数:

show status ; //查询mysql的性能参数

show status like 'value' ; //查看某一参数

常用参数介绍如下:

Connections:连接mysql服务器的次数

Uptime:mysql服务器的上线时间

Slow_queries:慢查询的次数

Com_select:查询操作的次数

Com_insert:插入操作的次数

Com_update:更新操作的次数

Com_delete:删除操作的次数

#2.优化查询

##2.1用explain和describe分析查询语句

注使用索引查询记录时,一定要注意索引的使用情况 ->>见上面使用索引的注意事项

我们将上面用过的explain来分析

explain select * from student where num=2;

执行结果如下:

1.id :select语句的编号

2.select_type:select语句的类型

#simple:简单查询,不包含连接查询和子查询

#primary:主查询或最外层的查询语句

#union:连接查询的第二个或后面的查询语句

2.table:查询的表

4.type:表的连接类型

#system:表中只有一条记录

#const:表中有多条记录,但只从表中查询一条

#all:对表进行了完整的扫描

#eq_ref:多表连接时,后面的表使用了union或者primary key

#ref:多表连接时,后面使用了普通索引

#unique_subquery:子查询使用了unique或者primary key

#index_subquery:子查询使用了普通索引

#range:查询语句给出了查询范围

#index:对表中的索引进行了完整的扫描

5.possible_keys:查询中可能使用的索引

6.key:查询使用的索引

7.key_len:索引字段的长度

8.ref:表示使用哪个列或常数与索引一起查询记录

9.rows:查询的行数

10:extra:查询附加信息

describe 语句使用方法和explain一样。

##2.2 优化子查询

多用连接查询来代替子查询

为什么呢?

因为子查询时,mysql需要为内层查询结果建立一个临时表,然后外层查询在临时表中查找,查询完后需要撤销临时表。

而连接查询不需要建立临时表,所以比子查询快。

##2.3优化插入记录的速度

插入记录时,索引,唯一性校验等都会影响到插入记录的速度。而且,一次插入多条记录和多次插入记录所耗费的时间也不同。

1.禁用索引

插入数据时,mysql会根据表的索引对插入的记录进行排序,降低插入速度。解决这个问题可以在插入记录之前禁用索引,等到插入完毕后在开启。

alter table 表名disable keys; //禁用索引

alter table 表名enable keys; //开启索引

2.禁用唯一性检查

插入数据时,mysql会对插入的记录进行唯一性检查,会降低插入速度。

set unique_checks=0; //禁用唯一性

set unique_checks=1; //开启唯一性

3.优化insert语句

insert into 表名values

(......),

(......);

insert into 表名values (......);

insert into 表名values (......);

上面两种插入方法,第一种与数据库的连接等操作,明显比第二种快。

##2.3分析表,检查表和优化表

分析表:分析关键字的分布

检查表:检查表是否存在错误

优化表:消除删除或者更新造成的空间浪费

当执行下面三个操作时,mysql数据库会给表加一个只读锁,只能读取记录,不能更新和插入记录。

1.分析表

analyze table 表名1 [,表名2....];

2.检查表

check table 表名1 [,表名2....] [option];

option有五个参数:quick,fast,changed,medium,extended ->>执行效率依次降低

-->option选项只对MyISAM类型的表有效。

3.优化表

optimize table 表名1 [,表名2....];

-->只能优化表中的varchar,blob和text字段

如果一个表使用了text或blob这样的数据类型,那么更新,删除等就会造成的磁盘空间的浪费。操作完成后,以前分配的磁盘空间不会自动回收。

使用optimize可以将磁盘碎片整理出来,以便利用。

下面是一些mysql基本使用命令。

一:mysql查看基本命令

(1):查看当前有哪些数据库

show databases;

(2):使用mysql数据库

use test;

(3):查看当前数据库下的表

show tables;

(4):查看上述grade表建立的命令

show create table grade;

(5):查看student表的结构

desc student;

(6):查看数据库支持的存储引擎

show engines;

show engines /G ; // /G让结果更美观

(7):查看默认存储引擎

show variables like 'storage_engine';

二:mysql的修改表

(1)将grade表的course字段的数据类型修改为varchar(20)

alter table grade modify course varchar(20);

(2)将s_num字段的位置改到course前面

alter table grade modify s_num int(10) after id;

(3)将grade字段改名为score

alter table grade change grade score varchar(10);

(4)删除grade的外键约束

alter table grade drop foreign key grade_fk;

(5)将grade的存储引擎修改为INnoDB

alter grade engine=INnoDB;

(6)将student的address字段删除

alter table student drop address;

(7)在student表中增加名位phone的字段

alter table student add phone int (10);

(8)将grade的表名修改为gradeinfo

lter table grade rename gradeinfo;

(9):删除student表

drop table student; //由于先前已经删除外键,所以才能删除父表studnet

bitsCN.com

人气教程排行