索引原理以及创建索引类型查询优化 - PHP

TOP

索引原理以及创建索引类型查询优化(三)

2019-08-23 00:39:33 【大中小】浏览:92次

s1 where id=123; 该sql命中了索引，但未覆盖索引。利用id=123到索引的数据结构中定位到该id在硬盘中的位置，或者说再数据表中的位置。但是我们select的字段为*，除了id以外还需要其他字段，这就意味着，我们通过索引结构取到id还不够，还需要利用该id再去找到该id所在行的其他字段值，这是需要时间的，很明显，如果我们只select id，就减去了这份苦恼，如下 select id from s1 where id=123; 这条就是覆盖索引了，命中索引，且从索引的数据结构直接就取到了id在硬盘的地址，速度很快

二、联合索引

三、索引合并

#索引合并：把多个单列索引合并使用

#分析：
组合索引能做到的事情，我们都可以用索引合并去解决，比如
create index ne on s1(name,email);#组合索引
我们完全可以单独为name和email创建索引

组合索引可以命中：
select * from s1 where name='egon' ;
select * from s1 where name='egon' and email='adf';

索引合并可以命中：
select * from s1 where name='egon' ;
select * from s1 where email='adf';
select * from s1 where name='egon' and email='adf';

乍一看好像索引合并更好了：可以命中更多的情况，但其实要分情况去看，如果是name='egon' and email='adf',
那么组合索引的效率要高于索引合并，如果是单条件查，那么还是用索引合并比较合理

三若想利用索引达到预想的提高查询速度的效果，我们在添加索引时，必须遵循以下原则

#1.最左前缀匹配原则，非常重要的原则，
create index ix_name_email on s1(name,email,)
- 最左前缀匹配：必须按照从左到右的顺序匹配
select * from s1 where name='egon'; #可以
select * from s1 where name='egon' and email='asdf'; #可以
select * from s1 where email='alex@oldboy.com'; #不可以
mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，
比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，
d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

#2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器
会帮你优化成索引可以识别的形式

#3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，
表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、
性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，
这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

#4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’
就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，
但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。
所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

最左前缀示范

mysql> select * from s1 where id>3 and name='egon' and email='alex333@oldboy.com' and gender='male';
Empty set (0.39 sec)

mysql> create index idx on s1(id,name,email,gender); #未遵循最左前缀
Query OK, 0 rows affected (15.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from s1 where id>3 and name='egon' and email='alex333@oldboy.com' and gender='male';
Empty set (0.43 sec)


mysql> drop index idx on s1;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index idx on s1(name,email,gender,id); #遵循最左前缀
Query OK, 0 rows affected (15.97 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from s1 where id>3 and name='egon' and email='alex333@oldboy.com' and gender='male';
Empty set (0.03 sec)

6. 最左前缀匹配
index(id,age,email,name)
#条件中一定要出现id(只要出现id就会提升速度)
id
id age
id email
id name

email #不行  如果单独这个开头就不能提升速度了
mysql> select count(*) from s1 where id=3000;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.11 sec)

mysql> create index xxx on s1(id,name,age,email);
Query OK, 0 rows affected (6.44 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  select count(*) from s1 where id=3000;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql>  select count(*) from s1 where name='egon';
+----------+
| count(*) |
+----------+
|   299999 |
+----------+
1 row in set (0.16 sec)

mysql>  select count(*) from s1 where email='egon3333@oldboy.com';
+------

首页上一页 1 2 3 4 5 下一页尾页 3/5/5
【大中小】【打印】【繁体】【投稿】【收藏】【推荐】【举报】【评论】【关闭】【返回顶部】

上一篇：[PHP] defunct僵尸进程	下一篇：PHP保存数组到数据库