MySQL创建高性能索引考点

淮南草 2020-05-14

MySQL索引的基础和类型

索引对性能的影响

  • 大大减少服务器需要扫描的数据量
  • 帮助服务器避免排序和临时表
  • 将随机I/O变顺序I/O
  • 大大提高查询速度，降低写的速度、占用磁盘

索引的使用场景

  • 对于非常小的表，大部分情况下全表扫描效率更高
  • 中到大型表，索引非常有效
  • 特大型的表，建立和使用索引的代价将随之增长，可以使用分区技术来解决

索引的类型

  • 索引有很多的类型，都是实现在存储引擎层的
  • 普通索引：最基本的索引，没有任何约束限制
  • 唯一索引：与普通索引类似，但是具有唯一性约束
  • 主键索引：特殊的唯一索引，不允许有空值
  • 一个表只能有一个主键索引，可以有多个唯一索引
  • 主键索引一定是唯一索引，唯一索引不一定是主键索引
  • 主键可以与外键构成参照完整性约束，防止数据不一致
  • 组合索引：将多个列组合在一起创建索引，可以覆盖多个列
  • 外键索引：只有InnoDB类型的表才可以使用外键索引，保证数据的一致性，完整性和实现级联操作
  • 全文索引：MySQL自带的全文索引只能用于MyISAM，并且只能对英文进行全文检索

MySQL索引的创建原则

原则：

  1. 最适合索引的列是出现在where子句中的列，是连接子句中的列而不是出现在select关键字后面的列
  2. 索引列的基数越大，索引的效果越好
  3. 对字符串进行索引，应该制定一个前缀长度，可以节省大量的索引空间
  4. 根据情况创建复合索引，复合索引可以提高查询效率
  5. 避免创建过多的索引，索引会额外占用磁盘空间，降低写操作效率
  6. 主键尽可能的选择较短的数据类型，可以有效减少索引的磁盘占用，提高查询效率

MySQL索引的注意事项

注意：

  1. 复合索引遵循前缀原则
  2. like查询，%不能在前，可以使用全文索引
  3. column is null可以使用索引
  4. 如果MySQL估计使用索引比全表扫描更慢，会放弃使用索引
  5. 如果or之前的条件中的列有索引，后面的咩有，索引都不会被用到　
  6. 列类型是字符串，查询时一定要给值加引号，否则索引失效
淮南草

淮南草

相关推荐

MySql索引使用策略分析

