MYSQL索引
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MYSQL索引，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含24257字，纯文字阅读大概需要35分钟。
内容图文
- 索引优化应该是对查询性能优化最有效的手段了。
- mysql只能高效地使用索引的最左前缀列。
- mysql中索引是在存储引擎层而不是服务器层实现的
- 如果不是按照索引的最左列开始查找，则无法使用索引。
- 不能跳过索引中的列
- 如果查询中有某列的范围查询，则其右边所有列都无法使用索引优化查询。
- 哈希索引只包含哈希值和行指针，不存储字段值，所以不能使用"覆盖索引"的优化方式，去避免读取数据表。
- 哈希索引数据并不是按照索引值顺序存储的，索引也就无法用于排序
- 哈希索引页不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容计算哈希值的。
- 哈希索引只支持等值比较查询，包括=，in(),<=>，不支持任何范围查询。列入where price>100
- 访问哈希索引的数据非常快，除非有很多哈希冲突（不同的索引列值却有相同的哈希值）
- 如果哈希冲突很多的话，一些索引维护操作的代价也会很高。
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章中会详细介绍 索引的优点
- 索引大大减少了服务器需要扫描的数据量
- 索引可以帮助服务器避免排序和临时表
- 索引可以将随机I/O变成顺序I/O
正确地创建和使用索引是实现高性能查询的基础。 1 独立的列 “独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。 例如：下面则无法使用actor_id列的索引：
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),组合前两种情况的联合以及相交。
- 当出现服务器对多个索引做相交操作时（通常有多个AND条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
- 当服务器需要对多个索引做联合操作时（通常有多个OR条件），通常需要耗费大量CPU和内存资源在算法的缓存，排序，和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回大量数据的时候。
- 更重要的是，优化器不会把这些计算到"查询成本(cost)"中，优化器只关心随机页面读取。
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索引+记录是数据行） 当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中。术语"聚簇"表示数据行和相邻的键值紧凑地存储在一起。 下图展示了聚簇索引中的记录是如何存放的。注意到，叶子页包含了行的全部数据，但节点页只包含了索引列。在这个案例中，索引列包含的是整数值。
- 可以把相关数据保存在一起。减少磁盘I/O
- 数据访问更快
- 使用覆盖索引扫描的查询可以直接使用页节点中的主键值
- 聚簇数据最大限度地提高了I/O密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没有那么重要了，聚簇索引也就没什么优势了。
- 插入速速严重依赖于插入顺序。
- 更新聚簇索引列的代价很高。
- 出入新行或者主键更新需要移动时，可能面临"页分裂(page split)"问题。当行的主键值要求必须插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间。
- 二级索引（非聚簇索引）即普通索引，在其叶子节点包含了引用行的主键列。
crate table layout_test( col1 int NOT NULL, col2 int NOT NULL, PRIMARY KEY(col1), KEY(col2) );
假设col1 取值1--10000，按照随机顺序插入。col2取值从1--100之间随机赋值，所以有很多重复的值。 myisam的数据分布非常简单，按照数据插入的顺序存储在磁盘上。如下图：
这种分布方式很容易创建索引，下图，隐藏了页的物理细节，只显示索引中的"节点"
索引中的每个叶子节点包含"行号。表的主键和行号在叶子节点中，且叶子节点根据主键顺序排列。
那col2列上的索引又会怎么样呢？有什么特殊吗？答案是否定的，他和其他任何索引一样。
事实上，myisam中主键索引和其他索引在结构上没有什么不同。主键索引就是一个名为PRIMARY的唯一非空索引。 innodb的数据分布。因为innodb支持聚簇索引，索引使用非常不同的范式存储同样的数据。看下图：
第一眼看上去，感觉和前面的图5-5没有什么不同，其实该图，显示了整个表，而不是只有索引。因为在innodb中，聚簇索引"就是"表，所以不用想myisam那样需要独立的行存储。 innodb二级索引的叶子节点中存储的不是"行指针"（即不是那个行号），而是主键值，并以此作为指向行的"指针"。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。当然是用主键值当做 指针会让二级索引占用更多的空间，同时避免了行出现移动或者数据分页时二级索引的维护。
聚簇和非聚簇表的对比图
innodb 最好主键设置为自增类型 整数； 向聚簇索引插入顺序的索引值
向聚簇索引中插入无序的值：
- 写入的目标页可能已经刷新到磁盘上并从缓存中移除，或者还没有加载到缓存中，这样innodb在插入前不得不先找到并从磁盘读取目标页到内存中。导致了大量的随机I/O。
- 因为写入是乱序的，innodb不得不频繁地做页分裂操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页。
- 由于频繁的页分裂，页会变得稀疏被不规则地填充，所以最终数据会有碎片。
- 索引条目通常远小于数据行大小，所以如果只需要读取索引，那么mysql就会极大地减少数据访问量。
- 因为索引是按照列值顺序存储的（至少在单个页内是如此），所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少得多。
- 一些存储引擎如Myisam在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。
- 由于innodb的聚簇索引，覆盖索引对innodb表特别有用。
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条件中的字段，但是整个查询涉及的字段，来看看会发什么情况，以及如何重写查询以解决该问题。
- 没有任何索引能够覆盖这个查询。
- mysql能在索引中最左前缀匹配的like比较如"Apoll%",而无法做通配符开头的like 如"%Apoll%"
- 第一个数据集，Sean Carrey 出演了30000部作品，其中有20000部标题包含了Apollo
- 第一个数据集，Sean Carrey 出演了30000部作品，其中有40部标题包含了Apollo
- 第一个数据集，Sean Carrey 出演了50部作品，其中有10部标题包含了Apollo
- 在第一个数据集中：
-
- 原查询：从索引actor中读到30000条数据，再根据得到的主键ID回数据表中再读30000条数据；总共读取60000条；
- 优化后的查询：先从索引actor2中读到30000条sena carrey，之后在所有Sean Carrey 中做like 比较 ，找到20000条prod_id；之后还是要回到数据表中，根据prod_id再读取20000条记录；总共读取50000条；
- 分析：总数虽然少了17%，但是子查询中的like比较开销会比较大，相抵之后效率并没有什么提升。
- 在第二个数据集中：
-
- 原查询：从索引actor中读到30000条数据，再根据得到的主键ID回数据表中再读30000条数据；总共读取60000条；
- 优化后的查询：先从索引actor2中读到30000条sena carrey，之后在所有Sean Carrey 中做like 比较 ，找到40条prod_id；之后还是要回到数据表中，根据prod_id再读取40条记录；总共读取30040条；
- 分析：读取总数降低了50%, 相比子查询中的开销 还是值得；
- 第三个数据集：显示了子查询效率反而下降的情况。因为索引过滤时符合第一个条件的结果集已经很小，索引子查询带来的成本反而比从表中直接提取完整行更高。
- order by a
-
- 满足最左前缀要求
- a = 3 order by b
-
- 满足最左前缀为常数
- order by a,b
-
- 满足最左前缀要求
- order by a desc,b desc
-
- 满足最左前缀要求
- a>5 order by a，b
-
- 满足最左前缀要求
- order by b
-
- 不满足最左前缀要求
- a >5 order by b
-
- 不满足最左前缀，且，最左前缀不是常数
- a in (1,3) order by b
-
- 不满足最左前缀，且，最左前缀不是常数
- oder by a asc ,b desc
-
- 排序方向不一致
单列索引 create index idx_test1 on tb_student(name); 联合索引 create index idx_test2 on tb_student(name,age) 索引中先根据name排序，name相同的情况下，根据age排序
设计索引原则：
- 搜索的索引列。
-
- 不一定是所要选择的列；即where 后面的查询条件加索引，而不是select 后面的选择列
- 使用唯一索引。
- 使用短索引。
-
- 如果对字符串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。
- 利用最左前缀。
- 不要过度索引
- innodb表，指定主键，并且是自增的最好；
- 都可以用在，where col=1 or col in (15,18,20),这样的定值查询中；
- 而在范围查询中，where col>1 and col<10 或者 col like ‘ab%‘ or col between ‘lisa‘ and ‘simon‘;此时只有BTREE索引能使用；HASH索引在这种情况中，不会被使用到，会对全表进行扫描；
维护索引和表 维护表有三个主要目的：
- 找到并修复损坏的表
- 维护准确的索引统计信息
- 减少碎片
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
索引项的值发生改变，此时索引项在索引表中的位置，就需要发生改变，这样一个行为称为索引维护； 因为如果不进行索引维护的话，就是说索引项的值改变后，并没有重新排序，这样改变项多了之后，就不是一个顺序排序了，就起不到索引的效果了；
- 索引维护由数据库自动完成
- 插入/修改/删除每一个索引行都变成一个内部封装的事务
- 索引越多，事务越长，代价越高
- 索引越多对表的插入和索引字段修改就越慢
1.使用WHERE查询条件建立索引 select a,b from tab where c=?; idx_c (c) select a,b from tab where c=? and d=? idx_cd(c,d) 2.排序ORDER BY,GROUP BY,DISTINCT 字段添加索引
3.联合索引与前缀查询
- 联合索引能为前缀单列，复列查询提供帮助
在mysql5.6前，where a? and c? 只能部分
- 合理创建联合索引，避免冗余
选择性很差的字段通常不适合创建单列索引
- 男女比例相仿的表中性别不适合创建单列索引
- 如果男女比例极不平衡，要查询的又是少数方（理工院校查女生）可以考虑使用索引
- 在非常长的字段上建立索引影响性能
- innodb索引单字段（utf8）只能取前767bytes
- 对长字段处理的方法
- 最核心SQL考虑索引覆盖
- 索引列进行数学运算或函数运算
- 未含复合索引的前缀字段
- 前缀通配‘_’ 和‘%’通配符
- 用OR分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么所涉及的索引都不会被用到。因为后面的查询肯定要走全表扫描，在存在全表扫描的情况下，就没有必要多一次索引扫描增加I/O访问，一次全表扫描过滤条件就足够了。
- where条件使用NOT,<>,!=
- 字段类型匹配
内容总结
以上是互联网集市为您收集整理的MYSQL索引全部内容，希望文章能够帮你解决MYSQL索引所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】