msysql 优化系列之索引

Beyond_man
136 阅读19分钟

本文是基于《高性能MySQL》第3版和《深入浅出MySQL-数据库开发优化与管理维护》第2版 整理归纳性文章，尽管书中涉及到了除了InnoDB的存以外的其它储引擎的分析和研究，但本文只整理 InnoDB 相关内容

InnoDB 概览

InnoDB采用MVCC来支持高并发，并且实现了四个标准的隔离级别。其默认级别是REPEATABLE READ（可重复读），并且通过间隙锁（next-key locking）策略防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。

索引

  • 索引大大减少了服务器需要扫描的数据量。
  • 索引可以帮助服务器避免排序和临时表。
  • 索引可以将随机I/O变为顺序I/O。

上图展示了B-Tree索引的抽象表示，大致反映了InnoDB索引是如何工作的

B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点（图示并未画出）开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么是找到对应的值，要么该记录不存在。

叶子节点比较特别，它们的指针指向的是被索引的数据，而不是其他的节点页（不同引擎的“指针”类型不同）。图5-1中仅绘制了一个节点和其对应的叶子节点，其实在根节点和叶子节点之间可能有很多层节点页。树的深度和表的大小直接相关。 B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据。例如，在一个基于文本域的索引树上，按字母顺序传递连续的值进行查找是非常合适的，所以像“找出所有以I到K开头的名字”这样的查找效率会非常高。

创建与删除索引的方式，可以创建单列索引，复合索引（联合索引），前缀索引。 索引的创建在表数据量中等两集的情况下，索引能极大的优化查询性能。但同时过多的索引列，也会增加数据行的插入耗时，同时InnoDB的索引基于B+数，索引本身也需要额外空间进行存储和维护一个表中建立索引最好不要查过5个。同时建立要把索引建立在区分度（离散性）高的列，如性别的列就没有姓名的列离散性高。这里只是举个例子，并不是在姓名上建立索引就是好的索引。

上图显示了 B+Tree 聚簇索引是如何组织数据的存储的。

聚簇索引，并不是一种单独的索引类型，而是一种数据存储方式。InnoDB将通过主键聚集数据，这也就是说图中的“被索引的列”就是主键列。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。InnoDB只聚集在同一个页面中的记录。

聚簇数据最大限度地提高了I/O密集型应用的性能，同时也有自身的缺点：

  • 插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。但如果不是按照主键顺序加载数据，那么在加载完成后最好使用OPTIMIZE TABLE命令重新组织一下表。
  • 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。
  • 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂（page split）”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间。
  • 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。

关于 UUID 作为主键带来的问题

UID主键插入行不仅花费的时间更长，而且索引占用的空间也更大。这一方面是由于主键字段更长；另一方面毫无疑问是由于页分裂和碎片导致的。

为了明白为什么会这样，来看看往第一个表中插入数据时，索引发生了什么变化。

如图所示，因为主键的值是顺序的，所以InnoDB把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时（InnoDB默认的最大填充因子是页大小的15/16，留出部分空间用于以后修改），下一条记录就会写入新的页中。一旦数据按照这种顺序的方式加载，主键页就会近似于被顺序的记录填满，这也正是所期望的结果

对比一下向第二个使用了UUID聚簇索引的表插入数据，看看有什么不同，上图显示了结果。因为新行的主键值不一定比之前插入的大，所以InnoDB无法简单地总是把新行插入到索引的最后，而是需要为新的行寻找合适的位置——通常是已有数据的中间位置——并且分配空间。这会增加很多的额外工作，并导致数据分布不够优化。下面是总结的一些缺点：

  • 写入的目标页可能已经刷到磁盘上并从缓存中移除，或者是还没有被加载到缓存中，InnoDB在插入之前不得不先找到并从磁盘读取目标页到内存中。这将导致大量的随机I/O。
  • 因为写入是乱序的，InnoDB不得不频繁地做页分裂操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页。
  • 由于频繁的页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片。

在把这些随机值载入到聚簇索引以后，也许需要做一次OPTIMIZE TABLE来重建表并优化页的填充。从这个案例可以看出，使用InnoDB时应该尽可能地按主键顺序插入数据，并且尽可能地使用单调增加的聚簇键的值来插入新行。

顺序的主键什么时候会造成更坏的结果?

  • 对于高并发工作负载，在InnoDB中按主键顺序插入可能会造成明显的争用。主键的上界会成为“热点”。因为所有的插入都发生在这里，所以并发插入可能导致间隙锁竞争。
  • 另一个热点可能是AUTO_INCREMENT锁机制；

如果遇到这个问题，则可能需要考虑重新设计表或者应用，或者更改innodb_autoinc_lock_mode配置

索引的创建

通常我们将我们通过计算列的离散型，来判断该列是否适合建立索引，计算列的离散型：

