MySQL——索引的创建原则
1、数据准备
创建数据库、表
create database if not exists testindexdb;
use testindexdb;
create table student_info(
id int(11) not null auto_increment,
student_id int(11) 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;
编写存储函数创建模拟数据
开启创建函数:
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
'abcdefghijklmnopqrstuvwsyzABCDEFGJHIKLMNOPQRSTUVWSYZ';
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 ;
创建插入数据的存储过程
创建插入课程表的存储过程:
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 ;
创建插入学生信息表的存储过程:
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);
call insert_stu(1000000);
2、适合创建索引的情况
1、字段的数值有唯一性限制
索引本身可以起到约束的作用,比如唯一索引、主键索引都是可以起到唯一性约束的,因此在我们的数据表中,如果哪个字段是唯一性的,就可以直接创建唯一性索引,或者主键索引。这样可以更快速地通过该索引来确定某条记录。
例如,学生表中学号是具有唯一性的字段,为该字段建立唯一性索引可以很快确定某个学生的信息,如果使用姓名的话,可能存在同名现象,从而降低查询速度。
2、频繁作为where查询条件的字段
某个字段在SELECT语句的WHERE条件中经常被使用到,那么就需要给这个字段创建索引了。尤其是在数据量大的情况下,创建普通索引就可以大幅提升数据查询的效率。
当不建索引时的查询时间:查询到10038条数据,时间花费 310ms
select course_id,class_id,name,create_time,student_id from student_info where course_id = 10085;
在course_id字段上创建索引:
create index idx_cid on student_info(course_id);
再次执行相同查询:查询到10038条数据,时间花费 30ms,提升非常明显
3、经常GROUP BY 和 ORDER BY 的列
索引就是让数据按照某种顺序进行存储或检索,因此当我们使用GROUP BY对数据进行分组查询,或者使用ORDER BY对数据进行排序的时候,就需要对分组或者排序的字段进行索引。如果待排序的列有多个,那么可以在这些列上建立组合索引。
如果查询中既使用了 GROUP BY 又使用了 ORDER BY,可以建立联合索引,其中 GROUP BY使用的字段放前面, ORDER BY使用的字段放后面。
4、UPDATE、DELETE时 的 WHERE 条件中的列
UPDATE、DELETE时 的 WHERE 条件中的列需要创建索引。
对数据按照某个条件进行查询后再进行UPDATE或 DELETE的操作,如果对WHERE字段创建了索引,就能大幅提升效率。原理是因为我们需要先根据WHERE条件列检索出来这条记录,然后再对它进行更新或删除。如果进行更新的时候,更新的字段是非索引字段,提升的效率会更明显,这是因为非索引字段更新不需要对索引进行维护。
5、DISTINCT 去重字段需要创建索引
有时候我们需要对某个字段进行去重,使用DISTINCT,那么对这个去重的字段创建索引,也会提升查询效率。
6、多表连接时创建索引
1、对WHERE 条件的列创建索引,因为WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下,没有WHERE条件过滤是非常可怕的。
2、对用于连接的字段创建索引,并且该字段在多张表中类型必须一致。比如course_id在student_info表和course表中都为int(11)类型,而不能一个为int另一个为varchar类型。
7、对使用列的数据类型范围小的创建索引
数据类型越小,在查询时进行的比较操作越快
数据类型越小,索引占用的存储空间就越少,在一个数据页内就可以放下更多的记录,从而减少磁盘I/0带来的性能损耗,也就意味着可以把更多的数据页缓存在内存中,从而加快读写效率。
当表的主键的数据类型范围小时,更适合加索引,因为不仅是聚簇索引中会存储主键值,其他所有的二级索引的节点处都会存储一份记录的主键值,如果主键使用更小的数据类型,也就意味着节省更多的存储空间和更高效的I/0。
8、使用字符串前缀创建索引·
假设我们的字符串很长,那存储一个字符串就需要占用很大的存储空间。在我们需要为这个字符串列建立索引时,我们可以通过截取字段的前面一部分内容建立索引,这个就叫前缀索引。
这样在查找记录时虽然不能精确的定位到记录的位置,但是能定位到相应前缀所在的位置,然后根据前缀相同的记录的主键值回表查询完整的字符串值。既节约空间,又减少了字符串的比较时间,还大体能解决排序的问题。
9、区分度高(散列性高)的列适合作为索引
列的基数指的是某一列中不重复数据的个数,比方说某个列包含值2,5,8,2,5,8,2,5,8,虽然有9条记录,但该列的基数却是3。也就是说,在记录行数一定的情况下,列的基数越大,该列中的值越分散;列的基数越小,该列中的值越集中。这个列的基数指标非常重要,直接影响我们是否能有效的利用索引。最好为列的基数大的列建立索引,为基数太小列的建立索引效果可能不好。
可以使用下面公式计算区分度,越接近1越好,一般超过33%就算是比较高效的索引了。
select count(distinct a)/count(*) from t1
拓展:联合索引把区分度高(散列性高)的列放在前面。
10、使用最频繁的列放到联合索引的左侧
最佳左前缀原则
11、在多个字段都要创建索引的情况下,联合索引优于单列索引
3、限制索引的数量
索引并不是越多越好,要根据查询有针对性的创建。虽然MySQL单个普通表上最多能建65个索引(64个二级索引 + 1个主键索引),但还是建议单张表索引数量不要超过6个,原因:
- 索引并不是越多越好,索引可以提高查询的效率,但会降低写数据的效率。有时不恰当的索引还会降低查询的效率。
- 每个索引都需要占用磁盘空间,索引越多,需要的磁盘空间就越大。
- 索引会影响INSERT、DELETE、UPDATE等语句的性能,因为表中的数据更改的同时,索引也会进行调整和更新,会造成负担。
- 优化器在选择如何优化查询时,会根据统一信息,对每一个可以用到的索引来进行评估,以生成出一个最好的执行计划,如果同时有很多个索引都可以用于查询,会增加MySQL优化器生成执行计划时间,降低查询性能。
4、不适合创建索引的情况
1、在wherw,GROUP BY 或 ORDER BY中使用不到字段,不要设置索引
2、数据量小的表最好不要使用索引
3、禁止给表中的每一列都建立单独的索引
4、有大量重复的列上不要建索引
5、不要对经常更新的表和频繁更新的字段创建索引
6、不建议用无序值作为索引
- 例如身份证、UUID、MD5、HASH、无序厂字符串等。
7、删除不再使用或者很少使用的索引
8、不要定义冗余或重复的索引
-
重复:primary key(id),index(id),unique index(id)
-
冗余:index(a,b,c),index(a,b),index(a),
-
重复的和冗余的索引会降低查询效率,因为MySQL查询优化器会不知道该使用哪个索引。