mysql相关知识整理

mysql相关知识整理

安装，以centos7 mysql5.6.40为例 rpm安装方式
访问http://luopengtest.gotoip2.com/rpm/MySQL-5.6.40-1.el7.x86_64.rpm 下载rpm包
使用rpm -ivh MySQL-5.6.40-1.el7.x86_64.rpm 安装

创建数据库
create database testdb1 charset utf8;

删除数据库
drop database testdb1;

使用，创建用户
create user 'testu1'@'192.168.8.8' identified by 'test123';
创建一个testu1用户,密码设置test123,授权192.168.8.8 IP可链接

create user 'testu1'@'192.168.8.' identified by 'test123';
创建一个testu1用户,密码设置test123,授权192.168.8. IP段可链接

create user 'testu1'@'%' identified by 'test123';
创建一个testu1用户,密码设置test123,授权任意IP可链接

删除用户
drop user 'testu1'@'%';

修改用户
rename user 'testu1'@'192.168.8.8' to 'newtestu1'@'192.168.6.6';

修改密码
set password for 'testu1'@'192.168.8.8'=Password('newpassword');

查看权限
show grants for 'testu1'@'192.168.8.8';

授权用户testu1对数据库testdb1的表t1的查询权限
grant select on testdb1.t1 to 'testu1'@'192.168.8.8';

授权用户testu1对数据库testdb1的任意表的查询、插入、更新权限
grant select,insert,update on testdb1.* to 'testu1'@'192.168.8.8';

授权用户testu1对数据库testdb1的任意表的所有权限,除grant命令,此命令仅root才有
grant all privileges on testdb1.* to 'testu1'@'192.168.8.8';

授权用户testu1对所有数据库的任意表的所有权限,除grant命令,此命令仅root才有
grant all privileges on *.* to 'testu1'@'192.168.8.8';

取消用户testu1对数据库testdb1的任意表的所有权限
revoke all on testdb1.* from 'testu1'@'192.168.8.8';

取消用户testu1对所有数据库的任意表的所有权限
revoke all on *.* from 'testu1'@'192.168.8.8';

flush privileges; 刷新权限
show databases; 查看所有数据库
use testdb1; 使用testdb1;
show tables; 查看当前数据库的所有表

创建表
create table t1(id int,name char(16)) default charset='utf-8';
语法
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名1 类型[(宽度) 约束条件],
...
);

删除表
drop table t1;

插入数据
insert into t1(id,name) values(1,'ropon'),(2,'pengge');
insert into t1 values(3,'luopeng');

删除数据
delete from t1 where id=1;

修改数据
update testdb1.t1 set name='ropon1' where id =2;

查询所有字段(慎用)
select * from t1;

查询具体字段
select id,name from t1;

加条件查询
select * from testdb1.t1 where id = 2;

查看表结构
desc t1;

查看表的详细结构
show create table t1\G;

查看数据库支持引擎
show engines\G;

查看正在使用引擎
show variables like 'storage_engine%';

指定存储引擎创建表
innodb,myisam,memory,blackhole
create table t2(id int,name char(10))engine=memory;

memory 内存表,重启mysql服务或重启服务器,表内容将清空
blackhole 可往表中插入任意数据,相当于丢入黑洞，表内不做记录

复制表结构和数据
create table tt1 select * from testdb1.t1;

复制表结构不要内容
create table tt2 select * from testdb1.t1 where 1>2;
其中1>2 相当于false,所以查询不到内容,故仅复制表结构

create table tt3 like testdb1.t1;

数据类型
数字
整型 tinyint smallint mediumint int bigint
tinyint[(m)] [unsigned] [zerofill] 小整型
unsigned 创建定义无符号
zerofill
插入数据时,当该字段的值的长度小于定义的长度时,会在该值的前面补上相应的0
zerofill默认为int(10)
当使用zerofill时,默认会自动加unsigned(无符号)属性
使用unsigned属性后,数值范围是原值的2倍
有符号 -128 ~ 127 无符号 0 ~ 255
注意 mysql中无布尔值,可以使用tinyint(1)构造

