精华内容
下载资源
问答
  • mysql数据库索引

    2019-07-19 21:58:39
    mysql数据库索引 1.索引 一. 什么是索引: 索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。 2.作用是什么? 索引的作用相当于图书的目录,可以根据目录...

    mysql数据库索引

    Mysql索引详解
    1.索引
    一. 什么是索引:
    索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。
    2.作用是什么?
    索引的作用相当于图书的目录,可以根据目录中的页码快速找到所需的内容,提高性能(查询速度) 打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。
    3.索引和普通查询的区别?
    比如,查询一个学校所有学生的成绩,将不及格的学生姓名和成绩显示下来。
    普通查询方法: select name,[成绩字段] from [表名] where [成绩字段]<60;
    查询的过程: 数据库将所有学生的成绩全部查询一遍,将不及格的学生显示出来。
    数据量小看不出区别,如果数据量达到百万,需要将所有学成全部查出,耗时非常大。
    索引查询: 首先建立一个索引,create index 索引名 on 表名(成绩字段);
    如果数据多,创建索引时会消耗大量时间,和内存。
    再次查询select name,(成绩字段) from (表名) where 成绩字段<60;
    查询过程: 索引将表中的数据排序,查询时列表有成绩10,30,60,70,100。
    查询时,查到60的时候,将不再向后查询。节省时间。
    4.索引是如何排序的?1.索引的本质是排序:有待研究,和数据结构有关系

    5.索引的优点和缺点:
    有了索引.对于记录数量很多的表,可以提高查询速度.
    缺点 索引是占用空间的. 索引会影响update insert delete速度
    6.什么样的字段不适合创建索引
    1.数据量较少
    2.定义的text,image,bit数据类型的列不应该增加索引。
    3.当修改性能远远大于检索性能时,不应该创建索 引
    。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因此,当修改性能远远大于检索性能时,不应该创建索引。
    4、唯一性太差的字段不适合建立索引什么是唯一性太差的字段。
    如状态字段、类型字段。那些只存储固定几个值的字段,例如用户登录状态、消息的status等。这个涉及到了索引扫描的特性。例如:通过索引查找键值为A和B的某些数据,通过A找到某条相符合的数据,这条数据在X页上面,然后继续扫描,又发现符合A的数据出现在了Y页上面,那么存储引擎就会丢弃X页面的数据,然后存储Y页面上的数据,一直到查找完所有对应A的数据,然后查找B字段,发现X页面上面又有对应B字段的数据,那么他就会再次扫描X页面,等于X页面就会被扫描2次甚至多次。以此类推,所以同一个数据页可能会被多次重复的读取,丢弃,在读取,这无疑给存储引擎极大地增加了IO的负担。
    7.什么时候适合创建索引:
    1、较频繁地作为查询条件的字段这个都知道。
    什么是叫频繁呢?分析你执行的所有SQL语句。最好将他们一个个都列出来。然后分析,发现其中有些字段在大部分的SQL语句查询时候都会用到,那么就果断为他建立索引。
    2、更新太频繁地字段不适合创建索引
    当你为这个字段创建索引时候,当你再次更新这个字段数据时,数据库会自动更新他的索引,所以当这个字段更新太频繁地时候那么就是不断的更新索引,性能的影响可想而知。大概被检索几十次会更新一次的字段才比较符合建立索引的规范。而如果一个字段同一个时间段内被更新多次,那么果断不能为他建立索引。
    7.创建索引注意事项:
    1.索引要创建在where和join用到的字段上.
    2.以下操作符号可以应用索引:<,<=,=,>,>=,BETWEEN,IN,LIKE不以%_开头
    以下需要注意不使用索引<>, NOT IN, LIKE %_开头<> 可以用 a>1 or a<3 代替,NOT IN 可以用NOT exists代替
    3.在使用max() min()时最好加上索引.
    4.单索引要创建在确实需要的地方.多列索引 有最佳左前缀特性 所以尽量左边的字段是最常用的.
    6.索引不会包括有NULL值,就是有NULL,索引就失效
    7.使用短索引,一个字段字太多,可以建立部分索引,只取前十个字.节约空间.
    8.不要在列上运算.例如:where MD5(“password”) = “myz”
    9.explain select * from myz 来测试一下语句的效率…输入 explain select * from customer; 后,出现一张表,个行的意思如下:table-显示此行数据属于哪张表;type-重要的一列,显示使用了何种连接,从好到差依次为const、eq_ref、ref、 range、index、all;possible_keys-可以应用在这张表中的索引,如果为null,则表示没有可用索引;key-实际使用的索 引,如为null,表示没有用到索引;key_len-索引的长度,在不损失精确度的情况下,越短越好;ref-显示索引的哪一列被使用了,如果可能的 话,是个常数;rows-返回请求数据的行数;extra-关于mysql如何解析查询的额外信息,下面会详细说明。
    type的描述:system-表只有一行,这是const连接类型的特例;const-表中一个记录的最大值能够匹配这个查询(索引可以是主 键或唯一索引)。因为只有一行,这个值实际就是常数,因为mysql先读这个值,再把它当作常数对待;eq_ref-从前面的表中,对每一个记录的联合都 从表中读取一个记录。在查询使用索引为主键或唯一索引的全部时使用;ref-只有使用了不是主键或唯一索引的部分时发生。对于前面表的每一行联合,全部记 录都将从表中读出,这个连接类型严重依赖索引匹配记录的多少-越少越好;range-使用索引返回一个范围中的行,比如使用>或<查找时发 生;index-这个连接类型对前面的表中的每一个记录联合进行完全扫描(比all好,因为索引一般小于表数据);all-这个连接类型多前面的表中的每 一个记录联合进行完全扫描,这个比较糟糕,应该尽量避免。
    extra行的描述:distinct-mysql找到了域行联合匹配的行,就不再搜索了;not exists-mysql优化了left join,一旦找到了匹配left join的行,就不再搜索了;range checked for each-没找到理想的索引,一次对于从前面表中来的每一个行组合;record(index map: #)-检查使用哪个索引,并用它从表中返回行,这是使用索引最慢的一种;using filesort-看到这个就需要优化查询了,mysql需要额外的步骤来发现如何对返回的行排序。他根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。using index-列数据是从单单使用了索引中的信息而没有读取实际行的表返回的,这发生在对表的全部的请求列都是同一个索引时;using temporary-看到这个就需要优化查询了,mysql需要创建一个临时表来查询存储结果,这通常发生在多不同的列表进行order by时,而不是group by;where used-使用了where从句来限制哪些行将与下一张表匹配或是返回给用户。如不想返回表中用的全部行,并连接类型是all或index,这就会发生,也可能是查询有问题。
    10查看索引信息show index from tablename;
    8.索引的类型及创建例子:
    1.PRIMARY KEY (主键索引)
    mysql> alter table table_name add primary key ( column )
    2.UNIQUE 或 UNIQUE KEY (唯一索引)
    mysql> alter table table_name add unique (column)
    3.FULLTEXT (全文索引)
    mysql> alter table table_name add fulltext (column )
    4.INDEX (普通索引)
    mysql> alter table table_name add index index_name ( column )
    5.多列索引 (聚簇索引)
    mysql> alter table table_name add index index_name ( column1, column2, column3 )

    展开全文
  • MySQL数据库索引

    千次阅读 2018-09-23 09:31:41
    数据库有哪些索引 唯一索引 聚簇索引与非聚簇索引 全文索引 使用索引一定能提高查询性能吗? 哪些情况下设置了索引但是无法使用 哪些情况下需要设置索引、哪些情况下不需要 什么情况下应该使用组合索引而非...

     

    目录

    索引是什么

    索引有哪些结构/索引常见的模型

    B+树索引

    数据库有哪些索引

    唯一索引

    聚簇索引与非聚簇索引

    全文索引

    索引的最左前缀原则

    索引下推

    使用索引一定能提高查询性能吗?

    哪些情况下设置了索引但是无法使用

    哪些情况下需要设置索引、哪些情况下不需要

    什么情况下应该使用组合/联合索引而非单独索引

    MySQL中索引是如何组织数据的存储的

    Mysql索引原理

    Mysql是如何根据索引查询数据的

    普通索引和唯一索引,应该怎么选择?


    索引是什么

    索引是对数据库表中一个或多个列的值进行排序的结构,是帮助MySQL高效获取数据的数据结构

    你也可以这样理解:索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中,索引允许用户不必翻阅完整本书就能迅速地找到所需要的信息。在数据库中,索引也允许数据库程序迅速地找到表中的数据,而不必扫描整个数据库。

    MySQL数据库几个基本的索引类型:普通索引、唯一索引、主键索引、全文索引

    1.索引加快数据库的检索速度

    2.索引降低了插入、删除、修改等维护任务的速度

    3.唯一索引可以确保每一行数据的唯一性

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

    5.索引需要占物理和数据空间

     

    索引有哪些结构/索引常见的模型

    1. 哈希表:一种以键-值(key-value)存储数据的结构,我们只要输入待查找的值即key,就可以找到其对应的值即Value。哈希的思路很简单,把值放在数组里,用一个哈希函数把key换算成一个确定的位置,然后把value放在数组的这个位置。如果出现hash冲突,则在冲突的value位置使用链表进行连接。

        适用场景:等值查询,Memcached及其他一些NoSQL引擎

        

    2. 有序数组

        适用场景:只适用于静态存储引擎。用于等值查询和范围查询(ID值必须是递增的)

        

    3. 搜索树:左子节点小于父节点、父节点小于右子节点。Innodb使用B+树,为什么数据库使用B+树作为索引?

     

    问:为什么采用B+树?这和Hash索引比较起来有什么优缺点吗?

    答:因为Hash索引底层是哈希表,哈希表是一种以key-value存储数据的结构,所以多个数据在存储关系上是完全没有任何顺序关系的,所以,对于区间查询是无法直接通过索引查询的,就需要全表扫描。所以,哈希索引只适用于等值查询的场景。而B+树是一种多路平衡查询树,所以他的节点是天然有序的(左子节点小于父节点、父节点小于右子节点),所以对于范围查询的时候不需要做全表扫描

    1、哈希索引适合等值查询,但是无法进行范围查询 

    2、哈希索引没办法利用索引完成排序 

    3、哈希索引不支持多列联合索引的最左匹配规则 

    4、如果有大量重复键值的情况下,哈希索引的效率会很低,因为存在哈希碰撞问题

     

    B+树索引

    我们举个例子,假设我们有一个主键列为ID的表,表中有字段k,并且在k上有索引。

    表中R1~R5的(ID,k)值分别为(100,1)、(200,2)、(300,3)、(500,5)和(600,6),两棵树的示例示意图如下。

    主键索引的叶子节点存的是整行数据。在InnoDB里,主键索引也被称为聚簇索引(clustered index)。

    非主键索引的叶子节点内容是主键的值。在InnoDB里,非主键索引也被称为二级索引(secondary index)。

        PS:主键长度越小,普通索引的叶子节点就越小,普通索引占用的空间也就越小。

    基于主键索引和普通索引的查询有什么区别?

    • 如果语句是select * from T where ID=500,即主键查询方式,则只需要搜索ID这棵B+树;
    • 如果语句是select * from T where k=5,即普通索引查询方式,则需要先搜索k索引树,得到ID的值为500,再到ID索引树搜索一次。这个过程称为回表。

    也就是说,基于非主键索引的查询需要多扫描一棵索引树。因此,我们在应用中应该尽量使用主键查询。

    索引维护

        对于主键不是递增的表,在插入数据时,如新插入数据ID值为400,则需要逻辑上挪动后面的数据,空出位置。

        而更糟的情况是,如果R5所在的数据页已经满了,根据B+树的算法,这时候需要申请一个新的数据页,然后挪动部分数据过去。这个过程称为页分裂

        除了性能外,页分裂操作还影响数据页的利用率。原本放在一个页的数据,现在分到两个页中,整体空间利用率降低大约50%。当

        然有分裂就有合并。当相邻两个页由于删除了数据,利用率很低之后,会将数据页做合并。合并的过程,可以认为是分裂过程的逆过程。

     

    数据库有哪些索引

    在MySql数据库中,有四种索引:聚集索引(主键索引)(聚簇索引)、普通索引唯一索引以及全文索引(FUNLLTEXT INDEX)

    索引又可分为聚簇索引非聚簇索引两种

     

    唯一索引

    一种索引,不允许具有索引值相同的行,从而禁止重复的索引或键值。系统在创建该索引时检查是否有重复的键值,并在每次使用 INSERT 或 UPDATE 语句添加数据时进行检查。

    CREATE UNIQUE CLUSTERED INDEX myclumn_cindex ON mytable(mycolumn)

     

    聚簇索引与非聚簇索引

    可以理解为主键索引与普通索引

    聚簇索引:是对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法。特点是存储数据的顺序和索引顺序一致,且一个表只能有一个聚簇索引,因为物理存储只能有一个顺序。主键索引一般都是聚簇索引

    非聚簇索引:表数据存储顺序与索引顺序无关。对于非聚簇索引,叶结点包含索引字段值及指向数据页数据行的逻辑指针,其行数量与数据表行数据量一致。非聚簇索引记录的物理顺序与逻辑顺序没有必然的联系,与数据的存储物理结构没有关系;一个表对应的非聚簇索引可以有多条,根据不同列的约束可以建立不同要求的非聚簇索引;

     

    一般情况下主键会默认创建聚簇索引,且一张表只允许存在一个聚簇索引。因为物理存储只能有一个顺序。

    聚簇索引的叶子节点就是数据节点(Innodb的B+树的主键对应的数据节点),而非聚簇索引的叶子节点仍然是索引节点,只不过有指向对应数据块的指针。

    聚簇索引主键的插入速度要比非聚簇索引主键的插入速度慢很多。

    相比之下,聚簇索引适合排序,非聚簇索引不适合用在排序的场合。因为聚簇索引本身已经是按照物理顺序放置的,排序很快。非聚簇索引则没有按序存放,需要额外消耗资源来排序。

    建立聚簇索引的语句:

    CREATE CLUSTER INDEX index_name ON table_name(column_name1,...);

     

    问:主键索引查询只会查一次,而非主键索引一定需要回表查询多次吗?

    答:通过覆盖索引也可以只查询一次

    覆盖索引(covering index)指一个查询语句的执行只用从索引中就能够取得,不必从数据表中读取。也可以称之为实现了索引覆盖。

    当一条查询语句符合覆盖索引条件时,MySQL只需要通过索引就可以返回查询所需要的数据,这样避免了查到索引后再返回表操作,减少I/O提高效率。

    如,表covering_index_sample中有一个普通索引 idx_key1_key2(key1,key2)。

    当我们通过SQL语句:select key2 from covering_index_sample where key1 = 'keytest';的时候,就可以通过覆盖索引查询,无需回表。

    注:如果这个覆盖索引是一个前缀索引,那么它依然需要回表,因为系统并不确定前缀索引的定义是否截断了完整信息。

     

    问:以下重建索引的步骤是否存在问题?

    重建普通索引 k

    alter table T drop index k;
    alter table T add index(k);
    

    重建主键索引

    alter table T drop primary key;
    alter table T add primary key(id);

    答:重建索引k的做法是合理的,可以达到省空间的目的。但是,重建主键的过程不合理。不论是删除主键还是创建主键,都会将整个表重建。所以连着执行这两个语句的话,第一个语句就白做了。这两个语句,可以用这个语句代替 : alter table T engine=InnoDB。

     

    全文索引

    全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。它能够利用【分词技术】等多种算法智能分析出文本文字中关键词的频率和重要性,然后按照一定的算法规则智能地筛选出我们想要的搜索结果。

     

    select * from 表名 where 标题 like '%xxx%' or 内容 like '%xxx%' or 作者 like '%xxx%';

    这种搜索效率无比底下

     

    全文索引是为了使得“关键词搜索”功能更加的高效能。

     

    我们有这么一张数据表: 

    文章id 文章标题 文章内容

    1 超级塞亚人  我是超级塞亚人我喜欢吃苹果,我不是233大国的人,也不是地球人

    2 我233大国威武,我233大国13亿人,我233大国

    3 我喜欢游泳 游泳有很多好方法

    4 动画片 我儿子喜欢看动画片,尤其是七龙珠,因为里面有塞亚人,而且塞亚人喜欢吃苹果,他们不是地球人

    5 运动 我喜欢运动,喜欢跑步,喜欢游泳,喜欢健身,喜欢xxoo

    6 打炮 我是一个二战的老兵,这是我的回忆录,我最幸福的时光就是在233大国吃着苹果打炮

    7 。。。  

    8 。。。  

    9 。。。  

     

    然后,根据以上的文章内容,如果建立了一个索引文件(这里忽略索引文件的数据结构,仅仅以一种易于理解的方式呈现): 

    关键词   文章id

    塞亚人    1,4

    苹果      1,4,6

    233大国      1,2,6

    地球        1,4

    游泳        3,5

    七龙珠      4

    喜欢     1,4,5,6   

    那么当我想搜索  “塞亚人”的时候,这个索引文件直接告诉我在文章id为1和4的文章里有这个词。 

     

    这个索引文件就是“全文索引”。

     

    如何使用全文索引和分词的方式来帮助优化你的搜索呢?

    需要工作的程序:索引程序,分词程序,数据库。 

    工作原理: 

    1、索引程序从数据库读取数据,比如上面例子中的数据表,索引程序通过sql语句:select 文章id,文章标题,文章内容 from 文章表.获得文章的相关数据 

    2、索引程序对需要索引的内容进行“分词”,而这里的分词就是调用分词程序啦! 

    3、索引程序对分好词的一个个词条加入索引文件。

     

    在你写的代码里,原来到数据库----like %xxx%-----的语句就变成了到索引文件里去查找,从而找到相应的数据(这点相信你已经理解啦!)

     

    创建全文索引的两种方法:

    1.在建表语句中

    2.在已知表中

    ALTER TABLE article ADD FULLTEXT INDEX fulltext_article(title,content);

     

     

    具体如何使用全文索引呢?

    不用全文索引时的写法:SELECT * FROM article WHERE content LIKE ‘%查询字符串%’;

    使用全文索引:SELECT * FROM article WHERE MATCH(title,content) AGAINST (‘查询字符串’);

     

    注意:

    1、MySql自带的全文索引只能对英文进行全文检索,目前无法对中文进行全文检索。如果需要对包含中文在内的文本数据进行全文检索,我们需要采用Sphinx(斯芬克斯)/Coreseek技术来处理中文。

    2、使用MySql自带的全文索引时,如果查询字符串的长度过短将无法得到期望的搜索结果。MySql全文索引所能找到的词默认最小长度为4个字符。另外,如果查询的字符串包含停止词,那么该停止词将会被忽略。

    3、如果可能,请尽量先创建表并插入所有数据后再创建全文索引,而不要在创建表时就直接创建全文索引,因为前者比后者的全文索引效率要高。

     

    索引的最左前缀原则

    在MySQL建立联合索引时会遵守最左前缀匹配原则,即最左优先,在检索数据时从联合索引的最左边开始匹配。

     

    索引下推

    Index Condition Pushdown (ICP) ,Mysql 5.6添加,用于优化数据查询。

    索引条件下推优化可以减少存储引擎查询基础表的次数,也可以减少MySQL服务器从存储引擎接收数据的次数。 

    用下面这种场景进行介绍

    假设有如下查询语句:select * from tuser where name like '张%' and age=10 and ismale=1;  (有联合索引 name,age)

    我们知道了前缀索引规则,所以这个语句在搜索索引树的时候,只能用 “张”

    以下是Mysql 5.6 之前的查询流程:

    以下是Mysql 5.6 时的查询流程:(使用了索引下推

    InnoDB在(name,age)索引内部就判断了age是否等于10,对于不等于10的记录,直接判断并跳过。在我们的这个例子中,只需要对ID4、ID5这两条记录回表取数据判断,就只需要回表2次。

     

    使用索引一定能提高查询性能吗?

    通常,通过索引查询数据比全表扫描要快,但是我们也必须注意到它的代价.

    索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改。这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5次的磁盘I/O.

    索引不但会使得插入和修改的效率降低,而且在查询的时候,有一个查询优化器,太多的索引会让优化器困惑,可能没有办法找到正确的查询路径,从而选择了慢的索引。

    索引范围查询(INDEX RANGE SCAN)适用于两种情况:

        1.基于一个范围的检索,一般查询返回结果集小于表中记录数的30%

        2.基于非唯一性索引的检索

        3.直接晋升为覆盖索引,避免多次查表

     

    哪些情况下设置了索引但是无法使用

    根本原因是查询优化器决定不使用索引:

    一条SQL语句的查询,可以有不同的执行方案,至于最终选择哪种方案,需要通过优化器进行选择,选择执行成本最低的方案。在一条单表查询语句真正执行之前,MySQL的查询优化器会找出执行该语句所有可能使用的方案,对比之后找出成本最低的方案。这个成本最低的方案就是所谓的执行计划。优化过程大致如下:

    1、根据搜索条件,找出所有可能使用的索引 

    2、计算全表扫描的代价 

    3、计算使用不同索引执行查询的代价 

    4、对比各种执行方案的代价,找出成本最低的那一个

    有时候查询语句没有按照索引的要求来也会导致无法使用索引,如下:

    1. 对单字段建了索引,where条件多字段。
    2. 建立组合索引,where条件单字段。与上面情况正好相反。INDEX(a,b,c),当条件为a或a,b或a,b,c或a,c时都可以使用索引,但是当条件为b,c时将不会使用索引。也就是说不是使用的第一部分,则不会使用索引。如果是INDEX(a,b),即使查询的where是b,a,由于sql优化器的优化作用,会把b,a换成a,b,这样就可以走索引了。如果是index(a,b,c),查询是(a,b,c,d)不会走索引
    3. 条件中用or,即使其中有条件带索引,也不会使用索引查询(这就是查询尽量不要用or的原因,用in)(注意:使用or,又想索引生效,只能将or条件中的每个列都加上索引,这样查询时每个列都会单独使用它们自己的索引
    4. like的模糊查询的模糊词在字符串前面,比如以%或_开头,索引失效。
    5. 在使用不等于(is null、is not null、!= 、<>)的时候无法使用索引会导致全表扫描。
    6. 类型错误,如字段类型为varchar,where条件用number。
    7. 对索引应用内部函数,这种情况下应该建立基于函数的索引。
    8. 索引列不能是表达式(id+1=5)的一部分,也不能是函数的参数
    9. 如果MySQL预计使用全表扫描要比使用索引快,则不使用索引

     

    哪些情况下需要设置索引、哪些情况下不需要

    需要:

    1).主键自动建立唯一索引
    2).频繁作为查询条件的字段应该创建索引
    3).查询中与其它表关联的字段,外键关系建立索引
    4).单键/组合索引的选择问题(在高并发下倾向创建组合索引)
    5).查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
    6).查询中统计或者分组字段

     

    不需要:

    1).表记录太少
    2).经常增删改的表(因为不仅要保存数据,还要保存一下索引文件) 索引本来是一种事先在写的阶段形成一定的数据结构,从而使得在读的阶段效率较高的方式,但是如果一个字段是写多读少,则会降低写的速度。
    3).数据重复且分布平均的表字段(比如性别),因此应该只为最经常查询和最经常排序的数据列建立索引。

    4).where条件里用不到的字段不创建索引

     

    什么情况下应该使用组合/联合索引而非单独索引

    假设有条件语句A=a AND B=b,如果A和B是两个单独的索引,在AND条件下只有一个索引起作用,对于B则要逐个判断,而如果使用组合索引(A, B),只要遍历一棵树就可以了,大大增加了效率。但是对于A=a OR B=b,由于是 或 的关系,因而组合索引是不起作用的,此时可以使用单独索引,这个时候,两个索引可以同时起作用。

     

    在建立联合索引的时候,如何安排索引内的字段顺序?

        评估标准是:索引的复用能力。因为可以支持最左前缀,所以当已经有了(a,b)这个联合索引后,一般就不需要单独在a上建立索引了。

        因此,第一原则是,如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的。

        那么,如果既有联合查询,又有基于a、b各自的查询呢?查询条件里面只有b的语句,是无法使用(a,b)这个联合索引的,这时候你不得不维护另外一个索引,也就是说你需要同时维护(a,b)、(b) 这两个索引。

        这时候,我们要考虑的原则就是空间了。也就是说,如果b的大小是比较小的,如boolean、int类型, 那么可以再多建立一个b索引

     

    下面通过一个例子来加深理解

    假设有这么一个表:

    CREATE TABLE `geek` (
      `a` int(11) NOT NULL,
      `b` int(11) NOT NULL,
      `c` int(11) NOT NULL,
      `d` int(11) NOT NULL,
      PRIMARY KEY (`a`,`b`),
      KEY `c` (`c`),
      KEY `ca` (`c`,`a`),
      KEY `cb` (`c`,`b`)
    ) ENGINE=InnoDB;

     

    有以下经常使用的查询语句
    select * from geek where c=N order by a limit 1;
    select * from geek where c=N order by b limit 1;

     

    这里我们需要思考,ca与cb索引是否都是必要的?

        索引 ca 的组织是先按c排序,再按a排序,同时记录主键(b),根据最左前缀原则,实际上,ca索引的功能同c索引的功能是差不多的,因此可以得出ca索引不是必要的

        索引 cb 的组织是先按c排序,再按b排序,同时记录主键(a),因此该索引需要保留

     

     

    MySQL中索引是如何组织数据的存储的

    假如有如下数据表:

    对于表中每一行数据,索引中包含了last_name、first_name、dob列的值,下图展示了索引是如何组织数据存储的。

    https://upload-images.jianshu.io/upload_images/175724-3ba760afbae4a52d.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/700

    可以看到,索引首先根据第一个字段来排列顺序,当名字相同时,则根据第三个字段,即出生日期来排序,正是因为这个原因,才有了索引的“最左原则”。

     

     

    Mysql索引原理

    Mysql是如何根据索引查询数据的

     

    普通索引和唯一索引,应该怎么选择?

    在介绍这两者的区别之前,我们先来介绍change buffer:

    什么是change buffer?
        当需要更新一个记录,就是要更新一个数据页:

        1. 如果数据页在内存中(buffer pool中时)就直接更新
        2. 如果这个数据页还没有在内存中(没有在buffer pool中)。InooDB 会将这些更新操作缓存在 change buffer 中。在下次查询需要访问这个数据页时,将数据页读入内存,然后执行 change buffer 中与这个页有关的操作
        将 change buffer 中的操作应用到原数据页,得到最新结果的过程称为 merge,它可以避免大量的磁盘随机访问I/O,merge的流程如下(并不会直接把数据写会磁盘):

               1、从磁盘读入数据页到内存(老版本的数据页);

               2、从change buffer里找出这个数据页的change buffer 记录(可能有多个),依次应用,得到新版数据页;

               3、写redo log。这个redo log包含了数据的变更和change buffer的变更。

        而唯一索引的更新就不能使用 change buffer:对于唯一索引,所有的更新操作都要先判断这个操作是否违反唯一性约束。那么必须将数据页读入内存才能判断。比如,要插入(4,400) 这个记录,就要先判断现在表中是否已经存在 k=4 的记录

        都已经读入内存中了,那直接更新内存会更快,没有必要使用change buffer了。

        所以,只有普通索引才能使用change buffer,考虑使用普通索引还是唯一索引,如果能保证不会数据重复,那么最好使用普通索引(可以使用change buffer,且两类索引查询能力没有区别)

     

    注意:不是所有的场景用change buffer都能加速:

        1. 设想一个对于写多读少的业务来说,change buffer 记录的变更越多越划算,例如账单类日志类

        2. 反过来,一个业务的更新模式是写入之后马上会做查询,change buffer里的内容不多,由于马上做查询要访问数据页,这样的io次数不会减少

     

    如果某次写入使用了change buffer机制,之后主机异常重启,是否会丢失change buffer和数据?

        虽然写入时只更新了内存,但是在事务提交的时候,change buffer的操作也会记录到redo log,所以崩溃恢复的时候,change buffer也能找回来,即数据可以找回来。

     

    所以普通索引和唯一索引,应该怎么选择?

        查询时:两种索引查询性能几乎没差别

        更新时:大部分场景下,因为有change buffer的存在,普通索引的更新速度会比唯一索引的快(特别适用于写多读少的场景)(如果所有的更新后面,都马上伴随着对这个记录的查询,那么应该关闭change buffer)

     

    展开全文
  • mysql数据库 索引

    2012-08-22 09:37:34
    索引索引:是针对数据建立的目录; 作用:可以加快查询速度; 负面影响:降低了增删改的速度 案例: 设有新闻表 15列,10行上有索引,共500W行数据,如何快速导入? 1.把空表的索引全部删除 2.导入...
    索引:
    索引:是针对数据建立的目录;
    作用:可以加快查询速度;
    负面影响:降低了增删改的速度


    案例:
    设有新闻表 15列,10行上有索引,共500W行数据,如何快速导入?
    1.把空表的索引全部删除
    2.导入数据
    3.数据导入完毕后,集中建立索引


    索引的创建原则:
    1.不要过度索引
    2.在where条件最频繁的列加上
    3.尽量索引散列值,过于集中的值加索引意义不大


    索引的类型:
    普通索引:index  仅仅是加快查询速度
    唯一索引:unique index 行上的值不能重复
    主键索引:primary key不能重复
        主键必唯一,但是唯一索引不一定是主键
        一张表上只能有一个主键,但是可以用一个或多个唯一索引
    全文索引:fulltext index


    查看一张表上的所有索引
    show index from 表名;




    create table member(
    id int,
    email varchar(30),
    tel char(11),
    intro text
    )engine myisam charset utf8;

    建立索引
    alter table 表名 add index/unique/fulltext [索引名](列名);
    alter table 表名 add primary key (列名)  //不需要加索引名,因为主键只有一个

    alter table member add index tel (tel);
    alter table member add unique (email);
    alter table member add fulltext (intro);
    alter table member add primary key (id);


    删除索引
    alter table 表名 drop 索引名;
    alter table member drop index intro;      删除非主键索引
    alter table member drop primary key;     删除主键索引


    关于全文索引的用法
    match (全文索引名) against ('keyword');

    select * from articles where match (body) against ('database');


    关于全文索引的停止词
    全文索引不针对非常频繁的词做索引
    如this , is , you , my 等等

    全文索引在mysql的默认情况下,对于中文意义不大 
    因为英文有空格,标点符号来拆成单词,进而对单词进行索引
    而对于中文,没有空格来隔开单词,mysql无法识别每个中文词
    展开全文
  • MySQL数据库索引案例

    2019-04-27 14:07:35
    MySQL数据库索引案例 MySQL索引可以用来快速地寻找某些具有特定值的记录,所有 MySQL.索引都以B-树形式保存。如果MySQL没有索引,执行 select时会从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录。如果...

    MySQL数据库索引案例

    MySQL索引可以用来快速地寻找某些具有特定值的记录,所有 MySQL.索引都以B-树形式保存。如果MySQL没有索引,执行 select时会从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录。如果表中数据有上亿条数据,查询一条数据花费的时间会非常长,索引类似于电子书的目录与页码的对应关系,可加快数据的查找。
    如果在需搜索条件的列上创建了索引, MySQL无须扫描全表记录即可快速得到相应的记录行。如果该表有100000条记录,通过索引查找记录要比全表顺序扫描至少快100倍,这就是索引在企业环境中带来的执行速度上的提升。

    1、MySQL数据库常见索引类型

    普通索引( normal)
    唯一索引( unique)
    全文索引( full text)
    主键索引( primary key)
    组合索引(combined index)
    以下为每个索引的应用场景及区别
    普通索引: normal,使用最广泛。
    唯一索引: unique,不允许重复的索引,允许有空值
    全文索引: full text,只能用于 MyISAM表, full text主要用于大量的内容检索
    主键索引: primary key又称为特殊的唯一索引,不允许有空值。
    组合索引:combined index为提高 MySQL效率可建立组合索引。

    2、MySQL数据库表创建各个索引命令,以t1表为案例,操作如下

    主键索引: ALTER TABLE t1 ADD PRIMARY KEY(’ column’)。
    唯一索引: ALTER TABLE t1 ADD UNIQUE(’ column’)
    普通索引: ALTER TABLE t1 ADD INDEX index_name(’ column’)。
    全文索引: ALTER TABLE t1 ADD FULLTEXT(’ column’)
    组合索引: ALTER TABLE tl ADD INDEX index_name(‘columnl’,’ column2’,’ column3’)

    4、MySQL数据库表删除各个索引命令,以表为案例,操作如下:

    DROP INDEX index_name ON t1;
    ALTER TABLE tl DROP INDEX index_name;
    ALTER TABLE tI DROP PRIMARY KEY;

    5、MySQL数据库查看表索引,操作如下:

    show index from t1;
    show keys from t1;

    展开全文
  • MySql数据库索引原理

    2017-05-15 10:36:05
    第一部分主要从数据结构及算法理论层面讨论MySQL数据库索引的数理基础。 第二部分结合MySQL数据库中InnoDB数据存储引擎中索引的架构实现讨论聚集索引、非聚集索引及覆盖索引等话题。 第三部分讨论MySQL中高...
  • mysql数据库索引优化.docmysql数据库索引优化.docmysql数据库索引优化.doc
  • 索引类型: B-TREE索引,哈希索引•B-TREE索引加速了数据访问,因为存储引擎不会扫描整个表得到需要的数据。相反,它从根节点开始。根节点保存了指向子节点的指针,并且存储引擎会根据指针寻找数据。它通过查找节点页...
  • 为什么MySQL数据库索引选择使用B+树?

    万次阅读 多人点赞 2018-03-05 21:19:57
    在进一步分析为什么MySQL数据库索引选择使用B+树之前,我相信很多小伙伴对数据结构中的树还是有些许模糊的,因此我们由浅入深一步步探讨树的演进过程,在一步步引出B树以及为什么MySQL数据库索引选择使用B+树!...
  • 软件安装:装机软件必备包SQL是Structured Query Language(结构化查询语言)的缩写。SQL是专为数据库而建立的操作命令集,是一种功能齐全的数据库语言。...在关系数据库中,索引是一种单独的、物理的对数据库表中一...
  • 以下的文章主要介绍的是MySQL数据库索引,即单列索引与多列索引的介绍,以及对多列索引的SQL命令的示例,以下就是这些内容的介绍,望你在浏览之后会对MySQL数据库索引的相关内容有更深入的了解。 为了提高搜索效率...
  • MySQL数据库索引、事务与存储引擎

    千次阅读 2020-10-12 23:42:11
    MySQL数据库索引、事务与存储引擎一 索引概念1.1 数据库索引1.2 索引的作用1.3 索引的分类1.3.1 普通索引1.3.2 唯一性索引1.3.3 主键索引1.3.4 组合索引1.3.5 全文索引1.4 创建索引发原则依据1.5 查看索引1.6 删除...
  • Mysql数据库索引原理及算法原理

    千次阅读 2019-03-07 18:01:23
    最近在网上看到了一篇关于mysql数据库索引的好文章,认真看完之后肯定受益匪浅,(虽说有的地方我不太理解)转来供大家学习交流。 另外:Ctrl C+V 好累啊,有没有快捷转载方法? 摘要 本文以MySQL数据库为研究...
  • MySql数据库索引介绍

    千次阅读 多人点赞 2019-05-27 17:08:27
    数据库索引对我们来说是透明的,因为数据库表创建索引前后,SQL语句都可以正常运行,索引的运用只是数据库引擎工作时候的优化手段。但是,这并不是说数据库索引仅仅是数据库设计开发人员和运维人员的事情,对于一个...
  • 此文章主要描述的是MySQL数据库索引类型其主要是可以分成4大类型,以及对MySQL数据库索引类型的实际应用与创建有相关的介绍。 以下的文章主要介绍的是MySQL数据库索引类型,其中包括普通索引,唯一索引,主键...
  • MySQL 数据库索引优化项目实战

    千次阅读 2018-05-15 00:00:00
    &#13; &#13; &#13; &#13; &#13; &#13; &#13; 本文来自作者 奋斗 在 GitChat 上分享 「MySQL 数据库索引优化项目实战」奋斗,小马用车...
  • MySQL数据库索引优化

    2018-04-17 21:13:20
    介绍BTree索引和Hash索引,详细介绍索引的优化策略等等 1.Btree索引和Hash索引 2.安装演示数据库 3.索引优化策略上 4.索引优化策略中 5.索引优化策略下
  • MySQL数据库索引失效与优化详解 案例所用的表结构、索引、与数据如下: 索引失效与优化 全值匹配 最佳左前缀法则 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描 ...
  • 为什么MySQL数据库索引选择使用B+树

    千次阅读 2020-04-06 20:25:55
    为什么MySQL数据库索引选择使用B+树1.前言-介绍各种树2.B/B+树3.为什么B+树比B树更适合数据库的索引?4.数据库的索引类型 1.前言-介绍各种树 1.二叉查找树:即有序二叉查找树,二叉搜索树; 对于某些情况,二叉查找...
  • Mysql数据库索引的理解及聚簇索引和非聚簇索引的区别 概念 索引是帮助Mysql搞笑获取数据的数据结构 对Mysql数据库来讲,其核心就是存储引擎,而索引就是属于存储引擎级别的概念,不同的存储引擎对索引的实现方式是...
  • 查看mySQL数据库索引

    2014-04-11 11:41:23
    mySQL索引查看 select * from information_schema.statistics where table_schema='数据库名称' and table_name = '表名称'

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 38,201
精华内容 15,280
关键字:

mysql数据库索引

mysql 订阅