MySQL索引设计原则与索引优化

设计原则

1. 代码先行，索引后上
   建完表之后，要等到业务主体功能开发完毕，把涉及到该表相关SQL拿出来分析后再考虑是否建索引
2. 联合索引尽量覆盖条件
   对一张表可以设计一两个联合索引，注意保证联合索引的顺序以尽量满足最左前缀原则，让联合索引的字段都尽量覆盖where、groupby、orderby等条件的字段
3. 不要再小基数字段上建索引，如只有几个值的字段
4. 对于长字符串可采用前缀索引，如name(20)
5. where与order by冲突时，优先where
6. 优化慢查询SQL
   “set global long_query_time = 4” 设置慢SQL查询时间；
   “set global slow_query_log = 1” 开启慢SQL查询日志；

尽量选择范围更小的数据类型

通常情况下，更小的数据类型范围会更好，会占用更少的内存、磁盘及CPU缓存和更少的CPU处理周期；若无法确定哪个更好，就选择不会超过范围内的最小范围类型

尽量使用内置简单数据类型

简单数据类型通常有更好的CPU处理周期，如应该用mysql自带的类型而不是字符串来存储日期和时间

尽量避免保存NULL值

最好将列都置为NOT NULL，除非确实可能需要保存NULL值；
存在NULL值的列在索引优化与值比较方面更复杂；
保存NULL值的列可能需要额外的空间，当可为NULL的列被索引时，每个索引记录需要额外一个字节存储；

整数类型

存储整数可用类型：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT，分别为1,2,3,4,8个字节；
整数有可选属性：UNSIGNED（无符号），表示不允许负值，能存储比原来多一倍的正数。如TINYINT存储范围为-128 ~ 127，而TINYINT UNSIGNED存储范围为0 ~ 255；
整数类型的宽度：INT(n)，宽度n不限制值的合法范围，只规定mysql一些客户端用来显示字符的个数，存储和计算上INT(1)和INT(20)是一样的。选择上遵循更小的更好原则；

实数类型

实数是带有小数部分的数字，mysql支持的精确实数类型DECIMAL，非精确实数类型FLOAT和DOUBLE类型；
MySQL 5.0 和更高版本将数字打包保存到一个二进制字符串中，每4个字节存9个数字。例如：DECIMAL(18,9)小数点两边将各存储9个数字，一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节；
MySQL 5.0 以后的版本中 DECIMAL 类型允许最多 65 个数字。浮点类型在存储同样范围的值时，通常比 DECIMAL 使用更少的空间。FLOAT 使用 4 个字节存储，DOUBLE占用8个字节，所以 DOUBLE 比 FLOAT有更高的精度和更大的范围；
因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用 DECIMAL；
在数据量比较大的而且要求精度时，可以考虑使用 BIGINT 代替 DECIMAL；

字符串类型

存储字符串可用类型：VARCHAR，CHAR，BLOB，TEXT，ENUM和SET类型；
VARCHAR：可变长字符串，比定长类型更节省空间，因为它仅使用必要的空间，在实现上VARCHAR需要使用 1 或 2个额外字节记录字符串的长度，如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节。VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是变长的，在UPDATE时新值比旧值长时，使行变得比原来更长，这就可能导致需要做额外的工作。如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，在这种情况下，MyISAM会将行拆分成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内。

CHAR：定长字符串，根据定义的字符串长度分配足够的空间，CHAR适合存储很短的字符串，或者所有值定长或都接近同一个长度，对于经常变更的数据，CHAR 也比 VARCHAR更好，因为定长的CHAR类型不容易产生碎片。用CHAR(1)来存储只有Y 和 N 的值，如果采用单字节字符集只需要一个字节，但是 VARCHAR(1) 却需要两个字节，因为还有一个记录长度的额外字节。

索引常用优化

索引下推优化

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

范围条件放最后

联合索引是按创建索引时的顺序进行分组排序的，尽量将范围条件放到最后使SQL查询时走索引

覆盖索引优化

索引是按照列值顺序存储的，对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少得多。由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询；
尽量使用覆盖索引，只访问索引列和查询列一致，不是必要的情况下减少 select * ，除非是需要将表中的全部列检索后进行缓存

OR关键字

一个使用到索引的搜索条件和没有使用该索引的搜索条件使用 OR 连接起来后是无法使用该索引，可能会走全表扫描