int 整型
有符号 -2147483648 ~ 2147483647 无符号 0 ~ 4294967295
bigint 大整型
有符号 -9223372036854775808 ~ 9223372036854775807 无符号 0 ~ 18446744073709551615

小数
float
double

推荐用decimal 精准 内部使用字符串形式存储
decimal[(m[,d])] [unsigned] [zerofill]
m 是整数部分总个数(-符号不计算) 最大值65 d 是小数点后个数 最大值30

字符串
char(10) 10个字符,浪费空间,比如ropon,会存成ropon00000,存取速度快
varchar 精准,节省空间,存取速度慢
优化建议 创建表定长先定义,变长后定义 大于255字符 存文件中 数据库存路径或URL地址
create table t8(x char(5),y varchar(4));
insert into t8 values('测试一','测试二');
length() 查看字节数
char_length() 查看字符数
select x,char_length(x),y,char_length(y) from t8;
在检索或者说查询时,查出的结果会自动删除尾部的空格,除非打开pad_char_to_full_length
SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
select x,length(x),y,length(y) from t8;
一个中文字符3个字节

tinytext text mediumtext longtext

时间类型
year
date
time
datetime
timestamp
create table t9(d date,t time,dt datetime);
insert into t9 values(now(),now(),now());

datatime的日期范围1001-9999 timestamp的日期范围1970-2038
datetime存储时间与是时区无关 timestamp存储时间与时区有关,显示值也依赖时区
datetime使用8字节存储空间 timestamp使用4字节存储空间 相比timestamp空间利用率更高
datetime默认值为空 timestamp默认值不为空,默认为当前时间

插入时间需要以字符串形式,并且按照对应格式插入
插入年份时,尽量使用4位值
插入2位年份时,小于等于69,以20开头比如55 2055 大于等于70,以19开头比如79 1979

create table students(
id int,
name varchar(20),
born_year year,
born date,
class_time time,
reg_time datetime
);

insert into students values
(2,'ropon',"1999","1999-01-01","12:12:12","2018-01-01 12:02:02"),
(3,'pengge',"1998","1998-02-02","13:13:13","2018-02-01 13:02:02");

枚举类型和集合类型
enum和set
应用场景 字段的值只能在给定范围中选择 比如单选框 enum 多选框 set
create table user(
id int,
name varchar(50),
sex enum('男','女','其它'),
level enum('注册会员','铜牌会员','银牌会员','黄金会员'),
fav set('玩游戏','听歌','看电影','逛街','吃')
);
insert into user values
(1,'Ropon','男','黄金会员','听歌'),
(1,'鹏哥','男','铜牌会员','看电影');

约束
primary key (pk) 标识该字段为该表的主键 可做唯一标识记录
foreign key (fk) 标识该字段为该表的外键
not null 标识该字段不能为空
unique key (uk) 标识该字段的值是唯一的
auto_increment 标识该字段的值自动增长(整数类型 且为主键)
default 为该字段设置默认值
unsigned 无符号
zerofill 使用0填充

sex enum('男','女') not null default '男'
age int unsigned not null default 20

主键 primary key
外键 foreign key
索引 index,unique

create table t9(id int,nid int not null);
insert into t9 values('',1);

create table students2(
id int not null,
name varchar(20) not null,
age int(3) unsigned not null default 18,
sex enum('男','女') default '男',
fav set('玩游戏','听歌','看电影','逛街','吃') default '听歌'
);
insert into students2(id,name) values(1,'Ropon');

unique 单列唯一
create table department(
id int,
name char(10) unique
);

create table department(
id int unique,
name char(10) unique
);

create table department(
id int,
name char(10),
unique(name)
);

create table services(
id int,
ip char(15),
port int,
unique(id),
unique(ip,port)
);

