MySql——ONE
为什么要用数据库存放数据呢？用文件存放不也行吗？
因为用文件存放时，当要对文件中的内容进行操作时，需要频繁的打开文件，取出数据等。而用数据库只要用数据库语句对数据库进行此操作就可以了。看着清新简洁心情好
一 MySql的下载安装
1.下载
下载地址：http://dev.mysql.com/download/mysql,依据自己电脑的情况进行下载。
2.安装
下载到任意一个目录之后，解压缩。
a.打开命令提示符，进入解压缩文件的bin目录，输入mysqld,mysqld就是mysql的服务端，意思就是启动服务端，但是这个时候报错了，我们需要在解压缩之后的文件里创建一个data文件夹，这个时候再输入mysql就没有问题了。
b.启动mysql的客户端
另外打开一个命令提示符，进入bin目录输入mysql
c.初始化登录名和密码
在bin目录下输入mysql --initialize-insecure。这句命令表示初始化了一个用户，用户名为root，没有密码。
d.windows服务
打开任务管理器的服务页面，此时没有MySql，我们在bin目录下输入mysql -install。这是再看服务时就有了。
net start mysql——启动Mysql服务
net stop mysql——停止Mysql服务
e.添加环境变量
打开环境变量，将bin下的目录添加到环境变量中，再进入客户端时就不用进入bin目录了
二 客户端连接
a.C:\mysql-5.7.16-winx64\mysql-5.7.16-winx64\mysql -u root -p
b.
初试：
show databases; # 查看当前Mysql都有那些数据，根目录都有那些文件夹
create database 数据库名; # 创建文件夹
use 数据库名; # 使用选中数据库，进入目录
show tables; # 查看当前数据库下都有那些表，
create table 表名(nid int,name varchar(20), pwd varchar(64)); # 创建数据库表
select * from 表名; # 查看表中的所有数据
insert into 表名(nid,name,pwd) values(1,'alex','123'); # 插入数据
-- select * from 表名;
三 用户授权
a.用户管理特殊命令：
创建用户
create user '用户名'@'IP地址' identified by '密码';
删除用户
drop user '用户名'@'IP地址';
修改用户
rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';;
修改密码
set password for '用户名'@'IP地址' = Password('新密码')
b.权限：
默认，什么都没有
grant 权限 on 数据库.表 to '用户'@'IP地址' -- 授权
权限：select,查
目标：数据库.表/test.tb1/test.*/*.*
'用户'@'IP地址'
xiaohu@localhost
四 数据库操作
show databases;#查看数据库
drop database 数据库名；#删除数据库
create database 数据库名；#创建一个数据库
use 数据库名；#进入数据库
数据表操作
create table 表名（列名 类型，列名 类型）engine=innodb default charset=utf8;#创建表
drop table 表名;删除表
show tables;#查看表
desc 表名;#查看这个表中有哪些列
下面创建一个表示例：
1 mysql> create table tb7( 2 -> nid int not null auto_increment primary key, 3 -> name varchar(20), 4 -> age int default 19)engine=innodb default charset=utf8;
小作解释：
not null#不为空 auto_increment#自增 primary key#主键 engine=innodb#引擎设置为innodb default charset=utf8#默认按utf-8编码创建表
用户管理
1 create user 用户名@IP地址 identified by '密码'; #创建用户 2 drop user 用户名@IP地址; #删除 3 rename user set 用户名@IP地址 to 新用户名@IP地址; #修改用户 4 set password for 用户名@IP地址 =Password('新密码') ; #修改密码
权限管理
1 show grants for 用户名@IP地址; -- 查看权限 2 grant 权限 on 数据库.表 to 用户名@IP地址; -- 授权 3 revock 权限 on 数据库.表 from 用户名@IP地址; -- 取消权限
权限解释
all privileges 除grant外的所有权限 select 仅查权限 select,insert 查和插入权限 ... usage 无访问权限 alter 使用alter table alter routine 使用alter procedure和drop procedure create 使用create table create routine 使用create procedure create temporary tables 使用create temporary tables create user 使用create user、drop user、rename user和revoke all privileges create view 使用create view delete 使用delete drop 使用drop table execute 使用call和存储过程 file 使用select into outfile 和 load data infile grant option 使用grant 和 revoke index 使用index insert 使用insert lock tables 使用lock table process 使用show full processlist select 使用select show databases 使用show databases show view 使用show view update 使用update reload 使用flush shutdown 使用mysqladmin shutdown(关闭MySQL) super 使用change master、kill、logs、purge、master和set global。还允许mysqladmin调试登陆 replication client 服务器位置的访问 replication slave 由复制从属使用
针对数据库的权限细分
数据库名.* 数据库中的所有
数据库名.表 指定数据库中的某张表
数据库名.存储过程 指定数据库中的存储过程
*.* 所有数据库
针对用户和IP的权限
用户名@IP地址 用户只能在该IP下才能访问 用户名@192.168.1.% 用户只能在该IP段下才能访问(通配符%表示任意) 用户名@% 用户可以再任意IP下访问(默认IP地址为%)
数据表操作
创建表
1 create table 表名( 2 字段 类型 是否为空 自增 主键 3 字段 类型 是否可以为空 4 )engine=innodb default charset=utf8
是否为空
null：表示为空
not null：表示不为空
是否为空 null 表示可以为空 not null 表示不可以为空 默认值 default n 表示当某一列不设置值时，默认是n 自增 auto_increment 表示这一列没有设置值时，按照前一个值进行自增 主键 每一个表只能有一个主键，但是这个主键可以由两列组成，主键不能为空，不能重复 primary key 外键 用来和另外表做约束关系
删除表
drop table 表名；
清空表
delete table 表名; #删除后增加的数据自增列接着以前的继续自增 truncate table 表名; #自增列的值从1开始
修改表
添加列：alter table 表名 add 列名 类型 删除列：alter table 表名 drop column 列名 修改列： alter table 表名 modify column 列名 类型; -- 类型 alter table 表名 change 原列名 新列名 类型; -- 列名，类型 添加主键： alter table 表名 add primary key(列名); 删除主键： alter table 表名 drop primary key; alter table 表名 modify 列名 int, drop primary key; 添加外键：alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段); 删除外键：alter table 表名 drop foreign key 外键名称 修改默认值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000; 删除默认值：ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
查询表
show tables；
对表中数据进行操作
插入数据
1 insert into 表名(列名，列名...)values(值，值...) 2 insert into 表名(列名，列名...)values((值，值...),(值，值...)...) 3 insert into 表名(列名，列名...) select (列名,列名) from 表名
删除数据
delete from 表名 delete from 表名 where 条件
修改数据
update 表名 set 列名 = 新值 where 条件
查询数据
selete * from 表名 selete * from 表名 where 条件 selete nid name age from 表名 where 条件
查询数据时可以设置的条件
条件 and #并且 between 条件1 and 条件2 #两个条件在之间 in () #属于()里边 not in () #不属于()里边 selete gender from (selete * from 表名) 通配符 % #表示任意多个字符 - #表示一个任意字符 分页 limit n,m #表示从第n条数据去m条（数据从第0条开始算） limit m offset n #同上 分组 group by 列名 #根据列名分组 连表 无对应关系则不显示 select A.num, A.name, B.name from A,B Where A.nid = B.nid left join 表名 on 条件 #主表在左边，如果从表行数不够，用NULl替补 right join 表名 on 条件 #同上，只不过主表在右边 inner join 表名 on 条件 #和left join 一样，只不过在基础上对有NULL的行进行过滤
基本数据类型
数值
int bigint tinyint等 #只是范围不同
字符
char #定长，优缺点：浪费空间，操作速度快
varchar #边长，优缺点：节省空间，但是操作速度慢
字节类型
bit
时间类型
DATE
YYYY-MM-DD（1000-01-01/9999-12-31）
TIME
HH:MM:SS（'-838:59:59'/'838:59:59'）
YEAR
YYYY（1901/2155）
DATETIME
YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59 Y）
TIMESTAMP
YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）
enum
枚举类型，
An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)
示例：
CREATE TABLE shirts (
name VARCHAR(40),
size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');
set
集合类型
A SET column can have a maximum of 64 distinct members.
示例：
CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
enum 和set的区别
在插入数据时，enum只能是其中的一个，而set可以是定义的数据的任意个。
视图
视图是通过SQL语句获取的动态结果集并给其临时命名，可以把其当做一个临时表，但是因为其根本不存在，所以只能做查询操作。
创建视图
1 CREATE VIEW v1 AS SELECT nid, 2 NAME, 3 part_nid 4 FROM 5 user_info
存储过程
存储过程是一个SQL语句集合，当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行。
创建并执行无参数存储过程
1 delimiter // 2 create procedure p1() 3 BEGIN 4 SELECT nid,name from user_info where nid>10; 5 end// 6 delimiter ; 7 call p1()
删除存储过程
1 drop procedure proc_name;
创建并执行有参数存储过程
参数有三种，如下：
in #用于传入参数
out #用户返回值
inout #可以传入参数，也可以接收返回值
1 delimiter // 2 CREATE PROCEDURE p11 ( 3 IN i1 INT, 4 IN i2 INT, 5 INOUT i3 INT, 6 OUT r1 INT 7 ) 8 BEGIN 9 DECLARE 10 temp1 INT ; DECLARE 11 temp2 INT DEFAULT 0 ; 12 SET temp1 = 1 ; 13 SET r1 = i1 + i2 + temp1 + temp2 ; 14 SET i3 = i3 + 100 ; 15 END// 16 17 delimiter ; 18 19 20 21 SET @t1 = 4; 22 23 24 SET @t2 = 0 ; 25 CALL p11 (1, 2 ,@t1 ,@t2); 26 SELECT @t1,@t2;
结合python使用存储过程
创建存储过程
1 delimiter \\ 2 CREATE PROCEDURE p11 ( 3 IN i1 INT, 4 IN i2 INT, 5 INOUT i3 INT, 6 OUT r1 INT 7 ) 8 BEGIN 9 DECLARE 10 temp1 INT ; DECLARE 11 temp2 INT DEFAULT 0 ; 12 SET temp1 = 1 ; 13 SET r1 = i1 + i2 + temp1 + temp2 ; 14 SET i3 = i3 + 100 ; SELECT 15 nid, 16 NAME 17 FROM 18 user_info 19 WHERE 20 nid > 5 ; 21 END\\ 22 delimiter ;
pthon进行调用
1 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='0123', db='test1', charset='utf8') 2 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) 3 r1 = cursor.callproc('p11', args=(1, 2, 3, 4)) 4 print(r1) 5 result = cursor.fetchall() 6 print(result) 7 result2 = cursor.execute('select @_p11_0,@_p11_1,@_p11_2,@_p11_3') 8 result2 = cursor.fetchall() 9 print(result2)
执行结果
1 (1, 2, 3, 4) 2 [{'nid': 6, 'NAME': 'ty'}, {'nid': 7, 'NAME': 'xiaohei'}, {'nid': 8, 'NAME': 'xiaohei'}, {'nid': 9, 'NAME': 'xiaohei'}, {'nid': 10, 'NAME': 'xiaohei'}, {'nid': 11, 'NAME': 'xiaohei'}] 3 [{'@_p11_0': 1, '@_p11_1': 2, '@_p11_2': 103, '@_p11_3': 4}]
条件语句
if条件语句
1 delimiter \\ 2 CREATE PROCEDURE proc_if () 3 BEGIN 4 5 declare i int default 0; 6 if i = 1 THEN 7 SELECT 1; 8 ELSEIF i = 2 THEN 9 SELECT 2; 10 ELSE 11 SELECT 7; 12 END IF; 13 14 END\\ 15 delimiter ;
循环语句
1 delimiter \\ 2 CREATE PROCEDURE proc_while () 3 BEGIN 4 5 DECLARE num INT ; 6 SET num = 0 ; 7 WHILE num < 10 DO 8 SELECT 9 num ; 10 SET num = num + 1 ; 11 END WHILE ; 12 13 END\\ 14 delimiter ;
动态执行SQL语句
1 delimiter \\ 2 DROP PROCEDURE IF EXISTS proc_sql \\ 3 CREATE PROCEDURE proc_sql () 4 BEGIN 5 declare p1 int; 6 set p1 = 11; 7 set @p1 = p1; 8 9 PREPARE prod FROM 'select * from tb2 where nid > ?'; 10 EXECUTE prod USING @p1; 11 DEALLOCATE prepare prod; 12 13 END\\ 14 delimiter ;
触发器
顾名思义，在执行某个操作前或后触发某个操作
创建触发器
1 delimiter // 2 create trigger trigger_name before/after insert/delete/update on 表名 for EACH ROW 3 BEGIN 4 ... 5 END 6 delimiter //
删除触发器
1 drop trigger 触发器名称;
注意：插入或修改的新数据封装在NEW中，通过new.列名就可以拿到。删除时的旧数据封装在old中，通过old.列名就可以拿到。
如果碰触触发器的主体句中是操作整张表，则会将这张表的每条数据都执行触发器的主题句
事务
事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。
1 delimiter // 2 CREATE PROCEDURE p2 (OUT p_reture_code TINYINT) 3 BEGIN 4 DECLARE 5 EXIT HANDLER FOR SQLEXCEPTION 6 BEGIN 7 -- ERROR 8 SET p_reture_code = 1 ; ROLLBACK ; 9 END ; DECLARE 10 EXIT HANDLER FOR SQLWARNING 11 BEGIN 12 -- WARNING 13 SET p_return_code = 2 ; ROLLBACK ; 14 END ; START TRANSACTION ; DELETE 15 FROM 16 u ; INSERT INTO user_info (nid, uname, part_nid) 17 VALUES 18 (90, 'seven', 1) ; COMMIT ; -- success 19 SET p_return_code = 0 ; 20 END// 21 delimiter ;
mysql内置函数
1 CHAR_LENGTH(str) 2 返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。 3 对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。 4 5 CONCAT(str1,str2,...) 6 字符串拼接 7 如有任何一个参数为NULL ，则返回值为 NULL。 8 CONCAT_WS(separator,str1,str2,...) 9 字符串拼接（自定义连接符） 10 CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。 11 12 CONV(N,from_base,to_base) 13 进制转换 14 例如： 15 SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示 16 17 FORMAT(X,D) 18 将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点，或不含小数部分。 19 例如： 20 SELECT FORMAT(12332.1,4); 结果为： '12,332.1000' 21 INSERT(str,pos,len,newstr) 22 在str的指定位置插入字符串 23 pos：要替换位置其实位置 24 len：替换的长度 25 newstr：新字符串 26 特别的： 27 如果pos超过原字符串长度，则返回原字符串 28 如果len超过原字符串长度，则由新字符串完全替换 29 INSTR(str,substr) 30 返回字符串 str 中子字符串的第一个出现位置。 31 32 LEFT(str,len) 33 返回字符串str 从开始的len位置的子序列字符。 34 35 LOWER(str) 36 变小写 37 38 UPPER(str) 39 变大写 40 41 LTRIM(str) 42 返回字符串 str ，其引导空格字符被删除。 43 RTRIM(str) 44 返回字符串 str ，结尾空格字符被删去。 45 SUBSTRING(str,pos,len) 46 获取字符串子序列 47 48 LOCATE(substr,str,pos) 49 获取子序列索引位置 50 51 REPEAT(str,count) 52 返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。 53 若 count <= 0,则返回一个空字符串。 54 若str 或 count 为 NULL，则返回 NULL 。 55 REPLACE(str,from_str,to_str) 56 返回字符串str 以及所有被字符串to_str替代的字符串from_str 。 57 REVERSE(str) 58 返回字符串 str ，顺序和字符顺序相反。 59 RIGHT(str,len) 60 从字符串str 开始，返回从后边开始len个字符组成的子序列 61 62 SPACE(N) 63 返回一个由N空格组成的字符串。 64 65 SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len) 66 不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。 67 68 mysql> SELECT SUBSTRING('Quadratically',5); 69 -> 'ratically' 70 71 mysql> SELECT SUBSTRING('foobarbar' FROM 4); 72 -> 'barbar' 73 74 mysql> SELECT SUBSTRING('Quadratically',5,6); 75 -> 'ratica' 76 77 mysql> SELECT SUBSTRING('Sakila', -3); 78 -> 'ila' 79 80 mysql> SELECT SUBSTRING('Sakila', -5, 3); 81 -> 'aki' 82 83 mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2); 84 -> 'ki' 85 86 TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str) 87 返回字符串 str ， 其中所有remstr 前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的,则假设为BOTH 。 remstr 为可选项，在未指定情况下，可删除空格。 88 89 mysql> SELECT TRIM(' bar '); 90 -> 'bar' 91 92 mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx'); 93 -> 'barxxx' 94 95 mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx'); 96 -> 'bar' 97 98 mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz'); 99 -> 'barx'
自定义函数
1 delimiter // 2 CREATE FUNCTION f1 ( 3 i1 INT, i2 INT) 4 RETURNS INT 5 BEGIN 6 DECLARE 7 num INT; 8 9 10 SET sum = i1 + i2; 11 12 RETURN (num); 13 end // 14 delimiter ;
索引
索引，是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，然后直接获取即可。
索引的作用
1.约束（主键索引，唯一索引）
2.加速查找（普通索引，主键索引，组合索引，合并索引，覆盖索引）
1.普通索引
创建索引
1 create table in1( 2 nid int not null auto_increment primary key, 3 name varchar(32) not null, 4 email varchar(64) not null, 5 extra text, 6 index ix_name (name) 7 )
创建索引
1 create index 索引名 on 表名（列名） 2 create index 索引名 on 表名（列名，列名）
删除索引
drop index 索引名 on 表名
查看索引
1 show index from 表名
注意：对于创建索引时如果是BLOB 和 TEXT 类型，必须指定length。
create index ix_extra on in1(extra(32));
2.唯一索引
唯一索引有两个功能：加速查询 和 唯一约束（可含null）
1 create table in1( 2 nid int not null auto_increment primary key, 3 name varchar(32) not null, 4 email varchar(64) not null, 5 extra text, 6 unique ix_name (name) 7 )
创建唯一索引
create unique index on 表名（列名）
删除唯一索引
drop unique index on 表名
3.主键索引
主键有两个功能：加速查询 和 唯一约束（不可含null）
创建表+主键索引
1 create table in1( 2 nid int not null auto_increment primary key, 3 name varchar(32) not null, 4 email varchar(64) not null, 5 extra text, 6 index ix_name (name) 7 ) 8 9 OR 10 11 create table in1( 12 nid int not null auto_increment, 13 name varchar(32) not null, 14 email varchar(64) not null, 15 extra text, 16 primary key(ni1), 17 index ix_name (name) 18 )
创建主键索引
alter table 表名 add primary key(列名);
删除主键索引
alter table 表名 drop primary key;
alter table 表名 modify 列名 int, drop primary key;
4.组合索引
将多列组合在一起创建一个索引
创建组合索引
create index 索引名 on 表名（列名，列名）
联合唯一索引
在联合唯一索引中，两列数据同时不相同时才能插入，否则报错。
注意：组合索引中有最左匹配
比如：
create index ix_name_email on in3(name,email)
- name and email -- 使用索引
- name -- 使用索引
- email -- 不使用索引
注意：对于同时搜索n个条件时，组合索引的性能好于多个单一索引合并。
5.短索引
创建段索引
create index 索引名 on表名（列名（长度））
6.合并索引
将多个列的单独索引一起来搜索
7.覆盖索引
在索引数据中就能查找到的数据，不用去数据表中查找。
命中索引原则
1 - like '%xx' 2 select * from tb1 where name like '%cn'; 3 - 使用函数 4 select * from tb1 where reverse(name) = 'wupeiqi'; 5 - or 6 select * from tb1 where nid = 1 or email = 'seven@live.com'; 7 特别的：当or条件中有未建立索引的列才失效，以下会走索引 8 select * from tb1 where nid = 1 or name = 'seven'; 9 select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex' 10 - 类型不一致 11 如果列是字符串类型，传入条件是必须用引号引起来，不然... 12 select * from tb1 where name = 999; 13 - != 14 select * from tb1 where name != 'alex' 15 特别的：如果是主键，则还是会走索引 16 select * from tb1 where nid != 123 17 - > 18 select * from tb1 where name > 'alex' 19 特别的：如果是主键或索引是整数类型，则还是会走索引 20 select * from tb1 where nid > 123 21 select * from tb1 where num > 123 22 - order by 23 select email from tb1 order by name desc; 24 当根据索引排序时候，选择的映射如果不是索引，则不走索引 25 特别的：如果对主键排序，则还是走索引： 26 select * from tb1 order by nid desc; 27 28 - 组合索引最左前缀 29 如果组合索引为：(name,email) 30 name and email -- 使用索引 31 name -- 使用索引 32 email -- 不使用索引
其他注意事项
1 - 避免使用select * 2 - count(1)或count(列) 代替 count(*) 3 - 创建表时尽量时 char 代替 varchar 4 - 表的字段顺序固定长度的字段优先 5 - 组合索引代替多个单列索引（经常使用多个条件查询时） 6 - 尽量使用短索引 7 - 使用连接（JOIN）来代替子查询(Sub-Queries) 8 - 连表时注意条件类型需一致 9 - 索引散列值（重复多）不适合建索引，例：性别不适合
limit分页
分页思路：每页显示10条
定义两个全局变量，分别把当前页的第一条和最后一条的自增id（nid）传给这两个全局变量。
取下一页时，将second全局变量传给sql语句
select * from user_info where nid >%s limit 10,lastp;
取上一页时，将first全局变量传给sql语句，
select * from user_info where nid<%s order by nid desc limit 10,firstp;
执行计划
1 explain + 查询SQL - 用于显示SQL执行信息参数，根据参考信息可以进行SQL优化 2 3 4 mysql> explain select * from tb2; 5 +----+-------------+-------+------+---------------+------+---------+------+------+-------+ 6 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 7 +----+-------------+-------+------+---------------+------+---------+------+------+-------+ 8 | 1 | SIMPLE | tb2 | ALL | NULL | NULL | NULL | NULL | 2 | NULL | 9 +----+-------------+-------+------+---------------+------+---------+------+------+-------+ 10 1 row in set (0.00 sec)
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; possible_keys 可能使用的索引 key 真实使用的 key_len MySQL中使用索引字节长度 rows mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值 extra 该列包含MySQL解决查询的详细信息 “Using index” 此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。 “Using where” 这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。 “Using temporary” 这意味着mysql在对查询结果排序时会使用一个临时表。 “Using filesort” 这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。 “Range checked for each record(index map: N)” 这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的。
慢日志查询
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慢日志
mysqldumpslow -s at -a /usr/local/var/mysql/MacBook-Pro-3-slow.log
部分摘自http://www.cnblogs.com/wupeiqi/articles/5716963.html
