• MySQL之索引


    索引:是特殊数据结构,定义在查找时作为查找条件的字段,在MySQL又称为键key,索引通过存储引擎实现。

    优点:

      索引可以降低服务需要扫描的数据量,减少了IO次数

      索引可以帮助服务器避免排序和使用临时表

      索引可以帮助将随机I/O转为顺序I/O

    缺点:

      占用额外空间,影响插入速度

     

    管理索引

      创建索引:

        1、CREATE [UNIQUE] INDEX index_name ON tbl_name (index_col_name[(length)],...);

        2、ALTER TABLE tbl_name ADD INDEX index_name(index_col_name);

        3、创建表的时候直接指定索引

          CREATE TABLE mytable( 

          ID INT NOT NULL,  

          username VARCHAR(16) NOT NULL, 

          INDEX [indexName] (username(length)) 

          ); 

      删除索引:

        DROP INDEX index_name ON tbl_name;

        ALTER TABLE tbl_name DROP INDEX index_name(index_col_name);

      查看索引:

        SHOW INDEXES FROM [db_name.]tbl_name;

      优化表空间:(清理删除的数据所占用的空间)

        OPTIMIZE TABLE tb_name;

      查看索引的使用情况,使用率

        SET GLOBAL userstat=1;(修改/etc/my.cnf)

        SHOW INDEX_STATISTICS;

     

    分析索引的有效性

      通过EXPLAIN来分析索引的有效性

      EXPLAIN SELECT clause

        获取查询执行计划信息,用来查看查询优化器如何执行查询

      输出信息说明:

        参考 https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

    mysql > EXPLAIN SELECT * FROM students WHERE  name='tom' ;   
    +------+-------------+----------+------+---------------+--------------+---------+-------+------+-----------------------+
    | id   | select_type | table    | type | possible_keys | key          | key_len | ref   | rows | Extra                 |
    +------+-------------+----------+------+---------------+--------------+---------+-------+------+-----------------------+
    |    1 | SIMPLE      | students | ref  | idx_name_age  | idx_name_age | 152     | const |    2 | Using index condition |
    +------+-------------+----------+------+---------------+--------------+---------+-------+------+-----------------------+
    1 row in set (0.00 sec)

    EXPLAIN语句各个字段介绍

      id: 当前查询语句中,每个SELECT语句的编号

      select_type

        简单查询为SIMPLE

        复杂查询:

          SUBQUERY 简单子查询

          PRIMARY 最外面的SELECT

          DERIVED 用于FROM中的子查询

          UNION UNION语句的第一个之后的SELECT语句

          UNION RESULT 匿名临时表

      table:SELECT语句关联到的表

      type:关联类型或访问类型,即MySQL决定的如何去查询表中的行的方式,以下顺序,性能从低到高

        ALL: 全表扫描

        index:根据索引的次序进行全表扫描;如果在Extra列出现“Using index”表示了使用覆盖索引,而非全表扫描

        range:有范围限制的根据索引实现范围扫描;扫描位置始于索引中的某一点,结束于另一点

        ref: 根据索引返回表中匹配某单个值的所有行

        eq_ref:仅返回一个行,但与需要额外与某个参考值做比较

        const, system: 直接返回单个行

      possible_keys:查询可能会用到的索引

      key:查询中使用到的索引

      key_len: 在索引使用的字节数

      ref: 在利用key字段所表示的索引完成查询时所用的列或某常量值

      rows:MySQL估计为找所有的目标行而需要读取的行数

      Extra:额外信息

        Using index:MySQL将会使用覆盖索引,以避免访问表

        Using where:MySQL服务器将在存储引擎检索后,再进行一次过滤

        Using temporary:MySQL对结果排序时会使用临时表

        Using filesort:对结果使用一个外部索引排序

  • 相关阅读:
    开发一个基于 Android系统车载智能APP
    Xilium.CefGlue利用XHR实现Js调用c#方法
    WPF杂难解 奇怪的DisconnectedItem
    (转)获取安卓iOS上的微信聊天记录、通过Metasploit控制安卓
    mac 安装npm
    mac安装Homebrew
    关于面试,我也有说的
    【分享】小工具大智慧之Sql执行工具
    领域模型中分散的事务如何集中统一处理(C#解决方案)
    小程序大智慧,sqlserver 注释提取工具
  • 原文地址:https://www.cnblogs.com/ysuwangqiang/p/11813710.html
Copyright © 2020-2023  润新知