day 36

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;
posted @ 2019-10-31 19:03  colacheng  阅读(...)  评论(... 编辑 收藏
Live2D