Python-数据库索引浅谈
检索原理
检索初识
索引在MySQL中是一种“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的检索性能，非常关键，尤其是当表中的数据量越大，索引对于性能的提升越显得重要。
索引优化是对查询性能优化最有效的手段。索引能够轻易将查询性能提高好几个数量级。索引相当于字典的音序表，要查某个字，如果不使用音序表，则需要从几百页中逐页去查，这个开销是巨大的。
索引的原理
通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。
磁盘IO与预读
磁盘IO是非常高昂的操作，计算机操作系统为此做了一些优化，当一次IO时，不仅读取当前磁盘地址的数据，还会把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理可知，当计算机访问一个地址的数据时，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是读取一页内的数据，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。
索引的数据结构
B+树
数据只存储在叶子节点中；索引的区间范围存放在根节点和枝节点当中，每次IO操作可以读取更多的数据
在叶子节点之间接入双向地址连接，更方便在叶子节点之间进行数据的读取
【性质】
- 索引字段尽量要小。硬盘中一个数据页的大小是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低，检索效率就越高。索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高
- 索引的最左匹配原则。b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的。
索引原理
**innodb索引 **
【聚集索引clustered index】
使用主键构建B+树，数据也是索引的一部分；如果没有定义主键，mysql会把第一个 唯一+为空的字段设为主键，也就作为聚集索引；每张表只能有一个聚集索引；
聚集索引的优势：一是利用逐渐的排序和查找速度非常快，叶子结点的数据就是索要查找的数据，又因为B+书的索引是双向链表，所以可以很快的找到最后一个数据页内的数据；二是范围查询，如果要通过主键查找某一范围内的数据，只需要通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可。
【辅助索引secondary index 】
除了主键之外所有的索引都是辅助索引，也叫非聚集索引，与聚集索引的区别是：辅助索引的叶子节点不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含一个书签（bookmark）。该书签用来告诉InnoDB存储引擎去哪里可以找到与索引相对应的行数据。
辅助索引往往是分两步查询到数据，第一步是找到指向主键索引的主键，第二步通过主键索引来找到一个完整的数据。
聚集索引与辅助索引相同的是：不管是聚集索引还是辅助索引，其内部都是B+树的形式，即高度是平衡的，叶子结点存放着所有的数据。
聚集索引与辅助索引不同的是：叶子结点存放的是否是一整行的信息
聚集索引
1.纪录的索引顺序与物理顺序相同
因此更适合between and和order by操作
2.叶子结点直接对应数据
从中间级的索引页的索引行直接对应数据页
3.每张表只能创建一个聚集索引
非聚集索引
1.索引顺序和物理顺序无关
2.叶子结点不直接指向数据页
3.每张表可以有多个非聚集索引，需要更多磁盘和内容
多个索引会影响insert和update的速度
【回表】
只查询一个索引有时并不能解决查询中的问题，仍需到具体的表中获取真正的数据
**myisam索引 **
辅助索引 除了主键之外所有的索引都是辅助索引
索引的种类
primary key的创建自带索引效果 非空 + 唯一 + 聚集索引
unique 唯一约束的创建也自带索引效果 唯一 + 辅助索引
index 普通的索引 辅助索引
索引的两大类型
#我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）
#不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；
索引的创建与删除
#方法一：创建表时
CREATE TABLE 表名 (
字段名1 数据类型 [完整性约束条件…],
字段名2 数据类型 [完整性约束条件…],
[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
[索引名] (字段名[(长度)] [ASC |DESC])
);
#方法二：CREATE在已存在的表上创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (字段名[(长度)] [ASC |DESC]) ;
#方法三：ALTER TABLE在已存在的表上创建索引
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名 (字段名[(长度)] [ASC |DESC]) ;
#删除索引：DROP INDEX 索引名 ON 表名字;
【实例】
#方式一
create table t1(
id int,
name char,
age int,
sex enum('male','female'),
unique key uni_id(id),
index ix_name(name) #index没有key
);
create table t1(
id int,
name char,
age int,
sex enum('male','female'),
unique key uni_id(id),
index(name) #index没有key
);
#方式二
create index ix_age on t1(age);
#方式三
alter table t1 add index ix_sex(sex);
alter table t1 add index(sex);
#查看
mysql> show create table t1;
| t1 | CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`name` char(1) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`sex` enum('male','female') DEFAULT NULL,
UNIQUE KEY `uni_id` (`id`),
KEY `ix_name` (`name`),
KEY `ix_age` (`age`),
KEY `ix_sex` (`sex`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
索引的优点：
查找速度快
索引的缺点：
浪费空间，拖慢写入的速度（有可能触发树结构调整，不要创建无用的索引）
【总结】
创建索引之后查询效率大幅度提高
创建索引之后占用的硬盘资源也大幅度提高
正确的使用索引***
没有命中索引的情况，一定要注意：
1.查询的字段不是创建索引的字段；
2.在条件中不能带运算或函数；
3.如果创建索引的列中的数据重复率比较高也不能发挥索引的作用（临界值比例在10:1左右,例如：性别，部门之类的接没必要建立索引）
4.数据对应的范围如果太大，也不能有效率用索引(between and , < , >, != , not in 之类的检索的范围如果差距比较大的话，就很难命中索引)
5.like把%放在前边，直接不能命中索引，而是全表扫描，%放在后面，可以命中索引
6.多条件：and情况下，只要有一个条件列是索引列，就可以命中索引；or的情况下，必须所有的条件列都是索引列才能命中索引
7.联合索引***：where a = XX and b = YYY 情况下：a和b分别创建了索引，正常情况下只能命中树结构比较健康的索引，另一个则不会命中；可以创建联合索引，
# 创建联合索引
create index ind_name on 表名（字段1，字段2）
# 在多个条件相连的情况下，使用联合索引的效率高于单字段索引的连用
# 多个索引连用时，条件中从哪一个字段开始出现了范围，索引就从哪里开始失效，所以创建索引时可以把范围条件的往后放
# 联合索引在使用时遵循最左前缀原则
# 联合索引中只有使用and才能生效，使用or不生效
【其他注意事项】
- 避免使用select *
- 使用count(*)
- 创建表时尽量使用 char 代替 varchar
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引（由于mysql中每次只能使用一个索引，所以经常使用多个条件查询时更适合使用组合索引）
- 尽量使用短索引
- 使用连接（JOIN）来代替子查询(Sub-Queries)
- 连表时注意条件类型需一致
- 索引散列值（重复少）不适合建索引，例：性别不适合
两个名词
# 覆盖索引 从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。
extra 显示 usingindex
select count(id) from 表;
select id from 表 where id <20;
# 索引合并
单独创建索引，使用时临时合并使用，extra显示 using union
MySQL 神器 explain
# 执行计划
explain select * from 表名 where 条件；
可以查看是否命中了索引，以及命中索引的类型
慢日志
- 执行时间 > 10
- 未命中索引
- 日志文件路径
配置：
- 内存
show variables like '%query%';
show variables like '%queries%';
set global 变量名 = 值
- 配置文件
mysqld --defaults-file='E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\my-default.ini'
my.conf内容：
slow_query_log = ON
slow_query_log_file = D:/....
注意：修改配置文件之后，需要重启服务
慢日志管理
MySQL可以通过配置文件开启慢日志记录；如果数据库在自己手里可以自己开；在DBA手里的话，可以让他开一下
慢日志
- 执行时间 > 10
- 未命中索引
- 日志文件路径
配置：
- 内存
show variables like '%query%';
show variables like '%queries%';
set global 变量名 = 值
- 配置文件
mysqld --defaults-file='日志文件路径'
my.conf内容：
slow_query_log = ON
slow_query_log_file = 路径
注意：修改配置文件之后，需要重启服务
慢查询优化
0.先运行看看是否真的很慢，注意设置SQL_NO_CACHE
1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
2.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
3.order by limit 形式的sql语句让排序的表优先查
4.了解业务方使用场景
5.加索引时参照建索引的几大原则
6.观察结果，不符合预期继续从0分析
七表联查速度慢怎么办？***
1.表结构上来说：一是用固定长度的数据类型代替可变长度数据类型；二是把固定长度的字段放在表的前边
2.从数据的角度来说：如果表中的数据越多，查询效率越低；列多进行垂直分表，行多进行水平分表，把经常查询的数据放在一个表里
3.从SQL语句角度来说：一是尽量把条件写的详细一些，where条件多做筛选；二是多变查询尽量使用连表查询代替子查询；三是创建有效的索引，规避无效的索引
4.配置角度来说，开启慢日志查询，确认具体慢的sql语句，进行代码优化
5.数据库搭集群，做数据读写分离，一读多写
导入导出数据
导出：mysqldump -u -p 库名 > 导出位置
导入:进入mysql 切换到要恢复数据的库下面
备份库： mysqldump -u -p --database 导出位置
开启事务
【事务的定义】
- 一个最小的不可再分的工作单元，通常一个事务对应一个完整的业务
- 一个完整业务需要批量的DML语句共同组成
- DML语句才有事务，业务逻辑不同，DML语句的个数也不同
begin; # 开启事务，挂起自动提交
select * from emp where id = 1 for update; # 查询id值，for update添加行锁；
update emp set salary=10000 where id = 1; # 完成更新
commit; # 提交事务，成功的结束，将所有的DML语句操作历史记录和底层硬盘数据来一次同步
rollback ; # 回滚，失败的结束，将所有的DML语句操作历史记录全部清空
【特性】
隔离性：事物之间不会相互影响
稳定性：数据库在事务执行前后状态都必须是稳定的
原子性：构成事务的所有DML语句必须是一个逻辑单元，要么全部执行，要么全不执行
持久性：事务执行成功后必须全部写入磁盘
python连接数据库
安装PyMySQL模块
import pymysql
db = pymysql.connect("数据库ip","用户","密码","数据库" ) # 打开数据库连接
cursor.execute("SELECT VERSION()") # 使用 execute() 方法执行 SQL 查询
cur = db.cursor() # 游标，数据库操作顺序执行
data1 = cur.fetchone() # 使用 fetchone() 方法获取单条记录
data2 = cur.fetchmany(值) # 使用 fetchmany() 方法获取相应数值的记录
data3 = cur.fetchall() # 使用 fetchmall() 方法获取所有的记录
print ("Database version : %s " % data1)
db.commit() # 进行增删改操作时提交数据给数据库
cur.close() # 关闭游标
db.close() # 关闭数据库连接
