mysql给列加唯一索引 mysql多列唯一索引

转载

mob64ca13fe9c58 2023-11-28 10:26:09

文章标签 mysql给列加唯一索引 mysql 数据库 sql 字段 文章分类 MySQL 数据库

一、索引的声明与使用
mysql的索引包括普通、唯一性、全文、单列、多列和空间索引
-从功能逻辑：普通、唯一、主键和全文索引
-从物理实现上：聚簇和非聚簇索引
-从作用字段个数上：单列和联合索引

普通索引：可以创建在任何数据类型中，查询记录时就可以根据该索引进行查询
唯一性索引：使用unique参数可以设置唯一性索引，在创建唯一性索引时，限制索引的值必须是唯一的，但允许为空值，一张表可以有多个唯一性索引
主键性索引：最多有一个主键，也叫做聚簇索引、
单列索引：就是单个字段上创建的索引
多列索引：多个字段上创建的索引，也叫做组合或者联合索引或者非聚簇索引，同时遵循最左前缀集合

二、索引的创建
查看索引
show create table book;
show index from book;
第一种在创建表的同时创建索引
1、普通索引
index idx_bname(book_name)
index:创建标识
idx_bname:索引名称
book_name:字段名称
2、创建唯一索引

unique index uk_idx_cmt(comment)

3、通过定义主键约束的方式定义主键索引（隐式的）
删除：alter table book2 drop primary key
4、单列索引
一个字段的索引
5、联合索引

index mul_bid_name_info(book_id, book_name, info)

6、全文索引

fulltext index futxt_idx_info(info(50))

全文索引用match+against查询：
select * from papers where match(title, content) against(‘查询字符串’);
7、创建空间索引
spatial index spa_idx_geo(geo)
第二种，表已经创建好

1、alter table … add …
 alter table book5 add index idx_cmt(comment);
 alter table book5 add unique index idx_cmt(comment);
 alter table book5 add index idx_cmt(id, comment, title);
 2、create index … on …
 create index idx_cmt on book6(comment);

三、索引的删除

1、alter table … drop …
 alter table book5 drop index idx_cmt;
 alter table book5 drop unique index idx_cmt;
 2、drop index … on …
 drop index idx_cmt on book6;

四、索引的新特性（8.0）
1、降序索引
alter table book5 add index idx_cmt(a, b desc);
2、隐藏索引
通过先将索引设置为隐藏索引，再删除索引的方式就是软删除
建表以后：
1、alter table … add … invisible
alter table book5 add unique index idx_cmt(comment) invisible;
默认为：visible-可见，invisible-不可见
1、create index … on … invisible

五、索引的设计原则
1、数据准备
第一步：创建数据库，创建表

create database atguigudb1;
 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;

第二步：创建模拟数据必需的存储函数

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 ;SELECT @@log_bin_trust_function_creators;
 SET GLOBAL log_bin_trust_function_creators = 1;

#函数2：创建随机数函数

DELIMITER //
 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 //
 DELIMITER ;

存储过程1：创建插入课程表存储过程

DELIMITER //
 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 //
 DELIMITER ;

存储过程2：创建插入学生信息表存储过程

DELIMITER //
 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 //
 DELIMITER ;

#调用存储过程：

CALL insert_course(100);
 SELECT COUNT() FROM course;
 CALL insert_stu(1000000);
 SELECT COUNT() FROM student_info;

那些情况适合创建索引
1、字段的数值有唯一性的限制
2、频繁作为where查询条件的字段
3、经常group by和order by的列
4、udpate,delete的where条件列
5、distinct字段需要创建索引
6、多表join连接操作时，创建索引注意事项
-连接表的数量尽量不要超过3张
-对where条件创建索引
-对用于连接的字段创建索引
7、使用列的类型小的创建索引
8、使用字符串前缀创建索引
9、区分度高的列适合作为索引
10、使用最频繁的列放到联合索引的左侧
11、多个字段都要创建索引联合索引优于单值索引
限制索引的数目
单表不超过6个，原因如下：
1、每个索引都要占用磁盘空间，索引多，占用空间大
2、索引会影响insert、delete、update等语句，索引的调整和更新会有负担
3、优化器在选择如何优化查询时，会对每一个用到的索引进行评估，以便找到最好的执行计划，会增加生成执行计划时间，降低查询性能

