高性能MYSQL（三）

2017-07-10 本文已影响0人 walker_liu_fei

MYSQL 只能高效利用最左前缀索引，对于不同的存储引擎，索引的实现也是不同的

BTree 索引

MyISAM 和 InnoDB的索引数据结构都是BTree索引，MyISAM在存储索引时利用了前缀压缩技术进行存储，可以节省存储空间。MyISAM通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行
B-Tree 对索引列是顺序组织存储的。所以很适合查找范围内数据
索引对多个值进行排序的依据是CREATE TABLE时中定义索引时列的顺序
B-Tree索引适用于全键值，键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。
B-Tree索引对如下类型的查询有效：全值匹配，匹配最左前缀，匹配列前缀，匹配范围值，精确匹配某一列并范围匹配另外一列，只访问索引的查询。
B-tree索引的限制：
如果不是按照索引的最左列开始查找，则无法使用索引。
不能跳过索引中的列
如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查询。

全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键词而不是直接比较索引中的值
全文索引适用的场景，有点类似于搜索引擎

在相同的列上同时使用全文索引并不会有冲突，全文索引匹配的操作是MATCH AGAINST，而不是普通的WHRER操作

索引的优点

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

索引三星评价
评价索引是否适合某查询

第一星
索引将相关data行放到一起

第二星
索引的data行按查询所需顺序排序

第三星
索引含查询全部列

索引的缺陷

索引存储也是需要空间的，所以，索引一般对于中大型的表才有使用价值

索引策略

不要在以索引列为条件查询时使用表达式：select * from actors where action_id + 1 = 5,对于这种查询语句，MYSQL是无法解析WHERE中的表达式，

将索引列单独放在比较符号的一侧

对于一些应用场景，利用前缀索引，不仅仅可以节省索引表存储的空间，而且可以加快比较的速度
当出现服务器对多个索引做交互操作的时候（多个AND条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引
当不考虑分组和排序时，将选择性最高的列放到索引的最前列

多列索引

多列索引又叫联合索引，不用于多个列的单独索引，多列索引能够很好的适用于类似

select * from auction where auction_id = "xxx" or auction_name  =  "xxx"

这样的查询。

如果是两个单独索引的话，这样的查询会直接走全表的查询,两个单独的索引排不上用场，除非查询改成

select * from auction where acution_id  = "xx" unoin all select * from auction where auction where auciton_name = "xxx" and auction_id != "xxx"

当应用中的sql语句的where 条件中出现大量的多列的AND 或者OR 操作时，多列索引很有可能能够派上
另外索引的顺序也会决定一个索引设计的好坏，通常来讲，将选择性最高的索引放在第一位是经验方法

聚簇索引

聚簇索引中，索引树的叶级页包含实际的数据：记录的索引顺序与物理顺序相同。在非聚簇索引中，叶级页指向表中的记录：记录的物理顺序与逻辑顺序没有必然的联系。
一般来说，DBMS都会以聚簇索引的形式来存储实际的数据，它是其它二级索引的基础。

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。InnoDB的聚簇索引实际上实在同一个结构中保存了BTree索引和数据行
存储特点：
- 聚集索引。表数据按照索引的顺序来存储的，也就是说索引项的顺序与表中记录的物理顺序一致。对于聚集索引，叶子结点即存储了真实的数据行，不再有另外单独的数据页。
- 在一张表上最多只能创建一个聚集索引，因为真实数据的物理顺序只能有一种。
  非聚集索引。表数据存储顺序与索引顺序无关。对于非聚集索引，叶结点包含索引字段值及指向数据页数据行的逻辑指针，其行数量与数据表行数据量一致。
InnoDB默认通过主键来聚集索引，没有主键，InnoDB会默认选择一个索引，如果没有索引，它会创建一个主键
聚簇索引带来的优势
把先关的数据绑定在一起，减少IO的次数
使用覆盖索引的查询可以直接使用页节点中的主键值
聚簇索引的适用范围
1、主键列,该列在where子句中使用并且插入是随机的。
2、按范围存取的列，如pri_order > 100 and pri_order < 200。
3、在group by或order by中使用的列。
4、不经常修改的列。
5、在连接操作中使用的列。

