最新要闻

广告

手机

iphone11大小尺寸是多少？苹果iPhone11和iPhone13的区别是什么？

iphone11大小尺寸是多少？苹果iPhone11和iPhone13的区别是什么？

 警方通报辅警执法直播中被撞飞：犯罪嫌疑人已投案

警方通报辅警执法直播中被撞飞：犯罪嫌疑人已投案

家电

  1. 首页
  2. 移动通信
  3. 内容

MySQL索引

来源：博客园


(资料图)

一：索引的声明及使用

索引是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对性能的影响就愈发重要。索引优化应该是对查询性能优化最有效的手段，创建一个真正最优的索引经常需要重写SQL查询语句。要理解MySQL中索引的工作原理，最简单的方法就是去看一看一本书的索引部分：比如你想在一本书中寻找某个主题，一般会先看书的索引目录，找到对应的章节、对应的页码后就可以快速找到你想看的内容。在MySQL中，存储引擎用类似的方法使用索引，其先在索引中查找对应的值，然后根据匹配的索引记录找到对应的数据行，最后将数据结果集返回给客户端。

1：索引的分类

MySQL索引分类：主键索引、唯一索引、普通索引、组合索引、全文索引、空间索引Ⅰ：主键索引一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。Ⅱ：唯一索引使用UNIQUE参数可以设置唯一性索引, 创建唯一性索引后，该列的值必须是唯一的，但允许有空值，在一张数据表里可以有多个唯一索引。Ⅲ：普通索引创建普通索引时，不附带任何条件，只是用于提高查询效率；普通索引可以创建在任何数据类型中，其值是否唯一和非空,要由字段本身的    完整性约束条件决定。Ⅳ：组合索引组合索引是在表的多个字段上组合创建一个索引，该索引指向创建时对应的多个字段，可以通过这几个字段进查询，但是只有查询条件中使    用了这些字段中的第一个字段时才会被使用；组合索引可以分为组合唯一索引和组合普通索引两种Ⅵ：全文索引全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。它能够利用[分词技术]等多种算法智能分析出文本文字中关键词的频率和    重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。全文索引非常适合大型数据集。对于小的数据集，它的用处比较小。    具体参考：https://zhuanlan.zhihu.com/p/35675553Ⅶ：空间索引空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。    MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。    创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建
-- 创建数据库并使用数据库CREATE DATABASE IF NOT EXISTS demo_index;USE demo_index;-- 创建一个student的表CREATE TABLE IF NOT EXISTS student (    sid INT COMMENT "学生ID",    scard VARCHAR ( 10 ) COMMENT "学号",    sname VARCHAR ( 5 ) COMMENT "姓名",    ssex CHAR(1) COMMENT "性别",    sage TINYINT UNSIGNED COMMENT "年龄",    stel VARCHAR ( 11 ) COMMENT "电话") ENGINE = INNODB DEFAULT CHARSET = utf8mb4;-- 说明：上面的表没有任何索引，这是后面慢慢在下文中慢慢完善，其中sid为主键索引，--      scard和sname和ssex为联合唯一索引，sage为普通索引，stel为唯一索引-- 数据添加INSERT INTO student VALUES (1,"2022000001","张三丰","男",25,"18859657745"),(2,"2022000002","周卓浩","男",24,"18859874417"),(3,"2022000003","潘恩依","女",25,"13658778954"),(4,"2022000004","岳列洋","女",24,"15158975568"),(5,"2022000005","钱勤堃","男",24,"15548795584"),(6,"2022000006","李鑫灏","男",25,"13568997458"),(7,"2022000007","易江维","男",24,"17684985598");
基本数据准备（为下面创建索引提供）

2：创建索引（创建表时创建【隐式】）

