【mysql】PHP面试题 -索引总结

什么是数据库索引？索引有哪几种类型？什么是最左前缀原则？索引算法有哪些？有什么区别？

主键索引:数据列不允许重复，不允许为NULL.一个表只能有一个主键。

唯一索引:数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。

普通索引:基本的索引类型，没有唯一性的限制，允许为NULL值。

全文索引：是目前搜索引擎使用的一种关键技术。

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);	//创建全文索引

最左原则

在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。

CREATE TABLE test ( id int(11),a int(11), b  int(11) ,c int(11));
ALTER table test_key add INDEX a_b_c (a,b,c);

EXPLAIN SELECT * FROM test WHERE a = 1 and b = 1 AND c = 1;	//a,b,c（使用索引）
EXPLAIN SELECT * FROM test WHERE c = 1 AND b = 1 AND a = 1;	//c,b,a（使用索引）
EXPLAIN SELECT * FROM test WHERE c = 1 AND a = 1 AND b = 1;	//c,a,b（使用索引）
EXPLAIN SELECT * FROM test WHERE a = 1 and b = 1;			//a,b（使用索引）
EXPLAIN SELECT * FROM test WHERE a = 1 and c = 1;			//a,c （使用索引）
EXPLAIN SELECT * FROM test WHERE c = 1 AND a = 1;			//c,a （使用索引）
EXPLAIN SELECT * FROM test WHERE c = 1;						//c (没有使用索引)
EXPLAIN SELECT * FROM test WHERE b = 1;						//b (没有使用索引)
EXPLAIN SELECT * FROM test WHERE b =1AND c = 1;				//b,c(未使用索引)
EXPLAIN SELECT * FROM test WHERE c = 1 AND b = 1;			//c,b(未使用索引)

MySQL 关于BTree 搜索知识点

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

select * from test where name like 'cap%'; 
以通配符开头，或者没有使用常量，则不会使用索引，例如： 
select * from test where name like '%cap'; 

MySQL 关于EXPLAIN 使用问题

对于查询语句，最重要的优化方式就是使用索引。 而执行计划，就是显示数据库引擎对于SQL语句的执行的详细情况，其中包含了是否使用索引，使用什么索引，使用的索引的相关信息等。

执行计划包含的信息 id 有一组数字组成。表示一个查询中各个子查询的执行顺序;

• id相同执行顺序由上至下。
• id不同，id值越大优先级越高，越先被执行。
• id为null时表示一个结果集，不需要使用它查询，常出现在包含union等查询语句中。

select_type 每个子查询的查询类型，一些常见的查询类型。

id	select_type	description
1	SIMPLE	不包含任何子查询或union等查询
2	PRIMARY	包含子查询最外层查询就显示为 PRIMARY
3	SUBQUERY	在select或 where字句中包含的查询
4	DERIVED	from字句中包含的查询
5	UNION	出现在union后的查询语句中
6	UNION RESULT	从UNION中获取结果集，例如上文的第三个例子

table 查询的数据表，当从衍生表中查数据时会显示 x 表示对应的执行计划id partitions 表分区、表创建的时候可以指定通过那个列进行表分区。

type(非常重要，可以看到有没有走索引) 访问类型

一般来说至少要达到 range ，要求一般是ref ，最好能达到 consts
index 全扫描，速度非常慢

type	description
ALL	扫描全表数据
index	遍历索引
range	索引范围查找
index_subquery	在子查询中使用 ref
unique_subquery	在子查询中使用 eq_ref
ref_or_null	对Null进行索引的优化的 ref
fulltext	使用全文索引
ref	使用非唯一索引查找数据
eq_ref	在join查询中使用PRIMARY KEYorUNIQUE NOT NULL索引关联。

possible_keys 可能使用的索引，注意不一定会使用。查询涉及到的字段上若存在索引，则该索引将被列出来。当该列为 NULL时就要考虑当前的SQL是否需要优化了。

key 显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。

TIPS 查询中若使用了覆盖索引(覆盖索引：索引的数据覆盖了需要查询的所有数据)，则该索引仅出现在key列表中

key_length 索引长度

ref 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows 返回估算的结果集数目，并不是一个准确的值。

extra 的信息非常丰富，常见的有：

1. Using index 使用覆盖索引
2. Using where 使用了用where子句来过滤结果集
3. Using filesort 使用文件排序，使用非索引列进行排序时出现，非常消耗性能，尽量优化。
4. Using temporary 使用了临时表

简单先写到这里，想起来在做补充