数据库备份与索引

#  数据库备份
# 逻辑迁移 和 物理迁移
/*
# 导出 退出数据库  mysqldump   > 导出，  d:/java1.sql 位置 
mysqldump -uroot -p java1 >d:/java1.sql
# 导入 < 
mysql -uroot -p s1< d:/java1.sql
# 或source导入
mysql> use s2;
mysql> source d:/java1.sql
*/
#eg1 退出数据库  >导出
mysqldump -uroot -p j01 > e:/myjava01.sql

# eg2 导入
#创建新的数据
create database s1;
#退出数据库  < 导入
mysql -uroot -p s1 < e:/myjava01.sql

# eg3 导入
# 创建数据库
create database s2;
# 选择数据库
use s2
# 注意mysql> 中导入数据库
mysql>  source e:/myjava01.sql

# 索引
# 普通索引
/*
(1) 创建索引CREATE INDEX indexName 索引名称 ON employee(name[(length)]字段名);
create index index_name on employee(name); #重要
(2) 修改表结构(添加索引)ALTER table employee ADD INDEX indexName2(name);
alter table employee add index index_name(name);
(3) 创建表的时候直接指定
CREATE TABLE myta1(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX  index_username(username)  
);  
(4) 删除索引的语法 DROP INDEX [indexName] ON mytable; 
drop index index_name on employee; #重要
alter table employee drop index index_name;
# 查看索引信息
show create table employee;
desc employee;
# 目的 添加索引提高查询效率
select * from employee where name ='zhanghei'; # 有索引name字段查询效率高。
#explain  查看 select 语句的查询速度和信息
explain select * from employee where name ='zhanghei';

1. const： 根据主键、普通唯一索引列等值匹配查询（is null除外），这种查询是很快的，查询速率认为是常数级别的，定义为const。
2.ref ： 根据普通索引等值匹配，或is null。（前面说的普通唯一索引列查询时 is null也是这种场景）。这种方式需要先根据普通索引
         匹配到多个主键，然后根据主键进行回表。
3.range：根据主键索引或普通索引（包含唯一索引）进行范围查找
4.index：索引覆盖，你查询的列刚好是索引列，即使查询条件是联合索引的非最左索引列，查询的条件是联合索引中的列，
		也可能会走索引覆盖
5.ALL: 全表扫描，直接扫描主键索引，这种访问方式称为all。
*/
# eg1 创建普通索引
show create table employee;
create index index_name on employee(name);
# eg2 删除普通索引
drop index index_name on employee;
# eg3 explain 解析查询select 信息
explain select name,salary from employee where id <= 1;
        查询类型       表名        type 索引访问方式                                              查询行数           额外                                                                           using where
		                        all（全表扫描） < index (组合索引非最左优先也有) 
								< range（范围 id<10） < ref(普通索引，唯一索引，组合索引) < const(主键)
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employee | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

# 唯一索引（了解）
#(1)创建索引CREATE UNIQUE INDEX indexName ON employee(name(length));
create unique index  unique_name on employee(name);
(2) 删除索引
#drop index unique_name on employee;

# 主键（了解）
#添加主键索引
alter table myta1 add primary key(id);
#删除主键
alter table myta1 drop primary key;
#注意： 如果主键是自增的列,应该先取消自增,才能删除,ALTER TABLE employee MODIFY id int;

# 外键（了解）
(1)删除外键： 注意: 先删除外键约束关系,然后删除Mysql添加的对应索引 
 解除外键约束关系
alter table employee drop foreign key employee_bifk_1;
 删除索引
alter table employee drop index job_id;
(2)创建外键
alter table employee add foreign key (job_id) references job(id);

(# 全文索引 （将来会有第三方中文分词插件）(了解)
# 使用在 通过关键字的匹配来进行查询过滤（like）,varchar,char,text 的字段
alter table employee drop index index_)n;
alter table employee add fulltext  index_full(name);
# 目的,提高like 命中效率

# 组合索引重要 drop index username on employee;
use java01;
show create table myadmin;
create index name_pass on myadmin(uname,password);

# 命中索引 : 最左优先原则
explain select id,uname from myadmin where uname="tom" and password=md5('123456');
explain select id,uname from myadmin where password=md5('123456') and  uname="tom";
explain select id,uname from myadmin where uname="tom";
explain select id,uname from myadmin where uname="tom" and age = 18; #uname="tom" 起作用，age = 18不起作用

# 不会命中索引
explain select id,uname from myadmin where  password=md5('123456');

select a.id,a.name,a.salary from employee as a
inner join depart as b on a.department_id = b.id

# 什么时候用索引
/*
(1)索引固然是能够加快查询的速度，但是也不是越多越好
(2)一般情况下我们只需要将查询频率比较高的字段添加索引即可，其他的字段慎加索引。
(3) 不建议join超过三个表，应该join的数据表越多，扫描的数据表就越多，性能也就越差；
  能使用索引的就使用索引，如果不使用索引并且驱动表跟join_buffer的比值比较大的情况下，建议拆分查询
(4) explain select * from city where id + 1 = 2; 不能命中索引，在使用索引时，索引的列不能够是一个表达式。
(5) 当有多个条件查询时，组合索引可以有效地提高MySQL的查询性能
(6) 唯一索引和普通索引都适用于读的场景，而唯一索引不适用于写的场景.在写场景比较多的情况下，普通索引的性能要优于唯一索引
(7) 使用左模糊匹配（like "%xx"）一般会走全表扫描，不能命中索引

*/