创建表的时候创建索引（隐式）：隐式创建是MySQL中，我们在创建主键索引、唯一索引、外键约束（其实也是索引）不使用标准的语法，    只是使用它特有的关键字在列后面设置，从而达到设置索引的用意-- 删除表    DROP TABLE student;-- 创建课程表    CREATE TABLE IF NOT EXISTS course (        cid INT PRIMARY KEY AUTO_INCREMENT COMMENT "课程ID",        cname VARCHAR ( 10 ) COMMENT "课程名称"        ) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;-- 创建学生表    CREATE TABLE IF NOT EXISTS student (        sid INTPRIMARY KEY AUTO_INCREMENT COMMENT "学生ID",  -- 在这个字段创建了主键索引PRIMARY KEYscard VARCHAR ( 10 ) COMMENT "学号",        sname VARCHAR ( 5 ) COMMENT "姓名",        ssex CHAR(1) COMMENT "性别",        sage TINYINT UNSIGNED COMMENT "年龄",        stel VARCHAR ( 11 ) UNIQUECOMMENT "电话",          -- 在这个字段创建了唯一索引（可为空）cid INT COMMENT "课程ID",       CONSTRAINT fk_cid_course_cid FOREIGN KEY(cid) REFERENCES course(cid) -- 创建外键（隐式创建一个普通索引）) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;-- 查询索引情况（分成2行展示）    SHOW INDEX FROM student;        +---------+------------+-------------------+--------------+-------------+-----------+-------------+        | Table   | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality |        +---------+------------+-------------------+--------------+-------------+-----------+-------------+        | student |          0 | PRIMARY           |            1 | sid         | A         |           0 |        | student |          0 | stel              |            1 | stel        | A         |           0 |        | student |          1 | fk_cid_course_cid |            1 | cid         | A         |           0 |        +---------+------------+-------------------+--------------+-------------+-----------+-------------+                  ----------+--------+------+------------+---------+---------------+---------+------------+                   Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |                  ----------+--------+------+------------+---------+---------------+---------+------------+                       NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |                       NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |                       NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |                  ----------+--------+------+------------+---------+---------------+---------+------------+     -- 由上面可以看出主键名称为PRIMARY，其它则是自己定义的名称，注意，创建外键后会自带把当前外键字段设置为普通索引字段说明：   Table： 表示创建索引的数据表名   Non_unique：表示该索引是否是唯一索引。若不是唯一索引，则该列的值为1；若是唯一索引，则该列的值为0 Key_name：表示索引的名称（默认为当前索引列的名称，但是主键永远为 PRIMARY）Seq_in_index：表示该列在索引中的位置，如果索引是单列的，则该列的值为1；如果索引是组合索引，则该列的值为每列在索引定义中的顺序    Column_name： 表示定义索引的列字段    Collation：   表示列以何种顺序存储在索引中。在MySQL中，若为"A"(升序)，若为"D"(降序)    Cardinality： 索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数，所以即使对于小型表，该值也没有必要是精确的。                  基数越大，当进行联合时MySQL使用该索引的机会就越大。    Sub_part：    表示列中被编入索引的字符的数量。若列只是部分被编入索引，则该列的值为被编入索引的字符的数目；                  若整列被编入索引，则该列的值为 NULL。    Packed：      指示关键字如何被压缩。若没有被压缩，值为NULL。    Null：        用于显示索引列中是否包含NULL。若列含有NULL，该列的值为YES。若没有，则该列的值为NO或空。    Index_type：  显示索引使用的类型和方法（BTREE、FULLTEXT、HASH、RTREE）。    Comment：     索引的说明。    Visible：     当前索引是否被隐藏（MySQL8.0后才可以设置）

3：创建索引（创建表时创建）