primary key
一个表中单列可作主键,多列也可以作主键
约束等价于not null nuique 字段唯一不为空
innodb存储引擎,一张表必须有一个主键

单列主键
create table user2(
id int primary key,
name char(20) not null
);

复合(多列)主键
create table services2(
ip char(15),
port int,
primary key(ip,port)
);

auto_increment
约束的字段为自动增长
create table user3(
id int primary key auto_increment,
name char(20) not null
);

清空表
truncate user3; 数据量大,删除效率高,有自增id删除后可从0开始
delete from user3; 有自增id删除后可从原来结束值开始

show variables like 'auto_inc%';
auto_increment_increment 步长默认为1
auto_increment_offset 起始偏移量默认为1

foreign key
外键
关联 约束fk_dep dep_id外键参考dep的id
constraint fk_dep foreign key(dep_id) references dep(id)
同步删除
on delete cascad
同步更新
on update cascade

创建表时先创建被关联表,再创建关联表
create table dep(
id int primary key,
name varchar(20) not null,
descripe varchar(30) not null
);

create table emp(
id int primary key,
name varchar(20) not null,
dep_id int,
foreign key(dep_id) references dep(id)
on delete cascade
on update cascade
);

insert into dep values
(1,'技术部','技术部'),
(2,'运维部','运维部'),
(3,'销售部','销售部');

insert into emp values
(1,'Ropon',1),
(2,'Pengge',3),
(3,'Luopeng',2),
(4,'Liss',1);

加foreign key约束,2张表形成三种关系
多对一(一对多)
多对多
一对一

1.以emp表看
是否emp表的多条记录对应dep表的一条记录
如果是说明emp表的一个字段foreign key(外键)dep表的一个字段(通常是id)

2.以dep表看
是否dep表的多条记录对应dep表的一条记录
如果是说明dep表的一个字段foreign key(外键)emp表的一个字段(通常是id)

如果1成立 则emp表多对一dep表
如果2成立 则dep表多对一emp表

如果1和2同时成立 则emp表和dep表双向多对一,即多对多
且需要定义一个表存emp表和dep表的关系

如果1和2都不成立 而且emp表的一条记录唯一对应dep表的一条记录 反之亦然
通常是emp表一个字段foreign key(外键)emp表的一个字段且将emp表外键字段设置成unique

多对一(一对多)
例子:书和出版社
create table press(
id int primary key auto_increment,
name varchar(20) not null
);

create table book(
id int primary key auto_increment,
name varchar(20) not null,
press_id int not null,
constraint fk_book_press foreign key(press_id) references press(id)
on delete cascade
on update cascade
);

insert into press(name) values
('出版社1'),('出版社2');

insert into book(name,press_id) values
('书籍1',1),('书籍2',1),('书籍3',2);

多对多
例子:书和作者
create table author(
id int primary key auto_increment,
name varchar(20) not null
);

create table author2book(
id int not null unique auto_increment,
author_id int not null,
book_id int not null,
constraint fk_author foreign key(author_id) references author(id)
on delete cascade
on update cascade,
constraint fk_book foreign key(book_id) references book(id)
on delete cascade
on update cascade,
primary key(author_id,book_id)
);

insert into author(name) values('ropon'),('pengge');

insert into author2book(author_id,book_id) values
(1,1),(1,2),(1,3),(2,1),(2,2);

一对一
例子:用户和博客
create table user(
id int primary key auto_increment,
name varchar(20)
);

create table blog(
id int primary key auto_increment,
url varchar(100) not null,
user_id int unique,
constraint fk_user foreign key(user_id) references user(id)
on delete cascade
on update cascade
);

insert into user(name) values('ropon'),('pengge'),('luopeng');

insert into blog(url,user_id) values
('ropon.top/ropon',1),
('ropon.top/pengge',2),
('ropon.top/luopeng',3);

增删改查

增
插入完整数据(顺序插入)
insert into 表名(字段1,字段2,字段3...) values(值1,值2,值3...);
insert into 表名 values(值1,值2,值3...);

