day37

上节回顾

数据库操作
数据表
- 数据类型
- 外键
  - 一对多
  - 多对多
  - 一对一
- 自增
- 唯一索引
数据行
- 排序：order by desc/asc
- 分组：group by
- 条件：where
- 链表：
  - left join
  - right join
  - inner join
- 临时表
- 通配符
- 分页：limit
- 组合：
  - union
视图
触发器
函数
存储过程
- 游标
- 事务
pymysql
- 存储过程的调用方式
- commit
- select

链接别人的mysqla

mysql -u root -h 10.10.100.3 -p

今日内容

1.索引

1.1作用

约束
加速查找

1.2索引

普通索引
- 加速查找
主键索引
- 加速查找，不能为空，不能重复
唯一索引
- 加速查找，不能重复
联合索引（多列）
- 联合主键索引
- 联合唯一索引
- 联合普通索引
加速查找
- select * from tb where name= "asdf"

1.2.1hash索引

ha xi
hash索引是建立一个索引表，把数据转化成指定的个是储存，后面加上硬盘地址，hash保存是没有顺序的。你查文件时它会把硬盘地址直接给你。
范围查找速度会慢

1.2.2btree索引

bi chui
btree是以中值分为金字塔排的，小于中值左边，大于中值右边，依次往下排。
- 例如：100*2=50，小于50左，大于50右。50 *2=25，小于25左，大于25右。
- 寻找次数 2**10，寻找次数10次
- 二叉树

1.2.3建立索引

额外的文件保存特殊的数据结构

1.普通索引

普通索引仅有一个功能：加速查询

创建表 + 索引

create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    index ix_name (name)
)

创建索引

create index 名字 on 表文件名 (字段)

删除索引
- ```
drop index 名字 on 表文件名
```
查看索引
- ```
show index from table_name;
```
- 注意：对于创建索引时如果是blob和text 类型，必须指定length
  - ```
  create index ix_extra on in1(extra(32));
```

2.唯一索引

唯一索引有两个功能：加速查找和唯一约束（可含null）

创建表 + 唯一索引

create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    unique ix_name (name)
)
?

创建索引

create unique index 名字 on 表文件名 (字段)

删除索引

drop unique index 名字 on 表文件名

3.组合索引

组合索引是将n个列组合成一个索引
其应用场景为：频繁的同时使用n列来进行查询
例如：where n2 = "alex" and n3 = 666。

创建表

create table in3(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text
)

创建索引

create index 名字 on 表文件名 (字段，字段)

删除索引
- ```
drop index 名字 on 表文件名
```
查看索引
- ```
show index from table_name;
```
- 注意：对于同时搜索n个条件时，组合索引的性能好与多个单一索引合并。

组合索引遵循最左前缀匹配

create index ix_name_email on wserinfo3(name,email,ex)
select * from userinfo3 where name="alex" 
select * from userinfo3 where name="alex"and email="a" 
select * from userinfo3 where email="xxx"
1和2可以三不可以，因为三没有遵循最左前缀匹配，最左前缀是name开头，name没有开头就不行

索引合并没有组合索引效率高
索引组合是把有索引的数据行组合进行索引

4.主键索引

创建表+创建主键

create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    index ix_name (name)
)

OR

create table in1(
    nid int not null auto_increment,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    primary key(ni1),
    index ix_name (name)
)

创建主键

alter table 表名 add primary key(列名);

删除主键

alter table 表名 drop primary key;
alter table 表名  modify  列名 int, drop primary key;

5.覆盖索引

名词：不是真实的索引
覆盖索引就是创建一个email索引，显示时只显示email它就不会去数据表里找，在索引文件中直接获取数据

select email from tb1 where email = "alex@qq.com111"

6.索引合并

名词：不存在
把多个单列索引合并使用

select * from tb1 where a1 = "1" and a2 = "2"

1.3正确使用索引

数据库表中添加索引后确实会让查询速度起飞，但前提必须是正确的使用索引来查询，如果以错误的方式使用，则即使建立索引也会不奏效。即使建立索引，索引也不会生效：

