一:索引的声明及使用
索引是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键,尤其是当表中的数据量越来越大时,索引对性能的影响就愈发重要。索引优化应该是对查询性能优化最有效的手段,创建一个真正最优的索引经常需要重写SQL查询语句。要理解MySQL中索引的工作原理,最简单的方法就是去看一看一本书的索引部分:比如你想在一本书中寻找某个主题,一般会先看书的索引目录,找到对应的章节、对应的页码后就可以快速找到你想看的内容。在MySQL中,存储引擎用类似的方法使用索引,其先在索引中查找对应的值,然后根据匹配的索引记录找到对应的数据行,最后将数据结果集返回给客户端。
1:索引的分类
MySQL索引分类: 主键索引、唯一索引、普通索引、组合索引、全文索引、空间索引 Ⅰ:主键索引 一种特殊的唯一索引,一个表只能有一个主键,不允许有空值。一般是在建表的时候同时创建主键索引。 Ⅱ:唯一索引 使用UNIQUE参数可以设置唯一性索引, 创建唯一性索引后,该列的值必须是唯一的,但允许有空值,在一张数据表里可以有多个唯一索引。 Ⅲ:普通索引 创建普通索引时,不附带任何条件,只是用于提高查询效率;普通索引可以创建在任何数据类型中,其值是否唯一和非空,要由字段本身的 完整性约束条件决定。 Ⅳ:组合索引 组合索引是在表的多个字段上组合创建一个索引,该索引指向创建时对应的多个字段,可以通过这几个字段进查询,但是只有查询条件中使 用了这些字段中的第一个字段时才会被使用;组合索引可以分为组合唯一索引和组合普通索引两种 Ⅵ:全文索引 全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。它能够利用[分词技术]等多种算法智能分析出文本文字中关键词的频率和 重要性,然后按照一定的算法规则智能地筛选出我们想要的搜索结果。全文索引非常适合大型数据集。对于小的数据集,它的用处比较小。 具体参考:https://zhuanlan.zhihu.com/p/35675553 Ⅶ:空间索引 空间索引是对空间数据类型的字段建立的索引,MYSQL中的空间数据类型有4种,分别是GEOMETRY、POINT、LINESTRING、POLYGON。 MYSQL使用SPATIAL关键字进行扩展,使得能够用于创建正规索引类型的语法创建空间索引。 创建空间索引的列,必须将其声明为NOT NULL,空间索引只能在存储引擎为MYISAM的表中创建
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
-- 创建数据库并使用数据库 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:基本数据准备
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
说明:创建学生表和课程表,其中学生表插入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