MySQL索引常见问题

hwq:)

已于 2024-02-03 13:35:53 修改

阅读量137

点赞数 1

文章标签： mysql 数据库

于 2023-08-26 15:04:53 首次发布

本文链接：https://blog.csdn.net/weixin_44465821/article/details/132494369

版权

索引的优缺点是什么？

没有索引的话操作系统会把所有的数据加载到内存依次进行检索，加载的数据很多IO次数也会很多，有了索引只会加载某个列或者主键id，IO的次数就会比较少，速度就会比较快，提升了数据库的效率，缺点就是索引需要占用空间以空间换时间来提升效率的，创建和维护索引要耗费时间这个时间随着数据量的增加而增加。

聚簇索引和非聚簇索引

聚簇索引：

将数据存储在索引树的叶子节点上，查询索引树的时候可以直接获取到数据减少了回表的一次查询
聚簇索引的缺点是在修改或者删除操作的时候需要更新索引树，会增加系统的开销
主要用于提高查询的效率

非聚簇索引（辅助索引）：

数据不会存放在索引树的叶子节点上，而是存储在数据页中
通常查询数据需要两次查询，一次查找索引树获取数据页的地址，第二次通过页地址查询数据（索引覆盖不需要回表）
在修改或者删除数据操作的时候不需要修改索引树，减少了系统的开销
主要用于提高更新和删除操作的效率

hash索引

通过hash算法计算索引列中的全部内容作为key，将这条数据的行地址作为value一并存到hash表中的对应位置
在MySQL中只有Memeory支持哈希索引
不支持部分索引列的匹配查找，比如给数据列（A，B）建立索引，因为它是根据索引列的全部内容来计算哈希值的，所以如果查询只有数据列A，则无法使用该索引
不支持范围查找，不可能把范围里面的每一个关键字都拉出来算他的索引值去查找
精准查询效率很高接近于O（1），但是有哈希冲突问题，如果出现哈希冲突问题存储引擎必须遍历链表中的所有数据，逐个比较代价很大

什么是唯一索引

确保写入数据库的数据具有唯一性
如何只在业务中做校验，可能在数据库中会出现脏数据
可以为空

联合索引

使用函数、会出现索引失效
联合索引最左匹配原则：（A，B）联合索引，查询中只要包含A,B不管顺序如何都会走索引，如果还有别的未建立索引的字段会进行回表查找，性能会降低，但如果没有A只有B则不会走索引，因为索引树是先给A排序之后再给B排序，直接查找B是乱序
模糊匹配如果是%sql%这种形式会出现索引失效，因为一开始都不确定是什么的话索引就不会生效，sql%这种模糊查询是会走索引的

使用索引一定能提升效率吗？

不一定

对于查询使用少的字段不建议建索引，占用空间增删改效率降低

InnoDB和MyISAM的索引有什么区别

都是B+树实现的

InnoDB使用的是聚簇索引，MyISAM使用的是非聚簇索引
InnoDB支持事务，外键，MyISAM不支持
InnoDB支持行级锁，MyISAM不支持行级锁只支持表级锁
InnoDB支持数据库异常崩溃后的安全恢复

什么是索引下推

针对MySQL5.6扫描二级索引的一项优化，本来一些过滤条件是在MySQL服务层去做的，现在在匹配索引的时候就加上这些过滤的条件，将索引过滤的条件下推到引擎层来降低回表的次数

哪些情况会导致索引失效

全值匹配，对索引中所有列都指定具体值。该情况下，索引生效，执行效率高，但需要回表。

explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';

2. 最左前缀法则
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。

匹配最左前缀法则，走索引：
在这里插入图片描述
违法最左前缀法则，索引失效：

如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：

范围查询右边的列，不能使用索引。

根据前面的两个字段name ， status 查询是走索引的，但是最后一个条件address 没有用到索引。
不要在索引列上进行运算操作，索引将失效。
字符串不加单引号，造成索引失效。

由于，在查询是，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效。

6). 尽量使用覆盖索引，避免select *

尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select * 。
在这里插入图片描述
如果查询列，超出索引列，也会降低性能。

TIP :

using index ：使用覆盖索引的时候就会出现

using where：在查找使用索引的情况下，需要回表去查询所需的数据

using index condition：查找使用了索引，但是需要回表查询数据

using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
示例，name字段是索引列，而createtime不是索引列，中间是or进行连接是不走索引的。
以%开头的Like模糊查询，索引失效。
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

解决方案：
通过覆盖索引来解决
如果MySQL评估使用索引比全表更慢，则不使用索引。
is NULL ， is NOT NULL 有时索引失效。
in 走索引， not in 索引失效。
单列索引和复合索引。

