MySQL--用户管理 pymysql 索引

目录

• 用户管理
  • 创建mysql账户
  • 权限管理
    • 涉及到的表
      • 语法
      • 案例
• pymysql
  • 基本使用
  • sql注入攻击
  • 案例
• 索引
  • 为什么需要索引
  • 索引的影响
  • 磁盘IO
  • 索引数据结构
  • 最左匹配原则
  • 聚集索引
  • 辅助索引
  • 覆盖查询
  • 回表查询
  • 语法
  • 结论

用户管理

主要是为了控制权限，让不同的人只能操作只属于只记得那部分数据

创建mysql账户

账户中涉及三个数据

1. 账户名

2. 密码

3. IP地址

   IP主要是用来限制某个账户只能在哪些机器进行登录

• 语法

  create user 用户名@主机地址 identified by "密码"；
  
  # 注意：创建用户只能由root账户进行操作
  
  # 删除用户
  drop user 用户名@主机地址
  

权限管理

涉及到的表

1. user 与用户相关信息
2. db 用户数据库权限信息
3. tables_priv 用户的表权限
4. columns_priv 用户的字段权限

语法

# 拥有所有权限
grant all on *.* to 用户名@主机地址 identified by "密码";
# 该语句会自动创建用户如果用户不存在

# 创建拥有某个库的所有权限
grant all on 库名.* to 用户名@主机地址 identified by "密码";

# 创建某个库下某个表的所有权限
grant all on 库名.表名 to 用户名@主机地址 identified by "密码";

# 创建某个库下某个表的某些字段权限
grant 事件(字段名),事件(字段名) on 库名.表名 to 用户名@主机地址 identified by "密码";

# 注意:事件包括update/select/delete....

# 收回权限
revoke all on *.* from 用户名@主机地址;

# 刷新权限
flush privileges;


# 将自己账户的权限给其他账户,自己的账户就得有授予权限的权限,可以通过with grant option进行
grant all  on *.*  to 用户名@主机地址 identified by "密码" with grant option;

# 注意:这种操作只能把自己拥有的权限给其他人

# 授予某个用户 可以在任何主机上登录
grant all  on *.*  to 用户名@"%"  identified by "密码";
grant all  on *.*  to 用户名@localhost  identified by "密码";

案例

grant select(name),update(name)  on day42.table1  to rose3@localhost  identified by "123";

pymysql

pymysql 是一个第三方, 帮我们封装了建立连接,用户认证,sql 执行以及结果的获取

基本使用

"""
1. 导入模块
2. 连接服务器
3. 用户认证
4. 发送指令
5. 提取结果
"""

import pymysql

# 连接服务器  获取连接对象(本质上就是封装号的socket)
conn = pymysql.connect(
    host = "127.0.0.1", # 如果是本机 可以忽略
    port = 3306, # 如果没改过 可以忽略
    user = "root",  # 必填
    password = "123",  # 必填
    database = "test"  # 必填
)

# 通过连接拿到游标,默认的游标返回的是元组类型,不方便使用,需要更换字典类型的游标
c = conn.cursor(pymysql.cursors.DictCursor)

# 执行sql语句
sql = "select * from table1"
res = c.execute(sql)  # 返回的是查询结果的数量

# 提取结果,这边获取的结果是一个迭代器,不能往回找结果
c.fetchall()  # 获取所有结果
# c.fetchmany(2) # 获取两条结果
# c.fetchone()  # 获取一条结果

# 关闭连接
c.close()
conn.close()


# 如果想获取之前的结果,可以通过移动光标 scroll进行,mode指定为相对路径或者绝对路径
c.scroll(1,mode="absolute/relative")

sql注入攻击

指的是用户在输入数据时,按照sql语句来编写带有攻击目的的sql语句,并插入到原原始语句中执行

例如:登录功能,需要用户输入用户名和密码

正常的一个登录功能代码如下:

try:
    conn = pymysql.connect(host="127.0.0.1",port=3306,user="root",password="",db="day46",)
    print("连接服务器成功!")
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    
    user = input("username:")
    password = input("password:")
    
    count = cursor.execute("select *from user where name = '%s' and password = '%s'" % (user,password))
    if count:
        print("登录成功!")
    else:
        print("登录失败!")
except Exception as e:
    print(type(e),e)
finally:
    if cursor:cursor.close()
    if conn: conn.close()

上述代码有被注入攻击的危险

尝试在用户名中输入以下内容,密码随意
 23' — ass 
或者连用户名都不用写
' or 1 = 1 -- asaa

解决方案:

1. 客户端发送sql给服务器前进行re判断

   这样的问题在于一些程序可以模拟客户端直接发送请求给服务器

2. 在服务器端将sql交给mysql是作进一步处理,相关的代码其实pymysql已经做了封装

   我们只要保证不要自己来拼接sql语句即可,将拼接参数操作交给pymysql.

案例

import pymysql
conn = pymysql.connect(
    host = "127.0.0.1",  #如果是本机 可以忽略
    port = 3306,    # 如果没改过 可以忽略
    user = "root", #必填
    password = "111", #必填
    database = "day42", #必填,
    #autocommit=False  # 开启自动提交  不常用....
)

c = conn.cursor(pymysql.cursors.DictCursor)
name = input("name:")
pwd = input("pwd:")

sql = "select *from user where name = %s"

if c.execute(sql,(name,)):
    print("用户名已存在!")
else:
    sql2 = "insert  into user values(%s,%s)"
    if c.execute(sql2,(name,pwd)):
        print("注册成功!")
        conn.commit() # 调用连接对象的提交函数
    else:
        print("注册失败!")

c.close()
conn.close()

注意:pymysql会自动开启事务,所以需要在合适的位置进行提交(commit)

索引

索引就是一个特殊的数据结构,存储的是数据的关键信息与详细信息的位置对应关系

为什么需要索引

索引就相当于书本的目录,可以简便查询,降查询范围进行了缩小,这样就可以加快查询速度

否则的话,就需要对数据进行遍历,非常耗时,所以添加索引是非常有必要的

索引的本质就是尽可能的减小搜索范围

注意:在数据库插入数据会引发索引的重建

索引的影响

1. 不是说有了索引就能加速,还需要你的查询语句正确使用索引
2. 索引也是需要占用额外的数据空间
3. 添加索引后,将导致增删改速度变慢

所以并不是所有数据都需要添加索引

一般来说, 只有查询较多,写入较少的且数据量较大的数据才需要添加索引

磁盘IO

按照正常的机械硬盘7200r/min, 那么查找一次数据至少花费9ms, 具体计算过程可参考计算机基础,而处理器以每秒计算5亿次, 那么9ms大概可以计算450万次运算,显然读取数据对于CPU来说,太慢了

所以每次找数据的时候CPU都会切换到其他的程序去运行, 下次查找再找CPU,这样CPU切来切去的也会降低CPU的利用效率,所以要增加查询速度就是要减少IO操作的次数

索引数据结构

索引的数据结构就是B+树数据结构

B+数据结构是根据二分法进行查找的, 这样就不需要进行遍历数据进行查找了

最左匹配原则

​ 当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候(多字段联合索引)，b+树会按照从左到右的顺序来建立搜索树，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

聚集索引

聚集索引中包含了所有字段的值,如果指定了主键,主键就是聚集索引,如果没有指定主键,那么搜索引擎就会自动找一个非空且唯一的字段作为聚集索引, 如果还是没有, 则会自动生成一个字段作为聚集索引

• 聚集索引中存放了所有的数据

辅助索引

除了聚集索引以外的索引都是辅助索引

• 辅助索引中存放的是当前的索引字段以及主键值

覆盖查询

覆盖查询就是当前索引结构中就能找到所有需要的数据, 如果使用聚集索引进行查询,那么一定是覆盖查询,而且是速度最快的

回表查询

回表查询就是当前索引中找不到需要的数据, 那么就需要通过主键去聚集索引中进行查找

速度慢于聚集索引

语法

# 创建索引
create index 索引的名字 on 表名(字段名);

# 联合索引
create index 索引的名字 on 表名(字段1,字段2);

# 删除索引
drop index 索引名称 on 表名

结论

1. 尽量使用占用空间小的字段作为索引

2. 不要在一行中存储太多数据, 如果字段太多可以进行分表操作

3. 尽量使用覆盖查询

4. 如果字段区分度太低,建立的索引没有什么意义, 也就是说应该将区分度高的字段作为索引

5. 模糊匹配中,百分号尽量不要写在前面

6. 不要在等号的左边进行运算

7. and语句中,会自动找一个具备索引的字段优先执行, 所以and语句中至少要包含一个具备索引的字段

8. or语句要尽量避免使用,因为or语句会遍历所有条件,如果一定要用,最好保证所有字段都有索引,这样才能加速

9. 联合索引中, 需要将区分度高的放在左边,最低的放到右边

   在使用查询语句的时候, 也要保证最左边的索引出现在语句中

注意:如果查询内容过于庞大也是无法通过索引进行加速的

总结: 不是添加索引就能加速, 还需要考虑索引建立是否合理, 查询语句是否合理使用索引