预计阅读时间:15分钟 小强前几篇文章介绍了mysql的索引原理以及sql优化的一些小技巧。mysql底层的算法选择哪种索引,有时候会和我们想象的不一样,大家可以继续往下看。...可以看到通过select出的字段是覆盖索引,MySQL底层使用了索引优化。...对于上面的这两种 name>'a' 和 name>'zzz'的执行结果, mysql最终是否选择走索引或者一张表涉及多个索引, mysql最终如何选择索引,可以通过trace工具来一查究竟,开启trace..., 索引MySQL最终会选择全表扫描。...,所以MySQL最终选择索引扫描。
相信大家对唯一索引和普通索引是有一定的了解的,那么在不同的业务场景,使用唯一索引还是普通索引呢,比如下面的场景 假设你在维护一个账户系统,每一个人都有一个唯一的身份证,而业务也能保证他的唯一性,此时我们设置唯一索引和普通索引其实都是可以的...那么我如何分析性能问题呢,我针对查询和更新两方面进行分析 查询过程 假设插叙的语句是select id from T where k=5,这个查询语句在索引树上查询的过程,先是通过B+树树根查询,找到叶子节点...,也就是上图的右下角的数据页,然后通过数据页内容通过二分法定位记录 对于普通索引,查询到满足的而第一个记录后,需要向后继续寻找,直到不满足条件 对于唯一索引,由于索引的唯一性,查询到数据后,直接停止查询...,既然已经在内存中了,我们直接更新内存会更快,就没有必要使用change buffer了,因此唯一索引的更新是不会使用change buffer,只有普通索引可以使用....索引的选择和实践 普通索引和唯一索引选择,其实,这类索引在查询能力上是没有差别,主要考虑的是对更新性能的影响,所以建议选择普通索引。
MySQL 普通索引和唯一索引该如何选择? 普通索引和唯一索引在查询能力上没啥差别,主要考虑对更新性能的影响,要尽量选择普通索引。接下来分析两种索引在查询语句和更新语句对性能的影响。...查询过程 MySQL InnoDB 是采用 B+ 树实现其索引结构。 ? image B+ 树的查找过程 如上图所示:现在需要查找 29 这个值。...https://dev.mysql.com/doc/refman/5.7/en/innodb-change-buffer.html 当需要更新一个数据页时,如果数据页在内存中就直接更新,如果这个数据页还没有内存中的话...MySQL 采用了什么方式提高更新效率呢?...MySQL 采用 WAL 技术,Write Ahead Loging,关键点是先写日志再写磁盘,具体执行如下:当有一条记录需要更新的时候,InnoDB 引擎会先把记录写到 redo log里,并更新内存
优化器如何选择索引? 优化器会根据扫描行数、是否使用临时表、是否排序等因素进行综合判断。 扫描行数如何判断? MySQL优化器只能根据统计信息来估算实际的记录数,该统计信息称为区分度。...MySQL中有两种存储索引统计的方式,可以通过设置参数innodb_stats_persistent 的值来选择: 设置为on,表示统计信息会持久化存储,N为20,M为10 设置为off,表示统计信息只存储在内存中...如何处理索引选择异常?...可以使用force index强制走某个索引,但该方法弊端过于明显,索引名称变更受影响,如果迁移到别的数据库语法不兼容 修改SQL语句,引导MySQL优化器选择正确的索引 新建一个更合适的索引,删除误用的索引...,来给优化器选择 -- 修改SQL语句,引导MySQL优化器选择正确的索引 explain select * from t where (a between 1 and 1000) and (b between
mysql添加索引的方法:可以通过【create table】语句来添加,如【CONSTRAINT PRIMARY KEY | INDEX [] [] 】,表示创建一般索引。...在mysql中可以在创建表(CREATE TABLE)的同时创建索引;也可以在创建表后创建索引,使用CREATE INDEX语句或ALTER TABLE 语句。...(视频教程推荐:mysql视频教程) 1、使用CREATE INDEX语句 可以使用专门用于创建索引的 CREATE INDEX 语句在一个已有的表上创建索引,但该语句不能创建主键。...mysql> CREATE TABLE tb_stu_info -> ( -> id INT NOT NULL, -> name CHAR(45) DEFAULT NULL, -> dept_id INT...示例:mysql> SHOW CREATE TABLE tb_stu_info\G *************************** 1. row ************************
mysql前缀索引的索引选择性 一....基础概念 在mysql中建立前缀索引的意义在于相对于整列建立索引,前缀索引仅仅是选择该列的部分字符作为索引,减少索引的字符可以节约索引空间,从而提高索引效率,但这样也会降低索引的选择性 关于索引的选择性...索引的选择性越高则查询效率越高,因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。...注意事项 ① 前缀索引是一种能使索引更小,更快的有效办法,但另一方面也有其缺点:mysql无法使用其前缀索引做ORDER BY和GROUP BY,也无法使用前缀索引做覆盖扫描 ② 要明确使用前缀索引的目的与优势...: .大大节约索引空间,从而提高索引效率 .对于 BOLB 、 TEXT 或者很长的 VARCHAR 类型的列,必须使用前缀索引,因为 MySQL 不允许索引这些列的完整长度 ③ 前缀索引会降低索引的选择性
1.添加PRIMARY KEY(主键索引) mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 例:alter table yx_marketing_details...add index(id); 2.添加UNIQUE(唯一索引) mysql>ALTER TABLE `table_name` ADD UNIQUE (`column` ) 3.添加INDEX...(普通索引) mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 4.添加FULLTEXT(全文索引) mysql...>ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 5.添加多列索引 mysql>ALTER TABLE `table_name` ADD INDEX
唯一索引和普通索引的区别? 普通索引的字段内容是可以重复的,唯一索引的字段内容不可重复。...,现有两种索引可供选择:唯一索引、普通索引。...,因此,在查询时,唯一索引和普通索引的性能差距很小。...对于普通索引来说,找到待插入位置,插入值,语句结束。 当目标页在内存中时,唯一索引和普通索引在插入时性能差距微乎其微。...引用自极客时间《MySQL实战45讲》
普通索引和唯一索引 基本概述 MySQL中可以创建普通索引与唯一索引,这两种索引的区别是: 普通索引(Non-Unique Index),也称为非唯一索引,它允许索引中的条目具有重复的键值。...普通索引的主要目的是加快查询速度,它并不关心数据的唯一性。 普通索引的特点: 可以包含重复的索引键值。 适用于快速查找具有相同索引值的多个记录的场景。 不保证数据的唯一性。...普通索引创建方法: CREATE INDEX idx_email ON customers(email); 唯一索引(Unique Index)是一种数据库索引,它要求索引中的所有键值都是唯一的。...如果尝试插入或更新记录以产生重复的索引键值,数据库将拒绝这种操作。 唯一索引的特点: 保证索引中的键值是唯一的。 适用于确保数据表中某一列或列组合的唯一性,例如身份证号、用户名等。...普通索引改成唯一索引后,某个业务的库内存命中率突然从 99% 降低到了 75%,整个系统处于阻塞状态,更新语句全部堵住。
索引的选择性: 是指不重复的索引值(也称基数)和数据表的记录总数(#T)的比值,范围从1/#T到1之间。索引的选择性越高则查询效率越高,因为选择性高的索引可以让MySQL在查找时过滤更多的行。...唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。...---- 为了保证前缀索引有较高的选择性,同时又不能太长可以使用计算完整列的选择性,并使前缀的索引性接近于完整列的选择性,方法如下: 第一步:计算完整列的选择性: 表名:city_demo, city是城市名称字段...第三步:创建前缀索引 mysql> ALTER TABLE `city_demo` ADD KEY(city(7)); ---- 其他 前缀索引缺点 MySQL 无法使用前缀索引做ORDER BY 和GROUP...MySQL原生并不支持反向索引,但是可以把字符串反转存储,并基于此建立前缀索引。可以通过触发器来维护这种索引。
现象 新建了一张员工表,插入了少量数据,索引中所有的字段均在where条件出现时,正确走到了idx_nap索引,但是where出现部分自左开始的索引时,却进行全表扫描,与MySQL官方所说的最左匹配原则...{ "considered_access_paths": [ { //可以看到这边MySQL...,因此选择了走索引 "condition_filtering_pct": 100, "rows_for_plan": 13, ... "join_execution": { "select#": 1, "steps": [ ] } } ] } 结论 MySQL...表数据量的大小,会影响索引的选择,具体的情况还是通过Explain和Optimizer Trace来查看与分析。
声明 本文所述的各种数据结构(二叉树等),均不考虑重复值的情况,本文简述各种数据结构的区别仅仅只是为了理解MySQL索引的需要而做的铺垫。...索引,是数据库管理系统中一个排序的数据结构,并用以协助快速查询、 更新数据库表中数据。 是的,索引是一种数据结构,但是那么多的数据结构中为何MySQL要选择B+树呢?...MySQL为何不选择平衡二叉树 既然平衡二叉树解决了普通二叉树的问题,那么mysql为何不选择平衡二叉树作为索引呢?...从上面我们可以看出B树效率相对于AVL树,在数据量大的情况效率已经提高了很多,那么为什么MySQL还是不选择B树作为索引呢? 那么接下来让我们先看看改良版的B+树,然后再下结论吧!...总结 本文简述了从二叉树到B+树之前的演进过程,并大致讲解了各种数据结构之间的差异以及MySQL为何最终会选择了B+树来作为索引。
但是有一种前缀索引不知道大家有没有用过或者听说过?今天松哥就来和大家聊一聊 MySQL 里边的这个前缀索引。...这有点类似于 Oracle 中对字段使用 Left 函数来建立函数索引,只不过 MySQL 的这个前缀索引在查询时是内部自动完成匹配的,并不需要使用 Left 函数。...索引的选择性越高则查询效率越高,因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行。 那有小伙伴要问了,是不是选择性越高的索引越好呢?当然不是!...那么我们该如何选择一个合适的索引选择性呢?索引前缀应该足够长,以便前缀索引的选择性接近于索引的整个列,即前缀的基数应该接近于完整列的基数。...松哥这里使用的数据样例是 MySQL 自己提供的数据样例,SQL 脚本下载链接:https://downloads.mysql.com/docs/world_x-db.zip。
Mysql有自己内部自动优化机制, 但有些自动优化机制可能不是最优的。这时候就需要人工去干预。 比如长期不优化表,Mysql判断出索引不优,就会不使用索引。...于是就清楚了,mysql选择索引不仅仅依据查询结构和索引结构,还会根据索引大概估算选择每种索引的数据量,然后选择他认为最快的索引。 ...可能是主键索引会比普通index更快,所以mysql最后选择了数据量跟大的id索引。 那么,如何解决这个问题呢? ...索引选择性是不重复的索引值也叫基数(cardinality)表中数据行数的比值,索引选择性=基数/数据行,基数可以通过“show index from 表名”查看。 ...高索引选择性的好处就是mysql查找匹配的时候可以过滤更多的行,唯一索引的选择性最佳,值为1。
1.前言在之前的文章中,我们讲解了索引的基本概念,相信你已经了解了唯一索引和普通索引的区别。今天我们就来讨论一下,在不同的业务场景下,应该选择普通索引,还是唯一索引?...由于身份证号字段比较大,不建议直接把身份证号当做主键,那么现在你有两个选择,要么给id_card字段创建唯一索引,要么创建一个普通索引。...如果业务代码已经保证了不会写入重复的身份证号,那么这两个选择逻辑上都是正确的。现在我要问你的是,从性能的角度考虑,你选择唯一索引还是普通索引呢?选择的依据是什么呢?...5.索引选择和实践回到我们文章开头的问题,普通索引和唯一索引应该怎么选择,其实,这两类索引在查询能力上没有差别,主要考虑的是对更新性能的影响,所以,我建议你尽量使用普通索引。...7.小结今天,我们从普通素银和唯一索引的选择开始,分析了数据的查询和更新过程,然后说明了change buffer的机制以及应用场景,最后讲到了索引选择的实践。
ACOUG年会杨长老的演讲中,曾提到一个问题, 一条SQL语句,两种执行计划的cost值相同,CBO是如何选择执行计划?...对于以下SQL, select * from z where a=1 and b=1; 根据10053显示,可以看出,IDX_Z_01和IDX_Z_02这两个索引,cost相同,CBO会选择何种执行计划...如果Cost值相同的索引的叶子块数量不同,则Oracle会选择叶子块数量较少的那个索引; 2. 如果Cost值相同的索引的叶子块数量相同,则Oracle会选择索引名的字母顺序在前面的那个索引。...先验证(2)的观点,从上面10053可以看出,两个索引的cost相同,叶子块数相同,此时CBO选择的是IDX_Z_01,因为他的名字,排在IDX_Z_02前面, Best:: AccessPath:...,10gR2及以上的版本,Oracle CBO还是有方法选择,索引叶子块是第一个条件,索引名称排序是第二个条件。
,针对MySQL数据库,不像Oracle数据库中有支持索引重建的语法(ALTER INDEX … REBUILD),在MySQL数据库中,重建索引具体有哪些方案呢?...相信大家也非常的熟悉了; 二、 ALTER TABLE Method 在验证之前,我们需要先知道如何可以查看MySQL中找到索引的创建或修改时间,可以通过如下的方式间接的进行查看: 1、表的创建时间...,可以间接推断索引的创建时间。...对于InnoDB存储引擎的表而言,对应的索引数据存储在ibd文件中,所以文件的创建时间或修改时间是间接判断索引创建时间。如果存储引擎为MyISAM的话,还有专门的索引文件MYI。...我们对比t1.ibd的创建时间,也可以间接验证了表和索引都REBUILD了。
MySQL如何强制指定索引一、介绍在平常的慢SQL优化过程中,往往会出现走不到索引的情况。有时候,确实是因为写的查询条件太差劲导致。...那么,该如何指定索引呢?...USE INDEX (index_name_bak) ON t1.id = t2.ref_idWHERE condition;和上面FORCE INDEX(index_name)不同的是,这边是允许MySQL...,如果有更好的索引,就选择更好的索引。...三、最后其实,我并不推荐这种指定索引的方式。强制索引可能会导致性能问题,一杆子打死,对后面优化器选择最优的索引不利。在大多数情况下,我们是没有必要强制指定索引
前言 Mysql数据库中的常见索引有多种方式,例如Hash索引,B-树索引,B+树索引,但是为啥mysql中默认是采用B+树索引索引呢?下面对这三种索引学习总结一下。B+树到底有啥优势?...哈希索引 哈希索引就是采用一定的哈希算法,把键值换算成新的哈希值,检索时不需要类似B+树那样从根节点到叶子节点逐级查找,只需一次哈希算法即可立刻定位到相应的位置,速度非常快。...从上面的图来看,B+树索引和哈希索引的明显区别是: 如果是等值查询,那么哈希索引明显有绝对优势,因为只需要经过一次算法即可找到相应的键值;当然了,这个前提是,键值都是唯一的。...,就没办法再利用索引完成范围查询检索; 哈希索引也没办法利用索引完成排序,以及like ‘xxx%’ 这样的部分模糊查询(这种部分模糊查询,其实本质上也是范围查询); 哈希索引也不支持多列联合索引的最左匹配规则...; B+树索引的关键字检索效率比较平均,不像B树那样波动幅度大,在有大量重复键值情况下,哈希索引的效率也是极低的,因为存在所谓的哈希碰撞问题。
MySQL优化器索引选择迷思。 高鹏(八怪)对本文亦有贡献。 1. 问题描述 群友提出问题,表里有两个列c1、c2,分别为INT、VARCHAR类型,且分别创建了unique key。...,用EXPLAIN查看执行计划,发现优化器优先选择了VARCHAR类型的c2列索引。 他表示很不理解,难道不应该选择看起来代价更小的INT类型的c1列吗? 2....问题分析 其实原因很简单粗暴:优化器认为这两个索引选择的代价都是一样的,只是优先选中排在前面的那个索引而已。...所以,优化器认为选择哪个索引都是一样的,就看哪个索引排序更靠前。...看来,MySQL优化器还是有必要进一步提高的哟 :) 测试使用版本:GreatSQL 8.0.25(MySQL 5.6.39结果亦是如此)。
领取专属 10元无门槛券
手把手带您无忧上云