mysql索引字段in 效率 mysql索引速度

转载

mob6454cc798a0c 2024-08-30 07:11:48

文章标签 mysql索引字段in 效率 mysql email能索引吗 mysql 字段名字段 文章分类 MySQL 数据库

现实操作里，读写操作中读的比例往往远高于写。我们需要更快地查到所想查看的数据，所以要用索引来优化(加速查询)。

首先要明确，索引在mysql里是键，查询会有key。索引如同字典里的目录、音序表。

其次，明确磁盘里的IO和预读。一次IO约为9ms，包括寻道时间(磁臂移动到指定磁道所花的时间，一般5ms以下)、旋转延迟(60/7200/2=4.17ms)、传输时间(磁盘读出或写入的时间，零点几秒，相比较可以忽略)。一次IO的时间足以执行几百万条指令。

b+树的概念：磁盘块，磁盘块内包含几个数据项和指针，非叶子节点不存储真实的数据。

普通索引INDEX：加速查找

唯一索引：-主键索引PRIMARY KEY：加速查找+约束(不为空、不能重复)-唯一索引UNIQUE:加速查找+约束(不能重复)

联合索引：-PRIMARY KEY(id,name):联合主键索引-UNIQUE(id,name):联合唯一索引-INDEX(id,name):联合普通索引

常用索引

#方法一：创建表时
CREATE TABLE 表名 (
字段名1 数据类型 [完整性约束条件…],
字段名2 数据类型 [完整性约束条件…],
[UNIQUE| FULLTEXT | SPATIAL ] INDEX |KEY
[索引名] (字段名[(长度)] [ASC|DESC])
);#方法二：CREATE在已存在的表上创建索引
CREATE [UNIQUE | FULLTEXT |SPATIAL ] INDEX 索引名
ON 表名 (字段名[(长度)] [ASC|DESC]) ;#方法三：ALTER TABLE在已存在的表上创建索引
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT |SPATIAL ] INDEX
索引名 (字段名[(长度)] [ASC|DESC]) ;#删除索引：DROP INDEX 索引名 ON 表名字;
创建/删除索引的语法
测试索引

#1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
declare i int default1;while(i<3000000)do
insert into s1 values(i,'egon','male',concat('egon',i,'@oldboy'));
set i=i+1;
endwhile;
END$$#$$结束

delimiter ; #重新声明分号为结束符号

#3. 查看存储过程
show create procedure auto_insert1\G#4. 调用存储过程
call auto_insert1();

准备阶段

有大量数据的情况下，建索引会很慢，也占硬盘空间，但是建完后查询速度会加快。要注意innodb和myisam的不同，innodb的索引存在数据的文件里。而myisam表的索引有单独的索引文件.myi。

本人电脑，实际操作中：like虽然也是精确的，但是比where中的=要慢很多。

1.范围问题：索引情况下，范围小，速度快，范围大，速度慢，其中包括大于等于不等于、between、like。(本人电脑，like即便精确，速度明显不如小范围，但是他们时间几乎为0)

like里的%放头放尾区别大。2.尽量选区分度高的列作为索引，区分度公式：count(distinct col)/count(*)。3.=和in可以乱序。4.索引不能参与计算。5.and和or