尽量使用复合索引，而少使用单列索引。

创建复合索引

create index idx_name_sta_address on tb_seller(name, status, address);

就相当于创建了三个索引：
name
name + status
name + status + address
创建单列索引

create index idx_seller_name on tb_seller(name);
create index idx_seller_status on tb_seller(status);
create index idx_seller_address on tb_seller(address);

数据库会选择一个最优的索引（辨识度最高索引）来使用，并不会使用全部索引。
13. 使用order by可能导致索引失效

使用order By能否通过索引排序？

不一定，有三种情况

第一种情况：建立了一个索引，根据这个索引字段order by进行排序，但是where后面没有这个条件，这时是不会走索引的，因为sql执行的顺序是先根据where后面的查询条件得到数据，之后再根据order by去排序，会看根据where后面的查询条件查找数据的时候有没有用到索引，如果用到了索引那么order by直接获取索引的顺序就不用自己去排序了，如果没有用到就不会走索引
第二种情况：where后面有order by排序的这个字段，那么会走索引，order by直接获取索引的顺序就不用自己去排序了
第三种情况：查询的字段也建了索引值，他直接会走二级索引并且不用回表，因为查询的字段在索引树里面都存在，就不用再根据主键值再去回表查别的字段了

B树和B+树的区别

B树的每个节点上都会存数据，B+树只有叶子节点会存数据
B+树叶子节点之间有指针连接，B树没有
在查询的时候，B树相当于对范围内的每个关键词做二分查找，可能还没有查询到叶子节点，查询就结束了，查询效率比较低并且不稳定，而B+树做查询的时候是先遍历到索引树的叶子节点，通过叶子节点之间的指针做顺序查找，所以效率比较高也更稳定

数据库表设计时，字段应该如何选择

字段类型优先级
char：定长
varchar:不定长
char>varchar
可以选择整型就不选字符串，整型排序快一些
够用就行不要慷慨：大的内存影响速度
尽量避免使用null，null不利于索引也不利于查询
char和varchar:char长度固定，处理速度要比varchar快很多，但是比较费内存空间，在速度上有要求可以使用char类型

MySQL中varchar(M)最多能存储多少数据？

65535个字节，除了blob和text类型的列之外，其他所有的列占用的字节长度加起来不超过65535

事务的四个特性

原子性：一个事务中的操作要么全部成功，要么全部失败
一致性：数据前后的总额是相等的
隔离性：一个事务在修改未提交之前其他事务锁不可见的
持久性：一个事务一旦提交，所做的修改会永久保存到数据库中

事务隔离级别

读未提交：最低的隔离级别，允许读取未提交的数据，有脏读、幻读、不可重复读的问题。
读已提交：允许读取已经提交的数据，可以阻止脏读，有幻读、不可重复读的问题。
可重复读：对同一字段多次读取的结果是一致的，可以阻止脏读，不可重复读的问题，但是幻读仍有可能发生。
可串行化：最高的隔离级别，所有事务逐个进行，事务之间完全不会产生干扰，可以防止脏读、幻读、不可重复读的问题。

不可重复读和幻读有什么区别？

不可重复读关注的是在两次相同的查询之间，数据是否发生了变化；而幻读关注的是在两次相同的查询之间，是否有新的数据被插入。

脏读的解决

InnoDB会给每行数据增加一个事务id，当一个事务开始的时候会获取一个唯一的事务id，当一个事务读取一行数据的时候，innoDB会检查这个数据的事务id是否小于当前事务的id，如果小于说明这是已经提交的数据，如果大于说明这个数据还没被提交，innoDB将阻止该事务读取改行数据。

不可重复读的解决

在可重复读隔离级别下，通过MVCC来解决，当使用快照读进行读取的时候只有第一次读取的时候会生成一个Read View，对比Read View中的事务id和undolog中快照的事务id，选择快照，后续所有的快照读都是用同一个快照，所以就不会发生不可重复读的问题了。

Read View

还未提交的事务id数组

幻读的解决

临键锁（记录锁+间隙锁）：左开右闭，锁住一段范围
间隙锁：开区间，锁住一段范围
在读已提交隔离级别下，通过临键锁来解决。

快照读和当前读

快照读：就是读取的是当前快照生成的那一刻的数据，select语句就是快照读
当前读：读的是最新的数据，给select加锁还有一些增删改会进行当前读

这些快照数据放到undo log中，一条记录的多个快照会用指针给他连起来形成一个快照链表

事务并发可能造成什么问题？

