DB[0]：mysql-SQL & 索引性能优化

关于优化

第一阶段 sql 优化&索引优化
第二阶段搭建缓存
第三阶段读写分离
第四阶段利用分区表（不推荐）
第五阶段垂直拆分
第六阶段水平拆分

sql 优化&索引优化

执行计划分析

# 表结构
CREATE TABLE `beas_class_type` (
  `id` int(11) NOT NULL,
  `cname` varchar(500) DEFAULT NULL,
  `ename` varchar(500) DEFAULT NULL,
  `code` varchar(200) DEFAULT NULL,
  `gid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code_gid` (`code`,`gid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

EXPLAIN 执行计划中 type 字段分为以下几种：

性能从差到优
ALL < INDEX < RANGE < REF < EQ_REF < CONST,SYSTEM < NULL

ALL

type = ALL，全表扫描，MYSQL 扫描全表来找到匹配的行。

INDEX

type = index，索引全扫描，MYSQL 遍历整个索引来查找匹配的行。（虽然 where 条件中没有用到索引，但是要取出的列是索引包含的列，所以只要全表扫描索引即可，直接使用索引树查找数据）。
sql sql

RANGE

type = range ，索引范围扫描，常见于<、<=、>、>=、between、in 等操作符
sql sql
sql sql

REF

type = ref ，使用非唯一性索引或者唯一索引的前缀扫描，返回匹配某个单独值的记录行。

注意下边两张图的索引使用情况

sql sql
sql sql

EQ_REF

type = eq_ref，相对于 ref 来说就是使用的是唯一索引，对于每个索引键值，只有唯一的一条匹配记录（在联表查询中使用 primary key 或者 unique key 作为关联条件）

CONST,SYSTEM

type = const/system，单表中最多只有一条匹配行，查询起来非常迅速，所以这个匹配行中的其他列中的值可以被优化器在当前查询中当做常量来处理。例如根据主键或者唯一索引进行的查询。
sql.png
sql.png

NULL

type = NULL，MYSQL 不用访问表或者索引就直接能到结果。

索引优化

常用的索引有：B-Tree 索引，哈希索引，空间索引，全文索引。除此之外，按照数据存储方式可以分为聚集索引和非聚集索引。

B-Tree 索引

B-Tree 索引
结构中，每一层节点均从左往右从小到大排列，key1 < key2 < … < keyN，对于小于 key1 或在[key1，key2)或其他的值的节点，在进入叶子节点查找，是一个范围分布，同时，同一层节点之间可直接访问，因为他们之间有指针指向联系（MyISAM 的 BTREE 索引没有）。每次搜索是一个区间搜索，有的话就找到了，没有的话就是空。索引能加快访问速度，因为有了它无需全表扫描数据（不总是这样），根据查找的值，跟节点中的值比较，通常使用二分查找，对于排好序的数值来说，平均速度几乎是最快的。

val 指向了哪里，对于 InnoDB，它指向的就是表数据，因为 InnoDB 的表数据本身就是索引文件，这是与 MyISAM 索引的显著区别，MyISAM 的索引指向的是表数据的地址（val 指向的是类似于 0x7DFF…之类）。

前面在 BTREE 的抽象结构中，索引值的节点是放在页中的，这里有两个需注意的问题：

计算机的存储空间是一块一块的，通常一块用完了再用另一块，如果上一块只剩余 5kb，但这里刚好要申请 8kb 的空间，就需要在一个新的块上申请这个空间，然后以后的申请又接在这个 8kb 后面，只要这个块的空间足够，那么上一块的 5kb 通常就成了所谓的“碎片”。在 mysql 中，这里的页可理解为块存储空间，即索引的树节点是存放在页中的，每一页（称为逻辑页）有固定大小，InnoDB 目前是 16kb，一页用完了，当继续插入表生成新的索引节点时，就去新的页中存储这个节点，再有新的节点就继续放在这个新的页的节点后面。
页分裂问题，一页总要被存满，然后新开一页继续，这种行为被称作页分裂。何时开辟新的页，mysql 规定了一个分裂因子，达到页存储空间的 15/16 则存到下一页。页分裂的存在可能极大影响性能维护索引的性能。通常提倡的是，设定一个无意义的整数自增索引，有利于索引存储。

如果非自增或不是整数索引，如非自增整数、类似 MD5 的字符串，以他们作为索引值时，因为待插入的下一条数据的值不一定比上一条大，甚至比当前页所有值都小，需要跑到前几页去比较而找到合适位置，InnoDB 无法简单的把新行插入到上一行后面，而找到并插入索引后，可能导致该页达到分裂因子阀值，需要页分裂，进一步导致后面所有的索引页的分裂和排序，数据量小也许没什么问题，数据量大的话可能会浪费大量时间，产生许多碎片。

主键总是唯一且非空，InnoDB 自动对它建立了索引（primary key），对于非主键字段上建立的索引，又称辅助索引，索引排列也是顺序排列，只是它还附带一个本条记录的主键值的数据域，不是指向本数据行的指针，在使用辅助索引查找时，先找到对应这一列的索引值，再根据索引节点上的另一个数据域—主键值，来查找该行记录，即每次查找实际经过查找了两次。额外的数据域存储主键值的好处是，当页分裂发生时，无需修改数据域的值，因为即使页分裂，该行的主键值是不变的，而地址就变了。

包含一列的索引称为单列索引，多列的称为复合索引，因为 BTREE 索引是顺序排列的，所以比较适合范围查询，但是在复合索引中，还应注意列数目、列的顺序以及前面范围查询的列对后边列的影响。

BTREE 索引使用

在 BTREE 索引的使用上，以下几种情况可以用到该索引或索引的一部分（使用 explain 简单查看使用情况）：

全值匹配
匹配最左列，对于复合索引来说，不总是匹配所有字段列，但是可以匹配索引中靠左的列
匹配列前缀，即一个索引中列的前一部分，主要用在模糊匹配（like ‘xxx%’）
匹配范围
精确匹配一列并范围匹配右侧相邻列，即前一列是固定值，后一列是范围值
只访问索引的查询(select 字段都存在索引)
前缀索引
某些列，一般是字符串类型，很长，全部作为索引大大增加存储空间，索引也需要维护，对于长字符串，又想作为索引列，一个可取的办法就是取前一部分（前缀），代表一整列作为索引串，问题是：如何确保这个前缀能代表或大致代表这一列？所以 mysql 中有个概念是索引的选择性，是指索引中不重复的值的数目（也称基数）与整个表该列记录总数（#T）的比值，比如一个列表（1,2,2,3），总数是 4，不重复值数目为 3，选择性为 3/4，因此选择性范围是[1/#T, 1]，这个值越大，表示列中不重复值越多，越适合作为前缀索引，唯一索引（UNIQUE KEY）的选择性是 1。
1
2
alter table tab add index idx_pn(name(9)); --单独前缀索引
alter table tab add index idx_cpn(count, name(9)); --复合前缀索引

以上为常见的使用索引的方式，有这么些情况不能用或不能全用，有的就是上面情况的反例，以 key(a, b, c)为例

跳过列，where a = 1 and c = 3，最多用到索引列 a；where b = 2 and c = 3，一个也用不到，必须从最左列开始
前面是范围查询，where a = 1 and b > 2 and c = 3，最多用到 a, b 两个索引列；
索引列上使用了表达式，如 where substr(a, 1, 3) = ‘hhh’，where a = a + 1，表达式是一大忌讳，再简单 mysql 也不认。有时数据量不是大到严重影响速度时，一般可以先查出来，比如先查所有有订单记录的数据，再在程序中去筛选以’cp1001’开头的订单，而不是写 sql 过滤它；
模糊匹配时，尽量写 where a like ‘J%’，字符串放在左边，这样才可能用得到 a 列索引，甚至可能还用不到，当然这得看数据类型，最好测试一下。

排序对索引的影响

排序也遵循最左前缀列的原则

哈希索引

类似于数据结构中简单实现的 HASH 表（散列表）一样，当我们在 mysql 中用哈希索引时，也是对索引列计算一个散列值（类似 md5、sha1、crc32），然后对这个散列值以顺序（默认升序）排列，同时记录该散列值对应数据表中某行的指针。

建立 hash 索引，生成 hash 值按顺序排列，但是顺序排列的 hash 值并不对应表中记录，从地址指针可反应出来，而且，hash 索引可能建立在两列或者更多列上，取得是多列数据后的 hash 值，它不存储表中数据。它先计算列数据的 hash 值，与索引中的 hash 值比较，找到了然后比对列数据是否相等，可能涉及其他列条件，然后返回数据。hash 当然会有冲突，即碰撞，除非有很多冲突，一般 hash 索引效率很高，否则 hash 维护成本较高，因此哈希索引通常用在选择性较高的列上面。哈希索引的结构决定了它的特点：

hash 索引只是 hash 值顺序排列，跟表数据没有关系，无法应用于 order by；
hash 索引是对它的所有列计算哈希值，因此在查询时，必须带上所有列，比如有(a, b)哈希索引，查询时必须 where a = 1 and b = 2，少任何一个不行；
hash 索引只能用于比较查询 = 或 IN，其他范围查询无效，本质还是因不存储表数据；
一旦出现碰撞，hash 索引必须遍历所有的 hash 值，将地址所指向数据一一比较，直到找到所有符合条件的行。

文章目录