like '%xx'
    select * from tb1 where name like '%cn';
 使用函数
    select * from tb1 where reverse(name) = 'wupeiqi';
 or
    select * from tb1 where nid = 1 or email = 'seven@live.com';
    特别的：当or条件中有未建立索引的列才失效，以下会走索引
            select * from tb1 where nid = 1 or name = 'seven';
            select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex'
 类型不一致
    如果列是字符串类型，传入条件是必须用引号引起来，不然...
    select * from tb1 where name = 999;
 !=
    select * from tb1 where name != 'alex'
    特别的：如果是主键，则还是会走索引
        select * from tb1 where nid != 123
 >
    select * from tb1 where name > 'alex'
    特别的：如果是主键或索引是整数类型，则还是会走索引
        select * from tb1 where nid > 123
        select * from tb1 where num > 123
 order by
    select email from tb1 order by name desc;
    当根据索引排序时候，选择的映射如果不是索引，则不走索引
    特别的：如果对主键排序，则还是走索引：
        select * from tb1 order by nid desc;
 
 组合索引最左前缀
    如果组合索引为：(name,email)
    name and email       -- 使用索引
    name                 -- 使用索引
    email                -- 不使用索引

1.4其他注意事项

避免使用select *
count(1)或count(列) 代替 count(*)
创建表时尽量时 char 代替 varchar
表的字段顺序固定长度的字段优先
组合索引代替多个单列索引（经常使用多个条件查询时）
尽量使用短索引
使用连接（JOIN）来代替子查询(Sub-Queries)
连表时注意条件类型需一致
索引散列值（重复少）不适合建索引，例：性别不适合

2.ORM框架—sqlaichemy

用类和对象对数据库操作 a ke mi

3.其他

3.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 ;

3.2循环语句

3.2.1while循环

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 ;

3.2.2repeat循环

delimiter \\
CREATE PROCEDURE proc_repeat ()
BEGIN

    DECLARE i INT ;
    SET i = 0 ;
    repeat
        select i;
        set i = i + 1;
        until i >= 5
    end repeat;

END\\
delimiter ;

3.2.3loop

BEGIN
    
    declare i int default 0;
    loop_label: loop
        
        set i=i+1;
        if i<8 then
            iterate loop_label;
        end if;
        if i>=10 then
            leave loop_label;
        end if;
        select i;
    end loop loop_label;

END

3.3动态执行sql语句

delimiter \\
DROP PROCEDURE IF EXISTS proc_sql \\
CREATE PROCEDURE proc_sql ()
BEGIN
    declare p1 int;
    set p1 = 11;
    set @p1 = p1;

    PREPARE prod FROM 'select * from tb2 where nid > ?';
    EXECUTE prod USING @p1;
    DEALLOCATE prepare prod; 

END\\
delimiter ;

4.时间

4.1预测时间

大概判断一条sql命令所需的时间
创建预测
explain mysql命令

4.1.1当中的简述

type = all 是全表扫描，id不是唯一表示，只是代指这个表，type = ref是走了索引的

id
       查询顺序标识
           如：mysql> explain select * from (select nid,name from tb1 where nid < 10) as B;
           +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
           | id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
           +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
           |  1 | PRIMARY     |  | ALL   | NULL          | NULL    | NULL    | NULL |    9 | NULL        |
           |  2 | DERIVED     | tb1        | range | PRIMARY       | PRIMARY | 8       | NULL |    9 | Using where |
           +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
       特别的：如果使用union连接气值可能为null

  select_type
      查询类型
          SIMPLE          简单查询
          PRIMARY         最外层查询
          SUBQUERY        映射为子查询
          DERIVED         子查询
          UNION           联合
          UNION RESULT    使用联合的结果
          ...
  table
      正在访问的表名

  type
      查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
          ALL             全表扫描，对于数据表从头到尾找一遍
                          select * from tb1;
                          特别的：如果有limit限制，则找到之后就不在继续向下扫描
                                 select * from tb1 where email = 'seven@live.com'
                                 select * from tb1 where email = 'seven@live.com' limit 1;
                                 select * from tb1 where email = 'seven@live.com' limit 1,10;#显示前十条
                                 虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。
  
          INDEX           全索引扫描，对索引从头到尾找一遍
                          select nid from tb1;
  
          RANGE          对索引列进行范围查找
                          select *  from tb1 where name < 'alex';
                          PS:
                              between and
                              in
                              >   >=  <   <=  操作
                              注意：!= 和 > 符号

          INDEX_MERGE     合并索引，使用多个单列索引搜索
                          select *  from tb1 where name = 'alex' or nid in (11,22,33);
  
          REF             根据索引查找一个或多个值
                          select *  from tb1 where name = 'seven';
  
          EQ_REF          连接时使用primary key 或 unique类型
                          select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;

          CONST           常量
                          表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。
                          select nid from tb1 where nid = 2 ;
  
          SYSTEM          系统
                          表仅有一行(=系统表)。这是const联接类型的一个特例。
                          select * from (select nid from tb1 where nid = 1) as A;
  possible_keys
      可能使用的索引
  
  key
      真实使用的
  
  key_len
      MySQL中使用索引字节长度
  
  rows
      mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值
  
  extra
      该列包含MySQL解决查询的详细信息
      “Using index”
          此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。
      “Using where”
          这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。
      “Using temporary”
          这意味着mysql在对查询结果排序时会使用一个临时表。
      “Using filesort”
          这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。
      “Range checked for each record(index map: N)”
          这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的。


