mysql强制指定索引 主键 mysql 强制索引 有用吗

一：索引优势劣势

优势
1） 类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本。
2） 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。
劣势
1） 实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的。
2） 虽然索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

二： 索引分类

1） 单值索引 ：即一个索引只包含单个列，一个表可以有多个单列索引
2） 唯一索引 ：索引列的值必须唯一，但允许有空值
3） 复合索引 ：即一个索引包含多个列

三：索引语法

1）创建普通索引

-- 语法
CREATE 	[UNIQUE|FULLTEXT|SPATIAL]  INDEX index_name 
[USING  index_type]
ON tbl_name(index_col_name,...)


index_col_name : column_name[(length)][ASC | DESC]

-- 语法说明
-- [UNIQUE|FULLTEXT|SPATIAL]索引类型，可选
-- index_name ：索引名称
-- [USING  index_type]：指定索引类型，如果不指定默认的是BTree ,可选
-- tbl_name：表名
-- index_col_name：字段名

-- 举例(对city表的city_name字段创建名为idx_city_name 的索引)
create index idx_city_name on city(city_name)

2）查看索引

-- 语法
SHOW INDEX FROM  table_name;

-- 语法说明
-- table_name：表名

3）删除索引

-- 语法
DROP INDEX  index_name  ON  tbl_name;

-- 语法说明
-- index_name：索引名称
-- tbl_name：表名

4） ALTER命令

-- 1). 添加一个主键索引，这意味着索引值必须是唯一的，且不能为NULL

	alter  table  tb_name  add  primary  key(column_list); 

-- 2). 添加唯一索引（除了NULL外，NULL可能会出现多次）
	
	alter  table  tb_name  add  unique index_name(column_list);
	
-- 3). 添加普通索引， 索引值可以出现多次。

	alter  table  tb_name  add  index index_name(column_list);
	
-- 4). 该语句指定了索引为FULLTEXT， 用于全文索引
	
	alter  table  tb_name  add  fulltext  index_name(column_list);

-- 语法说明
-- tb_name：表名
-- index_name：索引名
-- column_list：一个或者多个字段

四： 索引设计原则

• 对查询频次较高，且数据量比较大的表建立索引。
• 索引字段的选择，最佳候选列应当从where子句的查询条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。
• 使用唯一索引，区分度越高，使用索引的效率越高。
• 索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。
• 使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。
• 利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。
  1）创建复合索引

-- 最左索引举例（对tb_seller表的NAME,email,STATUS的这些字段，创建复合索引，索引名idx_name_email_status ）
create index idx_name_email_status on tb_seller(NAME,email,`STATUS`);

--就相当于
--	对name 创建索引 ;
--	对name , email 创建了索引 ;
--	对name , email, status 创建了索引 ;

五：避免索引失效

1). 全值匹配 ，对索引中所有列都指定具体值。

-- 对name、status 、address 创建联合索引。全都使用了，索引生效
explain select * from tb_seller where name='小米科技' and status = '1' and address = '西安市'

2). 最左前缀法则（复合索引中应该遵循的，必须包含联合索引的最左列），也就是使用了左面的索引，下一个索引才能生效。

-- 对name、status 、address 创建联合索引
-- 索引中不能跳过中间索引
explain select * from tb_seller where name='小米科技' -- 索引生效
explain select * from tb_seller where name='小米科技' and status = '1' -- 索引生效
-- 索引生效，和where后使用的顺序无关
explain select * from tb_seller where status = '1' and address = '西安市' and name='小米科技'
-- 部分索引失效，只有name索引生效，因为跨越了status,所以address索引失效
explain select * from tb_seller where name='小米科技' and address = '西安市'
-- 索引失效，因为没有从最左面的列开始
explain select * from tb_seller where  status = '1' and address = '西安市'

3). 范围查询右边的列，不能使用索引 。

-- 对name、status 、address 创建联合索引
-- 部分索引失效，只有name，和status索引生效，因为使用了大于（>），所以address索引失效
explain select * from tb_seller where name='小米科技' and status > '1' and address = '西安市'

4). 不要在索引列上进行运算操作， 索引将失效。

-- 对name、status 、address 创建联合索引
-- 索引失效
explain select * from tb_seller where substring(name,3,2) = '科技'

5). 字符串不加单引号，造成索引失效（针对数字字符串的）。

-- 对name、status 、address 创建联合索引
-- 部分索引失效，name索引失效，但是status索引不生效
explain select * from tb_seller where name='小米科技' and status = 1

6). 尽量使用覆盖索引，避免select *。覆盖索引（只访问索引的查询（索引列完全包含查询列））

-- 对name、status 、address 创建联合索引
-- 虽然where中的索引生效了，但是查询列如果使用的是非索引中的字段，会回调查询
explain select name from tb_seller where name='小米科技'

7). 用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

-- 对name、status 、address 创建联合索引
-- 索引失效，虽然name有索引，但是or后面的字段没有索引，导致所有的索引失效
explain select * from tb_seller where name='小米科技' or nickname = '小米官方旗舰店'

8). 以%开头的Like模糊查询，索引失效。

-- 对name、status 、address 创建联合索引
-- 索引生效
explain select * from tb_seller where name like '科技%'
-- 索引失效（可以通过覆盖索引解决，也就是查询列是索引字段）
explain select * from tb_seller where name like '%科技'

9). 如果MySQL评估使用索引比全表更慢，则不使用索引。（9和10道理相同）

出现情况：数据量大，并且where条件的字段重复的多，可以索引失效，不走索引。

10). is NULL ， is NOT NULL 不一定索引失效。（9和10道理相同）

索引字段的值 少量是null的时候，就走索引，is not null 同理

11). in 走索引， not in 索引失效。

六： 单列索引和复合索引。

尽量使用复合索引，而少使用单列索引 。如果对name、status 、address 创建联合索引，和分别对它们创建单独索引，数据库在查询多个字段的时候，单独索引只能生效一个。