整理自《高性能MySQL》
索引的类型
B-Tree索引：最常用的，有序的
CREATE TABLE People(
last_name varchar(50) not null,
first_name varchar(50) not null,
dob date not null,
gender enum('m','f') not null,
key(last_name,first_name,dob)
);
适用于B-Tree索引的查询类型
- 全值匹配：即where中包含last_name,first_name,dob的项
- 匹配最左的前缀：只使用第一个索引，如where中只包含last_name
- 匹配列前缀：只使用索引的部分信息，如where中包含以j开头的last_name
- 精确匹配某一列并范围匹配另一列：如第一列last_name全匹配，并匹配以J开头的first_name
- 只访问索引的数据
不适合于B-Tree索引的查询
- 如果不是按照索引的最左列开始查找，索引会无能为力。如不能查找first_name为Bob的人，也不能查找某个生日的人。因为这都不是最左索引列。
- 不能跳过索引的列，即不能使用last_name和dob作为条件来查询。
- 如果查询中有某个列的范围查询，则其右边的列都无法使用索引优化查找。
哈希索引：只有Memory支持，无序的
CREATE TABLE testhash(
first_name varchar(50) not null,
last_name varchar(50) not null,
key using hash(first_name)
)ENGINE=MEMORY;
select * from testhash;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Arjen | Lentz |
| Baron | Schwartz |
| Peter | Zaitsev |
| Vadim | Tkachenko |
+------------+-----------+
mysql> desc select * from testhash where first_name='Peter'\G;
*************************** 1. row ************
id: 1
select_type: SIMPLE
table: testhash
partitions: NULL
type: ref
possible_keys: first_name
key: first_name
key_len: 52
ref: const
rows: 2
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)Hash索引只储存对应的哈希值，结构紧凑，查找速度非常快。
不适用于Hash索引的查询类型
- 哈希索引只包含哈希值和行指针，不存储字段，所以不能使用索引中的值来避免读取行。但一般不影响性能
- 数据是无序的，无法用于排序。
- 哈希索引不支持部分索引列匹配查找，因为是根据全部内容来计算哈希的。
- 只支持等值比较查询包括：=，in()，<=>。不支持>或者<的范围查询。
- 访问哈希索引的数据非常快，除非哈希冲突很多。
- 如果哈希冲突很多，索引维护成本可能会很高O(n)
自适应哈希索引
当Innodb注意到某些索引值被使用得非常频繁时，他会在内存中基于B-Tree索引上再创建一个哈希索引。
自定义Hash索引：例如需要储存大量的URL时，根据URL进行查找使用B-Tree会占用很大的储存，查询如下SELECT id FROM urls WHERE url='www.baidu.com'。若删除这个url的索引，新增一个url_crc列并作为索引，就可以这样查询SELECT id FROM urls WHERE url='www.baidu.com' AND url_crc=CRC32('www.baidu.com');。MYSQL优化器会使用url_crc作为索引来查询，速度会非常快。
写一个触发器来维护索引
create table pseuohash(
id int unsigned not null auto_increment,
url varchar(255) not null,
url_crc int unsigned not null default 0,
primary key(id)
);
delimiter //
create trigger pseuohash_crc_ins
before insert on pseuohash
for each row begin
set new.url_crc=crc32(new.url);
end;
//
create trigger pseuohash_crc_upd
before update on pseuohash
for each row begin
set new.url_crc=crc32(new.url);
end;
//
delimiter ;
insert into pseuohash(url) values
('http://www.baidu.com');
select * from pseuohash;
+----+----------------------+------------+
| id | url | url_crc |
+----+----------------------+------------+
| 1 | http://www.baidu.com | 3500265894 |
+----+----------------------+------------+注意尽量不要使用SHA1()和MD5()作为哈希函数，因为计算出来的值太强了。
此外，where语句中还要有常量值，不能仅有Hash，因为哈希冲突十分常见。看看生日悖论。
索引的优点
索引可以大大减少服务器需要扫描的数据量
索引可以帮助服务器避免排序（B-Tree）
索引可以将随机IO变成顺序IO
高性能索引策略
1.使用独立的列
即索引列不是表达式的一部分或函数的参数。我们应该养成简化WHERE条件的习惯。
//错误的示范1
SELECT actor_id FROM actor
WHERE actor_id + 1 = 5;
//错误的示范2
SELECT ... FROM ...
WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10;2.前缀索引和索引选择性
前缀索引：对于某些很长的字符串，只使用前缀作为索引。
索引选择性：不重复的索引值与记录总数的比值。该比值越大越好，最大为1。
在上述两者中权衡：选择足够长的前缀以保证高选择性，并不能太长以节省空间。
mysql> select count(*) cnt ,city
-> from city_demo
-> group by city
-> order by cnt desc
-> limit 10;
+------+-------------+
| cnt | city |
+------+-------------+
| 1203 | London |
| 861 | Jedda |
| 827 | Bellevue |
| 806 | Lhokseumawe |
| 800 | Blumenau |
| 785 | Merlo |
| 777 | Salamanca |
| 775 | Maring |
| 775 | Pereira |
| 770 | Hiroshima |
+------+-------------+
mysql> select count(*) cnt,left(city,3) pref
-> from city_demo
-> group by pref
-> order by cnt desc
-> limit 10;
+------+------+
| cnt | pref |
+------+------+
| 8494 | San |
| 3750 | Cha |
| 3256 | Sal |
| 3162 | Sou |
| 3100 | Tan |
| 3049 | al- |
| 2771 | Kam |
| 2713 | Hal |
| 2573 | Sha |
| 2503 | Bat |
+------+------+
//看看选择性的情况，越大越好，但也不能太长。
mysql> 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,
-> count(distinct left(city,8))/count(*) as sel8
-> from city_demo;
+--------+--------+--------+--------+--------+--------+
| sel3 | sel4 | sel5 | sel6 | sel7 | sel8 |
+--------+--------+--------+--------+--------+--------+
| 0.0012 | 0.0015 | 0.0015 | 0.0015 | 0.0016 | 0.0016 |
+--------+--------+--------+--------+--------+--------+
1 row in set (0.70 sec)
//增加前缀索引
alter table city_demo add key(city(7));3.多列索引
单列索引的局限性：在早期版本中会使用全表扫描，但是在更新后，可见优化器使用了UNION
desc select film_id, actor_id from sakila.film_actor
where actor_id=1 or film_id=1;
******************* 1. row *******************
id: 1
select_type: SIMPLE
table: film_actor
partitions: NULL
type: index_merge
possible_keys: PRIMARY,idx_fk_film_id
key: PRIMARY,idx_fk_film_id
key_len: 2,2
ref: NULL
rows: 29
filtered: 100.00
Extra: Using union(PRIMARY,idx_fk_film_id); Using where
1.当出现服务器对多个索引做相交操作（多个AND条件）时，通常意味着需要一个包含相关列的多列索引，而不是独立的单列索引。
2.当服务器需要对多个索引做联合（多个OR条件）时，通常耗费大量CPU和内存在算法的缓存、排序和合并操作。特别当有些索引的选择性不高。
3.最重要的是，优化器不会把这些计算到“查询成本”中，优化器只关心随机页面读取。这会使得查询的成本被低估，导致该执行计划还不如全表扫描。
4.选择合适的索引列顺序
select * from payment where staff_id = 2 and customer_id = 584;
上面的语句是应该变换一下查询顺序呐？还是创建一个（staff_id, customer_id）索引呐？
select sum(staff_id = 2),sum(customer_id=584) from payment; +-------------------+----------------------+ | sum(staff_id = 2) | sum(customer_id=584) | +-------------------+----------------------+ | 7992 | 30 | +-------------------+----------------------+
按照经验法则，应该将customer_id放在前面，因为数量更小。之后看看customer_id的条件，对应的staff_id的选择性。
select count(distinct staff_id)/count(*) staff_id_selectivity, count(distinct customer_id)/count(*) customer_id_selectivity, count(*) from payment; +----------------------+-------------------------+----------+ | staff_id_selectivity | customer_id_selectivity | count(*) | +----------------------+-------------------------+----------+ | 0.0001 | 0.0373 | 16049 | +----------------------+-------------------------+----------+
根据上述的结果，选择高的customer_id更适合作为第一项。
5.聚簇索引
这是一种数据储存方式，InnoDB的聚簇索引在同一个结构中保存了B-Tree索引和数据行，会通过主键聚集数据。如果没有主键，InnoDB会选择一个唯一的非空索引代替。如果再没有这种索引，会隐式定义一个主键作为聚簇索引。
优点
- 可以将相关数据保存在一起，如将同一个用户id的邮件保存在一起，减少了磁盘IO的次数。
- 数据访问更快。
- 使用覆盖索引扫描的查询可以直接使用页节点的主键值。
缺点
- 最大限度的提高IO密集型应用的性能，但若放在内存中就没有优势了
- 插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。但如果不是，最好用
OPTIMIZE TABLE来优化一下表。 - 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。
- 基于聚簇索引的表在插入新行，或者主键被更新导致要移动行的时候，可能面临”页分裂”的问题。当行的主键值要求必须将这一行插入到某个已满的页中，存储引擎会将该页分裂成两个页来容纳该行，这就是一次页分裂。会导致占用更多磁盘空间。
- 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏时，或者由于页分裂导致数据储存不连续。
- 二级索引（非聚簇索引）可能比想象中大，因为在二级索引的叶子节点包含了引用行的主键值。
- 二级索引访问需要两次索引查找，不是一次。因为二级索引的叶子节点获得对应的主键值，再根据这个值去聚簇索引中查找行。
6.覆盖索引
如果索引的叶子节点中已经包含要查询的数据，那么还有什么必要去回表查询呐？如果一个索引包含了（或覆盖）需要查询的所有字段的值，称之为覆盖索引。
优点：
- 索引条目远小于数据行代销，只需要读取索引，IO开销更小。
- 因为索引是按照列值顺序储存的，所以对于IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少得多。
- 如MyISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。这会造成严重性能问题
- InnoDB有聚簇索引，且二级索引在叶子节点中保存了行的主键值，所以如果二级主键能覆盖查询，则可以避免对主键的二次查询。
desc select store_id,film_id
from inventory\G;
******************** 1. row *********************
id: 1
select_type: SIMPLE
table: inventory
partitions: NULL
type: index
possible_keys: NULL
key: idx_store_id_film_id
key_len: 3
ref: NULL
rows: 4581
filtered: 100.00
Extra: Using index
使用覆盖索引的列在Extra中会显示Using index
//未优化的版本
desc select * from products
where actor='SEAN CARREY'
and title like '%APOLLO%'\G;
//1.没有任何索引能覆盖这个查询，因为选择了所有列。
//2.不能再索引中执行LIKE操作。
//优化的版本
desc select * from products
join (
select prod_id from products
where actor='SEAN CARREY'
and title like '%APOLLO%'
) as t1
on (t1.prod_id=products.prod_id)\G;这查询语句看的一脸懵逼呀。这叫做延迟关联，因为延迟了对列的访问。在查询的第一阶段MySQL可以使用覆盖索引，在子查询中找到匹配的prod_id,然后根据这些prod_id在外层查询匹配需要的所有列值。
看一个使用实例
- 数据集1: Sean Carrey出演30000部作品，20000部包含Apollo
- 数据集2: Sean Carrey出演30000部作品，40部包含Apollo
- 数据集3: Sean Carrey出演50部作品，10部包含Apollo
| 数据集 | 原查询 | 优化的查询 |
|---|---|---|
| 1 | 每秒5次 | 每秒5次 |
| 2 | 每秒7次 | 每秒35次 |
| 3 | 每秒2400次 | 每秒2000次 |
1. 数据集1中返回的结果集过大，看不到优化的效果。
2. 数据集2经过索引过滤，只返回很少的结果集，优化效果明显，因为只要读取40行完整数据。
3. 数据集3，效率反而下降了，因为索引过滤时符合第一个条件的结果集已经很小，所以子查询带来的成本更高。
7.使用索引扫描来做排序
有两种方式可以生成有序的结果：通过排序操作；或者按索引顺序扫描；如果EXPLAIN出来的type列的值为“index”，则说明MySQL使用了索引扫描来做排序。
如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录都要回表查询一次对应的列。这样的话会比顺序的全表扫描慢，尤其在IO密集型的工作负载时。
只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（正序或倒序）都一样时，MySQL才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求；否则MySQL都需要执行排序操作，而无法利用索引排序。
mysql> desc select rental_id,staff_id from rental
-> where rental_date = '2005-05-25'
-> order by inventory_id,customer_id\G;
******************* 1. row *******************
id: 1
select_type: SIMPLE
table: rental
partitions: NULL
type: ref
possible_keys: rental_date
key: rental_date
key_len: 5
ref: const
rows: 1
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.04 sec)8.压缩（前缀压缩）索引
MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下能极大地提高性能。
对于CPU密集型应用，因为扫描需要随机查找，压缩索引使得MyISAM在索引查找上要慢好几倍。压缩索引的倒序扫描就更慢了。压缩索引需要在CPU内存资源与磁盘之间做权衡，压缩索引可能只需要十分之一大小的磁盘空间，如果是IO密集型应用，对某些查询带来的好处会比成本多得多。
可以在CREATE TABLE语句中指定PACK_KEYS参数来控制索引压缩的方式。
8.冗余和重复索引
重复索引指：相同的列中按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引，发现以后也应该马上移除。
create table test(
ID int not null primary key,
A int not null,
B int not null,
unique(id),
index(id)
)engine=InnoDB;上述的语句在创建了三个重复的索引，因为唯一限制和主键限制都是以索引实现的。
冗余索引：如果创建了索引(A,B), 再创建了索引(A)就是冗余索引，因为只是前一个索引的前缀索引。但是创建一个索引(B)或者索引(B,A)则不是。
9.索引和锁
索引可以让查询锁定更少的行。如果你的查询从不访问那些不需要的行，那么就会锁定更少的行。这对性能都有好处。
InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB的行数。但这只有当InnoDB在储存引擎层能够过滤掉所有不需要的行时才有效。
//终端1
start transaction;
select actor_id from actor
where actor_id<5 and actor_id <> 1
for update;
+----------+
| actor_id |
+----------+
| 2 |
| 3 |
| 4 |
+----------+
desc select actor_id from actor
where actor_id < 5 and actor_id <> 1
for update\G;
*************** 1. row ***************
id: 1
select_type: SIMPLE
table: actor
partitions: NULL
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: NULL
rows: 4
filtered: 100.00
Extra: Using where; Using index
//终端2
begin;
select actor_id from actor
where actor_id = 1
for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
