文章目录

存储引擎和索引

存储引擎

索引

语法示例

使用场景

索引优化原则

存储引擎和索引

与数据库的交互性能下降、命令执行时间长等问题可能源自于不合理的查询语句，也可能是选择了错误的存储引擎、未建立索引的字段或失效的索引。这里我们简单介绍常用存储引擎的区别，并对索引有一个初步的了解。

存储引擎

常用存储引擎之间的区别：

对比项 MyISAM InnoDB

主外键 不支持 支持

事务 不支持 支持

行表锁 表锁，即使操作一条记录也会锁住整个表。 行锁，操作时只锁某一行，不对其他行有影响，适合高并发操作。

缓存 只缓存索引，不缓存真实数据。 不仅缓存索引还要缓存真实数据，对内存要求较高。

表空间 小 大

关注点 性能 事务

默认安装 Y Y

索引

索引是一种用以提升数据查询和排序性能的数据结构，它将数据以B Tree数据结构的方式进行排序，使得查询时不需要进行线性查找，而是以极短的时间定位到数据所在的节点，再从节点中找出数据。

索引的劣势

索引需要占用一定的存储空间。

索引提高了查询速度，但由于增删改操作同时需要更新索引，这些操作效率就会降低。

语法示例

# 主键

ALTER TABLE `user` ADD PRIMARY KEY (`id`)

# 唯一键

ALTER TABLE `user` ADD UNIQUE uk_name (`name`)

# 单值索引

ALTER TABLE `user` ADD INDEX idx_role (`role`)

# 复合索引

ALTER TABLE `user` ADD INDEX idx_namePassword (`name`, `password`)

# 全文索引

ALTER TABLE `user` ADD FULLTEXT ftk_password (`password`)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

使用场景

主键会自动建立唯一索引。

频繁查询或用作分组的字段应创建索引。

作为与其他表关联列的字段应创建索引。

频繁更新的字段不适合创建索引。

如果是包含大量重复数据的字段不适合创建索引。

索引优化原则

如果需要在查询中使用复合索引，必须从索引的最左列开始使用，且不跳过中间的列。

不要在索引列上进行计算、函数、类型转换等操作，否则将导致索引失效。

如果在使用复合索引的查询中，对某一索引列进行范围查询，该索引列之后的索引都将失效。

尽量使用覆盖索引，即查询列与使用到的索引列一致。（减少SELECT *）

如果使用不等于操作进行索引列的扫描，将会导致索引失效。

如果使用IS NULL或IS NOT NULL进行索引列的扫描，将会导致索引失效。

使用模糊查询进行索引列的范围扫描时，如果参数常量的左边有“%”通配符，索引将失效。可以通过使用覆盖索引解决该问题。

如果索引列进行查询时参数的常量应为字符串，那么需要添加引号，否则索引将失效。

如果索引列进行查询时使用OR关键字连接条件，那么索引将失效，应尽量避免使用。

————————————————

版权声明：本文为CSDN博主「早季9」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。

原文链接：https://blog.csdn.net/saki09/article/details/103525628