一视图
二触发器
三事务
四存储过程
五函数
7.1、准备表
六流程控制
while循环
七索引与慢查询优化
- 测试索引
  - - 联合索引
数据库的三大设计范式

一视图

1 什么是视图？

视图是一个虚拟表（非真实存在），其本质是 根据SQL语句获取动态的数据集，并为其命名，然后保存下来，下次直接使用即可

2 为什么要使用视图？

如果要频繁使用一张表，使用视图我们可以把查询过程中的临时表摘出来，

这样以后再想使用这张表的数据时就无需重写复杂的sql了，直接去视图中查找即可

3 如何使用视图

create view teacher2course as
select * from teacher inner join course on teacher.tid = course.teacher_id;

但视图有明显地效率问题，并且视图是存放在数据库中的

如果我们程序中使用的sql过分依赖数据库中的视图，即强耦合，那就意味着扩展sql极为不便，因此并不推荐使用

使用视图以后就无需每次都重写子查询的sql，但是这么效率并不高，还不如我们写子查询的效率高

而且有一个致命的问题：视图是存放到数据库里的，

如果我们程序中的sql过分依赖于数据库中存放的视图，那么意味着，一旦sql需要修改且涉及到视图的部分，则必须去数据库中进行修改，

而通常在公司中数据库有专门的DBA负责，你要想完成修改，必须付出大量的沟通成本DBA可能才会帮你完成修改，极其地不方便

4 使用视图

修改视图，原始表也跟着改

mysql> update course_view set cname='xxx'; 更新视图中的数据
Query OK, 4 rows affected (0.04 sec)
Rows matched: 4 Changed: 4 Warnings: 0

mysql> insert into course_view values(5,'yyy',2); 往视图中插入数据
Query OK, 1 row affected (0.03 sec)

原始表的记录也跟着修改了**

我们不应该修改视图中的记录，而且在涉及多个表的情况下是根本无法修改视图中的记录的

5 修改视图

语法：ALTER VIEW 视图名称 AS SQL语句
mysql> alter view teacher_view as select * from course where cid>3;
Query OK, 0 rows affected (0.04 sec)

4 删除视图

语法： DROP VIEW 视图名称

DROP VIEW teacher_view

强调
1、在硬盘中，视图只有表结构文件，没有表数据文件
2、视图通常是用于查询，尽量不要修改视图中的数据

二触发器

1 什么是触发器？

使用触发器可以定制用户对表进行 增，删，改 操作时前后的行为，注意：没有查询

2 为什么要用触发器？

触发器专门针对我们对某一张表数据增insert、删delete、改update的行为，这类行为一旦执行就会触发触发器的执行，即自动运行另外一段sql代码

1 创建触发器

插入前

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 $$ # 将mysql默认的结束符由;换成$$
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
if NEW.success = 'no' then # 新记录都会被MySQL封装成NEW对象
insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
end if;
end $$
delimiter ; # 结束之后记得再改回来，不然后面结束符就都是$$了

往表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');

查询errlog表记录

select * from errlog;

删除触发器

drop trigger tri_after_insert_cmd;

三事务

1 什么是事务？

事务用于将某些操作的多个SQL作为原子性操作，

一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。

2 事务的作用?

保证了对数据操作的安全性

案例：用交通银行的卡操作上海银行的ATM机给中国银行转钱

3 ACID特性

原子性（Atomicity）：一个事务是一个不可分割的工作单位，事务中的操作要么全部执行，要么全都不执行，不能只完成部分操作

一致性（Consistency）：事务开始之前，数据库处于一致性的状态；事务结束后，数据库必须仍处于一致性状态。一致性与原子性是密切相关的。数据库一致性的定义是由用户负责的。例如，在银行转账中，用户可以定义转账前后两个账户金额之和保持不变。

隔离性（Isolation）：系统必须保证事务不受其他并发执行事务的影响，即当多个事务同时运行时，各事务之间相互隔离，不可互相干扰

持续性（Durability）：指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响

这 4 个特性简称为 ACID 特性。

4 使用

create table user(
id int primary key auto_increment,
name char(32),
balance int
);

insert into user(name,balance)
values
('wsb',1000),
('egon',1000),
('ysb',1000);

开始事务;

start transaction;

修改操作

update user set balance=900 where name='wsb'; #买支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
update user set balance=1090 where name='ysb'; #卖家拿到90元

回滚到上一个状态（撤销任务）

rollback;

开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘

提交任务

