day-44（2）mysql

ps :
针对mysql的:
start transaction
drop table t1;
rollback;

无法回滚删除操作，
其他的数据库中也是一样的, 但是除了oracle(flashback)

现在阿里云上, 数据库不建议使用外键,效率问题

存储引擎:

　　create table t1(
　　id int auto_increment primary key,
　　name varchar(32) not null default ''
　　)engine=Innodb charset=utf8;

分类: 
　　Innodb
　　1.(默认版本包含5.5)
　　2.支持事务
　　3.不支持全文索引
　　4.索引和数据都是在同一个文件中, .ibd
　　　　表的结构实在.frm文件中

　　MyIsam
　　1.(默认版本5.5以下 5.3)
　　2.不支持事务
　　3.支持全文索引(类似百度搜索框在数据库搜索)
　　4..frm: 表结构
　　.MYD: 表数据
　　.MYI: 表索引

　　memory

　　全文索引:
　　　　sphinx

　　1).文章,新闻等安全性要求不高的,选myisam
　　2). 订单,资金,账单,火车票等对安全性要求高的,选用innodb
　　3). 对于临时中转表,可以用memory型 ,速度最快

索引:

　　作用: 加快查询的速度
　　　　类比: 新华字典的目录, 可以将索引理解成一个特殊的文件, 然后如果没有这个文件的话, 查询是从前到后查找数据的,
　　　　如果有这个文件的话, 会按照一种特殊的数据结构(二叉树)查找数据

　　分类:
　　　　主键索引: 加快查询 + 不能重复 + 不能为空 primary key
　　　　唯一索引: 加快查询 + 不能重复 unique(列名)
　　　　联合唯一索引: 加快查询 + 不能重复 unique(列名1,列名2)
　　　　普通索引: 加快查询 index('列名')

　　创建:
　　　　主键索引:
　　　　　　第一种:
　　　　　　　　create table t1(
　　　　　　　　id int auto_increment primary key,
　　　　　　　　name varchar(32) not null default ''
　　　　　　　　)engine=Innodb charset=utf8;
　　　　　　第二种:
　　　　　　　　alter table t1 change id id int auto_increment primary key;

　　　　唯一索引:
　　　　　　第一种:
　　　　　　　　create table t1(
　　　　　　　　id int auto_increment primary key,
　　　　　　　　name varchar(32) not null default '',
　　　　　　　　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);

　　　　普通索引:
　　　　　　第一种:
　　　　　　　　create table t1(
　　　　　　　　id int auto_increment primary key,
　　　　　　　　name varchar(32) not null default '',
　　　　　　　　index ix_name ('name')
　　　　　　　　)engine=Innodb charset=utf8;

　　　　　　第二种:
　　　　　　　　create index 索引名称(ix_name) on 表名(t1)(name);

　　删除:
　　　　drop 索引名称(ix_name) on 表名(t1);

　　查看索引:
　　　　show index from 表名；

　　场景:
　　　　使用频繁的列上加一个索引

　　索引的缺点:
　　　　版本5.3以下:
　　　　删除和修改的速度就变慢了

　　　　版本5.5以上:
　　　　删除和修改的速度不是特别的慢

　　　　create table t12(
　　　　id int auto_increment primary key,
　　　　name varchar(32) not null default '',
　　　　email varchar(32) not null default ''
　　　　)engine=Innodb charset=utf8;


　　索引的使用:

　　　　explain 工具

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

　　　　　　给执行的SQL语句出一个报告, 通过此报告来判断sql语句的执行效率和效果

　　　　　　id
　　　　　　　　查询顺序标识
　　　　　　select_type
　　　　　　　　查询类型
　　　　　　　　SIMPLE 简单查询
　　　　　　　　PRIMARY 最外层查询
　　　　　　　　SUBQUERY 映射为子查询
　　　　　　　　DERIVED 子查询
　　　　　　　　UNION 联合
　　　　　　　　UNION RESULT 使用联合的结果
...
　　　　　　table
　　　　　　　　正在访问的表名
　　　　　　type
　　　　　　　　查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
　　　　　　　　ALL 全表扫描，对于数据表从头到尾找一遍

　　　　　　　　INDEX 全索引扫描，对索引从头到尾找一遍

　　　　　　　　RANGE 对索引列进行范围查找

　　　　　　　　REF 根据索引查找一个或多个值

　　　　　　CONST 常量
　　　　　　　　表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,
　　　　　　　　const表很快,因为它们只读取一次。
　　　　　　　　select nid from tb1 where nid = 2 ;
　　　　　　SYSTEM 系统
　　　　　　　　表仅有一行(=系统表)。这是const联接类型的一个特例。
　　　　　　possible_keys
　　　　　　　　可能使用的索引

　　　　　　key
　　　　　　　　真实使用的

　　　　　　key_len
　　　　　　　　MySQL中使用索引字节长度

　　　　　　rows
　　　　　　　　mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值

　　　　　　extra
　　　　　　　　该列包含MySQL解决查询的详细信息
　　　　　　“Using index”
　　　　　　　　比较好，使用了索引
　　　　　　“Using where”
　　　　　　　　比较好，使用了索引
　　　　　　“Using temporary”
　　　　　　　　没用到索引，或sql语句有问题
　　　　　　“Using filesort”

　　　　　　　　没用到索引，或sql语句有问题
　　ES (elasticsearch )
　　SQL语句的规则:
　　　　- 不建议使用 like 进行搜索　　　　

　　　　-不建议使用函数
　　　　-类型不一致(规定的和传入的不一致)
　　　　-order by 排序的列没有索引，不建议

　　- 组合索引最左前缀
　　如果组合索引为：(name,email)
　　where name and email -- 使用索引
　　where name -- 使用索引
　　where email -- 不使用索引

慢日志查询(slow log):

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

　　开启的步骤:
　　　　1. show variables like '%query%';
　　　　2. set global long_query_time = 1; 设置慢查询的时间
　　　　3. set global slow_query_log = ON 开启慢日志
　　　　4. set global slow_query_log_file = E:programmysql-5.6.44-winx64dataoldboy-slow.log 慢日志文件

普通日志记录(general log):

　　SQL审计 (记录sql的操作语句)

　　show variables like '%general%';
　　+------------------+------------------------------------------------+
　　| Variable_name | Value |
　　+------------------+------------------------------------------------+
　　| general_log | ON |
　　| general_log_file | E:programmysql-5.6.44-winx64dataoldboy.log |
　　+------------------+------------------------------------------------+
　　set global general_log = ON;


权限管理:

　　创建用户
　　create user '用户名'@'IP地址' identified by '密码';
　　creaee user 'zekai'@'192.168.1.123' identified by '123qwe';
　　creaee user 'zekai'@'192.168.1.%' identified by '123qwe';
　　create user 'zekai'@'%' identified by '123qwe';


　　删除用户
　　　　drop user '用户名'@'IP地址';
　　修改用户
　　　　rename user '用户名'@'IP地址' to '新用户名'@'IP地址';
　　修改密码
　　　　set password for '用户名'@'IP地址' = Password('新密码')
　　查看权限
　　　　show grants for '用户'@'IP地址'

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

　　　　grant select on db1.* to 'zekai'@'%';
　　　　grant select on *.* to 'zekai'@'%';
　　　　grant select, insert, delete on db1.* to 'zekai'@'%';
　　　　*.* 所有
　　　　select, insert, delete 权限
　　取消授权：
　　　　revoke 权限 on 数据库.表 from '用户'@'IP地址'

　　记住:
　　　　flush privileges;