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 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优化器生成执行计划时间，降低查询性能。
.
作者：蚂蚁小哥
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须在文章页面给出原文链接，否则保留追究法律责任的权利。
