24个经典的MySQL索引问题，你都遇到过哪些？
1、什么是索引？
索引是⼀种特殊的⽂件(InnoDB数据表上的索引是表空间的⼀个组成部分)，它们包含着对数据表⾥所有记录的引⽤指针。
索引是⼀种数据结构。数据库索引，是数据库管理系统中⼀个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使⽤B树及其变种B+树。
更通俗的说，索引就相当于⽬录。为了⽅便查书中的内容，通过对内容建⽴索引形成⽬录。索引是⼀个⽂件，它是要占据物理空间的。
2、索引有哪些优缺点？
索引的优点
（1）可以⼤⼤加快数据的检索速度，这也是创建索引的最主要的原因。
（2）通过使⽤索引，可以在查询的过程中，使⽤优化隐藏器，提⾼系统的性能。
索引的缺点
（1）时间⽅⾯：创建索引和维护索引要耗费时间，具体地，当对表中的数据进⾏增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执⾏效率；
（2）空间⽅⾯：索引需要占物理空间。
3、索引使⽤场景（重点）
where
上图中，根据id查询记录，因为id字段仅建⽴了主键索引，因此此SQL执⾏可选的索引只有主键索引，如果有多个，最终会选⼀个较优的作为检索的依据。
-- 增加⼀个没有建⽴索引的字段altertable innodb1 add sex char(1);
-- 按sex检索时可选的索引为nullEXPLAINSELECT*from innodb1 where sex='男';
可以尝试在⼀个字段未建⽴索引时，根据该字段查询的效率，然后对该字段建⽴索引（alter table 表名 add index(字段名)），同样的SQL执⾏的效率，你会发现查询效率会有明显的提升（数据量越⼤越明显）。
order by
当我们使⽤order by将查询结果按照某个字段排序时，如果该字段没有建⽴索引，那么执⾏计划会将查询出的所有数据使⽤外部排序（将数据从硬盘分批读取到内存使⽤内部排序，最后合并排序结果），这个操作是很影响性能的，因为需要将查询涉及到的所有数据从磁盘中读到内存（如果单条数据过⼤或者数据量过多都会降低效率），更⽆论读到内存之后的排序了。
但是如果我们对该字段建⽴索引alter table 表名 add index(字段名)，那么由于索引本⾝是有序的，因
此直接按照索引的顺序和映射关系逐条取出数据即可。⽽且如果分页的，那么只⽤取出索引表某个范围内的索引对应的数据，⽽不⽤像上述那取出所有数据进⾏排序再返回某个范围内的数据。（从磁盘取数据是最影响性能的）
join
导航代码在线生成对join语句匹配关系（on）涉及的字段建⽴索引能够提⾼效率
索引覆盖
如果要查询的字段都建⽴过索引，那么引擎会直接在索引表中查询⽽不会访问原始数据（否则只要有⼀个字段没有建⽴索引就会做全表扫描），这叫索引覆盖。因此我们需要尽可能的在select后只写必要的查询字段，以增加索引覆盖的⼏率。
这⾥值得注意的是不要想着为每个字段建⽴索引，因为优先使⽤索引的优势就在于其体积⼩。
4、索引有哪⼏种类型？
主键索引
数据列不允许重复，不允许为NULL，⼀个表只能有⼀个主键。
唯⼀索引
数据列不允许重复，允许为NULL值，⼀个表允许多个列创建唯⼀索引。
（1）可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯⼀索引
（2）可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2)；创建唯⼀组合索引
普通索引
基本的索引类型，没有唯⼀性的限制，允许为NULL值。
（1）可以通过ALTER TABLE table_name ADD INDEX index_name (column)；创建普通索引
（2）可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3)；创建组合索引
全⽂索引
是⽬前搜索引擎使⽤的⼀种关键技术。
可以通过ALTER TABLE table_name ADD FULLTEXT (column)；创建全⽂索引
5、索引的数据结构（b树，hash）
索引的数据结构和具体存储引擎的实现有关，在MySQL中使⽤较多的索引有Hash索引，B+树索引等，⽽我们经常使⽤的InnoDB存储引擎的默认索引实现为：B+树索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝⼤多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余⼤部分场景，建议选择BTree索引。
（1）B树索引
mysql通过存储引擎取数据，基本上90%的⼈⽤的就是InnoDB了，按照实现⽅式分，InnoDB的索引类型⽬前只有两种：BTREE（B树）索引和HASH索引。B树索引是Mysql数据库中使⽤最频繁的索引类型，基本所有存储引擎都⽀持BTree索引。通常我们说的索引不出意外指的就是（B树）索引（实际是⽤B+树实现的，因为在查看表索引时，mysql⼀律打印BTREE，所以简称为B树索引）
查询⽅式：
主键索引区:PI(关联保存的时数据的地址)按主键查询,
普通索引区:si(关联的id的地址，然后再到达上⾯的地址)。所以按主键查询，速度最快
B+tree性质：
1）n棵⼦tree的节点包含n个关键字，不⽤来保存数据⽽是保存数据的索引。
2）所有的叶⼦结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶⼦结点本⾝依关键字的⼤⼩⾃⼩⽽⼤顺序链接。3）所有的⾮终端结点可以看成是索引部分，结点中仅含其⼦树中的最⼤（或最⼩）关键字。
4）B+ 树中，数据对象的插⼊和删除仅在叶节点上进⾏。
5）B+树有2个头指针，⼀个是树的根节点，⼀个是最⼩关键码的叶节点。
（2）哈希索引
error0199 system security简要说下，类似于数据结构中简单实现的HASH表（散列表）⼀样，当我们在mysql中⽤哈希索引时，
主要就是通过Hash算法（常见的Hash 算法有直接定址法、平⽅取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的Hash值，与这条数据的⾏指针⼀并存⼊Hash表的对应位置；如果发⽣Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。当然这只是简略模拟图。
6、索引的基本原理
索引⽤来快速地寻那些具有特定值的记录。如果没有索引，⼀般来说执⾏查询时遍历整张表。
索引的原理很简单，就是把⽆序的数据变成有序的查询
（1）把创建了索引的列的内容进⾏排序
（2）对排序结果⽣成倒排表
（3）在倒排表内容上拼上数据地址链
（4）在查询的时候，先拿到倒排表内容，再取出数据地址链，从⽽拿到具体数据
7、索引算法有哪些？
索引算法有 BTree算法和Hash算法
BTree算法
BTree是最常⽤的mysql数据库索引算法，也是mysql默认的算法。因为它不仅可以被⽤在=,>,>=,<,<=和between这些⽐较操作符上，⽽且还可以⽤于like操作符，只要它的查询条件是⼀个不以通配符开头的常量，例如：
-- 只要它的查询条件是⼀个不以通配符开头的常量select*fromuserwhere name like'jack%';
-- 如果⼀通配符开头，或者没有使⽤常量，则不会使⽤索引，例如： select*fromuserwhere name like'%jack';
Hash算法
Hash Hash索引只能⽤于对等⽐较，例如=,<=>（相当于=）操作符。由于是⼀次定位数据，不像BTree索引需要从根节点到枝节点，最后才能访问到页节点这样多次IO访问，所以检索效率远⾼于BTree索引。
8、索引设计的原则？
（1）适合索引的列是出现在where⼦句中的列，或者连接⼦句中指定的列
（2）基数较⼩的类，索引效果较差，没有必要在此列建⽴索引
（3）使⽤短索引，如果对长字符串列进⾏索引，应该指定⼀个前缀长度，这样能够节省⼤量索引空间
（4）不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进⾏更新甚⾄重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。
9、创建索引的原则（重中之重）
索引虽好，但也不是⽆限制的使⽤，最好符合⼀下⼏个原则
1）最左前缀匹配原则，组合索引⾮常重要的原则，mysql会⼀直向右匹配直到遇到范围查询(>、<、between、like)就停⽌匹配，⽐如a = 1 and b = 2 and c > 3 and d = 4 如果建⽴(a,b,c,d)顺序的索引，d是⽤不到索引的，如果建⽴(a,b,d,c)的索引则都可以⽤到，a,b,d的顺序可以任意调整。
2）较频繁作为查询条件的字段才去创建索引
数据结构中快速排序
3）更新频繁字段不适合创建索引
4）若是不能有效区分数据的列不适合做索引列(如性别，男⼥未知，最多也就三种，区分度实在太低)
5）尽量的扩展索引，不要新建索引。⽐如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
6）定义有外键的数据列⼀定要建⽴索引。
7）对于那些查询中很少涉及的列，重复值⽐较多的列不要建⽴索引。
8）对于定义为text、image和bit的数据类型的列不要建⽴索引。
10、创建索引的三种⽅式，删除索引
第⼀种⽅式：在执⾏CREATE TABLE时创建索引
CREATETABLE user_index2 (
第⼆种⽅式：使⽤ALTER TABLE命令去增加索引
ALTERTABLE table_name ADDINDEX index_name (column_list);
ALTER TABLE⽤来创建普通索引、UNIQUE索引或PRIMARY KEY索引。
其中table_name是要增加索引的表名，column_list指出对哪些列进⾏索引，多列时各列之间⽤逗号分隔。
索引名index_name可⾃⼰命名，缺省时，MySQL将根据第⼀个索引列赋⼀个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。
第三种⽅式：使⽤CREATE INDEX命令创建
CREATEINDEX index_name ON table_name (column_list);
CREATE INDEX可对表增加普通索引或UNIQUE索引。（但是，不能创建PRIMARY KEY索引）
删除索引
根据索引名删除普通索引、唯⼀索引、全⽂索引：
ALTER TABLE 表名 DROP KEY 索引名 altertable user_index dropKEY NAME;
altertable user_index dropKEY id_card;
altertable user_index dropKEY information;
删除主键索引：alter table 表名 drop primary key（因为主键只有⼀个）。这⾥值得注意的是，如果主键⾃增长，那么不能直接执⾏此操作（⾃增长依赖于主键索引）：
需要取消⾃增长再⾏删除：
altertable user_index -- 重新定义字段MODIFY id int,dropPRIMARYKEY
但通常不会删除主键，因为设计主键⼀定与业务逻辑⽆关。
11、创建索引时需要注意什么？
（1）⾮空字段：
应该指定列为NOT NULL，除⾮你想存储NULL。在mysql中，含有空值的列很难进⾏查询优化，因为它们使得索引、索引的统计信息以及⽐较运算更加复杂。你应该⽤0、⼀个特殊的值或者⼀个空串代替空值；
（2）取值离散⼤的字段：
（变量各个取值之间的差异程度）的列放到联合索引的前⾯，可以通过count()函数查看字段的差异值，返回值越⼤说明字段的唯⼀值越多字段的离散程度⾼；mysql面试题索引
（3）索引字段越⼩越好：
数据库的数据存储以页为单位⼀页存储的数据越多⼀次IO操作获取的数据越⼤效率越⾼。
12、使⽤索引查询⼀定能提⾼查询的性能吗？为什么
通常，通过索引查询数据⽐全表扫描要快。但是我们也必须注意到它的代价。
（1）索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本⾝也会被修改。这意味着每条记录的INSERT，DELETE，UPDATE将为此多付出4，5 次的磁盘I/O。因为索引需要额外的存储空间和处理，那些不必要的索引反⽽会使查询反应时间变慢。使⽤索引查询不⼀定能提⾼查询性能，索引范围查询(INDEX RANGE SCAN)适⽤于两种情况:
（2）基于⼀个范围的检索，⼀般查询返回结果集⼩于表中记录数的30%
（3）基于⾮唯⼀性索引的检索
13、百万级别或以上的数据如何删除
关于索引：由于索引需要额外的维护成本，因为索引⽂件是单独存在的⽂件，所以当我们对数据的增加，修改，删除，都会产⽣额外的对索引⽂件的操作，这些操作需要消耗额外的IO，会降低增/改/删的执⾏效率。所以，在我们删除数据库百万级别数据的时候，查询MySQL官⽅⼿册得知删除数据的速度和创建的索引数量是成正⽐的。
（1）所以我们想要删除百万数据的时候可以先删除索引（此时⼤概耗时三分多钟）
（2）然后删除其中⽆⽤数据（此过程需要不到两分钟）
（3）删除完成后重新创建索引(此时数据较少了)创建索引也⾮常快，约⼗分钟左右。
（4）与之前的直接删除绝对是要快速很多，更别说万⼀删除中断,⼀切删除会回滚。那更是坑了。
14、前缀索引
语法：index(field(10))，使⽤字段值的前10个字符建⽴索引，默认是使⽤字段的全部内容建⽴索引。
前提：前缀的标识度⾼。⽐如密码就适合建⽴前缀索引，因为密码⼏乎各不相同。
实操的难度：在于前缀截取的长度。
我们可以利⽤select count(*)/count(distinct left(password,prefixLen));，通过从调整prefixLen的值（从1⾃增）查看不同前缀长度的⼀个平均匹配度，接近1时就可以了（表⽰⼀个密码的前prefixLen个字符⼏乎能确定唯⼀⼀条记录）
java反混淆工具
按钮特效代码css315、什么是最左前缀原则？什么是最左匹配原则
（1）顾名思义，就是最左优先，在创建多列索引时，要根据业务需求，where⼦句中使⽤最频繁的⼀列放在最左边。
（2）最左前缀匹配原则，⾮常重要的原则，mysql会⼀直向右匹配直到遇到范围查询(>、<、between、like)就停⽌匹配，⽐如a = 1 and b = 2 and c > 3 and d = 4 如果建⽴(a,b,c,d)顺序的索引，d是⽤不到索引的，如果建⽴(a,b,d,c)的索引则都可以⽤到，a,b,d的顺序可以任意调整。
（3）=和in可以乱序，⽐如a = 1 and b = 2 and c = 3 建⽴(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
16、B树和B+树的区别
（1）在B树中，你可以将键和值存放在内部节点和叶⼦节点；但在B+树中，内部节点都是键，没有值，叶⼦节点同时存放键和值。
（2）B+树的叶⼦节点有⼀条链相连，⽽B树的叶⼦节点各⾃独⽴。
17、使⽤B树的好处

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。