[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='[email protected]'
如果遇到上述業務情況， 錯誤的做法：
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='[email protected]' ---命中索引
例：
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='[email protected]'\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;
