VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > Python基础教程 >

  • 数据库（内置函数、流程控制、索引）

10.17 内置函数

强调：mysql内置的函数只能在sql语句中使用

复制代码
#数学函数
round(x,y)      #返回参数x的四舍五入的有y位小数的值
rand()          #返回０到１内的随机值,可以通过提供一个参数(种子)使rand()随机数生成器生成一个指定的值。
​
#加密函数
md5()           #计算字符串str的MD5校验和
password(str)   #返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。
复制代码

date_format:

复制代码
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篇','2017-05-01 18:31:21');
select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');
+-------------------------------+-----------+
| date_format(sub_time,'%Y-%m') | count(id) |
+-------------------------------+-----------+
| 2015-03                       |         2 |
| 2017-05                       |         1 |
+-------------------------------+-----------+
复制代码

10.171 自定义函数

函数中不要写sql语句（否则会报错），函数仅仅只是一个功能，是一个在sql中被应用的功能,若要想在begin...end...中写sql，请用存储过程.

复制代码
delimiter //
create function f1(
    i1 int,
    i2 int)
returns int
BEGIN
    declare num int;
    set num = i1 + i2;
    return(num);
END //
delimiter ;
复制代码

删除函数：

drop function func_name;

执行函数：

复制代码
# 获取返回值
select UPPER('egon') into @res;
SELECT @res;
+------+
| @res |
+------+
| EGON |
+------+
# 在查询中使用
select f1(11,nid) ,name from tb2;
复制代码

10.18 流程控制

if条件语句:

复制代码
delimiter //
create function f5(i int)
returns int
begin
    declare res int default 0;
    if i = 10 then
        set res=100;
    elseif i = 20 then
        set res=200;
    elseif i = 30 then
        set res=300;
    else
        set res=400;
    end if;
    return res;
end //
delimiter ;
复制代码

控制流函数：

复制代码
select 
case
    when name = 'egon' then name
    when name = 'alex' then concat(name,'_BIGSB')
    else concat(name,'_SB')
end
from emp;
复制代码

循环语句:

复制代码
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 ;
复制代码

10.19 索引

索引就相当于书的目录，是mysql中一种专门的数据结构，称为key， 索引的本质原理就是通过不断地缩小查询范围，来降低 i/o 次数从而提升查询性能，一旦为表创建了索引，以后的查询都会先查索引，再根据索引定位的结果去找数据

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

聚集索引(primary key)：就是按照每张表的主键构造一棵B+树，同时叶子结点存放的即为整张表的行记录数据，也将聚集索引的叶子结点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。特点：叶子节点存放的一整条数据

聚集索引的优点：1.它对主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表，查询最后的10位用户信息，由于B+树索引是双向链表，所以用户可以快速找到最后一个数据页，并取出10条记录 2.范围查询(range query),即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可

辅助索引(unique，index):非聚集索引即从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。特点：辅助索引的叶子节点不包含行记录的全部数据。如果是按照name字段创建的索引，那么叶子节点存放的是：{name：名字所在那条记录的主键的值}

每张表上可以有多个辅助索引，但只能有一个聚集索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶子级别的指针获得主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

使用覆盖索引的一个好处是：辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作,InnoDB存储引擎支持覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。

#覆盖索引：在辅助索引的叶子节点中就已经找到了所有我们想要的数据
select name from user where name='egon';
#非覆盖索引：命中辅助索引，但是未覆盖索引，还需要从聚集索引中查找age
select age from user where name='egon';

联合索引:联合索引指对表上的多个列合起来做一个索引,在第一个键相同的情况下，已经对第二个键进行了排序处理

mysql中的primary key，unique，index ,联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能

复制代码
普通索引 index：加速查找
唯一索引：
    -主键索引 primary key：加速查找+约束（not null  + unique）
    -唯一索引 unique:加速查找+约束（不能重复）
联合索引：
    -primary key(id,name):联合主键索引，加速查找+约束
    -unique(id,name):联合唯一索引，加速查找+约束
    -index(id,name):联合普通索引
复制代码

innodb:聚集索引，辅助索引 myisam:辅助索引

10.191 创建和删除索引