脏读：读取到另外一个事务未提交的数据的现象
不可重复读：前后两次读取的数据出现了不一致
幻读：读取同一个范围的数据，在事务B两次读取的过程中事务A插入了数据，导致事务B后一次读取到了第一次没有查询到的行

MySQL各种索引

单列索引：

主键索引：建立在主键上的索引，一张表只能有一个主键索引不能为空
唯一索引：一张表可以有多个唯一索引，运行为null，列表中出现多个空值不会发生重复冲突在主键上的索引
普通索引：在普通字段上建立的索引

组合索引：相当于创建了多个索引，一般把最常用的放在最左边
create index index3 on demo(col1,col2,col3)
相当于创建了col1，col1+col2，col1+col3，col1+col2+col3

全文索引（fulltext）：只能在char，varchar，text等字段才能使用全文索引，全文索引是抽取到一列内容的关键字，然后通过关键字建立索引。相当于含like的模糊查询。

InnoDB一颗B+树key存放约2000万行数据

如何提高insert的性能

合并多条insert为一条：日志会减少很多

全局锁、共享锁、排他锁

全局锁：对整个数据库加锁，使整个库处于可读的状态，之后更新语句会被阻塞
共享锁（读锁）：读取操作创建的锁，只能读取数据但是不能修改，直到已释放所有的共享锁，
排他锁（写锁）：在事务的某一行加上了排他锁，只有这个事务可以对其进行读写，其他事务都不能进行读和写的操作，知道这个锁被释放，它是悲观锁的一种实现。

谈一下MySQL中的死锁

死锁是两个或者两个以上的进程在执行过程中，手中拥有其他进程需要的资源，并且持续请求自己需要的资源也不放手自己手中的资源，形成一种相互等待的局面，这就是死锁

如何查看死锁：通过命令show engine innodb status
对待死锁的两种策略：

设置超时时间，超时之后对应的资源就会被释放。
发起死锁检测，发现死锁之后主动回滚死锁中的某一个事务让其他事务继续执行。

MySQL的锁类型有哪些

基于锁的级别分类

共享锁：又叫读锁，读数据时不允许其他事务对其进行修改但是可以查询，sql语句是lock in share mode
排他锁：又叫独占锁，一个事务加了写锁，其他的事务既不能读也不能写，也不能加锁，sql语句是for update。他是基于悲观锁的一种实现，增删改会自动增加排它锁

锁的粒度

表级锁：上锁的时候锁的是整个表，其他的事务不能访问这个表，必须要等锁释放了之后才能访问，粒度大，容易冲突
行级锁：对一行或者多行上锁，其他事务不能访问这些行，粒度小，不容易冲突
记录锁：只会锁一行记录

锁的状态
意向锁：一个事务在表里加了一个排他锁，那就不允许其他事务再加排他锁和共享锁了，后面的事务需要获取一个状态知道自己能不能对表加锁，避免了对索引树的每个结点进行扫描是否加锁，这个状态就是意向锁。

MyISAM表锁

在这里插入图片描述
MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁。

一个事务给一个表上了读锁，其他事务可以读但是不能写，该事务也只能读这张表的数据不能对其他表做操作，直到commit提交后释放锁。
一个事务给一个表上了写锁，其他事务不能对该表进行读和写操作，直到commit提交后释放锁。
此外，MyISAM 的读写锁调度是写优先，这也是MyISAM不适合做写为主的表的存储引擎的原因。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。MyISAM适合读多写少。

InnoDB行锁

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；
对于普通SELECT语句，InnoDB不会加任何锁；
在这里插入图片描述

如果不通过索引条件检索数据（无索引或索引失效），那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样。
当我们用范围条件，而不是使用相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据进行加锁（间隙锁）；对于键值在条件范围内但并不存在的记录，叫做 “间隙（GAP）”

如何进行sql优化

基本写法优化

少用select *
条件的类型要写对，像字符串不加单引号索引就会失效
少用order by排序，因为order by它可能走索引也可能不走索引，一般分为三种情况，第一种情况锁给order by修饰的字段建立索引之后，where后面没有查询这个字段那就会索引失效，会进行全表查询，第二种情况锁where后面有这个字段的查询条件，因为索引底层B+树它本身就是有序的，所以根据where条件查询后的数据排序的时候会直接根据B+树的顺序就不用order by自己再去排序了，第三种情况是查询的字段就是你建立索引的那个字段，那就是覆盖索引，可以直接查二级索引树不用回表，这种情况索引也是生效的，所以如果必须要用到order by排序可以在where条件里面加上这个字段的条件，这样就可以走索引了
查询时候少用null，因为null不利于索引也不利于查询
少用like，如果要用尽量用like%，不然会造成索引失效
少用函数，函数也会造成索引失效