索引 实际上就是一张表中数据的目录,可以任意选择能够做目录的字段
balance tree 平衡树
让目录的数据结构变得合理化
缺点:
查询每一个数据所需要的io是不一样的,不是很稳定
取范围的时候,需要向上一个数据页去寻找
b+树 以平衡树为基础
所有的数据都存储在叶子节点,查找数据时候的IO更加稳定
叶子节点与叶子节点之间的地址通过一个双向的指针相连,查找范围的时候更简便
innodb索引的数据结构是b+树 一次性默认读取数据16k
数的高度决定了IO次数,数据的长度越短,作为索引能够建立的这个树形结构越矮,效率越高
应该使用区分度大的内容来作为索引列
innodb和myisam的索引的存储方式
innodb内部 有聚集索引(聚簇索引),叶子节点除了存储索引,还存储值 磁盘预读性原理 和非聚集索引
innodb必须有一个主键,即使没有,mysql也会自动添加一列作为这张表的主键
myisam 内部没有聚集索引,所有索引都是非聚集索引
索引的种类
primary key 除了约束 非空+唯一之外,还有其他的作用 聚集索引 全表只能有一个聚集索引
unique 约束 唯一 非聚集索引的作用
index 没有约束的作用 普通的非聚集索引
正确使用索引
create index ind_id on s1(id)
innodb中 所有的索引数据和存储的数据是存在一个文件里的
创建索引必然会拖慢写的速度
如果没有索引,是全表扫描,有了索引,就可以根据目录快速找到数据
正确使用索引:
1.条件必须是已经创建索引的列
2.如果在条件中对索引列使用了范围,并且范围很大,那么速度会很慢
3.like 后面必须是'确定内容%' 是可以用索引加速查询的 如果是like '%内容',是不能用索引查询的
4.确认使用的索引列的区分度 选择区分度低的列作为索引实际上不会有加速查找的效果
5.索引列在条件中不能参与计算
and/or
使用and条件的时候,mysql在优化查询的时候会优先选择区分度高的列先查询,来缩小其他条件要查询的范围
or条件 只会从左到右依次进行判断,不会加快查询速度,除非条件中出现的所有列区分度都很高,每一个字段都要添加所有
联合索引
create index 索引名 on 表名(字段1,字段2)
最左前缀匹配原则:必须带上字段1进行索引
覆盖索引
要查找的字段是条件中筛选的字段
# select name from 表 where id =10000 # 用到索引了 ,但是不是覆盖索引
# select id from 表 where id =10000 # 用到索引了 ,也是覆盖索引
# select count(id) from 表 where id >10000 # 用到索引了 ,也是覆盖索引
索引合并
对单列进行索引的创建
id email
# 使用了两个条件id = 100000 or email = 'eva100000@oldboy';
删除索引
drop index 索引名 on 表名
python操作数据库
import pymysql conn = pymysql.connect(host='127.0.0.1',user='root',password=None,database='db3') cursor = conn.cursor() sql = 'select * from employee where post = "teacher";' #将sql语句封装起来,用sql变量接收 cursor.execute(sql) # ret = cursor.fetchall() #显示全部的值 # ret = cursor.fetchone() #显示一个值 ret = cursor.fetchmany(2) #可以指定值显示,括号内不写数字默显示一个值 print(ret) conn.close()
事务和锁
begin; # 开启事务
select * from emp where id = 1 for update; # 查询id值，for update添加行锁；
update emp set salary=10000 where id = 1; # 完成更新
commit; # 提交事务
避免同时修改数据库中的数据,造成数据不安全,在修改时,利用事务保证数据安全
注意 sql语句只能一条一条赋值然后执行,不能把整个事务的sql语句一起赋值
import pymysql conn = pymysql.connect(host='127.0.0.1',user='root',password=None,database='db3') cursor = conn.cursor() sql1 = 'begin;' sql2 = 'select * from employee where id = 1 for update;' sql3 = 'update employee set salary=9000 where id = 1;' sql4 = 'commit;' cursor.execute(sql1) cursor.execute(sql2) cursor.execute(sql3) cursor.execute(sql4) #逐条执行 conn.close()
数据库备份
mysqldump -uroot -p123 数据库名 > 路径 # 备份所有的表以及数据 create database 数据库名 use 数据库 mysql> source 文件的路径
mysqldump -uroot -p123 --databases homework > 路径下 # 备份这个库和所有的表以及数据 source 文件路径