数据库性能优化(sql优化)_索引详解02_yxy
数据库性能优化_索引详解02
-
1 该怎么创建索引？
-
- 1.1 数据筛选性
- 1.2 取消回表问题
- 1.3 增删改操作较少
- 1.4 创建组合索引的顺序
-
2 为什么不走索引？
-
- 2.1 最左匹配原则
- 2.2 条件列有计算或函数
- 2.3 存在隐式类型转换
- 2.4 大量回表
- 2.5 统计信息不准确
1 该怎么创建索引？
真实环境中什么情况应该建索引，该怎么创建索引才更合理呢？
1.1 数据筛选性
索引列的筛选性较好，重复数据少，查询的行只占全表的一小部分可以考虑建索引;
能够快速定位数据并减少回表所耗费的时间
例如
--初始化数据
CREATE TABLE "TEST01"("A" INT,"B" CHAR(10),"C" CHAR(10));
INSERT INTO TEST01 SELECT LEVEL,LEVEL,LEVEL FROM DUAL CONNECT BY LEVEL <= 10;
INSERT INTO TEST01 SELECT 1,'1','1' FROM DUAL CONNECT BY LEVEL <= 10000;
commit;
--查看表数据
select A ,count(*) from TEST01 group by A;
A count(*)
1 10001
2 1
3 1
4 1
5 1
6 1
7 1
8 1
9 1
10 1
例子中TEST01表中共有10010行数据，等于2的数据有1行，等于1的有10001行
--创建A列索引
CREATE OR REPLACE INDEX "YXY_TEST01_A" ON "TEST01"("A" ASC) ;
--开始测试
select * from TEST01 where A=2; --等于2的有1行
select * from TEST01 where A=1; --等于1的有10001行
1.针对select * from TEST01 where A=1查询语句
数据筛选性很差，等于1的重复数据很多，如果查询走了YXY_TEST01_A索引去扫描数据，那么YXY_TEST01_A索引上没有B、C列数据怎么办，定位到10001行数据后，只能再去扫描原表，就造成了10001行的回表操作;
2.针对select * from TEST01 where A=2查询语句
数据筛选性较好，等于2的数据只有1行，查询走了YXY_TEST01_A索引去扫描数据就会很快，只有1行的回表操作;
1.2 取消回表问题
索引一般没有包括全部数据列，比如上面YXY_TEST01_A索引，就只包含了A列，类似于一个原表裁剪出来的一小部分
真实环境中，如果select后的列较少，索引可以完全包含需要查询的列进行优化，这样可以避免回表操作，就算筛选性差也可以走索引;
例如
--初始化数据
CREATE TABLE "TEST01"("A" INT,"B" CHAR(10),"C" CHAR(10));
INSERT INTO TEST01 SELECT LEVEL,LEVEL,LEVEL FROM DUAL CONNECT BY LEVEL <= 10;
INSERT INTO TEST01 SELECT 1,'1','1' FROM DUAL CONNECT BY LEVEL <= 10000;
commit;
--创建A列索引
CREATE OR REPLACE INDEX "YXY_TEST01_A" ON "TEST01"("A" ASC) ;
--开始测试
select A,B from TEST01 where A=1; --有回表
select A from TEST01 where A=1; --没有回表
跟上个例子数据相同，其中A=1筛选性很差，有10001行重复数据
1.针对select A,B from TEST01 where A=1
这个select需要查询A，B两列数据，扫描YXY_TEST01_A索引找到A=1的数据后，索引上没有B列的信息，就只能经过10001次回表操作，回到原表上去找到满足A=1的B列数据，针对这种情况，走索引效率就会很差;
2.针对select A from TEST01 where A=1
这个select后的列，只有A，并且A在索引列中，这时索引也可以看成是一个小表，查询时快速定位到A=1的数据，并且可以避免回表操作，直接返回结果，针对这种情况，即使是扫描索引速度也会更快;
1.3 增删改操作较少
索引虽然可以提高查询速度，但它们也会增加数据库的存储需求，并可能降低插入、删除和更新操作的性能，因为索引本身也需要被更新，每增删改一行数据都更新索引会降低性能;
例如
--初始化数据
CREATE TABLE "TEST01"("A" INT,"B" CHAR(10),"C" CHAR(10));
INSERT INTO TEST01 SELECT LEVEL,LEVEL,LEVEL FROM DUAL CONNECT BY LEVEL <= 10;
INSERT INTO TEST01 SELECT 1,'1','1' FROM DUAL CONNECT BY LEVEL <= 10000;
commit;
--创建A、B、C三个索引
CREATE OR REPLACE INDEX "YXY_TEST01_A" ON "TEST01"("A" ASC) ;
CREATE OR REPLACE INDEX "YXY_TEST01_B" ON "TEST01"("B" ASC) ;
CREATE OR REPLACE INDEX "YXY_TEST01_C" ON "TEST01"("C" ASC) ;
--测试
假设我们要向 TEST01 表中插入一条新记录：
INSERT INTO TEST01 (A,B,C) VALUES (9,'99','999');
commit;
数据库首先在表中插入这条记录.
然后，数据库需要在 YXY_TEST01_A 索引中找到合适的位置，并插入 9 的索引信息
接着，在 YXY_TEST01_B 索引中找到合适的位置，并插入 99 的索引信息
同样，在 YXY_TEST01_C 索引中插入 999 的索引信息
如果索引较多，这个过程会花费更多的时间，导致插入操作变慢
在插入数据时，会涉及索引数据结构的维护，后续在讲索引存储结构时会介绍B树怎么进行增删改
1.4 创建组合索引的顺序
①等值条件放前面，范围条件放后面
②筛选性好的放前面，筛选性差的放后面
因为查询时，组合索引只能利用上一个非等值字段
例1
--初始化数据
CREATE TABLE "TEST01"("A" INT,"B" CHAR(10),"C" CHAR(10));
--创建组合索引
CREATE OR REPLACE INDEX "yxy_A_B_C" ON "TEST01"("A" ASC,"B" ASC,"C" ASC) ;
--开始测试
Ⅰ.
select * from TEST01 where A>5 and B=10; --只利用上了A列索引
Ⅱ.
select * from TEST01 where B>5 and A=10; --利用上了A，B列索引
Ⅰ，因为yxy_A_B_C索引，A列在前，只能先走A索引去过略数据，A条件是范围过滤，过略出A>5数据后，在此基础上再扫描全部满足条件的数据去筛选B=10的数据，这种情况只利用上了A列索引;
（B树特性，索引只能利用上一个范围条件，后续的列不能再走索引）
Ⅱ，因为yxy_A_B_C索引，A列在前，第二种情况A=10为等值条件，过滤A=10的数据后，还能继续利用索引过滤B>5的数据;
这样就能够A，B列都利用上索引，不用多过略一次数据
例2
--初始化数据
CREATE TABLE "TEST01"("A" INT,"B" CHAR(10),"C" CHAR(10));
INSERT INTO TEST01 SELECT LEVEL,LEVEL,LEVEL FROM DUAL CONNECT BY LEVEL <= 10000;
INSERT INTO TEST01 SELECT 1,2,LEVEL FROM DUAL CONNECT BY LEVEL <= 10000;
commit;
--创建组合索引
CREATE OR REPLACE INDEX "yxy_A_B_C" ON "TEST01"("A" ASC,"B" ASC,"C" ASC) ;
CREATE OR REPLACE INDEX "yxy_B_A_C" ON "TEST01"("B" ASC,"A" ASC,"C" ASC) ;
--开始测试
select * from TEST01 where B=10 and A=1; --A=1有10001行，B=10有1行
select * from TEST01 where A=10 and B=2; --B=2有10001行，A=10有1行
1.select * from TEST01 where B=10 and A=1;
针对这种情况，因为A=1有10001行，B=10有1行，B的筛选性更好
走yxy_B_A_C索引，先快速定位到B=10的1行数据，在此基础上找出A=1的数据
如果走yxy_A_B_C索引，先通过A定位到10001行数据，在此基础上找出B=10的数据
明显走筛选性更好的列，能更快的定位到数据
2.select * from TEST01 where A=10 and B=2;
B=2有10001行，A=10有1行,A筛选性更好，走yxy_A_B_C索引能更快的定位到数据
真实环境中，因为索引底层数据结构的原因，将筛选性较好的列放前面效率更高
2 为什么不走索引？
真实环境中，创建了索引，为什么查看执行计划却没有走索引？
2.1 最左匹配原则
最左匹配原则是指在使用复合索引时，查询条件从左到右依次匹配索引列的顺序，一旦中间有列未匹配，索引将停止工作或部分失效。
索引最左边的列，必须在条件列中找到，才能走索引;
这是底层B树结构决定的
例如
create table TEST (A INT,B VARCHAR,C VARCHAR);
create index yxy_A on TEST(A,B);
select * from TEST表 where B='yxy';
例子中的索引最左边的列A，在select的条件列中未找到，所以不能走yxy_A索引，这就是最左匹配原则;
但是真实环境中都是基于条件列来选择建索引，最左边的索引列不在条件列中的情况很少遇到;
2.2 条件列有计算或函数
索引存储的是原始数据值，如果查询条件中对索引列应用了函数，索引中找不到这些经过函数处理的值，就没法走索引
例如
create table TEST (A INT,B VARCHAR,C VARCHAR);
create index yxy_A on TEST(A,B);
select * from TEST表 where A+1=5; --条件列有计算
select * from TEST表 where abs(A)=5; --条件列有函数
示例中两中情况都会使索引失效
2.3 存在隐式类型转换
这个也跟上述情况类似
例如
create table TEST (A INT,B VARCHAR,C VARCHAR);
create index yxy_A on TEST(A,B);
select * from TEST表 where A='123456789'; --走索引
select * from TEST表 where A='1234567890'; --不走索引
例子中A是int类型，但是查询时A等于了一个字符串，如果常量大于10，就会使索引失效
注
有些数据库优化器会自动去做一个转换，会自动转换常量小于10的值，也会走索引
2.4 大量回表
如果走索引后有大量的回表操作，优化器也会自动屏蔽索引
例如
create table TEST (ID INT,NAME VARCHAR);
create index yxy_ID on TEST(ID);
select ID,name from TEST表 where ID=5; --99/100，不走索引
select ID,name from TEST表 where ID=10; --1/100，走索引
例子中表共100行，ID=5的数据就有99行，优化器就会自动屏蔽索引，如果ID=10，数据有1行，那么走索引就更合理，回表只用一行;
2.5 统计信息不准确
上面我们提了很多表总行数，回表行数等，但是数据库本身是不知道ID=5和ID=10的数据有多少，这时候就需要利用数据库统计信息;
例如
create table TEST表 (ID INT,NAME VARCHAR);
create index yxy_ID on TEST(ID);
select ID from TEST表 where ID=5; --ID=5，实际有99行
例子中如果没有更新统计信息，统计信息中记录了ID=5的数据只有1行，那么优化器通过计算会认为走索引会更好，但是实际ID=5的有99行，真实情况下走索引就会特别慢，只能更新统计信息解决;
最简单的统计信息就是频率直方图，表中的列数据每个值有多少行都记录在表格中，优化器能通过统计信息去计算执行sql的每个计划代价，选择最小代价的执行计划执行;
（这就是常说的CBO，Cost-Based Optimization，基于代价的优化，后面章节会讲CBO原理，统计信息等）
下一篇：
数据库性能优化(sql优化)_索引详解03_yxy，索引底层结构（B树原理等）
更多其他数据库优化(sql优化)专栏：
数据库性能优化(sql优化)_基本思路_yxy
数据库性能优化(sql优化)_索引详解01_yxy