1分钟过完mysql索引内容

索引优劣势

优势

1.类似书籍目录，提高检索效率，降低数据的io成本。
2.通过索引列对数据进行排序，降低数据排序成本，降低cpu消耗。

劣势

1.索引是表，保存了主键与索引字段，并指向实体类记录，其以文件的形式存储在磁盘中，占用内存空间。
2.降低表的更新速度，对表进行插入，更新删除时，mysql不仅要保存数据，还要更新索引。

索引结构

索引是在mysql存储引擎中实现，而不是在服务器层次实现
三种存储引擎对各种索引类型的支持：

索引	InnoDB	MyISAM	Memory
btree索引	√	√	√
hash索引	×	×	√
R-tree索引（空间索引）	×	√	×
full-text	5.6版本后支持	支持	不支持

索引分类

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

索引语法

创建：create [unique|fulltext|spatial] index index_name on table_name(col,col)

create index idx_city_name on city(city_name);

查看：show index from table;

show index from index;

删除：drop index index_name on table;
alter命令

alter table table_name add primary key(column_list);
添加主键，索引值唯一且不能为空
alter table table_name add unique index_name(column);
创建索引的值必须唯一（除null外，null可能出现多次）
alter table table_name add index index_name(column);
添加普通索引，索引值可以出现多次
alter table table_name add fulltext index_name(colume);
添加全局索引

索引设计原则

1.查询频率高，数据量大的表建立索引
2.索引字段应从where子句中获取，where中组合比较多，应挑选最常用，过滤效果最好的。
3.使用唯一性索引，区分的越高过滤条件越好。
4.对于增删查操作频繁的表，索引多，会引入相当高的维护代价，降低DML操作的效率。
5.使用短索引，索引字段短，在给定大小的存储块内可以存储更多的索引值，相应的可以提高mysql访问索引的io效率。
6.利用最左前缀，n个列组合而成的组合索引，那么相当于建立了n个索引（实际就一个索引），如果查询时where子句中使用了组成该索引的前几个字段，那么这条sql语句可以利用组合索引来提高效率。

创建复合索引
  create index idx_name_emali_status on table(name,email,status);
  就相当于：
        对name创建索引；
        对name，eamil创建索引；
        对name,eamil,status创建索引；

索引生效

1.全值匹配:对索引中索引列都指定具体值，索引生效执行效率高。
select name from user where id=‘1’ and status=‘dgj’ and address=‘北京’；
2.最左前缀法则：查询条件包含左边列，但不能跳跃，类比爬楼梯。
3.范围查询：范围查询条件后的索引字段失效。
select name from user where id>1 and status=‘dgj’ and address=‘北京’；
4.字符串不加单引号索引失效。
5.尽量使用覆盖索引（索引列完全包含查询列），减少select*。
extra 为Using index condition索引回表查询。
6.or条件之后没有索引，那么涉及的索引都不会被用到。
select name from user where id=‘1’ or number=1234；
7.模糊匹配%加在前面索引失效。
select name from user where address=‘%北京’；
加后面不失效。
解决方法：查询字段覆盖索引。
8.索引中含有重复量大的字段有可能不走索引，此时走全表扫描快。
9.is null ,is not,null有时索引失效。myslq会自动判断走全表扫描快还是索引块。
10.in走索引，not in 不走索引，推荐使用in。
11.尽量使用复合索引，少使用单列索引。