mysql索引的使用视频_MYSQL初级学习笔记九：MySQL索引的使用！（视频序号：初级_51)...

知识点十一：索引的使用(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会使用索引