覆盖索引

覆盖索引
直接在索引上保存表数据，哈希索引，空间索引和全文索引都不存储索引列的值，MYSQL只能用BTree来覆盖索引。

InnoDB的二级索引的叶子节点都包含了主键的值，这意味着InnoDB的二级索引可以有效的利用这些额外的主键来覆盖查询

使用索引扫描来做排序

mysql 的排序

indexsort 利用有序索引获取有序数据
原理：我们知道，mysql的基础数据结构是B+树，任何的一个表都是一颗B+树，你在表上建的索引也是一颗B+树，B+树的特别是在叶子节点上是有序，且前一个节点存在指向相邻节点的指针。那么在写SQL中的ORDER BY语句时候，若是ORDER BY的条件和返回的数据都在一颗树上，那么就可以利用B+树自身的特点来天然排序了，自然效率会比较高。

使用条件：

查询的WHERE子句和ORDER BY子句中查询的字段在同一颗索引树上，
ORDER BY 字段的顺序是跟建立索引的顺序是一致的。
查询的字段也在同一颗索引树
以上三个条件必须同时满足

2.filesort 文件排序
原理：这里的文件排序并不是字面那表示的意思，利用了磁盘IO来进行排序，不过是优化器告诉你，进行了一个排序操作，具体排序的地方还是内存，相对应的参数是sort_buffer_size 设定的大小

filesort不一定会产生临时表
filesort 与临时表数据写入磁盘是没有任何直接联系

只有当索引的列顺序和ORDER BY 子句的顺序完全一致，并且所有列的排序方向都一样时，MYSQL才能够使用索引来对结果排序。

如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表是，才能使用索引做排序。

ORDER BY 和WHERE 子句一样都是需要满足索引最左前缀的要求，即，第一个条件需要时索引列

不能用索引排序的查询:

使用了不同的【排序汤相，但是索引列都是正序排列的
..where rental_date = 'xx' order by column_1 desc ,column_2 asc
ORDER BY 子句引用了一个不在索引的列
WHERE和ORDERBY 中的列无法组合成索引的最左前缀

索引和锁

InnoDB 只有在访问行的时才会对其加锁（行级锁），而索引能够减少InnoDB访问的次数，从而减少锁的数量

InnoDB在二级索引上是使用共享（读）锁，但访问主键索引需要排它(写)锁，这消除了使用覆盖索引的可能性，并且，使得SELECT FOR UPDATE 比LOCK IN SHARE MODE 或费锁定查询要慢许多

案例与总结

考虑表上所有的选项，当设计索引时，不要只为现有的查询考虑需要的那些索引，还需要考虑对查询进行优化，如果发现某些查询需要创建新索引，但是这个索引会降低另一些查询的效率，那么应该想一想是否能优化原来的查询。

避免多个范围的查询

duib

MYSQL松散索引扫描

参考： MySQL松散索引扫描与紧凑索引扫描

维护索引和表

维护表的三个目的：找到并修复损坏的表，维护准确的索引统计信息，减少碎片
InooDB通过抽样的方式来计算统计信息，首先随机的读取少量的索引页面，然后一起为样本计算索引的统计信息。可以通过innodb_stats_sample_pages 来设置样本页的数量。设置的值更大，理论上来说可以帮助生成更准确的索引信息

Btree需要随机磁盘访问才能定位到叶子页，所以随机访问是不可避免的,如果叶子节点在物理分布上是顺序而且紧密的，那么查询的性能就会变得更好。
对于表的数据存储来说，数据存的碎片化有三种类型

行碎片：一个行的数据被存储到多个地方的多个片段中
行间碎片：逻辑上循序的行，在磁盘上存储的不是顺序的
剩余空间碎片：剩余空间碎片是指数据页中有大量的剩余空间，这会导致非要我要去读取大量不需要的数据，从而造成浪费

结论

在选择索引和编写利用这些索引时，有如下的三个原则：

单方访问是很慢的，如果服务器从存储中仅仅是为了获取其中的一行，那么就浪费了很多工作
按顺序访问范围数据是很快的
索引覆盖的的查询是很快的