知识点十一:索引的使用(51)
什么是索引:
索引的定义:
在关系型数据库中,索引是一种与表有关的数据库结构,它可以使对应于表的SQL语句执行的更快。索引的作用相当于图书的目录,可以
根据目录中的页码快速找到所需要的内容。在关系型数据库中,索引是一种与表有关的数据库结构,它可以使对应于表的SQL语句执行得更快。
索引的原理:
二叉树 --> 索引文件:效率:log2N
例如:检测10次:2的10次方。1024条记录
索引带来的开销:
查看数据文件(安装目录下的data目录),会发现有三个文件(ISAM引擎下):
.frm:表示文件结构
.myd:表示数据
.myi:表示索引的文件
索引带来的问题:会导致insert,uodate,delete的效率降低。
更新非常频繁的字段不适合创建索引。
唯一性比较差的字段不适合创建索引。比如人的性别只有男和女。
满足以下条件的字段才会创建索引:
肯定在WHERE条件中经常使用到的。
该字段变化不会太频繁。
总结:
索引由数据库中一列或多列组合而成,其作用是提高对表中数据的查询速度
索引的优点是可以提高检索数据的速度
索引的缺点是创建和维护索引需要耗费时间
索引可以提高查询速度,会减慢写入速度
索引的分类:
普通索引:
所谓普通索引,就是在创建索引时,不附加任何限制条件(唯一,非空等限制)。该类型的索引可以创建在任何数据类型的字段上。
1 --创建数据库并添加索引
2 CREATE TABLEtest4(3 id TINYINTUNSIGNED,4 username VARCHAR(20),5 INDEXin_id(id),6 KEYin_username(username)7 );8 INSERT INTO test4 VALUES (1,'aa'),9 (2,'bb'),10 (3,'cc');11
12 --删除索引
13 DROP INDEX in_id ONtest4;14 DROP INDEX in_username ONtest4;15
16 --添加索引
17 CREATE INDEX in_id ONtest4(id);18 ALTER TABLE test4 ADD INDEXin_username(username);19
20 --校验索引是否被引用
21 EXPLAIN SELECT * FROM test4 WHERE id>0\G22 EXPLAIN SELECT id FROMtest4 \G23
24 --通过SHOW PROFILE分析SQL
25 --开启新的窗口,并指定数据库
26 SELECT @@have_profiling;27
28 SET PROFILING =1;29
30 SELECT COUNT(*) FROMtest4;31 SHOW PROFILES;32 SHOW PROFILE FOR query 1;
普通索引测试
唯一索引:
所谓唯一索引,就是在创建索引时,限制索引的值必须唯一。通过该类型索引,可以更快速地查询某条语句。
注意:
唯一的索引所在的列可以重复有NULL值;
唯一的索引所在的列不能为空字符串。
1 --创建数据库并添加索引
2 CREATE TABLEtest5(3 id TINYINT UNSIGNED AUTO_INCREMENT KEY,4 username VARCHAR(20) NOT NULL UNIQUE,5 card CHAR(18) NOT NULL,6 UNIQUE KEYuni_card(card)7 );8 INSERT INTO test5 VALUES (1,'aa','147'),9 (2,'bb','258'),10 (3,'cc','369');11
12 --删除索引
13 ALTER TABLE test5 DROP INDEXuni_card;14 DROP INDEX username ONtest5;15
16 --添加索引
17 CREATE UNIQUE INDEX uni_username ONtest5(username);18 ALTER TABLE test5 ADD UNIQUE INDEXuni_card(card);19
20
21 --校验索引是否被引用
22 EXPLAIN SELECT * FROM test5 WHERE id>0\G23 EXPLAIN SELECT id FROMtest5 \G24
25 --通过SHOW PROFILE分析SQL
26 --开启新的窗口,并指定数据库
27 SELECT @@have_profiling;28
29 SET PROFILING =1;30
31 SELECT COUNT(*) FROMtest5;32 SHOW PROFILES;33 SHOW PROFILE FOR query 1;
唯一索引测试
全文索引:
全文索引主要关联在数据类型为CHAR,VARCHAR和TEXT的字段上,以便能够更快加速地查询数据量较大地字符串类型地字段。
MySQL从3.23.23版本开始支持全文索引,只能在存储引擎为MyISAM地数据库表上创建全文索引。在默认情况下,全文索引地搜索执行方式为不区分大小写,
如果全文索引所关联地字段为二进制数据类型,则以区分大小写地搜索方式执行。
FULLTEXT索引,用于全文索引。只有MyISAM表类型支持FULLTEST索引。FULLTEXT索引只可以从CHAR,VARCHAR和TEXT列中创建。整个列都会被编入索引,不支持对部分列编入索引。
1 --创建数据库并添加索引
2 CREATE TABLEtest6(3 id TINYINT UNSIGNED AUTO_INCREMENT KEY,4 username VARCHAR(20) NOT NULL UNIQUE,5 userDesc VARCHAR(20) NOT NULL,6 FULLTEXT INDEXfull_userDesc(userDesc)7 )ENGINE=MyISAM;8 INSERT INTO test6 VALUES (1,'aa','147258'),9 (2,'bb','258147'),10 (3,'cc','369147');11
12 --删除索引
13 DROP INDEX full_userDesc ONtest6;14
15 --添加索引
16 CREATE FULLTEXT INDEX full_userDesc ONtest6(userDesc);17
18
19 --校验索引是否被引用
20 EXPLAIN21 SELECT * FROMtest622 WHERE MATCH (userDesc) AGAINST ('258147')\G23
24 --通过SHOW PROFILE分析SQL
25 --开启新的窗口,并指定数据库
26 SELECT @@have_profiling;27
28 SET PROFILING =1;29
30 SELECT COUNT(*) FROMtest6;31 SHOW PROFILES;32 SHOW PROFILE FOR query 1;
全文索引测试
单列索引:
1 --创建单列索引
2 CREATE TABLEtest7(3 id TINYINT UNSIGNED AUTO_INCREMENT KEY,4 test1 VARCHAR(20) NOT NULL,5 test2 VARCHAR(20) NOT NULL,6 test3 VARCHAR(20) NOT NULL,7 test4 VARCHAR(20) NOT NULL,8 INDEXin_test1(test1)9 );
单列索引测试
多列索引
所谓多列索引,是指在创建索引时,所关联的字段不是一个字段,而是多个字段。虽然可以通过过关联的字段进行查询,但是只有查询条件中使用了关联字段中的第一个字段,多列索引才会被启用。
1 --创建多列索引
2 CREATE TABLEtest8(3 id TINYINT UNSIGNED AUTO_INCREMENT KEY,4 test1 VARCHAR(20) NOT NULL,5 test2 VARCHAR(20) NOT NULL,6 test3 VARCHAR(20) NOT NULL,7 test4 VARCHAR(20) NOT NULL,8 INDEXmul_t1_t2_t3(test1,test2,test3)9 );10
11 ALTER TABLE test8 DROP INDEXmul_t1_t2_t3;12 ALTER TABLE test8 ADD INDEXmul_ti_t2_t3(test1,test2,test3);13
14 --创建唯一性的多列索引
15 CREATE TABLEtest9(16 id TINYINT UNSIGNED AUTO_INCREMENT KEY,17 test1 VARCHAR(20) NOT NULL,18 test2 VARCHAR(20) NOT NULL,19 test3 VARCHAR(20) NOT NULL,20 test4 VARCHAR(20) NOT NULL,21 UNIQUE KEYmul_t1_t2_t3(test1,test2,test3)22 );
多列索引测试
空间索引
1 --创建空间索引
2 CREATE TABLEtest10(3 id TINYINT UNSIGNED AUTO_INCREMENT KEY,4 test GEOMETRY NOT NULL,5 SPATIAL INDEXspa_test(test)6 )ENGINE=MyISAM;7
8 DROP INDEX spa_test ONtest10;9
10 CREATE SPATIAL INDEX spa_test ON test10(test);
空间索引测试
如何创建索引:
(1)创建表的时候创建索引:
CREATE TABLEtbl_name(
字段名称 字段类型[完整性约束条件],
…,[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY [索引名称] (字段名称[(字段长度)]
[ASC | DESC]);
(2)在已经存在的表上创建索引:
CREATE [UNIQUE | FULLTEXT |SPATIAL] INDEX 索引名称 ON表名 {字段名称][(字段长度)] [ASC | DESC]};ALTER TABLE tbl_name ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX索引名称(字段名称[字段名称[(字段长度)] [ASC | DESC];
如何删除索引:
DROP INDEX 索引名称 ON tbl_name;
如何查询索引:
SHOW KEYS FROM表名;
SHOW INDEXESFROM 表明;
查看索引是否被调用:
EXPLAINSELECT * FROM til_name WHERE '一个存在的条件'\G;
通过EXPLAIN分析索引是否被启用。
通过SHOW PROFILE分析SQL执行低效的SQL语句。
1.首先查看MySQL是否支持SHOW PROFILE:
SELECT @@HAVE_PROFILING;
2,如果PROFILE时关闭的,可以通过SET语句在session级别开启PROFILEING
SET PROFILING=1;
3.执行完毕之后,可以通过SHOW PROFILES语句,查看当前SQL的Query_ID。
SHOW PROFILES;
4.通过SHOW PROFILES FOR QUERY query_ID查看执行语句各处用时;
索引的常用应用案例:
MySQL在以下操作场景下会使用索引:
1.快速查找符合WHERE条件的记录;
2.快速确定候选集。若WHERE条件使用了多个索引字段,则MySQL会优先使用能使候选记录集规模最小的那个索引,以便尽快淘汰不符合条件的记录。
3.如果表中存在几个字段构成的联合索引,则查找记录时,这个联合索引的最左前缀匹配字段也会被自动作为索引来加速查找。
例如:若为某表创建3个字段(c1,c2,c3)构成的联合索引,则(c1),(c1,c2),(c1,c2,c3)均会作为索引,(c1,c3)就不会作为索引,而(c1,c3)其实只利用到(c1)索引。
4.多表做join操作时会使用索引(如果参与join字段在这些表中均建立了索引的话)
5.若某字段已建立索引,求该字段的MIN()或MAX()时,MySQL会使用索引
6.对建立了索引的字段做SORT或GROUP操作时,MySQL会使用索引