发布网友
发布时间：2022-04-19 22:19
共3个回答
懂视网
时间：2022-04-29 20:11
hash索引的话，不支持范围查询，因为hash就是一个键对应一个值的，没办法范围查询
二叉树的话，它的特点就是左子树小于根节点小于右子树，如果根节点取值有问题的话，有可能会退化成链表，就是树不分叉了，树一直往左或者一直往右，这样就不能折半查找从而减少IO次数了，不支持范围查询，要是范围查询的话，每次都要从根部遍历，树也太高了，树越高，IO操作越频繁，浪费资源
平衡二叉树的话，它就没有了二叉树的这种退化成链表的缺点，因为他左右子节点最多相差1层，可是他也不支持范围查找这一点和二叉树的问题一样
b树的话，和二叉树比起来树是很矮胖，IO操作减少了，是个多叉树，它每个节点都存了对应的行数据，可是如果这一行的数据的列不断的增加，那么这一页存储的节点就会变少，因为所占的空间不断的变大，树也会越来越高，增加IO操作次数，同时是也不支持范围查找。要是相同大小的空间可以存很多的节点数据的话就更好了，所以就有了下面的b+树
b+树 它非叶子节点只存索引的数据，不存整行数据，但是叶子节点是冗余的，冗余了非叶子节点，叶子节点还都用双向链表链接起来，这样有助于顺序查找，b+树和b树比起来，更加矮胖，磁盘IO次数更少
我们可以简单的理解为 聚簇索引就是主键索引，非聚簇索引就是普通索引
本质的区别是
聚簇索引的叶子节点存储的是整行数据
innodb是通过主键来实现聚簇索引的，如果没有主键的话，那么他就会选择一个唯一非空的索引来实现，如果再没有的话，他就会隐式生成一个主键来实现聚簇索引
非聚簇索引存储的是索引值和主键值
普通索引一张表中可以有多个普通索引，随便一个字段都可以建立的索引，我们平常建立的索引大部分都是普通索引
联合索引好几个字段联合起来建立的索引
唯一索引业务中唯一的字段适合建立唯一索引，一个表中可以有多个唯一索引
主键索引和唯一索引一样，主键索引也是唯一的，不同的就是，一个表只能有一个主键索引
创建主键索引
ALTER TABLE test add PRIMARY KEY (id)复制代码
创建唯一索引
ALTER TABLE test add UNIQUE idx_id_card(id_card)复制代码
创建普通索引
ALTER TABLE test add INDEX idx_name(name)复制代码
创建联合索引
ALTER TABLE test add INDEX idx_age_name(age,name)复制代码
修改索引名称 ：先删除再添加
删除索引 （两种方式）
ALTER TABLE test DROP INDEX idx_id_cardDROP INDEX idx_id_card on test --删除主键索引DROP PRIMARY key on test ALTER TABLE test DROP PRIMARY key复制代码
查看表中索引
SHOW INDEX FROM test复制代码
分析索引
EXPLAIN SELECT * from test WHERE name = "xhJaver"复制代码
我们先给name字段添加一个索引,索引名字叫做idx_name
ALTER TABLE test add INDEX idx_name(name)复制代码
查看test表中的索引
SHOW INDEX FROM test复制代码
其中的属性
table: 表名
Non_unique: 能重复的话为1，不能重复的话为0，我们主键的那里是0，而name那里是1，因为name可以重复，而主键不能重复
Key_name: 索引名称
Seq_in_index:索引中列的顺序
Column_name:列名称
Collation:列以什么方式存储的，A升序，null无序
Cardinality:数目越大，则使用该索引的可能性越大
Sub_part:如果列只是部分的编入索引，则被编入索引的字符数目，如果整列被编入索引，则为null
Packed：关键字是否被压缩，null表示没有被压缩
Null:如果该列含有null,则为yes,如果没有null,则为no
Index_type:索引数据结构
Comment:多种评注
select * from test where name = "xhJaver"复制代码
假如说我们name字段建立了索引，然后当我们运行这一句sql语句的时候，因为建立的是普通索引，所以我们的b+树的叶子节点存储的数据是id，我们会找到name是xhJaver的这条记录的id，再根据这个id,去主键索引的那棵b+树去查询，查询到叶子节点时即查询出这条记录，可见这个过程中，我们从一棵树跑到了另一棵树继续查，这样就叫做“回表查询”，那有没有办法只查一棵树就可以查询出结果呢？
办法当然是有的啦，那就是覆盖索引，我们注意到，刚才这个sql语句时查询出来了所有元素，假如说我们这样写的话
select address from test where name = "xhJaver"复制代码
假如说我们建立的索引是（name,address）那么这个时候（name,address）这棵b+树的叶子节点存储的数据就包括address了，此时就不需要再根据name = "xhJaver"的id去第二棵树查了，这样就避免了回表查询
假如说现在我们写一个这样的sql语句
select * from test where name = "xhJaver" and age =23 and address="京东"复制代码
并且我们建立的索引是（name,address,age）这样是会用到（name,address,age）索引的，可是如果要这样写的话
select * from test where name = "xhJaver" and age >23 and address="京东"复制代码
这样只会用到（name，age）这两个索引，从左边开始匹配，如果要是遇到范围查询的话，则不继续往右匹配索引
我们用explain语句解析一下下面这条sql语句
EXPLAIN SELECT * from test WHERE name = "xhJaver"复制代码
它的属性有
id: 执行的顺序
select_type: 查询的类型
table: 关于哪张表的
partitions: 分区相关（还没搞懂呜呜呜）
type:访问类型
性能由好至坏依次是 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL一般来说，好的sql查询至少达到range级别，最好能达到ref
system:表中只有一行数据
const:常量查询 通常用于比较主键等于一个常量，用索引查询一次就查到了
eq_ref:唯一性索引，每个索引对应一条数据，例如主键索引
ref:非唯一索引，每个索引有可能对应多行数据，例如普通索引
range: 范围查询，用到了>，<，in,between等查询
index：全表扫描，但是是遍历整棵索引树
all:全表扫描，没有用到索引
possible_keys：查询的字段上有索引的话，就会显示出来，
key : 具体用到的索引，若用到了覆盖索引，则possible_keys为null,只会显示在key中
key_len:索引中使用的字节数，最大可能长度，并非实际长度，key_len是根据表定义计算而得的，不是通过表内检索出的
ref: 表示使用索引的是哪一个字段
rows:大致估算出所需要读取的行数
filtered:显示了通过条件过滤出的行数的百分比估计值。
Extra:
Using filesort ： mysql无法利用索引完成的排序被称为文件排序
Using temporary： 使用临时表存储了下中间结果，mysql对查询结果排序时是使用了临时表，常见于order by 和 group by
Using index：使用了覆盖索引，查询内容在索引内
只有Using where 查询内容不在索引内，且对查出来的数据进行了过滤
1. EXPLAIN SELECT (select student.id from student WHERE student.`name`="xhJaver") FROM teacher2. EXPLAIN SELECT * FROM teacher where teacher.id = (select student.id from student WHERE student.`name`="xhJaver") 复制代码
我们写几个sql语句实际分析下
1.SELECT后面
2.where后面
我们就拿后面这个图来实战分析一下,挑几个重要的属性说一下
select_type：
我们最外层的查询是 from teacher 所以table为teacher的那个表的select_type就是primary
select/where后面的括号中的查询语句中的表是student，所以table为student的那个表的select_type就是subquery
table: 这条sql查询用到的表
type: 访问类型
第一行const : teacher.id =巴拉巴拉巴拉（这个是常数）主键和常数比较时，这个表最多有一个匹配数据，只读取一次
第二行ref：代表用到了普通索引，就是这个索引name和xhJaver匹配,可能匹配到很多相同的值
possible_key: 代表可能用到的索引，但是不一定会用到
key: 代表用到的索引， 用到了idx_name，PRIMARY索引
ref: 这一列显示了在key列记录的索引中，表查找值所用到的列或常量， 常见的有：const，字段名
extra：
select student.id from student WHERE student.`name`="xhJaver"复制代码
name字段有索引，查询的是id，b+树叶子节点存的数据就是id，所以不需要回表查询了，用到了覆盖索引
遇到范围查询（>,<,like,beetwon），右边的索引列会失效
索引字段不能有函数操作或者不能是表达式的一部分
索引字段隐式类型转换 索引字段类型是string，我们传进来个int
使用时or,is null ,is not null , !=, <>, like "%xxx" 索引会失效
但是用覆盖索引就可以解决 like左模糊查询走不到索引的情况 如果只select索引字段，或者select索引字段和主键，也会走索引的。
更多相关免费学习推荐：mysql教程(视频)
热心网友
时间：2022-04-29 17:19
在满足语句需求的情况下，尽量少的访问资源是数据库设计的重要原则，这和执行的 SQL 有直接的关系，索引问题又是 SQL 问题中出现频率最高的，常见的索引问题包括：无索引（失效）、隐式转换。1. SQL 执行流程看一个问题，在下面这个表 T 中，如果我要执行 需要执行几次树的搜索操作，会扫描多少行？
这分别是 ID 字段索引树、k 字段索引树。
这条 SQL 语句的执行流程：
1. 在 k 索引树上找到 k=3，获得 ID=3002. 回表到 ID 索引树查找 ID=300 的记录，对应 R33. 在 k 索引树找到下一个值 k=5，ID=5004. 再回到 ID 索引树找到对应 ID=500 的 R4
5. 在 k 索引树去下一个值 k=6，不符合条件，循环结束
这个过程读取了 k 索引树的三条记录，回表了两次。因为查询结果所需要的数据只在主键索引上有，所以必须得回表。所以，我们该如何通过优化索引，来避免回表呢？2. 常见索引优化2.1 覆盖索引覆盖索引，换言之就是索引要覆盖我们的查询请求，无需回表。
如果执行的语句是 ，这样的话因为 ID 的值在 k 索引树上，就不需要回表了。
覆盖索引可以减少树的搜索次数，显著提升查询性能，是常用的性能优化手段。
但是，维护索引是有代价的，所以在建立冗余索引来支持覆盖索引时要权衡利弊。
2.2 最左前缀原则
B+ 树的数据项是复合的数据结构，比如 的时候，B+ 树是按照从左到右的顺序来建立搜索树的，当 这样的数据来检索的时候，B+ 树会优先比较 name 来确定下一步的检索方向，如果 name 相同再依次比较 sex 和 age，最后得到检索的数据。
可以清楚的看到，A1 使用 tl 索引，A2 进行了全表扫描，虽然 A2 的两个条件都在 tl 索引中出现，但是没有使用到 name 列，不符合最左前缀原则，无法使用索引。所以在建立联合索引的时候，如何安排索引内的字段排序是关键。评估标准是索引的复用能力，因为支持最左前缀，所以当建立（a，b）这个联合索引之后，就不需要给 a 单独建立索引。原则上，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。上面这个例子中，如果查询条件里只有 b，就是没法利用（a，b）这个联合索引的，这时候就不得不维护另一个索引，也就是说要同时维护（a，b）、（b）两个索引。这样的话，就需要考虑空间占用了，比如，name 和 age 的联合索引，name 字段比 age 字段占用空间大，所以创建（name，age）联合索引和（age）索引占用空间是要小于（age，name）、(name）索引的。
2.3 索引下推
以人员表的联合索引（name, age）为例。如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是26岁的所有男性”。那么，SQL 语句是这么写的
通过最左前缀索引规则，会找到 ID1，然后需要判断其他条件是否满足在 MySQL 5.6 之前，只能从 ID1 开始一个个回表。到主键索引上找出数据行，再对比字段值。而 MySQL 5.6 引入的索引下推优化(index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。这样，减少了回表次数和之后再次过滤的工作量，明显提高检索速度。
2.4 隐式类型转化
隐式类型转化主要原因是，表结构中指定的数据类型与传入的数据类型不同，导致索引无法使用。所以有两种方案：
修改表结构，修改字段数据类型。
修改应用，将应用中传入的字符类型改为与表结构相同类型。
3. 为什么会选错索引3.1 优化器选择索引是优化器的工作，其目的是找到一个最优的执行方案，用最小的代价去执行语句。在数据库中，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。
3.2 扫描行数
MySQL 在真正开始执行语句之前，并不能精确的知道满足这个条件的记录有多少条，只能通过索引的区分度来判断。显然，一个索引上不同的值越多，索引的区分度就越好，而一个索引上不同值的个数我们称为“基数”，也就是说，这个基数越大，索引的区分度越好。
MySQL 使用采样统计方法来估算基数：采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。
在 MySQL 中，有两种存储索引统计的方式，可以通过设置参数 innodb_stats_persistent 的值来选择：
on 表示统计信息会持久化存储。默认 N = 20，M = 10。
off 表示统计信息只存储在内存中。默认 N = 8，M = 16。
由于是采样统计，所以不管 N 是 20 还是 8，这个基数都很容易不准确。所以，冤有头债有主，MySQL 选错索引，还得归咎到没能准确地判断出扫描行数。
可以用 来重新统计索引信息，进行修正。
3.3 索引选择异常和处理1. 采用 force index 强行选择一个索引。2. 可以考虑修改语句，引导 MySQL 使用我们期望的索引。3. 有些场景下，可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。
热心网友
时间：2022-04-29 18:37