谈谈MySQL的索引

TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。 ALTER TABLE table_name ADD INDEX index_name (column_list); # CREATE INDEX可对表增加普通索引或UNIQUE索引。 CREATE INDEX index_name ON table_name (column_list) # 删除 DROP INDEX index_name ON talbe_name ALTER TABLE table_name DROP INDEX index_name # 这个只在删除主键的时候使用 常常一张表只有一个主键 ALTER TABLE table_name DROP PRIMARY KEY

下面的是简单使用情况以及结果分析（有索引和没索引的分析），先看一开始表结构的索引情况

执行以下语句，建立一个first_name_last_name索引。

USE myemployees;
SHOW TABLES;
DESC employees;
# 建立了二级索引，是一个联合索引
ALTER TABLE employees ADD INDEX first_name_last_name
(first_name, last_name);
# 为了明确看到查询性能，我们启用profiling并关闭query cache：
SET profiling = 1;
SET query_cache_type = 0;
SET GLOBAL query_cache_size = 0;
# 用EXPLAIN来查看sql语句执行的情况
EXPLAIN SELECT * from employees WHERE first_name=‘Alyssa‘ AND last_name LIKE ‘%on‘;

DESC employees;
# 删除索引
DROP INDEX first_name_last_name ON employees;

# 查看无索引状态下的执行效率
SELECT * from employees WHERE first_name=‘Alyssa‘ AND last_name LIKE ‘%on‘;

查看此时的索引结构，以及有了索引

执行查询sql，看看有无索引的情况下的EXPLAIN语句情况

首先是无索引下的结果

再来是有索引的

这里解释下我标注出来的这三个参数，其实这里的数据量不是很大，看查询时间差距不大，所以查看rows的参数便可以参考下两个查询的区别，一个只需一行，另一个走了107行数据。所以说索引加快查询效率。之所以会有快速的效果，就是由于上面的B+树的数据结构在起作用。

就像十亿个数据，如果按照常规逻辑，可能最差的情况下，需要匹配十亿次才可以找到，加上这十亿个数据给内存带来了多少的负荷可想而知，所以要是转化为平衡树，可能只需要十层或者十几层之类的树结构，也就数据只需要花费很少的IO开销就可以找到了。这两个的差别就是天壤之别了。

type：表示MySQL在表中找到所需行的方式

? ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

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

ROWS： 表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

Extra：该列包含MySQL解决查询的详细信息

最后

借鉴1

借鉴2

谈谈MySQL的索引

标签：col   查询   lock   size   文件   tables   create   统计信息   操作