基本语法：   CREATE TABLE 表名 [col_name data_type]    [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]①：UNIQUE、FULLTEXT和SPATIAL为可选参数，分别表示唯一索引、全文索引和空间索引        ②：INDEX与KEY为同义词，两者的作用相同，用来指定创建索引        ③：index_name指定索引的名称，为可选参数，如果不指定，那么MySQL默认col_name为索引名        ④：col_name为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择        ⑤：length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度        ⑥：ASC或DESC指定升序或者降序的索引值存储在建表时创建索引：    -- 删除原先表    DROP TABLE student;    -- 创建表及索引    CREATE TABLE IF NOT EXISTS student (        sid INT AUTO_INCREMENT COMMENT "学生ID",        scard VARCHAR ( 10 ) COMMENT "学号",        sname VARCHAR ( 5 ) COMMENT "姓名",        ssex CHAR(1) COMMENT "性别",        sage TINYINT UNSIGNED COMMENT "年龄",        stel VARCHAR ( 11 ) COMMENT "电话",        PRIMARY KEY aaa(sid),               -- 主键索引（此时这里虽然创建索引名称为aaa，但是会强制为PRIMARY）        UNIQUE KEY uk_stel(stel),           -- 唯一索引（名称为uk_stel，可以存放空值，但是其它值必须唯一）        INDEX idx_ssage(sage),              -- 普通索引（名称为idx_ssage，没有任何限制，加快查询）        UNIQUE KEY mul_uk(scard,sname,ssex) -- 组合索引（scard,sname,ssex三个字段构成组合索引）) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;    -- 查询表索引情况    SHOW INDEX FROM student;        +---------+------------+-----------+--------------+-------------+-----------+-------------+----------+        | Table   | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part |        +---------+------------+-----------+--------------+-------------+-----------+-------------+----------+        | student |          0 | PRIMARY   |            1 | sid         | A         |           0 |     NULL |        | student |          0 | uk_stel   |            1 | stel        | A         |           0 |     NULL |        | student |          0 | mul_uk    |            1 | scard       | A         |           0 |     NULL |        | student |          0 | mul_uk    |            2 | sname       | A         |           0 |     NULL |        | student |          0 | mul_uk    |            3 | ssex        | A         |           0 |     NULL |        | student |          1 | idx_ssage |            1 | sage        | A         |           0 |     NULL |        +---------+------------+-----------+--------------+-------------+-----------+-------------+----------+                --------+------+------------+---------+---------------+---------+------------+                 Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |                --------+------+------------+---------+---------------+---------+------------+                   NULL |      | BTREE      |         |               | YES     | NULL       |  主键索引                   NULL | YES  | BTREE      |         |               | YES     | NULL       |  唯一索引                   NULL | YES  | BTREE      |         |               | YES     | NULL       |  组合索引(序号1)                   NULL | YES  | BTREE      |         |               | YES     | NULL       |  组合索引(序号2)                   NULL | YES  | BTREE      |         |               | YES     | NULL       |  组合索引(序号3)                   NULL | YES  | BTREE      |         |               | YES     | NULL       |  普通索引                --------+------+------------+---------+---------------+---------+------------+注意：组合索引说明：其实按照上面，我们对scard,sname,ssex三个字段创建了组合索引，顺序分别为1，2，3；我们在查询时必须遵循”最左前缀原则“；    就是我们在查询时不管scard(1),sname(2),ssex(3)这三个字段(前面括号为字段索引顺序)出现在WHERE后面的哪里，只要出现    1，2，3 或 1，2 或 1 都可以命中索引；否则这个索引创建后为失效索引    如(可命中索引)：        SELECT * FROM student WHERE ssex = xxx AND sname = xxx AND scard = xxx （顺序无所谓，优化器会处理）        SELECT * FROM student WHERE scard = xxx AND sname = xxx        SELECT * FROM student WHERE scard = xxx    如(不可命中索引)：        SELECT * FROM student WHERE sname = xxx        SELECT * FROM student WHERE scard = xxx AND ssex = xxx

4：创建索引（创建表之后创建）

基本语法：在已经创建的表中创建索引可以使用 ALTER TABLE 语句或 CREATE INDEX 语句；   ①：使用ALTER TABLE语句创建索引 ALTER TABLE 语句创建索引的基本语法如下：        ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]        [index_name] (col_name[length],...) [ASC | DESC]②：使用CREATE INDEX创建索引 CREATE INDEX 语句可以在已经存在的表上添加索引，在MySQL中，CREATE INDEX 被映射到一个ALTER TABLE语句上，基本语法结构为：        CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name        ON table_name (col_name[length],...) [ASC | DESC]具体说明参考创建表时创建索引语法举例：    -- 删除原先表    DROP TABLE student;    -- 创建表    CREATE TABLE IF NOT EXISTS student (        sid INT COMMENT "学生ID",        scard VARCHAR ( 10 ) COMMENT "学号",        sname VARCHAR ( 5 ) COMMENT "姓名",        ssex CHAR(1) COMMENT "性别",        sage TINYINT UNSIGNED COMMENT "年龄",        stel VARCHAR ( 11 ) COMMENT "电话"    ) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;    -- 添加主键索引    ALTER TABLE student ADD PRIMARY KEY(sid);    -- 添加普通索引    ALTER TABLE student ADD INDEX idx_sage(sage);    -- 添加唯一索引    ALTER TABLE student ADD UNIQUE KEY uk_stel(stel);    -- 添加组合索引    ALTER TABLE student ADD UNIQUE KEY mul_uk(scard,sname,ssex);查询当前表索引：SHOW INDEX FROM student;

