时间：2021-07-01 10:21:17 帮助过：10人阅读
.png)
图中根节点没有画出来。
B-Tree对索引列是顺序组织存储的，索引很适合查找范围数据。
B-Tree索引的限制
mysql> select id from url where url=‘http://www.mysql.com‘;
若删除原来url列上的索引，而新增一个被索引的url_crc列，使用crc32做哈希。就可以实现一个伪哈希索引；查询就变成下面的方式：
mysql> select id from url where url=‘http://www.mysql.com‘ -> and url_crc=crc32("http://www.mysql.com");
这样性能会提高很多。 当然这样实现的缺陷是需要维护哈希值，就是url改变对应哈希值也应该改变。可以手动维护，当然最好是使用触发器实现。 创建URL表
create table URL （ id int unsigned NOT NULL auto_increment, url varchar(255) NOT NULL, url_crc int unsigned NOT NULL DEFAULT 0, PRIMARY KEY (id), KEY (url_crc) ）;创建触发器：
delimiter // create trigger url_hash_crc_ins before insert on URL FOR EACH ROW BEGIN SET NEW.url_crc=crc32(NEW.url); END; // CREATE TRIGGER url_hash_crc_upd BEFORE UPDATE ON URL FOR EACH ROW BEGIN SET NEW.url_crc=crc32(NEW.url); END; // delimiter ; mysql> select * from URL; +----+-----------------------+------------+ | id | url | url_crc | +----+-----------------------+------------+ | 1 | htttp://www.mysql.com | 1727608869 | +----+-----------------------+------------+ 1 row in set (0.00 sec) mysql> insert into URL(url) values(‘htttp://www.‘); Query OK, 1 row affected (0.00 sec) mysql> select * from URL; +----+-----------------------+------------+ | id | url | url_crc | +----+-----------------------+------------+ | 1 | htttp://www.mysql.com | 1727608869 | | 2 | htttp://www. | 1196108391 | +----+-----------------------+------------+ 2 rows in set (0.00 sec) mysql> UPDATE URL SET url=‘http://www.baidu.com‘ where id=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from URL; +----+-----------------------+------------+ | id | url | url_crc | +----+-----------------------+------------+ | 1 | htttp://www.mysql.com | 1727608869 | | 2 | http://www.baidu.com | 3500265894 | +----+-----------------------+------------+ 2 rows in set (0.00 sec)
如果采用这种方式，不要使用SHA1()和MD5()作为哈希函数，应该这个函数计算出来的哈希值是非常长的字符串，会浪费大量空间，比较时页回更慢。 而如果数据表非常大，crc32()会出现大量的哈希冲突，而解决哈希冲突，可以在查询中增加url本身，进行进一步排除； 如下面查询就可以解决哈希冲突的问题：
mysql> select id from url where url=‘http://www.mysql.com‘ -> and url_crc=crc32("http://www.mysql.com");
空间数据索引（R-Tree） myisam 表支持空间索引，可以用作地理数据存储。 全文索引 全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。第7章中会详细介绍 索引的优点
mysql> select actor_id from sakila.actor where actor_id + 1 = 5而下面的actor_id 列的索引则会被使用
mysql> select actor_id from sakila.actor where actor_id = 5 - 1
2 前缀索引和索引选择性 前缀的选择性计算：
mysql> select count(DISTINCT city)/count(*) from table_name 前缀去重数 除 总数。 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 from city; +--------+--------+--------+--------+--------+ | sel3 | sel4 | sel5 | sel6 | sel7 | +--------+--------+--------+--------+--------+ | 0.7633 | 0.9383 | 0.9750 | 0.9900 | 0.9933 | +--------+--------+--------+--------+--------+
可以看到当前缀长度达到6之后，选择性提升的幅度已经很小了。 因此选择前缀长度为6； 前缀索引是一种能使索引更小，更快的有效办法，但也是有缺点的： mysql无法使用前缀索引做order by 和group by，也无法使用前缀索引做覆盖扫描。 3 多列索引 在多个列上建立的单列索引大部分情况下并不能提高mysql的查询性能。mysql5.0以后引入了一种叫"索引合并(index merge)"的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。 例子：表film_actor在字段film_id 和 actor_id上各有一个单列索引。
mysql> show create table film_actor; | film_actor | CREATE TABLE `film_actor` ( `actor_id` smallint(5) unsigned NOT NULL, `film_id` smallint(5) unsigned NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`actor_id`), KEY `idx_fk_film_id` (`film_id`), CONSTRAINT `fk_film_actor_actor` FOREIGN KEY (`actor_id`) REFERENCES `actor` (`actor_id`) ON UPDATE CASCADE, CONSTRAINT `fk_film_actor_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | mysql> explain select film_id,actor_id from film_actor where actor_id=1 or film_id =1\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: film_actor type: index_merge possible_keys: PRIMARY,idx_fk_film_id key: PRIMARY,idx_fk_film_id key_len: 2,2 ref: NULL rows: 29 Extra: Using union(PRIMARY,idx_fk_film_id); Using where
可以看到使用合并索引（index_merge）技术，优化了此次查询； 实际上也说明了表上的索引建得很糟糕，不然就不用系统优化了； 合并索引有三个变种：OR条件的联合(union),and条件的相交(intersection),组合前两种情况的联合以及相交。
mysql> select * from payment where staff_id =2 and customer_id=584;
是应该创建一个(staff_id,customer_id)索引还是应该颠倒一下？可以跑一些查询来确定在这个表中值的分布情况，并确定哪个列的选择性更高。
mysql> select sum(staff_id=2),sum(customer_id=584) from payment \G; *************************** 1. row *************************** sum(staff_id=2): 7992 sum(customer_id=584): 30 1 row in set (0.04 sec)
应该讲customer_id放在前面，因为对于条件值的customer_id数量更小。
mysql> select sum(staff_id=2) from payment where customer_id=584 \G; *************************** 1. row *************************** sum(staff_id=2): 17 1 row in set (0.00 sec)
可以看到custmoer_id=584时staff_id=2 只有17个； 需要注意，查询结果非常依赖于选定的具体指定值； 当然还可以使用计算两参数的选择性，来确定哪个参数放在前面：
mysql> select count(DISTINCT staff_id) / count(*) AS staff_id_first, count(DISTINCT customer_id) / count(*) AS customer_id_first from payment\G *************************** 1. row *************************** staff_id_first: 0.0001 customer_id_first: 0.0373
显然customer_id的选择性（列去重数 除 所有列总数） 更好， 索引列的基数（即特定条件下的数量），会影响索引性能； 尽管关于选择性和基数的经验法则值得去研究和分析，但一定要记住where 子句中的排序，分组和范围条件等其他因素，这些因素可能对查询的性能造成非常大的影响。 5 聚簇索引 聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。 innodb的聚簇索引实际上在同一结构中保存了BTree索引和数据行。（主键是BTree索引+记录是数据行） 当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中。术语"聚簇"表示数据行和相邻的键值紧凑地存储在一起。 下图展示了聚簇索引中的记录是如何存放的。注意到，叶子页包含了行的全部数据，但节点页只包含了索引列。在这个案例中，索引列包含的是整数值。
.png)
innodb通过主键聚集数据，上图中的"被索引的列"就是主键列。
聚集的优点：
crate table layout_test( col1 int NOT NULL, col2 int NOT NULL, PRIMARY KEY(col1), KEY(col2) );
假设col1 取值1--10000，按照随机顺序插入。col2取值从1--100之间随机赋值，所以有很多重复的值。 myisam的数据分布非常简单，按照数据插入的顺序存储在磁盘上。如下图：
这种分布方式很容易创建索引，下图，隐藏了页的物理细节，只显示索引中的"节点"
索引中的每个叶子节点包含"行号。表的主键和行号在叶子节点中，且叶子节点根据主键顺序排列。
.png)
那col2列上的索引又会怎么样呢？有什么特殊吗？答案是否定的，他和其他任何索引一样。
.png)
事实上，myisam中主键索引和其他索引在结构上没有什么不同。主键索引就是一个名为PRIMARY的唯一非空索引。 innodb的数据分布。因为innodb支持聚簇索引，索引使用非常不同的范式存储同样的数据。看下图：
.png)
第一眼看上去，感觉和前面的图5-5没有什么不同，其实该图，显示了整个表，而不是只有索引。因为在innodb中，聚簇索引"就是"表，所以不用想myisam那样需要独立的行存储。 innodb二级索引的叶子节点中存储的不是"行指针"（即不是那个行号），而是主键值，并以此作为指向行的"指针"。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。当然是用主键值当做 指针会让二级索引占用更多的空间，同时避免了行出现移动或者数据分页时二级索引的维护。
聚簇和非聚簇表的对比图
.png)
innodb 最好主键设置为自增类型 整数； 向聚簇索引插入顺序的索引值
.png)
向聚簇索引中插入无序的值：
.png)
这样的缺点:
mysql> explain select store_id,film_id from inventory \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: inventory type: index possible_keys: NULL key: idx_store_id_film_id key_len: 3 ref: NULL rows: 3496 Extra: Using index 1 row in set (0.00 sec)
此时，有查的字段select store_id,film_id，有一个多列索引idx_store_id_film_id，此时便使用到了覆盖索引，不会再返回数据表去找数据行，因为索引中已经包含了； 假设索引覆盖了where条件中的字段，但是整个查询涉及的字段，来看看会发什么情况，以及如何重写查询以解决该问题。
.png)
注意：extra列的"using index" 和type列的"index"不同，type列和覆盖索引毫无关系，它只是表示这个查询访问数据的方式，或者说mysql查找行的方式。而extra列的"using index"则说明。数据使用了 覆盖索引；
上面例子中，使用了ACTOR索引，但是没有使用覆盖索引直接得到数据原因：
.png)
我们把这种方式叫做延迟关联(defferred join),因为延迟了对列的访问。
查询在子查询中使用了覆盖索引，并找到了prod_id,然后做了内连接，通过prod_id再去查其他列 会快很多。
当然这一切都要基于 数据集，假设这个products表中有100万行，我们来看一下上面两个查询在三个不同的数据集上的表现，每个数据集都包含100万行：
.png)
结果分析：
.png)
实际上在ID上建了三个索引，这就是重复索引。
冗余索引：
已有索引(A,B),再重建索引(A)就是冗余索引；
而此时(B,A)，则不是冗余索引。索引(B)也不是索引(A,B)的冗余索引；
已有索引(A),再建索引(A,ID),其中ID是主键，对innodb来说主键列已经包含在二级索引中了，所以这也是冗余索引；
大多数情况都不需冗余索引，应该尽量扩展已有的索引而不是创建新索引。
当然有时候也是需要冗余索引的，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的查询的性能。
创建索引
.png)
设计索引原则：
mysql> show index from actor\G; *************************** 1. row *************************** Table: actor Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: actor_id Collation: A Cardinality: 200 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 2. row *************************** Table: actor Non_unique: 1 Key_name: idx_actor_last_name Seq_in_index: 1 Column_name: last_name Collation: A Cardinality: 200 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: 2 rows in set (0.00 sec) Cardinality，显示了存储引擎估算索引列有多少个不同的取值。 mysql5.6 以后可以通过参数innodb_analyze_is_persistent，来控制analyze 是否启动； 减少索引和数据的碎片 数据碎片三种类型： 行碎片(row fragmentation) 数据行被存储为多个地方的多个片段中。 行间碎片(Intra-row fragmentation) 逻辑上顺序的页，在磁盘上不是顺序存储的。 剩余空间碎片(Free space fragmentation) 数据页中有大量的空余空间。 使用命令： optimize table tb_name，清理碎片。 mysql> OPTIMIZE TABLE actor; +--------------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------+----------+----------+-------------------------------------------------------------------+ | sakila.actor | optimize | note | Table does not support optimize, doing recreate + analyze instead | | sakila.actor | optimize | status | OK | +--------------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (0.02 sec)
对于不支持该命令的引擎可以通过一个不做任何操作（no-op）的alter table 操作来重建表。
mysql> alter table actor engine=innodb; Query OK, 200 rows affected (0.02 sec) Records: 200 Duplicates: 0 Warnings: 0
索引项的值发生改变，此时索引项在索引表中的位置，就需要发生改变，这样一个行为称为索引维护； 因为如果不进行索引维护的话，就是说索引项的值改变后，并没有重新排序，这样改变项多了之后，就不是一个顺序排序了，就起不到索引的效果了；
3.联合索引与前缀查询
在mysql5.6前，where a? and c? 只能部分
.png)
选择性很差的字段通常不适合创建单列索引