-- 计算离散性（选择性/差异性）更好的列，在这里k列区分度最高
SELECT COUNT(DISTINCT k)/COUNT(*) AS k_sens ,COUNT(DISTINCT d)/COUNT(*) AS d_sens ,COUNT(*) FROM t;



 -- alter 添加、删除索引
 
 ALTER TABLE t_customer ADD INDEX idx_ph (phoneNo);
  
 ALTER TABLE t_customer DROP INDEX idx_ph ;
  
  
  --  CREATE、DROP 方式 （建前缀索引）
  
  CREATE INDEX  index_name ON t_customer (NAME(10));
  
  DROP INDEX index_name ON t_customer

前缀索引是一种能使索引更小、更快的有效办法，但另一方面也有其缺点：MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。

索引失效

  1. 如果 MySQL 估计使用索引比全表扫描更慢，则不使用索引，例如 where 条件中in 中的值的数量过大。所以 in 的值的数量要控制在一定的范围内
  2. 用 or 分割开的条件，如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到。
  3. 复合索引，没有遵循最左匹配原则，那么在查询中这个索引也不会被 MySQL 采用。最左匹配原则简单理解：在创建索引时候创建了以 a，b， c 先后顺序复合索引，查询就要在where条件中如果使用到 a，b ，c或者使用到了a，b 就符合最左匹配原则，注意：最左匹配原则并不要求where条件后的列的位置顺序必须是已创建联合索引时的顺序必须一致，这是因为即使不一致如 where条件后是c,a,b这样的顺序 mysql 的在经历查询优化器后会按创建索引的顺序优化 sql 语句

-- 创建表 T 创建了k,d 列的非唯一联合索引
DROP TABLE IF EXISTS T;
 CREATE TABLE T (
ID INT PRIMARY KEY,
k INT NOT NULL DEFAULT 0, 
s VARCHAR(16) NOT NULL DEFAULT '',
d VARCHAR(16) NOT NULL DEFAULT '',
INDEX inx_sd(k,d))
ENGINE=INNODB;

-- 插入数据

INSERT INTO T VALUES
(100,1, 'aa', 'aa')
,(200,2,'bb','bb')
,(300,3,'cc','cc')
,(500,5,'ee','ee')
,(600,6,'ff','ff')
,(700,7,'gg','gg')
,(101,8, 'aa', 'aa')
,(202,9,'bb','bb')
,(303,10,'cc','cc');
  • 符合最左匹配原则，两条sql执行计划相同都只用到了联合索引 inx_sd ，type列是 ref
EXPLAIN SELECT * FROM T WHERE k = 3 AND d  = 'cc'

EXPLAIN SELECT * FROM T WHERE d  = 'cc' AND k = 3 

EXPLAIN SELECT * FROM T WHERE k = 3

EXPLAIN SELECT * FROM T WHERE k BETWEEN 3 AND 5

  • 不符合最左匹配原则
EXPLAIN SELECT * FROM T WHERE d  = 'cc'
  1. 条件列的字段 like 是以 ％ 开始
  2. 如果列类型是字符串，那么一定记得在 where 条件中把字符常量值用引号引起来，否则的话即便这个列上有索引，MySQL 也不会用到的，因为，MySQL 默认把输入的常量值进行转换以后才进行检索。
  3. 对索引列进行函数操作例如，下面这个查询无法使用actor_id列的索引：
   mysql> SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

凭肉眼很容易看出WHERE中的表达式其实等价于actor_id=4 下面是另一个常见的错误：

    mysql> SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10;
  1. 函数操作、隐式类型转换、隐式字符编码转换
  • 此条涵盖了上述：5、6 两条
  1. 查询条件中某列使用否定条件的（!= <> IS NOT NULL），存储引擎不能使用索引中该列其后的所有列
  2. 查询条件中出现某个列是范围查询的，存储引擎不能使用复合索引中该列其后的所有列

建议：

  • 对于单键索引，尽量选择针对当前查询过滤性更好的索引，能选择复合索引的尽量选择复合索引
  • 在选择复合索引的时候，当前查询中过滤性最好的字段在索引字段顺序中，位置越靠前越好
  • 在选择复合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面

Explain 中的Extra列:

Using union(PRIMARY,idx_fk_film_id)，意味着出现了索引合并，如果在EXPLAIN中看到有索引合并，应该好好检查一下查询和表的结构，看是不是已经是最优的。也可以通过参数optimizer_switch来关闭索引合并功能。也可以使用IGNORE INDEX提示让优化器忽略掉某些索引。

“Using index” 这意味着发起一个被索引覆盖的查询（也叫做索引覆盖查询）时，

复合索引

复合索引（多列索引/联合索引）的列顺序至关重要,对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列

当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化WHERE条件的查找。在这种情况下，这样设计的索引确实能够最快地过滤出需要的行，对于在WHERE子句中只使用了索引部分前缀列的查询来说选择性也更高。

