Mysql 学习笔记(六)：索引，数据备份，事务，sql注入

数据准备

索引原理

#!/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()