mysql 索引

索引

• 思考：在图书馆中是如何找到一本书的？
• 一般的应用系统对比数据库的读写比例在10:1左右，而且插入操作和更新操作很少出现性能问题，遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，所以查询语句的优化显然是重中之重
• 当数据库中数据量很大时，查找数据会变得很慢
• 优化方案：索引
• 主键和唯一索引，都是索引，可以提高查询速度
  • 主键是数据物理存储的位置
  • 索引会单独创建一个目录，对应数据的位置
• 索引分单列索引和组合索引
  • 单列索引，即一个索引只包含单个列，一个表可以有多个单列索引
  • 组合索引，即一个索引包含多个列

语法

• 查看索引

show index from 表名;

　　

• 创建索引
  • 如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
  • 字段类型如果不是字符串，可以不填写长度部分

create index 索引名称 on 表名(字段名称(长度))

　　

• 删除索引：

drop index 索引名称 on 表名;

　　

缺点

• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件
• 建立索引会占用磁盘空间的索引文件

示例

创建测试表testindex

create table test_index(title varchar(10));

　　

向表中加入十万条数据

• 创建存储过程proc_test，在存储过程中实现插入数据的操作
• step1：定义分割符

delimiter //

　　

• step2：定义存储过程

create procedure proc_test()
begin
declare i int default 0;
while i<100000 do
insert into test_index(title) values(concat('test',i));
set i=i+1;
end while;
end //

　　

• step3：还原分割符

delimiter ;

　　

• 执行存储过程proc_test

call proc_test();

　　

查询

• 开启运行时间监测：

set profiling=1;

　　

• 查找第1万条数据test10000

select * from test_index where title='test10000';

　　

• 查看执行的时间：

show profiles;

　　

• 为表title_index的title列创建索引：

create index title_index on test_index(title(10));

　　

• 执行查询语句：

select * from test_index where title='test10000';

　　

• 再次查看执行的时间

show profiles;