覆盖索引

如果索引的叶子节点中已经包含要查询的数据，那么还有什么必要再回表查询呢?如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。

考虑一下如果查询只需要扫描索引而无须回表，会带来多少好处： 索引条目通常远小于数据行大小，所以如果只需要读取索引，那MySQL就会极大地减少数据访问量

由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。

覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引。

下图展示了查询中查询了索引列实现了覆盖索引：

使用索引扫描来做排序

MySQL有两种方式可以生成有序的结果：通过排序操作；或者按索引顺序扫描。使用索引扫描的前提是：查询的列都是索引列

如果EXPLAIN出来的type列的值为“index”，则说明MySQL使用了索引扫描来做排序（不要和Extra列的“Using index”搞混淆了）。

扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录就都回表查询一次对应的行。这基本上都是随机I/O，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢，尤其是在I/O密集型的工作负载时。

实现索引扫描排序需要注意：

  1. 只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能够使用索引来对结果做排序
  2. 如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序
  3. ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求；否则，MySQL都需要执行排序操作，而无法利用索引排序。

有一种情况下ORDER BY子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候。如果WHERE子句或者JOIN子句中对这些列指定了常量，就可以“弥补”索引的不足。

这里从《高性能Mysql》第三版中摘取的示例：


CREATE TABLE `rental` (
  `rental_id` INT(11) NOT NULL AUTO_INCREMENT,
  `rental_date` DATETIME NOT NULL,
  `inventory_id` MEDIUMINT(8) UNSIGNED NOT NULL,
  `customer_id` SMALLINT(5) UNSIGNED NOT NULL,
  `return_date` DATETIME DEFAULT NULL,
  `staff_id` TINYINT(3) UNSIGNED NOT NULL,
  `last_update` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`rental_id`),
  UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
  KEY `idx_fk_inventory_id` (`inventory_id`),
  KEY `idx_fk_customer_id` (`customer_id`),
  KEY `idx_fk_staff_id` (`staff_id`)
) ENGINE=INNODB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8;

还有一些可以使用索引排序的查询示例：


-- 这个索引第一列在where上，第二列在order by上
select rental_id,staff_id from sakila.rental where rental_date='2005-05-05' order by inventory_id desc;

-- 这个查询因为order by使用的两列就是索引的最左前缀：
select rental_id,staff_id from sakila.rental where rental_date='2005-05-05' order by rental_date,inventory_id

下面是一些不能使用索引做排序的查询：

-- 这个查询使用了两种不同的排序方向，但是索引列最左前缀是符合的：
select rental_id,staff_id from sakila.rental where rental_date='2005-05--25' rder by inventory_id desc,customer_id asc

-- 这个查询的order by子句中使用了一个不在索引中的列 
select rental_id,staff_id from sakila.rental where rental_date='2005-05--25' order by inventory_id,staff_id

-- 这个查询的where和order by中的列无法组合成索引的最左前缀,中间缺失了inventory_id列
select rental_id,staff_id from sakila.rental where rental_date='2005-05-25' order by customer_id

-- 这个查询在索引列的第一列上是范围查询，所以mysql无法使用索引的其余列，这个范围条件后边的无论是查询条件列还是排序列都无法使用到索引
select rental_id,staff_id from sakila.rental where rental_date >'2005-05-25' order by inventory_id,customer_id

-- 这个查询在inventory_id列上有多个等于条件，对于排序来说，这也是一种范围查询，inventory_id条件列后面的无论是查询还是排序都无法使用索引
select rental_id,staff_id from sakila.rental where rental_date=2005-05-25 and inventory_id in (1,2) order by customer_id

-- 这个查询理论上是可以使用索引进行关联排序的，但是由于优化器在优化时将film_actor表当作关联的第二个表，所以实际上无法使用索引，即排序列不是驱动表的列就无法使用索引排序
select actor_id,title from sakila.film_actor join sakila.film using(film_id) order by actor_id

尽量避免NULL列

如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理。当可为NULL的列被索引时，每个索引记录需要一个额外的字节，在MyISAM里甚至还可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引。如果计划在列上建索引，就应该尽量避免设计成可为NULL的列。

MySql中的范围查询对组合索引使用的影响

如果组合索引中包含的列，有范围查询，那么它之后的列将不会走索引

范围查询通常是：between..and..,>,< >=,<=,in 关键字的查询

底层B+树叶子节点的键值是通过链表连接的，而组合索引是从左向右按照索引字段的顺序进行查找，只有当前一个索引列是确定值的时候，下一个索引列的值才是递增的状态，才能通过B+树遍历得到，而一旦前一个索引列是范围匹配，则之后的索引列的值不存在递增关系，那么就没办法通过B+树遍历得到。 由此我们可以知道范围查询中 in 关键字的查询是可以使用索引的。