(建立索引会占用磁盘空间的索引文件。2.很少数据的列也不应该建立索引,比如 一个性别字段 0或者1,在查询中,结果集的数据占了表中数据行的比例比较大,mysql需要扫描的行数很多,增加索引,并不能提高效率

moyekongling / 0评论 2020-11-13

MyISAM与InnoDB索引实现对比详解

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。这里设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引示意。虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然

chenjiazhu / 0评论 2020-09-29

导致MySQL索引失效的一些常见写法总结

最近一直忙着处理原来老项目遗留的一些SQL优化问题，由于当初表的设计以及字段设计的问题，随着业务的增长，出现了大量的慢SQL，导致MySQL的CPU资源飙升，基于此，给大家简单分享下这些比较使用的易于学习和使用的经验。这次的话简单说下如何防止你的索引失效。

liuweiq / 0评论 2020-09-09

MySQL索引的各种类型

索引是数据库存储引擎用于快速查找到指定数据的一种数据结构。可以用新华字典做类比：如果新华字典中对每个字的详细解释是数据库中表的记录，那么按部首或拼音等排序的目录就是索引，使用它可以让我们快速查找的某一个字详细解释的位置。在MySQL中，存储引擎也是用了类似

silencehgt / 0评论 2020-09-07

MySQL索引的基本语法

索引是排好序的数据结构！可以用在 where 条件查找的字段，和order by 排序的字段，有了索引，便可以快速地定位数据所在的物理地址并找出来。ALTER TABLE 表名 ADD INDEX 索引名 ;ALTER TABLE 表名 DROP INDE

mrandy / 0评论 2020-08-15

mysql 14 覆盖索引+回表

MySQL可以利用索引返回select列表中的字段值。而不必根据主键再次读取聚簇索引数据文件查到数据，也就是平时所说的不需要回表操作。覆盖索引其实是索引覆盖的意思，索引字段就已经囊括select查询的字段，即索引字段覆盖了需查询的字段。t; 即id是聚集索

Accpcjg / 0评论 2020-08-02

MySQL中的不可见索引、倒序索引

大多数MySQL索引在BTREE中存储

写程序的赵童鞋 / 0评论 2020-07-18

MySQL 索引结构 hash 有序数组

Hash本身是一种函数，又被称为散列函数。不过在有大量重复值得情况下，hash索引的效率极低，因为要频发地处理Hash冲突。对于Hash索引的联合索引，是将联合索引字段值相捆绑然后计算Hash值的，无法利用对单一字段的Hash值。当字段的重复度低，而且经常

wangshuangbao / 0评论 2020-07-05

MySQL索引原理

　　1.B+树的叶子节点包含了所有的数据；　　2.叶子节点可以直接访问其他叶子节点，不需要回到根节点；　　操作系统中，默认的是每一页是4KB；而InnoDB存储引擎，默认每一页是16KB；　　UserRecord（行记录）区域，保存的是真正的数据，会包含多

bluetears / 0评论 2020-07-05

MySQL索引 索引分类 最左前缀原则 覆盖索引 索引下推 联合索引顺序

索引就是帮助RDBMS高效获取数据的数据结构。索引可以让我们避免一行一行进行全表扫描。普通索引 INDEX或者key index_name 没有任何约束，一张表可以有多个普通索引。唯一索引 UNIQUE INDEX index_name 在不同索引

bendan / 0评论 2020-07-04

MySql索引失效的十大杂症

最近生产爆出一条慢sql，原因是用了or和!于是，总结了索引失效的十大杂症，希望对大家有帮助，加油。如果它一开始就走全表扫描，直接一遍扫描就完事。mysql是有优化器的，处于效率与成本考虑，遇到or条件，让索引失效，看起来也合情合理嘛。在联合索引中，查询条

minggehenhao / 0评论 2020-07-04

数据库索引过长(Specified key was too long; max key length is 767 bytes)

在新建数据库或者导入别人的数据库脚本的时候会出现Specified key was too long; max key length is 767 bytes错误。这是由于数据库建立索引的时候索引的数据过长导致的。我发现报错的那张表建立了一个varchar

AngelicaA / 0评论 2020-07-04

MySQL优化之索引篇: Explain工具

explain专门用来做SQL语句的调优的，在select语句前面加一个explain可以把SELECT的执行过程都列出来，包括哪些用了索引，哪些没用索引，哪些查询是全表查询，哪些是索引查询等 . 使用效果如下图 :. 执行编号，标识select所属的行。

minggehenhao / 0评论 2020-06-21

1071 -Specified key was too long; max key length is 767 bytes

执行下面语句报1071 -Specified key was too long; max key length is 767 bytes. 这是因为数据库表采用utf8编码，其中varchar的column进行了唯一键索引而mysql默认情况下单个列的索引

TMD咯MySQL / 0评论 2020-06-16

导致索引失效的情况

——按需取数据用多少取多少。

淮南草 / 0评论 2020-06-16

mysql对于很长的字符列的索引方案

SELECT * FROM urlT WHERE url_hash = hash AND url = ‘www.blog.csdn.net‘;

TNTMysql工程师 / 0评论 2020-06-16

MySQL 索引失效

mysql 再使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描。全列匹配我最爱，最左前缀要遵守；带头大哥不能死，中间兄弟不能断；索引列上少计算，范围之后全失效；LIKE百分写最右，覆盖索引不写星；不等空值还有or，索引失效要少用；

Iamready / 0评论 2020-06-14

MySQL索引介绍

索引类似大学图书馆建书目索引，可以提高数据检索的效率，降低数据库的IO成本。MySQL在300万条记录左右性能开始逐渐下降，虽然官方文档说500~800w记录，所以大数据量建立索引是非常有必要的。MySQL提供了Explain，用于显示SQL执行的详细信息

variab / 0评论 2020-06-14

Mysql索引类型与基本用法实例分析

是最基本的索引，它没有任何限制。与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。一般是在建表的时候同时创建主键索引。主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引配合

MySQL源氏boy / 0评论 2020-06-14

MySQL8新特性之降序索引底层实现详解

大家可能对索引比较熟悉，而对降序索引比较陌生，事实上降序索引是索引的子集。上面sql的意思是在t1表中，针对b,c,d三个字段创建一个联合索引。asc表示的是升序，使用这种语法创建出来的索引叫做升序索引。在Mysql8中，Collation字段的结果为D,

mengtianyalll / 0评论 2020-06-14

面试官：小伙子，你给我说一下你对MySQL索引的理解吧

索引是帮助MySQL高效获取数据的数据结构。索引非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。索引能够轻易将查询性能提高好几个数量级，总的来说就是可以明显的提高查询效率。这里所描述的是索引存储时保存的形式，因此，尽量以自增id作为I

敏敏张 / 0评论 2020-06-13

查询与索引优化

查看配置的sqlshow VARIABLES like ‘slow_query_log‘;mysqldumpslow 汇总除查询条件外其他完全相同的SQL,并将分析结果按照参数中所指定的顺序输出。执行计划--key_len ：表示索引中使用的字节数，可通过

gsmfan / 0评论 2020-06-12

面试必看！凭借着这份 MySQL 高频面试题，我拿到了京东，字节的offer！

本文主要受众为开发人员,所以不涉及到MySQL的服务部署等操作,且内容较多,大家准备好耐心和瓜子矿泉水.前一阵系统的学习了一下MySQL,也有一些实际操作经验,偶然看到一篇和MySQL相关的面试文章,发现其中的一些问题自己也回答不好,虽然知识点大部分都知道

weiguoxin / 0评论 2020-06-11

mysql 常见优化

LIMIT 能很好地解决分页问题，但如果 offset 过大的话，会造成严重的性能问题，原因主要是因为 MySQL 每次会把一整行都扫描出来，扫描 offset 遍，找到 offset 之后会抛弃 offset 之前的数据，再从 offset 开始读取 1

cyyking / 0评论 2020-06-11

MySQL 索引

即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。全文索引并不是和MyISAM一起诞生的，它的

好记忆也需烂 / 0评论 2020-06-10

生产上用对了这些场景下的索引，技术总监夸我有点东西

生产上为了高效地查询数据库中的数据，我们常常会给表中的字段添加索引，大家是否有考虑过如何添加索引才能使索引更高效，考虑如下问题。为啥有时候明明添加了索引却不生效。看了本文相信你会对索引的原理有更清晰的认识。不过在查询的时候要记得同时带上 url 和 url

康慧欣 / 0评论 2020-06-10

mysql 强制索引和禁止某个索引

select * from table force index limit 2;select * from table ignore index limit 2;

勇往直前 / 0评论 2020-06-10

mysql性能分析之explain

Explain命令在解决数据库性能上是第一推荐使用命令，大部分的性能问题可以通过此命令来简单的解决，Explain可以用来查看SQL语句的执行效 果，可以帮助选择更好的索引和优化查询语句，写出更好的优化语句。当type 显示为 “index” 时，并且Ex

liuweiq / 0评论 2020-06-09

Python学习第101天（mysql索引）

本文从如何建立mysql索引以及介绍mysql的索引类型,再讲mysql索引的利与弊,以及建立索引时需要注意的地方。首先:先假设有一张表,表的数据有10W条数据,其中有一条数据是nickname=‘css‘,如果要拿这条数据的话需要些的sql是 SELEC

debugjoker / 0评论 2020-06-07

MySQL数据库优化

尽可能地把字段设置为 NOT NULL. 1）where，group by ，order by字段后面用索引。2）对字符串进行索引，应该设置一个前缀长度，可以节省大量的索引空间。3）查询多的更适合用索引， DML多的不适合用索引。4）组合索引优于多个单独索

勇往直前 / 0评论 2020-06-07

MySQL 索引

几乎所有的索引都是建立在字段之上.增加索引的前提条件: 索引本身会产生索引文件 ,会非常耗费磁盘空间.如果某个字段需要作为查询的条件经常使用, 那么可以使用索引;

xiaojiang0 / 0评论 2020-06-05

mysql 重新整理——索引优化explain字段介绍二 [十]

优化的标准为至少达到range级别，最好是ref级别。system 表只有一行记录，这是const类型的特列，基本不会出现。all 表示全表扫描来查询性能最低。key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。key_len 显

webliyang / 0评论 2020-06-03

SQL优化

因为Oracle总是先解析SQL语句，把小写的字母转换成大写字母再执行。数据库在解析的过程中会将 * 依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接，

talkingDB / 0评论 2020-05-31

mysql之索引失效的情况

图二根据kq_time字段查询并没有使用索引,我们可以根据kq_time换一种写法,他就可以走索引了,见图三。=导致了全表扫描,<>,也会导致全表扫描,我这里就不演示了

CosEmon / 0评论 2020-05-30

mysql数据库索引数据模型

另外，非叶子几点可以存储多个索引，叶子节点有前后元素的指针

jiong / 0评论 2020-05-29

MYSQL备忘

select 字段 from 表 group by 字段 having count(*) >1;ALTER TABLE 数据表 DROP INDEX 字段;

msmysql / 0评论 2020-05-29

mysql增删改字段，重命名替换字段

ALTER TABLE 表 {ADD ADD{COLUMN 字段类型 [ (字长)] [NOT NULL] [CONSTRAINT 索引 ] |. field 指定在表内增加或删除的字段。size字段的字符长度。了解更多有关如何构造本索引的信息，请看

贤冰 / 0评论 2020-05-28

SELECT COUNT(*)成本开销

原因是会造成全表扫描，有位读者说这种说法是有问题的，实际上针对无 where_clause 的 COUNT(*)，MySQL 是有优化的，优化器会选择成本最小的辅助索引查询计数，其实反而性能最高，这位读者的说法对不对呢。但有个前提，在 MySQL 5.6

暗夜之城 / 0评论 2020-05-27

【从头开始学】数据结构02

是一种力图将数据元素的存储位置与关键码之间建立确定对应关系的查找技术。通过转换函数把关键值转换为地址存储，在查询时又通过函数求得地址值取得元素。散列存储又称HASHLIST，Hash表存在的优点显而易见，能够在常数级的时间复杂度上进行查找，并且插入数据和删

minkee / 0评论 2020-05-26

唯一索引和普通索引怎么选择

前段时间还在忙，终于又出点空更新了，虽然不是所有的付出都有收获，很多时候需要我们先付出才可以看到希望。这时候，如果来了一个查询动作，刚好要查这条数据的情况下，Mysql 会从磁盘上读取这个数据页，然后发现 change buffer 有修改了这个数据页，会

gululululu / 0评论 2020-05-21

MySQL （InnoDB）在什么情况下无法使用索引

InnoDB 索引使用的数据结构是 B+ 树。每次搞不懂 B+ 树的时候，可以想想小时候怎么查字典的。a 索引过滤之后是有序的，所以可以使用 b 索引进行过滤，b 过滤之后是无序的。这个只是有可能，因为 innodb 底层是基于成本选择使用索引的。但是如果

CosEmon / 0评论 2020-05-20

mysql 索引优化 Explain的使用

MySQL提供了Explain，用于显示SQL执行的详细信息，可以进行索引的优化。关联查询过多等等。索引使用情况在possible_keys、key和key_len三列 。explain 查询后，查看SQL语句的type类型 ， 索引是否使用。

阳历六月 / 0评论 2020-05-19

MySQL索引优化-性能分析Explain（转）

使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或是表结构的性能瓶颈。select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。id相同，执行顺序从上到下，

QianChia / 0评论 2020-05-19

基于MySQL 的 SQL 优化总结

文章首发于我的个人博客，欢迎访问。在数据库运维过程中，优化 SQL 是 DBA 团队的日常任务。例行 SQL 优化，不仅可以提高程序性能，还能减低线上故障的概率。目前常用的 SQL 优化方式包括但不限于：业务层优化、SQL 逻辑优化、索引优化等。其中索引优

adsadadaddadasda / 0评论 2020-05-18

一分钟掌握MySQL的InnoDB引擎B+树索引

MySQL的InnoDB索引结构采用B+树，B+树什么概念呢，二叉树大家都知道，我们都清楚随着叶子结点的不断增加，二叉树的高度不断增加，查找某一个节点耗时就会增加，性能就会不断降低，B+树就是解决这个问题的。看下图我们说说B树的特点，很明显一个节点存储的数

inhumming / 0评论 2020-05-17

mysql中的索引优化

比较简单的就是也是最基本的优化方式就是给数据库表添加适合索引，提升查询性能。

翡翠谷 / 0评论 2020-05-16

MySQL索引学习

MySQL官方对索引的定义为：索引是帮助MySQL高效获取数据的数据结构。树，例如平衡二叉搜索树，查询/插入/修改/删除的平均时间复杂度都是O；可以看到，不管是读请求，还是写请求，哈希类型的索引，都要比树型的索引更快一些，那为什么，索引结构要设计成树型呢？

azhou / 0评论 2020-05-14

mysql索引原理与用法实例分析

本文实例讲述了mysql索引原理与用法。分享给大家供大家参考，具体如下：。索引创建在数据表对象上，由一个或多个字段组成，这若干个字段组成“键”存储到数据结构中。mysql支持的索引类型有：普通索引、唯一索引、全文索引、单列索引、多列索引、空间索引。普通索引

这些年来 / 0评论 2020-04-16

为什么选择B+树作为索引结构？

mysql的B+树索引 查找使用了二分查找，redis 跳表也使用了二分查找法，kafka查询消息日志也使用了二分查找法，二分查找法时间复杂度O;在MySQL中，主要有四种类型的索引，分别为：B-Tree索引，Hash索引，Fulltext索引和R-Tre

MissFuTT / 0评论 2020-05-11