欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

day 36

程序员文章站 2023-02-03 19:11:50
[TOC] pymysql操作mysql 安装 连接 增 删 改 查 索引 为什么使用索引以及索引的作用 使用索引就是为了提高查询效率的 类比 字典中的目录 索引的本质 一个特殊的文件 索引的底层原理 B+树 索引的种类(重点) 主键索引 加速查找 + 不能重复 + 不能为空 唯一索引 加速查找 + ......

目录

pymysql操作mysql

安装

pip install pymysql

连接

# 连接数据库的参数
conn = pymysql.connect(host='localhost', user='root', password='123456', database='test', charset='utf8')

# cursor = conn.cursor()    默认返回的值是元组类型
cursor = conn.cursor(cursor=pymysql.cursors.dictcursor) # 返回的值是字典类型(********)

sql = "insert into user (name, password) values (%s, %s)"
# cursor.execute(sql,('xxx','qwe')) 新增一条数据
data =[
    ('zekai','qwe'),
    ('zekai1','qwe1'),
    ('zekai2','qwe2'),
    ('zekai3','qwe3'),
]
cursor.executrmany(sql,data)    新增多条数据
#### 加如下代码
conn.commit()

sql = "delete from user where id=%s"
cursor.execute(sql,('wsedrf',2))

conn.commit()

cursor.close()
conn.close()

sql = "update user set name=%s where id=%s"
cursor.excute(sql,('wsedrf',2))

conn.commit

cursor,close()
conn.close()

fetchall() : 取出所有的数据,返回的是列表套字典
fetchone() : 取出一条数据,返回的是字典
fetchmany(size) : 取出size条数据,返回的是列表套字典

索引

为什么使用索引以及索引的作用

使用索引就是为了提高查询效率的

类比

字典中的目录

索引的本质

一个特殊的文件

索引的底层原理

b+树

索引的种类(重点)

主键索引

加速查找 + 不能重复 + 不能为空 primary key

唯一索引

加速查找 + 不能重复 unique (name)

联合唯一索引

unique(name, email)

普通索引

加速查找 index (name)

联合索引

index (name,email)

索引的创建

主键索引

新增主键索引

create table xxx(
        id int auto_increment,
        primary key(id)
)

alter table xxx change id id int auto_increment primary key;

alter table t1 add primary key (id);

删除主键索引

alter table t1 drop primary key;

唯一索引

新增唯一索引

1.
create table t2(
        id int auto_increment primary key,
        name varchar(32) not null default '',
        unique u_name (name)
)charset=utf8;
2.
create index 索引名 on 表名 (字段名);
        create index ix_name on t3(name);
3.
alter table t3 add index ix_name (name)

删除唯一索引

alter table t3 drop index u_name;

索引的优缺点

通过观察 *.ibd文件可知:

​ 优点:索引加快了查询速度

​ 缺点:加了索引之后,会占用大量的磁盘空间

索引加的越多越好

并不是

不会命中索引的情况

a.不能再sql语句中,进行四则运算,会降低sql的查询效率

b.使用函数:

select * from tb1 where reverse(email) = 'zakai';

c.类型不一致

​ 如果列是字符串类型,传入条件是必须用引号引起来,不然查询时间会相当长

select * from tb1 where (email) = 999;

排序条件为索引,则select字段必须也是索引字段,否则无法命中

d.order by

select name from s1 order by email desc;

当根据索引排序时候,select查询的字段如果不是索引,则速度仍然很慢

select email from s1 order by email desc;

特别的:如果对主键排序,则还是速度很快:

select * from tb1 order by nid desc;

e.count(1)count(列)代替count(*)在mysql中没有差别了

f.组合索引最左前缀

什么时候会创建联合索引?

​ 根据公司的业务场景,在最常用的几列上添加索引

select * from user where name = 'zekai' and email='zekai@qq.com'

​ 如果遇到上述业务情况, 错误的做法:

index ix_name (name),

index ix_email(email)

​ 正确的做法:

index ix_name_email(name,email)

​ 如果组合索引为:

ix_name_email (name,email)************

where name='zekai' and email='xxx' ---命中索引

where name='zekai' ---命中索引

where email='zekai@qq.com' ---命中索引

​ 例:

index (a,b,c,d)

where a=2 and b=3 and c=4 and d=5 --命中索引

where a= 2 and c=4 and d=5 --未命中索引

g. explain

mysql>explain select * from user where name='zekai' and email='zekai@qq.com'\g
*************************** 1. row ***************************
                               id: 1          
                      select_type: simple    
                            table: user
                       partitions: null
                             type: ref       索引指向 all
                    possible_keys: ix_name_email     可能用到的索引
                              key: ix_name_email     确实用到的索引
                          key_len: 214            索引长度
                              ref: const,const
                             rows: 1            扫描的长度
                         filtered: 100.00
                            extra: using index   使用到了索引
            

索引覆盖

select id from user where id=2000

慢查询日志

查看慢sql的相关变量

mysql>show variables like '%slow%';
    +---------------------------+-----------------------------------------------+
    | variable_name             | value                                         |
    +---------------------------+-----------------------------------------------+
    | log_slow_admin_statements | off                                           |
    | log_slow_slave_statements | off                                           |
    | slow_launch_time          | 2                                             |
    | slow_query_log            | off   ### 默认关闭慢sql查询日志, on                       
    | slow_query_log_file       | d:\mysql-5.7.28\data\desktop-910unqe-slow.log | ## 慢sql记录的位置
    +---------------------------+-----------------------------------------------+
    5 rows in set, 1 warning (0.08 sec)
                
mysql> show variables like '%long%';
    +----------------------------------------------------------+-----------+
    | variable_name                                            | value     |
    +----------------------------------------------------------+-----------+
    | long_query_time                                          | 10.000000 |

配置慢sql的变量:

set global 变量名 = 值
set global slow_query_log = on;
set global slow_query_flie="d:/mysql-5.7.28/data/myslow.log";
set global long_query_time=1;