索引的增删改成查

摘要:
1创建表时就指定索引createtablet1(idint,namechar(5),uniquekeynui_name(name),primarykey(id));createtablet2(idint,namechar(5),indexidx_name(name));2在创建完表后为其添加索引createtablet3(idint,namechar(5));createindeidx_nameo

1 创建表时就指定索引

create table t1(

id int,

name char(5),

unique key nui_name(name),

primary key (id)

);

create table t2(

id int,

name char(5),

index idx_name(name)

);

2 在创建完表后为其添加索引

create table t3(

id int,

name char(5)

);

create inde idx_name on t3(name);

alter table t3 add index idx_id(id);

alter table t3 add primary key(id);

查看
mysql> show create table t3;
+-------+-------------------------------------
| Table | Create Table
+-------+-------------------------------------
| t3 | CREATE TABLE `t3` (
`id` int(11) DEFAULT NULL,
`name` char(5) DEFAULT NULL,
KEY `idx_name` (`name`),
KEY `idx_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------
1 row in set (0.00 sec)

删除
drop index idx_id on t3;

alter table t3 drop primary key;

#加索引可以加快查询效率,但是会降低写的效率
mysql> select count(*) from s1 where id=100000;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.27 sec)

mysql> create index idx_id on s1(id); #速度很慢
Query OK, 0 rows affected (3.27 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select count(*) from s1 where id=100000;#查询条件中的字段是索引字段,速度很快
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from s1 where name='egon'; #查询条件中的字段是非索引字段,速度仍然很慢
+----------+
| count(*) |
+----------+
| 793686 |
+----------+
1 row in set (0.30 sec)

select count(*) from s1 where name='egon' and gender ='male' and id > 3333 and email ='xxx';
对于多个and条件,会依次往右找到一个区分度高的索引字段,加速查询
对于多个or条件,会依次从左到右判断
create index idx_xx on s1(name,gender,id,email); #无法加速查询

create index idx_xx on s1(name,gender,email,id); #可以加速查询

create index idx_name on s1(name);
create index idx_id on s1(id);
select count(*) from s1 where name='egon' and id=3000;
select count(*) from s1 where name='egon' and id>3000;

select count(*) from s1 where name='egon' or id=3000;

#总结:正确使用索引的注意事项
1、应该选择区分度高的字段作为索引字段
2、范围问题(:>、>=、<、<=、!= 、between...and...、like、),范围过大,即便是有索引速度也会很慢
3、索引字段一定不能参与计算:select * from s1 where id*10 > 12000;
4、最左前缀匹配原则:
应该把范围查询字段往右放

create index idx_xx on s1(id,name,gender,email);
id name gender email
id gender email
id email

gender email

#可以匹配的条件是:
1、id=1 and name = 'egon' and gender='male' and email ='xxx';
2、id=1 and email ='xxx';
3、id=1 and name = 'egon' and email ='xxx';
4、name = 'egon' and email ='xxx';
5、name = 'egon' and gender='male' and email ='xxx';

select count(*) from s1 where id = 1 and name = 'egon' and gender ='male' and email = 'xxxx';
select count(*) from s1 where name = 'egon' and email ='xxx';
select count(*) from s1 where name = 'egon' and gender='male' and email ='xxx';

select count(*) from s1 where name = 'egon' and id>3 and gender='male' and email ='xxx';

create index idx_xx on s1(gender,email,name,id)
能够匹配条件中出现的字段如下
gender email name id/id email name gender
gender id
gender email
gender email id
gender

select count(*) from s1 where name = 'egon' and id>3 and gender='male' and email ='xxx';

select count(*) from s1 where name = 'egon' and id>3 and email ='xxx';
select count(*) from s1 where gender='male';
select count(*) from s1 where gender='xxx';

免责声明:文章转载自《索引的增删改成查》仅用于学习参考。如对内容有疑问,请及时联系本站处理。

上篇待人处世100个技巧选读Photoshop cs5 永久序列号下篇

宿迁高防,2C2G15M,22元/月;香港BGP,2C5G5M,25元/月 雨云优惠码:MjYwNzM=

相关文章

MySQL 三万字精华总结

写在之前:不建议那种上来就是各种面试题罗列,然后背书式的去记忆,对技术的提升帮助很小,对正经面试也没什么帮助,有点东西的面试官深挖下就懵逼了。 个人建议把面试题看作是费曼学习法中的回顾、简化的环节,准备面试的时候,跟着题目先自己讲给自己听,看看自己会满意吗,不满意就继续学习这个点,如此反复,好的offer离你不远的,奥利给 文章收录在 GitHub...

Oracle中游标的使用

一、 游标的概念:       游标是SQL的一个内存工作区,由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下,需要把数据从存放在磁盘的表中调到计算机内存中进行处理,最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高,否则频繁的磁盘数据交换会降低效率。   二、游标分类:       游标有两种类...

MySQL 百万级分页优化(Mysql千万级快速分页)(转)

http://www.jb51.net/article/31868.htm 以下分享一点我的经验 一般刚开始学SQL的时候,会这样写 复制代码 代码如下: SELECT * FROM table ORDER BY id LIMIT 1000, 10; 但在数据达到百万级的时候,这样写会慢死 复制代码 代码如下: SELECT * FROM t...

iScroll框架的使用和修改

引用 http://www.cnblogs.com/JoannaQ/p/3155873.html iScroll框架的使用和修改 iScroll 的诞生是因为手机 Webkit 浏览器(iPhone、iPod、Android 和 Pre)本身没有为固定宽度和高度的元素提供滚动内容的方法。这导致了很多网页使用 position:absolute 无法固定页...

mysql性能优化-慢查询分析、优化索引和配置【转】

一、优化概述 二、查询与索引优化分析 1性能瓶颈定位 Show命令 慢查询日志 explain分析查询 profiling分析查询 2索引及查询优化 三、配置优化 1)      max_connections 2)      back_log 3)      interactive_timeout 4)      key_buffer_size 5)  ...

修复数据库索引问题:删除索引以提升性能

在一个数据库上创建索引会给数据库带来负面影响。当对表执行插入、更新和删除操作时,您就会看到这个性能的负面影响。您对表每作一次修改,包含这些修改记录的索引都必须更新,以符合最新的修改。  使用过滤索引后,需要更新的索引变少了。然而,包含这些记录的索引仍然需要在记录修改时进行更新。   因为这些操作必须在每一次数据修改时进行,您应该把数据库中不用于查询数据的索...