提到MySQL,想必大多后端同学都不会陌生,提到B+树,想必还是有很大部分都知道InnoDB引擎的索引实现,利用了B+树的数据结构。 那InnoDB 的一棵B+树可以存放多少行数据?它又有多高呢?...所以InnoDB对这一点做了优化,新的数据放入新创建的页,不移动原有页面的任何记录。...降低页的利用率!这也是为什么在InnoDB中建议设置主键自增的原因! 这棵树的非叶子结点上存的都是主键,那如果一个表没有主键会怎么样?...有数据插入那就有删除,如果这个用户表频繁的插入和删除,那会导致数据页产生碎片,页的空间利用率低,还会导致树变的“虚高”,降低查询效率!这可以通过索引重建来消除碎片提高查询效率!...3InnoDB引擎索引高度 回到开篇的问题:InnoDB 的一棵B+树可以存放多少行数据?它又有多高呢?
为什么要进行SQL优化呢?...其实它就是一个树,我们用的比较多的就是B树、Hash树,在MySQL里面,用的就是B树索引; B树索引 首先我画一个图,假装这个是数据表,并且给age列加一个索引: 就把这个索引当成一个目录,也就是age...2.索引不是所有情况均可适用比如:少量数据、频繁更新的字段(如果数据表中的某一列经常会发生改变,那么这一列就不适合做索引) 3.索引确实可以提高查询效率,但是同时会降低增删改的效率,比如: 我们没有索引...,你改44,改成45,很好改,直接改就行了,如果你有索引,我不光要改表里面的44,我需要把B树里面的44也要改: 有些人就觉得不划算了,提升一个降低三个,这样就很不划算了,其实很划算的!...IO的使用率 2.降低CPU使用率 比如说我sql里面有一个order by desc 根据年龄降序或升序,如果没有索引,你需要把age全部拿出来全部排个序,但是如果有了索引,你就不需要排序了,B树本身就是一个排好序的结构
什么是索引(What is indexing)? 索引是对记录集的多个字段进行排序的方法。...这就是索引用来改进的地方。 假如索引记录只包含一个索引列以及一个指向原记录数据的指针,那么它显而易见会比原记录(多列)要小。所以索引本身所需要的磁盘块要更少,扫描数目也少。...(record pointer) Special 4 bytes 注意: MySQL里的指针按表大小的不同分别可能是 2, 3, 4 或 5 个字节。...鉴于创建索引需要额外的磁盘空间(上面的例子需要额外的277778个磁盘块),以及太多的索引会导致文件系统大小限制所产生的问题,所以对哪些字段建立索引,什么情况下使用索引,需要审慎考虑。...低基数的二分查找效率将降低为一个线性排序,而且查询优化器可能会在基数小于记录数某个比例时(如30%)的情况下将避免使用索引而直接查询原表,所以这种情况下的索引浪费了空间。
按照主键B+树的排列方式存放,子节点存放的就是数据。 如果没有主键,以第一列为聚集索引. 只有一个聚集索引。 普通索引指向聚集索引。 非聚集索引: 属于MyIsam。...另外,过多的复合索引,在有单字段索引的情况下,一般都是没有存在价值的,不但影响查询性能 还会降低数据增加删除时的性能,特别是对频繁更新的表来说,负面影响更大 索引的生效规则 对于一个不包含子查询的SQL...全文索引(LIKE优化) 优化的方式就是建立全文检索FULLTEXT 使用Mysql全文检索FULLTEXT的先决条件 MyISAM 引擎表和 InnoDB 引擎表(MySQL 5.6 及以上版本)都支持中文全文检索...explain显示了MySQL如何使用索引来处理select语句以及连接表。 可以帮助选择更好的索引和写出更优化的查询语句。...Not exists MYSQL优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行,就不再搜索了。
写在前面 在我们日常使用数据库的时候,肯定避免不了对数据库的优化。那么对数据库的优化又少了不索引的知识。 是的,建立索引能极大地提高查询的效率。...那么你知道吗,如果合理建立索引,可以更大地榨出数据库的性能——也就等同于进一步提高查询效率。 写下这篇文章就是为了记录一下对索引的优化,合理建立索引。...什么是索引 MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。 我们可以简单理解为:快速查找排好序的一种数据结构。...) where user_name = '我是用户名' and user_phone='110' 此种情况出现的概率比较小,毕竟mysql的解释器很复杂,也做了足够多的优化。...只有排查慢日志并且分析确定索引冲突的情况才需要强制使用索引。 优化 (总结) 只在经常使用的字段上建立索引,否则会拖慢数据更新和插入的速度。
优化和索引 提升SELECT 的最好方式是使用索引。索引条目作为表数据行的指针,使得查询能够很快的定位到所要查找的数据。所有的MySQL数据类型都可以创建索引。...索引只能创建在列全部的值上,而不能使用列部分值。 对于Innodb单表上的特定类型的全文索引,MySQL会有些优化以优化查询: FULLTEXT 查询只返回文档ID,或者文档ID和查询评级。...JOIN tbl_name ON tbl_name.key = expr 如果一个索引导致平均值集合的大小增加(索引的一个值对应数据表中的记录数),那么此索引可用性降低。...对于 = 比较符,无论表中有多少个NULL 值,为了优化,相关索引值集合大小为非NULL 值集合。然而,MySQL将不再收集和使用平均只集合大小。...无法使用HASH索引优化ORDER BY 操作。(这种类型的索引无法用于查询排序) MySQL无法通过此索引估计范围条件间的数据行(优化器对于范围查询的优化(选择索引))。
索引优化 1. 尽量全值匹配 当建立索引后,能再where条件中使用索引列,就尽量使用。...尽量使用覆盖索引 覆盖索引(只访问索引的查询(索引列和查询列一致)),而尽量避免 select * 6. 不等于要慎用 在使用不等于(!...(解决方案同上,覆盖索引) 8. LIKE查询要当心 like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作。...EXPLAIN select name,age from staffs where name='July' or name = 'z3' 除了索引优化之外,还有一些查询优化的技巧: 1....LIMIT 1 当查询结果只可能为1条数据的时候,加上LIMIT 1可以增加性能,MySQL数据库引擎会在找到一条数据后停止搜索,而不是继续往后查少下一条符合记录的数据。
首先通过索引可以很大程度的提高检索效率,这个就不解释了 关于B+树的相关知识和应用可以点击传送门 图解:深入理解MySQL索引底层数据结构与算法 图解:什么是B-树、B+树、B*树 1....const 表示通过一次索引查询就查询到了,一般对应索引列为primarykey 或者unique where语句中 指定 一个常量,因为只匹配一行数据,MYSQL能把这个查询优化为一个常量,所以非常快...MySQL 在查询时具体使用了哪些索引, 由 key 字段决定. key 显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。 key_len 显示MySQL决定使用的键长度。...,并且没有什么规律的话 会导致插入的时候比较随机 可能会导致较多的旋转、合并和拆分操作 降低效率,还会在磁盘空间中产生碎片 增加碎片整理的开销 如果你没有建立任何主键 而MySQL中InnoDB引擎是要求表必须有一个主键的...查询条件,尽量使用主键 一般利用辅助索引的话,需要进行两棵B+树的扫描 如果能够直接使用主键索引 那么就可以只进行一棵树的扫描 比如分页查询的优化,当数据量达到百万、千万级的时候 select
from s1 where name='egon' and email='asdf'; #可以 select * from s1 where email='alex@oldboy.com'; #不可以 mysql...=和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器 会帮你优化成索引可以识别的形式 #3.尽量选择区分度高的列作为索引,...) = ’2014-05-29’ 就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值, 但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。...所以语句应该写成create_time = unix_timestamp(’2014-05-29’); #5.需要在那些上面建立索引 解答: select user,host from mysql.user...索引一定要创建在 where 后的条件列上,而不是 select 后的选择数据的列上,另外,我们要尽量选择在唯一值多的大表上的列建立索引,例如:男女性别列唯一值, 不适合建立索引 慢查询优化 先运行看看是否真的很慢
写在前面 在我们日常使用数据库的时候,肯定避免不了对数据库的优化。那么对数据库的优化又少了不索引的知识。 是的,建立索引能极大地提高查询的效率。...那么你知道吗,如果合理建立索引,可以更大地榨出数据库的性能——也就等同于进一步提高查询效率。 写下这篇文章就是为了记录一下对索引的优化,合理建立索引。...什么是索引 MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。 我们可以简单理解为:快速查找排好序的一种数据结构。...) where user_name = '我是用户名' and user_phone='110' 此种情况出现的概率比较小,毕竟mysql的解释器很复杂,也做了足够多的优化。...长字符串可以使用前缀索引,只对字符串的前面一定字符长度建立索引。 组合索引的顺序合理优化(会有新文章介绍) 当多个单字段索引发生冲突时,强制使用某个索引。
最左前缀示范 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: 0 mysql> 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...-- 不使用索引 - count(1)或count(列)代替count(*)在mysql中没有差别了 - create index xxxx on tb(title(19)) #text类型,必须制定长度
1、MyISAM是MySQL 5.5之前版本默认的存储引擎,从5.5之后,InnoDB开始成为MySQL默认的存储引擎。MyISAM和InnoDB都是使用B+树实现主键索引、唯一索引和非主键索引。...不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助,例如知道了InnoDB的索引实现后,就很容易明白为什么不建议使用过长的字段作为主键,因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大...如果在根结点插入新元素,空间满了,则进行分裂操作,这样原来的根结点中的中间关键字元素向上移动到新的根结点中,因此导致树的高度增加一层。...这是数据库选用B+树的最主要原因。 当然,B树的好处,就是成功查询特别有利,因为树的高度总体要比B+树矮。不成功的情况下,B树也比B+树稍稍占一点点便宜。...mysql 底层存储是用B+树实现的,因为MySQL的索引是存储在磁盘上的。内存中B+树是没有优势的,但是一到磁盘,B+树的威力就出来了。
上图就是一棵B+树,每个部分有3个主要概念:物理磁盘块、数据项(蓝色)、指针(红色) 如磁盘块1,包含数据项 17、35,包含指针 P1、P2、P3,P1指向小于17的磁盘块,P2指向在17和35之间的磁盘块...真实的数据存于叶子节点中,即 3、5、9、10、13、15、28、29、36、60、75、79、90、99 非叶子节点中并不存放真实数据项,只存放指引搜索方向的数据项,如 17、35 并不真实存在于数据表中 B+树查找过程...内存中做二分查找找到29,结束查询 总计三次IO,即可找到目标数据项 3层的B+树可以表示上百万的数据,对查询性能的提高是巨大的
例如下面的2个写法是等价的,因为MySQL会将查询的顺序优化成和联合索引的顺序一致 select * from table where a = '1' and b = '1' select * from...MySQL中,有两种方式生成有序结果集: 通过有序索引顺序扫描直接返回有序数据 Filesort排序,对返回的数据进行排序 因为索引的结构是B+树,索引中的数据是按照一定顺序进行排列的,所以在排序查询中如果能利用索引...更新会变更 B+ 树,更新频繁的字段建立索引会大大降低数据库性能。...'; 这时我们可以索引开始的部分字符,这样可以大大节约索引空间,从而提高索引效率,但这样也会降低索引的区分度。...,要么对超过特定阈值的页数进行 SQL 改写,单开一文来讲 推荐阅读 MySQL索引为什么要用B+树实现?
作者:邵建永 索引基础 索引用途 索引有很多用途,并不仅仅是优化查询性能,这些用途包括: 保持数据完整性(主键和唯一索引) 优化数据检索性能(使用索引进行条件匹配和模式匹配) 改进表的连接操作(使用索引连接表...) 优化结果排序操作(ORDER BY) 优化聚合数据操作(GROUP BY) 创建索引 创建表时指定索引 主键索引: PRIMARY KEY index-name 非主键索引: UNIQUE KEY...查看索引 可以用SHOW INDEXES命令查看索引的信息,该命令输出包括索引的类型和当前报告的MySQL索引基数等信息。...首先,索引加快了检索速度,但却降低了许多写入操作的速度,包括插入、删除,以及包含索引列的更新。其次,索引要占用磁盘空间,索引越多,它所占用的磁盘空间也就越大。...我们可以借助这条命令深入了解MySQL基于开销的优化器,还可以获得很多可能被优化器考虑到的访问策略的细节(possible_keys,被评估的索引),以及当运行SQL语句时哪种策略会被优化器采用。
索引 文章已同步至GitHub开源项目: Java超神之路 概念 MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。...一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上 优势 类似大学图书馆建书目索引,提高数据检索的效率,降低数据库的IO成本 通过索引列对数据进行排序,降低数据排序的成本...,降低了CPU的消耗 劣势 实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用空间的虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE...,或优化查询语句 mysql索引分类 主键索引 设定为主键后数据库会自动建立索引,innodb为聚簇索引 单值索引 即一个索引只包含单个列,一个表可以有多个单列索引 唯一索引 索引列的值必须唯一...- 真实的情况是,3层的b+树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的,如果没有索引,每个数据项都要发生一次IO,那么总共需要百万次的IO,显然成本非常非常高。
语句 2.3优化order by语句 2.4优化group by 2.5优化子查询 2.6优化limit查询 1.使用索引优化 索引是数据库优化最常用也是最重要的手段之一, 通过索引通常可以帮助用户解决大多数的...MySQL 的性能优化问题。..., 通过索引通常可以帮助用户解决大多数的MySQL 的性能优化问题。...,也会降低性能。...,也会降低性能。
1.mysql索引 MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。 索引分单列索引和组合索引。...上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。...firstmatch(tb_name):5.6.x开始引入的优化子查询的新特性之一,常见于where字句含有in()类型的子查询。...如果内表的数据量比较大,就可能出现这个 loosescan(m..n):5.6.x之后引入的优化子查询的新特性之一,在in()类型的子查询中,子查询返回的可能有重复记录时,就可能出现这个 filtered...9.MySQL索引的优化 对于创建的多列索引,只要查询条件使用了最左边的列,索引一般就会被使用。
使用覆盖索引,少使用select* 2.6. mysql在使用不等于(!=或者)的时候无法使用导致全表扫描 2.7....字符串不加单引号导致索引失效 2.11. 单表查询优化 2.12. 两表查询优化 2.13. 三表查询优化 2.14. 小表驱动大表 2.15. order by 排序的索引生效 2.15.1....mysql的全表扫描 尽量使用覆盖索引,比如索引为name,age,address的组合索引,那么尽量覆盖这三个字段之中的值,mysql将会直接在索引上取值(using index)。...并且返回值不包含不是索引的字段 mysql在使用不等于(!...单表查询优化 在经常查询或者排序的字段建立索引 两表查询优化 我们一般会使用联合查询,比如left Join,right Join 我们在不建立索引的情况下,如下: -- 没有索引,全表扫描 explain