文章目录
-
-
- 一、数据库引擎
-
- 1. 查看数据库引擎
- 2. 查看表结构
- 3. 查看表相关文件
- 4. 各存储引擎的区别
- 二、MySQL索引
-
- 1. 索引分类
- 2. 索引的创建和删除
- 3. 关于缓存问题
- 4. 过滤条件字段涉及类型转换则无法使用索引
- 5. 删除索引
- 6. explain字段含义
- 7. 加索引优化原则
-
一、数据库引擎
1. 查看数据库引擎
show engines
文章图片
2. 查看表结构
show create table [student]
:查看表结构,其中表使用的数据库引擎和字符集等可以在配置文件中修改。windows下的配置文件为安装目录下的my.ini
,linux则在/etc/mysql/my.cnf
文章图片
3. 查看表相关文件
- 使用MyISAM存储引擎的表对应的文件有三个:
*.frm
,*.MYD
,*.MYI
,分别表示表结构、表数据、表索引 - 使用InnoDB存储引擎的表对应的文件有两个:
*.frm
,*.ibd
,分别表示表结构、表数据和表索引,数据和索引放在一个文件中
文章图片
面试问题:为什么使用InnoDB存储引擎的表会自动生成主键,而使用MyISAM存储引擎的表不会自动生成主键?
因为MyISAM的数据和索引是单独存放的,手动加上主键会生成主键索引存放在
*.MYI
,没有主键的话*.MYI
里就不用存放索引。而InnoDB会默认生成一个整型类型的索引,因为Innodb的数据和索引放在一个文件中,数据就是放在索引树上的,没有索引,数据也没有地方存放。4. 各存储引擎的区别
种类 | 锁机制 | B树索引 | 哈希索引 | 外键 | 事务 | 索引缓存 | 数据缓存 |
---|---|---|---|---|---|---|---|
MyISAM | 表锁 | 支持 | 不支持 | 不支持 | 不支持 | 支持 | 不支持 |
InnoDB | 行锁 | 支持 | 不支持 | 支持 | 支持 | 支持 | 支持 |
Memory | 表锁 | 支持 | 支持 | 不支持 | 不支持 | 支持 | 支持 |
- 锁机制:表示数据库在并发请求访问的时候,多个事务在操作时,并发操作的力度
- B树索引和哈希索引:主要是加速SQL的查询速度
- 外键:子表的字段依赖父表的主键,设置两张表的依赖关系
- 事务:多个SQL语句,保证它们共同执行的原子操作,要么成功要么失败,不能只成功一部分,失败需要回滚事务
- 索引缓存和数据缓存:和MySQL Server的查询缓存相关,在没有对数据和索引做修改之前,重复查询可以不用进行磁盘I/O(数据库的性能提升,目的是减少磁盘I/O提升访问效率),读取上一次内存中查询的缓存就可以了
当表中的数据量达到上百万的时候,SQL查询花费的时间会很长,需要使用索引加速SQL查询
由于索引也是需要存储成索引文件的,因此使用索引也会涉及磁盘I/O操作。如果索引过多,使用不当,SQL查询时会造成大量无用的磁盘I/O操作,降低查询效率。
此外,我们改动数据以后,不仅是数据文件需要做修改,索引文件也需要所修改,索引过多,修改的索引也会更多,所以索引并不是越多越好。
1. 索引分类 索引是创建在表上的,是对数据库表中一列或多列的值进行排序的一种结果,其核心就是提高查询的速度
- 物理上分为:聚集索引、非聚集索引
- 逻辑上分为:
- 普通索引:没有任何限制条件,可以给任何字段创建普通索引(一张表的一次SQL查询只能使用一个索引,比如
where age=1 and sex="man"
只能使用一个索引) - 唯一性索引:使用
unique
修饰的字段,值不能重复,主键索引就是一种唯一性索引 - 主键索引:使用
PRIMARY KEY
修饰的索引 - 单列索引:在一个字段上创建索引
- 多列索引:在表的多个字段上创建索引 (uid+cid,age+name等,先按第一个字段排序,再按第二个字段排序),多列索引必须使用到第一个列,才能用到多列索引,否则索引用不上
- 全文索引:使用
FULLTEXT
参数可以设置全文索引,只支持CHAR
,VARCHAR
和TEXT
类型的字段上,常用于数据量较大的字符串类型上
- 普通索引:没有任何限制条件,可以给任何字段创建普通索引(一张表的一次SQL查询只能使用一个索引,比如
- 索引的优点:提高查询效率
- 索引的缺点:索引并不是越多越好,过多的索引会导致CPU使用率居高不下,数据的改变也会造成索引文件的改变,过多的磁盘I/O造成CPU负载太重
CREATE TABLE table1(id INT,
name VARCHAR(20),
sex ENUM('male', 'female'),
INDEX(id,name));
在已经创建的表上添加索引:
CREATE [UNIQUE] INDEX 索引名 ON 表名(属性名(length) [ASC | DESC]);
删除索引:
DROP INDEX 索引名 ON 表名;
此时表结构如下:
show create table student \G
文章图片
使用具有主键索引的id进行过滤查找:
explain select * from student where uid=3;
文章图片
使用没有索引的name属性扫描
explain select * from student where name="zhangsan";
文章图片
给name添加索引
create index nameidx on student(name);
文章图片
用name索引加速搜索
文章图片
- type为ref,表示在扫描索引树
- key_len这里是152,对于给字符串类型数据建立索引的时候,一般会限制索引长度。若前面一部分字符区可以用于区分不同的数据,没必要使用很长长度的索引(key_len很大)。因为索引长了,索引文件会变大,就会使用更多的磁盘IO,应尽量避免
3. 关于缓存问题
文章图片
文章图片
对于相同的操作,若中间没有更新数据(insert/delete/update),则第一次花费时间长,第二次花费时间短,这是因为存储引擎对索引和数据进行了缓存。 第一次查询后的结果会放在数据缓存或者索引缓存里,第二次就不用花费磁盘I/O从磁盘读取索引了。
4. 过滤条件字段涉及类型转换则无法使用索引
查看表结构后发现,password属性是
varchar
,然而查询的时候使用的是int
,这就涉及到了类型转换,所以不会使用索引。文章图片
5. 删除索引
drop index pwdidx on t_user;
6. explain字段含义
- select_type
文章图片
- table
文章图片
- type
文章图片
- ref
文章图片
- 【数据库|MySQL存储引擎以及索引】Extra
文章图片
- 若经常作为过滤条件(where)的属性,需要加上索引
- 给字符串属性添加索引的时候,需要限制索引的长度(key_len)
- 索引字段涉及类型强转、mysql聚合函数调用、表达式计算等,不会使用索引
推荐阅读
- 数据库|MySQL表锁、行锁、排它锁和共享锁
- 数据库|MySQL数据类型、运算符以及数据库范式
- 数据库|MySQL SQL和索引优化总结
- 网络安全|测试攻击机伪装成目标机 IP 给目标机发送攻击报文是否成功
- 将 AWS S3 数据迁移至 TiDB Cloud 集群
- MySQL|MySQL实战(order by 语句怎么优化())
- java|码神之路博客项目部署
- mysql|【MySQL】表左连接,对右表过滤数据时的坑
- ClickHouse|ClickHouse实时分析(七)- ClickHouse分布式表详解