1、索引
索引是表的目录,在查找内容之前可以先在目录中查找索引位置,以此快速定位查询数据。对于索引,会保存在额外的文件中。
2、索引种类
- 普通索引:仅加速查询
- 唯一索引:加速查询 + 列值唯一(可以有null)
- 主键索引:加速查询 + 列值唯一 + 表中只有一个(不可以有null)组合索引:多列值组成一个索引,
- 专门用于组合搜索,其效率大于索引合并
- 全文索引:对文本的内容进行分词,进行搜索
索引合并,使用多个单列索引组合搜索,
覆盖索引,select的数据列只用从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖
3、相关命令
创建普通索引:create index 索引名 on 表名.列名;
创建唯一索引:create unique index 索引名 on表名.列名;
增加索引:ALTER TABLE table_name ADD INDEX index_name (column_list);
增加唯一索引 :ALTER TABLE table_name ADD UNIQUE (column_list);
增加主键索引 :ALTER TABLE table_name ADD PRIMARY KEY (column_list);
删除索引:DROP INDEX index_name ON talbe_name; ALTER TABLE table_name DROP INDEX index_name;
删除主键:ALTER TABLE table_name DROP PRIMARY KEY;
查看生成表的SQL语句:SHOW CREATE TABLE 表名;
查看索引:show index from 表名;
4、索引的效果
索引是专门用于加速搜索而生,所以加上索引之后,查询效率会快几十上百倍。
5 使用索引应该注意的问题:
命中索引才能提高索引的搜索效率
会失效的情况:
-
like
'%xx'
select
*
from
tb1
where
name
like
'%cn'
;
使用函数
select
*
from
tb1
where
reverse(
name
) =
'wupeiqi'
;
使用or的时候
-
or
select
*
from
tb1
where
nid = 1
or
email =
'seven@live.com'
;
特别的:当
or
条件中有未建立索引的列才失效,以下会走索引
select
*
from
tb1
where
nid = 1
or
name
=
'seven'
;
select
*
from
tb1
where
nid = 1
or
email =
'seven@live.com'
and
name
=
'alex'
如果组合索引为:(
name
,email)
name
and
email
-- 使用索引
name
-- 使用索引
email
-- 不使用索引
6 limit分页性能提升
limit时常要用到,它的工作流程是先在数据表里匹配到第一行然后取得后面的行数。如果数据量比较大 比如
SELECT * FROM USER LIMIT 2000000,1000;
就会要5秒左右的时间
如果换成
SELECT * FROM USER where id >2000000 LIMIT 1000;
就是毫秒级的了
7 执行计划
执行计划是指评估一条命令,得到相关数据,以便针对的优化SQL语句
命令:EXPLAIN SQL语句;
实例
id : 查询顺序标识
select_type 查询类型 SIMPLE 简单查询 PRIMARY 最外层查询 SUBQUERY 映射为子查询 DERIVED 子查询 UNION 联合 UNION RESULT 使用联合的结果
type 查询时的访问方式,性能:all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
ALL 全表扫描,对于数据表从头到尾找一遍 select * from tb1; 特别的:如果有limit限制,则找到之后就不在继续向下扫描 select * from tb1 where email = 'seven@live.com' select * from tb1 where email = 'seven@live.com' limit 1; 虽然上述两个语句都会进行全表扫描,第二句使用了limit,则找到一个后就不再继续扫描。 INDEX 全索引扫描,对索引从头到尾找一遍 select nid from tb1; RANGE 对索引列进行范围查找 select * from tb1 where name < 'alex'; PS: between and in > >= < <= 操作 注意:!= 和 > 符号 INDEX_MERGE 合并索引,使用多个单列索引搜索 select * from tb1 where name = 'alex' or nid in (11,22,33); REF 根据索引查找一个或多个值 select * from tb1 where name = 'seven'; EQ_REF 连接时使用primary key 或 unique类型 select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid; CONST 常量 表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。 select nid from tb1 where nid = 2 ; SYSTEM 系统 表仅有一行(=系统表)。这是const联接类型的一个特例。 select * from (select nid from tb1 where nid = 1) as A;
更详细的explain 字段说明
7 慢日志
查看当前配置信息:
show variables like '%query%'
修改当前配置:
set global 变量名 = 值
1 配置MySQL自动记录慢日志
slow_query_log = OFF 是否开启慢日志记录long_query_time = 2 时间限制,超过此时间,则记录
slow_query_log_file = /usr/slow.log 日志文件
log_queries_not_using_indexes = OFF 为使用索引的搜索是否记录
2 查看MySQL慢日志
--verbose 版本
--debug 调试
--help 帮助
-v 版本
-d 调试模式
-s ORDER 排序方式
what to sort by (al, at, ar, c, l, r, t), 'at' is default
al: average lock time
ar: average rows sent
at: average query time
c: count
l: lock time
r: rows sent
t: query time
-r 反转顺序,默认文件倒序拍。reverse the sort order (largest last instead of first)
-t NUM 显示前N条just show the top n queries
-a 不要将SQL中数字转换成N,字符串转换成S。don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names
-g PATTERN 正则匹配;grep: only consider stmts that include this string
-h HOSTNAME mysql机器名或者IP;hostname of db server for *-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l 总时间中不减去锁定时间;don't subtract lock time from total time