高性能MySQL笔记-第5章Indexing for High Performance-004怎样用索引才高效

一、怎样用索引才高效

1.隔离索引列

MySQL generally can’t use indexes on columns unless the columns are isolated in the query. “Isolating” the column means it should not be part of an expression or be inside a function in the query.

如,以下的查询不能用actor_id索引

SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

这个也不能应用索引

1 SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10;

 

2.给长文本加上前缀索引

 1 -- 示范以列的部分前缀来建索引,首先找出数量最多的列和最常查询的列
 2 CREATE TABLE sakila.city_demo(city VARCHAR(50) NOT NULL);
 3 INSERT INTO sakila.city_demo(city) SELECT city FROM sakila.city;
 4 -- Repeat the next statement five times:
 5 INSERT INTO sakila.city_demo(city) SELECT city FROM sakila.city_demo;
 6 -- Now randomize the distribution (inefficiently but conveniently):
 7 UPDATE sakila.city_demo
 8 SET city = (SELECT city FROM sakila.city ORDER BY RAND() LIMIT 1);
 9 
10 SELECT COUNT(*) AS cnt, city FROM sakila.city_demo GROUP BY city ORDER BY cnt DESC LIMIT 10;
11 
12 SELECT COUNT(*) AS cnt, LEFT(city, 3) AS pref FROM sakila.city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 10;
13 
14 SELECT COUNT(*) AS cnt, LEFT(city, 7) AS pref FROM sakila.city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 10;
15 
16 --to find the full column’s selectivity:
17 SELECT COUNT(DISTINCT city)/COUNT(*) FROM sakila.city_demo;
18 
19 -- to find the selectivity of several prefix lengths in one query:
20 
21 SELECT COUNT(DISTINCT LEFT(city, 3))/COUNT(*) AS sel3,
22 COUNT(DISTINCT LEFT(city, 4))/COUNT(*) AS sel4,
23 COUNT(DISTINCT LEFT(city, 5))/COUNT(*) AS sel5,
24 COUNT(DISTINCT LEFT(city, 6))/COUNT(*) AS sel6,
25 COUNT(DISTINCT LEFT(city, 7))/COUNT(*) AS sel7
26 FROM sakila.city_demo;
27 
28 ALTER TABLE sakila.city_demo ADD KEY (city(7));

缺点:

Prefix indexes can be a great way to make indexes smaller and faster, but they have downsides too: MySQL cannot use prefix indexes for ORDER BY or GROUP BY queries, nor can it use them as covering indexes.
A common case we’ve found to benefit from prefix indexes is when long hexadecimal identifiers are used.

 

 

3.Multicolumn Indexes  

When you see an index merge in EXPLAIN , you should examine the query and table structure to see if this is really the best you can get. You can disable index merges with the optimizer_switch option or variable. You can also use IGNORE INDEX

 

4.Choosing a Good Column Order

 1 -- 选择正确的列顺序作索引
 2 SELECT SUM(staff_id = 2), SUM(customer_id = 584) FROM payment\G
 3 SELECT SUM(staff_id = 2) FROM payment WHERE customer_id = 584\G
 4 SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
 5 COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
 6 COUNT(*)
 7 FROM payment\G
 8 
 9 ALTER TABLE payment ADD KEY(customer_id, staff_id);
10 
11 SELECT COUNT(DISTINCT threadId) AS COUNT_VALUE
12 FROM Message
13 WHERE (groupId = 10137) AND (userId = 1288826) AND (anonymous = 0)
14 ORDER BY priority DESC, modifiedDate DESC
15 
16 SELECT COUNT(*), SUM(groupId = 10137),
17 SUM(userId = 1288826), SUM(anonymous = 0)
18 FROM Message\G

 

5.等。。。。

 

转载于:https://www.cnblogs.com/shamgod/p/5383255.html

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值