精华内容
下载资源
问答
  • 选择哪些列需要建立索引,可以通过添加索引的方式给一个表添加多个索引查看SQL执行计划强调:在SQL语句执行之前,会专门有个叫查询优化器的组件对我们写的SQL进行优化和调整,然后生成执行计划,也就是说,最终执行的SQL不...

    使用Navicat for MySQL操作索引

    1.选中需要加索引的表,点击设计表

    16f0a7ab0d6770ce283df550ad3ee3b0.png

    2.选择哪些列需要建立索引,可以通过添加索引的方式给一个表添加多个索引

    a6687f56255bfc84cee8227f42836188.png

    查看SQL执行计划

    强调:

    在SQL语句执行之前,会专门有个叫查询优化器的组件对我们写的SQL进行优化和调整,然后生成执行计划,也就是说,最终执行的SQL不一定是我们当初自己写的SQL,如果出现这个情况大家不要惊讶!

    语法:

    explain 查询语句;

    fc5010781a87e579b6f6c332f28f4944.png

    其中type是我们需要优化的:其值常见的有以下几个,按照执行性能排列为

    systme > const > eq_ref > ref > range > index > all

    const: 根据主键索引或者唯一索引查询到的结果

    explain select id,name from employee where id = 1;

    ref: 使用非唯一性索引做查询,返回匹配的记录行,常见于多表查询中

    explain select * from department d join employee e on d.id = e.dept_id;

    range: 索引做范围查询,常见于、>=、between等操作符

    explain select age from employee where age >= 20 and age <= 30

    index: 索引全查询,MySQL遍历整个索引来查找匹配的行。

    explain select age from employee where age >= 20

    注意:

    SQL优化的目标之一要把type优化在ref到index之间,该值没有优化的情况下一般都是all

    其他列的含义可以参考博客: https://www.cnblogs.com/clphp/p/5403215.html

    适当使用索引

    建立索引的原则

    较频繁的作为查询条件的字段应该创建索引,如:登录操作唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件 作为索引的列,如果不能有效的区分数据,那么这个列就不适合作为索引列;比如(性别,状态不多的状态列)更新非常频繁的字段不适合创建索引,原因:索引有维护成本不会出现在where 子句中的字段不该创建索引索引不是越多越好;(只为必要的列创建索引)

    join的原则

    注意:不要使用隐式连接,原因在于SQL的执行顺序

    数据量小的表写在join的左边,数据量大的表写在join的右边MySQL中join都是通过Nested Loop Join来实现,简单理解为循环嵌套,应该数据量少的表作为外层循环,数据量大的表作为内层循环,然后合并结果优先优化Nested Loop的内层循环保证join语句中被作为连接条件的字段已经建立了索引扩大缓冲区的大小,容纳更大的查询数据(在配置文件中配置,DBA来做)

    避免索引失效(忽略查询优化器)

    where语句中索引列参与算术计算,该索引失效where语句中索引列参与函数运算,该索引失效where语句中使用in运算符有时会让索引失效where语句中做不等于( != , <> )运行,该索引失效where语句中发生类型转发,该索引失效where语句中模糊查询时以%开头,该索引失效在复合索引的使用时跟声明时顺序不一致或者中间有列的缺失,该索引失效如:声明了(a,b,c)的复合索引,但是在用是时候中间有列的缺失where a = xx and c = xx中间缺失了b,所以无法使用该复合索引,只要不是缺失中间列,其他情况索引均有效
    展开全文
  • 深入浅出数据库索引原理参见:https://www.cnblogs.com/aspwebchh/p/6652855.html1.为什么...而加上主键的表,会变成树形(B树/B+树),这样整个表就变成一个索引,即聚合索引假如我们执行一个SQL语句:select * fro...

    深入浅出数据库索引原理

    参见:https://www.cnblogs.com/aspwebchh/p/6652855.html

    1.为什么给表加上主键?

    • 1.平时创建表的时候,都会给表加上主键。如果没有主键的表,数据会一行行的排列在磁盘上,查找一个数据需要一条条的进行对比。而加上主键的表,会变成树形(B树/B+树),这样整个表就变成一个索引,即聚合索引

    83802bed7283b84b96fdd8690c9088b4.png

    假如我们执行一个SQL语句:

    select * from table where id = 1256;

    首先根据索引定位到1256这个值所在的叶结点,然后再通过叶结点取到id等于1256的数据行。

    2.为什么索引后会使查询变快?

    • 假如一张表有一亿条数据 ,需要查找其中某一条数据,按照常规逻辑, 一条一条的去匹配的话, 最坏的情况下需要匹配一亿次才能得到结果,用大O标记法就是O(n)最坏时间复杂度,这是无法接受的,而且这一亿条数据显然不能一次性读入内存供程序使用, 因此, 这一亿次匹配在不经缓存优化的情况下就是一亿次IO开销,以现在磁盘的IO能力和CPU的运算能力, 有可能需要几个月才能得出结果 。如果把这张表转换成平衡树结构(一棵非常茂盛和节点非常多的树),假设这棵树有10层,那么只需要10次IO开销就能查找到所需要的数据, 速度以指数级别提升,用大O标记法就是O(log n),n是记录总树,底数是树的分叉数,结果就是树的层次数。

    a0e7d88127ca7b7de0105749918747f7.png

    3.为什么加索引后会使写入、修改、删除变慢?

    • 事物都是有两面的, 索引能让数据库查询数据的速度上升, 而使写入数据的速度下降,原因很简单的, 因为平衡树这个结构必须一直维持在一个正确的状态, 增删改数据都会改变平衡树各节点中的索引数据内容,破坏树结构, 因此,在每次数据改变时, DBMS必须去重新梳理树(索引)的结构以确保它的正确,这会带来不小的性能开销,也就是为什么索引会给查询以外的操作带来副作用的原因。

    4.什么情况下要同时在两个字段上建索引?

    4.1 非聚合索引

    • 非聚集索引和聚集索引一样, 同样是采用平衡树作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段, 假如给user表的name字段加上索引 , 那么索引就是由name字段中的值构成,在数据改变时, DBMS需要一直维护索引结构的正确性。如果给表中多个字段加上索引 , 那么就会出现多个独立的索引结构,每个索引(非聚集索引)互相之间不存在关联。 如下图

    e87c2fc17262113b23ad52062f183e6e.png
    • 每次给字段建一个新索引, 字段中的数据就会被复制一份出来, 用于生成索引。 因此, 给表添加索引,会增加表的体积, 占用磁盘存储空间。

    非聚集索引和聚集索引的区别在于, 通过聚集索引可以查到需要查找的数据, 而通过非聚集索引可以查到记录对应的主键值 , 再使用主键的值通过聚集索引查找到需要的数据,如下图

    cb2d6d1f0f5716d3aef2ba3455c2870d.png

    4.2 覆盖索引

    • 非聚合索引都会利用主键通过聚合索引来定位到数据,聚合索引(主键)是通往真实数据所在的唯一路径,但是有一种例外是可以不使用聚合索引就能查询到所需要的数据,这种办法被称为覆盖索引。

    先看下面这个SQL语句

    //建立索引

    create index index_birthday on user_info(birthday);

    //查询生日在1991年11月1日出生用户的用户名

    select user_name from user_info where birthday = '1991-11-1'

    这句SQL语句的执行过程如下

    首先,通过非聚集索引index_birthday查找birthday等于1991-11-1的所有记录的主键ID值

    然后,通过得到的主键ID值执行聚集索引查找,找到主键ID值对就的真实数据(数据行)存储的位置

    最后, 从得到的真实数据中取得user_name字段的值返回, 也就是取得最终的结果

    我们把birthday字段上的索引改成双字段的覆盖索引

    create index index_birthday_and_user_name on user_info(birthday, user_name);

    这句SQL语句的执行过程就会变为

    通过非聚集索引index_birthday_and_user_name查找birthday等于1991-11-1的叶节点的内容,然而, 叶节点中除了有user_name表主键ID的值以外, user_name字段的值也在里面, 因此不需要通过主键ID值的查找数据行的真实所在, 直接取得叶节点中user_name的值返回即可。 通过这种覆盖索引直接查找的方式, 可以省略不使用覆盖索引查找的后面两个步骤, 大大的提高了查询性能,如下图

    1564b665cbf41f33eeda2b3b6ffab7cc.png

    一、为什么要创建索引呢(优点)?

    这是因为,创建索引可以大大提高系统的性能。

    第一, 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。

    第二, 可以大大加快数据的检索速度,这也是创建索引的最主要的原因。

    第三, 可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。

    第四, 在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。

    第五, 通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。

    二、建立方向索引的不利因素(缺点)

    也许会有人要问:增加索引有如此多的优点,为什么不对表中的每一个列创建一个索引呢?这种想法固然有其合理性,然而也有其片面性。虽然,索引有许多优点,但是,为表中的每一个列都增加索引,是非常不明智的。这是因为,增加索引也有许多不利的一个方面。

    第一, 创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。

    第二, 索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。

    第三, 当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。

    三、创建方向索引的准则

    索引是建立在数据库表中的某些列的上面。因此,在创建索引的时候,应该仔细考虑在哪些列上可以创建索引,在哪些列上不能创建索引。

    一般来说,应该在这些列上创建索引。

    第一, 在经常需要搜索的列上,可以加快搜索的速度;

    第二, 在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构;

    第三, 在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度;

    第四, 在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的;

    第五, 在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间;

    第六, 在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。

    同样,对于有些列不应该创建索引。一般来说,不应该创建索引的的这些列具有下列特点:

    第一, 对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。

    第二, 对于那些只有很少数据值的列也不应该增加索引。这是因为,由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。

    第三, 对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少。

    第 四, 当修改性能远远大于检索性能时,不应该创建索引。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少 索引时,会提高修改性能,降低检索性能。因此,当修改性能远远大于检索性能时,不应该创建索引。

    展开全文
  • 自动建立的索引除了显式地(使用CREATE INDEX的SQL语句)建立索引以外,SQL Server还在我们进行以下操作时为我们自动建立索引:设立唯一(UNIQUE)约束:会自动创建一个唯一非聚集索引唯一约束,就是需要在插入...

    v2-1c4d7ae8d22f98e4bdeec43cc4769585_1440w.jpg?source=172ae18b

    自动建立的索引

    除了显式地(使用CREATE INDEX的SQL语句)建立索引以外,SQL Server还在我们进行以下操作时为我们自动建立索引:

    • 设立唯一(UNIQUE)约束:会自动创建一个唯一非聚集索引。唯一约束,就是需要在插入数据时进行唯一检查,那怎么检查呢?不可能使用全表扫描吧?只能通过唯一索引来快速查找。所以,唯一约束本质上是依赖唯一索引的。
      此外,删除唯一约束也会同时删除该约束所依赖的唯一索引。
    • 设立主键(PRIMARY KEY)约束:
    1. 如果表上还没有建立聚集索引,会自动创建一个聚集唯一索引
    2. 如果表上已经有了聚集索引,会自动创建一个聚集唯一索引

    所以要注意,如果面试官问你:主键就是唯一聚集索引吗?你正确的回答是:不一定……

    以上SQL Server自动创建的索引,在SQL Server Object Explore中不会显示,我们需要在系统视图(视图的概念后文详述)sys.indexes中查看:

    -- [type]:1 聚集; >1 非聚集
    

    演示:

    @试一试@:

    • 新建一个UNIQUE约束,会不会利用已有的UNIQUE索引
    • 删除一个UNIQUE约束依赖的UNIQUE索引,会有什么结果?

    合理规划索引

    使用索引的目的是为了提高查询的性能。

    但是,我们首先要知道使用索引的代价。事实上,天下没有免费的午餐,凡事皆有代价!这是我们在做性能优化的时候尤其要牢记的一点(尤其是“性能控”的同学)。具体来说,索引会:

    1. 占用更多的磁盘空间。
    2. 在对数据进行增删改操作时更慢。因为在进行这些操作的同时还要进行索引的维护。

    所以,我们在建立索引时应综合考虑以下因素,以确定在某列上有无必要建立索引:

    1. 表是经常被读(取),还是被写(入)。大多数时候,表的读取操作远大于写入操作(增删改),所以索引的存在是可取的;但是,某些特殊的表,比如日志记录,是写大于读的,在这种表上,建立索引,很有可能得不偿失。
    2. 是否经常使用该列作为过滤条件进行查找。如果几乎不会使用这一列进行查找,比如Student表的SelfDescription(自我介绍),想想,使用自我介绍内容进行查找的时候多不多?
    3. 该列上行数据的类型和大小。行数据的类型越简单,比较速度越快;空间小,就不会有空间压力,这样就适合建立索引;反之就不宜在其上建立索引。同样比如Student的SelfDescription,数据类型为NVARCHAR(1024),实际存储内容也不小,就不适合用于建立索引。(NVARCHAR(MAX)、TEXT等“大”值类型语法就不允许做索引列)
    4. 各行数据分散(不重复)的程度。数据不分散,重复度高,就无法建立一个高效的查询树。直观的说,就是分不了叉。典型的就是BIT类型,只有1和0两个值,怎么做索引?硬要做索引,是不是就是一个二叉树,左边是一串0,右边是一串1就完了,有没有索引的价值?不要觉得少1/2的查询次数就够了!理想的索引,应该取得指数级的性能优化(复习:算法和复杂度)

    另外,聚集索引是最高效的,应该把它用到最常用最适合索引的列上(通常就是主键)。

    索引失效

    但是,哪怕你建立了索引,也不能确保SQL Server就按照你构想的方式使用这个索引。注意,我们前面讲的“如果表上已有聚集索引,SQL Server首先在索引中检索……”这里的,准确的说,是“大概率会”。为什么呢?!因为:

    执行计划

    事实上,SQL Server会把SQL语句进行解析,明白我们的查询目的之后,自动的生成一个“执行计划”,然后根据该“执行计划”在数据库中进行查询。

    执行计划的主要作用是对查询进行优化。一条查询SQL语句(尤其是当它比较复杂的时候)的目标,可能有多种途径实现,SQL Server会为我们选择一个“最佳路径”……是不是有点人工智能的味道?^_^

    所以,如果SQL Server认为使用索引比不使用索引更慢,即使有索引,SQL Server也不会在执行计划中使用索引!

    所以,进行SQL Server的性能优化,查看并理解执行计划至关重要。

    演示:查看执行计划

    执行计划的第一步SELECT,根据是否使用索引如何使用索引,可分为以下种类:

    扫描(Scan)

    Scan用于查找全部数据,通常可以由以下SQL语句引发:

    -- 有聚集索引Clustered Scan,没有Table Scan
    

    在执行计划图中用扫描(Scan)标识:

    v2-cf7355a7705f7e00b7931914e6fa524e_b.jpg
    • 如果表上没有聚集索引:会进行表扫描(Table Scan),即通过IAM对表数据进行遍历,需要查找整个表的数据。
    • 如果表上已有聚集索引:通常会进行聚集索引扫描(Clustered Index Scan),即通过聚集索引进行索引树的遍历,还是会查找整个表的数据。

    查找(Seek)

    不进行全表扫描,(因为使用索引)可以只比对部分索引键值就获取到所需数据。通常可以由以下SQL语句引发:

    -- Student表在Id上建立了聚集索引,注意SELECT后是*
    

    在执行计划图中用查找(Seek)标识:

    v2-9d8038f2dfb2405e77988fa604e6d96e_b.jpg
    • 如果使用的是非聚集索引,显示Index Seek(NonClustered)
    • 如果使用的是聚集索引,显示Clustered Index Seek(Clustered)

    想一想我们示例的SQL语句,假如我们进行如下修改:

    -- Student表在Age上建立了非聚集索引,注意SELECT后改成了* (不再是Age)
    

    会出现什么情况?

    再次查找(Lookup)

    当我们利用非聚集索引进行查找,且要求返回除索引键值以外的行数据时,因为非聚集索引的叶子节点上没有存放实际的行数据,所以还需要再进行一次查询(复习:索引(一))。在执行计划图中就会表现为这种形式:

    v2-8293d38e57c9ac5fbc7a4bbca3d9766f_b.jpg

    除了之前我们讲过的Index Seek,还出现了RID Lookup(Heap),这就是基于非聚集索引查找结果的再次查询。

    根据表上是否已建立聚集索引,Lookup又可以分为:

    v2-92165cfd540ee48edb2460e9d1635967_b.jpg
    • RID Lookup(Heap):表上没有聚集索引,只能在堆上查找RID获得行数据
    • Key Lookup(Clustered):表上有聚集索引,通过聚集索引的键值找到行数据

    复习:索引(一)

    好了,现在我们回头来看一看之前我们说的:“即使有索引,SQL Server也不会在执行计划中使用索引”有没有实际的例子?

    执行如下SQL语句:

    SELECT 

    v2-26240ec2a0b8bbc1fd03dd5c2186d1e4_b.png

    我们发现:执行计划直接使用了Table Scan,根本没有理会在Gender上建立的索引IX_Teacher_Gender!为什么呢?

    因为Gender是BIT类型的,而BIT类型的索引——我们前面经过——是非常低效的。并且,如果坚持使用这个非聚集索引,我们在完成了索引的seek之后,还得再通过RID进行一次Lookup查找(是不是这本质上就是一次Table Scan?),是不是更加的费事?

    所以,请牢牢的记住这一点:

    SQL的SELECT语句,是指令性的,结果导向的!

    换言之,SELECT语句只是给出了对查询结果的要求,而没有给出具体的执行步骤。理解这一点非常重要,因为由此我们才能明白数据库性能调优为什么要强调:

    No Profile,No Improvement!(大致可翻译为:没有实测效果,就没有性能优化)

    很多同学会有一种错觉:SELECT语句的不同书写方式可以决定SQL Server的执行方式——注意:不是这样的!至少不完全是这样的。

    SQL Server如何确定执行计划非常复杂,本文不予讲述。同学们只需要记住:在具体的开发实践中,性能优化一定要测试测试再测试。 在特定情况下,经过反复测试,如果SQL Server的执行计划未能达到理想效果,我们可以通过 Hints 技术来强制SQL Server使用索引。

    同时,SQL Server的执行计划图,还可以让我们查看更详细的信息,包括:

    1. 表里共有多少行,检索时读取了多少行
    2. 检索耗费的CPU和I/O资源
    3. 每个步骤执行耗费资源占比等等

    这些都有助于我们更好的了解该执行计划并进行性能优化。

    v2-d40ab830331ed6721bb758d1f2abc347_b.png
    I/O和CPU的消耗

    演示:

    作业

    新建表Message(Id, FromUser, ToUser, UrgentLevel, Kind, HasRead, IsDelete, Content),使用该表和SQL语句,证明:

    • 唯一约束依赖于唯一索引
    • 主键上可以是非聚集索引

    并利用“执行计划”图演示说明:Scan、Seek和Lookup的使用和区别。

    每日单词

    v2-a5958bcd3c754a73741d5c1101c34e21_b.png

    感谢童鞋们的阅读!^_^

    我就是:黑律师/包工头/创业狗/老码农……现在还是教书匠的大飞哥。

    再次重申这个系列的目标是:

    1)通俗易懂。2)实战为主。3)面向就业。

    系列内容的完善需要你的反馈!

    欢迎点赞和评论,以及加入我们的QQ交流群:326801052。

    展开全文
  • 通过创建唯一性索引,可以保证数据库表中的每一行数据的唯一性。 可以加快数据的检索速度 可以加速表与表之间的连接 在使用分组和排序进行检索的时候,可以减少查询中分组和排序的时间 四、缺点 创建索引和维护索引...

    MySQL索引优缺点、何时需要/不需要创建索引、索引及sql语句的优化

    一、什么是索引?

    索引是对数据库表中的一列或多列值进行排序的一种结构,使用索引可以快速访问数据库表中的特定信息。

    二、索引的作用?

    索引相当于图书上的目录,可以根据目录上的页码快速找到所需的内容,提高性能(查询速度)

    三、优点:

    通过创建唯一性索引,可以保证数据库表中的每一行数据的唯一性。

    可以加快数据的检索速度

    可以加速表与表之间的连接

    在使用分组和排序进行检索的时候,可以减少查询中分组和排序的时间

    四、缺点

    创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。

    索引需要占用物理空间,数据量越大,占用空间越大

    会降低表的增删改的效率,因为每次增删改索引,都需要进行动态维护

    五、什么时候需要创建索引

    主键自动建立唯一索引

    频繁作为查询条件的字段应该创建索引

    查询中排序的字段创建索引将大大提高排序的速度(索引就是排序加快速查找

    查询中统计或者分组的字段;

    六、什么时候不需要创建索引

    频繁更新的字段不适合创建索引,因为每次更新不单单是更新记录,还会更新索引,保存索引文件

    where条件里用不到的字段,不创建索引;

    表记录太少,不需要创建索引;

    经常增删改的表;

    数据重复且分布平均的字段,因此为经常查询的和经常排序的字段建立索引。注意某些数据包含大量重复数据,因此他建立索引就没有太大的效果,例如性别字段,只有男女,不适合建立索引。更多面试题,欢迎关注公众号 Java面试题精选

    七、索引的分类:

    普通索引:最基本的索引,它没有任何限制

    唯一索引:索引列的值必须唯一,且不能为空,如果是组合索引,则列值的组合必须唯一。

    主键索引:特殊的索引,唯一的标识一条记录,不能为空,一般用primary key来约束。

    联合索引:在多个字段上建立索引,能够加速查询到速度

    八、索引和sql语句的优化

    1、前导模糊查询不能使用索引,如name like ‘%静’

    2、Union、in、or可以命中索引,建议使用in

    3、负条件查询不能使用索引,可以优化为in查询,其中负条件有!=、<>、not in、not exists、not like等

    4、联合索引最左前缀原则,又叫最左侧查询,如果在(a,b,c)三个字段上建立联合索引,那么它能够加快a|(a,b)|(a,b,c)三组的查询速度。

    5、建立联合查询时,区分度最高的字段在最左边

    6、如果建立了(a,b)联合索引,就不必再单独建立a索引。同理,如果建立了(a,b,c)索引就不必再建立a,(a,b)索引

    7、存在非等号和等号混合判断条件时,在建索引时,要把等号条件的列前置

    8、范围列可以用到索引,但是范围列后面的列无法用到索引。索引最多用于一个范围列,如果查询条件中有两个范围列则无法全用到索引。范围条件有:<、<=、>、>=、between等。往期:100期面试题汇总

    9、把计算放到业务层而不是数据库层。在字段上计算不能命中索引。

    10、强制类型转换会全表扫描,如果phone字段是varcher类型,则下面的SQL不能命中索引。Select * fromuser where phone=13800001234

    11、更新十分频繁、数据区分度不高的字段上不宜建立索引。

    更新会变更B+树,更新频繁的字段建立索引会大大降低数据库性能。“性别”这种区分度不太大的属性,建立索引是没有什么意义的,不能有效过滤数据,性能与全表扫描类似。一般区分度在80%以上就可以建立索引。区分度可以使用count(distinct(列名))/count(*)来计算。

    12、利用覆盖索引来进行查询操作,避免回表。被查询的列,数据能从索引中取得,而不是通过定位符row-locator再到row上获取,即“被查询列要被所建的索引覆盖”,这能够加速度查询。

    13、建立索引的列不能为null,使用not null约束及默认值

    14、利用延迟关联或者子查询优化超多分页场景,MySQL并不是跳过offset行,而是取offset+N行,然后放弃前offset行,返回N行,那当offset特别大的时候,效率非常低下,要么控制返回的总数,要么对超过特定阈值的页进行SQL改写。

    15、业务上唯一特性的字段,即使是多个字段的组合,也必须建成唯一索引。

    16、超过三个表最好不要用join,需要join的字段,数据类型必须一致,多表关联查询时,保证被关联的字段需要有索引。

    17、如果明确知道查询结果只要一条,limit 1能够提高效率,比如验证登录的时候。

    18、Select语句务必指明字段名称

    19、如果排序字段没有用到索引,就尽量少排序

    20、尽量用union all 代替 union。Union需要将集合合并后在进行唯一性过滤操作,这会涉及到排序,大量的cpu运算,加大资源消耗及延迟,当然,使用union all的前提条件是两个结果集没有重复数据。

    21、使用合理的分页提高效率。select id,name from product limit 866613, 20

    使用上述SQL语句做分页的时候,可能有人会发现,随着表数据量的增加,直接使用limit分页查询会越来越慢。

    优化的方法如下:

    可以取前一页的最大行数的id,然后根据这个最大的id来限制下一页的起点。

    比如此列中,上一页最大的id是866612。

    SQL可以采用如下的写法:select id,name from product where id> 866612 limit 20。
    高质量编程视频:shangyepingtai.xin

    展开全文
  • 不好意思哈,有事情许久未更文,今天我又出现啦!3.索引的定义与删除索引是行位置的列表,按一个或...通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。3.1创建索引在SQL语言中,建立索引使用create inde...
  • 索引相当于图书上的目录,可以根据目录上的页码快速找到所需的内容,提高性能(查询速度)三、优点:通过创建唯一性索引,可以保证数据库表中的每一行数据的唯一性。可以加快数据的检索速度可以加速表与表之间的连接在...
  • 通过创建唯一性索引,可以保证数据库表中的每一行数据的唯一性。 可以加快数据的检索速度 可以加速表与表之间的连接 在使用分组和排序进行检索的时候,可以减少查询中分组和排序的时间 四、缺点 创建索引和...
  • 请问下面的sql语句,要想加快查询速度,该怎么创建索引?以下,以mysql数据库为准。select * from test where a=? and b>? order by c limit 0,100结果可能会出乎你的意料。我们首先准备一下运行环境,然后按照最...
  • 索引在数据库中是一个很特殊的存在,它的目的就是为了提高数据查询得效率。同样,它也有弊端,更新一个带索引的表的时间比更新一个没有带索引的时间更长。有得有失。我希望做一些研究测试,搞清楚索引对于我们使用...
  • 通过创建唯一性索引,可以保证数据库表中的每一行数据的唯一性。 可以加快数据的检索速度 可以加速表与表之间的连接 在使用分组和排序进行检索的时候,可以减少查询中分组和排序的时间 四、缺点 创建索引和维护...
  • 从使用的角度来看,MYSQL的索引可以分为以下几类: 单列索引 对每个字段创建索引 组合索引 使用表的多个字段组合创建索引,这类索引要想查询时派上用场,需要查询用的SQL语句遵循最左匹配原则。 单列索引和组合...
  • 索引相当于图书上的目录,可以根据目录上的页码快速找到所需的内容,提高性能(查询速度)三、优点:通过创建唯一性索引,可以保证数据库表中的每一行数据的唯一性。可以加快数据的检索速度可以加速表与表之间的连接在...
  • 如何某表的某个字段有主键约束和唯一性约束,则Oracle 则会自动在相应的约束列上建议唯一索引。数据库索引主要进行提高访问速度。 建设原则:  1、索引应该经常建在Where 子句经常用到的列上。如果某个大表经常...
  • 索引相当于图书上的目录,可以根据目录上的页码快速找到所需的内容,提高性能(查询速度)三、优点:通过创建唯一性索引,可以保证数据库表中的每一行数据的唯一性。可以加快数据的检索速度可以加速表与表之间的连接在...
  • 创建表、视图、索引sql语句如下: CREAT TABLE (列名,数据类型,约束) create view(创建视图) create index (创建索引)  1、primary key(主键)  2、unique (唯一性)  3、foreign key references...
  • SQL语句(五) 索引建立

    万次阅读 2018-07-16 16:59:42
    1.创建唯一性索引 SET STATISTICS TIME ON SELECT * FROM customer WHERE name ='阿爆' ; SET STATISTICS TIME OFF CREATE UNIQUE INDEX customer_name ON customer(name);--在customer表上的name列创建唯一性索引 ...
  • 通过创建唯一性索引,可以保证数据库表中的每一行数据的唯一性。 可以加快数据的检索速度 可以加速表与表之间的连接 在使用分组和排序进行检索的时候,可以减少查询中分组和排序的时间 四、缺点 创建索引和维护...
  • 我们通常使用下面的语句创建一个索引:create index idx_t1_bcd on t1(b,c,d);上面sql的意思是在t1表中,针对b,c,d三个字段创建一个联合索引。但是大家不知道的是,上面这个sql实际上和下面的这个sql是等价的:...
  • sql语句创建数据表

    2019-03-08 15:36:00
    unsigned 数值类型 无符号化 AUTO_INCREMENT 自增长 PRIMARY KEY 唯一主键 COMMENT 备注信息 ...约束作用,规范数据的唯一性,同时也在这个key上建立了一个唯一索引; decima...
  • 通过创建唯一性索引,可以保证数据库表中的每一行数据的唯一性。 可以加快数据的检索速度 可以加速表与表之间的连接 在使用分组和排序进行检索的时候,可以减少查询中分组和排序的时间 四、缺点 ...
  • 在上篇文章里,我讨论了唯一和非唯一聚集索引的区别。...当我们在表上定义PRIMARY KEY约束时,SQL Server会为我们创建唯一聚集索引;另外我们可以通过CREATE UNIQUE CLUSTERED INDEX语句在表上创建唯一...
  • ①为员工表创建一个索引名为emp_ssn的唯一性非聚集索引索引关键字是SSN,填充因子80% 。 create unique nonclustered index emp_ssn on employee(ssn) with(fillfactor=80) ② 重命名索引,将索引...
  • 经典SQL语句大全

    热门讨论 2014-12-20 12:00:30
    10、说明:几个简单的基本的sql语句 选择:select * from table1 where 范围 插入:insert into table1(field1,field2) values(value1,value2) 删除:delete from table1 where 范围 更新:update table1 set field1...
  • 使用 EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。 2、为搜索字段建索引,一般来说,应该在这些列 上创建索引,例如:  在作为主键的列上,强制...
  • 方法一:直接在sql语句中声明字段主键约束 create table table_name (id type[length]constraintpk_nameprimary key,name tyoe[length],age type[length],class_id); 方法二:alter更改表添加约束 alter table ...

空空如也

空空如也

1 2 3 4 5 ... 13
收藏数 248
精华内容 99
关键字:

创建唯一性索引sql语句