数据类型

  • 时间类型

    • DATETIME和TIMESAMP列都可以存储相同类型的数据：时间和日期，精确到秒。然而TIMESTAMP只使用DATETIME一半的存储空间，并且会根据时区变化，具有特殊的自动更新能力。另一方面，TIMESTAMP允许的时间范围要小得多

  • 整数类型

    • TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT。分别使用8，16，24，32，64位存储空间。它们可以存储的值的范围从−2（N−1）到2（N−1）−1，其中N是存储空间的位数。整数类型有可选的UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提高一倍。例如TINYINT UNSIGNED可以存储的范围是0～255，而TINYINT的存储范围是−128～127。有符号和无符号类型使用相同的存储空间，并具有相同的性能，因此可以根据实际情况选择合适的类型。
    • MySQL可以为整数类型指定宽度，例如INT（11），对大多数应用这是没有意义的：它不会限制值的合法范围，只是规定了MySQL的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，INT（1）和INT（20）是相同的。

  • 实数是带有小数部分的数字

    • FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算。DECIMAL类型用于存储精确的小数。浮点和DECIMAL类型都可以指定精度。对于DECIMAL列，可以指定小数点前后所允许的最大位数。这会影响列的空间消耗。例如，DECIMAL（18,9）小数点两边将各存储9个数字,一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节。

    • 浮点类型在存储同样范围的值时，通常比DECIMAL使用更少的空间。FLOAT使用4个字节存储。DOUBLE占用8个字节，相比FLOAT有更高的精度和更大的范围。和整数类型一样，能选择的只是存储类型；MySQL使用DOUBLE作为内部浮点计算的类型。

    应该尽量只在对小数进行精确计算时才使用DECIMAL——例如存储财务数据。但在数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。

  • 字符类型

    • VARCHAR 类型用于存储可变长字符串，是最常见的字符串数据类型。它比定长类型更节省空间，因为它仅使用必要的空间（例如，越短的字符串使用越少的空间）。有一种情况例外，如果 MySQL 表使用 ROW_FORMAT=FIXED 创建的话，每一行都会使用定长存储，这会很浪费空间。VARCHAR 在 UPDATE 时可能使行变得比原来更长，这就导致需要做额外的工作。不同的存储引擎的处理方式是不一样的，InnoDB则需要分裂页来使行可以放进页内
    • CHAR类型是定长的：MySQL总是根据定义的字符串长度分配足够的空间，CHAR值会根据需要采用空格进行填充以方便比较。CHAR适合存储很短的字符串，或者所有值都接近同一个长度。对于经常变更的数据，CHAR也比VARCHAR更好，因为定长的CHAR类型不容易产生碎片。对于非常短的列，CHAR比VARCHAR在存储空间上也更有效率。

  • BLOB和TEXT类型

    • BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储BLOB和TEXT家族之间仅有的不同是BLOB类型存储的是二进制数据，没有排序规则或字符集，而TEXT类型有字符集和排序规则。
    • MySQL对BLOB和TEXT列进行排序与其他类型是不同的：它只对每个列的最前max_sort_length字节而不是整个字符串做排序。如果只需要排序前面一小部分字符，则可以减小max_sort_length的配置，或者使用ORDER BY SUSTRING（column，length）。MySQL不能将BLOB和TEXT列全部长度的字符串进行索引，也不能使用这些索引消除排序。

其他数据类型Enum,Set,BIT（0/1）不详细记录

2.优化表

  • OPTIMIZE TABLE

删除数据操作不会立即回收空间，而这些删除操作会产生大量的碎片，执行查询可能会影响到执行计划，所以需要整理碎片，通过多种方式的操作；则应使用 OPTIMIZE TABLE 命令来进行表优化。这个 命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费，但OPTIMIZE TABLE 命令只对 MyISAM、BDB 和 InnoDB 表起作用。

OPTIMIZE TABLE 使用在线 DDL 操作修改 Innodb 日常表和分区表，这样减少了同时DML操作的停机时间；该方式会在准备阶段和提交阶段持有表级锁：在准备阶段修改数据并且创建一个中间表，在提交阶段提交元数据的修改。由于准备阶段和提交阶段在整个事务中的时间非常小，可以认为应该优化表的过程中不影响表的其他后续操作。

optimize table t;

注意：OPTIMIZE 执行期间将对表进行锁定，因此一定注意要在数据库不繁忙的时候执行相关的操作。

  • ALTER TABLE方法

其实等价于REBUILD表（REBUILD表就是重建表的英文），所以索引也等价于重建了。

ALTER TABLE t ENGINE=InnoDB

验证可以通过查询表创建时间

mysql>SELECT table_name,create_time FROM information_schema.TABLES WHERE table_name='t';
avatar
文章
阅读
粉丝