commit;
开启事务检测操作是否完整，不完整主动回滚到上一个状态，如果完整就应该执行commit操作

站在python代码的角度，应该实现的伪代码逻辑，

try:
update user set balance=900 where name='wsb'; #买支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
update user set balance=1090 where name='ysb'; #卖家拿到90元
except 异常:
rollback;
else:
commit;

四存储过程

存储过程（自定义函数）包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql

三种开发模式

第一种

应用程序：只需要开发应用程序的逻辑
mysql:编写好存储过程，以供应用程序调用
优点：开发效率，执行效率都高
缺点：考虑到人为因素、跨部门沟通等问题，会导致扩展性差

第二种

应用程序：除了开发应用程序的逻辑，还需要编写原生sql
优点：比方式1，扩展性高(非技术性的)
缺点：
1、开发效率，执行效率都不如方式1
2、编写原生sql太过于复杂，而且需要考虑到sql语句的优化问题

第三种

应用程序：开发应用程序的逻辑，不需要编写原生sql，基于别人编写好的框架来处理数据，ORM
优点：不用再编写纯生sql，这意味着开发效率比方式2高，同时兼容方式2扩展性高的好处
缺点：执行效率连方式2都比不过

创建存储过程（无参）

delimiter //
create procedure p1()
BEGIN
select * from blog;
INSERT into blog(name,sub_time) values("xxx",now());
END //
delimiter ;

在mysql中调用
call p1()

在python中基于pymysql调用
cursor.callproc('p1')
print(cursor.fetchall())

创建存储过程（有参）

delimiter $$
create procedure p1(
in m int, # in表示这个参数必须只能是传入不能被返回出去
in n int,
out res int # out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
)
begin
select tname from teacher where tid > m and tid < n;
set res=0;
end $$
delimiter ;

小知识点补充，当一张表的字段特别多记录也很多的情况下，终端下显示出来会出现显示错乱的问题

select * from mysql.user\G;

如何用存储过程

大前提:存储过程在哪个库下面创建的只能在对应的库下面才能使用！！！

1、直接在mysql中调用

set @res=10 # res的值是用来判断存储过程是否被执行成功的依据，所以需要先定义一个变量@res存储10
call p1(2,4,10); # 报错
call p1(2,4,@res);

查看结果

select @res; # 执行成功，@res变量值发生了变化

2、在python程序中调用

pymysql链接mysql
产生的游表cursor.callproc('p1',(2,4,10)) # 内部原理：@_p1_0=2,@_p1_1=4,@_p1_2=10;
cursor.excute('select @_p1_2;')

3、存储过程与事务使用举例(了解)

delimiter //
create PROCEDURE p5(
OUT p_return_code tinyint
)
BEGIN
DECLARE exit handler for sqlexception
BEGIN
-– ERROR
set p_return_code = 1;
rollback;
END;

DECLARE exit handler for sqlwarning
BEGIN
-– WARNING
set p_return_code = 2;
rollback;
END;

START TRANSACTION;
update user set balance=900 where id =1;
update user123 set balance=1010 where id = 2;
update user set balance=1090 where id =3;
COMMIT;

-– SUCCESS
set p_return_code = 0; #0代表执行成功

END //
delimiter ;

五函数