and会被优化，先找区分度高的。6.最左前缀匹配原则，非常重要的原则，对于组合索引mysql会一直向右匹配直到遇到范围查询(>、 3 and d = 4如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。7.其他情况

使用函数

类型不同

排序条件#排序条件为索引，则select字段必须也是索引字段，否则无法命中
order by
select namefroms1 order by email desc;
当根据索引排序时候，select查询的字段如果不是索引，则速度仍然很慢
select emailfroms1 order by email desc;
特别的：如果对主键排序，则还是速度很快：
select* fromtb1 order by nid desc;-组合索引最左前缀
如果组合索引为：(name,email)
nameand email --命中索引
name--命中索引
email--未命中索引- count(1)或count(列)代替count(*)在mysql中没有差别了- create index xxxx on tb(title(19)) #text类型，必须制定长

注意事项：- 避免使用select *

- count(1)或count(列) 代替 count(*)-创建表时尽量时 char 代替 varchar-表的字段顺序固定长度的字段优先-组合索引代替多个单列索引(经常使用多个条件查询时)-尽量使用短索引- 使用连接(JOIN)来代替子查询(Sub-Queries)-连表时注意条件类型需一致- 索引散列值(重复少)不适合建索引，例：性别不适合

小总结

自我总结，以三百万数据为例：

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

联合索引，gender打头。1.如果select里没有gender，必慢。

gender和id组合(id前有email，所以无论如何，id都会慢！！！但是，如果索引gender的值不存在，也是瞬间出结果！！这应该是mysql的优化结果！！！)

mysql> select * from s1 where gender='f' and id='1'; #快
Empty set (0.00sec)
mysql> select * from s1 where gender='f' and id>'1'; #快
Empty set (0.00sec)
mysql> select * from s1 where gender='male' and id>'2999990'; #范围小，却慢
+---------+------+--------+--------------------+
| id | name | gender | email |
+---------+------+--------+--------------------+
| 2999991 | egon | male | egon2999991@oldboy |
| 2999992 | egon | male | egon2999992@oldboy |
| 2999993 | egon | male | egon2999993@oldboy |
| 2999994 | egon | male | egon2999994@oldboy |
| 2999995 | egon | male | egon2999995@oldboy |
| 2999996 | egon | male | egon2999996@oldboy |
| 2999997 | egon | male | egon2999997@oldboy |
| 2999998 | egon | male | egon2999998@oldboy |
| 2999999 | egon | male | egon2999999@oldboy |
+---------+------+--------+--------------------+
9 rows in set (1.70sec)
mysql> select * from s1 where gender='f' and id>'2999990'; #但是gender不存在，范围小，却快
Empty set (0.00sec)
mysql> select count(id) from s1 where gender='f' and id>'2999990';+-----------+
| count(id) |
+-----------+
| 0 |
+-----------+
1 row in set (0.00sec)
mysql> select count(id) from s1 where gender='male' and id>'2999990'; #gender存在，id小范围，依旧慢
+-----------+
| count(id) |
+-----------+
| 9 |
+-----------+
1 row in set (1.58 sec)
1.参考练习gender和id
gender和email的组合(email就是gender后面的，所以，快，所以没测gender值不存在的情况)

mysql> select count(id) from s1 where gender='male' order by email limit 3; #写了一个搞笑的句子，计算的所有id，在最后限制的却是email

+-----------+
| count(id) |
+-----------+
| 2999999 |
+-----------+
1 row in set (1.50sec)
mysql> select email from s1 where gender='male' order by email limit 3; #排序email，很快
+--------------------+
| email |
+--------------------+
| egon1000000@oldboy |
| egon1000001@oldboy |
| egon1000002@oldboy |
+--------------------+
3 rows in set (0.02sec)
mysql> select email from s1 where gender='male' order by email limit 3; #重新输了一次，更快，缓存？
+--------------------+
| email |
+--------------------+
| egon1000000@oldboy |
| egon1000001@oldboy |
| egon1000002@oldboy |
+--------------------+
3 rows in set (0.00sec)
mysql> select id from s1 where gender='male' order by email limit 3; #排email展示id并不慢！！！
+---------+
| id |
+---------+
| 1000000 |
| 1000001 |
| 1000002 |
+---------+
3 rows in set (0.00sec)
mysql> select id from s1 where gender='male' and email = 'egon20@oldboy'; #快得很
+------+
| id |
+------+
| 20 |
+------+
1 row in set (0.00sec)
mysql> select id from s1 where gender='male' and email < 'egon1000003@oldboy'; #不会出现id那种，除了gender自我了断怎么都慢
+---------+
| id |
+---------+
| 1000000 |
| 1000001 |
| 1000002 |
+---------+
3 rows in set (0.00sec)
mysql> select id from s1 where gender='male' and email between 'egon1000003@oldboy' and 'egon1000006@oldboy';; #后面的报错是因为多写了一个；，还是检测email的速度而已
+---------+
| id |
+---------+
| 1000003 |
| 1000004 |
| 1000005 |
| 1000006 |
+---------+
4 rows in set (0.00sec)

ERROR:

No query specified#

2.参考练习gender和email

之前都是gender在前的组合，现在打乱顺序，并且同时混入id和email

mysql> select id from s1 where email between 'egon1000003@oldboy' and 'egon1000006@oldboy';+---------+
| id |
+---------+
| 1000003 |
| 1000004 |
| 1000005 |
| 1000006 |
+---------+
4 rows in set (1.28sec)
mysql> select id from s1 where email between 'egon1000003@oldboy' and 'egon1000006@oldboy' and gender='male';+---------+
| id |
+---------+
| 1000003 |
| 1000004 |
| 1000005 |
| 1000006 |
+---------+
4 rows in set (0.00sec)
mysql> select id from s1 where email between 'egon1000003@oldboy' and 'egon1000006@oldboy' and gender='male' and id=1000003;+---------+
| id |
+---------+
| 1000003 |
+---------+
1 row in set (0.00sec)
mysql> select id from s1 where gender='male' and id=1000003 and email between 'egon1000003@oldboy' and 'egon1000006@oldboy';+---------+
| id |
+---------+
| 1000003 |
+---------+
1 row in set (0.00sec)
mysql> select id from s1 where gender='male' and id>1000003 and email between 'egon1000003@oldboy' and 'egon1000006@oldboy'; #不受id插在email的影响，就是快
+---------+
| id |
+---------+
| 1000004 |
| 1000005 |
| 1000006 |
+---------+
3 rows in set (0.00sec)
mysql> select id from s1 where gender='male' and id=1000003 and email > 'egon1000003@oldboy'; #从gender可以得出email的顺序
Empty set (1.45sec)
mysql> select id from s1 where gender='male' and id=1000003 and email > 'egon1000001@oldboy'; #由此辅佐得出，还是先走的email，不然凭借id可以优先缩小范围。
+---------+
| id |
+---------+
| 1000003 |
+---------+
1 row in set (1.52 sec)

3.参考练习小混乱

最后加入name的干扰

mysql> select id from s1 where gender='fmale' and id=1000003 and email > 'egon1000003@oldboy'; #对上面的小回应
Empty set (0.00sec)
mysql> select id from s1 where gender='fmale' and name='egon' and id=1000003 and email > 'egon1000003@oldboy'; #从这开始更清晰地验证语句的顺序的无用(除非有重要语句缺——区分度高的语句)
Empty set (0.00sec)
mysql> select id from s1 where gender='male' and name='egon' and id=1000003 and email > 'egon1000003@oldboy'; #因为email的过程太长，换成小于号可破
Empty set (1.50sec)
mysql> select id from s1 where gender='male' and id=1000003 and email > 'egon1000003@oldboy' and name='ego'; #同上
Empty set (1.49sec)
mysql> select id from s1 where gender='male' and id=1000003 and email ='egon1000003@oldboy' and name='ego'; #区分度高的都快(说明范围缩得很小了)，其他都快
Empty set (0.00sec)
mysql> select id from s1 where gender='male' and id=1000003 and email ='egon1000003@oldboy' and name='egon';+---------+
| id |
+---------+
| 1000003 |
+---------+
1 row in set (0.00sec)
mysql> select id from s1 where name='egon' and id=1000003 and email='egon1000003@oldboy' and gender='male';+---------+
| id |
+---------+
| 1000003 |
+---------+
1 row in set (0.00 sec)

4.参考练习大混乱

如有其他，再添加。

mysql> select count(id) from s1 where name='egon' and id=1000003 and email='egon1000003@oldboy' or gender='male';
ERROR1317 (70100): Query execution was interrupted #百度说是查询超时，有说闲置超时，后面没有调整，重新输入，没有报错
mysql> select count(*) from s1 where name='egon' and id=1000003 and email='egon1000003@oldboy' or gender='male';+----------+
| count(*) |
+----------+
| 2999999 |
+----------+
1 row in set (1.36sec)
mysql> select count(id) from s1 where name='egon' and id=1000003 and email='egon1000003@oldboy' or gender='male';+-----------+
| count(id) |
+-----------+
| 2999999 |
+-----------+
1 row in set (1.38 sec)

小报错

mysql里where 语句里面如果带有or条件, myisam表能用到索引， innodb不行。

mysql版本5.5.24测试不成功

mysql版本5.5.35innodb测试不成功。

如果有or，试着用in

mysql> select id from s1 where gender='male' and name='egon' and id=1000003 or email='egon1000003@oldboy';+---------+
| id |
+---------+
| 1000003 |
+---------+
1 row in set (1.34sec)
mysql> select id from s1 where gender='male' and name='egon' and email='egon1000003@oldboy' or id=1000000;+---------+
| id |
+---------+
| 1000000 |
| 1000003 |
+---------+
2 rows in set (1.69sec)
mysql> select id from s1 where gender='male' and email='egon1000003@oldboy' or id=1000000;+---------+
| id |
+---------+
| 1000000 |
| 1000003 |
+---------+
2 rows in set (1.46sec)
mysql> select id from s1 where gender='male' and email='egon1000003@oldboy' and id=1000000;
Empty set (0.00sec)
mysql> select id from s1 where gender='male' and email='egon1000003@oldboy' and id=1000003;+---------+
| id |
+---------+
| 1000003 |
+---------+
1 row in set (0.00sec)
mysql> select id from s1 where gender='male' and email='egon1000003@oldboy' or id=1000003 ; #和上面明显的对比，用or即慢(联合索引的情况下)，即便顺序不乱
+---------+
| id |
+---------+
| 1000003 |
+---------+
1 row in set (1.45 sec)
or
mysql> explain select id from s1 where gender ='male' and email='egon11@oldboy';+----+-------------+-------+------+---------------+--------+---------+-------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------------+------+--------------------------+
| 1 | SIMPLE | s1 | ref | ix_all | ix_all | 172 | const,const | 1 | Using where; Using index |
+----+-------------+-------+------+---------------+--------+---------+-------------+------+--------------------------+
1 row in set (0.00 sec)

试用explain

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。