• 在线客服

  • 扫描二维码
    下载博学谷APP

  • 扫描二维码
    关注博学谷微信公众号

  • 意见反馈

原创 MySQL中索引如何使用?有哪些优缺点?

发布时间:2022-02-11 11:12:31 浏览 999 来源:博学谷 作者:小谷

    MySQL中索引如何使用?有哪些优缺点?索引在MySQL中叫做“键”,当数据库中数据量很大时查找数据很慢,我们就可以通过索引来提高数据库的查询效率。

    MySQL中索引如何使用

    学习目标:能够写出创建索引的SQL语句。

    1. 索引的介绍

    索引在MySQL中也叫做“键”,它是一个特殊的文件,它保存着数据表里所有记录的位置信息,通俗的说数据库索引好比是一本书前面的目录,能加快数据库的查询速度。

    应用场景:当数据库中数据量很大时,查找数据会变得很慢,我们就可以通过索引来提高数据库的查询效率。

    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 classes;
    alter table classes 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 这两种组合
    

    说明:在使用联合索引的查询数据时候一定要保证联合索引的最左侧字段出现在查询条件里面,否则联合索引失效

    6. MySQL中索引的优点和缺点和使用原则

    优点:加快数据的查询速度

    缺点:创建索引会耗费时间和占用磁盘空间,并且随着数据量的增加所耗费的时间也会增加

    使用原则:

    1、通过优缺点对比,不是索引越多越好,而是需要自己合理的使用。

    2、对经常更新的表就避免对其进行过多索引的创建,对经常用于查询的字段应该创建索引,

    3、数据量小的表最好不要使用索引,因为由于数据较少,可能查询全部数据花费的时间比遍历索引的时间还要短,索引就可能不会产生优化效果。

    4、在一字段上相同值比较多不要建立索引,比如在学生表的"性别"字段上只有男,女两个不同值。相反的,在一个字段上不同值较多可是建立索引。

    7. 索引小结

    索引是加快数据库的查询速度的一种手段,创建索引使用: alter table 表名 add index 索引名[可选] (字段名, xxx);删除索引使用: alter table 表名 drop index 索引名。

    申请免费试学名额    

在职想转行提升,担心学不会?根据个人情况规划学习路线,闯关式自适应学习模式保证学习效果
讲师一对一辅导,在线答疑解惑,指导就业!

上一篇: 事务工作执行单元如何使用?其特性有哪些 下一篇: 闭包是什么?具体怎么使用?

相关推荐 更多

热门文章

  • 前端是什么
  • 前端开发的工作职责
  • 前端开发需要会什么?先掌握这三大核心关键技术
  • 前端开发的工作方向有哪些?
  • 简历加分-4步写出HR想要的简历
  • 程序员如何突击面试?两大招带你拿下面试官
  • 程序员面试技巧
  • 架构师的厉害之处竟然是这……
  • 架构师书籍推荐
  • 懂了这些,才能成为架构师
  • 查看更多

扫描二维码,了解更多信息

博学谷二维码