注意与存储过程(自定义函数）的区别

mysql内置的函数只能在sql语句中使用！

MySQL中提供了许多内置函数

1 数学函数
ROUND(x,y)
返回参数x的四舍五入的有y位小数的值

RAND()
返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。

2 聚合函数(常用于GROUP BY从句的SELECT查询中)
AVG(col)返回指定列的平均值
COUNT(col)返回指定列中非NULL值的个数
MIN(col)返回指定列的最小值
MAX(col)返回指定列的最大值
SUM(col)返回指定列的所有值之和
GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果

3 字符串函数

CHAR_LENGTH(str)
    返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。
CONCAT(str1,str2,...)
    字符串拼接
    如有任何一个参数为NULL ，则返回值为 NULL。
CONCAT_WS(separator,str1,str2,...)
    字符串拼接（自定义连接符）
    CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。

CONV(N,from_base,to_base)
    进制转换
    例如：
        SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示

FORMAT(X,D)
    将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若  D 为 0, 则返回结果不带有小数点，或不含小数部分。
    例如：
        SELECT FORMAT(12332.1,4); 结果为： '12,332.1000'
INSERT(str,pos,len,newstr)
    在str的指定位置插入字符串
        pos：要替换位置其实位置
        len：替换的长度
        newstr：新字符串
    特别的：
        如果pos超过原字符串长度，则返回原字符串
        如果len超过原字符串长度，则由新字符串完全替换
INSTR(str,substr)
    返回字符串 str 中子字符串的第一个出现位置。

LEFT(str,len)
    返回字符串str 从开始的len位置的子序列字符。

LOWER(str)
    变小写

UPPER(str)
    变大写
   
REVERSE(str)
    返回字符串 str ，顺序和字符顺序相反。
    
SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
    不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。

    mysql> SELECT SUBSTRING('Quadratically',5);
        -> 'ratically'

    mysql> SELECT SUBSTRING('foobarbar' FROM 4);
        -> 'barbar'

    mysql> SELECT SUBSTRING('Quadratically',5,6);
        -> 'ratica'

    mysql> SELECT SUBSTRING('Sakila', -3);
        -> 'ila'

    mysql> SELECT SUBSTRING('Sakila', -5, 3);
        -> 'aki'

    mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
        -> 'ki'

4 日期和时间函数
CURDATE()或CURRENT_DATE() 返回当前的日期
CURTIME()或CURRENT_TIME() 返回当前的时间
DAYOFWEEK(date) 返回date所代表的一星期中的第几天(1~7)
DAYOFMONTH(date) 返回date是一个月的第几天(1~31)
DAYOFYEAR(date) 返回date是一年的第几天(1~366)
DAYNAME(date) 返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE);
FROM_UNIXTIME(ts,fmt) 根据指定的fmt格式，格式化UNIX时间戳ts
HOUR(time) 返回time的小时值(0~23)
MINUTE(time) 返回time的分钟值(0~59)
MONTH(date) 返回date的月份值(1~12)
MONTHNAME(date) 返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE);
NOW() 返回当前的日期和时间
QUARTER(date) 返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);
WEEK(date) 返回日期date为一年中第几周(0~53)
YEAR(date) 返回日期date的年份(1000~9999)

重点:
DATE_FORMAT(date,format) 根据format字符串格式化date值

   mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
    -> 'Sunday October 2009'
   mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
    -> '22:23:00'
   mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
    ->                 '%D %y %a %d %m %b %j');
    -> '4th 00 Thu 04 10 Oct 277'
   mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
    ->                 '%H %k %I %r %T %S %w');
    -> '22 22 10 10:23:00 PM 22:23:00 00 6'
   mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
    -> '1998 52'
   mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
    -> '00'

5 加密函数
MD5()
计算字符串str的MD5校验和
PASSWORD(str)
返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。

6 控制流函数
CASE WHEN[test1] THEN [result1]..…ELSE [default] END
如果testN是真，则返回resultN，否则返回default
CASE [test] WHEN[val1] THEN [result]..…ELSE [default]END
如果test和valN相等，则返回resultN，否则返回default

IF(test,t,f)   
    如果test是真，返回t；否则返回f

IFNULL(arg1,arg2) 
    如果arg1不是空，返回arg1，否则返回arg2

NULLIF(arg1,arg2) 
    如果arg1=arg2返回NULL；否则返回arg1

7 控制流函数小练习

7.1、准备表

/*
Navicat MySQL Data Transfer

Source Server : localhost_3306
Source Server Version : 50720
Source Host : localhost:3306
Source Database : student

Target Server Type : MYSQL
Target Server Version : 50720
File Encoding : 65001

Date: 2018-01-02 12:05:30
*/

SET FOREIGN_KEY_CHECKS=0;

-– Table structure for course

