MySQL数据库进阶
1 Navicat软件
navicat可以用可视化界面操作数据库
"""
一开始学习python的时候 下载python解释器然后直接在终端书写
pycharm能够更加方便快捷的帮助书写python代码
我们在终端操作MySQL 也没有自动提示也无法保存等等 不方便开发
Navicat内部封装了所有的操作数据库的命令
用户在使用它的时候只需要鼠标点点即可完成操作 无需书写sql语句
"""
1.1 Navicat安装
直接百度搜索 有破解版的也有非破解
非破解的有试用期 你如果不嫌麻烦 你就用使用
到期之后重新装再使用 或者破解一下也很简单
https://www.cr173.com/soft/126934.html
下载完成后是一个压缩包 直接解压 然后点击安装 有提醒直接点击next即可
navicat能够充当多个数据库的客户端
navicat图形化界面有时候反应速度较慢 你可以选择刷新或者关闭当前窗口再次打开即可
当你有一些需求该软件无法满足的时候 你就自己动手写sql
1.2 Navicat提示
"""
1 MySQL是不区分大小写的
验证码忽略大小写
内部统一转大写或者小写比较即可
upper
lower
2 MySQL建议所有的关键字写大写
3 MySQL中的注释 有两种
--
#
4 在navicat中如何快速的注释和解注释
ctrl + ？ 加注释
ctrl + ？ 基于上述操作再来一次就是解开注释
如果你的navicat版本不一致还有可能是
ctrl + shift + ？解开注释
"""
1.3 练习题
"""
课下一定要把握上课将的这几道题全部自己独立的理解并写出来
在解决sql查询问题的时候 不要慌
一步一步慢慢来 最终能够东拼西凑出来就过关了！！！
"""
-- 1、查询所有的课程的名称以及对应的任课老师姓名
SELECT
course.cname,
teacher.tname
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid;
-- 2、查询平均成绩大于八十分的同学的姓名和平均成绩
SELECT
student.sname,
t1.avg_num
FROM
student
INNER JOIN (
SELECT
score.student_id,
avg( num ) AS avg_num
FROM
score
INNER JOIN student ON score.student_id = student.sid
GROUP BY
score.student_id
HAVING
AVG( num ) > 80
) AS t1 ON student.sid = t1.student_id;
-- 3、 查询没有报李平老师课的学生姓名
# 分步操作
# 1 先找到李平老师教授的课程id
# 2 再找所有报了李平老师课程的学生id
# 3 之后去学生表里面取反 就可以获取到没有报李平老师课程的学生姓名
SELECT
student.sname
FROM
student
WHERE
sid NOT IN (
SELECT DISTINCT
score.student_id
FROM
score
WHERE
score.course_id IN ( SELECT course.cid FROM teacher INNER JOIN course ON teacher.tid = course.teacher_id WHERE teacher.tname = '李平老师' )
);
-- 4、 查询没有同时选修物理课程和体育课程的学生姓名
-- (只要选了一门的 选了两门和没有选的都不要)
# 1 先查物理和体育课程的id
# 2 再去获取所有选了物理和体育的学生数据
# 3 按照学生分组 利用聚合函数count筛选出只选了一门的学生id
# 4 依旧id获取学生姓名
SELECT
student.sname
FROM
student
WHERE
student.sid IN (
SELECT
score.student_id
FROM
score
WHERE
score.course_id IN ( SELECT course.cid FROM course WHERE course.cname IN ( '物理', '体育' ) )
GROUP BY
score.student_id
HAVING
COUNT( score.course_id ) = 1
);
-- 5、 查询挂科超过两门(包括两门)的学生姓名和班级
# 1 先筛选出所有分数小于60的数据
# 2 按照学生分组 对数据进行计数获取大于等于2的数据
SELECT
class.caption,
student.sname
FROM
class
INNER JOIN student ON class.cid = student.class_id
WHERE
student.sid IN (
SELECT
score.student_id
FROM
score
WHERE
score.num < 60 GROUP BY score.student_id HAVING COUNT( score.course_id ) >= 2
);
2 pymysql模块
python中的pymysql模块
2.1 基本使用
"""
pip3 install pymysql
支持python代码操作数据库MySQL
"""
import pymysql
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='\\123456',
database='day48',
charset='utf8'
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 括号内不加cursor=pymysql.cursors.DictCursor拿到的数据是((2, '李平老师'), (3, '刘海燕老师'), (4, '朱云海老师'), (5, '李杰老师'))
# 括号内加了cursor=pymysql.cursors.DictCursor后拿到的数据是[{'tid': 2, 'tname': '李平老师'}, {'tid': 3, 'tname': '刘海燕老师'}, {'tid': 4, 'tname': '朱云海老师'}, {'tid': 5, 'tname': '李杰老师'}]
sql = 'select * from teacher' # 要执行的sql语句
res = cursor.execute(sql)
print(res) # res是sql语句影响的行数
print(cursor.fetchone()) # 只拿一条(fetch操作是类似指针的形式,取一条后再取一条就是第二条)
print(cursor.fetchall()) # 拿所有数据(拿了除了第一条的所有数据)
print(cursor.fetchmany(2)) # 拿2条数据(已经没数据了,拿到的是空)
cursor.scroll(-1) # 相对现在指针位置,左移一位
print(cursor.fetchall()) # 拿所有数据(拿了最后一条数据)
cursor.scroll(1, 'absolute') # 从头开始 右移1位
print(cursor.fetchall()) # 拿所有数据(拿了除了第一条的所有数据)
print(type(cursor)) # <class 'pymysql.cursors.DictCursor'>
2.2 增删改查
# 针对增删改 pymysql需要二次确认才能真正的操作数据
import pymysql
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
passwd='123456',
db='day48',
charset='utf8',
# autocommit=True # 该参数默认为False,即不会自动确认
)
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 增
sql = 'insert into user(name,password) values(%s,%s)'
rows = cursor.execute(sql, ('jackson', 123)) # 执行sql语句
print(rows)
rows = cursor.executemany(sql, [('xxx', 123), ('ooo', 123), ('yyy', 123)])
# 将列表中的元素分次执行,即执行了3个sql语句,分别替换
print(rows)
conn.commit() # 确认之后才改数据库的数据
# 修改
sql = 'update user set name="jasonNB" where id=1'
rows = cursor.execute(sql)
print(rows)
conn.commit() # 确认之后才改数据库的数据
# 删除
sql = 'delete from user where id=7'
rows = cursor.execute(sql)
print(rows)
conn.commit() # 确认之后才改数据库的数据
# 查
sql = 'select * from user'
cursor.execute(sql)
print(cursor.fetchall())
# 不需要确认,因为无需更改数据库的数据
"""
增删改查中
删改增它们的操作涉及到数据的修改
需要二次确认
"""
# 还可以一次性插入N多条数据
rows = cursor.executemany(sql,[('xxx',123),('ooo',123)])
3 sql注入
"""
注入就是利用一些语法的特性 书写一些特定的语句实现固定的语法
MySQL注入利用的是MySQL的注释语法
select * from user where name='jason' -- jhsadklsajdkla' and password=''
select * from user where name='xxx' or 1=1 -- sakjdkljakldjasl' and password=''
"""
import pymysql
conn = pymysql.connect(
host = '127.0.0.1',
port = 3306,
user = 'root',
password = '123456',
database = 'day48',
charset = 'utf8' # 编码千万不要加-
) # 链接数据库
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
username = input('>>>:')
# wu' -- dgagsdg
password = input('>>>:')
# 输入任何字符
sql = "select * from user where name='{}' and password='{}'".format(username, password)
print(sql)
# select * from user where name='wu' -- dgagsdg' and password=''
# 该sql语句变成了select * from user where name='wu' + 注释
# 导致直接登录成功
rows = cursor.execute(sql)
if rows:
print('登录成功')
print(cursor.fetchall())
else:
print('用户名密码错误')
# 甚至更进一步,用户名输入fas' or 1=1 -- fdaf
# select * from user where name='fas' or 1=1 -- fdaf' and password=''
# 不仅能不用 用户名和密码登录成功, 并且能直接获取数据库中所有的用户名和密码
# 日常生活中很多软件在注册的时候都不能含有特殊符号
# 是为了防止用户构造出特定的语句入侵数据库
# 防止sql注入的问题的解决方案:
# 敏感的数据不要自己做拼接 交给execute帮你拼接即可
# 结合数据库完成一个用户的登录功能？
import pymysql
conn = pymysql.connect(
host = '127.0.0.1',
port = 3306,
user = 'root',
password = '123456',
database = 'day48',
charset = 'utf8' # 编码千万不要加-
) # 链接数据库
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
username = input('>>>:')
password = input('>>>:')
sql = "select * from user where name=%s and password=%s"
# 不要手动拼接数据 先用%s占位 之后将需要拼接的数据直接交给execute方法即可
print(sql)
rows = cursor.execute(sql,(username,password)) # 自动识别sql里面的%s用后面元组里面的数据替换
if rows:
print('登录成功')
print(cursor.fetchall())
else:
print('用户名密码错误')
4 视图(了解)
4.1 视图的定义
"""
视图就是通过查询得到一张虚拟表，然后保存下来，下次可以直接使用
其实视图也是表
但是视图并没有实际的数据,保存文件只有frm文件,视图看到的数据也是原表中的数据
"""
4.2 视图的作用
"""
如果要频繁的操作一张虚拟表(拼表组成的)，你就可以制作成视图 后续直接操作
"""
4.3 视图的使用
# 固定语法
create view 表名 as 虚拟表的查询sql语句
# 具体操作
create view teacher2course as
select * from teacher INNER JOIN course
on teacher.tid = course.teacher_id
;
4.4 注意
"""
1 创建视图在硬盘上只会有表结构 没有表数据(数据还是来自于之前的表)
2 视图一般只用来查询 里面的数据不要继续修改 可能会影响真正的表
"""
4.5 总结
视图到底使用频率高不高呢？
"""
不高
当创建了很多视图之后 会造成表的不好维护
视图了解即可 基本不用！！！
"""
5 触发器(了解)
5.1 触发器简介
在满足对表数据进行增、删、改的情况下，自动触发的功能
使用触发器可以帮助我们实现监控、日志...
触发器可以在六种情况下自动触发 增前 增后 删前删后 改前改后
5.2 基本语法结构
create trigger 触发器的名字 before/after insert/update/delete on 表名
for each row
begin
sql语句
end
# 具体使用 针对触发器的名字 我们通常需要做到见名知意
# 针对增
create trigger tri_before_insert_t1 before insert on t1
for each row
begin
sql语句
end
create trigger tri_after_insert_t1 after insert on t1
for each row
begin
sql语句
end
"""针对删除和修改 书写格式一致"""
ps:修改MySQL默认的语句结束符 只作用于当前窗口
delimiter $$ 将默认的结束符号由;改为$$
delimiter ;
# 案例
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
);
"""
当cmd表中的记录succes字段是no那么就触发触发器的执行去errlog表中插入数据
NEW指代的就是一条条数据对象
"""
delimiter $$
create trigger tri_after_insert_cmd after insert on cmd
for each row
begin
if NEW.success = 'no' then
insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
end if;
end $$
delimiter ;
# 朝cmd表插入数据
INSERT INTO cmd (
USER,
priv,
cmd,
sub_time,
success
)
VALUES
('jason','0755','ls -l /etc',NOW(),'yes'),
('jason','0755','cat /etc/passwd',NOW(),'no'),
('jason','0755','useradd xxx',NOW(),'no'),
('jason','0755','ps aux',NOW(),'yes');
# 删除触发器
drop trigger tri_after_insert_cmd;
6 事务
6.1 事务的定义
"""
开启一个事务可以包含多条sql语句 这些sql语句要么同时成功
要么一个都别想成功 称之为事务的原子性
"""
6.2 事务的作用
"""
保证了对数据操作的安全性
"""
eg:还钱的例子
egon用银行卡给我的支付宝转账1000
1 将egon银行卡账户的数据减1000块
2 将jason支付宝账户的数据加1000块
你在操作多条数据的时候可能会出现某几条操作不成功的情况
6.3 事务的四大特性
"""
ACID
A:原子性（Atomicity）
一个事务是一个不可分割的单位，事务中包含的诸多操作
要么同时成功要么同时失败
C:一致性（Consistency）
事务必须是使数据库从一个一致性的状态变到另外一个一致性的状态
一致性跟原子性是密切相关的
I:隔离性（Isolation）
一个事务的执行不能被其他事务干扰
（即一个事务内部的操作及使用到的数据对并发的其他事务是隔离的，并发执行的事务之间也是互相不干扰的）
D:持久性（Durability）
也叫"永久性"
一个事务一旦提交成功执行成功 那么它对数据库中数据的修改应该是永久的
接下来的其他操作或者故障不应该对其有任何的影响
"""
6.4 事务的使用
# 事务相关的关键字
# 1 开启事务
start transaction;
# 2 回滚(回到事务执行之前的状态)
rollback;
# 3 确认(确认之后就无法回滚了)
commit;
"""模拟转账功能"""
create table user(
id int primary key auto_increment,
name char(16),
balance int
);
insert into user(name,balance) values
('jason',1000),
('egon',1000),
('tank',1000);
# 1 先开启事务
start transaction;
# 2 多条sql语句
update user set balance=900 where name='jason';
update user set balance=1010 where name='egon';
update user set balance=1090 where name='tank';
"""
总结
当你想让多条sql语句保持一致性 要么同时成功要么同时失败
你就应该考虑使用事务
"""
7 存储过程(了解)
7.1 存储过程简介
存储过程就类似于python中的自定义函数
它的内部包含了一系列可以执行的sql语句，存储过程存放于MySQL服务端中，你可以直接通过调用存储过程触发内部sql语句的执行
7.2 存储过程基本使用
create procedure 存储过程的名字(形参1,形参2,...)
begin
sql代码
end
# 调用
call 存储过程的名字();
7.3 三种开发模型
第一种
"""
应用程序:程序员写代码开发
MySQL:提前编写好存储过程，供应用程序调用
好处:开发效率提升了 执行效率也上去了
缺点:考虑到认为元素、跨部门沟通的问题 后续的存储过程的扩展性差
"""
第二种
"""
应用程序:程序员写代码开发之外 设计到数据库操作也自己动手写
优点:扩展性很高
缺点:
开发效率降低
编写sql语句太过繁琐 而且后续还需要考虑sql优化的问题
"""
第三种
"""
应用程序:只写程序代码 不写sql语句 基于别人写好的操作MySQL的python框架直接调用操作即可 ORM框架
优点:开发效率比上面两种情况都要高
缺点:语句的扩展性差 可能会出现效率低下的问题
"""
总结
第一种基本不用。一般都是第三种，出现效率问题再动手写sql
`
7.4 存储过程具体演示
sql语句
delimiter $$
create procedure p1(
in m int, # 只进不出 m不能返回出去
in n int,
out res int # 该形参可以返回出去
)
begin
select tname from teacher where tid>m and tid<n;
set res=666; # 将res变量修改 用来标识当前的存储过程代码确实执行了
end $$
delimiter ;
# 针对形参res 不能直接传数据 应该传一个变量名
# 定义变量
set @ret = 10;
call p1(2,5,@ret);
# 查看变量对应的值
select @ret;
pymysql模块中使用存储过程
import pymysql
conn = pymysql.connect(
host = '127.0.0.1',
port = 3306,
user = 'root',
passwd = '123456',
db = 'day48',
charset = 'utf8',
autocommit = True
)
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 调用存储过程
cursor.callproc('p1',(1,5,10))
"""
@_p1_0=1
@_p1_1=5
@_p1_2=10
"""
# print(cursor.fetchall())
cursor.execute('select @_p1_2;')
print(cursor.fetchall())
8 函数(了解)
如果存储过程是自定义函数，函数就类似于内置函数
('jason','0755','ls -l /etc',NOW(),'yes')
CREATE TABLE blog (
id INT PRIMARY KEY auto_increment,
NAME CHAR (32),
sub_time datetime
);
INSERT INTO blog (NAME, sub_time)
VALUES
('第1篇','2015-03-01 11:31:21'),
('第2篇','2015-03-11 16:31:21'),
('第3篇','2016-07-01 10:21:31'),
('第4篇','2016-07-22 09:23:21'),
('第5篇','2016-07-23 10:11:11'),
('第6篇','2016-07-25 11:21:31'),
('第7篇','2017-03-01 15:33:21'),
('第8篇','2017-03-01 17:32:21'),
('第9篇','2017-03-01 18:31:21');
select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');
9 流程控制(了解)
# if判断
delimiter //
CREATE PROCEDURE proc_if ()
BEGIN
declare i int default 0;
if i = 1 THEN
SELECT 1;
ELSEIF i = 2 THEN
SELECT 2;
ELSE
SELECT 7;
END IF;
END //
delimiter ;
# while循环
delimiter //
CREATE PROCEDURE proc_while ()
BEGIN
DECLARE num INT ;
SET num = 0 ;
WHILE num < 10 DO
SELECT
num ;
SET num = num + 1 ;
END WHILE ;
10 索引(了解)
ps:数据都是存在与硬盘上的，查询数据不可避免的需要进行IO操作
10.1 索引的定义
索引:就是一种数据结构，类似于书的目录。意味着以后在查询数据的应该先找目录再找数据，而不是一页一页的翻书，从而提升查询速度降低IO操作
索引在MySQL中也叫“键”,是存储引擎用于快速查找记录的一种数据结构
- primary key
- unique key
- index key
注意foreign key不是用来加速查询用的，不在我们的而研究范围之内
上面的三种key，前面两种除了可以增加查询速度之外各自还具有约束条件，而最后一种index key没有任何的约束条件，只是用来帮助你快速查询数据
10.2 索引的本质
通过不断的缩小想要的数据范围筛选出最终的结果，同时将随机事件(一页一页的翻)
变成顺序事件(先找目录、找数据)
也就是说有了索引机制，我们可以总是用一种固定的方式查找数据
一张表中可以有多个索引(多个目录)
索引虽然能够帮助你加快查询速度但是也有缺点
"""
1 当表中有大量数据存在的前提下 创建索引速度会很慢
2 在索引创建完毕之后 对表的查询性能会大幅度的提升 但是写的性能也会大幅度的降低
"""
索引不要随意的创建！！！
10.3 聚集索引(primary key)
聚集索引的结构是一个以主键为搜索依据的b+树
"""
聚集索引指的就是主键
Innodb 只有两个文件 直接将主键存放在了idb文件中
MyIsam 三个文件 单独将索引存在一个文件myi中
"""
10.4 b+树
"""
只有叶子节点存放的是真实的数据 其他节点存放的是虚拟数据 仅仅是用来指路的
树的层级越高查询数据所需要经历的步骤就越多(树有几层查询数据就需要几步)
一个磁盘块存储是有限制的
为什么建议你将id字段作为索引
占得空间少 一个磁盘块能够存储的数据多
那么久降低了树的高度 从而减少查询次数
"""
10.5 辅助索引(unique,index)
查询数据的时候不可能一直使用到主键，也有可能会用到name,password等其他字段
那么这个时候是没有办法利用聚集索引的。
这个时候就可以根据情况给其他字段设置辅助索引(也是一个b+树）,
通过该b+树可以得到主键值,然后再通过该主键值搜索聚集索引的b+树,得到对应的数据
"""
叶子节点存放的是数据对应的主键值
先按照辅助索引拿到数据的主键值
之后还是需要去主键的聚集索引里面查询数据
"""
10.6 覆盖索引
在辅助索引的叶子节点就已经拿到了需要的数据,无需再通过聚集索引的b+树获取数据,
这样的辅助索引称为覆盖索引
# 给name设置辅助索引
select name from user where name='jason';
# 非覆盖索引
select age from user where name='jason';
10.7 测试索引是否有效
感兴趣就试一试
#1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);
#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
declare i int default 1;
while(i<3000000)do
insert into s1 values(i,'jason','male',concat('jason',i,'@oldboy'));
set i=i+1;
end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号
#3. 查看存储过程
show create procedure auto_insert1\G
#4. 调用存储过程
call auto_insert1();
# 表没有任何索引的情况下
select * from s1 where id=30000; # 1.4s
# 避免打印带来的时间损耗
select count(id) from s1 where id = 30000; # 1.4s
select count(id) from s1 where id = 1; # 1.4s
# 给id做一个主键
alter table s1 add primary key(id); # 速度很慢 7.1s
select count(id) from s1 where id = 1; # 速度相较于未建索引之前两者差着数量级 0s
select count(id) from s1 where name = 'jason' # 速度仍然很慢 0.7s
"""
范围问题
"""
# 并不是加了索引，以后查询的时候按照这个字段速度就一定快
select count(id) from s1 where id > 1; # 速度相较于id = 1慢了很多0.653s
select count(id) from s1 where id >1 and id < 3; # 0.001s
select count(id) from s1 where id > 1 and id < 10000; # 0.007s
select count(id) from s1 where id != 3; # 0.657s
alter table s1 drop primary key; # 删除主键 单独再来研究name字段7.884s
select count(id) from s1 where name = 'jason'; # 又慢了1.167s
create index idx_name on s1(name); # 给s1表的name字段创建索引6.284s
select count(id) from s1 where name = 'jason' # 仍然很慢！！！4s
"""
再来看b+树的原理，数据需要区分度比较高，而我们这张表全是jason，根本无法区分
那这个树其实就建成了“一根棍子”
"""
select count(id) from s1 where name = 'xxx'; # 0s
# 这个会很快，我就是一根棍，第一个不匹配直接不需要再往下走了
select count(id) from s1 where name like 'xxx'; # 0s
select count(id) from s1 where name like 'xxx%'; # 0s
select count(id) from s1 where name like '%xxx'; # 慢 最左匹配特性 1.352s
# 区分度低的字段不能建索引
drop index idx_name on s1;
# 给id字段建普通的索引
create index idx_id on s1(id); # 4.404s
select count(id) from s1 where id = 3; # 快了 0s
select count(id) from s1 where id*12 = 3; # 慢了 索引的字段一定不要参与计算0.671s
drop index idx_id on s1;
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx'; # 1.41s
# 针对上面这种连续多个and的操作，mysql会从左到右先找区分度比较高的索引字段，先将整体范围降下来再去比较其他条件
create index idx_name on s1(name); # 6.559s
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx'; # 并没有加速 4.895s
drop index idx_name on s1;
# 给name，gender这种区分度不高的字段加上索引并不能加快查询速度
create index idx_id on s1(id); # 4.625s
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx'; # 快了 先通过id已经讲数据快速锁定成了一条了0.001s
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 慢了 基于id查出来的数据仍然很多，然后还要去比较其他字段1.949s
drop index idx_id on s1;
create index idx_email on s1(email); # 8.029s
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 快 通过email字段一剑封喉 0.004s
# 联合索引
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 0s
# 如果上述四个字段区分度都很高，那给谁建都能加速查询
# 给email加然而不用email字段
select count(id) from s1 where name='jason' and gender = 'male' and id > 3; # 1.65s
# 给name加然而不用name字段
select count(id) from s1 where gender = 'male' and id > 3; # 1.416s
# 给gender加然而不用gender字段
select count(id) from s1 where id > 3; # 0.976s
# 带来的问题是所有的字段都建了索引然而都没有用到，还需要花费四次建立的时间
create index idx_all on s1(email,name,gender,id); # 最左匹配原则，区分度高的往左放9.734s
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 速度变快0.005s
总结:
搜索范围大，耗时长
识别度低的索引：耗时比无该索引更长
慢查询日志
设定一个时间检测所有超出该时间的sql语句，然后针对性的进行优化！
posted on