5：删除索引

基本语法    ①：使用ALTER TABLE删除索引 ALTER TABLE删除索引的基本语法格式如下：        ALTER TABLE table_name DROP INDEX index_name;    ②：使用DROP INDEX语句删除索引 DROP INDEX删除索引的基本语法格式如下：        DROP INDEX index_name ON table_name;示例：    -- 删除主键索引 PRIMARY        ALTER TABLE student DROP PRIMARY KEY;        注：删除主键索引或者唯一索引时，该列是 “AUTO_INCREMENT” 自增，则需要先删除自增然后再删除索引，否则报错：        ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must ...            ALTER TABLE student MODIFY sid INT; -- 修改并保证不能存在AUTO_INCREMENT自增；            然后再执行上面的删除主键！    -- 删除唯一索引        ALTER TABLE student DROP INDEX uk_stel;    -- 删除普通索引        ALTER TABLE student DROP INDEX idx_ssage;    -- 删除组合索引 mul_uk 顺序为2的sname字段        ALTER TABLE student DROP INDEX mul_uk;注：若我们删除了某个带索引的字段时，那么这个索引也会被自动删除；若删除了一个组合索引里的某个字段后，    那个组合索引会变为2个，并且顺序会有所调整    如上面组合索引，我删除sname字段，ALTER TABLE student DROP sname;则会变为顺序1为scard，顺序2为ssex注：索引没有更新之说，索引是通过删除后再添加的说法

二：MySQL8.0索引新特性

1：降序索引

