MySQL操作如何用到索引之原理分析

1.索引类型

1.1 主键索引

建立主键索引有两种方法，第一种，可以在创建表的时候建立，例如:
create table test(id not null primary key);
第二种
ALTER TABLE table_name ADD PRIMARY KEY ( column ) ；

1.2.唯一索引

唯一索引是这一列的数据不能重复
ALTER TABLE table_name ADD UNIQUE ( column ) ；
唯一索引和主键索引最大的区别是 唯一索引可以允许有一个列为null；

1.3 文本索引

ALTER TABLE table_name ADD FULLTEXT ( column) ；

1.4 普通索引

ALTER TABLE table_name ADD INDEX index_name ( column );

1.5 联合索引

ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 );
最左匹配原则；

1.6 聚集索引

聚集索引:在innodb 种的idb文件上存储了索引和数据都存储在这里，叶子节点存储了完整的记录行；通俗的讲就是索引键值顺序，数据行的物理存储顺序一致；除了聚集索引外的其他索引称为2及索引；

2.MSQL中问题总节

2.1 如果没有索引怎么办？

如果没有主键索引，则会找一个唯一的索引且不为空来作为聚集索引；

2.2 如果没有唯一索引，怎么办？

则会默认生成一个隐藏的rowId,来当作聚集索引。rowID相当于别名

2.3 覆盖索引

如果select 中的所有列已经包含在所用到的索引中，则会使用辅助索引，不需要再进行会表来查询；

2.4 如何建立索引

有一个离散公式来计算，用count（distinct（列明））/count（*），来作为判断依据，如果离散程度越高，则建立索引后，效果显著，如果说离散程度越低，则建立索引的效果几乎没有；

2.5 创建索引

1.再where 判断，order排序，join的on上面，group by 字段上面来创建索引；
2.索引的个数不要过多；
3.区分度低的字段不建议建立索引
4.频繁的更新值，不要作为主键或者索引；
5.不建议使用无序的值(身份证，UUID) 作为索引
6.复合索引把散列度高的字段放在前面；
7.创建复合索引，而不是修改单列索引
8.过长的字段，建立前缀索引

2.6 为什么不建议使用频繁更新的值建立索引？

因为再B+数据，频繁的建立索引，容易导致页的分裂，以及合并，这样会带来更多的性能负载；

2.7 明明建立索引，却不起作用？

1.索引列上使用函数，表达式；(replace,substr,concat,sum,count,avg);
2.字符串不加引号，出现隐式转换；
3.like中前面带有%;
4.负向查询

2.8 索引条件下推(Index Condition Pushdown (ICP) )

select *from student where last_name=‘wang’ and first_name like ‘%zi’;
首先介绍last_name,first_name 上面建立的是联合索引。
假如这条sql在last——name第一个的条件下查询3条数据出来，显然第二个索引是用不到索引的；
1.根据联合索引查出来姓‘wang’的二级索引为3条数据；
2.回表，到主索引上查询全部符合条件的数据；
3.把这3条数据交给server服务层，在服务层上过滤除名字以%zi结尾的员工；
索引条件下推的原理是:这3条数据不会再server端在此进行处理，而是再innodb引擎层，接着这个条件向下查询，查出了所有条件的数据后，返回一条sql给到server；

2.9 在什么条件下能使用索引条件下推呢

规定了什么情况可以使用ICP，什么不可以使用ICP。

可以使用ICP的情况如下：

1 表带有WHERE子句，且

2 存储引擎支持ICP，且

3 系统参数
index_condition_pushdown
被打开，且

4 查询不是针对多表的update或delete操作，且

5 查询也不是子查询，且

6 查询的表不是常量表（如果是常量表因记录数最多为1则没有必要使用ICP），且

7 索引不是聚集索引（如果是InnoDB的主键索引，则是聚集索引，此类索引的查询速度快，也不需要执行ICP以获取ICP能带来的好处）