Python 14 Mysql数据库（二）

内容概要：

1、引擎

2、索引

3、视图

4、触发器

5、函数

6、存储过程

7、优化方案

 

一、引擎

https://www.cnblogs.com/Eva-J/articles/9682063.html

Mysql有两种引擎：InnorDB和MyISAM

1、MyISAM：不支持事务和外键，只可以使用表锁，但运行速度较快，其索引信息另外存放一张表，对于多查询和多插入的程序速度较快，但不适用于多删改的程序。

2、InnorDB：支持事务和外键，同时支持表锁和行锁，但运行速度较慢，其索引信息与数据信息放在一起，对于多查询和多插入的程序速度较慢，但是对于多删改的程序性能较好，可以保证数据完整性。

begin;
select * from student for update; #整张表都锁
select * from student where id=1 for update; #只锁这一行数据
commit;  #解锁

cursor.execute('select * from student for update')

#Django中不能用锁，但是可以用事务
with trancation.automic:
    models.User.object.all().for_update()

创建和修改引擎：

# 创建引擎
mysql> create table ai(id bigint(12),name varchar(200)) ENGINE=MyISAM; 
mysql> create table country(id int(4),cname varchar(50)) ENGINE=InnoDB;

# 也可以使用alter table语句，修改一个已经存在的表的存储引擎。
mysql> alter table ai engine = innodb;

 

二、索引

https://www.cnblogs.com/Eva-J/articles/10126413.html

聚集索引：

#InnoDB存储引擎表是索引组织表，即表中数据按照主键顺序存放。
而聚集索引（clustered index）就是按照每张表的主键构造一棵B+树，同时叶子结点存放的即为整张表的行记录数据，也将聚集索引的叶子结点称为数据页。
聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。
    
#如果未定义主键，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚簇索引。
    
#如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。

#由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。
在多数情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。
此外由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值得查询。
聚集索引的好处之一：它对主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表，查询最后的10位用户信息，由于B+树索引是双向链表，所以用户可以快速找到最后一个数据页，并取出10条记录
聚集索引的好处之二：范围查询（range query），即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可

辅助索引：

表中除了聚集索引外其他索引都是辅助索引（Secondary Index，也称为非聚集索引），与聚集索引的区别是：辅助索引的叶子节点不包含行记录的全部数据。

叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含一个书签（bookmark）。该书签用来告诉InnoDB存储引擎去哪里可以找到与索引相对应的行数据。

由于InnoDB存储引擎是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引，但只能有一个聚集索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶子级别的指针获得只想主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

举例来说，如果在一棵高度为3的辅助索引树种查找数据，那需要对这个辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行3次查找，最终找到一个完整的行数据所在的页，因此一共需要6次逻辑IO访问才能得到最终的一个数据页。

 

索引的分类

普通索引、唯一索引、主键索引、组合索引、组合唯一索引

 

索引的创建

（1）普通索引

方式一：

create index 索引名 on table_name(column_name);

方式二：

alert table table_name add index column_name;

（2）唯一索引

create unique index 索引名 on table_name(column_name);

（3）组合索引

一个表中有多个索引并不是组合索引，组合索引是包含多列但只有一个索引名。

create index 索引名 on table_name('name', 'age', 'gender');

如果建立了这样一个组合索引，实际上是三个索引（name），（name，age），（name，age，gender），在查询时遵循最左前缀。

最左前缀：

1、如果不按照组合索引最左列开始查询，则不能使用索引。例如：select * from student where 'age' = 2 and 'gender' = 1，不能使用索引，因为最开始的条件不是name = xxx。

2、如果查询中某个列有范围查询，则他右边的索引都不生效。例如：select * from student where 'name' = 'sd' and 'age' like = 'aa%' and 'gender' = 1，gender的索引就不会使用。如果第一列就使用了范围查找，则整个查询都不能使用索引。

 

索引的删除

drop index 索引名 on table_name;

 

三、视图

视图相当于是给一条查询语句设置了别名，以后调用这个别名就相当于是执行了这条查询语句.

视图可以更加方便快捷的使用一些查询语句，减少重复输入，并且可以降低耦合性

# 创建视图
CREATE VIEW view_name AS select * from tablename;

# 使用视图
select * from view_name;

 

四、触发器

触发器就是让mysql去监听某个事件，当事件发生时执行某些操作，如监听增删改查操作。

DELIMITER ;;
create trigger trigger_name before/after insert on tablename for each row
begin
   xxxx
end;
DELIMITER ;

 

五、函数

函数就是类似python中的函数，帮助我们对数据进行一些操作

常用函数：

select avg(age) as 'avg' from tablename;
select max(age) from tablename;
select count(age) from tablename
select sum(age) from tablename;

 

六、存储过程

存储过程相当于是封装了sql语句，执行这个存储过程就能自动执行一些操作，可以传递参数

DELIMITER ;;
create precedure name1()
begin
    select * from table;
end;

create precedure name2(name)
begin
    select * from table where name=name;
end;
DELIMITER ;

call name1();
call name2('yin');

 

七、数据库优化方案