索引的作用
-
加速数据检索:通过为数据库表创建索引,可以极大地减少数据库引擎在查询过程中需要扫描的数据量,从而显著提升数据检索的速度。像是字典的目录,快速定位到所查找的内容。
-
强化数据完整性:唯一索引(Unique Index)能够确保表中的某一列或列组合的值是唯一的,有效防止数据重复,而主键索引(Primary Key)更是将这一约束提升到了表级别的唯一性和非空性要求,
主要的索引类型
-
主键索引 (Primary Key Index):主键索引是表中每条记录的唯一标识,一个表只能有一个主键索引。主键索引保证了数据唯一性,确保每条记录的非空性。
-
唯一索引 (Unique Index):与主键索引类似,唯一索引也用于保证列或列组合中的值唯一,但区别在于唯一索引允许列中存在空值(NULL),并且一个表可以定义多个unique index
-
普通索引 (Index OR Non-unique Index):普通索引是最基本的索引类型,它不包含任何唯一性约束,主要用于提高查询效率。通过创建普通索引,可以加快数据检索速度,尤其是在数据量较大的情况下效果更为明显。
-
全文索引 (Full-text Index):全文索引专为处理大量文本数据而设计,能够实现对文本内容的快速搜索。
-
多列索引 ( Multiple-column Index):多列索引允许在单个索引中包含多个列,以支持更加复杂和灵活的查询条件。使用得当一个联合索引比得上多个唯一索引
优点:
-
提高查询效率:索引可以显著减少数据库查询时需要扫描的数据量,通过直接访问索引来快速定位所需数据,从而加快查询速度。
-
加速排序和分组:索引有助于加速
ORDER BY
和GROUP BY
等SQL操作,减少排序和分组的时间消耗。 -
确保数据唯一性和完整性:通过创建唯一索引或主键索引,可以确保表中数据的唯一性和完整性,避免重复数据或无效数据的存在。
缺点:
-
占用空间:索引本身需要占用一定的存储空间。随着数据量的增加,索引所占用的空间也会增加,有时甚至索引的占用比得上数据本身的占用
-
降低写的性能:当对表中的数据进行插入、更新或删除操作时,索引也需要进行相应的维护,这可能会导致写操作的性能下降。尤其是在高并发写入场景下,维护成本大
-
优化难度:随着索引数量的增加,优化查询和索引的难度也会增加。
使用索引的原则
1.需求唯一性的的字段
2.经常需要条件查询的字段
3.多表联查自动添加索引 多表联查最多不要超过三张
4.经常使用order by 的列需要添加索引
5.区分度不大没有必要添加索引 例如性别
6.经常增删改的表不要添加索引
数据库优化
1.服务器调优
2.避免索引失效
1.联合索引 靠左原则 跳过失效
2.模糊查询 失效
3.where 后有内置函数 失效 4.类型自动转换时
5.where 两个条件 右侧失效 范围相关放右边
6.否定会失效(主键除外) !not <>
7.OR 一个不走索引 都不走索引 建议两边都使用索引
3.避免多表联查 超过三张表=>添加冗余字段
4.避免一张表上的数据过多 分库分表
联合索引 index(a,b,c)
ab ba 全都走索引 MySQL内有优化器 自动交换位置
ac ca a走联合索引 c不走联合索引 有自己的索引走自己的索引
bc cb 都不走联合索引 有自己的索引走自己的索引
其中MySQL优化器会自动选择最优查询,
EXPLAIN SELECT *FROM student_info WHERE student_id >1
按道理是走联合索引 但是优化器预计需要扫描表中大部分行时。查询优化器认为全表扫描比使用索引更快