IN与EXISTS关键字

依据小表驱动大表原则，即小的数据集驱动大的数据集。

-- 当B表的数据集小于A表的数据集时，IN优于EXISTS，IN先执行
select * from A where id IN (select aid from B)
等价于(先查出所有B的结果集，再对B结果集中的每个结果aid进行循环查A中的数据)
for (select aid from B) {
	select * from A where A.id = B.aid
}
-- 当A表的数据集小于B表的数据集时，EXISTS优于IN，将主查询A的结果数据作为子查询B的条件验证，根据验证结果(true或false)来确定主查询中的数据是否保留
-- 注：A表id与B表的aid应建立索引
select * from A where EXISTS (select 1 from B where A.id = B.aid)
等价于
for (select * from A) {
	select * from B where A.id = B.aid
}

EXISTS子查询只返回TRUE或FALSE，子查询中的 select * 可以替换为select 1，实际MySQL执行时会忽略SELECT清单。
注：EXISTS子查询实际执行过程可能经过优化而不是逐条对比；EXISTS子查询在某种场景可以使用JOIN代替

COUNT关键字

COUNT(*) , COUNT(1), COUNT(字段), COUNT(主键ID)之间效率对比

条件	效率	说明
字段有索引	count(*)≈count(1)>count(字段)>count(主键id)	字段有索引，count(字段)统计⾛⼆级索引，⼆级索引存储数据⽐主键索引少，所以count(字段)>count(主键id)
字段无索引	count(*)≈count(1)>count(主键id)>count(字段)	字段没有索引count(字段)统计⾛不了索引，count(主键id)还可以⾛主键索引，所以count(主键id)>count(字段)

COUNT(1)与COUNT(字段)类似，不同的是COUNT(1)用常量1而不需要取出字段作统计，故COUNT(1)比COUNT(字段)稍快
COUNT(id),Mysql会选择辅助索引而不是主键索引，因为辅助索引相对主键索引存储的数据少，故效率上会更高
COUNT(*)在MYSQL中做了优化，不会将全局字段取出进行统计，而是按行累加效率更高

LIKE查询

LIKE以通配符“%jeffrey”会导致索引失效变成全表扫描，若在上述通配符基础上使用覆盖索引优化会走辅助索引，type为index
有些搜索条件中虽然出现了索引列，但却不能使用到索引，比如下边这个查询：explain select * from user_role where uid > 1 and uid like ‘%2’; 老版本MySQL该查询不能使用到索引，而新版本对其进行索引条件下推优化，减少了回表次数提高效率

扫描区间

对于B+Tree索引来说，只要索引列和常数使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=（不等于也可以写成<>）或者LIKE操作符连接起来，就可以产生一个区间

1. IN 操作符的效果和若干个等值匹配操作符 = 之间用 OR 连接起来是一样的，也就是说会产生多个单点区间
2. != 产生的扫描区间
3. LIKE 操作符比较特殊，只有在匹配完整的字符串或者匹配字符串前缀时才产生合适的扫描区间。 对于某个索引列来说，字符串前缀相同的记录在由记录组成的单向链表中肯定是相邻的

排序分组扫描索引优化

MySQL通过排序操作或者按索引顺序扫描两种方式生成有序结果；

MySQL可以使用同一个索引既满足排序又用于查找行，因此设计索引时尽可能同时满足如下条件：

1. 只有当索引列顺序与order by子句的顺序完全一致并且所有列的排序方向都一样时，MySQL才会使用索引进行排序
2. 若查询需要关联多张表，则只有当order by子句中的字段全部为第一张表时才能使用索引做排序

文件排序：MySQL在内存中或磁盘上进行排序的操作（Using filesort）
注：Mysql group by操作默认会进行Order by排序操作，若禁用group by子句查询排序则可以显示加上 order by null.

分页查询

默认情况下，分页查询 limit 10000, 10 可以查询10000-10010的10条数据，MySQL执行时会直接查询出1-10010条数据再丢弃掉前面10000条数据最后保留10条数据作为结果集返回。limit这种分页查询在数据量很大的时候查询会特别慢。

自增连续主键的分页查询

