精华内容
下载资源
问答
  • 一般的应用系统,读写比例在10:1左右,而且插入操作和一般的更新操作很少出现性能问题,在生产环境中,我们遇到最多的,也是最容易出问题的,还是一些复杂的查询操作,因此对查询语句的优化显然是重中之重。...

    索引

    什么是索引?

    一般的应用系统,读写比例在10:1左右,而且插入操作和一般的更新操作很少出现性能问题,在生产环境中,我们遇到最多的,也是最容易出问题的,还是一些复杂的查询操作,因此对查询语句的优化显然是重中之重。说起加速查询,就不得不提到索引了

    为什么要有索引?

    索引在MySQL中也叫做“键”,是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键,尤其是当表中的数据量越来越大时,索引对于性能的影响愈发重要。

    索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。

    索引相当于字典的音序表,如果要查某个字,如果不使用音序表,则需要从几百页中逐页去查。

    索引的原理

    索引的目的在于提高查询效率,与我们查阅图书所用的目录是一个道理:先定位到章,然后定位到该章下的一个小节,然后找到页数。相似的例子还有:查字典,查火车车次,飞机航班等

    本质都是:通过不断地缩小想要获取数据的范围来筛选出最终想要的结果,同时把随机的事件变成顺序的事件,也就是说,有了这种索引机制,我们可以总是用同一种查找方式来锁定数据。

    数据库也是一样,但显然要复杂的多。

    因为不仅面临着等值查询,还有范围查询(>、

    我们回想字典的例子,能不能把数据分成段,然后分段查询呢?最简单的如果1000条数据,1到100分成第一段,101到200分成第二段,201到300分成第三段......这样查第250条数据,只要找第三段就可以了,一下子去除了90%的无效数据。但如果是1千万的记录呢,分成几段比较好?

    稍有算法基础的同学会想到搜索树,其平均复杂度是lgN,具有不错的查询性能。但这里我们忽略了一个关键的问题,复杂度模型是基于每次相同的操作成本来考虑的。

    而数据库实现比较复杂,一方面数据是保存在磁盘上的,另外一方面为了提高性能,每次又可以把部分数据读入内存来计算,因为我们知道访问磁盘的成本大概是访问内存的十万倍左右,所以简单的搜索树难以满足复杂的应用场景。

    磁盘IO与预读

    考虑到磁盘IO是非常高昂的操作,计算机操作系统做了一些优化,当一次IO时,不光把当前磁盘地址的数据,而且把相邻的数据也都读取到内存缓冲区内,因为局部预读性原理告诉我们,当计算机访问一个地址的数据的时候,与其相邻的数据也会很快被访问到。

    每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关,一般为4k或8k,也就是我们读取一页内的数据时候,实际上才发生了一次IO,这个理论对于索引的数据结构设计非常有帮助。

    索引的数据结构

    任何一种数据结构都不是凭空产生的,一定会有它的背景和使用场景,我们现在总结一下,我们需要这种数据结构能够做些什么?

    其实很简单,那就是:每次查找数据时把磁盘IO次数控制在一个很小的数量级,最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢?就这样,b+树应运而生。

    4fe375e37046e66cec491e1b1c570840.png

    如上图,是一颗b+树,关于b+树的定义可以参见自行查找,这里只说一些重点。

    浅蓝色的块我们称之为一个磁盘块,可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示),如磁盘块1包含数据项17和35,包含指针P1、P2、P3,P1表示小于17的磁盘块,P2表示在17和35之间的磁盘块,P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点不存储真实的数据,只存储指引搜索方向的数据项,如17、35并不真实存在于数据表中

    b+树的查找过程:

    如图所示,如果要查找数据项29,那么首先会把磁盘块1由磁盘加载到内存,此时发生一次IO,在内存中用二分查找确2917和35之间,锁定磁盘块1的P2指针,内存时间因为非常短(相比磁盘的IO)可以忽略不计,通过磁盘块1的P2指针的磁盘地址磁盘块3由磁盘加载到内存,发生第二次IO,29在26和30之间,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次IO,同时内存中做二分查找找到29,结束查询,总计三次IO。真实的情况是,3层的b+树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的,如果没有索引,每个数据项都要发生一次IO,那么总共需要百万次的IO,显然成本非常非常高。

    b+树性质:

    1.索引字段要尽量的小通过上面的分析,我们知道IO次数取决于b+数的高度h,假设当前数据表的数据为N,每个磁盘块的数据项的数量是m,则有h=㏒(m+1)N,当数据量N一定的情况下,m越大,h越小;而m = 磁盘块的大小 / 数据项的大小,磁盘块的大小也就是一个数据页的大小,是固定的,如果数据项占的空间越小,数据项的数量越多,树的高度越低。这就是为什么每个数据项,即索引字段要尽量的小,比如int占4字节,要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点,一旦放到内层节点,磁盘块的数据项会大幅度下降,导致树增高。当数据项等于1时将会退化成线性表。

    2.索引的最左匹配特性(即从左往右匹配):当b+树的数据项是复合的数据结构,比如(name,age,sex)的时候,b+数是按照从左到右的顺序来建立搜索树的,比如当(张三,20,F)这样的数据来检索的时候,b+树会优先比较name来确定下一步的所搜方向,如果name相同再依次比较age和sex,最后得到检索的数据;但当(20,F)这样的没有name的数据来的时候,b+树就不知道下一步该查哪个节点,因为建立搜索树的时候name就是第一个比较因子,必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时,b+树可以用name来指定搜索方向,但下一个字段age的缺失,所以只能把名字等于张三的数据都找到,然后再匹配性别是F的数据了, 这个是非常重要的性质,即索引的最左匹配特性。

    Mysql索引管理

    索引的功能:加速查找

    mysql中的primary key,unique,联合唯一也都是索引,这些索引除了加速查找以外,还有约束的功能

    MySQL的索引分类

    索引分类

    1.普通索引index:加速查

    2.唯一索引

    • 主键索引:primary key :加速查找+约束(不为空且唯一)

    • 唯一索引:unique:加速查找+约束 (唯一)

    3.联合索引

    • primary key(id,name):联合主键索引

    • unique(id,name):联合唯一索引

    • index(id,name):联合普通索引

    4.全文索引fulltext:用于搜索很长一篇文章的时候,效果最好。

    5.空间索引spatial:了解就好,几乎不用
     
     1 举个例子来说,比如你在为某商场做一个会员卡的系统。 2  3 这个系统有一个会员表 4 有下列字段: 5 会员编号 INT 6 会员姓名 VARCHAR(10) 7 会员身份证号码 VARCHAR(18) 8 会员电话 VARCHAR(10) 9 会员住址 VARCHAR(50)10 会员备注信息 TEXT11 12 那么这个 会员编号,作为主键,使用 PRIMARY13 会员姓名 如果要建索引的话,那么就是普通的 INDEX14 会员身份证号码 如果要建索引的话,那么可以选择 UNIQUE (唯一的,不允许重复)15 16 #除此之外还有全文索引,即FULLTEXT17 会员备注信息 , 如果需要建索引的话,可以选择全文搜索。18 用于搜索很长一篇文章的时候,效果最好。19 用在比较短的文本,如果就一两行字的,普通的 INDEX 也可以。20 但其实对于全文搜索,我们并不会使用MySQL自带的该索引,而是会选择第三方软件如Sphinx,专门来做全文搜索。2122 #其他的如空间索引SPATIAL,了解即可,几乎不用

    索引的两大类型:hash与btree

    我们可以在创建上述索引的时候,为其指定索引类型,分两类:

    hash类型的索引:查询单条快,范围查询慢

    btree类型的索引b+树,层数越多,数据量指数级增长(我们就用它,因为innodb默认支持它)

    不同的存储引擎支持的索引类型也不一样:

    • InnoDB 支持事务持行级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引

    • MyISAM 不支持事务,支持表级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引;

    • Memory 不支持事务,支持级别锁定,支持 B-tree、Hash 等索引,不支持 Full-text 索引;

    • NDB 支持事务,支持行级别锁定,支持 Hash 索引,不支持 B-tree、Full-text 等索引;

    • Archive 不支持事务,支持表级别锁定,不支持 B-tree、Hash、Full-text 等索引;

    创建/删除索引的语法

     
     1 #方法一:创建表时 2       CREATE TABLE 表名 ( 3                 字段名1  数据类型 [完整性约束条件…], 4                 字段名2  数据类型 [完整性约束条件…], 5                 [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY 6                 [索引名]  (字段名[(长度)]  [ASC |DESC])  7                 ); 8 10 #方法二:CREATE在已存在的表上创建索引11         CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名 12                      ON 表名 (字段名[(长度)]  [ASC |DESC]) ;13 15 #方法三:ALTER TABLE在已存在的表上创建索引16         ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX17                              索引名 (字段名[(长度)]  [ASC |DESC]) ;18                              19 #删除索引:DROP INDEX 索引名 ON 表名字;
    # 善用帮助文档help createhelp create index==================1.创建索引    -在创建表时就创建(需要注意的几点)    create table s1(    id int ,#可以在这加primary key    #id int index #不可以这样加索引,因为index只是索引,没有约束一说,    #不能像主键,还有唯一约束一样,在定义字段的时候加索引    name char(20),    age int,    email varchar(30)    #primary key(id) #也可以在这加    index(id) #可以这样加    );    -在创建表后在创建    create index name on s1(name); #添加普通索引    create unique age on s1(age);添加唯一索引    alter table s1 add primary key(id); #添加住建索引,也就是给id字段增加一个主键约束    create index name on s1(id,name); #添加普通联合索引2.删除索引    drop index id on s1;    drop index name on s1; #删除普通索引    drop index age on s1; #删除唯一索引,就和普通索引一样,不用在index前加unique来删,直接就可以删了    alter table s1 drop primary key; #删除主键(因为它添加的时候是按照alter来增加的,那么我们也用alter来删)

    帮助查看

    ccd080cc889346aa0291cb21840f2f65.png

    627c9253d96b85e9e24a5d1ec647e2e4.png

    测试索引

    准备数据:

    #1. 准备表create table s1(id int,name varchar(20),gender char(6),email varchar(50));#2. 创建存储过程,实现批量插入记录delimiter $$ #声明存储过程的结束符号为$$create procedure auto_insert1()BEGIN    declare i int default 1;    while(i<3000000)do        insert into s1 values(i,concat('egon',i),'male',concat('egon',i,'@oldboy'));        set i=i+1;    end while;END$$ #$$结束delimiter ; #重新声明分号为结束符号#3. 查看存储过程show create procedure auto_insert1\G #4. 调用存储过程call auto_insert1();

    在没有索引的前提下测试查询速度

    #无索引:从头到尾扫描一遍,所以查询速度很慢mysql> select * from s1 where id=333;+------+---------+--------+----------------+| id   | name    | gender | email          |+------+---------+--------+----------------+|  333 | egon333 | male   | 333@oldboy.com ||  333 | egon333 | f      | alex333@oldboy ||  333 | egon333 | f      | alex333@oldboy |+------+---------+--------+----------------+rows in set (0.32 sec)mysql> select * from s1 where email='egon333@oldboy';....... rows in set (0.36 sec)

    加上索引

    一定是为搜索条件的字段创建索引,比如select * from t1 where age > 5;就需要为age加上索引,在表中已经有大量数据的情况下,建索引会很慢,且占用硬盘空间,插入删除更新都很慢,只有查询快,比如create index idx on s1(id);会扫描表中所有的数据,然后以id为数据项,创建索引结构,存放于硬盘的表中。建完以后,再查询就会很快了。

    需要注意的是:innodb表的索引会存放于s1.ibd文件中,而myisam表的索引则会有单独的索引文件table1.MYI

    fc0681d9908c5b1ef6bcee439e364577.png

    正确使用索引

    覆盖索引

    例:select * from s1 where id=123;

    该sql命中了索引,但未覆盖索引。利用id=123到引的数据结构中定位到该id在硬盘中的位置,或者说再数据表中的位置。

    但是我们select的字段为*,除了id以外还需要其他字段,这就意味着,我们通过索引结构取到id还不够,还需要利用该id再去找到该id所在行的其他字段值,这是需要时间的,很明显,如果我们只select id,就减去了这份苦恼,如下:select id from s1 where id=123;

    这条就是覆盖索引了,命中索引,且从索引的数据结构直接就取到了id在硬盘的地址,速度很快

    db7157b90c405ffbbc2866380b741b0a.png

    联合索引

    afac3c7eb434479e31e55e88141c6561.png

    索引合并

    索引合并把多个单列索引合并使用

    组合索引能做到的事情,我们都可以用索引合并去解决,比如

    create index ne on s1(name,email);  #组合索引

    我们完全可以单独为name和email创建索引

    组合索引可以命中:

    select * from s1 where name='egon' ;select * from s1 where name='egon' and email='adf';

    索引合并可以命中:

    select * from s1 where name='egon' ;select * from s1 where email='adf';select * from s1 where name='egon' and email='adf';

    乍一看好像索引合并更好了:可以命中更多的情况,但其实要分情况去看,如果是name='egon' and email='adf',那么组合索引的效率要高于索引合并,如果是单条件查,那么还是用索引合并比较合理e1bcd044e122450f157734753ebf9c84.png若想利用索引达到预想的提高查询速度的效果,我们在添加索引时,必须遵循以下原则

    1.最左前缀匹配原则,非常重要的原则,例:create index ix_name_email on s1(name,email,),最左前缀匹配:必须按照从左到右的顺序匹配:

    select * from s1 where name='egon'; #可以select * from s1 where name='egon' and email='asdf'; #可以select * from s1 where email='alex@oldboy.com'; #不可以

    mysql会一直向右匹配直到遇到范围查询(>、 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。

    2.=和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式

    3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录

    4.索引列不能参与计算,保持列“干净”,比如from_unixtime(create_time) = ’2014-05-29’,就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

    最左前缀示范

    mysql> select * from s1 where id>3 and name='egon' and email='alex333@oldboy.com' and gender='male';Empty set (0.39 sec)mysql> create index idx on s1(id,name,email,gender); #未遵循最左前缀Query OK, 0 rows affected (15.27 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> select * from s1 where id>3 and name='egon' and email='alex333@oldboy.com' and gender='male';Empty set (0.43 sec)mysql> drop index idx on s1;Query OK, 0 rows affected (0.16 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> create index idx on s1(name,email,gender,id); #遵循最左前缀Query OK, 0 rows affected (15.97 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> select * from s1 where id>3 and name='egon' and email='alex333@oldboy.com' and gender='male';Empty set (0.03 sec)

    索引无法命中的情况需要注意:

    # like '%xx'select * from tb1 where email like '%cn';    # 使用函数select * from tb1 where reverse(email) = 'wupeiqi';       # orselect * from tb1 where nid = 1 or name = 'seven@live.com';# 特别的:当or条件中有未建立索引的列才失效,以下会走索引select * from tb1 where nid = 1 or name = 'seven';select * from tb1 where nid = 1 or name = 'seven@live.com' and email = 'alex'         # 类型不一致,如果列是字符串类型,传入条件是必须用引号引起来,不然...select * from tb1 where email = 999;    # 普通索引的不等于不会走索引# !=select * from tb1 where email != 'alex'# 特别的:如果是主键,则还是会走索引select * from tb1 where nid != 123# >select * from tb1 where email > 'alex'# 特别的:如果是主键或索引是整数类型,则还是会走索引        select * from tb1 where nid > 123        select * from tb1 where num > 123     #排序条件为索引,则select字段必须也是索引字段,否则无法命中# order byselect name from s1 order by email desc;# 当根据索引排序时候,select查询的字段如果不是索引,则不走索引select email from s1 order by email desc;# 特别的:如果对主键排序,则还是走索引:select * from tb1 order by nid desc; # 组合索引最左前缀# 如果组合索引为:(name,email)name and email       -- 使用索引name                 -- 使用索引email                -- 不使用索引# count(1)或count(列)代替count(*)在mysql中没有差别了# create index xxxx  on tb(title(19)) #text类型,必须制定长度
    • 避免使用select *

    • count(1)或count(列) 代替 count(*)

    • 创建表时尽量时 char 代替 varchar

    • 表的字段顺序固定长度的字段优先

    • 组合索引代替多个单列索引(经常使用多个条件查询时)

    • 尽量使用短索引

    • 使用连接(JOIN)来代替子查询(Sub-Queries)

    • 连表时注意条件类型需一致

    • 索引散列值(重复少)不适合建索引,例:性别不适合

    30bf59bd426c5c1833fd2711d45f81bc.png

    慢查询优化的基本步骤

    1. 先运行看看是否真的很慢,注意设置SQL_NO_CACHE

    2. where条件单表查,锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起,单表每个字段分别查询,看哪个字段的区分度最高

    3. explain查看执行计划,是否与1预期一致(从锁定记录较少的表开始查询)

    4. order by limit 形式的sql语句让排序的表优先查

    5. 了解业务方使用场景

    6. 加索引时参照建索引的几大原则

    7. 观察结果,不符合预期继续从0分析
    展开全文
  • 一般来说.如果有where a=? and b=? and c=? 的语句.如果表也有DML, 我一般只在a 上建索引. 这也是代价平衡的结果. 一方面 只在a 上建索引那么是...另一方面,DML也会更新index,更新三个column肯定比更新一个column...

    一般来说.如果有where a=? and b=? and c=? 的语句.

    如果表也有DML, 我一般只在a 上建索引.  这也是代价平衡的结果. 一方面 只在a 上建索引那么是

    index range scan, 不像联合索引那样可以index  unique scan , 我觉得速度差的不多(数据量不大的情况).

    另一方面,DML也会更新index,更新三个column肯定比更新一个column慢些. 所以我一般采取折中.只建单列或2列索引.

    联合索引对于查询

    where a=? and b=? and c=?

    这样的语句时,速度比分开的索引要快很多!

    我现在有一个应用

    几乎就是按照这种顺序来查找记录的

    所以,我就建立了联合索引

    ----------------------------------------------------

    建立联合索引之后,也不影响你再在这个字段上面创建普通索引。

    索引怎么建立,除了你的程序应用,还应当要考虑到表的活动是否频繁,

    如果是典型的oltp,索引就不要建立太多,位图索引就不用考虑,

    但是dss系统,主要是为了检索,索引多一点就无所谓

    联合索引使用结论:

    1):查询条件中出现联合索引第一列,或者全部,则能利用联合索引.

    2):条件列中只要条件相连在一起,以本文例子来说就是:

    last_name=’1′ and first_name=’1′

    first_name=’1′ and last_name=’1′

    ,无论前后,都会利用上联合索引.

    3):查询条件中没有出现联合索引的第一列,而出现联合索引的第二列,或者第三列,都不会利用联合索引查询.

    单一列索引的应用结论:

    1):只要条件列中出现索引列,无论在什么位置,都能利用索引查询.

    两者的共同点:

    1):要想利用索引,都要符合SARG标准.

    2) :都是为了提高查询速度.

    3):都需要额外的系统开销,磁盘空间.

    补充说明: stmtText信息来产生,在查询语句前面加上:SET STATISTICS PROFILE on.可以通过运行它,来观察你的查询是否合理,这样才能真正做到优化.

    本文主旨:讨论什么情况下能利用上索引.

    索引:创建索引可以根据查询业务的不同分为两种:单一列的索引,联合索引. 顾名思义,单一列索引就是指在表的某一列上创建索引,联合索引是在多个列上联合创建索引.

    优缺点比较:

    1):索引所占用空间:单一列索引相对要小.

    2):索引创建时间:单一列索引相对短.

    3):索引对insert,update,delete的影响程序:单一列索引要相对低.

    4):在多条件查询时,联合索引效率要高.

    索引的使用范围:单一列索引可以出现在where 条件中的任何位置,而联合索引需要按一定的顺序来写.

    本文所用测试软件环境如下:SQL05

    DEMO:创建一个人员表,包含人员ID,姓名.在人员ID上创建一个聚集索引,在first_name和last_name上创建一个联合

    索引.

    create table person (id int, last_name varchar(30), first_name varchar(30))

    create unique clustered index person_id on person (id)

    create index person_name on person (last_name, first_name)

    在上例中,id上创建了聚集索引,下面的查询都会用了聚集索引.

    where id=1

    where id>1

    where id<1

    where id between 1 and n

    where id like ’1%’

    where id in(1,2,3…)

    说明: id 列出现在条件中的位置并不一定要求第一列,不受位置影响.

    不过下面的查询方式则不会用上聚集索引.

    where person_id +1=n

    where person_id like ‘%5′

    where person_id like ‘%5%’

    where person_id abs(15)

    联合索引列比起单一列索引最大的好处在于,对于多条件的查询它比起单一列索引更加精确.拿上面的人员表来说吧,如果

    要查询一个人的全名,只知道first_name是很难马上找到这个人的全名的,如果知道first_name和last_name则会非常容易找到.

    下面根据不同的条件与输出列顺序说明索引的应用.

    第一种情况:–条件和输出列和索引列顺序相同

    select last_name,first_name from person where last_name=’1′ and first_name=’1′

    stmtText

    Index Seek(OBJECT:([bdg_web_vaction].[dbo].[person].[person_name]),

    SEEK:([bdg_web_vaction].[dbo].[person].[last_name]=[@1]

    AND [bdg_web_vaction].[dbo].[person].[first_name]=[@2]) ORDERED FORWARD)

    结果:利用person_name联合索引查找

    第二种情况:–条件列与索引列顺序不同,但输出列相同

    select last_name,first_name from person where first_name=’1′ and last_name=’1′

    stmtText

    Index Seek(OBJECT:([bdg_web_vaction].[dbo].[person].[person_name]),

    SEEK:([bdg_web_vaction].[dbo].[person].[last_name]=[@2] AND [bdg_web_vaction].

    [dbo].[person].[first_name]=[@1]) ORDERED FORWARD)

    结果:利用person_name联合索引查找

    第三种情况:–条件列与输出列与索引列的顺序都不相同

    select first_name,last_name from person where first_name=’1′ and last_name=’1′

    Index Seek(OBJECT:([bdg_web_vaction].[dbo].[person].[person_name]),

    SEEK:([bdg_web_vaction].[dbo].[person].

    [last_name]=[@2] AND [bdg_web_vaction].[dbo].[person].[first_name]=[@1]) ORDERED FORWARD)

    结果:利用person_name联合索引查找

    第四种情况:–条件列在first_name和last_name中间加入另外一个条件

    SELECT id, first_name,last_name from person where first_name=’1′ AND id=1 and last_name=’1′

    Clustered Index Seek(OBJECT:([bdg_web_vaction].[dbo].[person].[person_id]),

    SEEK:([bdg_web_vaction].[dbo].[person].[id]=CONVERT_IMPLICIT(int,[@2],0)),

    WHERE:([bdg_web_vaction].[dbo].[person].[first_name]=[@1] AND [bdg_web_vaction].[dbo].[person].[las

    结果:不能利用person_name联合索引查找

    第五种情况:--在输出列中分开first_name和last_name

    SELECT first_name,id,last_name from person where first_name='1' and last_name='1'

    Index Seek(OBJECT:([bdg_web_vaction].[dbo].[person].[person_name]),

    SEEK:([bdg_web_vaction].[dbo].[person].

    [last_name]=[@2] AND [bdg_web_vaction].[dbo].[person].[first_name]=[@1])

    ORDERED FORWARD)

    结果:利用person_name联合索引查找

    第六种情况:条件列没有出现联合索引的第一列

    SELECT first_name,id,last_name from person where first_name=’1′

    SELECT first_name,last_name from person where first_name=’1′

    SELECT last_name ,first_name from person where first_name=’1′

    Index Scan(OBJECT:([bdg_web_vaction].[dbo].[person].[person_name]),

    WHERE:([bdg_web_vaction].[dbo].[person].[first_name]=[@1]))

    结果:不能利用person_name联合索引.

    第七种情况:–条件列出现联合索引的第一列

    SELECT first_name,id,last_name from person where last_name=’1′

    SELECT first_name,last_name from person where last_name=’1′

    SELECT last_name ,first_name from person where last_name=’1′

    Index Seek(OBJECT:([bdg_web_vaction].[dbo].[person].[person_name]),

    SEEK:([bdg_web_vaction].[dbo].[person].[last_name]=[@1]) ORDERED FORWARD)

    结果:利用person_name联合索引查找

    联合索引使用总结:

    1):查询条件中出现联合索引第一列,或者全部,则能利用联合索引.

    2):条件列中只要条件相连在一起,以本文例子来说就是:

    last_name=’1′ and first_name=’1′

    first_name=’1′ and last_name=’1′

    ,无论前后,都会利用上联合索引.

    3):查询条件中没有出现联合索引的第一列,而出现联合索引的第二列,或者第三列,都不会利用联合索引查询.

    单一列索引的应用总结:

    1):只要条件列中出现索引列,无论在什么位置,都能利用索引查询.

    两者的共同点:

    1):要想利用索引,都要符合SARG标准.

    2) :都是为了提高查询速度.

    3):都需要额外的系统开销,磁盘空间.

    补充说明: stmtText信息来产生,在查询语句前面加上:SET STATISTICS PROFILE on.可以通过运行它,来观察你的查询是否合理,这样才能真正做到优化.

    总结:即使表上创建了索引,但如果查询语句写的不科学的话(不符合SARG标准),也于事无补,要根据表索引情况来优化查询语句,如没有合适的索引可用,则要创建相应索引

    展开全文
  • # 索引种类:# 普通索引 加速查找# 主键索引 加速查找 不能重复 不能为空# 唯一索引 加速查找 不能重复# 联合索引(多列): 联合主键索引 联合唯一索引 联合普通索引# 无索引:从前到后依次查找# 有索引:额外的文件...

    # 索引种类:

    # 普通索引 加速查找

    # 主键索引 加速查找 不能重复 不能为空

    # 唯一索引 加速查找 不能重复

    # 联合索引(多列): 联合主键索引 联合唯一索引 联合普通索引

    # 无索引:从前到后依次查找

    # 有索引:额外的文件保存特殊的数据结构。查询快。插入更新慢

    # 索引种类之hash索引:

    # 会把内容转换成hash值+内容存储地址放到新建表里(存储位置是无序的,所以查找单值速度很快,范围查找会慢)

    # 索引种类之btree索引(常用):

    # 会把内容值转换成数字以二叉树形式存储

    # 创建普通索引:

    # 例1 create index 索引别名 on 表(列);

    # 例2 create table tablename(

    # id int not null auto_increment primary key,

    # name varchar(32) not null,

    # index 索引别名 (name) # 创建表结构时 创建普通索引

    # )

    # 创建唯一索引:

    # 例1 create unique index 索引别名 on 表(列);

    # 例2 create table tablename(

    # id int not null auto_increment primary key,

    # name varchar(32) not null,

    # unique 索引别名 (name) # 创建表结构时 创建唯一索引

    # )

    # 创建主键索引:

    # alter table 表名 add primary key(列)

    # 创建局部索引:

    # create index 索引别名 on 表(列(前多少字符作为索引))

    # 删除普通索引(删除生成的索引文件):

    # drop index 索引别名 on 表;

    # 删除唯一索引:

    # drop unique index 索引别名 on 表;

    # 覆盖索引(名词):

    # 在索引特殊数据结构文件里直接就能拿到想要的值(在索引文件中直接获取数据)

    # 例 id 为索引列:select id from userinfo where id='999';

    # 索引合并(名词)(没有联合索引的效率高):

    # 把多个单列索引合并使用

    # 例 id 和 email为索引列:select * from userinfo where id=999 and email='999@qq.com';

    # 最左前缀匹配:

    # create index in_name_email_id on userinfo(name,email,id);

    # select * from userinfo where name='xxx'; # 走索引

    # select * from userinfo where name='xxx' and id='xxx'; # 走索引

    # select * from userinfo where email='xxx' # 不走索引;

    # 命中索引(假设userinfo表里,id和email为索引列):

    # like 慢

    # select * from userinfo where email='xxx'; 命中

    # select * from userinfo where email like 'xxx'; 慢

    # 函数 慢

    # select * from userinfo where reverse(email)='xxx'; 慢

    # or 慢

    # select * from userinfo where id=xxx or name='xxx'; # 用or如果其中一列不为索引将会 慢

    # select * from userinfo where id=xxx or name='xxx' and email='xxx' # 命中, 因为这里会使用id和email,不会使用name查找

    # 类型不一致 慢

    # (如果email为varcha类型) select * from userinfo where email=999; # 却使用int查询的话,慢

    # != 对普通索引会 慢 (但如果该列是主键的话,!=还是会走索引)

    # > 普通索引会 慢,(主键,整数类型,还是会走索引)

    # order by (主键,还是会走索引)

    # select name from userinfo order by email desc; 慢

    # select email from userinfo order by email desc; 走索引

    # 其他注意事项:

    # 避免使用 select *

    # count(1)或count(列) 代替count(*)

    # 创建表时尽量是char代替varchar

    # 表的字段顺序固定长度的字段优先

    # 组合索引替代多个单列索引(经常使用多个条件查询时)

    # 尽量使用短索引

    # 使用连接(join)来代替子查询(sub-queries)

    # 连表时注意条件类型一致

    # 索引散列值(重复少)不适合建索引。例:性别不适合

    展开全文
  • 一、Mysql 中的索引索引:顾名思义用来检索、查找数据的key (字段)几种Mysql 中的常见索引分类:普通索引(联合索引)、唯一索引、主键索引、全文索引优点:使得查询数据变快缺点:更新数据时,也需要更新索引。...

    一、Mysql 中的索引

    索引:顾名思义用来检索、查找数据的key (字段)

    几种Mysql 中的常见索引分类:普通索引(联合索引)、唯一索引、主键索引、全文索引

    优点:使得查询数据变快

    缺点:更新数据时,也需要更新索引。所以更新速度变慢,占据磁盘空间

    注意:myisam中可以延迟更新索引

    1.1 Mysql中都有那些索引

    MySQL有哪些索引类型,这是个半开放式命题;

    从数据结构角度分:

    B+树索引、哈希索引、以及不常用的FULLTEXT索引(现在MyISAM和InnoDB引擎都支持了)和R-Tree索引(用于对GIS数据类型创建SPATIAL索引);

    从物理存储角度分:

    聚集索引(clustered index)、非聚集索引(non-clustered index);

    从逻辑角度分:

    主键索引、普通索引,或者单列索引、多列索引、唯一索引、非唯一索引等等。需要掌握这些不同概念之间的区别,例如主键索引和唯一索引的区别是什么。

    为什么InnoDB表最好要有自增列做主键;

    为什么需要设置双1才能保证主从数据的一致性;

    有几种binlog格式*,及其区别是什么;

    如何确认MySQL replication真正的复制延迟是多少;

    1.1.0 主键索引和唯一索引的区别

    主键是一种约束,唯一索引是一种索引,两者在本质上是不同的。

    主键创建后一定包含一个唯一性索引,唯一性索引并不一定就是主键。

    唯一性索引列允许空值,而主键列不允许为空值。

    主键列在创建时,已经默认为空值 + 唯一索引了。

    主键可以被其他表引用为外键,而唯一索引不能。

    一个表最多只能创建一个主键,但可以创建多个唯一索引。

    主键更适合那些不容易更改的唯一标识,如自动递增列、身份证号等。

    在 RBO 模式下,主键的执行计划优先级要高于唯一索引。 两者可以提高查询的速度。

    1.1.0.1 为什么innodb优先使用自增列做主键

    B+ 树的特点:

    (1)所有关键字都出现在叶子结点的链表中(稠密索引),且链表中的关键字恰好是有序的;

    (2)不可能在非叶子结点命中;

    (3)非叶子结点相当于是叶子结点的索引(稀疏索引),叶子结点相当于是存储(关键字)数据的数据层;

    如果我们定义了主键(PRIMARY KEY),那么InnoDB会选择主键作为聚集索引、如果没有显式定义主键,则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引、如果也没有这样的唯一索引,则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增,这个ROWID不像ORACLE的ROWID那样可引用,是隐含的)。(关于内置三个字段中的ROWID:全称- DB_ROW_ID,默认自增)点击此处了解

    数据记录本身被存于主索引(一颗B+Tree)的叶子节点上。这就要求同一个叶子节点内(大小为一个内存页或磁盘页)的各条数据记录按主键顺序存放,因此每当有一条新的记录插入时,MySQL会根据其主键将其插入适当的节点和位置,如果页面达到装载因子(InnoDB默认为15/16),则开辟一个新的页(节点)

    如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页

    如果使用非自增主键(如果身份证号或学号等),由于每次插入主键的值近似于随机,因此每次新纪录都要被插到现有索引页得中间某个位置,此时MySQL不得不为了将新记录插到合适位置而移动数据,甚至目标页面可能已经被回写到磁盘上而从缓存中清掉,此时又要从磁盘上读回来,这增加了很多开销,同时频繁的移动、分页操作造成了大量的碎片,得到了不够紧凑的索引结构,后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

    综上总结,如果InnoDB表的数据写入顺序能和B+树索引的叶子节点顺序一致的话,这时候存取效率是最高的,也就是下面这几种情况的存取效率最高:

    使用自增列(INT/BIGINT类型)做主键,这时候写入顺序是自增的,和B+数叶子节点分裂顺序一致;

    该表不指定自增列做主键,同时也没有可以被选为主键的唯一索引(上面的条件),这时候InnoDB会选择内置的ROWID作为主键,写入顺序和ROWID增长顺序一致;

    除此以外,如果一个InnoDB表又没有显示主键,又有可以被选择为主键的唯一索引,但该唯一索引可能不是递增关系时(例如字符串、UUID、多字段联合唯一索引的情况),该表的存取效率就会比较差。

    1.1.1 Myisam 下的非聚集索引

    关于B+Tree 内容

    Myisam 和 innodb 内容

    myisam 为mysql 5.5 版本前的默认数据库引擎:

    myisam 使用B+Tree 结构存储数据。只有(只支持)主键索引、表锁,不支持外键,不支持事务,B+Tree 所有的非叶子节点都只存储 key

    myisam 在叶子节点 data 域存储的是数据的地址,不是数据,当myisam引擎进行查找时,先根据索引在索引表中找到位置,去对应的data域拿到数据所在地址

    然后去该地址提取出数据

    1.1.2 innodb 下的聚集索引

    innodb 为mysql 5.5 版本后的默认数据库引擎.

    innodb 也使用 B+Tree 结构存储数据,与myisam不同的有:支持外键、行锁、事务,B+Tree 的所有非叶子节点一样只存储 key

    innodb 在叶子节点 data 域存储的是数据,叶子节点数据类型[key, data], 当 innodb引擎进行查找时,根据索引找到 data 位置,直接将data 中的数据取出

    innodb 辅助索引(非主键索引外)进行查找时,根据辅助索引查找到 主键索引,再拿着主键索引到主键索引表中查找到 data 取出data。注意辅助索引并不存数据,

    二、什么时候使用索引

    1. 经常使用在 where、group by 、order by 的后面查询的字段,可设置索引

    减少使用子查询

    多使用limit 这样数据量被限制较小,能提高查询速度

    适度增加冗余字段,减少跨表查询,常用数据和不常用数据分表one2one保存

    尽量不使用 * 查询数据,需要什么字段就查什么字段

    别再数据库里面使用函数计算

    尽量根据索引来检索数据

    核心业务中别使用 like 语句模糊查询

    将区分度字段写在where前面

    三、添加索引

    创建表时添加索引:

    create table tb_name(

    --> id int not null auto_increment primary key,

    --> name char(32) not null,

    --> identify_num bigint not null,

    --> unique key(identify_num),

    --> index name_index (name(32))

    --> );

    更新表结构时添加:alter table tb_name add

    alter table tb_name add index name_index(name(32));

    create unique index identify_index on tb_name(identify_num)

    四、索引失效

    like会导致索引失效

    like "%name" //失效的

    like "name%" //不失效

    字段使用函数来修饰

    例:select * from u1 where md5(name) = 'j'

    联合索引中字段,将区分度较大的字段放大前面

    展开全文
  • 什么是索引 索引可以简单理解为是一种排好序的快速查找数据结构 索引对于排序和条件有很大的影响 ...唯一索引: 索引列的值必须唯一,但允许有空值 复合索引: 一个索引包含多个列 索引语法 创建: create index
  • mysql索引的认识

    2019-10-22 11:47:50
    Sql优化之索引认识问题查询sql 确认要添加的索引讲讲索引的分类?你知道哪些?从物理存储角度:从数据结构角度:从逻辑角度:如何避免回表查询...为什么唯一索引更新不使用 Change Buffer?mysql索引是什么结构的?...
  • 前言从hbase的查询方式来说,很多情况下并不知道具体的rowkey,于是hbase的查询速度会很,于是引入今天的文章。为什么需要二级索引?对于HBase而言,如果想精确地定位到某行记录,唯一的办法是通过rowkey来查询。...
  • 数据库索引

    2019-07-04 10:08:45
    下面是唯一索引,插入时:无则插入,有则插不上;查询速度加快,插入速度变; 【如果要实现"有则更新,无则插入"的功能可以使用beego的readOrCteate函数,索引就是和主键ID一样,不会有其他作用】 索引: ...
  • mysql 索引

    2019-10-04 18:10:46
    索引 思考:在图书馆中是如何找到一本书的? 一般的应用系统对比数据库的读写比例在10:1左右,而且插入操作和更新...主键和唯一索引,都是索引,可以提高查询速度 主键是数据物理存储的位置 索引会单独创建...
  • Mysql 索引问题集锦

    2018-09-30 23:27:00
    几种Mysql 中的常见索引分类:普通索引(联合索引)、唯一索引、主键索引、全文索引 优点:使得查询数据变快 缺点:更新数据时,也需要更新索引。所以更新速度变,占据磁盘空间 注意:myisam中可以延迟更新索引 ...
  • sql 索引

    2018-05-31 13:07:56
    对一个数据库较大的表创建合适的索引,由此索引唯一性,可以在很大程度上加快数据检索速度、加快表与表之间的连接、减少查询中分组与排序的事件,从而提高系统性能。但是索引并不是多多益善,因此创建和维护需要...
  • 学习-索引

    2015-08-27 13:19:18
    索引的使用场景:1....3.唯一键约束,有相对应的索引存在索引的属性:索引与表中数据独立,增加或删除索引对表数据库无影响,只不过对应数据查询速度变索引会降低dml操作性能(需要额外更新索引数据)...
  • MySQL-常见索引及使用

    2019-08-22 21:59:51
    一、认识索引 如果没有索引查询数据只能整表扫描,数据量大的时候速度很。通过建立索引,能够显著提升数据查询速度,这是索引最大的优点。但建立索引也会降低数据插入、删除、更新的速度,因为... 唯一索引(uniq...
  • 什么是索引? 索引(Index)是帮助MySQL高效获取数据的数据结构。提取句子主干,就可以得到索引的本质:==索引是一种数据结构。...1、UNIQUE,唯一索引,可以为null,但不能重复 2、INDEX 普通索引 3、Promary ...
  • 位图索引实现适用与不经常进行更新,插入,删除的列 。更适合于具有较少唯一值的静态列,典型的例子就是在数据仓库应用中,在一张包含人口统计信息的表中性别列是一个很好的例子。 B-树索引在各类应用中得到了广泛的...
  • 索引思考:在图书馆中是如何找到一本书的?一般的应用系统对比数据库的...索引主键和唯一索引,都是索引,可以提高查询速度主键是数据物理存储的位置索引会单独创建一个目录,对应数据的位置索引分单列索引和组合索...
  • 索引共分为:1、主键索引:特性:加速查找、不能为空、不能重复2、普通索引:特性:加速查找3、唯一索引:特性:加速查找、可以为空、不能重复4、联合索引:特征:(多列)联合主键索引、联合唯一索引、联合普通索引.....
  • [ztjSQL]索引

    2007-08-24 15:00:00
    索引可以加快查询速度,唯一索引可以确定唯一性等等,但是不索引越多越好呢?显然不是。对于经常要更新的表,如果多了,反而会,因为要插入之前,先要扫描下索引,就是你在字典中插(注意不是这个查)个字,你要在偏旁部...
  • MySQL(13):索引

    2018-06-27 20:38:11
    1.索引的引入 思考:在图书馆中,如何找到一本书呢? 一般的应用系统,读写比例在10:1左右,而且插入操作和更新操作很少出现性能问题,...主键和唯一索引,都是索引,可以提高查询速度。 2.建索引时选择列的数据...
  • ---恢复内容开始--- python/MySQL(索引、执行计划、BDA、分页) MySQL索引: 所谓索引的就是具有(约束和加速查找的一种方式)  创建索引的缺点是对数据进行...3、唯一索引:  特性:加速查找、可以为...
  • key分为主键,唯一和外键。... 索引虽可以加快查询速度,但其也加重了存储开销,且导致数据的更新(插入,修改,删除)速度变。 mysql的索引原则是:最左前缀 对于索引的优化,可以考虑以下几个注意点儿...
  • 唯一索引不一定比普通索引快, 还可能.查询时, 在未使用limit 1的情况下, 在匹配到一条数据后, 唯一索引即返回, 普通索引会继续匹配下一条数据, 发现不匹配后返回. 如此看来唯一索引少了一次匹配, 但实际上这个消耗...
  • 数据库上使用的唯一操作是“Select *”,因为我需要所有列(无插入或更新) . 当我查询数据库时,结果集很大时响应时间很(获取~18,000条记录需要160秒) .我已经多次改进了索引的使用,这是我得到的最快的响应时间 ....
  • 遇到问题 某一接口为数据接收接口,需要大量插入数据,但是同事反馈接口返回速度过 定位问题 ...去除原来的唯一索引,在阿里云查看单表可以节省三分之一的内存 把insert update 语句改为 insert...
  • 如taobao/qq的系统比拼byte系统,关键在于索引的命中率,缓存,系统的水平扩展。 如果数据库很少,你搞这么多byte能提高性能? 如果数据库很大,你搞这么多byte不兼容索引不兼容缓存,不是害自已吗? 如果数据库...
  • 唯一索引比普通索引快吗, 为什么唯一索引不一定比普通索引快, 还可能.查询时, 在未使用 limit 1 的情况下, 在匹配到一条数据后, 唯一索引即返回, 普通索引会继续匹配下一条数据, 发现不匹配后返回. 如此看来唯一...
  • 唯一索引比普通索引快吗, 为什么唯一索引不一定比普通索引快, 还可能.查询时, 在未使用 limit 1 的情况下, 在匹配到一条数据后, 唯一索引即返回, 普通索引会继续匹配下一条数据, 发现不匹配后返回. 如此看来唯一...
  • 唯一索引不一定比普通索引快, 还可能. 查询时, 在未使用limit 1的情况下, 在匹配到一条数据后, 唯一索引即返回, 普通索引会继续匹配下一条数据, 发现不匹配后返回. 如此看来唯一索引少了一次匹配, 但实际上这个...
  •  唯一索引不一定比普通索引快, 还可能。 查询时, 在未使用limit 1的情况下, 在匹配到一条数据后, 唯一索引即返回, 普通索引会继续匹配下一条数据, 发现不匹配后返回. 如此看来唯一索引少了一次匹配, 但实际上这...

空空如也

空空如也

1 2 3
收藏数 56
精华内容 22
关键字:

唯一索引更新慢