指定字段插入数据
insert into 表名(字段1,字段2) values(值1,值2);

插入多条数据
insert into 表名 values
(值1,值2,值3...),
(值1,值2,值3...),
(值1,值2,值3...);

插入查询其他表的结果
insert into 表名(字段1,字段2,字段3...)
select 字段1,字段2,字段3... from 表2
where ...;

删
删除表所有数据
delete from 表名;

删除指定记录
delete from 表名 where id=xx;

改
更新数据
update 表名 set 字段1=值1,
字段2=值2,
字段3=值3...
where ...;

查
表单查询
select 字段1,字段2,字段3... from 表名
where 条件
group by filed
having 筛选
order by field
limit 限制条数

关键字优先级
from 从哪个表,找表
where 约束条件,取记录
group by 取出的记录进行分组
having 分组的结果进行过滤
select 执行select
distinct 去重
order by 将结果按条件排序
limit 限制显示条数

create table employee(
id int primary key auto_increment,
name varchar(20) not null,
sex enum('男','女') not null default '男',
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int,
depart_id int
);

insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
('ropon','男','20','2017-01-03','it','1254025.25',11,1),
('ropon1','男','20','2017-02-03','it','254025.25',11,1),
('ropon2','男','21','2018-06-03','it','124025.24',11,2),
('ropon3','男','23','2018-02-03','it1','125025.23',11,3),
('ropon4','男','25','2018-01-03','it3','125425.22',11,3),
('ropon5','男','26','2018-03-03','it5','12525.21',11,3);

where 约束条件
比较运算符 > < >= <= <> !=
值在a和b之间 between a and b
值是a或b或c in(a,b,c)
模糊匹配 like 'xxxx'
_表示一个字符 比如like 'ropo_' ropon匹配成功 ropon1不匹配
%表示任意多字符 比如like 'ropo%' ropon匹配成功 ropon1也能匹配成功
逻辑运算符 and or not

group by 分组查询
分组是基于where之后得到的记录而进行的
分组是
将所有记录按照某个相同字段进行归类,比如针对员工信息表的职位分组或者按照性别进行分组等
为何要分组
取每个部门的最高工资
取每个部门的员工数
取男人数和女人数

'每'后面的字段,通常是分组的依据

可以按照任意字段分组,但是分组完毕后,比如group by post,只能查看post字段,如果想查看组内信息,需要借助于聚合函数

由于没有设置sql_mode为ONLY_FULL_GROUP_BY,默认都是组内的第一条记录,但其实这是没有意义的
如果想分组,则必须要设置全局的sql的模式为ONLY_FULL_GROUP_BY
set global sql_mode='ONLY_FULL_GROUP_BY';

聚合函数
max() 求最大值
min() 求最小值
avg() 求平均值
sum() 求和
count() 求总个数

查看每个部门有多少员工
select post,count(id) from employee group by post;

查看每个部门的最高薪水
select post,max(salary) from employee group by post;

查看每个部门的最低薪水
select post,min(salary) from employee group by post;

查看每个部门的平均薪水
select post,avg(salary) from employee group by post;

查看每个部门的所有薪水
select post,sum(salary) from employee group by post;

having 过滤
优先级 where > group by > having
where 发生在group by之前,因而where中可以有任意字段,但不能使用聚合函数
having 发生在group by之后,因而having中可以使用分组字段,无法直接取到其他字段,可以借助聚合函数

使用group by 要使用group_concat()函数,将所有的name值连接

查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
select post,group_concat(name),count(id) from employee group by post having count(id)<2;

查询各岗位平均薪资大于120000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary)>120000;

查询各岗位平均薪资大于120000且小于125000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary)>120000 and avg(salary)<125000;

order by 查询排序 默认升序 desc降序
按单列排序
select * from employee order by age;
select * from employee order by age asc;
select * from employee order by age desc;

