• 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;

  • 相关阅读:
    【leetcode】9. Palindrome Number
    【leetcode】7. Reverse Integer
    【Leetcode】6. ZigZag Conversion
    Mysql读写分离
    Windows下为PHP安装redis扩展
    jquery 写的图片左右连续滚动
    PHP中spl_autoload_register函数的用法
    MySQL命令行查询乱码解决方法:
    AJAX 教程
    Mac安装composer爬过的坑
  • 原文地址:https://www.cnblogs.com/klw1/p/11042208.html
Copyright © 2020-2023  润新知