索引是用于快速找到数据记录的一种数据结构。就像字典的目录,可以快速定位到我们要查询的内容。Mysql进行查询时,首先查看查询条件是否符合索引,符合则通过索引查找数据,如果不符合则全表扫描。
优点:
(1)索引可以,提高数据检索的效率,降低数据库的IO成本,这也是创建索引最主要的原因。
(2)通过创建唯一索引,可以保证数据库表中每一行数据的唯一性。
(3)在实现数据的参考完整性方面,可以加速表和表之间的连接。换句话说,对于有依赖关系的子表和父表联合查询时,可以提高查询速度。
(4)在使用分组和排序子句进行数据查询时,可以显著减少查询中分组和排序的时间,降低了CPU的消耗。
缺点:
(1)创建索引和维护索引要耗费时间,并且随着数据量的增加,所耗费的时间也会增加。
(2)索引需要占磁盘空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,存储在磁盘上,如果有大量的索引,索引文件就可能比数据文件更快达到最大文件尺寸。
(3)虽然索引大大提高了查询速度,同时却会降低更新表的速度。当对表中的数据进行增加、删除和修改的时候,索引也要动态地维护,这样就降低了数据的维护速度。
添加索引:
创建表时:
除了可以定义列的数据类型外,还可以定义主键约束、外键约束或者唯一性约束,而不论创建哪种约束,在定义约束的同时相当于在指定列上创建了一个索引。
创建表后增加索引:
alter table 表名 add index 索引名(字段)
查看索引的三种方式:
show create table 表名 查看表的创建语句
show index from 表名 查看表的索引
explain Sql语句 性能分析工具
适合添加索引的字段:
1.字段数值具有唯一性的限制
唯一性索引、主键索引都可以起到唯一性约束
2.频繁使用where条件查询的字段
在数据量大时,创建普通索引可以提高查询效率
3.经常使用order by 或group by 的字段
4.update、delete的where条件列
如果进行更新数据时,更新的字段是非索引列提升的效率更明显。但是如果是索引列,索引列也需要维护。
5.distinct字段
对去重字段添加索引也会提升查询效率
6.多表连接创建索引注意事项
连接表的数量不要超过三张
对where 条件字段创建索引
对表连接字段创建索引,该字段在多张表的类型必须一致,否则会导致索引失效
7.使用列的类型小的创建索引
数据类型越小,查询时比较操作就越快
数据类型小,索引占的存储空间就小
8.使用字符串前缀创建索引
如果字符串很长,创建索引将会占用大量空间
我们截取字符串前面一部分内容创建索引,这样虽然不能精确定位,但是能定位相应前缀,在查询完整的字符串,这样节约空间,又减少了字符串的比较时间。
9.使用最频繁的列放在联合索引的左侧
联合索引是最左原则,如果跳过最过前面的字段,则索引失效
10.限制索引的数量
单表的索引不要超过六个
索引占空间,还会影响增删改的效率
数据库优化
1.服务器调优
2.避免索引失效
3.避免多表查询,不要超过三张表
4.避免一张表上数据过多
索引失效
1.联合索引中靠左原则
靠左原则,如果跳过某个字段,索引后面的字段无法使用
如果跨字段,左侧字段会走索引,右侧字段索引失效
2.模糊查询索引失效
like 以%开头索引失效 只有%不在第一个位置索引才会起作用
3.where 后使用内置函数 索引失效
常量运算不会导致索引失效
列运算会导致索引失效
4.类型自动转换会导致索引失效 给字符串复制不要省略单引号
5.当where 后有条件时,范围条件右侧索引失效
当创建索引时,把范围相关的索引尽量放在右侧。mysql有调优器,会按照联合索引更改条件位置
6.否定会让索引失效
!= 、<>、is not null
7.or关键字中 如果又一个不走索引 则都不走索引
使用or关键字建议两边都使用索引
8.避免使用子查询
复杂的子查询可能导致索引失效