按多列排序 先按年龄升序排序,如果年龄相同,再按id降序排序
select * from employee order by age asc,id desc;

limit 限制查询的记录数
select * from employee order by salary desc limit 3; 默认初始位置为0
select * from employee order by salary desc limit 0,5; 从第0条开始,即先查询出第一条,然后再往后查4条 1,2,3,4,5
select * from employee order by salary desc limit 5,5; 从第5条开始,即先查询出第6条,然后再往后查4条 6,7,8,9,10

多表查询

create table department2(
id int not null,
name varchar(20) not null
);

create table employee2(
id int primary key auto_increment,
name varchar(20) not null,
sex enum('男','女') not null default '男',
age int not null,
dep_id int not null
);

insert into department2 values
(800,'技术部'),
(8001,'运维部'),
(8002,'人力资源'),
(8003,'销售部'),
(8004,'业务部');

insert into employee2(name,sex,age,dep_id) values
('ropon','男',18,800),
('luo','男',20,8001),
('peng','女',19,8002),
('lisi','女',22,8003),
('zhangsan','女',22,8005);

多表连接查询
语法
select 表1.字段1,表.字段2,表2.字段1... from 表1 inner|left|right join 表2 on 表1.字段=表2.字段;

交叉连接 不适用任何匹配条件 生成笛卡尔积
select * from employee2,department2;

内连接:只连接匹配的行
select employee2.id,employee2.name,employee2.age,department2.name from employee2
inner join department2 on employee2.dep_id=department2.id;

select employee2.id,employee2.name,employee2.age,department2.name from employee2,department where employee2.dep_id=department2.id;

以上2条sql含义相同

外链接之左连接:优先显示左表全部记录
select employee2.id,employee2.name,employee2.age,department2.name from employee2
left join department2 on employee2.dep_id=department2.id;

外链接之右连接:优先显示右表全部记录
select employee2.id,employee2.name,employee2.age,department2.name from employee2
right join department2 on employee2.dep_id=department2.id;

全外连接:显示左右两个表全部记录
select * from employee2 left join department2 on employee2.dep_id=department2.id
union all
select * from employee2 right join department2 on employee2.dep_id=department2.id;

select * from employee2 left join department2 on employee2.dep_id=department2.id
union
select * from employee2 right join department2 on employee2.dep_id=department2.id;

注意 union与union all的区别:union会去掉相同的纪录

符合条件连接查询
select employee2.id,employee2.name,employee2.age,department2.name from employee2
inner join department2 on employee2.dep_id=department2.id
where age>20;

select employee2.id,employee2.name,employee2.age,department2.name from employee2
inner join department2 on employee2.dep_id=department2.id
where age>18
order by age asc;

子查询
子查询指将一个查询语句嵌套在另一个查询语句中
内层查询语句结果,可以为外层查询语句提供查询条件
子查询语句中可以包含:in,not in,any,all,exists,not exists等关键字

in
查询平均年龄在20岁以上的部门名
select id,name from department2
where id in
(select dep_id from employee2 group by dep_id having avg(age)>20);

查看技术部员工姓名
select name from employee2
where dep_id in
(select id from department2 where name='技术部');

查看不足1人的部门名
select id,name from department2
where id not in
(select dep_id from employee2 group by dep_id);

带比较运算符的子查询
比较运算符:=,!=,>,>=,<,<=,<>

查询大于 所有人平均年龄 的员工姓名与年龄
select name,age from employee2 where age >
(select avg(age) from employee2);

查询大于 部门内平均年龄 的员工姓名与年龄

先查询每个部门平均年龄
select dep_id,avg(age) from employee2 group by dep_id;

得出临时表,表中dep_id和对应平均年龄,然后与员工表内链查询

select employee2.name,employee2.age from employee2
inner join
(select dep_id,avg(age) as avg_age from employee2 group by dep_id) as t1
on employee2.dep_id=t1.dep_id
and employee2.age>t1.avg_age;

带exists关键字的子查询

