高性能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;

这个也不能应用索引

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

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

-- 示范以列的部分前缀来建索引，首先找出数量最多的列和最常查询的列
CREATE TABLE sakila.city_demo(city VARCHAR(50) NOT NULL);
INSERT INTO sakila.city_demo(city) SELECT city FROM sakila.city;
-- Repeat the next statement five times:
INSERT INTO sakila.city_demo(city) SELECT city FROM sakila.city_demo;
-- Now randomize the distribution (inefficiently but conveniently):
UPDATE sakila.city_demo
SET city = (SELECT city FROM sakila.city ORDER BY RAND() LIMIT 1);

SELECT COUNT(*) AS cnt, city FROM sakila.city_demo GROUP BY city ORDER BY cnt DESC LIMIT 10;

SELECT COUNT(*) AS cnt, LEFT(city, 3) AS pref FROM sakila.city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 10;

SELECT COUNT(*) AS cnt, LEFT(city, 7) AS pref FROM sakila.city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 10;

--to find the full column’s selectivity:
SELECT COUNT(DISTINCT city)/COUNT(*) FROM sakila.city_demo;

-- to find the selectivity of several prefix lengths in one query:

SELECT COUNT(DISTINCT LEFT(city, 3))/COUNT(*) AS sel3,
COUNT(DISTINCT LEFT(city, 4))/COUNT(*) AS sel4,
COUNT(DISTINCT LEFT(city, 5))/COUNT(*) AS sel5,
COUNT(DISTINCT LEFT(city, 6))/COUNT(*) AS sel6,
COUNT(DISTINCT LEFT(city, 7))/COUNT(*) AS sel7
FROM sakila.city_demo;

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

-- 选择正确的列顺序作索引
SELECT SUM(staff_id = 2), SUM(customer_id = 584) FROM paymentG
SELECT SUM(staff_id = 2) FROM payment WHERE customer_id = 584G
SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
COUNT(*)
FROM paymentG

ALTER TABLE payment ADD KEY(customer_id, staff_id);

SELECT COUNT(DISTINCT threadId) AS COUNT_VALUE
FROM Message
WHERE (groupId = 10137) AND (userId = 1288826) AND (anonymous = 0)
ORDER BY priority DESC, modifiedDate DESC

SELECT COUNT(*), SUM(groupId = 10137),
SUM(userId = 1288826), SUM(anonymous = 0)
FROM MessageG

5.等。。。。