存储引擎 索引 日志查询（log） 管理权限

 

一、存储引擎：

　　create table t1(

　　　　id int auto_increement primary,

　　　　name varchar(32) not null default''

　　)engine=Innodb charset=utf8;

　　　　引擎分类：

　　　　　　Innodb:

　　　　　　　　1）5.5版本以上：默认版本为 Innodb

　　　　　　　　2）支持事务

　　　　　　　　3）不支持全文索引

　　　　　　　　4）索引和数据都在同一个文件中（索引是在.idb结尾的文件中；表的结构是在以.frm结尾的文件中）

　　　　　　MyIsam：

　　　　　　　　1）5.5版本以下的默认版本

　　　　　　　　2）不支持事务

　　　　　　　　3）支持全文索引

　　　　　　　　4）表结构：.frm | 表数据：.MYD | 表索引：.MYI

　　　　　　memory：

　　　　　　　　存储在内存中

　　　　　　　　全文索引：sphinx

　　　　　　blackhole:

　　　　　　　　放入的数据都找不到

　　　　　　　　

二、索引

　　1、索引作用：加快查询的速度

　　类比：新华字典的目录，可以将索引理解成一个特殊的文件，然后如果没有这个文件的话，查询是从前到后查找数据的，如果有这个文件，会按照一种特殊的数据结构（二叉树）查找数据

　　2、索引分类：

　　　　主键索引：加快查询、不能为空、primary key

　　　　唯一索引：加快索引、不能重复、unique 索引名称 (列名)

　　　　联合唯一索引：加快查询、不能重复、unique 索引名称 (列名1，列名2）

　　　　普通索引中：加快查询，index 索引名称 (列名)

　　3、创建索引

　　　　1）创建主键索引

　　　　　　第一种：创建表时创建主键索引

　　　　　　　　create table t1(

　　　　　　　　　　id int suto_increment primary key,

　　　　　　　　　　name varchar(32) not null default''

　　　　　　　　)engine=Innodb charset=utf8;

　　　　　　第二种：已有表，再创建主键

　　　　　　　　alter table 表名t1 change 新id 旧id int auto_increment primary key;

　　　　2）创建唯一索引

　　　　　　第一种：创建表时创建唯一索引

　　　　　　　　create table t1(

　　　　　　　　　　id int suto_increment primary key

　　　　　　　　　　name varchar(32) not null default'',

　　　　　　　　　　age int not null default'0',

　　　　　　　　　　unique ix_name('name')

　　　　　　　　)engine=Innodb charset=utf8;

　　　　　　第二种：已有表，再创建唯一索引

　　　　　　　　create unique index 索引名称（ix_name) on 表名（t1）（name）

　　　　　　　　create unique index 索引名称（ix_name_age) on 表名（t1）（name,age）

　　　　3）创建普通索引

　　　　　　第一种：创建表时创建唯一索引

　　　　　　　　create table t1(

　　　　　　　　　　id int suto_increment primary key,

　　　　　　　　　　name varchar(32) not null default'',

　　　　　　　　　　age int not null default'0',

　　　　　　　　　　index ix_name(name)

　　　　　　　　)engine=Innodb charset=utf8;

　　　　　　第二种：已有表，再创建唯一索引

　　　　　　　　create index 索引名称（ix_name) on 表名（t1）（name）

　　3、删除索引：

　　　　　　drop 索引名称（ix_name） on 表名(t1)

　　　　这不能删除主键索引，删除主键索引首先要去除自增，再删除主键索引

　　　　　　alter table t1 change id id int;

　　　　　　alter t1 drop primary key

　　4、使用场景：

　　　　　　在使用频率高的列上加索引

　　5、索引的缺点：

　　　　　　版本5.3以下：

　　　　　　　　删除和修改的速度变慢了

　　　　　　版本5.5以下：

　　　　　　　　删除和修改的速度不是特别的慢

　　6、索引的使用：

　　　　　　explain 工具

　　　　　　查看sql语句是否用的上索引，或者查看sql执行效率的工具

　　　　　　给执行的sql语句出一个报告，通过此报告的执行效率和效果

　　　　　　ES（elasticsearch）

　　　　　　sql语句的规则：

　　　　　　　　不建议使用like进行搜索

　　　　　　　　组合索引最左前缀

　　　　　　　　　　Eg：如果组合索引为：(name,email)

　　　　　　　　　　　　where name and email   使用索引

　　　　　　　　　　　　where name          使用索引

　　　　　　　　　　　　where email　　　　　　　不使用索引

三、日志查询（log）

　　1、慢日志查询（slow log）

　　　　　　日志文件：记录了执行速度特别慢的sql语句

　　　　1）开启的步骤：

　　　　　　（1）show variables like '%query%';

　　　　　　（2）set global long_query_time=num;　　　　自己设置慢查询时间num

　　　　　　（3）slow_query_time =on　　　　　　　　　　开启记录，

　　　　　　（4）slow_query_log_file = D:\python\.....  保存的文件路径

　　　　补充：怎么解决网站变慢？　　　　开启慢日志，记录慢的日志，在进行sql语句的分析

　　　　　　　一般DBA做

　　2、普通日志记录（general log）

　　　　sql审计（记录sql的操作语句）

　　　　show variables like '%general%';

　　　　　　

　　　　set global general_log = on

四、管理权限  ——主要用于运维

　　1、创建用户

　　　　create user '用户名'@'ip地址' identified by '密码'

　　　　Eg:

　　　　　　create user 'han'@'192.168.1.123' identified by '123'

　　　　　　create user 'han'@'192.168.1.$' identified by '123'

　　　　　　create user 'han'@'$' identified by '123'

　　2、删除用户

　　　　drop user '用户名'@'ip地址'

　　3、修改用户

　　　　rename user '旧用户名'@'ip地址' to user '新用户名'@'ip地址'

　　4、修改密码

　　　　set password for '用户名'@'IP地址' = password('新密码')

　　5、授权

　　　　grant 权限 on 数据库.表 to '用户名'@'IP地址'

　　　　grant select on db1.'*' to 'han'@'$'

　　　　grant select on '*' to 'han'@'$'

 

　　　　grant select,insert,delete on db1.'*' to 'han'@'$'

　　6、解除权限

　　　　

　　　　　　　　　　结束后要刷新:　　flush privileges