MySQL数据库索引的学习

索引的设计原则

哪些情况适合加索引？

准备工作

#创建随机产生字符串的函数

DELIMITER //	
CREATE FUNCTION rand_string(n INT)
	RETURNS VARCHAR(255) #该函数返回一个字符串
BEGIN
        DECLARE chars_str VARCHAR(100) DEFAULT 'adafqfabfabcafqifqfyhafnkfavbabvavhabdadhabcacakcacavhafqoqnacncabcbabaadaa';
        DECLARE return_str VARCHAR(255) DEFAULT '';		
        DECLARE i INT DEFAULT 0;
        WHILE i < n DO
        SET return_str=CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
        SET i=i+1;
END WHILE;
RETURN return_str;
END //
DELIMITER;       

SELECT @@log_bin_trust_function_creators

SET  GLOBAL log_bin_trust_function_creators=1

#创建随机产生数字的函数
DELIMITER //
CREATE FUNCTION rand_num(from_num INT,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i=FLOOR(from_num+RAND()*(to_num-from_num+1));
RETURN i;
END //
DELIMITER;

#存储过程1：创建插入课程表的存储过程
DELIMITER //
CREATE PROCEDURE insert_course(max_num INT)
BEGIN
DECLARE i INT DEFAULT 0;
	SET autocommit=0;
	REPEAT
	SET i=i+1;
	INSERT INTO course(course_id,course_name)VALUES(rand_num(10000,10100),rand_string(6));
	UNTIL i=max_num
	END REPEAT;
	COMMIT;
END //
DELIMITER;


##存储过程2：创建插入学生信息的存储过程
DELIMITER //
CREATE PROCEDURE insert_stu(max_num INT)
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit=0;
REPEAT
SET i=i+1;
INSERT INTO student_info(course_id,class_id,student_id,NAME)VALUES(rand_num(10000,10100),rand_num(10000,10200),rand_num(20000,20100),rand_string(6));
UNTIL i=max_num
END REPEAT;
COMMIT;
END //
DELIMITER;

CALL insert_course(100);
CALL insert_stu(1000000);

1.字段的数值有唯一性的限制
索引本身可以起到约束的作用，比如唯一索引、主键索引都是可以起到唯一性约束的，因此在我们的数据表中，如果某个字段是唯一性的，就可以直接创建唯一性索引，或者主键索引，这样就可以更快速地通过该索引来确认某条记录。

2.频繁作为WHERE查询条件的字段
某个字段在SELECT语句的WHERE条件中经常被使用到，那么就需要给这个字段创建索引了，尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。

3.经常GROUP BY和ORDER BY的列
索引就是让数据按照某种顺序进行存储或者检索，因此当我i们使用GROUP BY对数据进行分组查询或者使用ORDER BY 对数据进行排序的时候，就需要对分组或者排序的字段进行索引。如果待排序的列有多个，那么就可以在这些列上建立组合索引。

4.UPDATE、DELETE的WHERE条件列
对数据按照某个条件进行查询后再进行UPDATE或者DELETE的操作，如果对WHERE字段创建了索引，就能大幅度提升效率。原理是我们需要先根据WHERE条件列检索出来这条记录，然后再对它进行更新或者删除。如果进行更新的时候，更新的是非索引字段，提升的效率会更明显，这是因为非索引字段的更新不需要对索引进行维护。

5.DISTINCT字段需要创建索引
有时候，我们需要对某个字段进行去重，使用DISTICT，那么对这个字段创建索引，也会提升查询效率。这是因为索引会对数据按照某种顺序进行排序，所以在去重的时候也会快很多。

6.多表JOIN连接操作时，创建索引注意事项
首先，连接表的数量尽量不要超过3张表，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。
其次，对WHERE条件创建索引，因为WHERE才是对数据条件的过滤，如果在数据量非常大的情况下，没有WHRE条件过滤是非常可怕的。
最后，对于用于连接的字段创建索引，并且该字段在多张表中的类型必须要一致

7.使用列的类型小的创建索引
类型大小指的就是该类型表示的数据范围的大小。如果我们想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如我们能使用INT就不要使用BIGINT，能使用MEDIUMINT就不要使用INT。这是因为：
数据类型越小，在查询的时候进行的比较操作就越快，
数据类型越小，索引占用的存储空间就越小，在一个数据页内就可以放下更多的记录，从而减少磁盘I/0带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。
这个建议对于表的主键来说是更加适用的，因为不仅是聚簇索引中会存储主键值，其他所用的二级索引的节点处都会存储一份记录的主键值，如果主键使用更小的数据类型，也就意味着节省更多的存储空间和更高效的I/0.

8.使用字符串前缀创建索引
假设我们的字符串很长，那存储一个字符串就需要占用很大的存储空间，在我们需要为这个字符串列建立索引时，那就意味着在对应的B+树中有这么两个问题：
1.B+树索引中的记录需要把该列的完整字符串存储起来、更费时而且字符串越长，在索引中占据的存储空间就越大。
2.如果B+数索引中索引列存储的字符串很长，那在做字符串比较时就会占用更多的时间。
我们可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符值，既节约空间，又减少了字符串的比较时间，还大体能解决排序的问题。

问题是，截取多少呢？ 截取得多了，达不到节省索引存储空间的目的，截取的少了，重复的内容太多，选择性会降低。怎么计算不同长度的选择性呢？

引申出另外一个问题：索引列前缀对于排序的影响

使用索引列前缀的方式无法支持使用索引排序，只能使用文件排序

9.区分度高（散列性高）的列适合作为索引
列的基数指的时某一列中不重复数据的个数，比如某个列的包含值2，5，8，2，5，8，2，5，8，虽然有9条记录，但该列的基数却是3.也就是说，在记录行数一定会的情况下，列的基数越大，该列中的值越分散；列的基数越小，该列中的值越集中。这个列的基数指标非常重要，直接影响到我们是否能有效的利用索引，最好为列的基数大的列建立索引，为基数小的列建立索引的效果可能不好。
可以使用公式select count(distinct a) /count(*) from t1计算区分度，越接近于1越好，一般**超过33%**就算是比较高效的索引了
拓展：联合索引把区分度高(散列度高)的列放在前面

10.使用最频繁的列放到联合索引的左侧
这样也可以较少的建立一些索引。同时，由于最左前缀原则，可以增加联合索引的使用率。

11.在多个字段都要创建索引的情况下，联合索引优于单值索引

限制索引的数目？？
建议单表的索引数量不超过6个
1.每个索引都占用磁盘空间，索引越多，需要的磁盘空间就越大
2.索引会影响INSERT\UPDATE\DELETE等语句的性能，因为表中的数据更改的同时，索引也会进行调整和更新，会造成负担。
3.优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，会增加MySQL优化器生成执行计划的时间，降低查询的性能。

不适合创建索引的7种情况
1.在where中使用不到的字段，不要设置索引
where条件（包括group by、order by）里用不到的字段不需要创建索引，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的。
2.数据量小的表最好不要使用索引
如果表的记录太少，比如少于1000个，那么是不需要创建索引的，表的记录i太少，是否创建索引对查询的效率影响不大。
3.有大量重复数据的列上不要建立索引
当数据重复度比较大，比如高于10%的时候，也不需要对这个字段使用索引。
4.避免对经常更新的表创建过多的索引
5.不建议用无序的值作为索引
6.删除不再使用或者很少使用的索引
7.不要定义冗余或重复的索引

优先考虑覆盖索引

什么是覆盖索引？
理解方式1：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行，毕竟索引叶子节点存储了他们索引的数据，当能通过读取索引就可以找到想要的数据，那就不需要读取行了，一个索引包含满足查询结果的数据就叫做覆盖索引。
理解方式2：非聚簇复合索引的一种形式，它包括在查询里面的SELECT、JOIN和WHERE子句用到所有列(即)建索引的字段正好是覆盖查询条件中所涉及的字段）
简单来说：索引列+主键包含 SELECT 列 FROM 之间查询的列

覆盖索引的利弊
好处：
1.避免Innodb表进行索引的二次查询（回表）
2.可以把随机IO变成顺序IO，加快查询效率
(回表后是一个随机IO，因为聚簇索引是根据主键进行排序的，非聚簇索引是根据具体列排序的)
由于覆盖索引可以减少数的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化
弊端：
索引字段的维护总是有代价的，因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了，这是业务DBA或者业务数据架构师的工作

索引下推
使用前后对比
Index Condition Pushdown （ICP） 是Mysql 5.6中的新特性，是一种在存储引擎层使用索引过滤数据的优化方式。
如果没有ICP，存储引擎会遍历索引以定位基表中的行，并将他们返回给Mysql服务器，由mysql服务器评估WHERE后面的条件是否保留行。
启用ICP后，如果部分WHERE条件可以仅使用索引中的列进行筛选，则MySQL服务器会把这部分WHERE条件放到存储引擎筛选。然后，存储引擎通过使用索引条目来筛选数据，并且只有满足这一条件时才从表中读取行。
好处：ICP可以减少，存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数。但是ICP的加速效果取决于在存储引擎内通过ICP筛选掉数据的比例

ICP的开启\关闭
默认情况下启用索引条件下推，可以通过设置系统变量optimizer_switch控制：index_condition_pushdown

ICP的使用条件
1.如果表访问的类型为range、ref、eq_ref和ref_or_null可以使用ICP
2.ICP可以用于InnoDB和MyISAM表，包括分区表InnoDB和MyISAM表
3.对于InnoDB表，ICP仅用于二级索引。ICP的目标时减少全行读取次数，从而减少I\O操作。（ICP的前提是存在回表的操作，聚簇索引不用回表）
4.当SQL使用覆盖索引时，不支持ICP，因为这种情况下使用ICP不会减少I\O。（ICP的前提是存在回表的操作，索引覆盖不用回表）
5.相关子查询的条件不能使用ICP。