精华内容
下载资源
问答
  • 索引是关系数据库用于存放每一条记录的一种对象,主要目的是加快数据的读取速度和完整...下面给出建立索引的方法和要点。§3.5.1 建立索引1. CREATE INDEX命令语法:CREATE INDEXCREATE [unique] INDEX [user.]inde...

    索引是关系数据库中用于存放每一条记录的一种对象,主要目的是加快数据的读取速度和完整性检查。建立索引是一项技术性要求高的工作。一般在数据库设计阶段的与数据库结构一道考虑。应用系统的性能直接与索引的合理直接有关。下面给出建立索引的方法和要点。

    §3.5.1 建立索引

    1. CREATE INDEX命令语法:

    CREATE INDEX

    CREATE [unique] INDEX [user.]index

    ON [user.]table (column [ASC | DESC] [,column

    [ASC | DESC] ] ... )

    [CLUSTER [scheam.]cluster]

    [INITRANS n]

    [MAXTRANS n]

    [PCTFREE n]

    [STORAGE storage]

    [TABLESPACE tablespace]

    [NO SORT]

    Advanced

    其中:

    schema ORACLE模式,缺省即为当前帐户

    index 索引名

    table 创建索引的基表名

    column 基表中的列名,一个索引最多有16列,long列、long raw

    列不能建索引列

    DESC、ASC 缺省为ASC即升序排序

    CLUSTER 指定一个聚簇(Hash cluster不能建索引)

    INITRANS、MAXTRANS 指定初始和最大事务入口数

    Tablespace 表空间名

    STORAGE 存储参数,同create table 中的storage.

    PCTFREE 索引数据块空闲空间的百分比(不能指定pctused)

    NOSORT 不(能)排序(存储时就已按升序,所以指出不再排序)

    2.建立索引的目的:

    建立索引的目的是:

    l 提高对表的查询速度;

    l 对表有关列的取值进行检查。

    但是,对表进行insert,update,delete处理时,由于要表的存放位置记录到索引项中而会降低一些速度。

    注意:一个基表不能建太多的索引;

    空值不能被索引

    只有唯一索引才真正提高速度,一般的索引只能提高30%左右。

    Create index ename_in on emp (ename,sal);

    取消

    评论

    展开全文
  • 索引是由数据库表一列或者多列组合而成,其作用是提高对表数据的查询速度,建立索引查询速度有数量级提升 ; 类似于图书的目录,方便快速定位,寻找指定的内容; 2.索引的优缺点 优点:提高查询数据的速度;...

    一、索引

    1.索引定义

      索引是由数据库表中一列或者多列组合而成,其作用是提高对表中数据的查询速度,建立索引查询速度有数量级提升 ;

    类似于图书的目录,方便快速定位,寻找指定的内容;

    2.索引的优缺点

    • 优点:提高查询数据的速度;
    • 缺点:创建和维护索引的时间增加了;

    3.索引分类

    1,普通索引

    这类索引可以创建在任何数据类型中;

    2,唯一性索引

    使用 UNIQUE 参数可以设置,在创建唯一性索引时,限制该索引的值必须是唯一的;

    3,全文索引

    使用 FULLTEXT 参数可以设置,全文索引只能创建在 CHAR,VARCHAR,TEXT 类型的字段上。主要作用

    就是提高查询较大字符串类型的速度;只有 MyISAM 引擎支持该索引,Mysql 默认引擎不支持;

    4,单列索引

    在表中可以给单个字段创建索引,单列索引可以是普通索引,也可以是唯一性索引,还可以是全文索引;

    5,多列索引

    多列索引是在表的多个字段上创建一个索引;

    6,空间索引

    使用 SPATIAL 参数可以设置空间索引。空间索引只能建立在空间数据类型上,这样可以提高系统获取空间数

    据的效率;只有 MyISAM 引擎支持该索引,Mysql 默认引擎不支持;

    4.创建索引

    • 创建表的时候创建索引
    CREATE TABLE 表名 (属性名 数据类型 [完整性约束条件],
    
                                  属性名 数据类型 [完整性约束条件],
    
                                  ....
    
                                  属性名 数据类型
    
                                  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX| KEY
    
                                  [别名] (属性名 1 [(长度)] [ASC | DESC])
    
    );
    • 1,创建普通索引
    CREATE TABLE mytable(
    
    id INT PRIMARY KEY AUTO_INCREMENT,
    
    bookId INT,
    
    bookName VARCHAR(20) NOT NULL,
    
    price DECIMAL(6,2),
    
    INDEX(bookId)   //创建普通索引
    
    //UNIQUE INDEX (bookId)   //创建唯一性索引
    
    //UNIQUE INDEX bokId(bookId)   //创建唯一性索引并起别名为bokId
    
    //UNIQUE INDEX bokId(bookId,id)   //创建多列索引并起别名为bokId
    
    );
    • 2,创建唯一性索引
    • 3,创建全文索引(mysql默认搜索引擎不支持)
    • 4,创建单列索引 
    • 5,创建多列索引
    • 6,创建空间索引(不支持)
    • 在已经存在的表上创建索引
    CREATE [ UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名  ON 表名  (属性名 [(长度)] [ ASC | DESC]);

    如:

    CREATE INDEX bookId ON mytable(bookId); //创建普通索引
    CREATE UNIQUE INDEX bookId ON mytable(bookId); //创建唯一索引
    CREATE UNIQUE INDEX bookId_price ON mytable(bookId,price); //创建多列索引
    • 用 ALTER TABLE 语句来创建索引
    ALTER TABLE 表名 ADD [ UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (属性名 [(长度)] [ ASC | DESC]);
    
    如:
    ALTER TABLE mytable ADD INDEX index_bookId(bookId); //创建普通索引
    ALTER TABLE mytable ADD UNIQUE INDEX index_bookId(bookId); //创建唯一索引
    ALTER TABLE mytable ADD UNIQUE INDEX index_bookId_price(bookId,price); //创建多列索引

    5.删除索引

    DROP INDEX 索引名 ON 表名 ; 
    如:
    drop index index_bookId_price on mytable;

    二、视图

    第一节:视图的引入

    • 1,视图是一种虚拟的表,是从数据库中一个或者多个表中导出来的表。
    • 2,数据库中只存放了视图的定义,而并没有存放视图中的数据,这些数据存放在原来的表中。
    • 3,使用视图查询数据时,数据库系统会从原来的表中取出对应的数据。

    第二节:视图的作用

    • 1,使操作简便化;
    • 2,增加数据的安全性;
    • 3,提高表的逻辑独立性;

    第三节:创建视图

    CREATE [ ALGORITHM ={ UNDEFIEND | MERGE | TEMPTABLE }]
    
                   VIEW 视图名 [ ( 属性清单) ]
    
                   AS SELECT 语句  [ WITH [  CASCADED | LOCAL ] CHECK OPTION ];
    • ALGORITHM 是可选参数,表示视图选择的算法;
    • “属性清单”是可选参数,其指定了视图中各种属性的名词,默认情况下与 SELECT 语句中查询的属性相同;
    • SELECT 语句参数是一个完整的查询语句,标识从某个表查出某些满足条件的记录,将这些记录导入视图中;
    • WITH CHECK OPTION 是可选参数,表似乎更新视图时要保证在该视图的权限范围之内;
    • ALGORITHM 包括 3 个选项 UNDEFINED、MERGE 和 TEMPTABLE。其中,
    • UNDEFINED 选项表示 MySQL 将 自动选择所要使用的算法;
    • MERGE 选项表示将使用视图的语句与视图定义合并起来,使得视图定义的某一部分 取代语句的对应部分;
    • TEMPTABLE 选项表示将视图的结果存入临时表,然后使用临时表执行语句;
    • CASCADED 是可选参数,表示更新视图时要满足所有相关视图和表的条件,该参数为默认值;
    • LOCAL 表示更新视图时,要 满足该视图本身的定义条件即可;

    如:

    创建视图:
    create view v1 as select * from mytable where bookId >30;
    查询使用:
    select * from v1;

     

    第四节:查看视图

    • 4.1 DESCRIBE 视图名称; //查看视图字段属性信息
    • 4.2 SHOW TABLE STATUS LIKE ‘视图名称’;// 查看视图基本信息,因为视图是虚表,故查询结果字段都为空;此sql也可以查询普通表的基本信息;后面没有like指定视图时表示查询库中所有表和视图的基本信息
    • 4.3 SHOW CREATE VIEW 视图名称;//查看视图详细信息
    • 4.3 在 views 表中查看视图详细信息

    第五节:修改视图

    • 5.1 CREATE  OR  REPLACE  VIEW 语句修改视图
    CREATE  OR  REPLACE  [ ALGORITHM ={ UNDEFINED | MERGE | TEMPTABLE }]
    
                                             VIEW 视图名 [( 属性清单 )]
    
                                             AS SELECT 语句  [ WITH [ CASCADED | LOCAL ] CHECK OPTION ];
    create or replace  view v1 as select * from mytable where bookId < 30;
    • 5.2 ALTER 语句修改视图
    ALTER  [ ALGORITHM ={ UNDEFINED | MERGE | TEMPTABLE }]
    
                 VIEW 视图名 [( 属性清单 )]
    
                 AS SELECT 语句
    
                 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ];

     

    alter view v1 as select * from mytable;

    第六节:更新视图

    更新视图是指通过视图来插入(INSERT)、更新(UPDATE)和删除(DELETE)表中的数据。因为视图是一个虚

    拟的表,其中没有数据。通过视图更新时,都是转换基本表来更新。更新视图时,只能更新权限范围内的数据。

    超出了范围,就不能更新。

    6.1 插入(INSERT)     

    CREATE VIEW v1(b,p) AS select bookId,bookName from mytable;
    INSERT into v1 values(44,'think in java');
    --插入视图,再次查看视图v1时会看到插入的数据,视图操作实际是对表的操作,可以看到mytable表中增加了一条数据,但是其他没有插入的字段则是显示默认值

    6.2 更新(UPDATE)

    update v1 set b=8,p='设计模式' where b=44

    6.3 删除(DELETE)

    delete from v1 where b=44;

    第七节:删除视图

    删除视图是指删除数据库中已存在的视图。删除视图时,只能删除视图的定义,不会删除数据;

    DROP VIEW [ IF EXISTS ] 视图名列表 [ RESTRICT | CASCADE ]
    
    如: DROP VIEW  IF EXISTS  v1;

    三、触发器

    第一节:触发器的引入

    触发器(TRIGGER)是由事件来触发某个操作。这些事件包括 INSERT 语句、UPDATE 语句和 DELETE 语句。

    当数据库系统执行这些事件时,就会激活触发器执行相应的操作。

    第二节:创建与使用触发器

    2.1 创建只有一个执行语句的触发器

    CREATE TRIGGER 触发器名  BEFORE | AFTER 触发事件
    
                                  ON 表名  FOR EACH ROW 事件发生时要执行的语句
    
    如:CREATE TRIGGER trig_book AFTER INSERT
    
                 ON t_book FOR EACH ROW
    
                 UPDATE t_bookType SET bookNum=bookNum+1 WHERE new.bookTypeId=t_booktype.id;
    • 表示t_book表中国执行插入操作时触发t_bookType的更新操作,将bookNum加1
    • 注:new是中间量,表示插入(更新)时的那条数据,如果是删除时用old,表示删除的那条数据

    2.2 创建有多个执行语句的触发器

    CREATE TRIGGER 触发器名  BEFORE | AFTER 触发事件
    
                                  ON 表名  FOR  EACH  ROW  
    
                                  BEGIN  
    
                                       执行语句列表  
    
                                  END

    如:

    DELIMITER |
    
    CREATE TRIGGER trig_book2 AFTER DELETE
    
        ON t_book FOR EACH ROW
    
        BEGIN
    
           UPDATE t_bookType SET bookNum=bookNum-1 WHERE old.bookTypeId=t_booktype.id;
    
           INSERT INTO t_log VALUES(NULL,NOW(),'在book表里删除了一条数据');
    
           DELETE FROM t_test WHERE old.bookTypeId=t_test.id;
    
        END
    
    |
    
    DELIMITER ;
    • 注:DELIMITER | 包裹起来避免触发多条sql时遇到分号中断执行的情况

    第三节:查看触发器

    3.1 SHOW TRIGGERS; 语句查看触发器信息

    3.2 在 triggers 表中查看触发器信息

    第四节:删除触发器

    DROP TRIGGER 触发器名;

    展开全文
  • mysql索引篇(一)mysql索引sql执行过程索引类型1.主键索引2.非主键索引InnoDB索引结构/方法1.hash2.二叉树3.二叉平衡树4.B树5.B+树...命令分发器 建立连接后,mysql收到SQL语句,命令分发器判断sql是查询还是更新; 查

    mysql索引

    sql执行过程

    首先我们看一下一条查询sql在mysql中的大致流程
    一条查询sql的旅程

    首先简单阐述一下前置处理的流程

    • 开始 客户端向mysql服务端发送请求;
    • 连接器 请求到达连接器,连接器进行资源分配和权限验证;
    • 命令分发器 建立连接后,mysql收到SQL语句,命令分发器判断sql是查询还是更新;
    • 查询缓存 检查是否开启缓存验证,如果开启,检查是否存在缓存,存在的话就直接返回结果,不存在的话继续向下执行(这里的缓存是用hash的,所以任何sql语句的变化,即便是空格也会导致缓存不命中);
    • 分析器 语法分析器,检查sql语法;
    • 预处理器 检查数据库表、列、别名是否存在或合法;
    • 优化器 在这一步生成执行计划,即选什么索引等,可以用explain查看;
    • 执行器 执行sql;

    本篇文章是了解索引的,所以重点是右边泳池的后置处理内容

    • 如果不启用索引
    Created with Raphaël 2.2.0开始遍历数据表找到记录返回记录结束
    • 启用聚集索引
    Created with Raphaël 2.2.0开始查询聚集索引直接得到记录返回记录结束
    • 启用非聚集索引
    Created with Raphaël 2.2.0开始查询非聚集索引得到主键的值(即聚集索引的key)得到记录返回记录结束

    索引类型

    1.主键索引

    结构:B+树结构
    特点:中间节点保存的是主键的值对于InnoDB引擎,叶子节点保存的是主键对应的那一行数据;对于MyIsam引擎,叶子节点保存的是数据对应的地址。上述差异是因为MyIsam引擎中数据和索引是分开存储的,而InnoDB是放一起的。以下分别是MyIsam和InnoDB的主键索引结构
    myisam引擎的主键索引
    InnoDB的主键缩影

    2.非主键索引

    结构:多样化
    特点:对于MyIsam引擎来说,主键索引和非主键索引是没区别的;对于InnoDB引擎来说,非主键索引中存储的不再是数据了,而是主键的值。所以如果通过非主键索引去查询其他字段的值,比直接通过主键索引查询多一次回表操作,要操作两颗索引树
    在这里插入图片描述

    InnoDB索引结构/方法

    1.hash

    特点:key-value形式的索引结构
    优点:速度快、时间复杂度底 O(1)
    缺点:不适合范围查询,不满足一些的业务需求

    2.二叉树

    特点:左子树小于根节点,右子树大于根节点
    优点:适合范围查询、时间复杂度 O(log2n),取决于树的高度
    缺点:在一定情况下,会退化成链表结构
    理想二叉树
    退化:
    退化成链表的二叉树

    3.二叉平衡树

    特点:相较于二叉树,增加了自平衡的效果,左右子树高度相差最大不超过1
    优点:避免了二叉树退化成链表的情况
    缺点:如果数据量多,插入数据的时候平衡树也是一个很耗时的操作,而且树的高度也不会很低,每查一个节点就是一次IO

    4.B树

    特点:相当于多叉树,保留了右>左
    优点:空间利用率大大提高(假若,p1、p2为指针占4byte,key为bigint占8byte,一个节点的大小很明显是有限的,但是从磁盘读取一页的大小是16K,所以二叉树的空间利用率相较于多叉树是很低的)
    缺点
    缺点:做范围查询效率较低
    在这里插入图片描述

    5.B+树

    特点:相较于B树,它的数据都存在了子节点,而且子节点之间做成了双向链表,左边<key<=右边
    优点:改进了B+树范围查询效率差的不足,子节点之间的双向指针减少了范围查询带来的磁盘IO
    在这里插入图片描述

    6.B树和B+树的对比

    B树的索引项分布在整棵树,B+树的最底层叶子结点包含所有的索引项,所以如果要查找上图中key为17的数据。对于B树来说,查找到磁盘块2就可以返回数据了,对于B+树则需要找到磁盘块6中。
    下面详细说一下,如果要查询值为15(等值查询)的数据,B+树的查询路径

    • 第一次磁盘IO,将磁盘块1加载到内存中。在内存中从头遍历比较,发现15<28,磁盘根据p1寻址到磁盘块2
    • 第二次磁盘IO,将磁盘块2加载到内存中。在内存中从头遍历比较,发现10<15<17,磁盘根据p2寻址到磁盘块5
    • 第三次磁盘IO,将磁盘块5加载到内存中。在内存中从头遍历比较,发现15=15,取出data,如果data存储的是行记录,则取出data,查询结束。如果存储的是磁盘地址,说明不是最后节点,里面存储的还是磁盘地址,需要继续向下遍历
      如果是查28的话
    • 第一次磁盘IO,将磁盘块1加载到内存中。在内存中从头遍历比较,发现28=28,磁盘根据p2寻址到磁盘块3(如果是B树,在这一步已经返回结果了)
    • 第二次磁盘IO,将磁盘块3加载到内存中。在内存中从头遍历比较,发现28<36,磁盘根据p1寻址到磁盘块7
    • 第三次磁盘IO,将磁盘块7加载到内存中。在内存中从头遍历比较,发现28=28,取出data,如果data存储的是行记录,则取出data,查询结束。如果存储的是磁盘地址,说明不是最后节点,里面存储的还是磁盘地址,需要继续向下遍历
      对于等值查询,只有一个B树存在中间返回的情况的区别。但是对于范围查询的话,B+树就比B树的性能好太多了,因为B+树是双向链表,叶子结点是有序的。所以mysql索引都是B+树。

    索引的使用

    组合索引

    哪些字段创建:我们可以把常用的字段设置成组合索引
    怎么创建:区分度高的字段可以放在最左边
    为什么要创建:如果创建了一个{id,name,code}的组合索引,相当于创建了{id},{id,name},{id,name,code}三种索引,节约了磁盘空间,减少了插入数据的维护成本
    之后怎么使用:注意最左匹配原则,按顺序来,因为组合索引查找的时候就是按序来的。也就是说索引创建是{id,name,code},使用必须也是这个顺序,不按顺序的话就不会走索引

    覆盖索引

    哪怕使用了组合索引,仍然少不了找到主键再去回表,还是要遍历两颗索引树。
    仍然以id,code,name为例,这种组合不光经常出现在where、order by中,而且经常出现在select后面,所以对于以下例子,我们可以建一个{animal_type,sex,animal_species}索引,这样在遍历组合索引树的时候,我们所需要的数据也都正好是索引的key,不需要再去遍历主键索引树了。
    这种叫做覆盖索引,减少回表操作

    // 查询动物类别、性别、种类
    select animal_type,sex,animal_species from animal where animal_type = '哺乳动物' and sex= '雄性' and animal_species ='虎类';
    

    in索引

    当表中数据量小,而且in中的数据包含了大部分数据时,是不会走索引的,因为此时全表扫描和索引的效率差不多
    当表中数据量大的话,是会走索引的。

    展开全文
  • 通过本章内容,你将学会MySQL性能下降的原因,索引的简介,索引创建的原则,explain命令的使用,以及explain输出字段的意义。助你了解索引,分析索引,使用索引,从而写出更高性能的sql语句。还等啥子?撸起袖子...

    为什么你写的sql查询慢?为什么你建的索引常失效?通过本章内容,你将学会MySQL性能下降的原因,索引的简介,索引创建的原则,explain命令的使用,以及explain输出字段的意义。助你了解索引,分析索引,使用索引,从而写出更高性能的sql语句。还在等啥子?撸起袖子就是干!

    案例分析

    我们先简单了解一下非关系型数据库关系型数据库的区别。
    MongoDB是NoSQL中的一种。NoSQL的全称是Not only SQL,非关系型数据库。它的特点是性能高扩张性强模式灵活,在高并发场景表现得尤为突出。但目前它还只是关系型数据库的补充,它在数据的一致性,数据的安全性,查询的复杂性问题上和关系型数据库还存在一定差距。

    MySQL是关系性数据库中的一种,查询功能强数据一致性高数据安全性高支持二级索引。但性能方面稍逊与MongoDB,特别是百万级别以上的数据,很容易出现查询慢的现象。这时候需要分析查询慢的原因,一般情况下是程序员sql写的烂,或者是没有键索引,或者是索引失效等原因导致的。

    公司ERP系统数据库主要是MongoDB(最接近关系型数据的NoSQL),其次是Redis,MySQL只占很少的部分。现在又重新使用MySQL,归功于阿里巴巴的奇门系统和聚石塔系统。考虑到订单数量已经是百万级以上,对MySQL的性能分析也就显得格外重要。

    我们先通过两个简单的例子来入门。后面会详细介绍各个参数的作用和意义。

    场景一:订单导入,通过交易号避免重复导单

    业务逻辑:订单导入时,为了避免重复导单,一般会通过交易号去数据库中查询,判断该订单是否已经存在。

    最基础的sql语句

    mysql> select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
    +-------+--------------------+-------+------+----------+--------------+----------+------------------+-------------+-------------+------------+---------------------+
    | id    | transaction_id     | gross | net  | stock_id | order_status | descript | finance_descript | create_type | order_level | input_user | input_date          |
    +-------+--------------------+-------+------+----------+--------------+----------+------------------+-------------+-------------+------------+---------------------+
    | 10000 | 81X97310V32236260E |   6.6 | 6.13 |        1 |           10 | ok       | ok               | auto        |           1 | itdragon   | 2017-08-18 17:01:49 |
    +-------+--------------------+-------+------+----------+--------------+----------+------------------+-------------+-------------+------------+---------------------+
    
    mysql> explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
    +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
    | id | select_type | table               | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | itdragon_order_list | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
    +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+

    查询的本身没有任何问题,在线下的测试环境也没有任何问题。可是,功能一旦上线,查询慢的问题就迎面而来。几百上千万的订单,用全表扫描?啊?哼!
    怎么知道该sql是全表扫描呢?通过explain命令可以清楚MySQL是如何处理sql语句的。打印的内容分别表示:

    1. id : 查询序列号为1。
    2. select_type : 查询类型是简单查询,简单的select语句没有union和子查询。
    3. table : 表是 itdragon_order_list。
    4. partitions : 没有分区。
    5. type : 连接类型,all表示采用全表扫描的方式。
    6. possible_keys : 可能用到索引为null。
    7. key : 实际用到索引是null。
    8. key_len : 索引长度当然也是null。
    9. ref : 没有哪个列或者参数和key一起被使用。
    10. Extra : 使用了where查询。

    因为数据库中只有三条数据,所以rows和filtered的信息作用不大。这里需要重点了解的是type为ALL,全表扫描的性能是最差的,假设数据库中有几百万条数据,在没有索引的帮助下会异常卡顿。

    初步优化:为transaction_id创建索引

    mysql> create unique index idx_order_transaID on itdragon_order_list (transaction_id);
    mysql> explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
    +----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------+
    | id | select_type | table               | partitions | type  | possible_keys      | key                | key_len | ref   | rows | filtered | Extra |
    +----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------+
    |  1 | SIMPLE      | itdragon_order_list | NULL       | const | idx_order_transaID | idx_order_transaID | 453     | const |    1 |      100 | NULL  |
    +----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------+

    这里创建的索引是唯一索引,而非普通索引。
    唯一索引打印的type值是const。表示通过索引一次就可以找到。即找到值就结束扫描返回查询结果。
    普通索引打印的type值是ref。表示非唯一性索引扫描。找到值还要继续扫描,直到将索引文件扫描完为止。(这里没有贴出代码)
    显而易见,const的性能要远高于ref。并且根据业务逻辑来判断,创建唯一索引是合情合理的。

    再次优化:覆盖索引

    mysql> explain select transaction_id from itdragon_order_list where transaction_id = "81X97310V32236260E";
    +----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------------+
    | id | select_type | table               | partitions | type  | possible_keys      | key                | key_len | ref   | rows | filtered | Extra       |
    +----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------------+
    |  1 | SIMPLE      | itdragon_order_list | NULL       | const | idx_order_transaID | idx_order_transaID | 453     | const |    1 |      100 | Using index |
    +----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------------+

    这里将select * from 改为了 select transaction_id from
    Extra 显示 Using index,表示该查询使用了覆盖索引,这是一个非常好的消息,说明该sql语句的性能很好。若提示的是Using filesort(使用内部排序)和Using temporary(使用临时表)则表明该sql需要立即优化了。
    根据业务逻辑来的,查询结构返回transaction_id 是可以满足业务逻辑要求的。

    场景二,订单管理页面,通过订单级别和订单录入时间排序

    业务逻辑:优先处理订单级别高,录入时间长的订单。
    既然是排序,首先想到的应该是order by, 还有一个可怕的 Using filesort 等着你。

    最基础的sql语句

    mysql> explain select * from itdragon_order_list order by order_level,input_date;
    +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+
    | id | select_type | table               | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
    +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+
    |  1 | SIMPLE      | itdragon_order_list | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |      100 | Using filesort |
    +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+

    首先,采用全表扫描就不合理,还使用了文件排序Using filesort,更加拖慢了性能。
    MySQL在4.1版本之前文件排序是采用双路排序的算法,由于两次扫描磁盘,I/O耗时太长。后优化成单路排序算法。其本质就是用空间换时间,但如果数据量太大,buffer的空间不足,会导致多次I/O的情况。其效果反而更差。与其找运维同事修改MySQL配置,还不如自己乖乖地建索引。

    初步优化:为order_level,input_date 创建复合索引

    mysql> create index idx_order_levelDate on itdragon_order_list (order_level,input_date);
    mysql> explain select * from itdragon_order_list order by order_level,input_date;
    +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+
    | id | select_type | table               | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
    +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+
    |  1 | SIMPLE      | itdragon_order_list | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |      100 | Using filesort |
    +----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+

    创建复合索引后你会惊奇的发现,和没创建索引一样???都是全表扫描,都用到了文件排序。是索引失效?还是索引创建失败?我们试着看看下面打印情况

    mysql> explain select order_level,input_date from itdragon_order_list order by order_level,input_date;
    +----+-------------+---------------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
    | id | select_type | table               | partitions | type  | possible_keys | key                 | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+---------------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | itdragon_order_list | NULL       | index | NULL          | idx_order_levelDate | 68      | NULL |    3 |      100 | Using index |
    +----+-------------+---------------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+

    select * from换成了select order_level,input_date from后。type从all升级为index,表示(full index scan)全索引文件扫描,Extra也显示使用了覆盖索引。可是不对啊!!!!检索虽然快了,但返回的内容只有order_level和input_date 两个字段,让业务同事怎么用?难道把每个字段都建一个复合索引?

    MySQL没有这么笨,可以使用force index 强制指定索引。在原来的sql语句上修改force index(idx_order_levelDate)即可。

    mysql> explain select * from itdragon_order_list force index(idx_order_levelDate) order by order_level,input_date;
    +----+-------------+---------------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------+
    | id | select_type | table               | partitions | type  | possible_keys | key                 | key_len | ref  | rows | filtered | Extra |
    +----+-------------+---------------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------+
    |  1 | SIMPLE      | itdragon_order_list | NULL       | index | NULL          | idx_order_levelDate | 68      | NULL |    3 |      100 | NULL  |
    +----+-------------+---------------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------+

    再次优化:订单级别真的要排序么?

    其实给订单级别排序意义并不大,给订单级别添加索引意义也不大。因为order_level的值可能只有,低,中,高,加急,这四种。对于这种重复且分布平均的字段,排序和加索引的作用不大。
    我们能否先固定 order_level 的值,然后再给 input_date 排序?如果查询效果明显,是可以推荐业务同事使用该查询方式。

    mysql> explain select * from itdragon_order_list where order_level=3 order by input_date;
    +----+-------------+---------------------+------------+------+---------------------+---------------------+---------+-------+------+----------+-----------------------+
    | id | select_type | table               | partitions | type | possible_keys       | key                 | key_len | ref   | rows | filtered | Extra                 |
    +----+-------------+---------------------+------------+------+---------------------+---------------------+---------+-------+------+----------+-----------------------+
    |  1 | SIMPLE      | itdragon_order_list | NULL       | ref  | idx_order_levelDate | idx_order_levelDate | 5       | const |    1 |      100 | Using index condition |
    +----+-------------+---------------------+------------+------+---------------------+---------------------+---------+-------+------+----------+-----------------------+

    和之前的sql比起来,type从index 升级为 ref(非唯一性索引扫描)。索引的长度从68变成了5,说明只用了一个索引。ref也是一个常量。Extra 为Using index condition 表示自动根据临界值,选择索引扫描还是全表扫描。总的来说性能远胜于之前的sql。

    上面两个案例只是快速入门,我们需严记一点:优化是基于业务逻辑来的。绝对不能为了优化而擅自修改业务逻辑。如果能修改当然是最好的。

    索引简介

    官方定义:索引(Index) 是帮助MySQL高效获取数据的数据结构。
    大家一定很好奇,索引为什么是一种数据结构,它又是怎么提高查询的速度?我们拿最常用的二叉树来分析索引的工作原理。看下面的图片:

    9c149e6b8e8d1ed787adc49c47734df9.png

    创建索引的优势:

    1 提高数据的检索速度,降低数据库IO成本:使用索引的意义就是通过缩小表中需要查询的记录的数目从而加快搜索的速度。

    2 降低数据排序的成本,降低CPU消耗:索引之所以查的快,是因为先将数据排好序,若该字段正好需要排序,则真好降低了排序的成本。

    创建索引的劣势:

    1 占用存储空间:索引实际上也是一张表,记录了主键与索引字段,一般以索引文件的形式存储在磁盘上。

    2 降低更新表的速度:表的数据发生了变化,对应的索引也需要一起变更,从而减低的更新速度。否则索引指向的物理数据可能不对,这也是索引失效的原因之一。

    3 优质索引创建难:索引的创建并非一日之功,也并非一直不变。需要频繁根据用户的行为和具体的业务逻辑去创建最佳的索引。

    索引分类

    我们常说的索引一般指的是BTree(多路搜索树)结构组织的索引。其中还有聚合索引,次要索引,复合索引,前缀索引,唯一索引,统称索引,当然除了B+树外,还有哈希索引(hash index)等。

    单值索引:一个索引只包含单个列,一个表可以有多个单列索引唯一索引:索引列的值必须唯一,但允许有空值复合索引:一个索引包含多个列,实际开发中推荐使用
    实际开发中推荐使用复合索引,并且单表创建的索引个数建议不要超过五个

    基本语法:
    创建:

    create [unique] index indexName on tableName (columnName...)
    alter tableName add [unique] index [indexName] on (columnName...)

    删除:

    drop index [indexName] on tableName

    查看:

    show index from tableName

    哪些情况需要建索引:
    1 主键,唯一索引
    2 经常用作查询条件的字段需要创建索引
    3 经常需要排序、分组和统计的字段需要建立索引
    4 查询中与其他表关联的字段,外键关系建立索引

    哪些情况不要建索引:
    1 表的记录太少,百万级以下的数据不需要创建索引
    2 经常增删改的表不需要创建索引
    3 数据重复且分布平均的字段不需要创建索引,如 true,false 之类。
    4 频发更新的字段不适合创建索引
    5 where条件里用不到的字段不需要创建索引

    性能分析

    MySQL 自身瓶颈

    MySQL自身参见的性能问题有磁盘空间不足,磁盘I/O太大,服务器硬件性能低。
    1 CPU:CPU 在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
    2 IO:磁盘I/O 瓶颈发生在装入数据远大于内存容量的时候
    3 服务器硬件的性能瓶颈:top,free,iostat 和 vmstat来查看系统的性能状态

    explain 分析sql语句

    使用explain关键字可以模拟优化器执行sql查询语句,从而得知MySQL 是如何处理sql语句。

    +----+-------------+-------+------------+------+---------------+-----+---------+------+------+----------+-------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref  | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+-----+---------+------+------+----------+-------+

    id

    select 查询的序列号,包含一组可以重复的数字,表示查询中执行sql语句的顺序。一般有三种情况:

    第一种:id全部相同,sql的执行顺序是由上至下;

    第二种:id全部不同,sql的执行顺序是根据id大的优先执行;

    第三种:id既存在相同,又存在不同的。先根据id大的优先执行,再根据相同id从上至下的执行。

    select_type

    select 查询的类型,主要是用于区别普通查询,联合查询,嵌套的复杂查询

    simple:简单的select 查询,查询中不包含子查询或者union

    primary:查询中若包含任何复杂的子查询,最外层查询则被标记为primary

    subquery:在select或where 列表中包含了子查询

    derived:在from列表中包含的子查询被标记为derived(衍生)MySQL会递归执行这些子查询,把结果放在临时表里。

    union:若第二个select出现在union之后,则被标记为union,若union包含在from子句的子查询中,外层select将被标记为:derived

    union result:从union表获取结果的select

    partitions

    表所使用的分区,如果要统计十年公司订单的金额,可以把数据分为十个区,每一年代表一个区。这样可以大大的提高查询效率。

    type

    这是一个非常重要的参数,连接类型,常见的有:all , index , range , ref , eq_ref , const , system , null 八个级别。

    性能从最优到最差的排序:system > const > eq_ref > ref > range > index > all

    对java程序员来说,若保证查询至少达到range级别或者最好能达到ref则算是一个优秀而又负责的程序员。

    all:(full table scan)全表扫描无疑是最差,若是百万千万级数据量,全表扫描会非常慢。

    index:(full index scan)全索引文件扫描比all好很多,毕竟从索引树中找数据,比从全表中找数据要快。

    range:只检索给定范围的行,使用索引来匹配行。范围缩小了,当然比全表扫描和全索引文件扫描要快。sql语句中一般会有between,in,>,< 等查询。

    ref:非唯一性索引扫描,本质上也是一种索引访问,返回所有匹配某个单独值的行。比如查询公司所有属于研发团队的同事,匹配的结果是多个并非唯一值。

    eq_ref:唯一性索引扫描,对于每个索引键,表中有一条记录与之匹配。比如查询公司的CEO,匹配的结果只可能是一条记录,

    const:表示通过索引一次就可以找到,const用于比较primary key 或者unique索引。因为只匹配一行数据,所以很快,若将主键至于where列表中MySQL就能将该查询转换为一个常量。

    system:表只有一条记录(等于系统表),这是const类型的特列,平时不会出现,了解即可

    possible_keys

    显示查询语句可能用到的索引(一个或多个或为null),不一定被查询实际使用。仅供参考使用。

    key

    显示查询语句实际使用的索引。若为null,则表示没有使用索引。

    key_len

    显示索引中使用的字节数,可通过key_len计算查询中使用的索引长度。在不损失精确性的情况下索引长度越短越好。key_len 显示的值为索引字段的最可能长度,并非实际使用长度,即key_len是根据表定义计算而得,并不是通过表内检索出的。

    ref

    显示索引的哪一列或常量被用于查找索引列上的值。

    rows

    根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数,值越大越不好。

    extra

    Using filesort: 说明MySQL会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序” 。出现这个就要立刻优化sql。

    Using temporary: 使用了临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和 分组查询 group by。 出现这个更要立刻优化sql。

    Using index: 表示相应的select 操作中使用了覆盖索引(Covering index),避免访问了表的数据行,效果不错!如果同时出现Using where,表明索引被用来执行索引键值的查找。如果没有同时出现Using where,表示索引用来读取数据而非执行查找动作。

    覆盖索引(Covering Index) :也叫索引覆盖,就是select 的数据列只用从索引中就能够取得,不必读取数据行,MySQL可以利用索引返回select 列表中的字段,而不必根据索引再次读取数据文件。

    Using index condition: 在5.6版本后加入的新特性,优化器会在索引存在的情况下,通过符合RANGE范围的条数 和 总数的比例来选择是使用索引还是进行全表遍历。

    Using where: 表明使用了where 过滤

    Using join buffer: 表明使用了连接缓存

    impossible where: where 语句的值总是false,不可用,不能用来获取任何元素

    distinct: 优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作。

    filtered

    一个百分比的值,和rows 列的值一起使用,可以估计出查询执行计划(QEP)中的前一个表的结果集,从而确定join操作的循环次数。小表驱动大表,减轻连接的次数。

    1. 通过explain的参数介绍,我们可以得知:
    2. 表的读取顺序(id)
    3. 数据读取操作的操作类型(type)
    4. 哪些索引被实际使用(key)
    5. 表之间的引用(ref)
    6. 每张表有多少行被优化器查询(rows)

    性能下降的原因

    从程序员的角度
    1 查询语句写的不好
    2 没建索引,索引建的不合理或索引失效
    3 关联查询有太多的join
    从服务器的角度
    1 服务器磁盘空间不足
    2 服务器调优配置参数设置不合理

    总结

    1 索引是排好序且快速查找的数据结构。其目的是为了提高查询的效率。
    2 创建索引后,查询数据变快,但更新数据变慢。
    3 性能下降的原因很可能是索引失效导致。
    4 索引创建的原则,经常查询的字段适合创建索引,频繁需要更新的数据不适合创建索引。
    5 索引字段频繁更新,或者表数据物理删除容易造成索引失效。
    6 擅用 explain 分析sql语句
    7 除了优化sql语句外,还可以优化表的设计。如尽量做成单表查询,减少表之间的关联。设计归档表等。

    到这里,MySQL的索引优化分析就结束了,有什么不对的地方,大家可以提出来。

    来源:博客园

    作者:ITDragon龙

    原文:https://www.cnblogs.com/itdragon/p/8146439.html

    展开全文
  • Oracle中的SQL语言

    2019-06-08 15:07:00
    4.1 了解SQL语言(结构化查询语言)...2.统一性为许多任务提供了统一的命令,方便用户学习和使用,主要体现: 查询数据 插入,修改,删除记录 建立,修改和删除数据库的对象(表,视图,索引) 控制对数...
  • Oracle基础命令6索引何时建立索引索引的类别索引的创建索引操作 本文系列来自FREE教程,稍加修改以方便复习SQL,如果冒犯请联系作者删除。 索引 Oracle索引(index)最大的作用是用来优化数据库查询的效率,提升...
  • REBUILD(重新建立)其中有全部的索引的全文目录。 REORGANIZE(重组织)全文目录,这个操作可以优化内部索引和目录全文结构,这个过程被称 为主合并,意思是将那些细小的分散的全文索引物理(不是逻辑地)处理到...
  • 企业管理器,右击要创建索引的视图,弹出的菜单选择“设计视图”命令进入视图设计器。2.视图设计器显示了视图所包含的列,定义视图的SQL语句以及视图的数据。3.设计器任意一处右击鼠标,弹出的...
  • MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以...举例之前,先简单的介绍一下MySQL的explain,explain这个命令可以查看SQL语句的执行计划,结果如图所示: id: SELECT 查询的标识符. 每个 SELECT ...
  • 使用它时,只需要发出“做什么”的命令,“怎么做”是不用使用者考虑的。SQL功能强大、简单易学、使用方便,已经成为了数据库操作的基础,并且现在几乎所有的数据库均支持SQL关系数据库索引是一种单独的、...
  • :idea: SQL语句编写规范 1. DMBS(数据库管理系统) ...在SQL中,数据定义语言DDL(用来建立及定义数据表、字段以及索引等数据库结构)包含的命令有CREATE、DROP、ALTER;数据操纵语言DML(用来提供数据的查询、排...
  • SQL Server有一个Set Rowcount的的设置,它意思是使命令的处理响应指定行数之后停止处理命令,利用这个特点,我们可以借用它来一个千万行级数据表实现高性能分页查询。先来说说实现方式: 1、我们来...
  • 分页查询的方法已经很多很多,这里我也加入成为其中一员。SQL Server有一个Set ...先来说说实现方式:1、我们来假定Table有一个已经建立索引的主键字段ID(整数型),我们将按照这个字段来取数据进行分页。...
  • 2.2.5 维护SQL Server事务控制时向文件系统存储非结构化数据 69 2.2.6 “就地”赋值并修改数据库值 73 2.3 DELETE 75 2.3.1 删除行 75 2.3.2 截断表 76 2.4 高级数据修改技术 77 2.4.1 使用TOP分块...
  •  15、说明:选择每一组b值相同数据对应a最大记录所有信息(类似这样用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) select a,b,c from tablename ta where a=(select max(a) ...
  • 经典的SQL面试题(六)

    千次阅读 2008-10-27 18:09:00
    1、MS SQL Server,用来显示数据库信息的系统存储过程是( ) A sp_ dbhelp B sp_ db C sp_ help D sp_ helpdb 2、SQL语言,删除一个表的命令是( ) A DELETE B DROP C CLEAR D REMORE 3、关系数据库,主键...
  • 分页查询的方法已经很多很多,这里我也加入成为其中一员。 SQL Server有一个...先来说说实现方式: 1、我们来假定Table有一个已经建立索引的主键字段ID(整数型),我们将按照这个字段来取数据进行分页。 
  • ORACLE重建索引总结

    2011-12-13 10:32:28
    3 、rebuild online 时系统会产生一个 SYS_JOURNAL_xxx 的 IOT 类型的系统临时日志表 , 所有 rebuild online 时索引的变化都记录这个表 , 当新的索引创建完成后 , 把这个表的记录维护到新的索引去 , 然后 ...
  • Sql面试笔记

    2020-10-28 20:53:40
    查询语句分析 首先我们可以用EXPLAIN或DESCRIBE(简写:DESC)命令分析一条查询语句执行信息 ...中建立索引,优先考虑where、group by使用到字段。 查询条件中,尽量不使用select *,因为会返回过多
  • SQL21日自学通

    2014-12-19 14:55:27
    附件A 在SQL 中的常见术语495 ALTER DATABASE495 ALTER USER495 BEGIN TRANSACTION 495 CLOSE CURSOR495 COMMIT TRANSACTION496 CREATE DATABASE496 CREATE INDEX496 CREATE PROCEDURE496 CREATE TABLE497 CREATE ...
  • SQL Server Rowcount

    2013-03-26 10:23:00
    SQL Server有一个Set Rowcount的的设置,它意思是使命令的处理响应指定行数之后停止处理命令,利用这个特点,我们可以借用它来一个千万行级数据表实现高性能分页查询。先来说说实现方式: 1、...
  • 使用INPUT命令可以在SQL缓冲区增加一行或多行 SQL>i SQL>输入内容 e、调用外部系统编辑器 SQL>edit 文件名 可以使用DEFINE命令设置系统变量EDITOR来改变文本编辑器类型,在login.sql文件定义如下一行 DEFINE_...
  • MySQL命令大全

    2018-01-15 11:19:17
    如果要对用户所用登录终端进行限制,可以更新User表相应用户Host字段,进行了以上更改后应重新启动数据库服务,此时登录时可用如下类似命令: mysql -uroot -p; mysql -uroot -pnewpassword; mysql mydb ...

空空如也

空空如也

1 2 3 4 5 ... 7
收藏数 131
精华内容 52
关键字:

在sql中建立索引的命令是