金沙国际官网_金沙国际平台登录

因为这个金沙国际官网_金沙国际平台登录网站与很多的大型澳门赌场都有合作,金沙国际官网_金沙国际平台登录尽职尽责,高效执行,保持好奇心,不断学习,追求卓越,点击进入金沙国际官网_金沙国际平台登录马上体验吧,所以现在也正式地开始了营业。

您的位置:金沙国际官网 > 数据库 > MyISAM和InnoDB的主要区别和应用场景

MyISAM和InnoDB的主要区别和应用场景

发布时间:2019-11-01 17:52编辑:数据库浏览(138)

    主要区别:

    索引

    1、概述

    MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。

    虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。

    建立索引会占用磁盘空间的索引文件。

    2、索引种类

    • 普通索引:仅加速查询
    • 唯一索引:加速查询 + 列值唯一(可以有null)
    • 主键索引:加速查询 + 列值唯一 + 表中只有一个(不可以有null)
    • 组合索引:多列值组成一个索引,
                    专门用于组合搜索,其效率大于索引合并
    • 全文索引:对文本的内容进行分词,进行搜索 

    索引合并:使用多个单列索引组合查询搜索
    覆盖索引:select的数据列只用从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖

    a、普通索引

    普通索引仅有一个功能:加速查询

    图片 1图片 2

    create table in1(
        nid int not null auto_increment primary key,
        name varchar(32) not null,
        email varchar(64) not null,
        extra text,
        index ix_name (name)
    )
    

    创建表+索引

    图片 3图片 4

    create index index_name on table_name(column_name)
    

    创建索引

    图片 5图片 6

    drop index_name on table_name;
    

    删除索引

    图片 7图片 8

    show index from table_name;
    

    查看索引

    注意:对于创建索引时如果是BLOB 和 TEXT 类型,必须指定length。

    create index ix_extra on in1(extra(32));
    

    b、唯一索引

    唯一索引有两个功能:加速查询 和 唯一约束(可含null)

    图片 9图片 10

    create table in1(
        nid int not null auto_increment primary key,
        name varchar(32) not null,
        email varchar(64) not null,
        extra text,
        unique ix_name (name)
    )
    

    创建唯一索引+表

    图片 11图片 12

    create unique index 索引名 on 表名(列名)
    

    创建唯一索引

    图片 13图片 14

    drop unique index 索引名 on 表名
    

    删除唯一索引

    c、主键索引

    主键有两个功能:加速查询 和 唯一约束(不可含null)

    图片 15图片 16

    create table in1(
        nid int not null auto_increment primary key,
        name varchar(32) not null,
        email varchar(64) not null,
        extra text,
        index ix_name (name)
    )
    
    OR
    
    create table in1(
        nid int not null auto_increment,
        name varchar(32) not null,
        email varchar(64) not null,
        extra text,
        primary key(ni1),
        index ix_name (name)
    )
    

    创建表+创建主键

    图片 17图片 18

    alter table 表名 add primary key(列名);
    

    创建主键

    图片 19图片 20

    alter table 表名 drop primary key;
    alter table 表名  modify  列名 int, drop primary key;
    

    删除主键

    d、组合索引

    组合索引是将n个列组合成一个索引

    其应用场景为:频繁的同时使用n列来进行查询,如:where n1 = 'djb' and n2 = 666

    图片 21图片 22

    create table in3(
        nid int not null auto_increment primary key,
        name varchar(32) not null,
        email varchar(64) not null,
        extra text
    )
    

    创建表

    图片 23图片 24

    create index ix_name_email on in3(name,email);
    

    创建索引

    3、相关命令

    - 查看表结构
        desc 表名
    
    - 查看生成表的SQL
        show create table 表名
    
    - 查看索引
        show index from  表名
    
    - 查看执行时间
        set profiling = 1;
        SQL...
        show profiles;
    

    4、使用索引和不使用索引

    由于索引是专门用于加速搜索而生,所以加上索引之后,查询效率会快到飞起来。

    5、正确使用索引

    数据库表中添加索引后确实会让查询速度起飞,但前提必须是正确的使用索引来查询,如果以错误的方式使用,则即使建立索引也会不奏效。
    即使建立索引,索引也不会生效:

    - like '%xx'
        select * from tb1 where name like '%cn';
    - 使用函数
        select * from tb1 where reverse(name) = 'wupeiqi';
    - or
        select * from tb1 where nid = 1 or email = 'seven@live.com';
        特别的:当or条件中有未建立索引的列才失效,以下会走索引
                select * from tb1 where nid = 1 or name = 'seven';
                select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex'
    - 类型不一致
        如果列是字符串类型,传入条件是必须用引号引起来,不然...
        select * from tb1 where name = 999;
    - !=
        select * from tb1 where name != 'alex'
        特别的:如果是主键,则还是会走索引
            select * from tb1 where nid != 123
    - >
        select * from tb1 where name > 'alex'
        特别的:如果是主键或索引是整数类型,则还是会走索引
            select * from tb1 where nid > 123
            select * from tb1 where num > 123
    - order by
        select email from tb1 order by name desc;
        当根据索引排序时候,选择的映射如果不是索引,则不走索引
        特别的:如果对主键排序,则还是走索引:
            select * from tb1 order by nid desc;
    
    - 组合索引最左前缀
        如果组合索引为:(name,email)
        name and email       -- 使用索引
        name                 -- 使用索引
        email                -- 不使用索引
    

    6、其他注意事项

    1 - 避免使用select *
    2 - count(1)或count(列) 代替 count(*)
    3 - 创建表时尽量时 char 代替 varchar
    4 - 表的字段顺序固定长度的字段优先
    5 - 组合索引代替多个单列索引(经常使用多个条件查询时)
    6 - 尽量使用短索引
    7 - 使用连接(JOIN)来代替子查询(Sub-Queries)
    8 - 连表时注意条件类型需一致
    9 - 索引散列值(重复少)不适合建索引,例:性别不适合
    

    7、limit分页

    方案:
    记录当前页最大或最小ID
    1. 页面只有上一页,下一页
    # max_id
    # min_id
    下一页:
    select * from userinfo3 where id > max_id limit 10;
    上一页:
    select * from userinfo3 where id < min_id order by id desc limit 10;
    2. 上一页 192 193  [196]  197  198  199 下一页
    
    select * from userinfo3 where id in (select id from (select id from userinfo3 where id > max_id limit 30) as N order by N.id desc limit 10)
    

    8、执行计划

    explain + 查询SQL - 用于显示SQL执行信息参数,根据参考信息可以进行SQL优化

    mysql> explain select * from tb2;
    +----+-------------+-------+------+---------------+------+---------+------+------+-------+
    | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
    +----+-------------+-------+------+---------------+------+---------+------+------+-------+
    |  1 | SIMPLE      | tb2   | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
    +----+-------------+-------+------+---------------+------+---------+------+------+-------+
    1 row in set (0.00 sec)
    

    9、慢日志查询

    a、配置MySQL自动记录慢日志

    slow_query_log = OFF                            是否开启慢日志记录
    long_query_time = 2                              时间限制,超过此时间,则记录
    slow_query_log_file = /usr/slow.log        日志文件
    log_queries_not_using_indexes = OFF     为使用索引的搜索是否记录

    注:查看当前配置信息:
           show variables like '%query%'
         修改当前配置:
        set global 变量名 = 值

    b、查看MySQL慢日志

    mysqldumpslow -s at -a  /usr/local/var/mysql/MacBook-Pro-3-slow.log

    图片 25图片 26

    """
    --verbose    版本
    --debug      调试
    --help       帮助
    
    -v           版本
    -d           调试模式
    -s ORDER     排序方式
                 what to sort by (al, at, ar, c, l, r, t), 'at' is default
                  al: average lock time
                  ar: average rows sent
                  at: average query time
                   c: count
                   l: lock time
                   r: rows sent
                   t: query time
    -r           反转顺序,默认文件倒序拍。reverse the sort order (largest last instead of first)
    -t NUM       显示前N条just show the top n queries
    -a           不要将SQL中数字转换成N,字符串转换成S。don't abstract all numbers to N and strings to 'S'
    -n NUM       abstract numbers with at least n digits within names
    -g PATTERN   正则匹配;grep: only consider stmts that include this string
    -h HOSTNAME  mysql机器名或者IP;hostname of db server for *-slow.log filename (can be wildcard),
                 default is '*', i.e. match all
    -i NAME      name of server instance (if using mysql.server startup script)
    -l           总时间中不减去锁定时间;don't subtract lock time from total time
    """
    

    View

    认识mysql第三篇,发出的内容适合初学者,如果能持续关注我的博客,可以全面的掌握mysql的常用知识,后续我也会陆续发出python相关的知识,关注我,和我一共进步吧!

    • 1).MyISAM是非事务安全型的,而InnoDB是事务安全型的。
    • 2).MyISAM锁的粒度是表级,而InnoDB支持行级锁定。
    • 3).MyISAM支持全文类型索引,而InnoDB不支持全文索引。
    • 4).MyISAM相对简单,所以在效率上要优于InnoDB,小型应用可以考虑使用MyISAM。
    • 5).MyISAM表是保存成文件的形式,在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦。
    • 6).InnoDB表比MyISAM表更安全,可以在保证数据不会丢失的情况下,切换非事务表到事务表(alter table tablename type=innodb)。

    事务

    1、概述

    MySQL 事务主要用于处理操作量大,复杂度高的数据。比如说,在人员管理系统中,你删除一个人员,你即需要删除人员的基本资料,也要删除和该人员相关的信息,如信箱,文章等等,这样,这些数据库操作语句就构成一个事务,但是一旦有某一个出现错误,即可回滚到原来的状态,从而保证数据库数据完整性。!

    • 在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务
    • 事务处理可以用来维护数据库的完整性,保证成批的SQL语句要么全部执行,要么全部不执行
    • 事务用来管理insert,update,delete语句

    一般来说,事务是必须满足4个条件(ACID): Atomicity(原子性)、Consistency(稳定性)、Isolation(隔离性)、Durability(可靠性)

    • 1、事务的原子性:一组事务,要么成功;要么撤回。
    • 2、稳定性 : 有非法数据(外键约束之类),事务撤回。
    • 3、隔离性:事务独立运行。一个事务处理后的结果,影响了其他事务,那么其他事务会撤回。事务的100%隔离,需要牺牲速度。
    • 4、可靠性:软、硬件崩溃后,InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得, innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里。

    2、事务操作

    • 开启事务 start transaction
    • 回滚事务 rollback
    • 提交事务 commit
    • 保留点    savepoint

      delimiter -- 此条可更改SQL语句结束符 create PROCEDURE p1(

      OUT p_return_code tinyint
      

      ) BEGIN DECLARE exit handler for sqlexception BEGIN

      -- ERROR 
      set p_return_code = 1; 
      rollback; 
      

      END;

      DECLARE exit handler for sqlwarning BEGIN

      -- WARNING 
      set p_return_code = 2; 
      rollback; 
      

      END;

      START TRANSACTION;

      DELETE from tb1;
      insert into tb2(name)values('seven');
      

      COMMIT;

      -- SUCCESS set p_return_code = 0;

      END delimiter ;

    1、SQL查询
      1、执行顺序
        3、select ...聚合函数 from 表名
          1、where ...
          2、group by ...
          4、having ...
          5、order by ...
          6、limit ...
      2、group by
        1、作用 :给查询结果进行分组
        2、示例
          1、查询表中一共有几个国家

    应用场景:

    视图

    视图是一个虚拟表(非真实存在),其本质是【根据SQL语句获取动态的数据集,并为其命名】,用户使用时只需使用【名称】即可获取结果集,并可以将其当作表来使用。

    图片 27图片 28

    SELECT
        *
    FROM
        (
            SELECT
                nid,
                NAME
            FROM
                tb1
            WHERE
                nid > 2
        ) AS A
    WHERE
        A. NAME > 'djb';
    

    临时表搜素

    1、创建视图

    图片 29图片 30

    --格式:CREATE VIEW 视图名称 AS  SQL语句
    CREATE VIEW v1 AS 
    SELET nid, 
        name
    FROM
        A
    WHERE
        nid > 4
    

    创建视图

    2、删除视图

    图片 31图片 32

    --格式:DROP VIEW 视图名称
    
    DROP VIEW v1
    

    删除视图

    3、修改视图

    图片 33图片 34

    -- 格式:ALTER VIEW 视图名称 AS SQL语句
    
    ALTER VIEW v1 AS
    SELET A.nid,
        B. NAME
    FROM
        A
    LEFT JOIN B ON A.id = B.nid
    LEFT JOIN C ON A.id = C.nid
    WHERE
        A.id > 2
    AND C.nid < 5
    

    修改视图

    4、使用视图

    使用视图时,将其当作表进行操作即可,由于视图是虚拟表,所以无法使用其对真实表进行创建、更新和删除操作,仅能做查询用。

    图片 35图片 36

    select * from v1
    

    View Code

          2、计算每个国家的平均攻击力
          select country,avg(gongji) from sanguo
          group by country;
        3、查找所有国家中英雄数量最多的前2名的 国家名称和英雄数量
          select country,count(id) as number from sanguo
          group by country
          order by number desc
          limit 2;
    3、注意
      1、group by之后的字段名必须要为select之后的字段名
      2、如果select之后的字段名和group by之后的字段不一致,则必须对该字段进行聚合处理(聚合函数)
      3、having语句
    1、作用
    对查询的结果进行进一步筛选
    2、示例
      1、找出平均攻击力>105的国家的前2名,显示国家名和平均攻击力
      select country,avg(gongji) as pjgj from sanguo
      group by country
      having pjgj>105
      order by pjgj DESC
      limit 2;
    3、注意
      1、having语句通常和group by语句联合使用,过滤由group by语句返回的记录集
      2、where只能操作表中实际存在字段,having可操作由聚合函数生成的显示列
    4、distinct
      1、作用 :不显示字段重复值
      2、示例
    1、表中都有哪些国家
      select distinct country from sanguo;
    2、计算蜀国一共有多少个英雄
      select count(distinct id) from sanguo
      where country="蜀国";
    3、注意
      1、distinct和from之间所有字段都相同才会去重
      2、distinct不能对任何字段做聚合处理
    5、查询表记录时做数学运算
    1、运算符
      + - * / %
    2、示例
    1、查询时所有英雄攻击力翻倍
    select id,name,gongji*2 as gj from sanguo;

    本文由金沙国际官网发布于数据库,转载请注明出处:MyISAM和InnoDB的主要区别和应用场景

    关键词:

上一篇:慢日志查询,SQLServer数据表用法

下一篇:没有了