数据库备份与索引

青旅半醒 2024-03-26 18:19 45阅读 0赞

#  数据库备份
    # 逻辑迁移 和 物理迁移
    /*
    # 导出 退出数据库  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"）一般会走全表扫描，不能命中索引
    
    */