知识点十一：索引的使用(51)

　　什么是索引：

　　　　索引的定义：

　　　　　　在关系型数据库中，索引是一种与表有关的数据库结构，它可以使对应于表的SQL语句执行的更快。索引的作用相当于图书的目录，可以

　　　　　根据目录中的页码快速找到所需要的内容。在关系型数据库中，索引是一种与表有关的数据库结构，它可以使对应于表的SQL语句执行得更快。

　　　　索引的原理：　　　　

　　　　　　二叉树 --> 索引文件：效率：log2N

　　　　　　　　例如：检测10次：2的10次方。1024条记录

　　　　　　索引带来的开销：

　　　　　　　　查看数据文件（安装目录下的data目录），会发现有三个文件（ISAM引擎下）：

　　　　　　　　　　.frm：表示文件结构

　　　　　　　　　　.myd：表示数据

　　　　　　　　　　.myi：表示索引的文件

　　　　　　索引带来的问题：会导致insert,uodate，delete的效率降低。

　　　　　　　　更新非常频繁的字段不适合创建索引。

　　　　　　　　唯一性比较差的字段不适合创建索引。比如人的性别只有男和女。

　　　　　　满足以下条件的字段才会创建索引：

　　　　　　　　肯定在WHERE条件中经常使用到的。

　　　　　　　　该字段变化不会太频繁。

　　　　总结：

　　　　　　索引由数据库中一列或多列组合而成，其作用是提高对表中数据的查询速度

　　　　　　索引的优点是可以提高检索数据的速度

　　　　　　索引的缺点是创建和维护索引需要耗费时间

　　　　　　索引可以提高查询速度，会减慢写入速度

　　索引的分类：

　　　　普通索引：

　　　　　　所谓普通索引，就是在创建索引时，不附加任何限制条件（唯一，非空等限制）。该类型的索引可以创建在任何数据类型的字段上。

1 --创建数据库并添加索引 2 CREATE TABLE test4( 3 id TINYINT UNSIGNED, 4 username VARCHAR(20), 5 INDEX in_id(id), 6 KEY in_username(username) 7 ); 8 INSERT INTO test4 VALUES (1,'aa'), 9 (2,'bb'),10 (3,'cc');11 12 --删除索引13 DROP INDEX in_id ON test4;14 DROP INDEX in_username ON test4;15 16 --添加索引17 CREATE INDEX in_id ON test4(id);18 ALTER TABLE test4 ADD INDEX in_username(username);19 20 --校验索引是否被引用21 EXPLAIN SELECT * FROM test4 WHERE id>0\G22 EXPLAIN SELECT id FROM test4 \G23 24 --通过SHOW PROFILE分析SQL25 --开启新的窗口，并指定数据库26 SELECT @@have_profiling;27 28 SET PROFILING =1;29 30 SELECT COUNT(*) FROM test4;31 SHOW PROFILES;32 SHOW PROFILE FOR query 1;

　　　　唯一索引：

　　　　　　所谓唯一索引，就是在创建索引时，限制索引的值必须唯一。通过该类型索引，可以更快速地查询某条语句。

　　　　　　注意：

　　　　　　　　唯一的索引所在的列可以重复有NULL值；

　　　　　　　　唯一的索引所在的列不能为空字符串。

1 --创建数据库并添加索引 2 CREATE TABLE test5( 3     id TINYINT UNSIGNED AUTO_INCREMENT KEY, 4     username VARCHAR(20) NOT NULL UNIQUE, 5     card CHAR(18) NOT NULL, 6     UNIQUE KEY uni_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 INDEX uni_card;14 DROP INDEX username ON test5;15 16 --添加索引17 CREATE UNIQUE INDEX uni_username ON test5(username);18 ALTER TABLE test5 ADD UNIQUE INDEX uni_card(card);19 20 21 --校验索引是否被引用22 EXPLAIN SELECT * FROM test5 WHERE id>0\G23 EXPLAIN SELECT id FROM test5 \G24 25 --通过SHOW PROFILE分析SQL26 --开启新的窗口，并指定数据库27 SELECT @@have_profiling;28 29 SET PROFILING =1;30 31 SELECT COUNT(*) FROM test5;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 TABLE test6( 3     id TINYINT UNSIGNED AUTO_INCREMENT KEY, 4     username VARCHAR(20) NOT NULL UNIQUE, 5     userDesc VARCHAR(20) NOT NULL, 6     FULLTEXT INDEX full_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 ON test6;14 15 --添加索引16 CREATE FULLTEXT INDEX full_userDesc ON test6(userDesc);17 18 19 --校验索引是否被引用20 EXPLAIN21     SELECT * FROM test622         WHERE MATCH (userDesc) AGAINST ('258147')\G23 24 --通过SHOW PROFILE分析SQL25 --开启新的窗口，并指定数据库26 SELECT @@have_profiling;27 28 SET PROFILING =1;29 30 SELECT COUNT(*) FROM test6;31 SHOW PROFILES;32 SHOW PROFILE FOR query 1;

　　　　单列索引：

1 -- 创建单列索引2 CREATE TABLE test7(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 INDEX in_test1(test1)9 );

　　　　多列索引

　　　　　　所谓多列索引，是指在创建索引时，所关联的字段不是一个字段，而是多个字段。虽然可以通过过关联的字段进行查询，但是只有查询条件中使用了关联字段中的第一个字段，多列索引才会被启用。

1 -- 创建多列索引 2 CREATE TABLE test8( 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 INDEX mul_t1_t2_t3(test1,test2,test3) 9 );10 11 ALTER TABLE test8 DROP INDEX mul_t1_t2_t3;12 ALTER TABLE test8 ADD INDEX mul_ti_t2_t3(test1,test2,test3);13 14 --创建唯一性的多列索引15 CREATE TABLE test9(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 KEY mul_t1_t2_t3(test1,test2,test3)22 );

　　　　空间索引

1 -- 创建空间索引 2 CREATE TABLE test10( 3 id TINYINT UNSIGNED AUTO_INCREMENT KEY, 4 test GEOMETRY NOT NULL, 5 SPATIAL INDEX spa_test(test) 6 )ENGINE=MyISAM; 7  8 DROP INDEX spa_test ON test10; 9 10 CREATE SPATIAL INDEX spa_test ON test10(test);

　　如何创建索引：

　　　　（1）创建表的时候创建索引：

　　CREATE TABLE tbl_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 INDEXES FROM 表明;

　　　　　　查看索引是否被调用： 

　　　　　　EXPLAIN 　　　　　　　　SELECT * 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会使用索引