MySQL笔记
文章以及视频
参考文章:https://www.cnblogs.com/linhaifeng/p/7278389.html
参考视频:https://www.bilibili.com/video/BV1Dt4y1X7gV?p=1
-
数据库管理软件,套接字软件
-
共享数据,带来的数据竞争
-
处理数据安全问题加锁
-
处理数据库库层面安全问题
-
数据库管理软件性能问题
-
mysql的基本管理,SQL语句的一个学习
-
mysql就是一个基于socket编写的C/S架构的软件
-
数据库服务器:运行数据库管理软件的计算机
-
数据库管理软件:mysql,oracle,db2,sqlserver
-
库:文件夹
-
表:文件
-
记录:事物一系列典型的特征
-
数据:描述事物特征的符号
-
设置密码与破解密码(跳过授权表)
-
mysqld --skip-grant-tables # 跳过授权表
-
mysqladmin -uroot -p password “xxx” # 设置mysql管理员的账号密码
-
updata mysql.user set password=password("") where user=“root” and host=“localhost”; # 密码为空
-
flush privileges;
-
tasklist |findstr mysql
-
taskkill -F /PID xxx
-
mysqld --install:将mysqld添加到服务组件中
-
mysqld --remove:将mysqld从服务组件中已出
-
net start mysql
-
net stop mysql
-
mysql -uroot -pxxx -h 127.0.0.1 -P 3306
SQL语句:
- help create查询create语句有哪些,同理其他语句也一样
操作文件夹(库)
-
增
- create database db1 charset utf8;
-
查
- show create database db1;
- show databases;
-
改
- alter database db1 charset gbk;
-
删
- drop database db1;
操作文件(表)
-
切换文件夹
-
use db1;
-
-
查看当前所在文件夹
- select database();
-
增
- create table t1(id int, name char);
-
查
- show create table t1;
- show tables;
- desc t1;
-
改
- alter table t1 modify name char(6);
- alter table t1 change name NAME char(7);
-
删
- drop table t1;
操作文件内容(记录)
-
增
- insert t1(id,name) values(1,‘alex’),(2,‘hali’),(3,‘sogo’);
-
查
- select id,name from db1.t1;
- select * from db.t1;(不推荐)
-
改
- update db1.t1 set name=‘hhh’;
- update db1.t1 set name=‘gg’ where id=2;
-
删
- delete from t1;# 清空表
- truncate t1; # 应该用truncate来清空表
- delete from t1 where id=2;
-
什么是存储引擎
- 存储引擎就是表的类型
-
查看mysql支持的存储引擎
- show engines;
-
指定表类型/存储引擎
- create table t1(id int)engine=innodb;
- create table t2(id int)engine=memory;
- create table t3(id int)engine=blackhole;
- create table t4(id int)engine=myisam;
-
insert into t1 values(1);
-
insert into t2 values(1);
-
insert into t3 values(1);
-
insert into t4 values(1);
-
查看表结构/数据一行一行的显示:加\G,不用加分号
- show create table mysql.user\G
-
复制表
- 复制表结构和数据
- create table t1 select * from mysql.user;
- 只复制表结构,没有数据,下面两句一样的效果
- create table t1 select * from mysql.user where 1>2;
- create table t1 like mysql.user;
- 复制表结构和数据
字段类型
整数类型
-
整数类型的宽度指的是显示宽度,存储宽度修改不了,其他类型都是存储宽度
-
tinyint 1个字节
-
int 4个字节
-
浮点类型
- 区别在于精度,还有存储长度
- float,最大总长为255,小数最大30,精度最低
- double,最大总长为255,小数最大为30,精度其次
- decimal,最大总长为65,小数最大为30,精度最高
日期类型
- create table student(id int,name char(6),born_year year,birth_date date,class_time time,reg_time datetime);
- insert into student values(1,‘hh’,now(),now(),now(),now());
- insert into student values(1,‘gg’,‘1997’,‘1997-12-12’,‘12:12:12’,‘1997-12-12 12:12:12’);
- datetime和timestamp都是表示时间的类型,而且都有时间范围
- datetime 8个字节,表示1001-9999年(一般使用这个,因为timestamp只能到2038年)
- timestamp 4个字节,表示1970-2038年
字节类型
- char:定长,存取速度快,浪费空间(一般选这个)
- varchar:变长,多加了一个bytes用来存储数据长度,存取速度慢,不浪费空间
- 字符类型的宽度指的是字符的个数
- create table t13(name char(5));
- create table t14(name varchar(5));
- insert into t13 values(‘理解’);
- insert into t14 values(‘lijie’);
枚举类型与集合类型
-
字段的值只能在给定的范围中选择,如单选框、多选框
-
enum 单选
- 只能在给定的范围内选一个值,如性别sex 男male/女female
-
set 多选
- 在给定的范围内选择一个或一个以上的值(爱好1,爱好2,爱好3)
-
create table consumer( id int, name char(16), sex enum('male','female','other'), level enum('vip1','vip2','vip3'), hobbies set('play','music','read','run') ); insert into consumer values(1,'alex','male','vip2','music,read,run');
约束条件
null与default
-
create table t15( id int(11) unsigned zerofill # 这些都是type而不是约束条件 ); create table t16( id int, name char(6), sex enum('male','female') not null default 'male' ); insert into t16(id,name) values(1,'alex');
unique key
-
单列唯一
-
方式一: create table department( id int unique, name char(10) unique ); 方式二: create table department( id int, name char(10), unique(id), unique(name) ); insert into department values(1,'IT'),(2,'sale');
-
-
联合唯一
-
create table services( id int unique, ip char(15), port int, unique(ip,port) ); insert into services values(1,'192.168.1.10',80),(2,'192.168.1.10',81),(3,'192.168.1.13',80);
-
primary key:不为空且唯一
-
primary key 相当于 not null unique
-
存储引擎(innodb):对于innodb存储引擎来说,一张表内必须有一个主键
-
单列主键
-
create table t17( id int primary key, name char(16) ); insert into t17 values(1,'alex'),(2,'alun'); insert into t17(name) values('gg'); insert into t17(name) values('hh'); create table t18( id int not null unique, name char(16) );
-
-
-
复合主键
-
create table t19( ip char(15), port int, primary key(ip,port) ); insert into t19(name) values('1.1.1.1',80),('1.1.1.1',81),('2.2.2.2',80);
-
auto_increment
-
create table t20( id int primary key auto_increment, name char(16) ); insert into t20(name) values('egon'),('alex'),('wwx'); insert into t20(id,name) values(7,'hh'); insert into t20(name) values('egon1'),('alex1'),('wwx1');
-
了解
-
show variables like 'auto_inc%'; #步长: auto_increment_increment默认为1 #起始偏移量: auto_increment_offset默认为1 #session级别(本次链接)设置步长 set session auto_increment_increment=5; # global全季设置 set global auto_increment_increment=5; # 设置起始偏移量 set global auto_increment_offset=3; #强调:起始偏移量<=步长 create table t21( id int primary key auto_increment, name char(16) ); insert into t21(name) values('egon'),('alex'),('wxx'); # 清空表: delete from t20; delete form t20 where id=3; insert into t20(name) values('xxx'); truncate t20; # 应该用truncate来清空表
-
foreign key :建立表之间的关系
一般建表只是逻辑上关联而不使用外键
-
1 建立表关系
-
# 先建被关联的表(必须有主键) create table dep( id int primary key, name char(16), comment char(50) ); # 再建关联的表 create table emp( id int primary key, name char(10), sex enum('male','female'), dep_id int, foreign key(dep_id) references dep(id) on delete cascade on update cascade );
-
-
2 插入数据
-
# 先往被关联表插入数据 insert into dep values(1,'IT','技术能力有限部门'),(2,'销售','销售能力不足部门'),(3,'财务','花钱特别多部门'); # 再往关联表插入数据 insert into emp values(1,'egon','male',1); insert into emp values(2,'alex','male',1); insert into emp values(3,'hhh','male',2); insert into emp values(4,'faker','male',3);
-
# 现在要删除部门和对应的员工 delete from emp where dep_id=1; delete from dep where id=1;
-
表关系
先站在左边表角度看两张表关系,再站在右边表看两张表关系
多对一
-
出版社与书
-
create table press( id int primary key auto_increment, name varchar(20) ); create table book( id int primary key auto_increment, name varchar(20), press_id int not null, foreign key(press_id) references press(id) on delete cascade on update cascade ); insert into press(name) values ('北京出版社'),('广东出版社'),('湖南出版社'); insert into book(name, press_id) values ('九阳神功',1),('九阴真经',2),('天龙八部',2),('降龙十八掌',3),('葵花宝典',3);
-
多对多
两边都是多对一,就是多对多,因为要先建被关联的表再建关联表,这样会导致两边的表谁都不能先建,所以建一个中间表将外键独立出来
-
作者与书
-
create table author( id int primary key auto_increment, name varchar(20) ); # 书的表上面多对一已经建立了 # 建立中间表 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) ); # 插入4个作者,id依次排开 insert into author(name) values('egon'),('alex'),('bihu'),('hty'); # 每个作者与自己的代表作 egon: 九阳神功,九阴真经 alex:九阳神功,葵花宝典 bihu:九阴真经,天龙八部 hty:降龙十八掌,天龙八部 insert into author2book(author_id,book_id) values (1,1), (1,2), (2,1), (2,5), (3,2), (3,3), (4,4), (4,3) ;
-
一对一
在foreign key的基础上加unique
-
培训机构:客户和学生,客户发展成学生
-
# 学生一定是客户转换来的 # 客户不一定称为一个学生 # 一定是student表来foreign key 表customer,而且加上unique create table customer( id int primary key auto_increment, name varchar(20) not null, qq varchar(10) not null, phone char(11) not null ); create table student( id int primary key auto_increment, class_name varchar(20) not null, customer_id int unique, # 因为是一对一,该字段一定要唯一 foreign key(customer_id) references customer(id) # 外键的字段一定要保证unique on delete cascade on update cascade ); # 增加客户 insert into customer(name,qq,phone) values ('aaa','213213123','123123123'), ('bbb','213213123','123123123'), ('cc','213213123','123123123'), ('dd','213213123','123123123'), ('ee','213213123','123123123') ; # 增加学生 insert into student(class_name,customer_id) values ('培训1期',1), ('培训1期',2), ('培训2期',3), ('培训4期',5), ;
-
查询语句
https://www.cnblogs.com/linhaifeng/articles/7356064.html
**语句执行顺序:**from ->on->join-> where -> group by -> having ->select -> distinct -> order by ->limit
单表查询
-
select distinct 字段1,字段2,字段3 from 库.表
- where条件
- group by 分组条件
- having 过滤
- order by 排序字段
- limit n;
-
简单查询
-
避免重复distinct
-
通过四则运算查询(加减乘除)
-
定义显示格式:
- concat()函数用于连接字符串
- concat(‘姓名:’,name,‘年薪:’.salary*12)
- concat_ws() 第一个参数可以为分隔符
- SELECT CONCAT_WS(’:’,name,salary*12) AS Annual_salary
FROM employee;
- SELECT CONCAT_WS(’:’,name,salary*12) AS Annual_salary
- concat()函数用于连接字符串
-
分组:group by
- set global sql_mode=‘ONLY_FULL_GROUP_BY’; # 只能取分组的字段,以及每个组聚合结果
- group_concat(字段) 获取分组后的字段内容
-
聚合函数
- max
- min
- avg
- sum
- count
-
having:分组后过滤
-
order by 排序:执行顺序是在select 后面
- 升序:order by 字段 asc
- 降序:order by 字段 desc
-
limit 5,5:记录条数从5开始取5条
-
总结:
-
语法顺序:
- select distinct 字段1,字段2,字段3 from 库.表
- where条件
- group by 分组条件
- having 过滤
- order by 排序字段
- limit n;
- select distinct 字段1,字段2,字段3 from 库.表
-
执行顺序:from ->on->join-> where -> group by -> having ->select -> distinct -> order by ->limit
-
def from(db,table): f=open(r'%s\%s' %(db,table)) return f def where(condition,f): for line in f: if condition: yield line def group(line): pass def having(group_res): pass def distinct(having_res): pass def order(distinct_res): pass def limit(order_res): pass def select(): f = from('db1','t1') lines = where('id>3',f) group_res = group(lines) having_res = having(group_res) distinct_res = distinct(having_res) order_res = order(distinct_res) res = limit(order_res) print(res) return res
-
-
-
正则表达式:regexp
- select * from employee where name regexp ‘^jin.*(g|n)$’;
- 匹配员工名字开头是jin后面可能是g或者n结尾的信息
连表操作
表数据:https://www.cnblogs.com/linhaifeng/articles/7267596.html
不要使用where关联两张表,因为mysql有专门的语法来关联,where适合过滤查询数据
内连接inner join
- 只取两张表的共有部分
- select * from employee inner join department on employee.dep_id = department.id;
左连接left join
- 在内连接的基础上只保留左表的记录
- select * from employee left join department on employee.dep_id = department.id;
右连接right join
- 在内连接的基础上只保留右表的记录
- select * from employee right join department on employee.dep_id = department.id;
全外连接full join(mysql不支持)
-
虽然mysql不支持,但可以去实现
-
在内连接的基础上保留左右两表没有对应关系的记录
-
这条语句不支持,但可以用下面代替:select * from employee full join department on employee.dep_id = department.id;
-
select * from employee left join department on employee.dep_id = department.id union select * from employee right join department on employee.dep_id = department.id;
子查询
-
子查询是将一个查询语句嵌套在另一个查询语句中
-
带IN关键字的子查询
-
带比较运算符的子查询
-
带EXISTS关键字的子查询
-
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-H9tk3qFf-1598547538525)(C:\Users\dujun\AppData\Roaming\Typora\typora-user-images\image-20200826173028616.png)]
-
# 查询平均年龄在25岁以上的部门名 # 联合查询 select department.name from employee right join department on employee.dep_id=department.id group by dep_id having avg(age)>25; # 子查询 select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) >25); # 查看技术部门员工的姓名 select name from employee where dep_id in (select id from department where name='技术'); # 查看不足2人(<=1)的部门名 select * from department where id not in (select dep_id from employee group by dep_id having count(dep_id) >1); # 带EXISTS关键字的子查询 select * from employee where EXISTS (select id from department where name='IT'); # 可以将查询查出来的表用括号括起来然后as成一张表用于关联表 select * from department inner join (select name,sex,dep_id from employee group by dep_id having sex='female') as female on department.id = female.dep_id;
# 查询全部学生都选修了的课程号和课程名
select cid,cname from course where cid in (select course_id from score group by course_id having count('sid') = (select count(sid) from student))
# 查询平均成绩大于85的学生姓名和平均成绩
select s1.sname,s2.avg(number) from student as s1 inner join (select student_id,avg(number) from score group by student_id having avg(number) > 85) as s2 on s1.sid=s2.student_id;
多表查询
- 使用表关联语句和子查询语句来搭配完成多表查询
权限管理(grant/revoke)
-
1 创建账号
-
# 本地账号 create user 'djt'@'localhost' identified by '123'; # mysql -udjt -p123 # 远程账号 create user 'djt'@'192.168.1.%' identified by '123'; # 这句远程账号命令是指创建一个名为djt密码为123的账号,只允许在ip网段地址为192.168.1的客户端主机上来连接我现在本地的mysql服务端 #mysql -udjt -p123 -h 服务端ip create user 'djt'@'192.168.1.31' identified by '123'; create user 'djt'@'%' identified by '123'; #任意网段
-
-
2 授权与撤回权限
-
# 下面四张表对权限的范围是依次递减,user是指所有库下所有的表,db是指只对指定的库开放权限,tables_priv指只对指定的表,column_priv指只对指定的字段 user:*.* db:db1.* tables_priv:db1.t1 columns_priv:id,name # 授权权限只有root用户拥有 # 授权 grant all on *.* to 'djt'@'localhost'; # 对localhost下的djt用户开放所有权限作用在所有库和所有表 grant select on *.* to 'djt'@'localhost'; # 对localhost下的djt用户只开放select权限作用在所有库和所有表 grant select on db4.t1 to 'djt'@'localhost'; #对localhost下的djt用户只开放select权限作用在数据库db4 grant select(id,name),update(age) on db4.t2 on 'djt'@'localhost'; # 指定了字段,只开放了select和update权限,针对db4库t2表下的id、name、age字段 # 取消权限(注意是from) revoke select on *.* from 'djt'@'localhost'; 对localhost下的djt用户撤回对所有 库的所有权限 revoke select on db4.* from 'djt'@'localhost'; # 对localhost下的djt用户撤回对db4 库的select权限
-
navicat
pymysql
# 增删改
import pymysql
#建立连接
conn = pymysql.connect(
host='xxxx',
port=3306,
user = 'xx',
password = 'xx',
db = 'xx',
charset = 'utf8'
)
#拿到游标
cursor = conn.cursor()
#执行sql语句 增删改
# sql = 'insert into dep(name) values(%s)'
# sql = 'update dep set name=%s where name="time"'
sql = 'delete from dep where name=%s'
rows = cursor.execute(sql,('time1'))
# rows = cursor.executemany(sql,['time1','time2','time3'])
print(rows)
# 需要提交事务
conn.commit()
cursor.close()
conn.close()
# 查询
# import pymysql
# #建立连接
# conn = pymysql.connect(
# host='192.168.31.90',
# port=3306,
# user = 'root',
# password = 'root',
# db = 'db8',
# charset = 'utf8'
# )
# #拿到游标
# cursor = conn.cursor(pymysql.cursors.DictCursor) #以字典形式显示
# #执行sql语句 增删改
# sql = 'select * from dep'
# rows = cursor.execute(sql)
# print(rows)
# cursor.scroll(1,mode='absolute') # 游标移动3个位置(绝对位置)
# print(cursor.fetchone())
# cursor.scroll(2,mode='relative') # 游标移动2个位置(相对位置)
# print(cursor.fetchone())
# print(cursor.fetchmany(3))
# print(cursor.fetchall())
#
# cursor.close()
# conn.close()
MySQL内置功能
参考文章:
https://www.cnblogs.com/linhaifeng/articles/7495918.html#_label2
视图(create view xxx as xxxx)(不推荐)
-
不推荐使用,因为数据库中的数据经常修改的话,对应之前创建的虚拟表就也需要修改。
-
将虚拟表保存起来,就不用每次用虚拟表的时候重复执行sql语句,例如:存储多表查询创建的虚拟表
-
在磁盘里面只有表结构,没有表数据,因为它是虚拟表
-
create view course2teacher as select * from course inner join teacher on course.teacher_id=teacher.tid;
触发器
-
使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为,注意:没有查询
-
就像钩子函数,抑或是装饰器,可以定义函数执行前后的操作,为函数添加功能
-
# 插入前 CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW BEGIN ... END # 插入后 CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW BEGIN ... END # 删除前 CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW BEGIN ... END # 删除后 CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW BEGIN ... END # 更新前 CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW BEGIN ... END # 更新后 CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW BEGIN ... END
-
#准备表 CREATE TABLE cmd ( id INT PRIMARY KEY auto_increment, USER CHAR (32), priv CHAR (10), cmd CHAR (64), sub_time datetime, #提交时间 success enum ('yes', 'no') #0代表执行失败 ); CREATE TABLE errlog ( id INT PRIMARY KEY auto_increment, err_cmd CHAR (64), err_time datetime ); #创建触发器 delimiter // CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW BEGIN IF NEW.success = 'no' THEN #等值判断只有一个等号,NEW是一个触发器提供的对象,表示即将插入的数据行的对象,相对的有OLD对象,OLD表示更新/删除操作之前的数据对象 INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ; #必须加分号 END IF ; #必须加分号 END// delimiter ; # 删除触发器:drop trigger tri_after_insert_cmd; #往表cmd中插入记录,触发触发器,根据IF的条件决定是否插入错误日志 INSERT INTO cmd ( USER, priv, cmd, sub_time, success ) VALUES ('egon','0755','ls -l /etc',NOW(),'yes'), ('egon','0755','cat /etc/passwd',NOW(),'no'), ('egon','0755','useradd xxx',NOW(),'no'), ('egon','0755','ps aux',NOW(),'yes'); #查询错误日志,发现有两条 mysql> select * from errlog; +----+-----------------+---------------------+ | id | err_cmd | err_time | +----+-----------------+---------------------+ | 1 | cat /etc/passwd | 2017-09-14 22:18:48 | | 2 | useradd xxx | 2017-09-14 22:18:48 | +----+-----------------+---------------------+ 2 rows in set (0.00 sec)
-
特别的:NEW表示即将插入的数据行,OLD表示即将删除的数据行。
存储过程
-
存储过程包含了一系列可执行的sql语句,存储过程存放于mysql中,通过调用它的名字可以执行其内部的一堆sql
-
解释:
- 对于应用程序员和数据库程序员,应用程序员负责编写程序,而数据库程序员负责管理数据库中的数据,他们在一个项目中需要互相配合。
- 当应用程序员做项目需要操作数据时,数据库程序员可以将mysql的sql多条语句综合总结在一起,相当于封装起来,封装成一个API,直接给应用程序员去调用使用就可以了。
- 而存储过程就相当于将多条sql语句封装成sql操作API的一个过程。
- 这样应用程序员就不需要在应用层方面去编写处理mysql数据库数据的函数,直接使用数据库程序员编写好的API。
-
优点:
- 1 用于代替程序写的SQL语句,实现程序与sql解耦
- 2 基于网络传输,传别名的数据量小,而直接传sql数据量大
-
缺点:
- 程序员扩展功能不方便
-
程序与数据库结合使用的三种方式
-
# 方式一 MySQL:存储过程 程序:调用存储过程 # 方式二 MySQL: 程序:纯SQL语句 # 方式三 MySQL: 程序:类和对象,即ORM(本质还是纯SQL语句)
-
-
1 无参存储过程
-
delimiter // create procedure p1() BEGIN select * from db7.teacher; END // delimiter ; # MySQL中调用 call p1(); # Python中调用 cursor.callproc('p1') print(cursor.fetchall())
-
-
2 有参数过程
-
in表示输入,out表示输出,inout表示即可以是输入又可以是输出
-
delimiter // create procedure p2(in n1 int, in n2 int, out res int) BEGIN select * from db7.teacher where tid>n1 and tid<n2; set res=1; END // delimiter ; # MySQL中调用 set @x=0; # 定义一个x值为0 call p2(2,5,@x); select @x; # 查看结果 # Python中调用 cursor.callproc('p1',(2,4,0)) #@_p2_0=2,@_p2_1=4,@_p2_2=0,mysql内部其实是这三个变量,所以要查看返回值的话,就需要查询这三个元素 cursor.execute('select @_p2_2') print(cursor.fetchone())
-
事务
- 事务用于将某些操作的多个SQL作为原子性操作,一旦有某一个出现错误,即可回滚到原始的状态,从而数据库数据完整性。
- 事务是一种对sql语句的处理方式,将多条sql语句捆绑在一起一起执行。
- 事务、触发器、视图都可以封装到存储过程里面
函数
-
函数和存储过程的区别:
- 单个函数功能没那么多,而存储过程可以将函数、触发器、事务、视图、流程控制语句等都封装在一起,也是另外一方面的函数。
-
mysql提供了内置的函数可以供我们调用,比如:格式化时间的函数date_format(column_name,’%Y-%m’)
-
也可以自定义函数
流程控制
- 流程控制意思是使用mysql给予的if、for、while、repeat语句去控制整个代码的流程。
- 只是mysql的语法和python语法不太一样
索引
参考文章:
https://www.cnblogs.com/linhaifeng/articles/7274563.html
-
底层是B+树实现
-
索引在mysql中也叫做“键”
-
加快数据查询速度
-
索引也是一个文件,索引太多会影响应用程序的性能,索引太多又可能会导致查询速度不够而且修改索引会比较麻烦,所以索引一开始的建立定位就很重要。应该由应用程序员去添加,不该由DBA去添加,因为应用程序员比较熟悉业务的数据流,不管是添加还是修改都比较方便。
索引的原理
- 通过不断地缩小想要获取的数据的范围来筛选出最终的结果,同时把随机事件变成顺序事件,也就是说,有了这种索引机制,我们可以总是用同一种查找方式来锁定数据。
索引的数据结构
- 由于数据是存储在磁盘中,而每次读取数据都要去磁盘的话,会严重浪费计算机资源,这就有了磁盘预读操作,当一次OI时,不仅将当前磁盘地址的数据放入内存缓冲区中,而且还把相邻的数据也读取到内存缓冲区中,因为局部预读行原理,当数据被访问时,它相邻的数据被访问的速度也会很快,这个理论对于索引的数据结构设计非常有帮助。B+树的结构可以实现。
- 索引是B+树实现,B+树是通过二叉查找树,再由平衡二叉树,B树演化而来
- 数据都是存放在B+树的叶子节点,而其他节点都是存放着虚拟的节点外加指针
B+树性质
- BTree:https://blog.csdn.net/ty_hf/article/details/53526822
-
B树:二叉树
-
B-树:平衡二叉树,在B树的基础上加上了平衡算法
-
B+树:在B-树的基础上再定义,只有叶子节点中可以存放数据,非叶子节点都是存储数据的数据层(指针)。根节点和叶子节点都有横向链指针,指向挨着的邻居,加快查询速度
-
B*树:在B+树的基础上为非叶子节点也添加链表指针,用于指向兄弟节点/邻居节点
-
1 索引字段要尽量的小
- 因为从B+树的结构来看,树的高度就是代表查询数据时IO操作的次数,当索引字段小的时候,即代表磁盘块中的数据被分为更多的数据块,这样B+树每一层的数据块就越多,进而B+树的高度就可以越低,即IO操作的次数就越低,数据查询速度就越快。
-
2 索引的最左匹配特性
- 当要查询复合的数据时(’alex‘,20,male),先查询’alex’是在字段name中进行查询,进而在age和sex中查询,是从左到右
-
聚集索引与辅助索引
-
InnoDB存储引擎就是索引组织表,即表中的数据是按照主键顺序存放的,整张表只可以有一个主键
-
聚集索引是根据每个表的主键构造的一颗B+树,而辅助索引是根据某个指定字段来构建的B+树
-
使用explain可以查看索引的情况
-
数据库中的B+树可以分为聚集索引(clustered index)和辅助索引(secondary index)
- 相同点:
- 聚集索引和辅助索引内部都是B+树的形式,即高度是平衡的,叶子节点存放着数据
- 不同点
- 聚集索引中的叶子节点存放的是整行数据,辅助索引只存放对应字段的数据和指向聚集索引的指针
- 相同点:
MySQL索引管理
MySQL常用的索引
-
普通索引INDEX:加快查找
-
唯一索引:
-
主键索引PRIMARY KEY:加快查找+约束(不为空、不能重复)
-
唯一索引UINIQUE:加快查找+约束(不能重复,可以为空)
-
联合索引:
- PRIMARY KEY(id,name):联合主键索引
- UNIQUE(id,name):联合唯一索引
- INDEX(id,name):联合普通索引
-
其他索引例如空间索引等,暂时不去了解
应用场景
- 商场会员卡活动,会员卡信息包括:会员卡编号、姓名、身份证、其他详细信息
- 编号可以使用主键索引(PRIMARY KEY)
- 姓名可以使用普通索引(INDEX)
- 身份证可以使用唯一索引(UNIQUE)
- 其他详细信息可以使用全文索引(FULLTEXT)
索引两大类型
- hash类型的索引:查询单条块,范围查询慢
- btree类型的索引:b+树,层数越多,数据量指数级增长(innodb默认支持它)
- 不同的存储引擎支持的索引类型:
- InnoDB:支持事务,支持行级别锁定,支持B-tree、Full-text等索引,不支持Hash索引
- MyISAM:不支持事务,支持标级别锁定,支持B-tree、Full-text等索引,不支持Hash索引
- Memory:不支持事务,支持标级别锁定,支持B-tree、hash等索引,不支持Full-text索引
- NDB:支持事务,支持表级别锁定,支持Hash索引,不支持B-tree、Full-text等索引
- Archive:不支持事务,支持表级别锁定,不支持B-tree、Hash、Full-text等索引
联合索引和覆盖索引
- 联合索引:指让两个字段或更多字段结合成一个索引
- alter table s1 add key|index(name,sex)
- 使用key或者index关键字,将name字段和sex字段做一个联合索引
- 覆盖索引:https://www.jianshu.com/p/8991cbca3854
- InnoDB聚集索引普通索引,回表操作,索引覆盖
查询优化神奇-explain
- explain语句可以查看查询语句的执行状态,显示使用了什么索引,查询记录是几条,查询语句是否filesort,较为重要的两个数据是rows和Extra显示的数据
慢查询优化
- 慢查询是指查询的时间超过了阈值(规定的时间值)
- 优化的基本步骤
- 0 先运行看看是否真的很慢,注意设置SQL_NO_CACHE
- 1 where条件单表查,锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起,单表每个字段分别查询,看哪个字段的却分度最高
- 2 使用explain语句查看执行状态
- 3 order by limit 形式的sql语句让排序的表优先查
- 4 了解业务方使用场景,判断是否需要增加索引来优化
- 5 加索引时参照建索引的几大原则
- 6 观察结果,不符合预期则从0步重新分析