一：索引的声明及使用
索引是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对性能的影响就愈发重要。索引优化应该是对查询性能优化最有效的手段，创建一个真正最优的索引经常需要重写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 INT PRIMARY KEY AUTO_INCREMENT COMMENT '学生ID', -- 在这个字段创建了主键索引PRIMARY KEY
scard VARCHAR ( 10 ) COMMENT '学号',
sname VARCHAR ( 5 ) COMMENT '姓名',
ssex CHAR(1) COMMENT '性别',
sage TINYINT UNSIGNED COMMENT '年龄',
stel VARCHAR ( 11 ) UNIQUE COMMENT '电话', -- 在这个字段创建了唯一索引（可为空）
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 ASC b 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优化器生成执行计划时间，降低查询性能。
.
标签：COMMENT,--,创建,索引,student,MySQL,NULL From： https://www.cnblogs.com/antLaddie/p/17093577.html