数据库索引使用场景-java创建solr索引库

文章目录

说明：联合索引(name,age,birthDay) 1、匹配左边的列？

（1）使用联合索引中的单个列查询:

select * from tableName where name = "userName";
select * from tableName where age = 14;
select * from tableName where birthDay = "2008-09-09";

PS：仅name条件会走索引;

（2）使用联合索引多列查询：

//走联合索引
select * from tableName where name = "userName" and age=14 and birthDay = "2008-09-09";
//不走索引,原因请自行思考B+树目录项记录
select * from tableName where age=14 and birthDay = "2008-09-09";
//仅name走联合索引,原因是B+树目录项记录按建立顺序查找
select * from tableName where name = "userName" and birthDay = "2008-09-09";
select * from tableName where age=14 and name = "userName";//走联合索引,满足最左前列

PS:多列索引列查询,只要满足最左联合索引即可走索引,顺序可乱序,查询优化器会按照联合索引进行优化

总结：

（1）联合索引生效与否,查询条件只与其条件和联合索引的最前列(或多列)匹配即可;
（2）联合索引可部分生效,满足最前列生效,其他跳跃列不生效。

2、匹配列前缀？

（1）B+树索引中,无论是索引的目录项还是用户记录都是按照某些列的大小进行排序;
（2）索引查询中,无论单列索引还是联合索引,只需对索引列进行前缀匹配,即可快速定位索引和数据;
SELECT * FROM tableName WHERE name LIKE 'us%';//满足匹配前缀条件,走索引
SELECT * FROM tableName WHERE name LIKE '%As%';//B+树索引存储中,全模糊无法直接定位,索引失效
SELECT * FROM tableName WHERE name LIKE '%As';//B+树索引存储中,匹配条件通用在前,无法直接定位,索引失效

总结：模糊查询生效条件：前缀匹配版模糊(“condition%”),其他两种不生效

3、匹配范围值？

（1）单列索引值范围查找

SELECT * FROM tableName WHERE name > 'A' AND name < 'Z';//范围索引生效
B+树先根据name值等于A和Z的位置,根据这两个位置找到对应的主键,然后回表获得用户记录。

（2）多列索引值范围查找

SELECT * FROM tableName WHERE name > 'A' AND name < 'Z' and age < 20;//最左第一列生效
B+树的二级索引存储按照联合索引顺序排序,先获取到name为A和Z的索引,无法再根据age去查询,
原因是name值相同情况下才会有age顺序排列,即先根据name获取,在name值不同时,age是无序的,无法直接二分法查询,
会再name获得的记录中过滤age值。

说明：此处条件如果不是联合索引的第一列范围,则联合索引直接失效。

总结：联合索引在使用索引值进行范围查找时,只有其第一列可以走范围索引,其他值失效。

4、精确匹配某一列并范围匹配另外一列？

（1）最左边列是精确查找,右边列是范围,联合索引是生效的(仅挨着精确值的后一个范围生效)

SELECT * FROM tableName WHERE name = ‘A’ AND age < 20;

根据B+树二级索引排序,先找出name值为A的所有目录项记录,然后再使用age范围索引值进行查找。

（2）从最左列开始,挨个索引精确值,精确值后的紧挨范围生效

SELECT * FROM tableName WHERE name = ‘A’ AND age = 20 and birthDay > “2008-09-09”;

说明：name和age精确等值根据B+树查询,然后birthday再根据范围匹配,联合索引生效

5、用于排序？

SELECT * FROM tableName ORDER BY name desc, age desc, birthDay desc LIMIT 10;
或SELECT * FROM tableName ORDER BY name desc, age desc LIMIT 10;
B+树的二级索引是已经按照联合索引中排序好的,可以通过回表将索引外的列拿到并返回。

说明：可结合where条件中的最左索引和order by的索引列连接排序

排序中联合索引注意事项：

（1）顺序不能颠倒且按照联合索引顺序(可最左部分)

排序中的索引,必须按照联合索引中的顺序来,颠倒顺序也是不能生效,原因是二级索引是按照name,age,birthDay顺序来排序,无优化

（2）ASC、DESC不能混用(默认排序是asc)

  使用联合索引排序,不能字段排序混合使用
  	//此处省略了name的排序,默认为asc,混合使用造成失效
    错误使用如：SELECT * FROM tableName ORDER BY name, age DESC LIMIT 10;
    正确使用如：SELECT * FROM tableName ORDER BY name desc, age DESC LIMIT 10;//均使用desc
    或SELECT * FROM tableName ORDER BY name, age LIMIT 10;//默认排序asc

