在线时间:8:00-16:00
迪恩网络APP
随时随地掌握行业动态
扫描二维码
关注迪恩网络微信公众号
实践是检验真理的唯一途径,本篇只是站在索引使用的全局来定位的,你只需要通读全篇并结合具体的例子,或回忆以往使用过的地方,对整体有个全面认识,并理解索引是如何工作的,就可以了。在后续使用索引,或者优化索引时,可以从这些方面出发,进一步来加深对索引正确高效的使用。 一、索引失效 索引失效,是一个老生常谈的话题了。只要提到数据库优化、使用索引,都能一口气说出一大堆索引失效的场景,什么不能用、什么不该用这类的话,在此,我就不再一一罗列啰嗦了。 索引失效,是指表中有字段创建了索引,由于sql语句书写不当导致索引失效的情况。 在sql语句中,将索引列作为表达式的一部分、参与函数/数学等运算,将会导致索引失效。 例如,下面这个查询无法使用age列的索引: select id,name,age from t_user where age + 1 = 7; 很容易看出where中的表达式其实等价于age=8,但是MySQL无法自动解析这个表达式,这完全是用户行为。 (在上一篇文章中,我们知道MySQL先在索引上按值进行查找,然后返回索引值对应的数据行,一旦对索引列进行运算,则将无法正确的找到对应的数据行,从而改为全表逐行扫描查询对比) 二、前缀索引和索引选择性 有时候将内容很长的列作为索引列,这将会让索引变得很大而且很慢。如果非要在该列添加索引,解决策略就是上一篇文章提到过的模拟哈希索引。 通常可以索引开始的部分字符,这样可以大大节约索引空间,从而提高索引效率,但这样也会降低索引的选择性。 索引的选择性是指,不重复的索引值(也称为基数)和表数据的记录总数T的比值,范围从1/T到1之间。索引的选择性越高,则查询效率越高,因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。 唯一索引的选择性为1,这是最好的索引选择性,性能也是最好的。 对于BLOB、TEXT或很大的VARCHAR类型的列,作为查询条件时(原则上是要避免这样的操作,但有时总是情非得已),该列必须使用前缀索引,这样来提高查询性能。因为MySQL是不允许索引这些列的完整长度的。 三、多列索引 多列索引,是指为每个列创立独立的索引。 在SQL优化时,有人会采取“把where条件里面的列都建上索引”,希望能够对查询性能有所优化。但实际上这样的优化是非常错误的,这样一来最好的情况下也只能是“一星”索引,其性能比起真正最优的索引可能差几个数据级。有时如果无法设计一个“三星”索引,那么不如忽略掉where子句,集中精力优化索引列的顺序,或者创建一个全覆盖索引。
在多个列上建立独立的单列索引,大部分情况下并不能提高MySQL的查询性能。这也是将其错误的做法。 MySQL5.0及之后版本引入了索引合并策略,一定程度上可以使用表上的多个单列索引来定位指定的行。更早的MySQL只能使用其中某一个单列索引,然而这个情况下没有哪一个独立的单列索引是非常有效的。 索引合并策略有时候是一种优化的结果,但实际上更多时候说明了表上的索引建的很糟糕: 1)当出现对多个索引做相交操作时(通常由多个AND条件),通常意味着需要一个包含所有相关列的多列索引,而不是多个独立的单列索引。 2)当需要对多个索引做联合操作室(通常有多个OR条件),通常需要耗费大量的CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高,需要合并扫描返回的大量数据的时候。 3)优化器不会把这些计算到“查询成本”中,优化器只关心随机页面读取。这会使得查询的成本被“低估”,导致该执行计划还不如直接走全表扫描。这样做不但会消耗更多的CPU和内存资源,还可能会影响查询的并发性,但如果是单独运行这样的查询,则往往会忽略对并发性的影响。 如果在执行计划EXPLAIN中看到索引合并,应该好好检查一下查询和表的结构,看是不是已经是最优的。也可以通过参数optimizer_switch来关闭索引合并功能,也可以使用IGNORE INDEX提示让优化器忽略掉某些索引。 对于多列索引,只要查询的条件中用到了最左边的列,索引一般就不会失效。 举例说明如下: 表t_user创建了(id,name)的多列索引,具体如下: mysql> show create table t_user; +--------+---------------+ | Table | Create Table | +--------+---------------+ | t_user | CREATE TABLE `t_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `age` int(11) DEFAULT NULL, KEY `idx` (`id`,`name`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 | +--------+-----------------------------------------+ 1 row in set 根据id进行查询,具体如下: mysql> explain select * from t_user where id = 1; +----+-------------+--------+------------+------+---------------+-----+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+-----+---------+-------+------+----------+-------+ | 1 | SIMPLE | t_user | NULL | ref | idx | idx | 4 | const | 1 | 100 | NULL | +----+-------------+--------+------------+------+---------------+-----+---------+-------+------+----------+-------+ 1 row in set 从执行计划中的type可以看出,索引是有效的。但如果根据name进行查询,则索引将会失效(全表扫描),如下: mysql> explain select * from t_user where name = 'xcbeyond'; +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t_user | NULL | ALL | NULL | NULL | NULL | NULL | 7 | 14.29 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set 四、选择合适的索引列顺序 索引列顺序实在是非常重要的。正确的顺序依赖于使用该索引的查询,并且同时需要考虑如何更好的满足排序和分组的需要(只用于B-Tree索引,哈希或者其他索引存储数据并不是顺序存储)。 在一个多列B-Tree索引中,索引列的顺序意味着索引首先按照最左列进行排列。所以索引可以按照升序或者降序进行扫描,以满足符合列顺序的order by,group by和distinct等子句的查询需求。 所以多列索引列的顺序至关重要。对于如何选择索引的列顺序有一个经验法则:将选择性最高的索引放在索引的最前列。在某些场景这个经验时非常有用,但是通常不如避免随机IO和排序那么重要,考虑问题需要更全面。 当不需要考虑排序和分组时,将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化where条件的查找。这种情况下,这样设计的索引确实能够最快的过滤出需要的行,对于在where的子句中只是用了索引部分前缀列的查询来说选择性也更高。然而性能不只是依赖于所有索引列的选择性,也和查询条件的具体值有关,也就是和值的分布有关(需要根据那些运行频率最高的查询来调整索引列的顺序,让这种情况下的索引列的选择性最高)。 五、聚簇索引 聚簇索引并不是一种单独的索引类型,而是一种数据存储方式,将数据存储与索引放到了一块,找到索引页就找到了数据。具体的细节依赖于其实现方式,但
当表有聚簇索引时,它的数据行实际上存放在索引的叶子页中。“聚簇”表示数据行和相邻的键值紧凑的存储在一起。因为无法同时把数据行存放在两个不同的地方,所以一个表只能有一个聚簇索引。 聚簇索引的设定: 默认为主键。如果没有定义主键, (看到这里,如果你对B-Tree索引结构熟悉的话,就知道为啥[key、data]作为一个二元组存放在一个节点了) 聚簇主键可能对性能有帮助,但也可能导致严重的性能问题。所以需要仔细的考虑聚簇索引,尤其是将表的存储引擎从InnoDB改成其他引擎的时候(反过来也一样)。 聚簇索引的优点:
聚簇索引的缺点:
六、覆盖索引 通常大家都会根据查询的where条件来创建合适的索引,不过这也只是索引优化的一个方面。设计优秀的索引应该考虑到整个查询,而不单单是where条件部分。索引确实是一种查找数据的高效方式,但是MySQL也可以使用索引来直接获取列的数据,这样就不再需要读取数据行。如果一个索引包含所有需要查询的字段值,我们就称其为“覆盖索引”,即:一个索引覆盖where条件的所有列。 覆盖索引的好处如下:
不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列,而哈希索引、空间索引和全文索引等都不存储索引列的值,所以MySQL只能使用B-Tree所以来做覆盖索引,另外不同的存储引擎实现覆盖索引的方式也不同,而且不是所有的引擎都支持覆盖索引。 七、使用索引扫描来排序 MySQL有两种方式可以生成有序的结果集:通过排序操作,或者按索引顺序扫描。如果 扫描索引本身是很快的,因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列,那就不得不每扫描一条索引记录就都回表查询一次对应的行。这基本上都是随机I/O,因此按索引顺序读取数据的速度通常要比顺序的全表扫描慢,尤其是在I/O密集型的工作负载时。 MySQL可以使用同一个索引既满足排序,又用于查找行。因此,如果可能,设计索引时应该尽可能的同时满足这两种情况,即:索引列作为排序列。
八、冗余、重复索引 重复索引,是指在相同列上按照相同的顺序创建的相同类型的索引。应该避免这样的创建重复索引,发现以后也应该立即移除。 比如: create table test{ id int not null primary key, a int not null, b int not null, unique(id) index(id) }engine=InnoDB; 一个经验不足的人可能是想创建一个主键,先加上唯一限制 冗余索引和重复索引有一些不同,比如:如果创建了索引 冗余索引通常发生在为表添加新索引的时候。例如,有人可能会增加一个新的索引 大多数情况下不需要冗余索引,应该尽量扩展已有的索引而不是创建新的索引。但也有时候处于性能方面的考虑需要冗余索引,因为扩展已有的索引会导致其变得太大 ,从而影响其他使用该索引的查询的性能。例如,在一个整数列索引上添加一个很长的 解决冗余索引和重复索引的方法非常简单,删除这些索引就可以。但是首先要做的事找出这样的索引。可以通过写一些复杂的访问 九、未使用的索引 除了冗余索引和重复索引,可能还会有一些服务器永远不用的索引。这样的索引完全是累赘,建议直接删除。 可以使用 SELECT object_schema, object_name, index_name FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL AND count_star = 0 ORDER BY object_schema, object_name, index_name; 十、索引和锁 索引可以让查询锁定更少的行。如果你的查询从不访问那些不需要的行,那么就会锁定更少的行,从两个方面来看这对性能都有什么好处。 首先,虽然 十一、总结 通过上面大篇文字的讲解,都是用来说明如何高效的使用索引,避免错误使用。索引是一个看似简单,但实际用起来却是非常复杂的东西,要想真正用好它,需要不断的实践。实践是检验真理的唯一途径,本篇只是站在索引使用的全局来定位的,你只需要通读全篇并结合具体的例子,或回忆以往使用过的地方,对整体有个全面认识,并理解索引是如何工作的,就可以了。在后续使用索引,或者优化索引时,可以从这些方面出发,进一步来加深对索引正确高效的使用。 在平时使用索引中,有以下几点总结及建议:
以上就是MySQL性能优化之如何高效正确的使用索引的详细内容,更多关于MySQL 索引的资料请关注极客世界其它相关文章! |
请发表评论