MySQL索引以及优化小计

什么是MySQL索引？

索引是对数据库表中一列或多列的值进行排序的一种结构。一个非常恰当的比喻就是书的目录页与书的正文内容之间的关系，为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。

MySQL索引有哪些类型？分别有什么区别？

• 主键索引: 数据列不允许重复，不允许为NULL.一个表只能有一个主键。
• 唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引
• 普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。
• 全文索引： 是目前搜索引擎使用的一种关键技术。

如何创建MySQL索引？

ALTER TABLE table_name ADD UNIQUE (column); #创建唯一索引
ALTER TABLE table_name ADD UNIQUE (column1,column2); #创建唯一组合索引
ALTER TABLE table_name ADD INDEX index_name (column); #创建普通索引
ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3); #创建组合索引
ALTER TABLE table_name ADD FULLTEXT (column); #创建全文索引

Coder们常说的最左前缀，最左优先是什么？

顾名思义，就是最左优先，在创建多列索引(又名联合索引)时，要根据业务需求，where子句中使用最频繁的一列放在最左边。

还有一个就是生效原则 比如：

index(a,b,c)
where a=3	#只使用了a
where a=3 and b=5	#使用了a,b
where a=3 and b=5 and c=4	#使用了a,b,c
where b=3 and where c=4	#没有使用索引
where a=3 and c=4	#仅使用了a

注：以上where子句的字段条件顺序并不影响生效原则

面试中常问的MySQL索引算法是什么？

B-tree、B+tree(B树算法、B加树算法)：

BTree是最常用的mysql数据库索引算法，也是mysql默认的算法。因为它不仅可以被用在=,>,>=,<,<=和between这些比较操作符上，而且还可以用于like操作符，只要它的查询条件是一个不以通配符开头的常量， 例如：

select * from user where name like 'jack%'; 
# 如果一通配符开头，或者没有使用常量，则不会使用索引，例如(mysql 5.6之后解决的这个问题，但是在某些情况下依然会索引失效)： 
select * from user where name like '%jack'; 

Hash算法

Hash Hash索引只能用于对等比较，例如=,<=>（相当于=）操作符。由于是一次定位数据，不像BTree索引需要从根节点到枝节点，最后才能访问到页节点这样多次IO访问，所以检索效率远高于BTree索引。

索引有哪些设计原则？

1. 适合索引的列是经常出现在where子句中的列，或者连接子句中指定的列
2. 基数较小的列，索引效果较差，没有必要在此列建立索引(例如一些记录状态列的)
3. 使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间
4. 不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

哪些情况下索引会失效呢？

1. 在索引上进行任何操作，使用函数、计算、类型转换都会导致索引失效，进而进行全表扫描
2. msyql在使用不等于(!=、<>)的时候会导致索引失效。
3. is null和is not null也无法使用索引
4. like通配符在左边时不使用索引(5.6版本之前)
5. 整数类型的列中查询加了引号时会导致索引失效(同1)
6. 使用or查询会导致索引失效

MySQL的排序(ORDER BY)如何使用索引优化？

MySQL支持两种方式的排序，FileSort和Index,Index效率高。它指MySQL扫描索引本身完成排序。FileSort方式效率较低。

ORDER BY 满足两种情况，会使用Index方式排序

1. ORDER BY语句使用索引最左前列
2. 使用WHERE 子句与ORDER BY子句条件列组合满足索引最左前列

提高Order By的速度

1、Order by时select *是一个大忌只Query需要的字段，这点非常重要。在这里的影响是：

1.1 当Query的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时，会用改进后的算法-单路排序，否则用老算法一一多路排序。

1.2 两种算法的数据都有可能超出sort_buffe啲容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O,但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size

2、尝试提高 sort_buffer_size，不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的

3、尝试提高 max_length_for_sort_data，提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率。

GROUP BY 关键字优化

1. group by 实质是先排序后分组，遵照索引的最佳左前缀。
2. 当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
3. where 高于having，能写在where限定的条件就不要去having去限定了。

本作品采用知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议进行许可，非商业性质可转载须署名链接，详见本站版权声明。