DROP TABLE IF EXISTS course;
CREATE TABLE course (
c_id int(11) NOT NULL,
c_name varchar(255) DEFAULT NULL,
t_id int(11) DEFAULT NULL,
PRIMARY KEY (c_id),
KEY t_id (t_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-– Records of course

INSERT INTO course VALUES ('1', 'python', '1');
INSERT INTO course VALUES ('2', 'java', '2');
INSERT INTO course VALUES ('3', 'linux', '3');
INSERT INTO course VALUES ('4', 'web', '2');

-– Table structure for score

DROP TABLE IF EXISTS score;
CREATE TABLE score (
id int(11) NOT NULL AUTO_INCREMENT,
s_id int(10) DEFAULT NULL,
c_id int(11) DEFAULT NULL,
num double DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

-– Records of score

INSERT INTO score VALUES ('1', '1', '1', '79');
INSERT INTO score VALUES ('2', '1', '2', '78');
INSERT INTO score VALUES ('3', '1', '3', '35');
INSERT INTO score VALUES ('4', '2', '2', '32');
INSERT INTO score VALUES ('5', '3', '1', '66');
INSERT INTO score VALUES ('6', '4', '2', '77');
INSERT INTO score VALUES ('7', '4', '1', '68');
INSERT INTO score VALUES ('8', '5', '1', '66');
INSERT INTO score VALUES ('9', '2', '1', '69');
INSERT INTO score VALUES ('10', '4', '4', '75');
INSERT INTO score VALUES ('11', '5', '4', '66.7');

-– Table structure for student

DROP TABLE IF EXISTS student;
CREATE TABLE student (
s_id varchar(20) NOT NULL,
s_name varchar(255) DEFAULT NULL,
s_age int(10) DEFAULT NULL,
s_sex char(1) DEFAULT NULL,
PRIMARY KEY (s_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-– Records of student

INSERT INTO student VALUES ('1', '鲁班', '12', '男');
INSERT INTO student VALUES ('2', '貂蝉', '20', '女');
INSERT INTO student VALUES ('3', '刘备', '35', '男');
INSERT INTO student VALUES ('4', '关羽', '34', '男');
INSERT INTO student VALUES ('5', '张飞', '33', '女');

-– Table structure for teacher

DROP TABLE IF EXISTS teacher;
CREATE TABLE teacher (
t_id int(10) NOT NULL,
t_name varchar(50) DEFAULT NULL,
PRIMARY KEY (t_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-– Records of teacher

INSERT INTO teacher VALUES ('1', '大王');
INSERT INTO teacher VALUES ('2', 'alex');
INSERT INTO teacher VALUES ('3', 'egon');
INSERT INTO teacher VALUES ('4', 'peiqi');

7.2、统计各科各分数段人数.显示格式:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

select score.c_id,
course.c_name,
sum(CASE WHEN num BETWEEN 85 and 100 THEN 1 ELSE 0 END) as '[100-85]',
sum(CASE WHEN num BETWEEN 70 and 85 THEN 1 ELSE 0 END) as '[85-70]',
sum(CASE WHEN num BETWEEN 60 and 70 THEN 1 ELSE 0 END) as '[70-60]',
sum(CASE WHEN num < 60 THEN 1 ELSE 0 END) as '[ <60]'
from score,course where score.c_id=course.c_id GROUP BY score.c_id;

六流程控制

1 条件语句

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 ;

2 循环语句

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 ;
END //
delimiter ;

七索引与慢查询优化

知识回顾:数据都是存在硬盘上的，那查询数据不可避免的需要进行IO操作

索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。

primary key
unique key
index key

注意foreign key不是用来加速查询用的，不在我们研究范围之内,上面三种key前两种除了有加速查询的效果之外还有额外的约束条件(primary key:非空且唯一，unique key:唯一)，而index key没有任何约束功能只会帮你加速查询

索引就是一种数据结构，类似于书的目录。意味着以后再查数据应该先找目录再找数据，而不是用翻页的方式查询数据

本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

索引的影响:

在表中有大量数据的前提下，创建索引速度会很慢
在索引创建完毕后，对表的查询性能会大幅度提升，但是写的性能会降低

b+树

只有叶子结点存放真实数据，根和树枝节点存的仅仅是虚拟数据

查询次数由树的层级决定，层级越低次数越少

一个磁盘块儿的大小是一定的，那也就意味着能存的数据量是一定的。如何保证树的层级最低呢？一个磁盘块儿存放占用空间比较小的数据项

思考我们应该给我们一张表里面的什么字段字段建立索引能够降低树的层级高度>>> 主键id字段

聚集索引(primary key)

聚集索引其实指的就是表的主键，innodb引擎规定一张表中必须要有主键。先来回顾一下存储引擎。

myisam在建表的时候对应到硬盘有几个文件(三个)？

innodb在建表的时候对应到硬盘有几个文件(两个)？frm文件只存放表结构，不可能放索引，也就意味着innodb的索引跟数据都放在idb表数据文件中。

特点:叶子结点放的一条条完整的记录

辅助索引(unique,index)

辅助索引:查询数据的时候不可能都是用id作为筛选条件，也可能会用name，password等字段信息，那么这个时候就无法利用到聚集索引的加速查询效果。就需要给其他字段建立索引，这些索引就叫辅助索引

特点：叶子结点存放的是辅助索引字段对应的那条记录的主键的值(比如:按照name字段创建索引，那么叶子节点存放的是:{name对应的值:name所在的那条记录的主键值})

select name from user where name='jason';

上述语句叫覆盖索引:只在辅助索引的叶子节点中就已经找到了所有我们想要的数据

select age from user where name='jason';

上述语句叫非覆盖索引,虽然查询的时候命中了索引字段name，但是要查的是age字段，所以还需要利用主键才去查找

测试索引

准备

#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;
# 避免打印带来的时间损耗
select count(id) from s1 where id = 30000;
select count(id) from s1 where id = 1;

# 给id做一个主键
alter table s1 add primary key(id);  # 速度很慢

select count(id) from s1 where id = 1;  # 速度相较于未建索引之前两者差着数量级
select count(id) from s1 where name = 'jason'  # 速度仍然很慢


"""
范围问题
"""
# 并不是加了索引，以后查询的时候按照这个字段速度就一定快   
select count(id) from s1 where id > 1;  # 速度相较于id = 1慢了很多
select count(id) from s1 where id >1 and id < 3;
select count(id) from s1 where id > 1 and id < 10000;
select count(id) from s1 where id != 3;

alter table s1 drop primary key;  # 删除主键 单独再来研究name字段
select count(id) from s1 where name = 'jason';  # 又慢了

create index idx_name on s1(name);  # 给s1表的name字段创建索引
select count(id) from s1 where name = 'jason'  # 仍然很慢！！！
"""
再来看b+树的原理，数据需要区分度比较高，而我们这张表全是jason，根本无法区分
那这个树其实就建成了“一根棍子”
"""
select count(id) from s1 where name = 'xxx';  
# 这个会很快，我就是一根棍，第一个不匹配直接不需要再往下走了
select count(id) from s1 where name like 'xxx';
select count(id) from s1 where name like 'xxx%';
select count(id) from s1 where name like '%xxx';  # 慢 最左匹配特性

# 区分度低的字段不能建索引
drop index idx_name on s1;

# 给id字段建普通的索引
create index idx_id on s1(id);
select count(id) from s1 where id = 3;  # 快了
select count(id) from s1 where id*12 = 3;  # 慢了  索引的字段一定不要参与计算

drop index idx_id on s1;
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';
# 针对上面这种连续多个and的操作，mysql会从左到右先找区分度比较高的索引字段，先将整体范围降下来再去比较其他条件
create index idx_name on s1(name);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  # 并没有加速

drop index idx_name on s1;
# 给name，gender这种区分度不高的字段加上索引并不难加快查询速度

create index idx_id on s1(id);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  # 快了  先通过id已经讲数据快速锁定成了一条了
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 慢了  基于id查出来的数据仍然很多，然后还要去比较其他字段

drop index idx_id on s1

create index idx_email on s1(email);
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 快 通过email字段一剑封喉

联合索引

select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  
# 如果上述四个字段区分度都很高，那给谁建都能加速查询
# 给email加然而不用email字段
select count(id) from s1 where name='jason' and gender = 'male' and id > 3; 
# 给name加然而不用name字段
select count(id) from s1 where gender = 'male' and id > 3; 
# 给gender加然而不用gender字段
select count(id) from s1 where id > 3; 

# 带来的问题是所有的字段都建了索引然而都没有用到，还需要花费四次建立的时间
create index idx_all on s1(email,name,gender,id);  # 最左匹配原则，区分度高的往左放
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 速度变快

总结:上面这些操作，你感兴趣可以敲一敲，不感兴趣你就可以不用敲了，权当看个乐呵。理论掌握了就行了

慢查询日志

10s

设定一个时间检测所有超出该时间的sql语句，然后针对性的进行优化！

数据库的三大设计范式

第一范式

如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

每一列属性都是不可再分的属性值，确保每一列的原子性

第二示范

第二范式(确保表中的每列都和主键相关)

第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

第三范式

数据不能存在传递关系，即没个属性都跟主键有直接关系而不是间接关系。像：a-–>b-–>c 属性之间含有这样的关系，是不符合第三范式的。

比如Student表（学号，姓名，年龄，性别，所在院校，院校地址，院校电话）

这样一个表结构，就存在上述关系。学号-–> 所在院校 -–> (院校地址，院校电话)

这样的表结构，我们应该拆开来，如下。

（学号，姓名，年龄，性别，所在院校）-–（所在院校，院校地址，院校电话）

posted on 2019-12-17 18:21 Rannie` 阅读(177) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

一 视图

二 触发器

三 事务

四 存储过程

五 函数