说明：降序索引主要应用在多字段排序下可以达到很好的查询处理效率举例：分别在MySQL5.7版本和MySQL8.0版本中创建数据表ts1：    CREATE TABLE ts1(a int,b int,index idx_a_b(a,b desc));  在MySQL5.7中查询数据表ts1结构：SHOW CREATE TABLE ts1\G        *************************** 1. row ***************************               Table: ts1        Create Table: CREATE TABLE `ts1` (            `a` int(11) DEFAULT NULL,            `b` int(11) DEFAULT NULL,            KEY `idx_a_b` (`a`,`b`)        ) ENGINE=InnoDB DEFAULT CHARSET=utf8   在MySQL8.0中查询数据表ts1结构：SHOW CREATE TABLE ts1\G        *************************** 1. row ***************************               Table: ts1        Create Table: CREATE TABLE `ts1` (            `a` int DEFAULT NULL,            `b` int DEFAULT NULL,            KEY `idx_a_b` (`a`,`b` DESC)        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci   说明：在上面可以看出MySQL8.0设置降序索引是成功的，在MySQL5.7则展示“KEY`idx_a_b` (`a`,`b`)” 默认为ASC；　　 存储方式及索引的存储顺序，准备表和插入无序的表数据：    CREATE TABLE ts2(a int,b int,index idx_a_b(a,b desc));    INSERT INTO ts2 VALUES(2,1),(2,4),(2,3),(2,2),(1,1),(1,3),(1,2),(1,4);    我们知道创建索引后，我们插入无序数据时，会字段变成有序位置，所以下面是MySQL5.7和8.0的区别  MySQL5.7针对降序索引（失效，全部都为ASC排序，不支持降序DESC）        SELECT * FROM ts2;        +------+------+        | a    | b    |        +------+------+        |    1 |    1 |        |    1 |    2 |        |    1 |    3 |        |    1 |    4 |        |    2 |    1 |        |    2 |    2 |        |    2 |    3 |        |    2 |    4 |        +------+------+MySQL8.0针对降序索引（未失效，按照指定的降序还是升序方式排列）        SELECT * FROM ts2;        +------+------+        | a    | b    |        +------+------+        |    1 |    4 |        |    1 |    3 |        |    1 |    2 |        |    1 |    1 |        |    2 |    4 |        |    2 |    3 |        |    2 |    2 |        |    2 |    1 |        +------+------+总结：针对MySQL8.0出现的降序索引主要发生在组合索引的排序上，使排序更快而且可以利用上索引；正常我们创建一个索引总是以ASC从     小到大的顺序；那么我们有个需求对a，b字段创建一个组合索引，后期查询时需要a为正常的ASC，而b为DESC的查询；如果用到了     降序索引，我可以在插入数据时直接进行排序，后期查询通过a ASCb DESC可以快速查询并且用上索引；反之MySQL5.7则这样对     组合索引的查询排序效率会变的慢性能测试：分别在MySQL5.7版本和MySQL8.0版本的数据表ts1中插入800条随机数据，执行语句如下：    -- 在Navicat上执行自定义函数    CREATE PROCEDURE ts_insert ()    BEGIN        DECLARE i INT DEFAULT 1;        WHILE i <= 800            DO                INSERT INTO ts1 SELECT rand()* 80000, rand()* 80000;                SET i = i + 1;            END WHILE;            COMMIT;    END    -- 调用函数（插入数据800条）    CALL ts_insert();    -- 按照组合索引的a,b字段进行查询和排序操作    -- MySQL5.7中查询EXPLAIN SELECT * FROM ts1 ORDER BY a ASC, b DESC LIMIT 5;    +--+-----------+-----+----...+-----+----...+-------+---...+----+----+--------+---------------------------+    |id|select_type|table|part...|type |poss...|key    |key...|ref |rows|filtered|Extra                      |    +--+-----------+-----+----...+-----+----...+-------+---...+----+----+--------+---------------------------+    | 1|SIMPLE     |ts1  |NULL...|index|NULL...|idx_a_b|10 ...|NULL| 800|  100.00|Using index; Using filesort|    +--+-----------+-----+----...+-----+----...+-------+---...+----+----+--------+---------------------------+       -- 可以看出执行计划扫描数为800，而且使用了Using filesort。    -- MySQL8.0中查询EXPLAIN SELECT * FROM ts1 ORDER BY a ASC, b DESC LIMIT 5;    +--+-----------+-----+----------+-----+-------------+-------+-------+----+----+--------+-----------+    |id|select_type|table|partitions|type |possible_keys|key    |key_len|ref |rows|filtered|Extra      |    +--+-----------+-----+----------+-----+-------------+-------+-------+----+----+--------+-----------+    | 1|SIMPLE     |ts1  |NULL      |index|NULL         |idx_a_b|10     |NULL|   5|  100.00|Using index|    +--+-----------+-----+----------+-----+-------------+-------+-------+----+----+--------+-----------+      -- 从结果可以看出，执行计划中扫描数为5，而且没有使用Using filesort。注：Using filesort是MySQL中一种速度比较慢的外部排序。可以通过优化索引来尽量避免出现Using filesort，从而提高数据库执行速度注：降序索引只对查询中特定的排序顺序有效，如果使用不当，反而查询效率更低。    例如：EXPLAIN SELECT * FROM ts1 ORDER BY a DESC,b DESC LIMIT 5;    -- 都使用DESC则看具体效果        -- 此查询MySQL5.7比MySQL8.0要好，具体就是上面的2种方式性能的颠倒

2：隐藏索引

在MySQL 5.7版本及之前，只能通过显式的方式删除索引。如果发现删除索引后出现错误，又只能通过显式创建索引的方式将删除的索引创建回来。如果数据表中的数据量非常大，或者数据表本身比较大，这种操作就会消耗系统过多的资源，操作成本非常高。　　从MySQL 8.x开始支持 隐藏索引（invisible indexes），只需要将待删除的索引设置为隐藏索引，使查询优化器不再使用这个索引（即使使用force index（强制使用索引），优化器也不会使用该索引），确认将索引设置为隐藏索引后系统不受任何响应，就可以彻底删除索引。 这种通过先将索引设置为隐藏索引，再删除索引的方式就是软删除 。

使用方式：就是我们正常创建索引语句后面添加一个 “INVISIBLE” 关键字即可Ⅰ：创建表时直接创建    -- 在MySQL中创建隐藏索引通过SQL语句INVISIBLE来实现，    CREATE TABLE IF NOT EXISTS student2 (        sid INT COMMENT "学生ID",        scard VARCHAR ( 10 ) COMMENT "学号",        sname VARCHAR ( 5 ) COMMENT "姓名",        ssex CHAR(1) COMMENT "性别",        sage TINYINT UNSIGNED COMMENT "年龄",        stel VARCHAR ( 11 ) COMMENT "电话",        UNIQUE KEY uk_stel(stel) INVISIBLE    ) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;    -- 此时通过  SHOW INDEX FROM student2; 语句查看得出 Visible 列为NO，代表索引不可被发现Ⅱ：在已经存在的表上创建（通过ALTER TABLE语法）    ALTER TABLE student2 ADD INDEX idx_sage(sage) INVISIBLE;Ⅲ：在已经存在的表上创建（通过CREATE INDEX语法）    CREATE INDEX mul_uk ON student2(scard,sname,ssex) INVISIBLE;Ⅳ：切换索引为可见状态    ALTER TABLE student2 ALTER INDEX uk_stel VISIBLE;    -- 如果将uk_stel索引名称设置为可见状态后，通过EXPLAIN查看执行计划，SQL用到此索引字段，EXPLAIN会选择此索引进行查询；    -- 当索引被隐藏时，它的内容仍然是和正常索引一样实时更新的。如果一个索引需要长期被隐藏，那么可以将其删除，    -- 因为索引的存在会影响插入、更新和删除的性能；我们一般通过设置隐藏索引的可见性可以查看索引对调优的帮助。

三：索引的设计原则

1：基本数据准备

说明：创建学生表和课程表，其中学生表插入1000000条数据，课程表插入100条数据；用来测试索引性能Ⅰ：创建数据库和数据表    CREATE DATABASE demo_index_test;    USE demo_index_test;    -- 学生表    CREATE TABLE IF NOT EXISTS `student_info` (        `id` INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT "自增主键ID",        `student_id` INT NOT NULL COMMENT "学号",        `name` VARCHAR(20) DEFAULT NULL COMMENT "姓名",        `course_id` INT NOT NULL COMMENT "课程ID",        `class_id` INT(11) DEFAULT NULL COMMENT "班级ID",        `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT "记录创建时间"    ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;    -- 课程表    CREATE TABLE IF NOT EXISTS `course` (        `id` INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT "自增主键ID",        `course_id` INT NOT NULL COMMENT "课程ID",        `course_name` VARCHAR(40) DEFAULT NULL COMMENT "课程名称"    ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;Ⅱ：创建模拟数据必须的存储函数（Navicat执行）    -- 创建随机产生字符串函数（该函数会返回一个字符串）        CREATE FUNCTION rand_string ( n INT ) RETURNS VARCHAR ( 255 )        BEGIN            DECLARE chars_str VARCHAR ( 100 ) DEFAULT "abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ";            DECLARE return_str VARCHAR ( 255 ) DEFAULT "";            DECLARE i INT DEFAULT 0;            WHILE i < n DO                SET return_str = CONCAT( return_str, SUBSTRING( chars_str, FLOOR( 1+RAND ()* 52 ), 1 ));                SET i = i + 1;            END WHILE;            RETURN return_str;        END    -- 创建随机数函数        CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)        BEGIN            DECLARE i INT DEFAULT 0;            SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ;            RETURN i;        END    注：若创建函数时报错 This function has none of DETERMINISTIC......        由于开启过慢查询日志bin-log, 我们就必须为我们的function指定一个参数。        查看mysql是否允许创建函数：            SHOW VARIABLES LIKE "log_bin_trust_function_creators";        命令开启：允许创建函数设置（不加global只是当前窗口有效）：            SET GLOBAL log_bin_trust_function_creators=1;        mysqld重启，上述参数又会消失。永久方法：            在Windows下：my.ini[mysqld]加上：log_bin_trust_function_creators=1            在Linux下：/etc/my.cnf下my.cnf[mysqld]加上：log_bin_trust_function_creators=1Ⅲ：创建插入模拟数据的存储过程    -- 存储过程1：创建插入课程表存储过程        CREATE PROCEDURE insert_course( max_num INT )        BEGIN            DECLARE i INT DEFAULT 0;            SET autocommit = 0; # 设置手动提交事务            REPEAT # 循环                SET i = i + 1; # 赋值                INSERT INTO course (course_id, course_name ) VALUES (rand_num(10000,10100),rand_string(6));                UNTIL i = max_num            END REPEAT;            COMMIT; # 提交事务        END    -- 存储过程2：创建插入学生信息表存储过程        CREATE PROCEDURE insert_stu( max_num INT )        BEGIN            DECLARE i INT DEFAULT 0;            SET autocommit = 0; # 设置手动提交事务            REPEAT # 循环                SET i = i + 1; # 赋值                INSERT INTO student_info (course_id, class_id ,student_id ,NAME ) VALUES                (rand_num(10000,10100),rand_num(10000,10200),rand_num(1,200000),rand_string(6));                UNTIL i = max_num            END REPEAT;            COMMIT; # 提交事务        ENDⅣ：调用存储过程插入数据    CALL insert_course(100); -- 课程表中插入一百条数据    CALL insert_stu(1000000); -- 学生表中插入一百万条数据
准备基本的数据一百万条数据（数据库，数据表建立）

2：哪些情况适合创建索引

1：字段的数值有唯一的限制业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba）    说明：不要以为唯一索引影响了 INSERT 速度，这个速度损耗可以忽略，但提高查找速度是明显的。2：频繁作为 WHERE查询条件的字段某个字段在SELECT语句的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。尤其是在数据量大的情况下，    创建普通索引就可以大幅提升数据查询的效率。3：经常 GROUP BY 和 ORDER BY的列索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的    时候，就需要对分组或者排序的字段进行索引。如果待排序的列有多个，那么可以在这些列上建立组合索引 。4：UPDATE、DELETE 的 WHERE条件列对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就能大幅提升效率。    原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或删除。如果进行更新的时候，更新的字段是    非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。5：DISTINCT字段需要创建索引经常对某个字段进行去重，使用 DISTINCT，那么对这个字段创建索引，也会提升查询效率。    比如：我们查看学生表中，有多少个学生进行了选课（因为学生表中可能一个学生出现多条选课记录，所以要去重）执行SQL语句：        SELECT DISTINCT(student_id) FROM student_info;        -- 在没有对 student_id 字段添加索引时执行耗时 6 秒        -- 添加索引        ALTER TABLE student_info ADD INDEX idx_stuid(student_id);        -- 再次执行去重查询会发现查询速度挺快的6：多表JOIN连接操作时，创建索引注意事项①：连接表的数量尽量不要超过3张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率    ②：对WHERE条件创建索引，因为WHERE才是对数据条件的过滤。如果在数据量非常大的情况下，没有WHERE条件过滤是非常可怕的    ③：对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致7：使用列类型小的创建索引在创建索引的字段里能用INT类型的就尽量别用BIGINT等等类似的类型8：区分度高(散列性高)的列适合作为索引列的基数指的是某一列中不重复数据的个数，比方说某个列包含值2、5、8、2、5、8、2、5、8虽然有9条记录，但该列的基数却是3。    也就是说，在记录行数一定的情况下，列的基数越大，该列中的值越分散，列的基数越小，该列中的值越集中。这个列的基数指标非常重要，    直接影响我们是否能有效的利用索引。最好为基数大的列建立索引，为基数太小的列建立索引效果可能不好。    可以使用公式 "SELECT COUNT(DISTINCT 列名 ) / COUNT(*) FROM表名" 计算区分度,越接近1越好,一般超过33%就算是比较高效的索引了。    比如一张表一百万条数据，在性别字段计算区分度，因为字段就男和女，所以基数就2；9：使用字符串前缀创建索引创建一张商户表，因为地址字段比较长，在地址字段上建立前缀索引        CREATE TABLE shop(address VARCHAR(120) NOT NULL);        ALTER TABLE shop ADD INDEX(address(12));    主要说，应该使用字符串前缀多少来创建索引呢？上面用到的前缀是12，那么到底截取多少呢？截取得多了，达不到节省索引存储空间的目的；    截取得少了，重复内容太多，字段的散列度(选择性)会降低。怎么计算不同的长度的选择性呢？   先看一下字段在全部数据中的选择度：        SELECT COUNT(DISTINCT address) / COUNT(*) FROM shop;    再通过不同长度去计算，与全表的选择性对比：        COUNT(DISTINCT LEFT(列名, 索引长度))/COUNT(*)例如：        select count(distinct left(address,10)) / count(*) as sub10, -- 截取前10个字符的选择度        count(distinct left(address,15)) / count(*) as sub11, -- 截取前15个字符的选择度        count(distinct left(address,20)) / count(*) as sub12, -- 截取前20个字符的选择度        count(distinct left(address,25)) / count(*) as sub13 -- 截取前25个字符的选择度        from shop;    注意：索引列前缀对排序的影响Alibaba《Java开发手册》        【 强制 】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。        说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会 高达90% 以上 ，可以使用        count(distinct left(列名, 索引长度))/count(*)的区分度来确定。10：使用最频繁的列放到联合索引的左侧这样也可以较少的建立一些索引。同时，由于"最左前缀原则"，可以增加联合索引的使用率。11：在多个字段都要创建索引的情况下，联合索引优于单值索引

3：哪些情况不适合创建索引

1：在WHERE中使用不到的字段，不要设置索引2：数据量小的表最好不要使用索引因为数据量小的情况下，索引都发挥不出什么用处，而且索引的创建还占用磁盘空间，后期更新这个索引字段，还得去维护索引    所以说，一般数据在1000行以内的就不必创建索引3：有大量重复的列上不要建立索引在条件表达式中经常用到的不同值较多的列上建立索引，但字段中如果有大量重复数据，也不用创建索引。比如学生表的“性别”字段上只    有“男”与“女”两个不同值，因此无须建立索引。如果建立索引，不但不会提高查询效率，反而会严重降低数据史新速度。4：避免对经常更新的表或者字段创建过多的索引第一层含义：频繁更新的字段不一定要创建索引。因为更新数据时，也需要更新索引，更新索引会造成负担，从而影响效率    第二层含义：避免对经常更新的表创建过多的索引，并且索引中的列尽可能少。虽然提高了查询速度，同时却会降低更新表的速度5：不建议用无序的值作为索引因为创建索引是要对当前字段进行B+树的创建，若不是有序的，那么每次插入数据时经常出现页分裂等情况6：删除不再使用或者很少使用的索引7：不要定义冗余或重复的索引①：冗余索引如下：        CREATE TABLE IF NOT EXISTS student (            sid INT COMMENT "学生ID",            scard VARCHAR ( 10 ) COMMENT "学号",            sname VARCHAR ( 5 ) COMMENT "姓名",            ssex CHAR(1) COMMENT "性别",            sage TINYINT UNSIGNED COMMENT "年龄",            stel VARCHAR ( 11 ) COMMENT "电话",            UNIQUE KEY mul_uk(scard,sname,ssex),            UNIQUE KEY uk_scard(scard)        ) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;    从上面可知mul_uk索引就对scard列有着快速搜索功能，若再针对scard创建一个索引就是一个冗余索引，还要维护这个索引增删改成本    ②：重复索引如下：        CREATE TABLE repeat_index_demo (            col1 INT PRIMARY KEY,            col2 INT,            UNIQUE uk_idx_c1 (col1),            INDEX idx_c1 (col1)        );    col1既是主键、又给它定义为一个唯一索引，还给它定义了一个普通索引，可是主键本身就会生成聚簇索引，    所以定义的唯一索引和普通索引是重复的，这种情况要避免。

4：限制索引的数目

在实际工作中，我们也需要注意平衡，索引的数目不是越多越好。我们需要限制每张表上的索引数量，建议单张表索引数量不超过6个。原因:①：每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。    ②：索引会影响INSERT、DELETE、UPDATE等语句的性能，因为表中的数据更改的同时，索引也会进行调整和更新，会造成负担。    ③：优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出-个最好的执行计划，如果        同时有很多个索引都可以用于查询，会增加MySQL优化器生成执行计划时间，降低查询性能。

.

关键词：