说明：多字段排序混合使用错误原因为,先按照第一个字段值最小(或最大)的规则查询,然后根据后续字段的最小(或最大)的规则查询,来回重复查询,直到满足指定条件的条数,来回查找反而显得更慢,所以数据库直接采用了文件排序即fileSort;

（3）WHERE子句中出现非排序使用到的索引列(普通列)

（不能排序）where条件中使用普通列进行条件检索,先将数据过滤出来,然后在根据name排序如下：
    SELECT * FROM tableName WHERE address = 'beijing' ORDER BY name LIMIT 10;
    说明：此处先过滤是全表扫描,扫描到的结果因其无法定位到二级索引中的那些部分,所以无法用name来排序。
（可以排序）where条件中使用联合索引部分列(按顺序使用)和排序中也使用如下：
    SELECT * FROM tableName WHERE name = 'A' ORDER BY age LIMIT 10;

说明：where条件使用按顺序的索引列,先将索引记录拿出来(排序好的),基于拿出来的索引记录,然后再根据age进行排序,可生效

（4）排序列包含非同一个索引的列

SELECT * FROM tableName WHERE name = ‘A’ ORDER BY age,address LIMIT 10;

说明：排序列包含非同一个索引(联合索引),无法使用索引中的排序来生效。

（5）排序列使用了复杂的表达式

SELECT * FROM tableName ORDER BY UPPER(name) LIMIT 10;

说明：排序对值进行了函数计算或转换,破坏了索引中的顺序,无法直接使用索引中的排序

6、用于分组？

有时候我们为了方便统计表中的一些信息,会把表中的记录按照某些列进行分组。比如下边这个分组查询：
	//索引列可使用最左部分索引列分组
    SELECT name, birthDay, age, COUNT(1) FROM tableName GROUP BY name, age, birthDay;
这个查询语句相当于做了3次分组操作：
（1）先把记录按照name值进行分组,所有name值相同的记录划分为一组。
（2）将每个name值相同的分组里的记录再按照age的值进行分组,将age值相同的记录放到一个小分组里,所以看起来就像在一个大分组里又化分了好多小分组。
（3）再将上一步中产生的小分组按照birthDay的值分成更小的分组,所以整体上看起来就像是先把记录分成一个大分组,然后把大分组分成若干个小分组,
然后把若干个小分组再细分成更多的小小分组。

说明：根据分组的原理和联合索引建立的原理一致,可使用该索引的排序用于分组。

7、二级索引的回表？

以一个二级索引查询为例,阐述下二级索引查询过程如下：
SELECT * FROM tableName WHERE name > 'A' AND name < 'Z';
查询过程：
（1）从二级索引对应的B+树中取出name值在A～Z之间的目录项。
（2）由于二级对应的B+树目录项中只包含name、age、birthDay、id这4个字段,而查询列表是*,意味着要查询表中所有字段,还要包括address字段。
这时需要把从上一步中获取到的每一条记录的id字段都到聚簇索引对应的B+树中找到完整的用户记录即回表,然后把完整的用户记录返回给查询用户。

说明：

（1）第一步是顺序I/O(目录项获取id),第二步为随机I/O(根据id回表操作);

（2）需要回表的记录越多数据库索引使用场景数据库索引使用场景，使用二级索引的性能就越低

二级索引特点：

（1）会使用到两个B+树索引,一个二级索引,一个聚簇索引。
（2）访问二级索引使用顺序I/O,访问聚簇索引使用随机I/O。

8、覆盖索引？

解决回表消耗的性能代价,在查询列表中只包含索引列即覆盖索引。
如：SELECT name, birthDay, age FROM tableName order BY name, age, birthDay;//列表是索引列

说明：

（1）因为查询列表在二级索引中可以直接拿到，故不需要再进行回表操作即聚簇索引中查询;

（2）建议查询不要使用*,避免回表,可使用字段查询,尽量减少不必要的回表操作。

9、建立索引的场景？

（1）只为用于搜索、排序或分组的列创建索引;

出现在 WHERE子句中的列、连接子句中的连接列,或者出现在ORDER BY或GROUP BY子句中的列创建索引。
出现在查询列表中的列没必要建立索引：
SELECT birthDay,address FROM tableName WHERE name = 'A';
像查询列表中的birthDay、address两列就不需要建立索引,只需要为出现在WHERE子句中的name列创建索引就行了。