exists关键字表示存在,使用exists关键字时,内层查询语句不返回查询记录,而返回布尔值True或False
当返回True时,外层查询语句进行查询;当返回False时,外层查询语句不进行查询
SELECT * FROM employee2 WHERE EXISTS (SELECT id FROM department2 WHERE id=800);

SELECT * FROM employee2 WHERE EXISTS (SELECT id FROM department2 WHERE id=8005);

SELECT employee.`name`,employee.hire_date,employee.post FROM employee
INNER JOIN
(SELECT post,MAX(hire_date) AS maxdate FROM employee GROUP BY employee.post) AS t1
ON employee.hire_date = t1.maxdate;

mysql备份相关命令

导出数据库或数据库下某个表
语法：mysqldump -u用户名 -p密码 [--default-character-set=utf8] 数据库名 [表名] > 导出sql文件名
例子：mysqldump -uropon -p123456 ropon>~/ropon.sql [ d:\ropon.sql ]

仅导出数据库或数据库下某个表结构
语法：mysqldump -u用户名 -p密码 [--default-character-set=utf8] -d [--add-drop-table] 数据库名 [表名] > 导出sql文件名
例子：mysqldump -uropon -p123456 -d ropon >~/ropon.sql [ d:\ropon.sql ]

导入sql备份文件
语法：mysql -u用户名 -p密码 数据库名<~/ropon.sql [ d:\ropon.sql ]
例子：mysql -uropon -p123456 ropon<~/ropon.sql [ d:\ropon.sql ]

语法：mysqldump -u用户名 -p密码 数据库名<~/ropon.sql [ d:\ropon.sql ]
例子：mysqldump -uropon -p123456 ropon<~/ropon.sql [ d:\ropon.sql ]

语法：mysql -u用户名 -p密码
例子：mysql>source ~/ropon.sql [ d:\ropon.sql ];

索引
约束和加速查找

普通索引
唯一索引
主键索引
联合索引(多列)
联合主键索引
联合唯一索引
联合普通索引

无索引:从前往后一条一条查询
有索引:创建索引本质就是创建额外文件(某种格式存储,查询时候,先去额外文件找,定好位置
然后再去原始表中直接查询)

索引特点:
额外的文件保存特殊的数据结构
查询快,但增删改依然慢
创建索引之后,必须命中才有效

索引种类
hash类型的索引:查询单条快,查询范围慢
btree类型的索引:b+数,层数越多,数据量 成指数级 增长(innodb默认支持)

普通索引 仅有一个加速查找
创建表同时加普通索引
create table userinfo(
id int primary key auto_increment,
name varchar(20) not null,
passwd varchar(30) not null,
index ix_name(name)
);

创建普通索引
create index 索引名字 on 表名(列名)

删除索引
drop index 索引名字 on 表名

查看索引
show index from 表名

唯一索引 唯一索引有两个功能:加速查找和唯一约束 允许为空null,但只有能一行
创建表同时加唯一索引
create table userinfo(
id int primary key auto_increment,
name varchar(20) not null,
passwd varchar(30) not null,
unique index ix_name(name)
);

创建唯一索引
create unique index 索引名字 on 表名(列名)

删除索引
drop index 索引名字 on 表名

主键索引 主键索引有两个功能:加速查找和唯一约束 不能为空null
创建表同时加主键索引
create table userinfo3(
id int primary key auto_increment,
name varchar(20) not null,
passwd varchar(30) not null,
);

创建主键索引
alter table 表名 add primary key(列名)

删除主键索引
alter table 表名 drop primary key;

重新定义列类型并删除primay key属性
alter table 表名 modify 列名 int,drop primay key;

组合索引
组合索引是将n个列组合成一个索引
应用场景:频繁的同时使用n列来进行查询
create index 索引名 on 表名(列名1,列名2);

创建索引
命中索引
正确使用索引

create table userinfo4(
id int not null,
name varchar(20) not null,
gender enum('男','女') default '男',
email varchar(50) not null
);

