Mysql 索引

最新推荐文章于 2024-04-28 15:55:05 发布

季布，

最新推荐文章于 2024-04-28 15:55:05 发布

阅读量114

点赞数 2

分类专栏： Mysql 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_47906106/article/details/120812671

版权

Mysql 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

索引

1. 索引的介绍
索引在MySQL中也叫做“键”，它是一个特殊的文件，它保存着数据表里所有记录的位置信息，更通俗的来说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。(索引表中会存一份数据及数据的位置，索引表会比被设索引字段所占的磁盘空间大，索引查询效率比原表查询快，因为索引中可以使用不同的算法提高查询的效率。比如BTree:二分查找 Hash:映射关系)

应用场景:
当数据库中数据量很大时并且数据不会经常变更主要用以查找数据为主(因为一旦数据变了，其他的数据位置也会发生变化)，查找数据会变得很慢，我们就可以通过索引来提高数据库的查询效率。

如果给分数这个字段创建索引，那就得重新创建一个索引表用以记录分数的位置信息

2. 索引的使用

查看表中已有索引:

show index from 表名;

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

索引的创建:

-- 创建索引的语法格式
-- alter table 表名 add index 索引名[可选](列名, ..)
-- 给name字段添加索引
alter table classes add index my_name (name);

说明:
索引名不指定，默认使用字段名
在这里插入图片描述

索引的删除:

-- 删除索引的语法格式
-- alter table 表名 drop index 索引名
-- 如果不知道索引名，可以查看创表sql语句
show create table students;
alter table students drop index my_name;

在这里插入图片描述

3. 案例-验证索引查询性能

创建测试表testindex:

create table test_index(title varchar(10));

向表中插入十万条数据:

from pymysql import connect

def main():
    # 创建Connection连接
    conn = connect(host='localhost',port=3306,database='python',user='root',password='mysql',charset='utf8')
    # 获得Cursor对象
    cursor = conn.cursor()
    # 插入10万次数据
    for i in range(100000):
        cursor.execute("insert into test_index values('ha-%d')" % i)
    # 提交数据
    conn.commit()

if __name__ == "__main__":
    main()

验证索引性能操作：

-- 开启运行时间监测：
set profiling=1;
-- 查找第1万条数据ha-99999
select * from test_index where title='ha-99999';
-- 查看执行的时间：
show profiles;
-- 给title字段创建索引：
alter table test_index add index (title);
-- 再次执行查询语句
select * from test_index where title='ha-99999';
-- 再次查看执行的时间
show profiles;

在这里插入图片描述
可以看到使用索引查询的效率明显比未使用索引的查询效率高

4. 联合索引

联合索引又叫复合索引，即一个索引覆盖表中两个或者多个字段，一般用在多个字段一起查询的时候。

-- 创建teacher表
create table teacher
(
    id int not null primary key auto_increment,
    name varchar(10),
    age int
);

-- 创建联合索引
alter table teacher add index (name,age);

联合索引的好处:
减少磁盘空间开销，因为每创建一个索引，其实就是创建了一个索引文件，那么会增加磁盘空间的开销。

5. 联合索引的最左原则

在使用联合索引的时候，我们要遵守一个最左原则,即index(name,age)支持 name 、name 和 age 组合查询,而不支持单独 age 查询，因为没有用到创建的联合索引。(也就是说使用联合索引查询的时候一定要包含左边的索引)

最左原则示例:

-- 下面的查询使用到了联合索引
select * from stu where name='张三' -- 这里使用了联合索引的name部分
select * from stu where name='李四' and age=10 -- 这里完整的使用联合索引，包括 name 和 age 部分 
-- 下面的查询没有使用到联合索引
select * from stu where age=10 -- 因为联合索引里面没有这个组合，只有 name | name age 这两种组合