数据库索引好比一本书前面的目录,能加快数据库的查询速度。
创建索引
普通索引
这是最基本的索引,它没有任何限制,MyIASM中默认的BTREE类型的索引,也是我们大多数情况下用到的索引。
创建方式:
- 直接创建索引
CREATE INDEX index_name ON table(column(length))
- 修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name ON (column(length))
- 创建表的时候同时创建索引
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
INDEX index_name (title(length))
)
- 删除索引
DROP INDEX index_name ON table
2、唯一索引
与普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须是唯一的,创建方法和普通索引类似。
- 创建唯一索引
CREATE UNIQUE INDEX index_name ON table(column(length))
- 修改表结构
ALTER TABLE table_name ADD UNIQUE INDEX index_name ON (column(length))
- 创建表时同时创建索引
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
UNIQUE indexName (title(length))
);
3、组合索引
平时用的SQL查询
语句一般都有比较多的限制条件,所以为了进一步榨取MySQL的效率,就要考虑建立组合索引。例如上表中针对title和time建立一个组合索引:
ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))
建立这样的组合索引,其实是相当于分别建立了下面两组组合索引:
–title,time
–title
为什么没有time这样的组合索引呢?这是因为MySQL组合索引“最左前缀”的结果。
简单的理解就是只从最左面的开始组合。并不是只要包含这两列的查询都会用到该组合索引,如下面的几个SQL所示:
- 使用到上面的索引
SELECT * FROM article WHREE title='测试' AND time=1234567890;
SELECT * FROM article WHREE utitle='测试';
- 未使用到上面的索引
SELECT * FROM article WHREE time=1234567890;
总结:
常用的索引类型
1、普通索引
2、唯一索引
3、组合索引
普通索引和唯一索引的创建方式有三种,分别是直接创建、修改表结构创建、创建表时同时创建,注意组合索引的组合规则是最左前缀索引
索引分类
1.普通索引:不附加任何限制条件,可创建在任何数据类型中
2.唯一性索引:使用unique参数可以设置索引为唯一性索引,在创建索引时,限制该索引的值必须唯一,主键就是一种唯一性索引
3.全文索引:使用fulltext参数可以设置索引为全文索引。全文索引只能创建在char、varchar或text类型的字段上。查询数据量较大的字符串类型字段时,效果明显。但只有MyISAM存储引擎支持全文检索
4.单列索引:在表中单个字段上创建的索引,单列索引可以是任何类型,只要保证索引只对应一个一个字段
5.多列索引:在表中多个字段上创建的索引,该索引指向创建时对应的多个字段
6.空间索引:使用spatial参数可以设置索引为空间索引,空间索引只能建立在空间数据类型上比如geometry,并且不能为空,目前只有MyISAM存储引擎支持
执行计划
explain 执行计划
Id: MySQL QueryOptimizer 选定的执行计划中查询的序列号。表示查询中执行select 子句或操作表的顺序,id 值越大优先级越高,越先被执行。id 相同,执行顺序由上至下。
Select_type: 一共有9中类型,只介绍常用的4种:
SIMPLE: 简单的 select 查询,不使用 union 及子查询
PRIMARY: 最外层的 select 查询
UNION: UNION 中的第二个或随后的 select 查询,不 依赖于外部查询的结果集
DERIVED: 用于 from 子句里有子查询的情况。 MySQL 会 递归执行这些子查询, 把结果放在临时表里。
Table: 输出行所引用的表
Type: 从优到差的顺序如下:(红色标识的是常见的级别。)
system-->const-->eq_ref-->ref-->ref_or_null-->index_merge-->unique_subquery-->index_subquery-->range-->index-->all.
各自的含义如下:
system: 表仅有一行。这是 const 连接类型的一个特例。
const: const 用于用常数值比较 PRIMARY KEY 时。
eq_ref: 查询使用了索引为主键或唯一键的全部时使用。即:通过索引关键字可能查找到一个符合条件的行。
ref: 通过索引关键字可能查找到多个符合条件的行。
ref_or_null: 如同 ref, 但是 MySQL 必须在初次查找的结果里找出 null 条目,然后进行二次查找。
index_merge: 说明索引合并优化被使用了。
unique_subquery: 在某些 IN 查询中使用此种类型,而不是常规的 ref:valueIN (SELECT primary_key FROM single_table WHERE some_expr)
index_subquery: 在 某 些 IN 查 询 中 使 用 此 种 类 型 , 与unique_subquery 类似,但是查询的是非唯一 性索引
range: 检索给定范围的行。当使用 <>、>、>=、<、<=、BETWEEN 或者 IN 操作符时,会使用到range。
index: 全表扫描,只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。
all: 最坏的情况,从头到尾全表扫描。
possible_keys : 哪些索引可能有助于查询。如果为空,说明没有可用的索引。
key: 实际从 possible_key 选择使用的索引,如果为 NULL,则没有使用索引。很少的情况 下,MYSQL 会选择优化不足的索引。这种情 况下,可以在 SELECT语句中使用 USE INDEX (indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制 MYSQL 忽略索引
key_len: 使用的索引的长度。在不损失精确性的情况 下,长度越短越好。
ref: 显示索引的哪一列被使用了
rows: 请求数据返回的大概行数
extra: 其他信息,出现Using filesort、Using temporary 意味着不能使用索引,效率会受到重大影响。应尽可能对此进行优化。
Using filesort: 没有办法利用现有索引进行排序,需要额外排序,建议:根据排序需要,创建相应合适的索引
Using temporary: 需要用临时表存储结果集,通常是因为group by的列列上没有索引。也有可能是因为同
时有group by和order by,但group by和order by的列又不一样Using index : 利用覆盖索引,无需回表即可取得结果数据(即数据直接从索引文件中读取),这种结果是好的。
其中重要的几个就是 key、type 、rows、extra,其中key为null、all 、index时,需要调整、优化索引。一般需要达到 ref、eq_ref 级别,范围查找需要达到 range,extra有Using filesort、Using temporary 的一定需要优化,根据rows可以直观看出优化结果。