文章目录 1. 索引的声明与使用1.1 索引的分类1.2 创建索引1、创建表的时候创建索引2、在已经存在的表上创建索引 1.3 删除索引 2. MySQL 8.0 索引新特性2.1
Mysql 的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。
功能逻辑 按照上说，索引主要有 4 种：普通索引、唯一索引、主键索引、全文索引。物理实现方式 ，索引可以分为 2 种：聚簇索引和非聚簇索引。作用字段个数 进行划分，分成单列索引和联合索引。
小结：不同的存储引擎支持的索引类型也不一样
InnoDB：支持 B-tree、Full-text 等索引，不支持 Hash 索引；MyISAM：支持 B-tree、Full-text 等索引，不支持 Hash 索引；Memory：支持 B-tree、Hash 等 索引，不支持 Full-text 索引；NDB ：支持 Hash 索引，不支持 B-tree、Full-text 等索引；ArcHive：不支 持 B-tree、Hash、Full-text 等索引;
隐式的索引创建：
# 1.隐式的添加索引(在添加有主键约束、唯一性约束或者外键约束的字段会自动的创建索引)CREATE TABLE dept( dept_id INT PRIMARY KEY AUTO_INCREMENT,# 创建主键索引 dept_name VARCHAR(20));CREATE TABLE emp( emp_id INT PRIMARY KEY AUTO_INCREMENT,# 主键索引 emp_name VARCHAR(20) UNIQUE,# 唯一索引 dept_id INT, CONSTRaiNT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id)); # 外键索引显式的索引创建的话，基本语法格式如下，共有七种情况~
CREATE TABLE table_name [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 指定升序或者降序的索引值存储。1、创建普通索引
在book表中的year_publication字段上建立普通索引，sql语句如下:
# ①创建普通的索引CREATE TABLE book( book_id INT , book_name VARCHAR(100), AUTHORS VARCHAR(100), info VARCHAR(100) , COMMENT VARCHAR(100), year_publication YEAR, # 声明索引 INDEX idx_bname(book_name));通过命令查看索引有没有创建成功
# 方式1：SHOW CREATE TABLE book; # linux下添加\G参数
# 方式2：SHOW INDEX FROM book;
#性能分析工具：EXPLAIN，查看索引是否正在使用EXPLAIN SELECT * from book where book_name = 'mysql高级';
EXPLAIN语句输出结果的各个行我们在下一章讲解，这里主要关注两个字段
可以看到，possible_keys和key值都为idx_bname，查询时使用了索引
2、创建唯一索引
# ②创建唯一索引CREATE TABLE book1 ( book_id INT, book_name VARCHAR (100), AUTHORS VARCHAR (100), info VARCHAR (100), COMMENT VARCHAR (100), year_publication YEAR, #声明索引 UNIQUE INDEX uk_idx_cmt (COMMENT));show INDEX from book1;# 查看索引
3、主键索引
设定为主键后数据库会自动建立索引，innodb为聚簇索引，语法:
随表一起建索引:
# ③主键索引# 通过定义主键约束的方式定义主键索引create table book2( book_id int primary key, book_name varchar(100), AUTHORS VARCHAR (100), info VARCHAR (100), COMMENT VARCHAR (100), year_publication YEAR) ;删除主键索引:
# 通过删除主键约束的方式删除主键索引alter table book2drop primary key;修改主键索引：必须先删除掉（drop）原索引，再新建（add）索引
4、创建单列索引
#④ 创建单列索引CREATE TABLE book3( book_id INT, book_name VARCHAR(100), AUTHORS VARCHAR (100), info VARCHAR (100), COMMENT VARCHAR (100), year_publication YEAR, UNIQUE INDEX idx_bname(book_name));show index from book3;5、创建组合索引
举例：创建表 book4，在表中的 book_id、book_name和 info字段上建立组合索引，SQL 语句如下：
# ⑤ 创建联合索引create table book4( book_id INT, book_name VARCHAR(100), AUTHORS VARCHAR (100), info VARCHAR (100), COMMENT VARCHAR (100), year_publication YEAR, index mul_bid_bname_info(book_id,book_name,info))SHOW INDEX FROM book4;
注意上面三行依次是book_id,book_name,info，与我们创建索引时指定的顺序是严格对应的。在查询时会遵守最左索引原则，先进行book_id条件的比较，然后再进行book_name比较，最后才是info。因此注意把最常用的查询字段放在索引的最左边。
# 分析explain select * from book4 where book_id = 1001 and book_name = 'mysql'; # 会使用到mul_bid_bname_info索引explain select * from book4 where book_name = 'mysql';# 不会使用到mul_bid_bname_info索引6、 创建全文索引
FULLTEXT全文索引可以用于全文搜索，并且只为CHAR、VARCHAR和TEXT列创建索引。索引总是对整个列进行，不支持局部（前缀）索引。
举例1：创建表test4，在表中的 info 字段上建立全文索引，SQL 语句如下:
CREATE TABLE test4( id INT NOT NULL, name CHAR(30) NOT NULL, age INT NOT NULL, info VARCHAR(255), FULLTEXT INDEX futxt_idx_info(info)) ENGINE=MyISAM;在 MySQL 5.7 及之后版本中可以不指定最后的 ENGINE 了，因为在此版本中 InnoDB 支持全文索引。
语句执行完毕后，用SHOW CREATE TABLE查看表结构：
SHOW INDEX FROM test4\G;
由结果可以看到，info字段上已经成功建立了一个名为futxt_idx_info的FULLTEXT索引。
举例2：
CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR (200), body TEXT, FULLTEXT index (title, body)) ENGINE = INNODB ;创建了一个给 title 和 body 字段添加全文索引的表。
举例3：
CREATE TABLE `papers` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(200) DEFAULT NULL, `content` text, PRIMARY KEY (`id`), FULLTEXT KEY `title` (`title`,`content`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;不同于 like 方式的的查询:
SELECT * FROM papers WHERE content LIKE ‘%查询字符串%’;全文索引用 match+against 方式查询:
SELECT * FROM papers WHERE MATCH(title,content) AGAINST (‘查询字符串’);明显的提高查询效率
注意点
- 使用全文索引前，搞清楚版本支持情况；
- 全文索引比like + % 快 N倍，但是可能存在精度问题
- 如果需要全文索引的是大量数据，建议先添加数据，再创建索引。
7、创建空间索引
空间索引创建中，要求空间类型的字段必须为 非空 。
举例：创建表 test5，在空间类型为 GEOMETRY 的字段上创建空间索引，SQL 语句如下：
CREATE TABLE test5( geo GEOMETRY NOT NULL, SPATIAL INDEX spa_idx_geo(geo)) ENGINE=MyISAM;该语句执行完毕之后，使用SHOW CREATE TABLE查看表结构：
SHOW INDEX FROM test5\G;可以看到，test5的geo字段上创建了名称为spa_idx_geo的空间索引。注意创建时指定空间类型字段值的非空约束，并且表的存储引擎为MyISAM
在已经存在的表中创建索引可以使用 ALTER TABLE 语句或者 CREATE INDEX 语句。
1. 使用 ALTER TABLE 语句创建索引
ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY][index_name] (col_name[length],...) [ASC | DESC]
2. 使用 CREATE INDEX 创建索引
CREATE INDEX 语句可以在已经存在的表上添加索引，在 MySQL 中， CREATE INDEX 被映射到一个 ALTER TABLE 语句上，基本语法结构为：
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_nameON table_name (col_name[length],...) [ASC | DESC]举例1：在book表的comment字段上建立名为 的普通索引
create index idx_cmt on book(comment);举例2：在book表中的book_id字段上建立名为uk_idx_bid的唯一索引，SQL语句如下：
CREATE UNIQUE INDEX uk_idx_bid ON book(book_id);举例3：在book表的book_id、book_name、info字段上建立联合索引，SQL语句如下：
CREATE INDEX mul_bid_bname_info ON book(book_id,book_name,info);MySQL中删除索引使用ALTER TABLE或DROP INDEX语句，两者可实现相同的功能，DROP INDEX语句在内部被映射到一个ALTER TABLE语句中
1. 使用 ALTER TABLE 删除索引：
ALTER TABLE删除索引的基本语法格式如下：
ALTER TABLE table_name DROP INDEX index_name;练习：删除book表中名称为idx_bk_id的唯一索引
首先查看book表中是否名称为idx_bk_id的索引，输入SHOW语句如下：
SHOW INDEX FROM book\G;下面删除该索引，输入删除语句如下：
ALTER TABLE book DROP INDEX idx_bk_id;提示
添加AUTO_INCREMENT约束字段的唯一索引不能被删除（）
2. 使用 DROP INDEX 语句删除索引：
DROP INDEX删除索引的基本语法格式如下：
DROP INDEX index_name ON table_name;练习：删除book表中名称为idx_aut_info的组合索引，SQL语句如下：
DROP INDEX idx_aut_info ON book;语句执行完毕，使用SHOW查看索引是否删除：
SHOW CREATE TABLE book\G;可以看到，book表中已经没有名称为idx_aut_info的组合索引，删除索引成功。
提示 删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。
举例：分别在 MySQL 5.7 版本和 MySQL 8.0 版本中创建数据表 ts1，结果如下:
CREATE TABLE ts1(a int,b int,index idx_a_b(a,b desc)); 在 MySQL 5.7 版本中查看数据表 ts1 的结构，结果如下:
从结果可以看出，索引仍然是默认的升序。
在MySQL 8.0版本中查看数据表ts1的结构，结果如下:
从结果可以看出，索引已经是降序了。下面继续测试降序索引在执行计划中的表现。
分别在 MySQL 5.7 版本和 MySQL 8.0 版本的数据表 ts1 中插入 800 条随机数据，执行语句如下:
DELIMITER //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 //DELIMITER ; #调用CALL ts_insert();在 MySQL 5.7 版本中查看数据表 ts1 的执行计划，结果如下:
# 优化测试EXPLAIN SELECT * FROM ts1 ORDER BY a,b DESC LIMIT 5;
从结果可以看出，执行计划中扫描数为 799，而且使用了 Using filesort。
提示：Using filesort 是MySQL中一种速度比较慢的外部排序，能避免是最好的。多数情况下，管理员可以通过优化索引来尽量避免出现 Using filesort，从而提高数据库执行速度。
在 MySQL 8.0 版本中查看数据表 ts1 的执行计划
从结果可以看出，执行计划中扫描数为 5，而且没有使用 Using filesort。
注意：降序索引只对查询中特定的排序顺序有效，如果使用不当，反而查询效率更低。例如，上述查询排序条件改为 order by a desc, b desc，MySQL 5.7 的执行计划要明显好于 MySQL 8.0。
将排序条件改为order by a desc,b desc后，下面来对比不同版本中执行计划的效果。
在MySQL5.7版本中查看数据表ts1的执行计划，结果如下：
# 优化测试EXPLAIN SELECT * FROM ts1 ORDER BY a DESC,b DESC LIMIT 5;
在 MySQL 8.0 版本中查看数据表 ts1 的执行计划
从结果可以看出，修改后MySQL5.7的执行计划明显好于MySQL8.0
在 MySQL 5.7 版本及之前，只能通过显式的方式删除索引。此时，如果发现删除索引后出现错误，又只能通过显式创建索引的方式将删除的索引创建回来。如果数据表中的数据量非常大，或者数据表本身比较大，这种操作就会消耗系统过多的资源，操作成本非常高。
从MySQL 8.x 开始支持 隐藏索引(invisible indexes)，只需要将待删除的索引设置为隐藏索引，使查询优化器不再使用这个索引（即使使用 force index（强制使用索引），优化器也不会使用该索引）， 确认将索引设置为隐藏索引后系统不受任何响应，就可以彻底删除索引。这种通过先将索引设置为隐藏索引，再删除索引的方式就是软删除。
1. 创建表时直接创建
在 MySQL 中创建隐藏索引通过 SQL 语句 INVISIBLE 来实现，其语法形式如下：
CREATE TABLE tablename( propname1 type1[CONSTRAINT1], propname2 type2[CONSTRAINT2], ...... propnamen typen, INDEX [indexname](propname1 [(length)]) INVISIBLE);上述语句比普通索引多了一个关键字 INVISIBLE，用来标记索引为不可见索引。
练习：在创建书籍表book时，在字段idx_cmt上创建隐藏索引
#① 创建表时，隐藏索引create table book( book_id INT, book_name VARCHAR(100), AUTHORS VARCHAR (100), info VARCHAR (100), COMMENT VARCHAR (100), year_publication YEAR, # 创建不可见的索引 index idx_cmt(comment) invisible);通过explain查看发现，优化器并没有使用索引，而是使用的全表扫描
explain select * from book7 where comment = 'mysql...';
2. 在已经存在的表上创建
可以为已经存在的表设置隐藏索引，其语法形式如下：
CREATE INDEX indexnameON tablename(propname[(length)]) INVISIBLE;举例：
CREATE INDEX idx_year_pub ON book(year_publication) INVISIBLE;3. 通过 ALTER TABLE 语句创建
语法形式如下：
ALTER TABLE tablenameADD INDEX indexname (propname [(length)]) INVISIBLE;举例：
ALTER TABLE bookADD UNIQUE INDEX uk_idx_bname(book_name) INVISIBLE;4. 切换索引可见状态
已存在的索引可通过如下语句切换可见状态:
ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #切换成隐藏索引 ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #切换成非隐藏索引举例：
# 修改索引的可见性ALTER TABLE book ALTER INDEX idx_year_pub invisible;#可见--->不可见ALTER TABLE book ALTER INDEX idx_cmt visible;#不可见---》可见如果将 idx_cmt 索引切换成可见状态，通过 explain 查看执行计划，发现优化器选择了idx_cmt索引
**注意：**当索引被隐藏时，它的内容仍然是和正常索引一样实时更新的。如果一个索引需要长期被隐藏，那么可以将其删除，因为索引的存在会影响插入、更新和删除的性能。
通过设置隐藏索引的可见性可以查看索引对调优的帮助。
5. 使隐藏索引对查询优化器可见(了解)
在 MySQL 8.x 版本中，为索引提供了一种新的测试方式，可以通过查询优化器的一个开关 （use_invisible_indexes）来打开某个设置，使隐藏索引对查询优化器可见。如果 use_invisible_indexes 设置为 off（默认），优化器会忽略隐藏索引。如果设置为 on，即使隐藏索引不可见，优化器在生成执行计划时仍会考虑使用隐藏索引。
（1）在 MySQL 命令行执行如下命令查看查询优化器的开关设置。
mysql> select @@optimizer_switch \G在输出的结果信息中找到如下属性配置。
use_invisible_indexes=off此属性配置值为off，说明隐藏索引默认对查询优化器不可见。
（2）使隐藏索引对查询优化器可见，需要在 MySQL 命令行执行如下命令:
mysql> set session optimizer_switch="use_invisible_indexes=on";Query OK, 0 rows affected (0.00 sec)SQL 语句执行成功，再次查看查询优化器的开关设置。
mysql> select @@optimizer_switch \G*************************** 1. row ***************************@@optimizer_switch:index_merge=on,index_merge_uNIOn=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=on,skip_scan=on,hash_join=on1 row in set (0.00 sec)此时，在输出结果中可以看到如下属性配置。
use_invisible_indexes=onuse_invisible_indexes 属性的值为 on，说明此时隐藏索引对查询优化器可见。
（3）使用 EXPLAIN 查看以字段 invisible_column 作为查询条件时的索引使用情况。
explain select * from classes where cname = '高一2班';查询优化器会使用隐藏索引来查询数据。
（4）如果需要使隐藏索引对查询优化器不可见，则只需要执行如下命令即可。
mysql> set session optimizer_switch="use_invisible_indexes=off";Query OK, 0 rows affected (0.00 sec)再次查看查询优化器的开关设置。
mysql> select @@optimizer_switch \G;此时，use_invisible_indexes 属性的值已经被设置为“off”。
第1步：创建数据库、创建表
CREATE DATABASE testdb1;USE atguigudb1;#1.创建学生表和课程表CREATE TABLE `student_info` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `student_id` INT NOT NULL , `name` VARCHAR(20) DEFAULT NULL, `course_id` INT NOT NULL , `class_id` INT(11) DEFAULT NULL, `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`)) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;CREATE TABLE `course` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `course_id` INT NOT NULL , `course_name` VARCHAR(40) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;第2步：创建模拟数据必需的存储函数
#函数1:创建随机产生字符串函数DELIMITER //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 //DELIMITER ;#函数2:创建随机数函数DELIMITER //CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11) BEGINDECLARE i INT DEFAULT 0;SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ;RETURN i;END //DELIMITER ;创建函数，假如报错:
This function has none of DETERMINISTIC......由于开启过慢查询日志 bin-log, 我们就必须为我们的 function 指定一个参数。
主从复制，主机会将写操作记录在 bin-log 日志中。从机读取 bin-log 日志，执行语句来同步数据。如果使用函数来操作数据，会导致从机和主机操作时间不一致。所以，默认情况下，mysql 不开启创建函数设置。
查看 mysql 是否允许创建函数：
show variables like 'log_bin_trust_function_creators';命令开启：允许创建函数设置:
set global log_bin_trust_function_creators=1; mysqld 重启，上述参数又会消失。永久方法：
windows下：my.ini[mysqld]加上：
log_bin_trust_function_creators=1linux下：/etc/my.cnf 下 my.cnf[mysqld] 加上：
log_bin_trust_function_creators=1第3步：创建插入模拟数据的存储过程
# 存储过程1:创建插入课程表存储过程DELIMITER //CREATE PROCEDURE insert_course( max_num INT ) BEGINDECLARE 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_numEND REPEAT;COMMIT; #提交事务 END //DELIMITER ;# 存储过程2:创建插入学生信息表存储过程DELIMITER //CREATE PROCEDURE insert_stu( max_num INT ) BEGINDECLARE 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_numEND REPEAT;COMMIT; #提交事务END //DELIMITER ;第4步：调用存储过程
CALL insert_course(100); # 课程表中添加100条数据CALL insert_stu(1000000);# 学生表中插入1000000条数据
某个字段在 SELECT 语句的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。
比如 student_info 数据表（含 100 万条数据），假设我们想要查询 student_id=123110 的用户信息。
①查看student_info表中的索引
可以看出，我们没有对student_id字段创建索引。
②进行如下查询，耗时220ms
③添加索引
alter table student_info add index idx_sid(student_id);④再查询。耗时0ms。性能提升杠杠的~
索引其实就是让数据按照某种顺序进行存储或检索。当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，如果 对分组或者排序的字段建立索引，本身索引的数据就已经排好序了，进行分组查询和排序操作性能不是很nice吗？另外，如果待排序的列有多个，那么可以在这些列上建立 组合索引 。
①下面在有student_id索引的情况下，查询：
mysql> SELECT student_id,COUNT(*) AS num -> FROM student_info -> GROUP BY student_id -> LIMIT 100;+------------+-----+| student_id | num |+------------+-----+| 1 | 5 |.....此处省略n行......| 3 | 4 || 101 | 7 |+------------+-----+100 rows in set (0.00 sec)②删除索引
#删除idx_sid索引DROP INDEX idx_sid ON student_info;③再次查询 ，慢的像蜗牛~
mysql> SELECT student_id,COUNT(*) AS num -> FROM student_info -> GROUP BY student_id -> LIMIT 100;+------------+-----+| student_id | num |+------------+-----+| 95666 | 9 |.....此处省略n行......| 173440 | 14 || 67234 | 9 |+------------+-----+100 rows in set (0.78 sec)同样，如果是ORDER BY，也需要对字段创建索引
④如果同时使用GROUP BY和ORDER BY，先看看不加索引的情况
mysql> SELECT student_id,COUNT(*) AS num FROM student_info -> GROUP BY student_id -> ORDER BY create_time DESC -> LIMIT 100;ERROR 1055 (42000): Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'atguigudb1.student_info.create_time' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by⑤出现了一个异常信息，这是因为我们使用的sql_mode是only_full_group_by。修改下再来查询，时间代价是6.61s
mysql> SELECT @@sql_mode;+-----------------------------------------------------------------------------------------------------------------------+| @@sql_mode |+-----------------------------------------------------------------------------------------------------------------------+| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |+-----------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> SET @@sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';Query OK, 0 rows affected (0.00 sec); # 去掉ONLY_FULL_GROUP_BYmysql> SELECT student_id,COUNT(*) AS num FROM student_info -> GROUP BY student_id -> ORDER BY create_time DESC -> LIMIT 100;+------------+-----+| student_id | num |+------------+-----+| 21497 | 1 || 17311 | 1 |.....此处省略n行......| 183509 | 1 |+------------+-----+100 rows in set (6.61 sec)⑥再看看两个字段分别建立单列索引的情况，耗时5.26 s，快了一点点
mysql> ALTER TABLE student_info ADD INDEX idx_sid(student_id);Query OK, 0 rows affected (1.77 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> ALTER TABLE student_info ADD INDEX idx_cre_time(create_time);Query OK, 0 rows affected (1.49 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> SELECT student_id,COUNT(*) AS num FROM student_info -> GROUP BY student_id -> ORDER BY create_time DESC -> LIMIT 100;+------------+-----+| student_id | num |+------------+-----+| 64044 | 1 |.....此处省略n行......| 101052 | 1 || 152620 | 1 |+------------+-----+100 rows in set (5.26 sec)**注意：**建立多个单列索引，并不会都走，像刚才这个例子，只会走idx_sid索引
⑦分析下它的查询过程,原来我们只用了一个索引，由于我们是先GROUP BY student_id,后ORDER BY create_time，我们实际上只使用了索引idx_sid
mysql> EXPLAIN SELECT student_id,COUNT(*) AS num FROM student_info -> GROUP BY student_id -> ORDER BY create_time DESC -> LIMIT 100;+----+-------------+--------------+------------+-------+---------------+---------+---------+------+--------+----------+---------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+--------------+------------+-------+---------------+---------+---------+------+--------+----------+---------------------------------+| 1 | SIMPLE | student_info | NULL | index | idx_sid | idx_sid | 4 | NULL | 997130 | 100.00 | Using temporary; Using filesort |+----+-------------+--------------+------------+-------+---------------+---------+---------+------+--------+----------+---------------------------------+1 row in set, 1 warning (0.00 sec)⑧建立联合索引的情况,芜湖起飞，直接0.25s。此时我们用EXPLAIN查看命中的也是 联合索引
mysql> ALTER TABLE student_info ADD INDEX idx_sid_cre_time(student_id,create_time DESC);Query OK, 0 rows affected (2.09 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> SELECT student_id,COUNT(*) AS num FROM student_info -> GROUP BY student_id -> ORDER BY create_time DESC -> LIMIT 100;+------------+-----+| student_id | num |+------------+-----+| 1226 | 8 |.....此处省略n行......| 1400 | 2 |+------------+-----+100 rows in set (0.25 sec)mysql> EXPLAIN SELECT student_id,COUNT(*) AS num FROM student_info -> GROUP BY student_id -> ORDER BY create_time DESC -> LIMIT 100;+----+-------------+--------------+------------+-------+--------------------------+------------------+---------+------+--------+----------+----------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+--------------+------------+-------+--------------------------+------------------+---------+------+--------+----------+----------------------------------------------+| 1 | SIMPLE | student_info | NULL | index | idx_sid,idx_sid_cre_time | idx_sid_cre_time | 10 | NULL | 997130 | 100.00 | Using index; Using temporary; Using filesort |+----+-------------+--------------+------------+-------+--------------------------+------------------+---------+------+--------+----------+----------------------------------------------+1 row in set, 1 warning (0.00 sec)⑨再来测试,交换字段顺序建立联合索引idx_cre_time_sid，耗时5.24s。下面查询真正使用的索引key是idx_sid
mysql> ALTER TABLE student_info ADD INDEX idx_cre_time_sid(create_time DESC,student_id);Query OK, 0 rows affected (2.10 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> DROP INDEX idx_sid_cre_time ON student_info; #删除联合索引idx_sid_cre_timeQuery OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show INDEX from student_info; # 查看student_info中的索引+--------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |+--------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| student_info | 0 | PRIMARY | 1 | id | A | 993366 | NULL | NULL | | BTREE | | | YES | NULL || student_info | 1 | idx_sid | 1 | student_id | A | 199180 | NULL | NULL | | BTREE | | | YES | NULL || student_info | 1 | idx_cre_time | 1 | create_time | A | 82 | NULL | NULL | YES | BTREE | | | YES | NULL || student_info | 1 | idx_cre_time_sid | 1 | create_time | D | 77 | NULL | NULL | YES | BTREE | | | YES | NULL || student_info | 1 | idx_cre_time_sid | 2 | student_id | A | 967825 | NULL | NULL | | BTREE | | | YES | NULL |+--------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+5 rows in set (0.00 sec)mysql> SELECT student_id,COUNT(*) AS num FROM student_info -> GROUP BY student_id -> ORDER BY create_time DESC -> LIMIT 100;+------------+-----+| student_id | num |+------------+-----+| 64044 | 1 |.....此处省略n行......| 101052 | 1 || 152620 | 1 |+------------+-----+100 rows in set (5.24 sec)mysql> EXPLAIN SELECT student_id,COUNT(*) AS num FROM student_info -> GROUP BY student_id -> ORDER BY create_time DESC -> LIMIT 100;#起作用的是idx_sid+----+-------------+--------------+------------+-------+--------------------------+---------+---------+------+--------+----------+---------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+--------------+------------+-------+--------------------------+---------+---------+------+--------+----------+---------------------------------+| 1 | SIMPLE | student_info | NULL | index | idx_sid,idx_cre_time_sid | idx_sid | 4 | NULL | 997130 | 100.00 | Using temporary; Using filesort |+----+-------------+--------------+------------+-------+--------------------------+---------+---------+------+--------+----------+---------------------------------+1 row in set, 1 warning (0.00 sec)**总结：**如果我们仅仅使用GROUP BY 或者 ORDER BY，且后面只有一个字段，则单独建立索引；如果后面跟多个字段，则建立联合索引。如果既有GROUP BY 又有 ORDER BY，那就建立联合索引，且GROUP BY的字段写在前面，ORDER BY的字段写在后面。8.0后的版本也可以考虑使用降序索引
对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或删除。 如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护
mysql> UPDATE student_info SET student_id = 10002 -> WHERE NAME = '462eed7ac6e791292a79';# 550msQuery OK, 0 rows affected (0.55 sec)Rows matched: 0 Changed: 0 Warnings: 0mysql> ALTER TABLE student_info -> ADD INDEX idx_name(NAME);Query OK, 0 rows affected (2.26 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> UPDATE student_info SET student_id = 10002 -> WHERE NAME = '462eed7ac6e791292a79';# 1msQuery OK, 0 rows affected (0.001 sec)Rows matched: 0 Changed: 0 Warnings: 0有时候我们需要对某个字段进行去重，使用 DISTINCT，那么对这个字段创建索引，也会提升查询效率。
比如，我们想要查询课程表中不同的 student_id 都有哪些，如果我们没有对 student_id 创建索引，执行 SQL 语句：
SELECT DISTINCT(student_id) FROM `student_info`; 运行结果（600637 条记录，运行时间 0.683s ）
如果我们对 student_id 创建索引，再执行 SQL 语句:
SELECT DISTINCT(student_id) FROM `student_info`;运行结果（600637 条记录，运行时间0.010s ）
你能看到 SQL 查询效率有了提升，同时显示出来的 student_id 还是按照 递增的顺序 进行展示的。这是因为索引会对数据按照某种顺序进行排序，所以在去重的时候也会快很多。
首先，连接表的数量尽量不要超过 3 张 ，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快(n ,n^2 , n^3…)，严重影响查询的效率。
其次，对 WHERE 条件创建索引，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。
🔊注意：对于用连接的字段创建索引，这些字段在多张表中的 类型必须一致 。比如 course_id 在student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。否则在查询时，虽然也会帮我们进行隐式的类型转换，转换时会使用函数，但会导致索引失效。索引失效情况在后续文章中还会给大家详细介绍，敬请期待。
举个例子，如果我们只对 student_id 创建索引，执行 SQL 语句，耗时0.21s
mysql> SELECT c.course_id, NAME, s.student_id, course_name -> FROM student_info s JOIN course c -> ON s.course_id = c.course_id -> WHERE NAME = 'WloNYD';+-----------+--------+------------+-------------+| course_id | NAME | student_id | course_name |+-----------+--------+------------+-------------+| 10077 | WloNYD | 95666 | JfydVs || 10077 | WloNYD | 95666 | nZkayq || 10077 | WloNYD | 95666 | mTHDYg || 10085 | wLonyD | 98444 | pZdpsR |+-----------+--------+------------+-------------+4 rows in set (0.21 sec)这时，我们对 name 创建索引，再执行上面的 SQL 语句，耗时 0.00s
mysql> ALTER TABLE student_info -> ADD INDEX idx_name(NAME);# 为name创建索引Query OK, 0 rows affected (2.52 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> SELECT c.course_id, name, s.student_id, course_name -> FROM student_info s JOIN course c -> ON s.course_id = c.course_id -> WHERE name = 'WloNYD';+-----------+--------+------------+-------------+| course_id | name | student_id | course_name |+-----------+--------+------------+-------------+| 10077 | WloNYD | 95666 | mTHDYg || 10077 | WloNYD | 95666 | nZkayq || 10085 | wLonyD | 98444 | pZdpsR || 10077 | WloNYD | 95666 | JfydVs |+-----------+--------+------------+-------------+4 rows in set (0.00 sec)
创建一张商户表，因为地址字段比较长，在地址字段上建立前缀索引
create table shop(address varchar(120) not null);alter table shop add index(address(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(*)的区分度来确定。
这样也可以较少的建立一些索引。同时，由于"最左前缀原则"，可以增加联合索引的使用率。
索引建立的多，维护的成本也高。
多个字段进行联合查询时，其实只使用到一个索引。如下，只用到了idx_sid索引
mysql> EXPLAIN SELECT student_id,COUNT(*) AS num FROM student_info -> GROUP BY student_id -> ORDER BY create_time DESC -> LIMIT 100;在建立联合索引的相关字段做查询时，联合索引都能生效，使用频率比较高。足够优化sql执行的速度了
在实际工作中，我们也需要注意平衡，索引的数目不是越多越好。我们需要限制每张表上的索引数量，建议单张表索引数量不超过6个。原因：
①每个索索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
②索引会影响INSERT、DELETE、 UPDATE等语句的性能，因为表中的数据更改的同时，索引也会进行调整和更新，会造成负担。
③优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，会增加MySQL优化器生成执行计划时间，降低查询性能。
解释：表中创建的索引过多，优化器在possible_keys中选择合适的key 时需要的成本也会更多。比如下面查询中possible_keys有两个，实际使用的key只有一个，这其实优化器判断的哟。
mysql> EXPLAIN SELECT student_id,COUNT(*) AS num FROM student_info -> GROUP BY student_id -> ORDER BY create_time DESC -> LIMIT 100;+----+-------------+--------------+------------+-------+--------------------------+---------+---------+------+--------+----------+---------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+--------------+------------+-------+--------------------------+---------+---------+------+--------+----------+---------------------------------+| 1 | SIMPLE | student_info | NULL | index | idx_sid,idx_cre_time_sid | idx_sid | 4 | NULL | 997449 | 100.00 | Using temporary; Using filesort |+----+-------------+--------------+------------+-------+--------------------------+---------+---------+------+--------+----------+---------------------------------+1 row in set, 1 warning (0.00 sec)
结论：在数据表中的数据行数比较少的情况下，比如不到 1000 行，是不需要创建索引的。
例如身份证、UUID（在索引比较时需要转为 ASCII，并且插入时可能造成页分裂）、MD5、HASH、无序长字符串等。
冗余索引
举例：建表语句如下
CREATE TABLE person_info( id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(100) NOT NULL, birthday DATE NOT NULL, phone_number CHAR(11) NOT NULL, country varchar(100) NOT NULL, PRIMARY KEY (id), KEY idx_name_birthday_phone_number (name(10), birthday, phone_number), KEY idx_name (name(10)));我们知道，通过 idx_name_birthday_phone_number 索引就可以对 name 列进行快速搜索，再创建一 个专门针对 name 列的索引就算是一个 冗余索引，维护这个索引只会增加维护的成本，并不会对搜索有 什么好处。
重复索引
另一种情况，我们可能会对某个列 重复建立索引 ，比方说这样：
CREATE TABLE repeat_index_demo ( col1 INT PRIMARY KEY, col2 INT, UNIQUE uk_idx_c1 (col1), INDEX idx_c1 (col1));我们看到，col1 既是主键、又给它定义为一个唯一索引，还给它定义了一个普通索引，可是主键本身就会生成聚簇索引，所以定义的唯一索引和普通索引是重复的，这种情况要避免。
--结束END--
本文标题: 《MySQL高级篇》六、索引的创建与设计原则
本文链接: https://www.lsjlt.com/news/387978.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑，方便收藏和打印～
2024-05-16
2024-05-16
2024-05-16
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
官方手机版
微信公众号
商务合作
0