mysql - 索引/fulltext index/force index/use force /ignore index/索引失效

目录

1.MySQL索引分类

2.创建索引/增删改索引/查看索引

3.索引与约束的区别

4.全文索引FULLTEXT INDEX

5.索引优缺点

6.适创索引情景/不适创索引情景

7.索引失效的情况

8.强制使用或忽略索引(force index/use force /ignore index)

---

1.MySQL索引分类

(1)从存储结构上来划分

BTree索引（B-Tree或B+Tree索引）、Hash索引、full-index全文索引、R-Tree索引

mysql目前只支持4种索引,b-tree索引是mysql里最广泛的索引，除了archive基本所有的存储引擎都支持它.

(2)从应用层次来分

普通索引：即一个索引只包含单个列，一个表可以有多个单列索引

唯一索引：索引列的值必须唯一，但允许有空值

组合索引：即一个索引包含多个列

-- 普通索引：仅加速查询

-- 唯一索引：加速查询 + 列值唯一（可以有null）

-- 主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个

-- 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

-- 全文索引：对文本的内容进行分词，进行搜索

(3)根据中数据的物理顺序与键值的逻辑（索引）顺序关系：

聚簇索引：也称聚集索引，并不是一种单独的索引类型，而是一种数据存储方式。索引与数据存储在一起。

非聚簇索引：不是聚簇索引，就是非聚簇索引。索引与数据分开存储。

 

2.创建索引/增删改索引/查看索引

-- 在执行CREATE TABLE语句时可以创建索引，也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。

(1)ALTER TABLE

-- ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

ALTER TABLE table_name ADD INDEX index_name (column_list) ALTER TABLE table_name ADD UNIQUE (column_list) ALTER TABLE table_name ADD PRIMARY KEY (column_list)

-- 其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

 

(2)CREATE INDEX

-- CREATE INDEX可对表增加普通索引或UNIQUE索引。

CREATE INDEX index_name ON table_name (column_list) CREATE UNIQUE INDEX index_name ON table_name (column_list)

-- table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义，索引名不可选。另外，不能用CREATE INDEX语句创建PRIMARY KEY索引。

 

(3)删除索引

-- 可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句，DROP INDEX可以在ALTER TABLE内部作为一条语句处理，语法如下。

DROP INDEX index_name ON talbe_name; ALTER TABLE table_name DROP INDEX index_name; ALTER TABLE table_name DROP PRIMARY KEY;

-- 其中，前两条语句是等价的，删除掉table_name中的索引index_name。

-- 第3条语句只在删除PRIMARY KEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。

-- 如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

 

(4)查看索引

show index from tblname; show keys from tblname;

 

3.索引与约束的区别

约束作用：为了保证数据的完整性；

索引作用：快速定位特定数据，提高查询效率，确保数据的唯一性，快速定位特定数据；

 

4.全文索引FULLTEXT INDEX

https://www.cnblogs.com/guifanbiji/p/6202195.html

用于加快全文文字搜索、模糊查询等

全文索引的三种类型:

• 自然语言搜索将搜索字符串解释为自然语言中短语。
• 布尔全文搜索
• 查询扩展搜索

 

(1)创建表的同时创建全文索引

CREATE TABLE article ( 

                  id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, 

                  title VARCHAR(200), 

                  body TEXT, 

                  FULLTEXT(title, body) 

) TYPE=MYISAM; 

 

(2)通过 alter table 的方式来添加

ALTER TABLE `student` ADD FULLTEXT INDEX ft_stu_name  (`name`) ;

ALTER TABLE `student` ADD FULLTEXT ft_stu_name  (`name`);

 

(3)直接通过create index的方式

CREATE FULLTEXT INDEX ft_email_name ON `student` (`name`);

 

(4)删除全文索引

DROP INDEX full_idx_name ON tommy.girl ; 或者

ALTER TABLE tommy.girl DROP INDEX ft_email_abcd;

 

(5)使用全文索引

SELECT * FROM `student` WHERE MATCH(`name`) AGAINST('聪');

-- 以下创建name 与 address的联合全文索引

SELECT * FROM `student` WHERE MATCH(`name`,`address`) AGAINST('聪 广东');

 

5.索引优缺点

(1)优点

提高数据检索的效率(加快查询速度)，降低数据库IO成本

通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗

(2)缺点

索引是要占空间的，而且需要维护 [建立索引会占用磁盘空间的索引文件]

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

 

6.适创索引情景/不适创索引情景

(1)适创索引情景

-- 经常用于搜索的列；

-- 查询中与其他表关联的字段,即经常用于连接查询的列；

-- 查询中排序的字段，排序字段若通过索引去访问将大大提高排序效率 ,即经常需要排序的列；

-- 频繁作为查询条件的字段应该建立索引,即经常用于where子句的列上。

-- 查询中统计或者分组字段

补充:

-- 索引不是越多越好；一张表最好不要超过6个索引

-- 单键/组合索引的选择问题，组合索引性价比更高

-- 主键自动建立唯一索引

 

(2)不适创索引情景

-- 表数量少的

-- 查询中很少使用的列；

-- 对于很少数据值的列，例如性别一般只有两个值，不建议建索引

-- 对于数据量很大或者很小的列如text, image和bit不适合建索引；

-- 修改的性能大于检索的性能时不适合建索引。

-- 经常增删改的表或者字段

-- where条件不常用到的字段不创建索引

-- 不要对经常变动的数据加索引；

 

7.索引失效的情况

(1)索引失效情景

-- 组合索引中不能有列为null，如果存在则该列索引将会失效；

-- select中索引只能用一次，如果再where使用，则order by中就不要用了；

-- 模糊查询中，like "%aa%"将会使索引失效，但是后模糊不会例如like "aa%"；

-- 查询中使用"！= " 、 ">" 、 " < "会使索引失效，但是对主索引或者整形类型的索引使用的话则不会失效。

-- 在查询条件中使用IS NULL或者IS NOT NULL会导致索引失效；

-- or连接多个条件会使索引失效，除非or的条件都加了索引或者改成union all连接；

-- 索引列上使用表达式或者函数会使索引失效而进行全表扫描（在查询条件中使用正则表达式时，只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。）；

-- 类型不一致进行查询会导致索引失效，如字符串不加引号会使索引失效；

-- 如果排序的字段使用了索引，那么select的字段也要是索引字段，否则索引失效。特别的是如果排序的是主键索引则select * 也不会导致索引失效；

-- 尽量不要包括多列排序，如果一定要，最好为这队列构建组合索引；

 

(2)left join索引失效情景

• 两表字符集不一致
• 两表的存储引擎不一致

 

8.强制使用或忽略索引(force index/use force /ignore index)

(1)force index

select * from table_name force index (index_name) where conditions;

explain select id from test1 force index(name);

select a.name,b.number from t_name a left join t_number b force index(id) on a.id=b.id ;

select * from table force index(PRI,ziduan1_index) limit 2;

 

(2)use force

explain select id from test1 use index(name);

-- force index 和 use index 的区别:

-- use index 只是给mysql优化器一种选择的可能,具体的mysql优化器再进行优化选择 , 但是 force index 是强制mysql优化器使用某个索引

 

(3)忽略某个索引ignore index

-- 忽略某个索引(不使用某个索引)

select * from table ignore index(PRI) limit 2;(禁止使用主键)

select * from table ignore index(ziduan1_index) limit 2;(禁止使用索引"ziduan1_index")

select * from table ignore index(PRI,ziduan1_index) limit 2;(禁止使用索引"PRI,ziduan1_index")