MYSQL性能优化之Mysql数据库索引优化

索引

• 当数据越多，查询越多，内存不能完全缓存全部数据时，就需要索引来更快的找到需要的数据。
• 存储引擎层实现，而不是服务器层。
  
  • 不同存储引擎上的索引工作方式可能是不同的。
  • 不是所有的存储引擎支持所有的索引类型
  • 同一种索引，在不同的存储引擎的实现方法也有可能不同

使用索引的优点

• 索引文件的大小远远小于数据文件的大小（Innodb发生一次IO，最小的存储单位为页，默认情况一页大小为16k,数据越多读取效率越快）

使用索引的缺点

• 索引越多，数据修改所需要的时间越长。（插入缓存，多次插入合并成一次插入）
• 增加数据导入速度：删除所有索引（Innodb，保证最少有一个自增的主键）
• 
  

Btree索引

B+树特点（便于查找）：

• 每一个叶子节点都包含一个指向下一个叶子节点的指针
• 每一个叶子节点到根部的距离都相同
• 所有的记录节点都是按照键值大小按照顺序存放到同层叶子节点上的（键值大小有限制，和数据引擎类型有关，Innodb:767个字节,MYISAM：1000个字节）

• 各个叶子节点通过指针来连接

  对于不同的存储引擎，具体的实现可能有所不同。MyIsam索引，在叶子节点上通过数据的物理位置来引用行的。而InnoDB则是通过主键来引用被索引的行的。
  

• 在索引根节点进行搜索，根节点存放下层指针，向下查找
• 指针定义了值得上限和下限
• 叶子节点指针指向被索引的数据
• B树索引是顺序存储的，所以很适合范围查找

适用场景

匹配最左前缀：比如联合索引，order_sn=’’ and order_funck=’‘。
查询条件中没有出现联合索引的第一列,而出现联合索引的第二列,或者第三列,都不会利用联合索引查询.

只访问索引的查询（不访问数据行），覆盖索引
除了查询过滤，还可以使用在order by(B树索引是顺序存储)

Btree索引使用限制

Hash索引

存储引擎Memory支持Btree和Hash索引。InnoDB支持的Hash索引是存储引擎根据Btree使用情况来自行建立的（自适应Hash索引）

Hash索引特点：

Hash索引只能用到等值查询中（范围，模糊都不适用）
Hash索引表中，保存每一个Hash码代表数据行的指针
值存储了Hash码，存储紧凑，找到数据的速度非常快

Hash索引限制：

先找到行，再对行的内容进行读取，所以查找数据必须进行两次读取（运行在内存中，所以影响不是很大）
Hash保存的是键值和Hash码，以及对应的行的指针（并没有保存字段的值）
Hash索引是按照Hash码来进行存储的，所以不能进行排序
Hash索引只可以进行全键值的查找，而不支持部分键值的查找（包括范围查找）
有可能产生Hash冲突，不同的索引列计算的Hash码是相同的（Hash码通常比较小）
不适合用于选择性很差的列上（键值列重复值很多）

索引策略优化

- 对字符串的前缀进行索引可以大大节约索引的空间（在创建索引时指定列的宽度，Innodb:767个字节：255个字符,MYISAM：1000个字节），不过前缀索引会降低选择性
- 唯一索引（主键索引）的选择性是最高的
- 选择性也决定了联合索引的顺序
- mysql5.0之前，每一个查询只能使用一列上的索引（每条列都建索引，你想搞事情？）
- 5.0之后，合并索引，需要更多的内存和磁盘IO来缓存数据
- 联合索引（优于合并）（选择索引列的顺序，左到右）
- 状态列就不适合放在最左边（选择性比较差）
- 选择性越高越能过滤出更多的数据

覆盖索引

覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。包括在查询里的Select、Join和Where子句用到的所有列（即建索引的字段正好是覆盖查询条件中所涉及的字段，也即，索引包含了查询正在查找的数据）。

优点

- 减少数据访问量（索引比较小，缓存可以保存更多的数据）
- 二次查询：Innodb二级索引在叶子节点中保存的是行的主键值。主键->行数据
- mysql只缓存索引信息，数据靠系统来缓存，所以访问数据需要进行一次系统调用（性能差）

mysql二级索引:以InnoDB来说，每个InnoDB表具有一个特殊的索引称为聚集索引。如果您的表上定义有主键，该主键索引是聚集索引。如果你不定义为您的表的主键 时，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚集索引。如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作 为聚簇索引

缺点：

• 不支持MEMORY
• 只有在索引的叶子节点中包括键值的索引才能建立覆盖索引（Hash就不能）
• mysql底层API限制，mysql服务器只能提取数据行的的值，在内存中进行过滤
  

演示数据库的安装

覆盖索引演示

不能使用覆盖索引

查询后把数据放在内存中，然后进行过滤

组合索引

在Innodb二级索引中，会自动加上主键

索引优化查询

索引扫描->生成有序结果（type列使用index来排列）：

• 通过排序操作
• 按照索引顺序扫描数据

使用索引扫描的条件：

示例：
Innodb的数据逻辑顺序和主键顺序是一致的。

MyISAM

同时使用文件来排序

联合索引（rental_data）

在Innodb中和MyIsam中两表查询结构是一样的（处理方式一致），都能使用二级索引来排序

当我们违反升降序一致的规则时：

rental_data是联合索引中最左边的列，当使用这一列的范围查找条件时，order by后面的两个字段就会失去效率

mysql对BTree索引长度有限制->在长字符串进行查找->前缀索引->可选择性差
mysql对BTree索引长度有限制->在长字符串进行查找->Hash索引(B树索引模拟Hash索引)

B树索引模拟Hash索引

5.7之前使用触发器，5.7使用虚拟列 Generated (Virtual) Columns来实现自动更新数据

可以看出，先使用索引(title_md5)放入内存中，然后使用where过滤（title）

ps：同时过滤title_md5和title是为了避免Hash冲突

利用索引优化锁

演示

使用事务来添加一个排它锁

当我们在另一个终端中也添加一个排它锁时，尽管查询的数据是不同的，但还是被阻塞了

rollbak;

添加索引

另一个终端：

索引的维护和优化

冗余索引

有意冗余索引：

联合索引键值过大，最左边列可以添加一列冗余索引（独立索引），增加性能。

检查冗余索引：

查找未被使用过的索引：

如果索引统计信息不准确，索引很可能会做出错误判断（根据信息来选择使用什么索引）

不同引擎，生成信息和保存信息的方法也会不同
MyISAM会把信息存储在磁盘中，所以需要进行全索引的扫描，所以需要对表进行锁定
Innodb通过随机访问的方式进行评估并存储在内存中。效率高，但是生成的信息不会十分的准确（估算值）
B树索引更新时会产生大量的碎片，降低查询效率（碎片索引以无序的方式存储在磁盘上），表也会查询碎片