复制代码
#方式一
create table t1(
    id int,
    name char,
    age int,
    sex enum('male','female'),
    unique key uni_id(id),
    index ix_name(name)         #index没有key
);
#方式二
create index ix_age on t1(age);
#方式三
alter table t1 add index ix_sex(sex);
alter table t1 add primary key(id);
#联合索引
alter table t1 add primary key(id，name);
create index ix_age on t1(id，age);
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  | UNI | NULL    |       |
| name  | char(1)               | YES  | MUL | NULL    |       |
| age   | int(11)               | YES  | MUL | NULL    |       |
| sex   | enum('male','female') | YES  | MUL | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
#删除索引：
drop index idx_name on t1;
alter table t1 drop primary key;
​
and工作原理：对于连续多个and：mysql会按照联合索引，从左到右的顺序找一个区分度高的索引字段(这样便可以快速锁定很小的范围)，加速查询
or的工作原理：对于连续多个or：mysql会按照条件的顺序，从左到右依次判断,此时应该按照区分度高低设置联合索引
复制代码

10.192 正确使用索引

10.1921 索引未命中

并不是说我们创建了索引就一定会加快查询速度，若想利用索引达到预想的提高查询速度的效果，我们在添加索引时，必须遵循以下问题

 索引未命中
10.1922 其他注意事项
复制代码
1 避免使用select *
2 count(1)或count(列) 代替 count(*)
3 创建表时尽量时 char 代替 varchar
4 表的字段顺序固定长度的字段优先
5 组合索引代替多个单列索引（经常使用多个条件查询时）
6 尽量使用短索引
7 使用连接（JOIN）来代替子查询(Sub-Queries)
8 连表时注意条件类型需一致
9 索引散列值不适合建索引，例：性别不适合
复制代码

总结：

  1. 一定是为搜索条件的字段创建索引，比如select * from s1 where id = 333;就需要为id加上索引

  2. 在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，建完后查询速度加快

  3. 需要注意的是：innodb表的索引会存放于s1.ibd文件中，而myisam表的索引则会有单独的索引文件table1.MYI

10.193 慢查询优化的基本步骤

复制代码
0.先运行看看是否真的很慢，注意设置SQL_NO_CACHE
1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
2.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
3.order by limit 形式的sql语句让排序的表优先查
4.了解业务方使用场景
5.加索引时参照建索引的几大原则
6.观察结果，不符合预期继续从0分析
复制代码

10.194 慢日志管理

复制代码
 慢日志
            - 执行时间 > 10
            - 未命中索引
            - 日志文件路径
            
        配置：
            - 内存
                show variables like '%query%';
                show variables like '%queries%';
                set global 变量名 = 值
            - 配置文件
                mysqld --defaults-file='E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\my-default.ini'
                
                my.conf内容：
                    slow_query_log = ON
                    slow_query_log_file = D:/....
                    
                注意：修改配置文件之后，需要重启服务
复制代码
复制代码
MySQL日志管理
========================================================
错误日志: 记录 MySQL 服务器启动、关闭及运行错误等信息
二进制日志: 又称binlog日志，以二进制文件的方式记录数据库中除 SELECT 以外的操作
查询日志: 记录查询的信息
慢查询日志: 记录执行时间超过指定时间的操作
中继日志： 备库将主库的二进制日志复制到自己的中继日志中，从而在本地进行重放
通用日志： 审计哪个账号、在哪个时段、做了哪些事件
事务日志或称redo日志： 记录Innodb事务相关的如事务执行时间、检查点等
========================================================
一、bin-log
1. 启用
# vim /etc/my.cnf
[mysqld]
log-bin[=dir\[filename]]
# service mysqld restart
2. 暂停
//仅当前会话
SET SQL_LOG_BIN=0;
SET SQL_LOG_BIN=1;
3. 查看
查看全部：
# mysqlbinlog mysql.000002
按时间：
# mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56"
# mysqlbinlog mysql.000002 --stop-datetime="2012-12-05 11:02:54"
# mysqlbinlog mysql.000002 --start-datetime="2012-12-05 10:02:56" --stop-datetime="2012-12-05 11:02:54" 

按字节数：
# mysqlbinlog mysql.000002 --start-position=260
# mysqlbinlog mysql.000002 --stop-position=260
# mysqlbinlog mysql.000002 --start-position=260 --stop-position=930
4. 截断bin-log（产生新的bin-log文件）
a. 重启mysql服务器
b. # mysql -uroot -p123 -e 'flush logs'
5. 删除bin-log文件
# mysql -uroot -p123 -e 'reset master' 


二、查询日志
启用通用查询日志
# vim /etc/my.cnf
[mysqld]
log[=dir\[filename]]
# service mysqld restart

三、慢查询日志
启用慢查询日志
# vim /etc/my.cnf
[mysqld]
log-slow-queries[=dir\[filename]]
long_query_time=n
# service mysqld restart
MySQL 5.6:
slow-query-log=1
slow-query-log-file=slow.log
long_query_time=3
查看慢查询日志
测试:BENCHMARK(count,expr)
SELECT BENCHMARK(50000000,2*3);
复制代码


相关教程