创建存储过程,批量插入数据
声明存储过程的结束符号$$
delimiter $$
create procedure auto_insert1()
BEGIN
declare i int default 1;
while(i<3000000)do
insert into userinfo4 values(i,concat('ropon',i),'男',concat('pengge',i,'@ropon.top'));
set i=i+1;
end while;
END$$
delimiter ;

查看存储过程
show create procedure auto_insert1\G;

调用存储过程
call auto_insert1();

删除存储过程
drop procedure auto_insert1;

生成大数据例子2
drop table if exists userinfotest;
create table userinfotest(
id int not null primary key auto_increment,
name varchar(20) not null,
age int not null,
email varchar(50) not null
)engine=myisam;

创建存储过程,批量插入数据 例子2
delimiter $$
create procedure auto_insert3(in num int)
BEGIN
declare i int default 1;
while(i<num)do
insert into userinfotest(name,age,email) values(concat('ropon',i),rand()*50+18,concat('pengge',i,'@ropon.top'));
set i=i+1;
end while;
END$$
delimiter ;

正确使用索引,以下例子没有正确使用
like '%pon66' 模糊匹配
select email from userinfo4 where name like '%pon66';

使用函数 reverse 反转
select * from userinfo4 where reverse(name) = '999nopor';

or
注意:当or条件中 有未建议索引的列 才失效
select * from userinfo4 where id = 8 or name='pengg1888';
select * from userinfo4 where id =8 or email = 'pengge168@ropon.top' and name = 'pengge168';

数据类型不一致
select * from userinfo4 where name = 43565;

!=
如果是主键,会走索引
select count(email) from userinfo4 where name != 'ropon';
select count(email) from userinfo4 where id != '28';

>
如果是主键或者索引是整数类型,会走索引
select * from userinfo4 where name > 'ropon';
select * from userinfo4 where id > 666;

order by
根据索引排序时,选择映射如果不是索引,则不走索引
但是如果对主键排序,则还是会走索引
select * from userinfo4 order by id desc limit 50;

组合索引最左前缀,查询时优先比配左边
如果组合索引为(name,email)
name and email 走索引
select * from userinfo4 where name = 'ropon123' and email='pengge123@ropon.top';
name 走索引
select * from userinfo4 where name = 'ropon123';
email 不走索引
select * from userinfo4 where email='pengge123@ropon.top';

索引建议
避免使用select *
创建表时尽量使用char代替varchar
表的字段顺序固定长度优先
组合索引代替多个单列索引
尽量使用短索引 create index ix_title on article(title(10));
使用连接join代替子查询
连接表时注意条件类型是否一致
散列(重复少)不适合创建索引,比如性别

分析优化
显示sql执行信息参数
explain sql语句

参数说明

select_type
查询类型
simple 简单查询
primary 最外层查询
subquery 映射为子查询
derived 子查询
union 联合
union result 使用联合的结果
table
正在访问的表名
type
查询时访问方式
all 全表扫描 对数据表从头到尾找一遍
select * from userinfo4;
不过如果有limit限制,找到之后就不再继续向下扫描
index 全索引扫描 对索引从头到尾找一遍
range 对索引列进行范围查找
index_merge 合并索引,使用多个单列索引搜索
ref 根据索引查找一个或多个值
eq_ref 连接时使用primary key 或unique类型
const 常量
system 系统

慢查询日志
show variables like 'slow_query%';
slow_query_log 慢查询状态
slow_query_log_file 慢查询日志的存放路径

开启慢查询日志
set global slow_query_log=1;

或者my.cnf [mysqld]后配置
slow_query_log =1

show variables like 'long%';
long_query_time
慢查询超时时间,默认10s

修改慢查询超时时间 比如修改为1s
set global long_query_time=1

或者my.cnf [mysqld]后配置
long_query_time =1

修改慢查询日志存放路径
my.cnf [mysqld]后配置
slow_query_log_file ='xxxx'