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

    千次阅读 2020-02-01 18:15:08
    最近学习了关于mysql索引的一些知识,再次做一个总结。首先建立mysql索引的作用是为了更快访问一些数据,mysql索引文件是存储在磁盘上的文件。实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的...

    最近学习了关于mysql索引的一些知识,再次做一个总结。首先建立mysql索引的作用是为了更快访问一些数据,mysql索引文件是存储在磁盘上的文件。实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。

    目录

    mysql索引的数据结构类型:

    1.平衡二叉树:

    2.B树:

    3.B+树:

    4.Hash索引:

    mysql存储引擎:

    1.InnoDB(聚簇索引)

    2.MyIsam(非聚簇索引)

    3.联合索引 


    mysql索引的数据结构类型:

    1.平衡二叉树:

    平衡二叉树在数据结构中属于比较好理解的部分,按照键值的大小将索引存储在二叉树合适的位置,具体的存储方法就和平衡二叉树的存储是一样的。需要注意左右子树的高度差,进行二叉树的旋转保证二叉树的平衡。

    2.B树:

    特性如下图所示

    可以看到B树的每个结点内部都要存储一个指向关键字代表的具体数据的指针,因为每个盘块的大小是固定的,这样使得每个结点的度相对应较小,如果每个磁盘只存储对应的主键值或者只存储指向具体数据的指针这样可以解决存储空间。

    B树的存储方式很接近二叉树,这样会导致有些数据的查找较慢,比如有些数据的深度较大。采用B+树的方式使得查找更加稳定。

    3.B+树:

    B+树很好的弥补了B树的一些缺点提高了的查找效率和存储效率。

     

    4.Hash索引:

    和数据结构中的hash查找类似,通过计算关键字的hash值查找该数据对应的地址,可以理解为通过hash值并对hash值进行相应的处理确定数据的存储位置,如果发生数据冲突,解决的办法为链地址法。

    mysql存储引擎:

    1.InnoDB(聚簇索引)

    InnoDB的一个数据表中,它的索引文件和数据文件是同一个,也就是说,在查询时只要找到了索引就找到了数据文件。在InnoDB中及存储主键索引值,有存储行数据,叫做聚簇索引 。

    聚簇索引会将磁盘数据按照一列或者多列的值进行排序,通过是按照主键内一列的值进行排序的。聚簇索引的特点是存储的顺序和索引的顺序一致,一张表中只存在一个聚簇索引。

    2.MyIsam(非聚簇索引)

    非聚簇索引也就是数据和索引分开存储的

    3.联合索引 

    最左匹配原则,按照联合索引进行查找的原则是,查找字段的关键字中必须按照索引设定顺序出现才能按照索引进行查找。也就是说查找的关键字序列中出现了某一个关键字,但是该关键字在索引中的前一个关键字并未出现,则不能按照索引查找。

    比如我为一个字段设置了ABC三个关键字为联合索引。那么当我的查询条件出现了BC而没有写出A,那么将无法使用的索引的方式进行查询。

    展开全文
  • MySQL 索引

    千次阅读 2019-04-17 18:04:36
    MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。 打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。 索引分...

    MySQL 索引

    MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。

    打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。

    索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索引包含多个列。

    创建索引时,你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

    实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。

    上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。

    建立索引会占用磁盘空间的索引文件。

    普通索引
    创建索引
    这是最基本的索引,它没有任何限制。它有以下几种创建方式:

    CREATE INDEX indexName ON mytable(username(length)); 
    

    如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length。

    修改表结构(添加索引)
    ALTER table tableName ADD INDEX indexName(columnName)
    创建表的时候直接指定

    CREATE TABLE mytable(  
     
    ID INT NOT NULL,   
     
    username VARCHAR(16) NOT NULL,  
     
    INDEX [indexName] (username(length))  
     
    );  
    

    删除索引的语法

    DROP INDEX [indexName] ON mytable; 
    

    唯一索引
    它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:

    创建索引

    CREATE UNIQUE INDEX indexName ON mytable(username(length)) 
    

    修改表结构

    ALTER table mytable ADD UNIQUE [indexName] (username(length))
    

    创建表的时候直接指定

    CREATE TABLE mytable(  
     
    ID INT NOT NULL,   
     
    username VARCHAR(16) NOT NULL,  
     
    UNIQUE [indexName] (username(length))  
     
    );  
    

    使用ALTER 命令添加和删除索引
    有四种方式来添加数据表的索引:

    ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 
    

    该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。

    ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):
    

    这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。

    ALTER TABLE tbl_name ADD INDEX index_name (column_list): 
    

    添加普通索引,索引值可出现多次。

    ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):
    

    该语句指定了索引为 FULLTEXT ,用于全文索引。
    以下实例为在表中添加索引。

    mysql> ALTER TABLE testalter_tbl ADD INDEX (c);
    

    你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:

    mysql> ALTER TABLE testalter_tbl DROP INDEX c;
    

    使用 ALTER 命令添加和删除主键
    主键只能作用于一个列上,添加主键索引时,你需要确保该主键默认不为空(NOT NULL)。实例如下:

    mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
    mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);
    

    你也可以使用 ALTER 命令删除主键:

    mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;
    

    删除主键时只需指定PRIMARY KEY,但在删除索引时,你必须知道索引名。

    显示索引信息
    你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。

    尝试以下实例:

    mysql> SHOW INDEX FROM table_name; \G
    ........
    
    展开全文
  • mysql 索引

    千次阅读 2019-07-26 16:34:11
    1.mysql索引的分列方式: ①单列索引:一个索引只包含单个列,一个表可以有多个单列索引 ②组合索引:一个索引包含多个列 2.索引的创建需要确保索引是引用在sql查询语句上 3.增加索引的优点: ①可以大大提高...

    1.mysql索引的分列方式:

    ①单列索引:一个索引只包含单个列,一个表可以有多个单列索引

    ②组合索引:一个索引包含多个列

    2.索引的创建需要确保索引是引用在sql查询语句上

    3.增加索引的优点:

    ①可以大大提高查询效率

    ②可以提高Mysql的检索速度

    4.增加索引的缺点:

    ①增加索引会降低更新表的速度,如执行insert,update,不仅要保存数据,还要保存索引文件

    ②索引会占用磁盘空间的索引文件

    5.索引创建语句:create index indecName on tableName(userName(length))

    6.修改表结构(添加索引):alter table tableName add index indexName(columnName)

    7.删除索引:drop index indexName on tableName

    8.唯一索引

      (1)唯一索引与其他索引类似,不同的是唯一索引的列值必须唯一,但允许有空值,如果是组合索引,则列值的组合必须唯一

      (2)创建索引:create unique index indexName on tableName(columnName(length))

      (3)修改表结构(增加索引): alter  table tableName add unique [indexName] (columnName(length))

     (4)删除索引: alter  table tableName drop index indexName

     

     

    展开全文
  • Mysql索引

    千次阅读 2019-12-02 13:21:26
    好记忆不如烂笔头, 能记下点什么, 就记下点什么, 方便后期的巩固. 一.介绍 1.什么是索引? 2.... 1.MySQL索引分类 2.各索引应用场景 3.索引类型 4.操作索引 5.唯一索引 四.测试...

    好记忆不如烂笔头, 能记下点什么, 就记下点什么, 方便后期的巩固.

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

    2.为什么要有索引呢?

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

    索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。索引相当于字典的音序表,如果要查某个字,如果不使用音序表,则需要从几百页中逐页去查。

    二.索引的原理

    1.原理

    索引的目的在于提高查询效率,与我们查阅图书的目录是一个道理:先定位到类别,比如成功的秘诀,再定位到章节,比如1.成功需要考虑的五件事,再定位到页数,比如120页。

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

    数据库也是一样,但显然要复杂的多,因为不仅面临着等值查询,还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢?我们回想字典的例子,能不能把数据分成段,然后分段查询呢?

    最简单的如果1000条数据,1到100分成第一段,101到200分成第二段,201到300分成第三段……这样查第250条数据,只要找第三段就可以了,一下子去除了90%的无效数据。但如果是1千万的记录呢,分成几段比较好?

    稍有算法基础的同学会想到搜索树,其平均复杂度是lgN,具有不错的查询性能。但这里我们忽略了一个关键的问题,复杂度模型是基于每次相同的操作成本来考虑的。而数据库实现比较复杂,一方面数据是保存在磁盘上的,另外一方面为了提高性能,每次又可以把部分数据读入内存来计算,因为我们知道访问磁盘的成本大概是访问内存的十万倍左右,所以简单的搜索树难以满足复杂的应用场景。

    2.磁盘IO与预读

    考虑到磁盘IO是非常高昂的操作,计算机操作系统做了一些优化,当一次IO时,不光把当前磁盘地址的数据,而是把相邻的数据也都读取到内存缓冲区内,因为局部预读性原理告诉我们,当计算机访问一个地址的数据的时候,与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关,一般为4k或8k,也就是我们读取一页内的数据时候,实际上才发生了一次IO,这个理论对于索引的数据结构设计非常有帮助。

    3.索引的数据结构

    任何一种数据结构都不是凭空产生的,一定会有它的背景和使用场景,我们现在总结一下,我们需要这种数据结构能够做些什么,其实很简单,那就是:每次查找数据时把磁盘IO次数控制在一个很小的数量级,最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢?就这样,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并不真实存在于数据表中。

    4.b+树的查找过程

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

    5.b+树性质

    1.索引字段要尽量的小:通过上面的分析,我们知道IO次数取决于b+数的高度h,假设当前数据表的数据为n,每个磁盘块的数据项的数量是m,则有h=n/m,当数据量N一定的情况下,m越大,h越小;同样数据,每个层的m,数量项数量越多,那层次高度必然会降低。

    而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的数据了, 这个是非常重要的性质,即索引的最左匹配特性。

    三.索引管理

    1.MySQL的索引分类

    索引分类

    
     
    1. 1.普通索引index :加速查找
    2. 2.唯一索引
    3. 主键索引:primary key :加速查找+约束(不为空且唯一)
    4. 唯一索引:unique:加速查找+约束 (唯一)
    5. 3.联合索引
    6. -primary key(id,name):联合主键索引
    7. -unique(id,name):联合唯一索引
    8. -index(id,name):联合普通索引
    9. 4.全文索引fulltext :用于搜索很长一篇文章的时候,效果最好。
    10. 5.空间索引spatial :了解就好,几乎不用

    2.各索引应用场景

    举个例子来说,比如你在为某商场做一个会员卡的系统。

    这个系统有一个会员表
    有下列字段:
    会员编号 INT
    会员姓名 VARCHAR(10)
    会员身份证号码 VARCHAR(18)
    会员电话 VARCHAR(10)
    会员住址 VARCHAR(50)
    会员备注信息 TEXT

    那么这个 会员编号,作为主键,使用 PRIMARY
    会员姓名 如果要建索引的话,那么就是普通的 INDEX
    会员身份证号码 如果要建索引的话,那么可以选择 UNIQUE (唯一的,不允许重复)

    除此之外还有全文索引,即FULLTEXT
    会员备注信息 , 如果需要建索引的话,可以选择全文搜索。
    用于搜索很长一篇文章的时候,效果最好。
    用在比较短的文本,如果就一两行字的,普通的 INDEX 也可以。
    但其实对于全文搜索,我们并不会使用MySQL自带的该索引,而是会选择第三方软件如Sphinx,专门来做全文搜索。

    其他的如空间索引SPATIAL,了解即可,几乎不用

    3.索引类型

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

    • 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 等索引;

    4.操作索引

    创建索引
    在创建表时就创建(需要注意的几点)
    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 index age on s1(age); 添加唯一索引
    alter table s1 add primary key(id); #添加主键索引,也就是给id字段增加一个主键约束
    create index name on s1(id,name); #添加普通联合索引

    查看索引
    mysql> show index from stu; 查看sut表的索引
    删除索引
    drop index name on s1; #删除普通索引
    drop index age on s1; #删除唯一索引,就和普通索引一样,不用在index前加unique来删,直接就可以删了
    alter table s1 drop primary key; #删除主键(因为它添加的时候是按照alter来增加的,那么我们也用alter来删)

    对student字段前8个字符建立索引,而不是对整个
    create index index_name on student(name(8));

    提示:按条件列查询数据时, 联合索引是有前缀生效特性的index(a,b,c)仅 a,ab,abc 三个查询条件列可以走索引, b,bc,ac,c 等无法使用索引了尽量把最常用作为查询条件的列,放在第一位置

    联合索引,并前10个字符
    create index index_name_and_dept on student(name(10),dept(10));

    5.唯一索引

    一个表可以有多个UNIQUE字段
    对应的字段值不允许有重复
    UNQIUE字段的KEY标志是UNI
    UNIQUE字段的值允许为NULL,当将其修改为不允许为NULL,则此字段限制与主键相同

    建表时候添加
    UNIQUE(id));

    删除唯一索引
    drop index name on s2;

    配置
    create UNIQUE index name on s2(name);

    四.测试

    1. 准备表

    create table s1(
    id int,
    name varchar(20),
    gender char(6),
    email varchar(50)
    );

    2.创建存储过程,实现批量插入记录

    有关存储过程,请看参考

    以下为用mysql字典的存储过程功能,批量写入数据300万条数据

    
     
    1. delimiter $$ #声明存储过程的结束符号为$$
    2. create procedure auto_insert1()
    3. BEGIN
    4. declare i int default 1;
    5. while(i<3000000)do
    6. insert into s1 values(i,concat('egon',i),'male',concat('egon',i,'@oldboy'));
    7. set i=i+1;
    8. end while;
    9. END$$ #$$结束
    10. delimiter ; #重新声明分号为结束符号

    3.查看存储过程

    show create procedure auto_insert1\G

    4.调用存储过程

    需要等待30分钟左右
    call auto_insert1();

    5.查询

    查询在1秒左右
    select * from s1 where id=333;

    添加id作为普通索引
    create index name on s1(id);

    查询0.0秒
    select * from s1 where id=3334;

    五.索引优化

    1.覆盖索引

    该sql命中了索引,但未覆盖索引。
    select * from s1 where id=123;

    利用id=123到索引的数据结构中定位到该id在硬盘中的位置,或者说再数据表中的位置。
    但是我们select的字段为*,除了id以外还需要其他字段,这就意味着,我们通过索引结构取到id还不够,还需要利用该id再去找到该id所在行的其他字段值,这是需要时间的,很明显,如果我们只select id,就减去了这份苦恼,如下

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

    2.联合索引

    create index idn on s1(1d,name);

    3.索引选择情况

    
     
    1. #1.最左前缀匹配原则,非常重要的原则,
    2. create index ix_name_email on s1(name,email,)
    3. - 最左前缀匹配:必须按照从左到右的顺序匹配
    4. select * from s1 where name='egon'; #可以
    5. select * from s1 where name='egon' and email='asdf'; #可以
    6. select * from s1 where email='alex@oldboy.com'; #不可以
    7. mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,
    8. 比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,
    9. d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。
    10.  
    11. #2.=和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器
    12. 会帮你优化成索引可以识别的形式
    13.  
    14. #3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),
    15. 表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、
    16. 性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,
    17. 这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录
    18.  
    19. #4.索引列不能参与计算,保持列“干净”,比如from_unixtime(create_time) = ’2014-05-29’
    20. 就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,
    21. 但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。
    22. 所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
    23.  
    24. #5.需要在那些上面建立索引
    25. 解答: select user,host from mysql.user where password=...
    26.  
    27. 索引一定要创建在 where 后的条件列上,而不是 select 后的选择数据的列上,另外,我们要尽量选择在唯一值多的大表上的列建立索引,例如:男女性别列唯一值, 不适合建立索引

    4.最左前缀示范

    
     
    1. mysql> select * from s1 where id>3 and name='egon' and email='alex333@oldboy.com' and gender='male';
    2. Empty set (0.39 sec)
    3.  
    4. mysql> create index idx on s1(id,name,email,gender); #未遵循最左前缀
    5. Query OK, 0 rows affected (15.27 sec)
    6. Records: 0 Duplicates: 0 Warnings: 0
    7.  
    8. mysql> select * from s1 where id>3 and name='egon' and email='alex333@oldboy.com' and gender='male';
    9. Empty set (0.43 sec)
    10.  
    11.  
    12. mysql> drop index idx on s1;
    13. Query OK, 0 rows affected (0.16 sec)
    14. Records: 0 Duplicates: 0 Warnings: 0
    15.  
    16. mysql> create index idx on s1(name,email,gender,id); #遵循最左前缀
    17. Query OK, 0 rows affected (15.97 sec)
    18. Records: 0 Duplicates: 0 Warnings: 0
    19.  
    20. mysql> select * from s1 where id>3 and name='egon' and email='alex333@oldboy.com' and gender='male';
    21. Empty set (0.03 sec)

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

    
     
    1. - like '%xx'
    2. select * from tb1 where email like '%cn';
    3.  
    4.  
    5. - 使用函数
    6. select * from tb1 where reverse(email) = 'wupeiqi';
    7.  
    8.  
    9. - or
    10. select * from tb1 where nid = 1 or name = 'seven@live.com';
    11.  
    12.  
    13. 特别的:当or条件中有未建立索引的列才失效,以下会走索引
    14. select * from tb1 where nid = 1 or name = 'seven';
    15. select * from tb1 where nid = 1 or name = 'seven@live.com' and email = 'alex'
    16.  
    17.  
    18. - 类型不一致
    19. 如果列是字符串类型,传入条件是必须用引号引起来,不然...
    20. select * from tb1 where email = 999;
    21.  
    22. 普通索引的不等于不会走索引
    23. - !=
    24. select * from tb1 where email != 'alex'
    25.  
    26. 特别的:如果是主键,则还是会走索引
    27. select * from tb1 where nid != 123
    28. - >
    29. select * from tb1 where email > 'alex'
    30.  
    31.  
    32. 特别的:如果是主键或索引是整数类型,则还是会走索引
    33. select * from tb1 where nid > 123
    34. select * from tb1 where num > 123
    35.  
    36.  
    37. #排序条件为索引,则select字段必须也是索引字段,否则无法命中
    38. - order by
    39. select name from s1 order by email desc;
    40. 当根据索引排序时候,select查询的字段如果不是索引,则不走索引
    41. select email from s1 order by email desc;
    42. 特别的:如果对主键排序,则还是走索引:
    43. select * from tb1 order by nid desc;
    44.  
    45. - 组合索引最左前缀
    46. 如果组合索引为:(name,email)
    47. name and email -- 使用索引
    48. name -- 使用索引
    49. email -- 不使用索引
    50.  
    51.  
    52. - count(1)或count(列)代替count(*)在mysql中没有差别了
    53.  
    54. - create index xxxx on tb(title(19)) #text类型,必须制定长度

    6.避免

    
     
    1. - 避免使用select *
    2. - count(1)或count(列) 代替 count(*)
    3. - 创建表时尽量时 char 代替 varchar
    4. - 表的字段顺序固定长度的字段优先
    5. - 组合索引代替多个单列索引(经常使用多个条件查询时)
    6. - 尽量使用短索引
    7. - 使用连接(JOIN)来代替子查询(Sub-Queries)
    8. - 连表时注意条件类型需一致
    9. - 索引散列值(重复少)不适合建索引,例:性别不适合

    7.慢查询优化

    1. 先运行看看是否真的很慢,注意设置SQL_NO_CACHE
    2. where条件单表查,锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起,单表每个字段分别查询,看哪个字段的区分度最高
    3. explain查看执行计划,是否与1预期一致(从锁定记录较少的表开始查询)
    4. order by limit 形式的sql语句让排序的表优先查
    5. 了解业务方使用场景
    6. 加索引时参照建索引的几大原则
    7. 观察结果,不符合预期继续从0分析
    展开全文
  • 数据库索引原理,及MySQL索引类型

    万次阅读 多人点赞 2018-08-31 21:08:40
    MySQL索引类型一览 让MySQL高效运行起来 本文介绍了七种MySQL索引类型。在数据库表中,对字段建立索引可以大大提高查询速度。通过善用这些索引,可以令MySQL的查询和运行更加高效。 索引是快速搜索的关键。MySQL...
  • Mysql 索引

    千次阅读 2012-03-28 17:15:13
    什么是索引 索引时一种特殊的文件,他们包涵着对数据表里所有记录的引用指针。 当对数据表记录进行更新后,都会对索引进行刷新。 索引会占用相当大的空间,应该只为经常查询和最经常排序的数据列建立索引。 ...
  • MySQL索引的创建与使用

    万次阅读 多人点赞 2018-08-06 18:49:59
    声明:本人主要简单示例MySQL中的单列索引、组合索引的创建与使用。 索引的创建: 建表时创建: CREATE TABLE 表名( 字段名 数据类型 [完整性约束条件], ……, [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY ...
  • GitHub 4.8k Star 的Java工程师成神之路 ,不来了解一下吗? GitHub 4.8k Star 的Java工程师成神之路 ,真的不来...相信很多人对于MySQL索引都不陌生,索引(Index)是帮助MySQL高效获取数据的数据结构。 因为索引...
  • MySQL索引原理、失效情况

    万次阅读 多人点赞 2019-04-19 17:19:17
    1 mysql索引知识 1.1 B+Tree索引 1.2 主键索引和普通索引的区别 1.3 唯一索引vs普通索引 2 mysql索引优化 2.1 查看索引使用情况 2.2 mysql索引使用策略 2.3 mysql索引使用原则 1 mysql索引知识 1.1 ...
  • mysql索引总结----mysql 索引类型以及创建

    万次阅读 热门讨论 2018-04-29 17:18:18
    说明:本文是转载的文章 链接:我转载的地址是... ...关于MySQL索引的好处,如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。对于没有索引的...
  • MySQL索引优化

    千次阅读 2021-02-19 09:42:44
    一、MySQL索引基础 本文主要讨论MySQL索引的部分知识。将会从MySQL索引基础、索引优化实战和数据库索引背后的数据结构三部分相关内容。 一、MySQL索引基础 首先,我们将从索引基础开始介绍一下什么是索引,分析...
  • 曾经,我以为我很懂MySQL索引

    万次阅读 多人点赞 2020-08-26 09:04:17
    MySQL索引,我们真的了解么?腾讯云数据库负责人林晓斌说过:“我们面试MySQL同事时只考察两点,索引和锁”。言简意赅,MySQL索引的重要性不言而喻。MySQL索引历经了多个版本的迭代,从语法到底层数据结构都有很多...
  • 为mysql数据库建立索引;mysql索引总结----mysql 索引类型以及创建;mysql_建立索引的优缺点
  • MySQL索引与索引优化

    千次阅读 2020-09-04 16:48:00
    MySQL索引就是用于优化器上。 索引: MySQL官方对于索引的定义为:索引是帮助MySQL高效获取数据的数据结构。即可以理解为:索引是数据结构。 索引是对数据库表中一个或多个列的值进行排序的结构,建立索引有助于快速...
  • MySQL索引的使用实例

    千次阅读 多人点赞 2020-10-24 13:11:43
    MySQL索引的使用实例一. 慢查询日志二. 查询分析器——explain三. 索引的基本使用四. 复合索引五. 覆盖索引 一. 慢查询日志 //查看是否开启慢查询日志 mysql> show variables like '%slow%'; //临时开启慢查询...
  •  关于MySQL索引的好处,如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。对于没有索引的表,单表查询可能几十万数据就是瓶颈,而通常大型网站单日就...
  • 说到索引,很多人都知道“索引是一个排序的列表,在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址,...因为索引并不是关系模型的组成部分,因此不同的DBMS有不同的实现,我们针对MySQL数据库的实现进...
  • mysql 索引mysql 添加索引

    千次阅读 2017-11-24 11:35:28
     索引用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树的形式保存。如果没有索引,执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录。表里面的记录数量越多,这个操作...
  • mysql索引详解

    千次阅读 2019-01-21 13:34:03
    本文从以下几点介绍mysql索引: 1、如何建立mysql索引; 2、介绍mysql类型; 3、mysql索引的利与弊; 4、建立索引的注意事项 首先假设你有一张表,该表有10万条数据,其中有一条数据是nickname='css',如果要拿...
  • MySQL索引创建及使用

    万次阅读 多人点赞 2019-03-12 17:56:59
    背景 最近在深化学习数据库相关知识,借此机会总结记录一番,本来想自己总结概括一下,但是看到一...关于MySQL索引的好处,如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL...
  • Mysql索引:图文并茂,深入探究索引的原理和使用

    千次阅读 多人点赞 2020-11-25 16:43:44
    关于Mysql索引的走心总结,建议收藏,反复阅读。
  • 细说mysql索引

    千次阅读 2017-12-01 19:29:48
    本文转自 细说mysql索引 本文从如何建立mysql索引以及介绍mysql的索引类型,再讲mysql索引的利与弊,以及建立索引时需要注意的地方 首先:先假设有一张表,表的数据有10W条数据,其中有一条数据是nickname='css',...
  • MySQL索引的分类

    千次阅读 2020-12-09 00:44:37
    本教程中所使用的数据库的建表语句都在“MySQL高阶教程索引”这篇文章中,点击链接直达:索引&建表语句 摘要:本文主要介绍MySQL索引的分类 MySQL索引的分类
  • MySQL索引原理及BTree(B-/+Tree)结构详解

    万次阅读 多人点赞 2018-11-20 16:52:25
    目录 摘要 数据结构及算法基础 索引的本质 B-Tree和B+Tree B-Tree B+Tree 带有顺序访问指针的B+Tree ...MySQL索引实现 MyISAM索引实现 InnoDB索引实现 索引使用策略及优化 示例数据库 最左前缀原理与...
  • MySQL 索引分析

    千次阅读 2020-07-04 09:57:56
    检索原理MySQL 索引结构hash二叉树中序遍历平衡二叉树(AVL)二叉树的结构图结论B 树B 树的结构图底层原理缺点什么是磁盘块 / 页?磁盘块 / 页之间的关系B 树检索原理结论B+ 树B+ 树结构图检索原理结论从 B 树到 B+ ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 101,446
精华内容 40,578
关键字:

mysql索引

mysql 订阅