那些情况不适合创建索引
在where中使用不到的字段，不要设置索引
数据量小的表最好不要使用索引
大量重复数据的列不要建立索引
避免对经常更新的表创建过多的索引
不建议用无序的值作为索引
删除不再使用或者很少使用的索引
不要定义冗余或重复的索引

索引优化与查询优化
#建库

CREATE DATABASE atguigudb2;

#建表

CREATE TABLE class (
id INT(11) NOT NULL AUTO_INCREMENT,
className VARCHAR(30) DEFAULT NULL,
address VARCHAR(40) DEFAULT NULL,
monitor INT NULL ,
 PRIMARY KEY (id)
 ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;CREATE TABLE student (
id INT(11) NOT NULL AUTO_INCREMENT,
stuno INT NOT NULL ,
name VARCHAR(20) DEFAULT NULL,
age INT(3) DEFAULT NULL,
classId INT(11) DEFAULT NULL,
 PRIMARY KEY (id)
 #CONSTRAINT fk_class_id FOREIGN KEY (classId) REFERENCES t_class (id)
 ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;SET GLOBAL log_bin_trust_function_creators=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 ;

#用于随机产生多少到多少的编号

DELIMITER //
 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 //
 DELIMITER ;

#创建往stu表中插入数据的存储过程

DELIMITER //
 CREATE PROCEDURE insert_stu( START INT , max_num INT )
 BEGIN
 DECLARE i INT DEFAULT 0;
 SET autocommit = 0; #设置手动提交事务
 REPEAT #循环
 SET i = i + 1; #赋值
 INSERT INTO student (stuno, NAME ,age ,classId ) VALUES ((START+i),rand_string(6),rand_num(1,50),rand_num(1,1000));
 UNTIL i = max_num
 END REPEAT;
 COMMIT; #提交事务
 END //
 DELIMITER ;

#执行存储过程，往class表添加随机数据

DELIMITER //
 CREATE PROCEDURE insert_class( max_num INT )
 BEGIN
 DECLARE i INT DEFAULT 0;
 SET autocommit = 0;
 REPEAT
 SET i = i + 1;
 INSERT INTO class ( classname,address,monitor ) VALUES (rand_string(8),rand_string(10),rand_num(1,100000));
 UNTIL i = max_num
 END REPEAT;
 COMMIT;
 END //
 DELIMITER ;#执行存储过程，往class表添加1万条数据
 CALL insert_class(10000);
 #执行存储过程，往stu表添加50万条数据
 CALL insert_stu(100000,500000);SELECT COUNT() FROM class;
 SELECT COUNT() FROM student;

#删除表中指定索引

DELIMITER //
 CREATE PROCEDURE proc_drop_index(dbname VARCHAR(200),tablename VARCHAR(200))
 BEGIN
 DECLARE done INT DEFAULT 0;
 DECLARE ct INT DEFAULT 0;
 DECLARE _index VARCHAR(200) DEFAULT ‘’;
 DECLARE _cur CURSOR FOR SELECT index_name FROM information_schema.STATISTICS WHERE table_schema=dbname AND table_name=tablename AND seq_in_index=1 AND index_name <>‘PRIMARY’ ;

#每个游标必须使用不同的declare continue handler for not found set done=1来控制游标的结束
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=2 ;
#若没有数据返回,程序继续,并将变量done设为2

OPEN _cur;
 FETCH _cur INTO _index;
 WHILE _index<>‘’ DO
 SET @str = CONCAT("drop index " , _index , " on " , tablename );
 PREPARE sql_str FROM @str ;
 EXECUTE sql_str;
 DEALLOCATE PREPARE sql_str;
 SET _index=‘’;
 FETCH _cur INTO _index;
 END WHILE;
 CLOSE _cur;
 END //
 DELIMITER ;

覆盖索引的利弊
好处：
避免Innodb表进行索引的二次查询（回表）
可以把随机IO变成顺序IO加快查询效率：由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段
弊端：索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。

索引下推
好处：ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎次数
但是，ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例
默认情况下启用索引条件下推。当使用索引条件下推时，EXPLAIN语句输出结果中Extra列内容显示为Using index condition

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。