1. 为什么要使用索引使用索引是为了提高数据检索的效率。当数据量很大时如果没有索引数据库系统需要逐条扫描数据来找到符合条件的记录这样会消耗大量的时间和资源。而使用索引可以通过创建特定的数据结构将数据按照某种规则有序地组织起来从而加快数据的查找速度。索引可以在数据库表的一列或多列上创建它们包含了对应列值的引用和指针使得数据库系统可以快速定位到需要的数据。通过使用索引数据库系统可以根据索引的排序和搜索算法快速定位到符合查询条件的数据提高查询的效率。比如如图所示当基础数据量为60万时相同的SQL执行左边没加索引右边添加了索引效率差了10倍有余优点提高数据查询的效率降低数据库的IO成本。通过索引列对数据进行排序降低数据排序的成本降低CPU消耗。缺点索引会占用存储空间。索引大大提高了查询效率同时却也降低了insert、update、delete的效率。2. MySQL索引-结构MySQL数据库支持的索引结构有很多如Hash索引、BTree索引、Full-Text索引等。MySQL的索引是在存储引擎层实现的不同的存储引擎有不同的索引结构主要包含以下几种索引结构描述BTree索引最常见的索引类型大部分引擎都支持 B 树索引Hash索引底层数据结构是用哈希表实现的, 只有精确匹配索引列的查询才有效, 不支持范围查询R-tree(空间索引空间索引是MyISAM引擎的一个特殊索引类型主要用于地理空间数据类型通常使用较少Full-text(全文索引)是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES注意我们平常所说的索引如果没有特别指明都是指B树结构组织的索引。2.1 二叉树二叉查找树左边的子节点比父节点小右边的子节点比父节点大当我们向二叉查找树保存数据时是按照从大到小(或从小到大)的顺序保存的此时就会形成一个单向链表搜索性能会打折扣。可以选择平衡二叉树或者是红黑树来解决上述问题。红黑树也是一棵平衡的二叉树2.2 平衡二叉树但是在Mysql数据库中并没有使用二叉搜索数或二叉平衡数或红黑树来作为索引的结构。2.3 BTree(多路平衡搜索树)结构中也可以避免上述问题BTree结构每一个节点可以存储多个key有n个key就有n个指针节点分为叶子节点、非叶子节点叶子节点就是最后一层子节点所有的数据都存储在叶子节点上非叶子节点不是树结构最下面的节点用于索引数据存储的的是key指针为了提高范围查询效率叶子节点形成了一个双向链表便于数据的排序及区间范围查询为什么InnoDB存储引擎选择使用Btree索引结构?A. 相对于二叉树层级更少搜索效率高B. 对于B-tree无论是叶子节点还是非叶子节点都会保存数据这样导致一页中存储的键值减少指针跟着减少要同样保存大量数据只能增加树的高度导致性能降低C. 相对Hash索引Btree支持范围匹配及排序操作面试在MySQL中索引使用的数据结构是BTreeB树是基于B树的变种它具有B树的平衡性而且树的高度更低B树非叶子节点不存数据只存索引的标识和指针因此其内部节点相对B树更小树的高度更低查询产生的I/O更少B树查询效率更高B树使用双向链表串连所有叶子节点区间查询效率更高B树查询效率更稳定B树每次都必须查询到叶子节点才能找到数据而B树查询的数据可能不在叶子节点也可能在这样就会造成查询的效率的不稳定3. MySQL索引-语法作为查询的字段才可以加索引3.1 创建索引create [ unique ] index 索引名 on 表名 (字段名,... ) ;3.2 查看索引show index from 表名;3.3 删除索引drop index 索引名 on 表名;注意事项主键字段在建表时会自动创建主键索引添加唯一约束时数据库实际上会添加唯一索引4. MySQL索引-分类在MySQL数据库将索引的具体类型主要分为以下几类主键索引、唯一索引、常规索引、全文索引。分类含义特点关键字主键索引针对于表中主键创建的索引默认自动创建, 只能有一个PRIMARY唯一索引避免同一个表中某数据列中的值重复可以有多个UNIQUE常规索引快速定位特定数据可以有多个全文索引全文索引查找的是文本中的关键词而不是比较索引中的值可以有多个FULLTEXT而在在InnoDB存储引擎中根据索引的存储形式又可以分为以下两种分类含义特点聚集索引(Clustered Index)将数据存储与索引放到了一块索引结构的叶子节点保存了行数据必须有,而且只有一个二级索引(Secondary Index)将数据与索引分开存储索引结构的叶子节点关联的是对应的主键可以存在多个聚集索引选取规则:如果存在主键主键索引就是聚集索引。如果不存在主键将使用第一个唯一UNIQUE索引作为聚集索引。如果表没有主键或没有合适的唯一索引则InnoDB会自动生成一个rowid作为隐藏的聚集索引。聚集索引和二级索引的具体结构如下聚集索引的叶子节点下挂的是这一行的数据 。二级索引的叶子节点下挂的是该字段值对应的主键值比如当我们执行如下的SQL语句时具体的查找过程如下①. 由于是根据name字段进行查询所以先根据nameArm到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。②. 由于查询返回的数据是*所以此时还需要根据主键值10到聚集索引中查找10对应的记录最终找到10对应的行row。③. 最终拿到这一行的数据直接返回即可。回表查询 这种先到二级索引中查找数据找到主键值然后再到聚集索引中根据主键值获取数据的方式就称之为回表查询举例以下两条SQL语句那个执行效率高? 为什么?(备注: id为主键name字段创建的有索引)A. select * from user where id 10 ;B. select * from user where name Arm ;解答A 语句的执行性能要高于B 语句。因为A语句直接走聚集索引直接返回数据。 而B语句需要先查询name字段的二级索引然后再查询聚集索引也就是需要进行回表查询。5. 索引失效检查可以采用EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ; 示例 explain SELECT DATE_FORMAT(alarm_time, %H:00) AS date_time, ROUND(AVG(data_value),0) AS data_value FROM device_data WHERE iot_id esQFNsPbbmbcahxEuroCj0rk00 AND function_id HeartRate AND alarm_time BETWEEN 2024-01-20 00:00 and 2024-01-20 23:59 GROUP BY DATE_FORMAT(alarm_time, %H:00) order by alarm_time索引检查详解possible_key当前sql可能会使用到的索引key当前sql实际命中的索引如果为NULL则没有使用索引。type这条sql的连接的类型性能由好到差为NULL、system、const、eq_ref、ref、range、 index、allsystem查询系统中的表const根据主键查询eq_ref主键索引查询或唯一索引查询ref索引查询range范围查询index索引树扫描all全盘扫描key_len索引占用的大小表示索引中使用的字节数 该值为索引字段最大可能长度并非实际使用长度在不损失精确性的前提下 长度越短越好 。Extra额外的优化建议Extra含义Using where; Using Index查找使用了索引需要的数据都在索引列中能找到不需要回表查询数据Using index condition查找使用了索引但是需要回表查询数据idselect查询的序列号表示查询中执行select子句或者是操作表的顺序(id相同执行顺序从上到下id不同值越大越先执行)。select_type 表示 SELECT 的类型常见的取值有SIMPLE简单表即不使用表连接或者子查询PRIMARY主查询即外层的查询UNIONUNION 中的第二个或者后面的查询语句SUBQUERYSELECT/WHERE之后包含了子查询等rows MySQL认为必须要执行查询的行数在innodb引擎的表中是一个估计值可能并不总是准确的。filtered 表示返回结果的行数占需读取行数的百分比 filtered 的值越大越好。6. 索引失效的情况说明6.1 最左前缀法则最左前缀法则指的是复合索引联合索引使用时的规则查询条件必须从索引的最左列开始并且不能跳过中间的列否则索引会失效示例-- 创建测试表 CREATE TABLE user ( id INT PRIMARY KEY, name VARCHAR(50), age INT, city VARCHAR(50), phone VARCHAR(20) ); -- 创建复合索引name, age, city CREATE INDEX idx_name_age_city ON user(name, age, city);a. 完全匹配使用全部3列-- 使用索引3列都用上了 EXPLAIN SELECT * FROM user WHERE name 张三 AND age 25 AND city 北京; -- 结果keyidx_name_age_citytyperefb. 使用最左2列-- 使用索引用到 name 和 age EXPLAIN SELECT * FROM user WHERE name 张三 AND age 25;c. 只使用最左1列-- 使用索引只用到 name EXPLAIN SELECT * FROM user WHERE name 张三;d. 跳过最左列完全失效-- 不使用索引缺少 name EXPLAIN SELECT * FROM user WHERE age 25 AND city 北京; -- 结果 keynull全表扫描e. 跳过中间列部分失效-- 只使用 nameage 被跳过导致 city 失效 EXPLAIN SELECT * FROM user WHERE name 张三 AND city 北京; -- name 可以使用索引 -- 跳过了 age导致 city 无法使用索引 -- 实际只用到索引的第1列f. 顺序打乱但包含全部列-- 优化器会自动调整顺序仍使用索引 EXPLAIN SELECT * FROM user WHERE age 25 AND name 张三 AND city 北京;g. 范围查询的影响-- 范围查询后的列会失效 EXPLAIN SELECT * FROM user WHERE name 张三 AND age 25 AND city 北京; -- name 使用索引 -- age 25 使用索引范围查找 -- city 失效age用了范围查询6.2 索引列进行运算CREATE INDEX idx_phone ON user(phone); -- 失效 SELECT * FROM user WHERE phone 1 13800000000; -- 正确写法 SELECT * FROM user WHERE phone 13800000000 - 1;6.3 字符串不加引号CREATE INDEX idx_name ON user(name); -- 失效隐式类型转换 SELECT * FROM user WHERE name 123; -- 正确 SELECT * FROM user WHERE name 123;6.4 模糊查询CREATE INDEX idx_name ON user(name); -- 前缀模糊可用索引 SELECT * FROM user WHERE name LIKE 张%; -- 中缀/后缀模糊失效 SELECT * FROM user WHERE name LIKE %三%; SELECT * FROM user WHERE name LIKE %三;6.5 or 连接条件CREATE INDEX idx_name ON user(name); CREATE INDEX idx_age ON user(age); -- 两侧都有索引 SELECT * FROM user WHERE name 张三 OR age 25; -- 一侧无索引全表扫描 -- 假设 phone 无索引 SELECT * FROM user WHERE name 张三 OR phone 123456;6.6 数据分布影响-- 如果表中 90% 的人 age25MySQL 可能放弃索引 SELECT * FROM user WHERE age 25;说明优化器判断全表扫描更快时会不走索引如查询结果占比过高。7. 覆盖索引覆盖索引是指查询语句使用到了索引并且查询的所有内容都在索引中不需要进行回表查询