MySQL基础学习笔记——索引

索引

• 介绍：索引在MySQL中也叫做“键”，它是一种特殊的文件，它保存着数据表里所有记录的位置信息，更通俗的来说，数据库索引好比是一本书前的目录，能加快数据库的查询速度。
• 注意：index是MySQL中的关键字，不要把key定义为index。
• 应用场景：当数据库中数据量大时，查找数据会变得很慢，我们就可以通过索引来提高数据库的查询效率。

查看表中已有索引

• 说明：主键列会自动创建索引

show index from 表名;

创建索引

• 说明：索引名不指定，默认使用字段名

# 语法
alter table 表名 add index [索引名](列名, ...)
# 给name字段添加索引，命名方式习惯：i_字段名（i表示index）
alter table gongfu add index i_name(name);

删除索引

# 语法
alter table 表名 drop index 索引名;

# 如果不知道说明可以先查看索引
show create table 表名;
# 或者
shwo index from 表名;

# 删除name的索引
show index form hero;
alter table hero drop index i_name;

验证索引查询的性能

# 创建测试表：testindex表
create table(title varchar(10));

# 利用数据库编程想数据库中插入50万条数据
import pymysql
conn = pymysql.connect(host="localhost", port=3306, user="root", 
                password="1237894560", database="heima",charset="utf8")
cursor = conn.cursor()
sql = "insert into testindex value(%s)"
try:
    for i in range(500000):
        cursor.execute(sql, ['ha-' + str(i)])
    conn.commit()
except Exception as err:
    print("SQL操作失败，开始回滚", err)
    conn.rollback()
finally:
    cursor.close()
    conn.close()   

• 验证索引性能的操作

# 开启运行时间检测
set profiling = 1;

# 查找第50万条数据ha-499999
select * from testindex where title="ha-499999";
# 查看执行时间
show profiles;

# 给title添加索引
alter table testindex add index i_title(title);
# 再次查找第50万条数据ha-499999
select * from testindex where title="ha-499999";
# 再次查看执行的时间
show profiles;

• 说明：很明显查询速度快了83倍左右（只是我的电脑上），如果没有index，查询会进行全盘扫描，直到找到结果，复杂度最大为n。