mysql索引提高查询速度

使用索引提高查询速度

1.前言

  在web开发中,业务模版,业务逻辑(包括缓存、连接池)和数据库这三个部分,数据库在其中负责执行SQL查询并返回查询结果,是影响网站速度最重要的性能瓶颈。本文主要针对Mysql数据库,在淘宝的去IOE(I 代表IBM的缩写,即去IBM的存储设备和小型机;O是代表Oracle的缩写,去Oracle数据库,采用Mysql和Hadoop代替;E是代表EMC2,去EMC2的设备性,用PC server代替EMC2),大量使用Mysql集群!而优化数据的重要一步就是索引的建立,对于Mysql出现的慢查询,可以用索引提升查询速度。索引用于快速找出在某个列中有一特定值的行,不使用索引,Mysql将全表扫描,从第一条记录开始,然后读完整个表直到找出相关的行。

2.Mysql索引类型及创建

索引相关知识:

PRI主键约束;

UNI唯一约束;

MUL可以重复。

1).主键索引

它是一种特殊的唯一索引,不允许为空。一般建表时同时创建主键索引:

CREATE TABLE user(
id int unsigned not null auto_increment,
name varchar(50) not null,
email varchar(40) not null,
primary key (id)
); 

2).普通索引

这是最基本的索引,没有任何限制:

create index idx_email on user(
email(20)
);
create index idx_name on user(
name(20)
);

mysql 支持索引前缀,一般姓名不超过20字符,所以建立索引限定20长度,节省索引文件大小

3).唯一索引

它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,列值的组合必须唯一。

CREATE UNIQUE INDEX idx_email ON user(
email
); 

4).组合索引

create table sb_man(
id int PRIMARY key auto_increment,
new_name char(30) not null,
old_name char(30) not null,
index name(new_name,old_name)
);
# name索引是一个对new_name和old_name的索引。

查询方法:
select * from sb_man where new_name='yu';
select * from sb_man where new_name='yu' and old_name='yu1';
提示:>>>>>>   组合索引是最左前缀创建, 所以不能用如下sql
select * from sb_man where old_name='yu1';   <<<< 错误

3.什么时候用索引

1.索引引用

在索引列上,除了上面提到的有序查找之外,数据库利用各种各样的快速定位技术,能够大大提高查询效率。特别是当数据量非常大,查询涉及多个表时,使用索引往往能使查询速度加快成千上万倍。

例如,有2个未索引的表t1、t2、分别只包含列c1、c2 每个表分别含有1000行数据组成,值为111的数值,然后设置三张表,不同的几个值,

(这里我是用pymysql 执行 while 创建的数据)

查找对应值相等行的查询如下所示。

在无索引的情况下处理此查询,必须寻找3个表所有的组合,以便得出与WHERE子句相配的那些行。

select c1,c2 from t1,t2 where c1!=c2 

 结果

查询过程>>

2.创建索引

在执行CREATE TABLE语句时可以创建索引,也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。

1.ALTER TABLE

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引

删除索引: alter table tab_name drop {index|key} index_name;
     alter table t1 drop index idx_c1;
添加索引: alter table t1 add index idx_c1(c1);
     alter table t2 add index idx_c2(c2);

 查询结果

虽然感觉没什么卵用。。。但是索引查询就是如此了。。。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏潘昌伟的专栏

用好 mysql 分区表

大数据时代,数据量趋于海量,mysql单表很难满足大数据场景的一些统计需求,用好分区表可以很好的解决很大一部分的问题。

2.4K10
来自专栏跟着阿笨一起玩NET

SQL Server通过整理索引碎片和重建索引提高速度

本文章转载:http://database.51cto.com/art/201108/282408.htm

66910
来自专栏一个爱吃西瓜的程序员

学习SQL【5】-数据更新

数据的更新处理大体可以分为插入(INSERT)、删除(DELETE)和更新(UPDATE)三类,此外,还会给大家介绍数据库中用来管理数据更新的重要概念—事务。 ...

37770
来自专栏杨建荣的学习笔记

MySQL和Oracle中唯一性索引的差别(r12笔记第83天)

今天在修复MySQL数据的时候,发现一个看起来“奇怪”的问题。 有一个表里存在一个唯一性索引,这个索引包含3个列,这个唯一性索引的意义就是通过这3个列...

37160
来自专栏各种机器学习基础算法

如何编写高性能sql语句

一、什么是执行计划? 1)执行计划 执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案,这个方案是由查询优化器自动分析产生的,比如一条SQL语句...

34660
来自专栏杨建荣的学习笔记

特殊的物化视图刷新 (r4笔记第77天)

现在有一个需求,某个环境中存在两个用户,一个用户中存在物化视图,另一个用户中存在源表,根据业务的需要,需要做一种特别的物化视图刷新。 ? 物化视图用户中的物化...

36870
来自专栏莫韵的专栏

MySQL 占用空间一键查询实践

大家可能经常会遇到DB满的情况 特别是大家共用DB的时候 这时候需要找到,哪个DB比较大,哪个表比较大,默认的方法你得一个一个的查询, 但这肯定不是偷懒程序员爱...

1.1K10
来自专栏杨建荣的学习笔记

当主键碰到NULL(r6笔记第64天)

主键和Null看似没有多大的关系,因为一般的主键设置都是not null,但是把两者结合起来,会有很多意想不到的情况,说是意想不到是因为结果不在预期范围,但是如...

36570
来自专栏游戏杂谈

Node.js调用mysql的存储过程

例子仅在windows下测试通过,没有放在linux下测试。如有问题,可以电邮给我~

22110
来自专栏微信公众号:Java团长

MySQL的索引是什么?怎么优化?

索引类似大学图书馆建书目索引,可以提高数据检索的效率,降低数据库的IO成本。MySQL在300万条记录左右性能开始逐渐下降,虽然官方文档说500~800w记录,...

10310

扫码关注云+社区

领取腾讯云代金券