（2）考虑列的基数,基数越大,查询效果越好

列的基数指的是某一列中不重复数据的个数如1,2,3,4,6,7,3,1;这列基数为6。
在记录行数一定的情况下,列的基数越大,该列中的值越分散,列的基数越小,该列中的值越集中。

总结：最好为列的基数大的列建立索引,为基数太小列的建立索引效果可能不好。

（3）索引列的类型尽量小

类型大小指的就是该类型表示的数据范围的大小,以整数类型为例,有TINYINT、MEDIUMINT、INT、BIGINT几种,它们占用的存储空间依次递增。
如果我们想要对某个整数列建立索引的话,在表示的整数范围允许的情况下,尽量让索引列使用较小的类型,
如能使用INT就不要使用BIGINT,能使用MEDIUMINT就不要使用INT等。原因如下：
    （1）数据类型越小,在查询时进行的比较操作越快(CPU层次);
    （2）数据类型越小,索引占用的存储空间就越少,在一个数据页内就可以存更多的记录,减少磁盘I/O的性能损耗,即将更多的数据页缓存在内存中,加快读写效率。

（4）索引字符串值的前缀

对字符串类型的字段建立二级索引时,只保留字符串的前几个字符,虽然不能精确定位,但是能前缀匹配(节约对比时间)到某些索引,然后再回表进行全值匹配。

优势：

节约了存储空间和二级索引对比时间,全值回表对比相对来说也不会很慢。

在建表语句中只对name列的前8个字符进行索引可以设置如下：
    CREATE TABLE tableName(
     id INT(11) NOT NULL PRIMARY KEY,
     name VARCHAR(100) NOT NULL,
     age INT NOT NULL,
     birthDay DATE NOT NULL,
     address varchar(100) NOT NULL,
     KEY idx_name_age_birthDay (name(8),age,birthDay)
    );
name(8)就表示在建立的B+树索引中只保留记录的前8个字符的编码,这种只索引字符串值的前缀策略尤其在字符串类型能存储的字符比较多的时候是可观的。

劣势：

使用了索引列前缀,不能使用二级索引对排序生效。

  如前边只把name列的前8个字符放到了二级索引中,下面查询排序就不能走索引：
    SELECT * FROM tableName ORDER BY name LIMIT 10;
原因是二级索引中不包含完整的name列信息,即无法对前八个字符相同,后边的字符不同的记录进行排序,即索引列前缀的方式无法支持使用索引排序,只能用文件排序。

（5）让索引列在比较表达式中单独出现

WHERE age * 2 < 4;//此条件将值扩大了再比较,根据B+树索引是全表扫描,失效(列是经过计算的)
WHERE age < 4/2;//符合B+树索引查询原理,索引生效(单独列出现)

总结：如果索引列在比较表达式中不是以单独列的形式出现,而是以某个表达式,或者函数调用形式出现的话,是用不到索引的。

（6）主键插入顺序

让主键具有AUTO_INCREMENT,让存储引擎自己为表生成主键,而不是手动插入,避免中间数据页分裂,影响性能。

（7）冗余和重复索引

(1)冗余：联合索引和单列索引建立重复如(name,age,birthDay)和(name),最左列或者最左多列重复,增加了索引维护成本;
(2)索引重复：字段被多次建立如既是主键索引也是唯一索引或者普通索引。

总结：

（1）B+树索引在空间和时间上都有代价,建立必要的索引;
（2）B+树索引适用：
    （1）全值匹配;
    （2）匹配左边的列;
    （3）匹配范围值;
    （4）精确匹配某一列并范围匹配另外一列;
    （5）用于排序;
    （6）用于分组;
（3）使用索引注意事项：
    （1）只为用于搜索、排序或分组的列创建索引;
    （2）为列的基数大的列创建索引;
    （3）索引列的类型尽量小;
    （4）可以只对字符串值的前缀建立索引;
    （5）只有索引列在比较表达式中单独出现才可以适用索引;
    （6）为了尽可能少的让聚簇索引发生页面分裂和记录移位的情况,建议让主键拥有AUTO_INCREMENT属性;
    （7）定位并删除表中的重复和冗余索引;
    （8）尽量使用覆盖索引进行查询,避免回表带来的性能损耗。