如果数据表中的数据不会被删除，并且使用自增且连续的主键，那么limit 10000, 10可以改为 select * from A where id > 10000 limit 10 进行优化（type为range）
如果数据表中的部分数据被删除，并且使用自增且连续的主键时，那么使用limit 10000,10和where id > 10000 limit 10的结果可能会有所差异；所以在使用这种方式优化分页查询时可以采用动态传入id的方式，如第一次查询10条数据时，将这10条数据的最后一条数据的id作为下一次查询时的条件，下一次查询时查询的是比这次查到的最后一条数据id还要大的10条数据即可

非主键字段的排序分页查询

例如在user表中存在name, age, address联合索引，使用name字段作为排序字段，当数据量较大时执行 select * from user order by name limit 10000, 10 可能会走全表扫描而不会走联合索引。
上述全表扫描情况的优化为：select * from user A inner join (select id from user order by name limie 10000, 10) B on A.id = B.id 该优化首先会查询子查询使用了覆盖索引走了联合索引树，然后查询衍生表B表的结果只有10条数据会走全表扫描，最后查询A表使用了主键索引，排序是using index是在索引树中进行的，整体上基本都走了索引，效率较高

关联查询

示例：现有两个结构一样的表A，B，均有一个索引id, 其中A表的数据10000条，B表数据100条

NLJ嵌套循环连接算法

NLJ算法表示一次一行循环的从第一张表（驱动表）中读取行，在这行数据中取出关联字段，再通过关联字段的值在另一张表（被驱动表）中读取出满足条件的行，最后取出两张表的结果合集

select * from A inner join B on A.id = B.id
首先根据过滤条件过滤出结果数据的多少来判断是否为驱动表，由于SQL没有where其他过滤条件故根据关联字段条件判断A表数据有10000条件，B表数据为100条，依据数据量小的表作为驱动表原则，所以B表为驱动表先进行查询，此时会进行磁盘扫描100次读取出100条数据，由于是索引关联，所以B表中的每条数据都可以根据索引快速在A表中找到关联的行，即B表中的每行数据都可以在A表中磁盘扫描1次查找得到，所以共计磁盘扫描200次。

注意：当使用inner join时不能直接判断前表还是后表是驱动表还是被驱动表，而left join 和right join 则直接确定好的left join表示左表为驱动表，right join表示右表为驱动表；当使用join时mysql会选择数据量小的作为驱动表，大的作为被驱动表

执行连表查询时若关联字段为索引字段则会使用NLJ算法；
一般join语句中，执行计划Extra列若未出现Using join buffer, 则表示使用了NLJ算法

BNL基于块的嵌套循环连接算法

BNL算法表示将驱动表的数据读取到join buffer块状缓冲区，然后扫描被驱动表，再将被驱动表中的每条数据与join buffer做对比，满足则返回并继续下一条数据对比；
join buffer大小由参数join_buffer_size设定的，默认值是256k。如果放不下B表所有数据，就采用分段放方式，先放⼊B表部分数据，对⽐完成后，清空掉join_buffer，再读取剩下的数据，这样会导致A表扫描次数的增加。

执行连表查询时若关联字段为非索引字段则会使用BNL算法；
一般join语句中，执行计划Extra列若出现了Using join buffer, 则表示使用了BNL算法

select * from A inner join B on A.name = B.name
首先由于name非索引，所以在表关联查询时会使用BNL算法；会先从B表做一次全表扫描100次将数据放到join buffer, 然后在从A表全表扫描10000次，对A表中的每一行数据拿出来与join buffer中B表的每一行数据进行比对，满足则返回并继续下一个数据比对，所以共计磁盘扫描10010次，join buffer中内存比对1000000(10000*100)次。

因此MySQL对于被驱动表的关联字段没索引的关联查询，⼀般都会使⽤BNL算法。如果有索引⼀般选择NLJ算法，有索引的情况下NLJ算法⽐BNL算法性能更⾼

straight_join

straight_join功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执⾏顺序;
straight_join只适⽤于inner join，并不适⽤于left join，right join。（因为left join，right join已经代表指定了表的执⾏顺序）;
尽量关联字段加索引，让mysql做join操作时尽量选择NLJ算法，写多表连接sql时如果明确知道哪张表是⼩表可以⽤straight_join写法固定连接驱动⽅式，省去mysql优化器⾃⼰判断的时间

大小表

在决定哪个表做驱动表的时候，应该是两个表按照各⾃的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，⽽不单纯只看表数据量，最后过滤完成后的数据量⼩的那个表，就是“⼩表”，应该作为驱动表