数据准备

索引原理

#!/usr/bin/env python # -*- coding:utf-8 -*- # 什么是索引 -- 目录 # 就是建立起的一个在存储表阶段 # 就有的一个存储结构能在查询的时候加速 # 索引的重要性 # 读写比例 ： 10：1 # 读（查询）的速度就至关重要了 # 索引的原理 # block 磁盘预读原理 # for line in f # 4096个字节 # 读硬盘的io操作的时间非常的长，比CPU执行指令的时间长很多 # 尽量的减少IO次数才是读写数据的主要要解决的问题 # 数据库的存储方式 # 新的数据结构 —— 树 # 平衡树 balance tree - b树 # 在b树的基础上进行了改良 - b+树 # 1.分支节点和根节点都不再存储实际的数据了 # 让分支和根节点能存储更多的索引的信息 # 就降低了树的高度 # 所有的实际数据都存储在叶子节点中 # 2.在叶子节点之间加入了双向的链式结构 # 方便在查询中的范围条件 # mysql当中所有的b+树索引的高度都基本控制在3层 # 1.io操作的次数非常稳定 # 2.有利于通过范围查询 # 什么会影响索引的效率 —— 树的高度 # 1.对哪一列创建索引，选择尽量短的列做索引 # 2.对区分度高的列建索引，重复率超过了10%那么不适合创建索引 # 聚集索引和辅助索引 # 在innodb中 聚集索引和辅助索引并存的 # 聚集索引 - 主键 更快 # 数据直接存储在树结构的叶子节点 # 辅助索引 - 除了主键之外所有的索引都是辅助索引 稍慢 # 数据不直接存储在树中 # 在myisam中 只有辅助索引，没有聚集索引 # 索引的种类 # primary key 主键 聚集索引 约束的作用：非空 + 唯一 # 联合主键 # unique 自带索引 辅助索引 约束的作用：唯一 # 联合唯一 # index 辅助索引 没有约束作用 # 联合索引 # 看一下如何创建索引、创建索引之后的变化 # create index 索引名字 on 表(字段) # DROP INDEX 索引名 ON 表名字; # 索引是如何发挥作用的 # select * from 表 where id = xxxxx # 在id字段没有索引的时候，效率低 # 在id字段有索引的之后，效率高

b树 分支和根节点都会储存数据 b+树 分支和根节点储存索引，真实数据都储存在叶子节点 聚集索引，直接找到数据 辅助索引，先找到主键，再聚集索引根据主键找到数据

正确的使用索引