## 5.慢日志查询

- 需要：条件，路径

### 5.1 配置MySQL自动记录慢日志

```mysql
内存中配置
slow_query_log = OFF|ON             是否开启慢日志记录
long_query_time = 2              时间限制，超过此时间，则记录
slow_query_log_file = /usr/slow.log     日志文件
log_queries_not_using_indexes = OFF   为使用索引的搜索是否记录

*注：查看当前配置信息：*
　　   *show variables like '%query%'*
   *修改当前配置：*
　　　　*set global 变量名 = 值*
配置文件中配置
	mysqld --defaults-file="D:my.conf"    #启动配置文件
	my.conf内容：						 #配置文件
		slow_query_log = on
		slow_query_log_file = D:/...
#你可以把配置文件放在其他位置需要写这条命令my.ini
	datadir = 配置文件路径
	#然后再打开就可以应用这个配置文件了
	#注意：修改配置文件之后，需要重启服务
		#现将原本的配置备份，防止修改的配置文件出现问题，修改不回来

4.2 查看MySQL慢日志

mysqldumpslow -s at -a  /usr/local/var/mysql/MacBook-Pro-3-slow.log
"""``--verbose  版本``--debug   调试``--help    帮助` `-v      版本``-d      调试模式``-s ORDER   排序方式``       ``what to sort by (al, at, ar, c, l, r, t), 'at' is default``       ``al: average lock time``       ``ar: average rows sent``       ``at: average query time``        ``c: count``        ``l: lock time``        ``r: rows sent``        ``t: query time``-r      反转顺序，默认文件倒序拍。reverse the sort order (largest last instead of first)``-t NUM    显示前N条just show the top n queries``-a      不要将SQL中数字转换成N，字符串转换成S。don't abstract all numbers to N and strings to 'S'``-n NUM    abstract numbers with at least n digits within names``-g PATTERN  正则匹配；grep: only consider stmts that include this string``-h HOSTNAME mysql机器名或者IP；hostname of db server for *-slow.log filename (can be wildcard),``       ``default is '*', i.e. match all``-i NAME   name of server instance (if using mysql.server startup script)``-l      总时间中不减去锁定时间；don't subtract lock time from total time``"""

5.分页

方案：
- 记录当前页的最大值和最小值id
- 往下翻：
  - select*from yonghu > 124(得到的最大id) limit 10(往下十条数据)
  - 在多个页面中翻找如11，往14上找就可以用这个代码
```
select * from user where id in(
	select id from (select id from user where id > max_id limit 30 ) as n order by n,id desc limit 10
)
```
    select * from user where id in()
- 往上翻：
  - select*from yonghu < 124(得到的最小id) order by id desc(从大到小排序就可以拿到上面的10条数据) limit 10(往下十条数据)
  - 在多个页面中翻找如11，往14上找就可以用这个代码
```
select * from user where id in(
	select id from (select id from user where id < max_id limit 30 ) as n order by n,id desc limit 10
)
```
- 注意：id不连续，所以无法直接使用id进行范围查找

mysql

day37

day37

上节回顾

链接别人的mysqla

今日内容

1.索引

1.1作用

1.2索引

1.2.1hash索引

1.2.2btree索引

1.2.3建立索引

1.普通索引

2.唯一索引

3.组合索引

4.主键索引

5.覆盖索引

6.索引合并

1.3正确使用索引

1.4其他注意事项

2.ORM框架—sqlaichemy

3.其他

3.1.条件语句

3.2循环语句

3.2.1while循环

3.2.2repeat循环

3.2.3loop

3.3动态执行sql语句

4.时间

4.1预测时间

4.1.1当中的简述

4.2 查看MySQL慢日志

5.分页

相关