前期回顾：

MySQL性能优化(一)：MySQL架构与核心问题

MySQL性能优化(二)：选择优化的数据类型

MySQL性能优化(三)：深入理解索引的这点事

一、索引失效

select id,name,age from t_user where age + 1 = 7;

二、前缀索引和索引选择性

三、多列索引

三星索引：在Lahdenmaki和Leach编写的Relational Database Index Design and the Optimizers一书中，提到如何评价一个索引是否适合某个查询的“三星系统”：索引将相关的记录放到一起则获得“一星”；如果索引中的数据顺序和查找中的排序顺序一致则获得“二星”；如果索引中的列包含了查询中需要的全部列则获得“三星”。

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

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

四、选择合适的索引列顺序

五、聚簇索引

非聚簇索引：将数据存储与索引分开存储，索引结构的叶子节点指向了数据的对应行。当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因。

• 可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户ID来聚集数据，这样子只需要从磁盘中读取少数的数据也技能获取某个用户的全部邮件。
• 数据访问更快。聚簇索引把索引和数据都放在同一个B-Tree中，因此从聚簇索引中获取数据比从非聚簇索引中要快。
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

• 最大限度的提高了I/O密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势了。
• 插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB
  表中速度最快的方式。但如果不是按照逐渐顺序加载数据，那么在加载完成后最好使用OPTIMIZE TABLE
  重新组织一下表。
• 更新聚簇索引列的代价很高。因为要强制InnoDB将每个被更新的行移动到新的位置。
• 基于聚簇索引的表在插入新行，或者主键被更新导致移动行的时候，可能面临“页分裂”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页来容纳该行，这就是一次页分裂操作，这也意味着这样导致表占用更多的磁盘空间。
• 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏时，或者由于页分裂导致数据存储不连续的时候。
• 二级索引(非聚簇索引)可能比想想的要更大。因为二级索引的叶子结点包含了引用行的主键列。
• 二级索引访问需要两次索引查找，而不是一次。

六、覆盖索引

• 索引条目通常远小于数据行的大小，所以如果只需要读取索引，那么MySQL就会极大的减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷贝下。覆盖索引对于I/O密集型的应用也有帮助，因为索引比数据更小，更容易全部放进去内存。
• 因为索引是按照列值顺序存储的，对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少的多。对于某些存储引擎，例如MyISAM
  和Percona XtraDB
  ，甚至可以通过POTIMIZE
  命令使得索引完全顺序排列，这样就可以让简单的范围查询能使用完全排序的索引访问。
• 一些存储引擎，如MyISAM
  在内存中只缓存索引。数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。这可能会导致严重的性能问题，尤其是那些系统调用占了数据访问中的最大开销的场景。
• 由于InnoDB
  的聚簇索引，覆盖索引对于InnoDB表特别有用。InnoDB的二级索引在叶子节点保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询

七、使用索引扫描来排序

• 只有当索引的列顺序和order by
  子句的顺序完全一致，并且所有列的排序方向都一样时，MySQL才能够使用索引来对结果做排序。
• 如果查询需要关联多张表，则只有当order by
  子句引用的字段全部为第一个表时，才能使用索引做排序。order by
  子句和查找性查询的限制是一样的：需要满足索引的最左前缀的要求；否则，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;

十、索引和锁

十一、总结

1. 在区分度高的字段上面建立索引可以有效的使用索引，区分度太低，无法有效的利用索引，可能需要扫描所有数据页，此时和不使用索引区别不大。
2. 联合索引，注意最左匹配原则：必须按照从左到右的顺序匹配，MySQL会一直向右匹配直到遇到范围查询(>、<、between、like)
   就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4
   如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
3. 查询记录的时候，少使用*，尽量去利用索引覆盖，可以减少回表操作，提升效率。
4. 有些查询可以采用联合索引，进而使用到索引下推，也可以减少回表操作，提升效率。
5. 禁止对索引字段使用函数、运算符操作，这样将会使索引失效。
6. 字符串字段和数字比较的时候会使索引无效。
7. 模糊查询'%值%'
   会使索引无效，变为全表扫描，但是'值%'
   这种可以有效利用索引。
8. 排序中尽量使用到索引字段，这样可以减少排序，提升查询效率。

• MySQL性能优化(一)：MySQL架构与核心问题
• MySQL性能优化(二)：选择优化的数据类型
• MySQL性能优化(三)：深入理解索引的这点事
• 微服务架构下的核心话题 (一)：微服务架构下各类项目的顺势崛起
• 微服务架构下的核心话题 (二)：微服务架构的设计原则和核心话题
• 微服务架构下的核心话题 (三)：微服务架构的技术选型