#!/usr/bin/env python # -*- coding:utf-8 -*- # 以email为条件查询 # 不添加索引的时候肯定慢 # 查询的字段不是索引字段，也慢 # id作为条件的时候 # 如果不加索引，速度慢 # 加了索引，速度快 # 索引不生效的原因 # 要查询的数据的范围大 # > < >= <= != # between and # select * from 表 order by age limit 0，5 # select * from 表 where id between 1000000 and 1000005; # like # 结果的范围大 索引不生效 # 如果 abc% 索引生效，%abc索引就不生效 # 如果一列内容的区分度不高，索引也不生效 # name列 # 索引列不能在条件中参与计算 # select * from s1 where id*10 = 1000000; 索引不生效 # 对两列内容进行条件查询 # and and条件两端的内容，优先选择一个有索引的，并且树形结构更好的，来进行查询 # 两个条件都成立才能完成where条件，先完成范围小的缩小后面条件的压力 # select * from s1 where id =1000000 and email = 'eva1000000@oldboy'; # or or条件的，不会进行优化，只是根据条件从左到右依次筛选 # 条件中带有or的要想命中索引，这些条件中所有的列都是索引列 # select * from s1 where id =1000000 or email = 'eva1000000@oldboy'; # 联合索引 # create index ind_mix on s1(id,name,email); # select * from s1 where id =1000000 and email = 'eva1000000@oldboy'; # 在联合索引中如果使用了or条件索引就不能生效 # select * from s1 where id =1000000 or email = 'eva1000000@oldboy'; # 最左前缀原则 ：在联合索引中，条件必须含有在创建索引的时候的第一个索引列 # select * from s1 where id =1000000; 能命中索引 # select * from s1 where email = 'eva1000000@oldboy'; 不能命中索引 # (a,b,c,d) # a,b # a,c # a # a,d # a,b,d # a,c,d # a,b,c,d # 在整个条件中，从开始出现模糊匹配的那一刻，索引就失效了 # select * from s1 where id >1000000 and email = 'eva1000001@oldboy'; # select * from s1 where id =1000000 and email like 'eva%'; # 什么时候用联合索引 # 只对 a 对abc 条件进行索引 # 而不会对b，对c进行单列的索引 # 单列索引 # 选择一个区分度高的列建立索引，条件中的列不要参与计算，条件的范围尽量小，使用and作为条件的连接符 # 使用or来连接多个条件 # 在满上上述条件的基础上 # 对or相关的所有列分别创建索引 # 覆盖索引 # 如果我们使用索引作为条件查询，查询完毕之后，不需要回表查，覆盖索引 # explain select id from s1 where id = 1000000; # explain select count(id) from s1 where id > 1000000; # 合并索引 # 对两个字段分别创建索引，由于sql的条件让两个索引同时生效了，那么这个时候这两个索引就成为了合并索引 # 执行计划 : 如果你想在执行sql之前就知道sql语句的执行情况，那么可以使用执行计划 # 情况1： # 30000000条数据 # sql 20s # explain sql --> 并不会真正的执行sql，而是会给你列出一个执行计划 # 情况2： # 20条数据 --> 30000000 # explain sql # 原理和概念 # b树 # b+树 # 聚集索引 - innodb # 辅助索引 - innodb myisam # SQL索引的创建（单个、联合）、删除 # 索引的命中：范围，条件的字段是否参与计算(不能用函数)，列的区分度(长度)，条件and/or，联合索引的最左前缀问题 # 一些名词 # 覆盖索引 # 合并索引 # explain执行计划 # 建表、使用sql语句的时候注意的 # char 代替 varchar # 连表 代替 子查询 # 创建表的时候 固定长度的字段放在前面

数据备份

#!/usr/bin/env python # -*- coding:utf-8 -*- # mysqldump -uroot -p123 day40 > D:\code\s21day41\day40.sql # mysqldump -uroot -p123 --databases new_db > D:\code\s21day41\db.sql

第一个是备份day40库里所有的文件 第二个是备份new_db这个库 恢复文件： 恢复库 --> 直接source 备份文件路径 恢复表 --> 先创建库 再use库 再source备份文件路径

事务

# begin; # 开启事务 # select * from emp where id = 1 for update; # 查询id值，for update添加行锁； # update emp set salary=10000 where id = 1; # 完成更新 # commit; # 提交事务

sql注入

#!/usr/bin/env python # -*- coding:utf-8 -*- # create table userinfo( # id int primary key auto_increment, # name char(12) unique not null, # password char(18) not null # ) # # insert into userinfo(name,password) values('alex','alex3714') # 输入用户 # 输入密码 # # 用户名和密码到数据库里查询数据 # 如果能查到数据 说明用户名和密码正确 # 如果查不到，说明用户名和密码不对

sql注入导致的数据不安全 以下两种方法 不用登录依旧可以从数据库查出数据

# username = input('user >>>') # password = input('passwd >>>') # sql = "select * from userinfo where name = '%s' and password = '%s'"%(username,password) # print(sql) # -- 注释掉--之后的sql语句 # select * from userinfo where name = 'alex' ;-- and password = '792164987034'; # select * from userinfo where name = 219879 or 1=1 ;-- and password = 792164987034; # select * from userinfo where name = '219879' or 1=1 ;-- and password = '792164987034';

解决办法：让sql帮忙拼接，不要自己去拼接

import pymysql conn = pymysql.connect(host = '127.0.0.1',user = 'root', password = '123',database='day41') cur = conn.cursor() username = input('user >>>') password = input('passwd >>>') sql = "select * from userinfo where name = %s and password = %s" cur.execute(sql,(username,password)) print(cur.fetchone()) cur.close() conn.close()