精华内容
下载资源
问答
  • mysql——索引的添加及排序
    2021-02-11 03:51:02

    今天一顿操作,猛如虎,把一个部署到2核4g的小服务器挂掉了。

    幡然醒悟,关联操作还是要加上索引比较好,运行速度从几分钟迅速提到几秒。

    1.mysql添加索引的方法主要有以下几种(可以对关联的字段提前建索引,然后再关联)。

    a.添加PRIMARY KEY(主键索引) mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

    b.添加UNIQUE(唯一索引) mysql>ALTER TABLE `table_name` ADD UNIQUE ( `column` )

    c.添加INDEX(普通索引) mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

    d.添加FULLTEXT(全文索引) mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

    f.添加多列索引 mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

    2.mysql中增加一列数据,把取出的数据按顺序排1,2,3,4,5...,写法是

    set @rn=0;

    select @rn:[email protected]+1 as rn, A.* from A;

    原文:https://www.cnblogs.com/hongchenlianxinlu/p/12622366.html

    更多相关内容
  • 本文实例讲述了mysql索引排序的影响。分享给大家供大家参考,具体如下: 索引不仅能提高查询速度,还可以添加排序速度,如果order by 后面的语句用到了索引,那么将会提高排序的速度。 测试 1、创建测试表:t15表 ...
  • 主要给大家介绍了关于MySQL InnoDB 二级索引排序的相关资料,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧
  • MySQL中,有两种方式生成有序...MySQL索引通常是被用于提高WHERE条件的数据行匹配或者执行联结操作时匹配其它表的数据行的搜索速度。 MySQL也能利用索引来快速地执行ORDER BY和GROUP BY语句的排序和分组操作。 通过索
  • 创建表&创建索引 ...MySQL也能利用索引来快速地执行ORDER BY和GROUP BY语句的排序和分组操作。 通过索引优化来实现MySQL的ORDER BY语句优化: 1、ORDER BY的索引优化 如果一个SQL语句形如: SELECT
  • MySQL 建立索引的时候如何排序

    千次阅读 2021-01-19 07:47:33
    以下回复可能有需要更正的地方“表引擎在 innodb 的情况下建立 age、sex 的联合索引即可,innodb 会在建立索引时生成 btree 数据结构,这个数据结构上会隐式的加上主键“这是主键索引与二级索引之间的关联 ID,个人...

    以下回复可能有需要更正的地方

    “表引擎在 innodb 的情况下建立 age、sex 的联合索引即可,innodb 会在建立索引时生成 btree 数据结构,这个数据结构上会隐式的加上主键“

    这是主键索引与二级索引之间的关联 ID,个人理解不是隐式,而是显式

    “搜索时在这个索引上搜索来找到主键 (索引顺序是 age,sex,id),在通过主键找到数据”

    1、索引顺序更准确的应该是 age_sex,id

    2、是否需要通过主键找到数据,取决于是否需要回表,如果索引上已经可以拿到查询的所有数据,则不需

    要回表

    ”文中的 SQL 语句 select * from students where sex=1 and age >10 order by id desc 使用不到 id 的原因,是因为 age 是范围查找,如果是 age=xx 就可以用的上了,题主可以去找些关于表引擎的介绍 (高性能 MySQL 第五章 5.3.5 节)“

    1、《高性能 MySQL》第五章确实有关于 order 最左、第一个范围等不能使用索引等说明。

    2、结合自己的知识与测试后的结果是,范围查询 order by id,未出现 using filesort 等 order 未使用到索引的情况。

    // 创建表

    CREATE TABLE `t` (

    `id` int(11) NOT NULL,

    `city` varchar(16) NOT NULL,

    `name` varchar(16) NOT NULL,

    `age` int(11) NOT NULL,

    PRIMARY KEY (`id`),

    KEY `city` (`city`)

    ) ENGINE=InnoDB;

    // 执行的 SQL

    desc select id,city from t where city in('杭州','北京') order by 'id'

    执行结果

    6f16c2db5b49b05d9ff8208c7089bc62.png

    以下是自己整理的 MySQL order 是否能使用到索引的条件,当然还有其它因素:

    1、排序的字段是 有序 的

    2、优化器 认为走索引更快,order 最终才会走索引

    3、where 与 order 会共同影响(这方面的知识欠缺)

    展开全文
  • mysql索引详解

    万次阅读 多人点赞 2021-07-07 21:40:09
    一、MySQL三层逻辑架构 MySQL的存储引擎架构将查询处理与数据的存储/提取相分离。下面是MySQL的逻辑架构图: 一、对比InnoDB与MyISAM 1、 存储结构 MyISAM:每个MyISAM在磁盘上存储成三个文件。分别为:表定义...

    🍅 作者简介:哪吒,CSDN2021博客之星亚军🏆、新星计划导师✌、博客专家💪

    🍅 哪吒多年工作总结:Java学习路线总结,搬砖工逆袭Java架构师

    🍅 关注公众号【哪吒编程】,回复1024,获取Java学习路线思维导图、大厂面试真题、加入万粉计划交流群、一起学习进步

    目录

    一、MySQL三层逻辑架构

    1、第一层负责连接管理、授权认证、安全等等。

    2、第二层负责解析查询

    3、第三层是存储引擎

    二、对比InnoDB与MyISAM

    1、 存储结构

    2、 存储空间

    3、 可移植性、备份及恢复

    4、 事务支持

    5、 AUTO_INCREMENT

    6、 表锁差异

    7、 全文索引

    8、表主键

    9、表的具体行数

    10、CRUD操作

    11、 外键

    三、sql优化简介

    1、什么情况下进行sql优化

    2、sql语句执行过程

    3、sql优化就是优化索引

    四、索引

    1、索引的优势

    2、索引的弊端

    3、索引的分类

    4、创建索引

    5、MySQL索引原理 -> B+树

    五、如何触发联合索引

    1、对user表建立联合索引username、password

    2、触发联合索引

    六、分析sql的执行计划---explain

    1、explan使用简介

    2、explain查询结果简介


    一、MySQL三层逻辑架构

    MySQL的存储引擎架构将查询处理与数据的存储/提取相分离。下面是MySQL的逻辑架构图:

    1、第一层负责连接管理、授权认证、安全等等。

    每个客户端的连接都对应着服务器上的一个线程。服务器上维护了一个线程池,避免为每个连接都创建销毁一个线程。当客户端连接到MySQL服务器时,服务器对其进行认证。可以通过用户名和密码的方式进行认证,也可以通过SSL证书进行认证。登录认证通过后,服务器还会验证该客户端是否有执行某个查询的权限。

    2、第二层负责解析查询

    编译SQL,并对其进行优化(如调整表的读取顺序,选择合适的索引等)。对于SELECT语句,在解析查询前,服务器会先检查查询缓存,如果能在其中找到对应的查询结果,则无需再进行查询解析、优化等过程,直接返回查询结果。存储过程、触发器、视图等都在这一层实现。

    3、第三层是存储引擎

    存储引擎负责在MySQL中存储数据、提取数据、开启一个事务等等。存储引擎通过API与上层进行通信,这些API屏蔽了不同存储引擎之间的差异,使得这些差异对上层查询过程透明。存储引擎不会去解析SQL。

    二、对比InnoDB与MyISAM

    1、 存储结构

    MyISAM:每个MyISAM在磁盘上存储成三个文件。分别为:表定义文件、数据文件、索引文件。第一个文件的名字以表的名字开始,扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。

    InnoDB:所有的表都保存在同一个数据文件中(也可能是多个文件,或者是独立的表空间文件),InnoDB表的大小只受限于操作系统文件的大小,一般为2GB。

    2、 存储空间

    MyISAM: MyISAM支持支持三种不同的存储格式:静态表(默认,但是注意数据末尾不能有空格,会被去掉)、动态表、压缩表。当表在创建之后并导入数据之后,不会再进行修改操作,可以使用压缩表,极大的减少磁盘的空间占用。

    InnoDB: 需要更多的内存和存储,它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

    3、 可移植性、备份及恢复

    MyISAM:数据是以文件的形式存储,所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。

    InnoDB:免费的方案可以是拷贝数据文件、备份 binlog,或者用 mysqldump,在数据量达到几十G的时候就相对痛苦了。

    4、 事务支持

    MyISAM:强调的是性能,每次查询具有原子性,其执行数度比InnoDB类型更快,但是不提供事务支持。

    InnoDB:提供事务支持事务,外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

    5、 AUTO_INCREMENT

    MyISAM:可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引,如果是组合索引,自动增长可以不是第一列,他可以根据前面几列进行排序后递增。

    InnoDB:InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引,如果是组合索引也必须是组合索引的第一列。

    6、 表锁差异

    MyISAM: 只支持表级锁,用户在操作myisam表时,select,update,delete,insert语句都会给表自动加锁,如果加锁以后的表满足insert并发的情况下,可以在表的尾部插入新的数据。

    InnoDB: 支持事务和行级锁,是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁,只是在WHERE的主键是有效的,非主键的WHERE都会锁全表的。

    7、 全文索引

    MyISAM:支持 FULLTEXT类型的全文索引

    InnoDB:不支持FULLTEXT类型的全文索引,但是innodb可以使用sphinx插件支持全文索引,并且效果更好。

    8、表主键

    MyISAM:允许没有任何索引和主键的表存在,索引都是保存行的地址。

    InnoDB:如果没有设定主键或者非空唯一索引,就会自动生成一个6字节的主键(用户不可见),数据是主索引的一部分,附加索引保存的是主索引的值。

    9、表的具体行数

    MyISAM: 保存有表的总行数,如果select count() from table;会直接取出出该值。

    InnoDB: 没有保存表的总行数,如果使用select count(*) from table;就会遍历整个表,消耗相当大,但是在加了wehre条件后,myisam和innodb处理的方式都一样。

    10、CRUD操作

    MyISAM:如果执行大量的SELECT,MyISAM是更好的选择。

    InnoDB:如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑,应该使用InnoDB表。

    11、 外键

    MyISAM:不支持

    InnoDB:支持

    三、sql优化简介

    1、什么情况下进行sql优化

    性能低、执行时间太长、等待时间太长、连接查询、索引失效。

    2、sql语句执行过程

    (1)编写过程

    select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...

    (2)解析过程

    from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...

    3、sql优化就是优化索引

    索引相当于书的目录。

    索引的数据结构是B+树。

    四、索引

    1、索引的优势

    (1)提高查询效率(降低IO使用率)

    (2)降低CPU使用率

    比如查询order by age desc,因为B+索引树本身就是排好序的,所以再查询如果触发索引,就不用再重新查询了。

    2、索引的弊端

    (1)索引本身很大,可以存放在内存或硬盘上,通常存储在硬盘上。

    (2)索引不是所有情况都使用,比如①少量数据②频繁变化的字段③很少使用的字段

    (3)索引会降低增删改的效率

    3、索引的分类

    (1)单值索引

    (2)唯一索引

    (3)联合索引

    (4)主键索引

    备注:唯一索引和主键索引唯一的区别:主键索引不能为null

    4、创建索引

    alter table user add INDEX `user_index_username_password` (`username`,`password`)

    5、MySQL索引原理 -> B+树

    MySQL索引的底层数据结构是B+树

    B+Tree是在B-Tree基础上的一种优化,使其更适合实现外存储索引结构,InnoDB存储引擎就是用B+Tree实现其索引结构。

    B-Tree结构图中每个节点中不仅包含数据的key值,还有data值。而每一个页的存储空间是有限的,如果data数据较大时将会导致每个节点(即一个页)能存储的key的数量很小,当存储的数据量很大时同样会导致B-Tree的深度较大,增大查询时的磁盘I/O次数,进而影响查询效率。在B+Tree中,所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上,而非叶子节点上只存储key值信息,这样可以大大加大每个节点存储的key值数量,降低B+Tree的高度。

    B+Tree相对于B-Tree有几点不同:

    非叶子节点只存储键值信息。
    所有叶子节点之间都有一个链指针。
    数据记录都存放在叶子节点中。
    将上一节中的B-Tree优化,由于B+Tree的非叶子节点只存储键值信息,假设每个磁盘块能存储4个键值及指针信息,则变成B+Tree后其结构如下图所示:

    通常在B+Tree上有两个头指针,一个指向根节点,另一个指向关键字最小的叶子节点,而且所有叶子节点(即数据节点)之间是一种链式环结构。因此可以对B+Tree进行两种查找运算:一种是对于主键的范围查找和分页查找,另一种是从根节点开始,进行随机查找。

    可能上面例子中只有22条数据记录,看不出B+Tree的优点,下面做一个推算:

    InnoDB存储引擎中页的大小为16KB,一般表的主键类型为INT(占用4个字节)或BIGINT(占用8个字节),指针类型也一般为4或8个字节,也就是说一个页(B+Tree中的一个节点)中大概存储16KB/(8B+8B)=1K个键值(因为是估值,为方便计算,这里的K取值为〖10〗^3)。也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿 条记录。

    实际情况中每个节点可能不能填充满,因此在数据库中,B+Tree的高度一般都在2~4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的,也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

    数据库中的B+Tree索引可以分为聚集索引(clustered index)和辅助索引(secondary index)。上面的B+Tree示例图在数据库中的实现即为聚集索引,聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据,而是存储相应行数据的聚集索引键,即主键。当通过辅助索引来查询数据时,InnoDB存储引擎会遍历辅助索引找到主键,然后再通过主键在聚集索引中找到完整的行记录数据。

    五、如何触发联合索引

    1、对user表建立联合索引username、password

    2、触发联合索引

    (1)使用联合索引的全部索引键可触发联合索引

    (2)使用联合索引的全部索引键,但是用or连接的,不可触发联合索引

    (3)单独使用联合索引的左边第一个字段时,可触发联合索引

    (4)单独使用联合索引的其它字段时,不可触发联合索引

    六、分析sql的执行计划---explain

    explain可以模拟sql优化执行sql语句。

    1、explan使用简介

    (1)用户表

    (2)部门表

    (3)未触发索引

    (4)触发索引

    (5)结果分析

    explain中第一行出现的表是驱动表。

    1. 指定了联接条件时,满足查询条件的记录行数少的表为[驱动表]
    2. 未指定联接条件时,行数少的表为[驱动表]

    对驱动表直接进行排序就会触发索引,对非驱动表进行排序不会触发索引。

    2、explain查询结果简介

    (1)id:SELECT识别符。这是SELECT的查询序列号。

    (2)select_type:SELECT类型:

    1. SIMPLE: 简单SELECT(不使用UNION或子查询)
    2. PRIMARY: 最外面的SELECT
    3. UNION:UNION中的第二个或后面的SELECT语句
    4. DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询
    5. UNION RESULT:UNION的结果
    6. SUBQUERY:子查询中的第一个SELECT
    7. DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询
    8. DERIVED:导出表的SELECT(FROM子句的子查询)

    (3)table:表名

    (4)type:联接类型

    1. system:表仅有一行(=系统表)。这是const联接类型的一个特例。
    2. const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时。
    3. eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。eq_ref可以用于使用= 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。
    4. ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀,或如果键不是UNIQUE或PRIMARY KEY(换句话说,如果联接不能基于关键字选择单个行的话),则使用ref。如果使用的键仅仅匹配少量行,该联接类型是不错的。ref可以用于使用=或<=>操作符的带索引的列。
    5. ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。
    6. index_merge:该联接类型表示使用了索引合并优化方法。在这种情况下,key列包含了使用的索引的清单,key_len包含了使用的索引的最长的关键元素。
    7. unique_subquery:该类型替换了下面形式的IN子查询的ref:value IN (SELECT primary_key FROMsingle_table WHERE some_expr);unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
    8. index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引:value IN (SELECT key_column FROM single_table WHERE some_expr)
    9. range:只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符,用常量比较关键字列时,可以使用range
    10. index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
    11. all:对于每个来自于先前的表的行组合,进行完整的表扫描。如果表是第一个没标记const的表,这通常不好,并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL,使得行能基于前面的表中的常数值或列值被检索出。

    (5)possible_keys:possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意,该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。

    (6)key:key列显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

    (7)key_len:key_len列显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。注意通过key_len值我们可以确定MySQL将实际使用一个多部关键字的几个部分。

    (8)ref:ref列显示使用哪个列或常数与key一起从表中选择行。

    (9)rows:rows列显示MySQL认为它执行查询时必须检查的行数。

    (10)Extra:该列包含MySQL解决查询的详细信息。

    1. Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
    2. Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
    3. range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。对前面的表的每个行组合,MySQL检查是否可以使用range或index_merge访问方法来索取行。
    4. Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序,并按排序顺序检索行。
    5. Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时,可以使用该策略。
    6. Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。
    7. Using where:WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行,如果Extra值不为Using where并且表联接类型为ALL或index,查询可能会有一些错误。
    8. Using sort_union(...), Using union(...), Using intersect(...):这些函数说明如何为index_merge联接类型合并索引扫描。
    9. Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。并且,按最有效的方式使用索引,以便对于每个组,只读取少量索引条目。

    通过相乘EXPLAIN输出的rows列的所有值,你能得到一个关于一个联接如何的提示。这应该粗略地告诉你MySQL必须检查多少行以执行查询。当你使用max_join_size变量限制查询时,也用这个乘积来确定执行哪个多表SELECT语句。

    🍅 作者简介:哪吒,CSDN2021博客之星亚军🏆、新星计划导师✌、博客专家💪

    🍅 哪吒多年工作总结:Java学习路线总结,搬砖工逆袭Java架构师

    🍅 关注公众号【哪吒编程】,回复1024,获取Java学习路线思维导图、大厂面试真题、加入万粉计划交流群、一起学习进步

    关注公众号,回复1024,获取Java学习路线思维导图,加入万粉计划交流群

    展开全文
  • 详细介绍了各种高性能的索引使用策略,比如索引排序、索引下推、压缩索引等等,以及常见索引失效的情况。

    详细介绍了各种高性能的索引使用策略,比如索引排序、索引下推、压缩索引等等,以及常见索引失效的情况。

    前面我们已经介绍了各种类型的索引结构及其对应的优缺点:

    1. BTREE索引的数据结构以及具体实现原理深入解析
    2. 哈希索引的数据结构以及索引的优缺点

    正确的创建和使用索引是实现高性能查询的基础。我们通常会看到一些查询不当的使用索引,或者使用MySQL无法使用已有的索引,下面要讲的高性能的索引策略就是要避免索引失效,并尽可能的发挥这些索引的优势。

    上文高性能的索引策略以及常见索引失效的情况(1)中,我们讲解了部分高性能索引策略,下面我们来看看其他的策略。

    1 索引扫描排序

    MySQL可以通过排序操作或者按索引顺序扫描这两种方式来排序。如果EXPLAIN的type列的值为“index”,则说明MySQL使用了索引扫描来做排序。

    MySQL可以使用同一个索引既满足排序,又用于查找行,只有当索引列的顺序和ORDER BY字句的要求顺序是一致的,并且所有列的排序方向(倒序或正序时)MySQL才能够使用索引来对结果进行排序。

    如果查询需要关联多张表, 则只有当 ORDER BY 子句引用的字段全部为第一个表时,才能使用索引做排序。 ORDER BY 子句和查找型查询的限制是一样的:需要满足索引的最左前缀的要求。否则, MySQL都需要执行排序操作,而无法利用索引排序。

    一个例外是,如果前导列为常量的时候,如果WHERE或者JOIN字句中对这些列指定了常量,就可以弥补上面的不足。

    一个多列索引(a,b,c),使用如下查询:

      SELECT b, c, d FROM tablenames WHERE a = 123 ORDER BY b,c;  
    

    因为索引的第一列提供了常量,从而可以使用第二列开始进行排序,将三个列组合在一起,就行形成了索引的最左前缀。下面这个SQL也能使用排序:

      …… WHERE a = 123 ORDER BY b DESC;  
    

    下面的SQL也能使用排序,因为ORDER BY的两列就是索引的最左前缀:

      …… WHERE a > 123 ORDER BY a , b;  
    

    下面的SQL不能使用排序,因为ORDER BY使用了两种不同的排序方向:

      …… WHERE a = 123 ORDER BY a DESC, b ASC;  
    

    下面的SQL不能使用排序,因为ORDER BY使用了一个不在索引中的列:

      …… WHERE a = 123 ORDER BY b, d;  
    

    下面的SQL不能使用排序,因为WHERE和ORDER BY的列不满足最左前缀:

      …… WHERE a = 123 ORDER BY c;  
    

    下面的SQL不能使用排序,因为WHERE的第一列上是范围条件,所以MySQL无法使用其他索引列:

      …… WHERE a > 123 ORDER BY b,c;  
    

    下面的SQL不能使用排序,因为WHERE的第二列上有多个条件,对于排序来说也是范围查询:

      …… WHERE a > 123 AND b IN(1,2)  ORDER BY c;  
    

    无法使用索引排序的时候,将使用文件排序(filessort),但并不一定使用磁盘文件。

    2 索引下推

    最左前缀可以用于在索引中定位记录,而对于那些不符合最左前缀的查询条件部分,在MySQL 5.6之前只能在回表取到完整的行数据之后再一一比对,而MySQL 5.6 引入的索引下推优化(index condition pushdown,ICP),可以在索引遍历过程中,对索引中包含的字段先做判断(即使该字段没有使用到索引),直接过滤掉不满足条件的记录,减少回表次数,索引下推还支持Where中的“%xx%”模糊查询。注意,该优化不一定会每次都被使用。

    假设某个表中存在(name,age)的联合索引,我们需要检索出表中“姓张,而且年龄是10岁的人”。因为最左前缀的原则之一:范围查询的字段之后的索引字段失效,所以此时并没有使用到age这个索引字段。MySQL5.6之前,根据下图中的数据,图中一个箭头表示需要回表一次,name需要回表四次进行查询和筛选。

    在这里插入图片描述

    MySQL5.6及其之后的版本中,虽然age字段还是没有使用到索引,但是由于索引包含该查询字段age,因此InnoDB在(name,age)索引内部就判断了age是否等于10,对于不等于10的记录,直接判断并跳过。如下图,红色的箭头表示不会执行的回表查询,此时只需要回表两次即可,有效减少了IO次数。

    在这里插入图片描述

    3 压缩(前缀压缩)索引

    MyISAM使用前缀压缩来减少索引的大小,从而让更多的索引可以放入内存中,这在某些情况下能极大地提高性能。

    默认只压缩字符串,但通过参数设置也可以对整数做压缩。

    MyISAM压缩每个索引块的方法是:先完全保存索引块中的第一个值,然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分,把这部分存储起来即可。例如,索引块中的第一个值是“perform“,第二个值是”performance“,那么第二个值的前缀压缩后存储的是类似”7,ance“这样的形式。MyISAM对行指针也采用类似的前缀压缩方式。

    压缩块使用更少的空间,代价是某些操作可能更慢。因为每个值的压缩前缀都依赖前面的值,所以MyISAM查找时无法在索引块使用二分查找而只能从头开始扫描。正序的扫描速度还不错,但是如果是倒序扫描——例如ORDER BY DESC——就不是很好了。所有在块中查找某一行的操作平均都需要扫描半个索引块。

    测试表明,对于CPU密集型应用,因为扫描需要随机查找,压缩索引使得MyISAM在索引查找上要慢好几倍。压缩索引的倒序扫描就更慢了。压缩索引需要在CPU内存资源与磁盘之间做权衡。压缩索引可能只需要十分之一大小的磁盘空间,如果是I/O密集型应用,对某些查询带来的好处会比成本多很多。

    可以在CREATE TABLE语句中指定PACK_KEYS参数来控制索引压缩的方式。

    4 重复、冗余索引和未使用的索引

    MySQL允许在相同列上创建多个索引,并且需要单独维护重复的索引,并且优化器在优化查询的时候也需要逐个的进行考虑,这会影响性能。

    重复索引是指在相同的列上按照相同的顺序创建相同类型的索引,应该避免这样创建重复索引,发现后也应该立即删除。

    如下建表语句:

    CREATE TABLE test ( 
    	id BIGINT PRIMARY KEY, 
    	a INT, 
    	UNIQUE ( id ), 
    	INDEX ( id ) 
    );
    

    可能用于希望创建一个主键,并且加上唯一限制,然后加上索引以供查询**。事实上,MySQL的唯一限制和主键限制都是通过索引实现的,因此,上面的写法实际上在相同的列上创建了三个重复的索引,但这是没有必要的。**

    冗余索引和重复索引有一些不同,如果创建了多列索引(a,b),再创建索引(a)就是冗余索引,因为这只是前面一个索引的前缀索引,因此(a,b)也可以当作(a)来使用,但是如果再创建(b,a)就不是冗余索引,再创建索引(b)也不是,因为b不是索引(a,b)的最左前缀列,另外,其他不同类型的索引在相同列上创建(如哈希索引和全文索引)不会是B-Tree索引的冗余索引,而无论覆盖的索引列是什么。

    冗余索引通常发生再为表添加新索引的时候。例如,有人可能会增加一个新的索引(A,B)而不是扩展以后的索引(A)。还有一种情况是将一个索引扩展为(A,ID),其中ID是主键,对于InnoDB来说主键已经包含在二级索引中了,所以这也是冗余的。

    大多数情况下都不需要冗余索引,一种常见的索引优化就是:应该尽量扩展已有的索引而不是创建新索引,但也有时候处于性能方面的考虑需要冗余索引,因为扩展已有的索引会导致其变得太大,从而影响其他使用该索引的查询性能。如:如果在整数列上有一个索引,现在需要额外增加一个很长的varchar列来扩展该索引,那么性可能会急剧下降,特别是有查询把这个索引当作覆盖索引,或者这是MyISAM表并且有很多范围查询的时候(由于MyISAM的前缀压缩)。

    怎么找出冗余索引和重复索引呢?

    1. 可以使用Shlomi Noachcommon_schema中的一些视图来定位,common_schema是一系列可以安装到服务器上的常用的存储和试图。
    2. 可以使用Percona Toolkit中的pt_duplicate-key-checker,该工具通过分析表结构来找出冗余和重复的索引。

    在删除冗余索引的时候要非常小心:如果在InnoDB引擎表上有where a=5 order by id 这样的查询,那么索引(a)就会很有用,索引(a,b)实际上是(a,b,id)索引,这个索引对于where a=5 order by id 这样的查询就无法使用索引做排序,而只能使用文件排序了。所以,建议使用percona工具箱中的pt-upgrade工具来仔细检查计划中的索引变更。

    除了冗余索引和重复索引,可能还会有一些服务器永远不使用的索引,这样的索引完全是累赘,建议考虑删除,有两个工具可以帮助定位未使用的索引:

    1. percona server或者mariadb中先打开userstat=ON服务器变量,默认是关闭的,然后让服务器运行一段时间,再通过查询information_schema.index_statistics就能查到每个索引的使用频率。
    2. 使用percona toolkit中的pt-index-usage工具,该工具可以读取查询日志,并对日志中的每个查询进行explain操作,然后打印出关羽索引和查询的报告,这个工具不仅可以找出哪些索引是未使用的,还可以了解查询的执行计划,如:在某些情况下有些类似的查询的执行方式不一样,这可以帮助定位到那些偶尔服务器质量差的查询,该工具也可以将结果写入到mysql的表中,方便查询结果。

    5 常见索引失效情况

    对于索引列使用了函数或者表达式进行计算,一定不会走索引。因为对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能,而实际上Mysql优化器对于不改变有序性的函数,也不会考虑使用索引,没那么智能。

    隐式的类型转换。在MySQL中,字符串和数字做比较的话,是将字符串转换成数字,这是一种隐式的类型转换,使用CAST(xx AS signed int)函数。因此,对于数值类型的索引列使用数值字符串进行匹配,可以使用索引,因为MySQL对字符串执行了类型转换,但是如果对于字符串类型的索引列使用数值进行比较,则无法使用索引,因为索引执行了类型转换。

    隐式的字符编码转换。比如字符集utf8mb4是utf8的超集,所以当这两个类型的字符串在做比较的时候,MySQL内部的操作是,先把utf8字符串转成utf8mb4字符集,再做比较,使用CONVERT(traideid USING utf8mb4)。因此,对于多表联查的情况,如果两表使用的字符集不同,且被驱动的表的索引列使用utf8编码,则由于索引列执行了编码函数转换,进而无法使用索引。

    对于索引列使用LIKE模糊匹配时,如果%在前面,则一定不会走索引。

    不符合最左前缀匹配的规则不会走索引(规则在此前已经讲过了)。

    某些查询是否一定不会用到索引呢?比如is null、is not null、<>、!=、BETWEEN、not in等等,虽然很对文章都说不会走,但实际上这些都是不能确定的,特别是对于较高版本的mysql,一般来说如果满足这些条件的索引数据占比比较高,那么就不会走索引,如果符合条件的数据很少,则是完全有可能走索引的,并且,如果支持索引覆盖,那么仍然有大概率走索引(用了索引,但是没有用到索引的快速定位能力,而是全索引扫描)。反过来,即使是基于 = 的精确匹配,如果满足条件的索引数据占比比较高,那么仍可能走全表扫描而不是一定会走索引。这些判断都是基于select 条件以及MySQL优化器自己的判断,我们不应该武断的说一定会走或者一定不会走。

    6 三星索引

    《Relational Database index design and the optimizers》(数据库索引设计与优化)书中提出了判断索引是否适合某个查询的“三星系统”:

    1. 将相关记录放到一起则获得一星,即WHERE后面参与查询的列可以组成了单列索引或联合索引,where后面的谓词和索引列匹配的越多,索引片越窄,最终扫描的数据行也是越小。
    2. 索引中的数据顺序和查找中的排列顺序一致获得二星,如果ORDER BY和索引的顺序一致,则可以利用现成的顺序从而避免了生成临时表并使用文件排序。
    3. 索引中的列包含了查询中需要的全部列获得三星,避免回表查询,减少了IO操作,它可以保证查询只需访问索引而无需访问表记录。

    如果某个索引满足上面的三个条件,则该索引被称为“三星索引”。当然 了三星索引是一个比较理想化的标准,实际操作往往只能满足期望中的一颗或两颗星即可。

    参考资料:

    1. 《 MySQL 技术内幕: InnoDB 存储引擎》
    2. 《高性能 MySQL》

    如有需要交流,或者文章有误,请直接留言。另外希望点赞、收藏、关注,我将不间断更新各种Java学习博客!

    展开全文
  • Mysql排序使用索引

    千次阅读 2019-04-11 11:50:29
    Mysql两种排序方式:文件排序(filesort)或扫描有序索引排序(index) Mysql能为排序与查询使用相同的索引 KEY a_b_c(a,b,c) 1.order by 能使用索引最做前缀 - ORDER BY a - ORDER BY a,b - ORDER BY a,b,...
  • MySql索引详细介绍及正确使用方法 1.前言: 索引对查询的速度有着至关重要的影响,理解索引也是进行数据库性能调优的起点。 索引是存储引擎用于快速查找记录的一种数据结构,通过合理的使用数据库索引可以大大提高...
  • mysql需要在哪些字段上加索引

    千次阅读 2021-03-03 20:48:56
    mysql需要加索引的字段:1、经常出现在Where子句中的字段,特别是大表的字段,应该建立索引;2、经常与其他表进行连接的表,在连接字段上应该建立索引;3、分组字段或者排序字段应该创建索引;4、选择性高的字段上...
  • mysql中联合索引排序的问题

    千次阅读 2019-12-30 19:04:36
    在test数据库中,查询一组数据会根据id进行排序,而同样的条件在pre数据库中查询却没有根据默认的id主键来排序,于是激起了好奇心。 情况 正常情况下,在默认没有写order by条件的情况下,查询出来的结果会根据...
  • MYSQL order by排序索引关系总结

    千次阅读 2019-03-18 10:03:17
    MySQL InnoDB B-Tree索引使用Tips 这里主要讨论一下InnoDB B-Tree索引的使用,不提设计,只管使用。B-Tree索引主要作用于WHERE和ORDER BY子句。这里讨论的均在MySQL-Server-5.1.42测试 CREATE TABLE `friends` ( ...
  • 主要包括order by关键字排序优化(无过滤不排序;顺序错,必排序;方向反,必排序索引的选择;using filesort以及覆盖索引等内容)、group by关键字优化
  • mysql排序字段的索引认识

    千次阅读 2017-05-25 17:24:05
    因项目需要,需要对数据库一张表进行排序,取前10名 该表基数6W, invite_sign = 'geight' 条件加上后数据为2W 未优化前,sql语句如下: SELECT * FROM wk_active_gt_sign_info WHERE invite_sign = 'geight' ORDER ...
  • 引擎为mysiam~ 右边是用EXCEL把它们随机排列后的一个正常仿真数据表,把主键按照1-27再排列(不随机的话我在模拟数据时本来就是按顺序写的,再索引看不大出这个索引排序的过程) 也就是说右边的数据,使我们要测试的...
  • Mysql 索引

    千次阅读 2021-01-19 03:17:33
    常见索引类型(实现层面)首先不谈Mysql怎么实现索引的,先马后炮一下,如果让我们来设计数据库的索引,该怎么设计?我们首先思考一下索引到底想达到什么效果?其实就是想能够实现快速查找数据的策略,所以索引的实现...
  • mysql索引的排列顺序

    2021-01-15 13:45:50
    索引排序是按照定义索引的顺序来的 索引的顺序要遵循三个规则 要遵循最左前缀 无论是多个还是一个列的索引 都不应该跳过最左列 如果在查询语句当中 没有使用最左前缀的字段 就不会使用索引 index_key1_key2_key...
  • MySQL索引的本质,MySQL索引的实现,MySQL索引的数据结构
  • Mysql-索引排序

    千次阅读 2016-12-08 23:43:09
    上一篇刚刚通俗化的说明了B-TREE的几个结果与存储方式,其实跟...右边是用EXCEL把它们随机排列后的一个正常仿真数据表,把主键按照1-27再排列(不随机的话我在模拟数据时本来就是按顺序写的,再加索引看不大出这个索引
  • 本文首先会简单介绍SQL如何利用索引避免排序代价,然后会介绍MySQL实现排序的内部原理,并介绍与排序相关的参数,最后会给出几个“奇怪”排序例子,来谈谈排序一致性问题,并说明产生现象的本质原因。 1.排序优化与...
  • mysqlmysql时间字段怎么加索引

    千次阅读 2021-02-02 16:12:26
    查出5万条结果大约需要4.5秒,这个字段已经了普通索引,但是explain的类型还是ALL。怎么优化?语句是between and。换了>=和<=查询需要的时间也差不多。SELECT id,name,sex,city,headimg FROM user WHERE ...
  • MySql索引总结

    千次阅读 2021-01-25 12:47:17
    索引概念B+树索引分为聚集索引和非聚集索引(辅助索引),但是两者的数据结构都和B+树一样,区别是存放的内容。可以说数据库必须有索引,没有索引则检索过程变成了顺序查找,O(n)的时间复杂度几乎是不能忍受的。我们...
  • 一文搞懂MySQL索引所有知识点(建议收藏)

    万次阅读 多人点赞 2020-10-24 12:19:05
    Mysql索引 索引介绍 索引是什么 官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。 一般来说索引本身也很大,不可能全部存储在内存中,因此...
  • mysql索引

    千次阅读 2021-01-27 19:59:55
    索引(index)是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定有这样的一个student 表mysql> select * from student;+----+-----+--------+---------------+------+-------+-...
  • MySql创建索引时百支持ASC或DESC排序。 下面举例 创建表时同时度创建索知引降序排序(sname 字段上普通道索引降序) create table tbl1 ( id int unique, sname varchar(50), index tbl1_index_sname(sname desc) ); ...
  • 文章目录推荐阅读索引建立选择适合建立索引不适合建立索引索引失效原因使用索引注意...查询中排序的字段,排序的字段若通过索引去访问将大提升排序速度( 索引能够提高检索的速度和排序的速度) 查询中统计或分组的字段

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 185,746
精华内容 74,298
关键字:

mysql索引加排序

mysql 订阅