文章目录

  mysql数据（包括索引）存储在磁盘，而一般查询数据的性能问题主要是IO问题，提高IO效率的关键问题主要是减少磁盘IO次数和IO的量

  局部性原理：数据和程序都有聚集成群的倾向，同时之前被访问过的数据很可能再次被查询，空间局部性和时间局部性。

  磁盘预读：内存跟磁盘发生交互的时候，一般情况下有一个最小的逻辑单元，称为页（datapage），页一般由操作系统决定是多大，一般是4k或者8k，而我们在进行数据交互的时候，可以取页的整数倍来进行读取，InnoDB存储引擎每次读取数据，读取16k。

  1. 索引

  索引本质上是一种可以可以加快检索速度的、排好序的数据结构。索引运用在表中某些字段上，存储索引需要单独占据一定的空间。

  1.1 索引的优缺点

  索引的优点：

  索引的缺点：

  1.2 索引的使用场景

  在哪些列上面创建索引：

  不在哪些列建索引？

  1.3 索引的三种常见底层数据结构以及优缺点

  三种常见的索引底层数据结构：分别是哈希表、有序数组和搜索树。

  2. 常见索引类型

  常见的索引类型有：普通索引、唯一索引、主键索引、全文索引、组合索引。

  2.1 普通索引

  最基本的索引，没有任何限制；

   --直接创建索引：

    CREATE INDEX index_name ON table(column(length);
     
    --修改表结构的方式添加索引：
    ALTER TABLE table_name ADD INDEX index_name ON (column(length));
     
    --创建表的时候同时创建索引：
    CREATE TABLE ‘table’(  
    ID INT NOT NULL,   
    username VARCHAR(16) NOT NULL,  
    INDEX [indexName] (username(length))  
    );  

  2.2 唯一索引

  与普通索引类似，但索引列的值必须唯一，允许有空值，可以有多个NULL值。如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似。

   --创建唯一性索引：

    CREATE UNIQUE INDEX indexName ON mytable(username(length));
     
    --修改表结构：
    ALTER TABLE table_name ADD UNIQUE indexName ON (column(length));
     
    --创建表的时候指定：
    CREATE TABLE mytable(  
    ID INT NOT NULL,   
    username VARCHAR(16) NOT NULL,  
    UNIQUE [indexName] (username(length))
    );  

  2.3 主键索引

  可以理解为一种特殊的唯一索引，不允许有空值

   --创建表的时候创建，当把某个列设为主键的时候,数据库会自动的创建一个以主键作为名称的主键索引。

    CREATE TABLE table(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(32) NOT NULL)
    );
     
    --修改表结构：
    ALTER TABLE `table_name` ADD PRIMARY KEY ( `col` );

  2.4 全文索引（仅可用于 MyISAM 表）

  全文索引仅可用于 MyISAM 表，并只支持从CHAR、VARCHAR或TEXT类型，用于替代效率较低的like 模糊匹配操作，而且可以通过多字段组合的全文索引一次性全模糊匹配多个字段。对于大容量的数据表，生成全文索引是一个非常消耗时间和硬盘空间的做法。对于较大的数据集，将你的数据输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把数据输入现有FULLTEXT索引的速度更为快。

  全文索引使用B树存放索引数据，但使用的是特定的算法，将字段数据分割后再进行索引（一般每4个字节一次分割），索引文件存储的是分割前的索引字符串集合，与分割后的索引信息，对应Btree结构的节点存储的是分割后的词信息以及它在分割前的索引字符串集合中的位置。

   –创建表的适合添加全文索引

    CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
    `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT (content)
    );
     
    –修改表结构添加全文索引
    ALTER TABLE article ADD FULLTEXT index_content(content);
     
    –直接创建索引
    CREATE FULLTEXT INDEX index_content ON article(content);

  2.5 联合索引、索引下推、回表

  多个字段共同组成的索引，使用时需匹配最左匹配原则。

   --创建组合索引：

    ALTER TABLE `table_name` ADD INDEX index_name (col1(length), col2(length), col3(length));

  索引下推：MySQL 5.6 引入的索引下推优化（Index Condition Pushdown，简称ICP)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。like KK%其实就是用到了索引下推优化。

  例如对于辅助的联合索引(name,age,position)，正常情况按照最左前缀原则，SELECT * FROM employees WHERE name like ‘LiLei%’ AND age = 22 AND position =‘manager’ 这种情况只会走name字段索引，因为根据name字段过滤完，得到的索引行里的age和position是无序的，无法很好的利用索引。

  在MySQL5.6之前的版本，这个查询只能在联合索引里匹配到名字是 ‘LiLei’ 开头的索引，然后拿这些索引对应的主键逐个回表，到主键索引上找出相应的记录，再比对age和position这两个字段的值是否符合。MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 ‘LiLei’ 开头的索引之后，同时还会在索引里过滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。索引下推会减少回表次数，对于innodb引擎的表索引下推只能用于二级索引，innodb的主键索引（聚簇索引）树叶子节点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。

  回表：当查询的数据在索引树中找不到的时候，需要回到主键索引树中去获取，这个过程叫做回表。

  为什么范围查找Mysql没有用索引下推优化？

  估计应该是Mysql认为范围查找过滤的结果集过大，like KK% 在绝大多数情况来看，过滤后的结果集比较小，所以这里Mysql选择给 like KK% 用了索引下推优化，当然这也不是绝对的，有时like KK% 也不一定就会走索引下推。

  2.6 覆盖索引

  覆盖索引是select的数据列只用从索引中就能够取得，不必回表，换句话说，查询列要被所建的索引覆盖。 覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

  2.7 聚簇索引与非聚簇索引

  在MySQL中，聚簇索引和非聚簇索引的概念与一般的数据库系统相似，但是MySQL中的实现方式有一些特定的特点。

  在MySQL中，辅助索引的选择性是一个重要的因素。选择性指的是索引键值的唯一性，即索引键值的不重复程度。具有高选择性的辅助索引可以更有效地缩小搜索范围，从而提高查询性能。

  需要注意的是，MySQL InnoDB存储引擎中的表默认使用聚簇索引（主键索引）来组织数据，即数据行按照主键的顺序进行排序。 而其他存储引擎，如MyISAM存储引擎，没有聚簇索引的概念，它使用B树索引来组织数据。在使用MyISAM引擎的表中，可以通过创建辅助索引来加速查询操作。

  综上所述，MySQL中的聚簇索引即主键索引，将数据按照主键顺序进行排序和存储；非聚簇索引即辅助索引，基于表的其他列创建的独立索引结构，通过索引键值和行指针来加速查询操作。

  聚簇索引一般在下面场景使用：

  （1）主键列，InnoDB存储引擎中，默认为表的主键建立一个聚簇索引。

  （2）按范围存取的列或者在group by或order by中使用的列。在聚簇索引下，因为表中数据存储的物理顺序与索引的逻辑顺序一致，所以在包含范围检查(between、=)或使用group by或order by的查询时，一旦找到具有范围中第一个键值的行，具有后续索引值的行保证物理上毗连在一起而不必进一步搜索，避免了大范围扫描，可以大大提高查询速度。

  （3）在连接操作中使用的列。

  （4）不经常修改的列。因为码值修改后，数据行必须移动到新的位置。

  数据存储文件和索引文件是分开的。物理顺序与索引顺序不相同，可以有多个，小于249个；

  3. 索引的数据结构

  常见的索引的数据结构有：B+Tree、Hash索引。

  3.1 B+Tree索引

  B+Tree是MySQL中使用最频繁的索引数据结构。是Innodb和Myisam存储引擎模式的索引类型。相对Hash索引，B+Tree索引在查找时需要从根节点到叶节点进行多次IO操作，在查询速度比不上Hash索引，但是更适合排序等操作。

  为什么选择 B+ 树

  B 树能够在非叶子节点存储数据，但会导致在查询连续数据可能带来更多的随机 IO。B+ 树的所有叶节点可以通过指针来相互连接，减少顺序遍历带来的随机 IO。

  二叉树

  红黑树

  Hash表

  B-Tree

  B+Tree（B-Tree变种）

  3.2 MyISAM和InnoDB索引实现

  注：存储引擎最小是表级别的，不同的表可以有不同的存储引擎。

  MyISAM存储引擎中索引是非聚集索引

  InnoDB存储引擎中索引是聚集索引

  为什么非主键索引结构叶子结点存储的是主键值？数据冗余、减少数据维护

  为什么InnoDB表必须有主键？？

  为什么推荐使用整型的自增主键？

  3.3 联合索引的底层存储结构、最左匹配原则

  按照联合索引的键值顺序排序的索引。

  mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，示例：对列col1、列col2和列col3建一个联合索引

   CREATE INDEX test_col1_col2_col3 ON test(col1,col2,col3);

  联合索引 test_col1_col2_col3 实际建立了(col1)、(col1,col2)、(col,col2,col3)三个索引。

  SELECT * FROM test WHERE col1=“1” AND clo2=“2” AND clo4=“4”

  上面这个查询语句执行时会依照最左前缀匹配原则，检索时会使用索引(col1,col2)进行数据匹配。

  创建联合索引时列的选择原则（参考博客创建联合索引时列的选择原则）：

  列的离散性计算：count（distinct col）/ count(col)

  例如：

  id列一共9列都不重复 9/9 = 1

  性别列一共9列只有（男或者女）两列 2/9 约等于0.2

  离散性越高选择性越大

  4. 索引失效的场景

  对索引使用左或者左右模糊匹配，也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效。原因在于查询的结果可能是多个，不知道从哪个索引值开始比较，于是就只能通过全表扫描的方式来查询。：

  select * from doc where title like ‘%XX’； --不能使用索引

  select * from doc where title like ‘XX%’； --非前导模糊查询，可以使用索引

  反向条件不走索引：负向条件有：!=、、not in、not exists、not like 等

  select * from doc where status != 1 and status != 2; --不能使用索引

  select * from doc where status in (0,3,4); --优化为 in 查询，可以使用索引

  IS NULL、IS NOT NULL 在无法使用索引，不过在mysql的高版本已经做了优化，允许使用索引

  在索引列做任何操作（计算、函数、表达式）会导致索引失效而转向全表扫描

  select * from doc where YEAR(create_time)