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

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

　　什么是索引：

　　　　索引的定义：

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

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

　　　　索引的原理：　　　　

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

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

　　　　　　索引带来的开销：

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

　　　　　　　　　　.frm：表示文件结构

　　　　　　　　　　.myd：表示数据

　　　　　　　　　　.myi：表示索引的文件

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

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

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

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

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

　　　　　　　　该字段变化不会太频繁。

　　　　总结：

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

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

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

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

　　索引的分类：

　　　　普通索引：

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

--创建数据库并添加索引
CREATE TABLE test4(
id TINYINT UNSIGNED,
username VARCHAR(20),
INDEX in_id(id),
KEY in_username(username)
);
INSERT INTO test4 VALUES (1,'aa'),
(2,'bb'),
(3,'cc');

--删除索引
DROP INDEX in_id ON test4;
DROP INDEX in_username ON test4;

--添加索引
CREATE INDEX in_id ON test4(id);
ALTER TABLE test4 ADD INDEX in_username(username);

--校验索引是否被引用
EXPLAIN SELECT * FROM test4 WHERE id>0\G
EXPLAIN SELECT id FROM test4 \G

--通过SHOW PROFILE分析SQL
--开启新的窗口，并指定数据库
SELECT @@have_profiling;

SET PROFILING =1;

SELECT COUNT(*) FROM test4;
SHOW PROFILES;
SHOW PROFILE FOR query 1;

　　　　唯一索引：

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

　　　　　　注意：

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

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

--创建数据库并添加索引
CREATE TABLE test5(
    id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20) NOT NULL UNIQUE,
    card CHAR(18) NOT NULL,
    UNIQUE KEY uni_card(card)
    );
INSERT INTO test5 VALUES (1,'aa','147'),
(2,'bb','258'),
(3,'cc','369');

--删除索引
ALTER TABLE test5 DROP INDEX uni_card;
DROP INDEX username ON test5;

--添加索引
CREATE UNIQUE INDEX uni_username ON test5(username);
ALTER TABLE test5 ADD UNIQUE INDEX uni_card(card);


--校验索引是否被引用
EXPLAIN SELECT * FROM test5 WHERE id>0\G
EXPLAIN SELECT id FROM test5 \G

--通过SHOW PROFILE分析SQL
--开启新的窗口，并指定数据库
SELECT @@have_profiling;

SET PROFILING =1;

SELECT COUNT(*) FROM test5;
SHOW PROFILES;
SHOW PROFILE FOR query 1;

　　　　全文索引：

　　　　　　全文索引主要关联在数据类型为CHAR，VARCHAR和TEXT的字段上，以便能够更快加速地查询数据量较大地字符串类型地字段。

　　　　　　MySQL从3.23.23版本开始支持全文索引，只能在存储引擎为MyISAM地数据库表上创建全文索引。在默认情况下，全文索引地搜索执行方式为不区分大小写，

　　　　　如果全文索引所关联地字段为二进制数据类型，则以区分大小写地搜索方式执行。

　　　　　　FULLTEXT索引，用于全文索引。只有MyISAM表类型支持FULLTEST索引。FULLTEXT索引只可以从CHAR，VARCHAR和TEXT列中创建。整个列都会被编入索引，不支持对部分列编入索引。

--创建数据库并添加索引
CREATE TABLE test6(
    id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20) NOT NULL UNIQUE,
    userDesc VARCHAR(20) NOT NULL,
    FULLTEXT INDEX full_userDesc(userDesc)
    )ENGINE=MyISAM;
INSERT INTO test6 VALUES (1,'aa','147258'),
(2,'bb','258147'),
(3,'cc','369147');

--删除索引
DROP INDEX full_userDesc ON test6;

--添加索引
CREATE FULLTEXT INDEX full_userDesc ON test6(userDesc);


--校验索引是否被引用
EXPLAIN
    SELECT * FROM test6
        WHERE MATCH (userDesc) AGAINST ('258147')\G

--通过SHOW PROFILE分析SQL
--开启新的窗口，并指定数据库
SELECT @@have_profiling;

SET PROFILING =1;

SELECT COUNT(*) FROM test6;
SHOW PROFILES;
SHOW PROFILE FOR query 1;

　　　　单列索引：

-- 创建单列索引
CREATE TABLE test7(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
test1 VARCHAR(20) NOT NULL,
test2 VARCHAR(20) NOT NULL,
test3 VARCHAR(20) NOT NULL,
test4 VARCHAR(20) NOT NULL,
INDEX in_test1(test1)
);

　　　　多列索引

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

-- 创建多列索引
CREATE TABLE test8(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
test1 VARCHAR(20) NOT NULL,
test2 VARCHAR(20) NOT NULL,
test3 VARCHAR(20) NOT NULL,
test4 VARCHAR(20) NOT NULL,
INDEX mul_t1_t2_t3(test1,test2,test3)
);

ALTER TABLE test8 DROP INDEX mul_t1_t2_t3;
ALTER TABLE test8 ADD INDEX mul_ti_t2_t3(test1,test2,test3);

--创建唯一性的多列索引
CREATE TABLE test9(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
test1 VARCHAR(20) NOT NULL,
test2 VARCHAR(20) NOT NULL,
test3 VARCHAR(20) NOT NULL,
test4 VARCHAR(20) NOT NULL,
UNIQUE KEY mul_t1_t2_t3(test1,test2,test3)
);

　　　　空间索引

-- 创建空间索引
CREATE TABLE test10(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
test GEOMETRY NOT NULL,
SPATIAL INDEX spa_test(test)
)ENGINE=MyISAM;

DROP INDEX spa_test ON test10;

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