2024-11-03

MySQL存储引擎与索引

MySQL存储引擎

MyISAM:

.frm是存储表结构

.myd存储表数据

.myi存储索引文件

InnoDB:

.frm是存储表结构

.ibd存放索引文件和数据（需开启innodb_file_per_table参数）

innodb_file_per_table这项设置告知InnoDB是否需要将所有表的数据和索引存放在共享表空间里(innodb_file_per_table = OFF) 或者为每张表的数据单独放在一个.ibd文件(innodb_file_per_table = ON)。每张表一个文件允许你在drop、truncate或者rebuild表时回收磁盘空间。

配置优化

索引

优缺点

优点：

1.通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
2.可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
3.可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
4.在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
5.通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能

缺点：

为什么索引会增加表查询速度，降低表更新速度，因为MySQL在更新表数据时，不仅需要保存数据，还需要保存一下索引文件。

1.创建索引和维护索引需要耗费时间，这种时间随数据量的增加而增加。
2.除数据表占数据空间外，每一个索引还要占一定的物理空间，若要建立聚簇索引，那么需要的空间就会更大。
3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

最左前缀匹配原则：对于多列索引，总是从索引的最前面字段开始，接着往后，中间不能跳过。

比如列索引(a,b,c)创建的同时，已经对(a)、(a,b)、(a,b,c)上建立了索引，中间不能跳过，比如(a,c)就不行。

但是内部顺序不要求一定按照顺序来，因为MySQL会自动优化，比如说(a,b)写成(b,a)也没问题。

索引语句

创建索引：

ALTER TABLE

1
2
3

ALTER TABLE 表名 ADD INDEX 索引名 (列名)
ALTER TABLE 表名 ADD UNIQUE 索引名(列名)
ALTER TABLE 表名 ADD PRIMARY KEY 索引名(列名)

索引名可不写

CREATE INDEX

1 2	CREATE INDEX 索引名 ON 表名 (列名) CREATE UNIQUE INDEX 索引名 ON 表名 (列名)

索引名必须有,不能用CREATE INDEX语句创建PRIMARY KEY索引。

删除索引

1 2	DROP INDEX 索引名 ON 表名 ALTER TABLE 表名 DROP INDEX 索引名

查看索引

1 2	show index from 表名; show keys from 表名;

问答

1.什么情况下该使用索引？

在经常需要搜索的列上，可以加快搜索的速度；
在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

2.什么情况下不该不使用索引？
对于那些在查询中很少使用或者参考的列不应该创建索引。因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
对于那些只有很少数据值的列也不应该增加索引。例如性别，只有两种可能数据。意味着索引的二叉树级别少，这样的二叉树查询无异于全表扫描。

频繁更新的字段不要使用索引

3.什么情况下索引会失效？

1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)，要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引。

select id from t where num=10 or num=20;
#可以这样查询：
select id from t where num=10;
union all
select id from t where num=20;

2.like查询是以%开头

3.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

4.如果mysql估计使用全表扫描要比使用索引快,则不使用索引

5.对索引列进行运算导致索引失效,我所指的对索引列进行运算包括(+，-，*，/，! 等)

1 2	错误的例子：selectfrom test where id-1=9; 正确的例子：select fromtest where id=10;

6.使用 <> 、not in、not exist、!=

1	select id from t where num in(1,2,3);

7.索引字段进行判空查询时。也就是对索引字段判断是否为NULL时。语句为is null 或is not null。

1
2
3

select id from twhere num is null;
#可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：  
select id from t where num=0;

4.索引方法有哪些？

哈希表和B-tree表

1	MySQL对于每个表最多可以创建64个索引，包括主键索引和二级索引。这个限制是非常有意义的，因为过多的索引会带来严重的性能问题。