一、什么是索引
1.概念:索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。
2.索引的优点:
a. 高效性: 利用索引可以提高数据库的查询 效率;
b. 唯一性: 索引可以确保所查的数据的唯一 性;
c.完整性:用户可以加速表和表之间的连接,实现表与表之间的参照完整性;
d.特殊能力:通过使用索引,可以在查询过程中使用优化隐藏器,从而提高系统性能。
3.索引的缺点:
a.虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、 UPDATE和DELETE;
b.因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。建立索引会占用磁盘空间的索引文件;
c.如果在一个大表上创建了多种组合索引,索引文件的会膨胀很快。
二、常用的索引有哪些种类
1.主键索引:在数据库关系图中为表定义一个主键将自动创建主键索引
create table 表名(
字段1 int() auto_increment primary key,
或 primary key(字段1)
)
2.唯一索引:不允许具有索引值相同的行,从而禁止重复的索引或键值
create table 表名(
字段1 int() not null unique,
或 UNIQUE KEY(字段1)
)
3.常规索引:最基本的索引类型,没有唯一性之类的限制
create table 表名(
字段1 int() not null,
index/key(字段1)
)
4.全文索引:搜索引擎的关键技术,用于检索文本信息,可以是词语或者段落
create table 表名(
字段1 varchar(32) not null,
fulltext key (字段名,字段名,字段名) with parser ngram
)
三、索引失效
Ⅰ.会使用索引的场景
1.匹配全值:对索引中的所有列都有等值匹配的条件;
2.匹配值的范围查询:对索引的值进行范围查询;
3.匹配最左前缀:仅仅使用索引中的最左边列进行查询;
4.仅对索引进行查询:当查询列都在索引字段中;
5.匹配列前缀:仅仅使用索引的第一列,并且只包含索引第一列的开头部分进行查询;
6.索引部分等值匹配,部分范围匹配;
7.若列名是索引,则使用column_name is null就会使用索引;
Ⅱ.索引失效
1.以%开头的like模糊查询;
2.数据类型出现隐式转化,不会使用索引;
3.组合索引,不满足最左原则,不使用符合索引;
4.估计使用索引比全表扫描还慢,则不要使用索引;
5.用or分隔条件,若or前后只要有一个列没有索引则不使用;
6.使用! = 或 <> 操作符时 :引擎会放弃使用索引而进行全表扫描;
7.对字段进行 null 值判断: 应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描;
8.避免 select * : 在解析时,会将 '*' 依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间。