首页 > 数据库技术 > 详细

数据库学习之MySQL进阶

时间:2018-09-22 19:16:21      阅读:182      评论:0      收藏:0      [点我收藏+]

                   数据库进阶                   

   一、索引      

1.索引简介
索引在MySQL中也叫做“键”,是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键,尤其是当表中的数据量越来越大时,索引对于性能的影响愈发重要。

索引优化应该是对查询性能优化最有效的手段了。
索引能够轻易将查询性能提高好几个数量级。
索引相当于字典的音序表,如果要查某个字,如果不使用音序表,则需要从几百页中逐页去查。

索引特点:创建与维护索引会消耗很多时间与磁盘空间,但查询速度大大提高!


2.创建索引语法

--创建表时
CREATE TABLE 表名 (
字段名1 数据类型 [完整性约束条件…],
字段名2 数据类型 [完整性约束条件…],
[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
[索引名] (字段名[(长度)] [ASC |DESC])
);


实例:

CREATE TABLE test2(
  id INT,
  name VARCHAR(20),
  INDEX index_name (name)
  );

--添加索引

---CREATE在已存在的表上创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (字段名[(长度)] [ASC |DESC]) ;

---ALTER TABLE在已存在的表上创建索引

ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名 (字段名[(长度)] [ASC |DESC]) ;



CREATE INDEX index_emp_name on emp1(name);
ALTER TABLE emp2 ADD UNIQUE INDEX index_bank_num(band_num);


-- 删除索引

语法:DROP INDEX 索引名 on 表名

DROP INDEX index_emp_name on emp1;
DROP INDEX bank_num on emp2;

3.索引测试实验

--创建表
create table t1(id int,name varchar(20));


--存储过程

delimiter $$
create procedure autoinsert() 
BEGIN
declare i int default 1;
while(i<5000)do
insert into t1 values(i,‘yuan‘);
set i=i+1;
end while;
END$$

delimiter ;

--调用函数
call autoinsert();

-- 花费时间比较:
-- 创建索引前
select * from t1 where id=300000;--0.32s
-- 添加索引 
create index index_id on t1(id);
-- 创建索引后
select * from Indexdb.t1 where id=300000;--0.00s
--删除索引 
drop index index_id on t1;

 

实验二:

use crawed;
desc doutula;
select count(*) from doutula;
+----------+
| count(*) |
+----------+
| 27308 |
+----------+
select name from doutula where name like ‘%坏蛋%‘;
--34 rows in set (0.53 sec)
create index index_name on doutula (name);
select name from doutula where name like ‘%坏蛋%‘;
--34 rows in set (0.05 sec)

4.正确使用索引
  数据库表中添加索引后确实会让查询速度起飞,但前提必须是正确的使用索引来查询,如果以错误的方式使用,则即使建立索引也会不奏效。
即使建立索引,索引也不会生效:

技术分享图片
 - like %xx
            select * from tb1 where name like %cn;
        - 使用函数
            select * from tb1 where reverse(name) = wupeiqi;
        - or
            select * from tb1 where nid = 1 or email = seven@live.com;
            特别的:当or条件中有未建立索引的列才失效,以下会走索引
                    select * from tb1 where nid = 1 or name = seven;
                    select * from tb1 where nid = 1 or email = seven@live.com and name = alex
        - 类型不一致
            如果列是字符串类型,传入条件是必须用引号引起来,不然...
            select * from tb1 where name = 999;
        - !=
            select * from tb1 where name != alex
            特别的:如果是主键,则还是会走索引
                select * from tb1 where nid != 123
        - >
            select * from tb1 where name > alex
            特别的:如果是主键或索引是整数类型,则还是会走索引
                select * from tb1 where nid > 123
                select * from tb1 where num > 123
        - order by
            select email from tb1 order by name desc;
            当根据索引排序时候,选择的映射如果不是索引,则不走索引
            特别的:如果对主键排序,则还是走索引:
                select * from tb1 order by nid desc;
         
        - 组合索引最左前缀
            如果组合索引为:(name,email)
            name and email       -- 使用索引
            name                 -- 使用索引
            email                -- 不使用索引
View Code

  二、limit分页

技术分享图片
每页显示10条:
    当前 118 120125

    倒序:
                大      小
       970  7 6  6 5  54  43  32
    19 98     
    下一页:

        select 
            * 
        from 
            tb1 
        where 
            nid < (select nid from (select nid from tb1 where nid < 当前页最小值 order by nid desc limit 每页数据 *【页码-当前页】) A order by A.nid asc limit 1)  
        order by 
            nid desc 
        limit 10;



        select 
            * 
        from 
            tb1 
        where 
            nid < (select nid from (select nid from tb1 where nid < 970  order by nid desc limit 40) A order by A.nid asc limit 1)  
        order by 
            nid desc 
        limit 10;


    上一页:

        select 
            * 
        from 
            tb1 
        where 
            nid < (select nid from (select nid from tb1 where nid > 当前页最大值 order by nid asc limit 每页数据 *【当前页-页码】) A order by A.nid asc limit 1)  
        order by 
            nid desc 
        limit 10;


        select 
            * 
        from 
            tb1 
        where 
            nid < (select nid from (select nid from tb1 where nid > 980 order by nid asc limit 20) A order by A.nid desc limit 1)  
        order by 
            nid desc 
        limit 10;
View Code


  三、Python关于MySQL的API接口-pymysql模块  

pymsql是Python中操作MySQL的模块,其使用方法和py2的MySQLdb几乎相同。
安装:pip install pymysql

代码:

# -*- coding: utf-8 -*-
import pymysql #1.连接mysql conn = pymysql.connect(host=‘localhost‘,port=3306,user=‘root‘,password=‘0000‘,db=‘python3‘) # conn = pymysql.connect(host=‘localhost‘,user=‘root‘,password=‘0000‘,db=‘crawed‘,charset=‘utf8‘,cursorclass = pymysql.cursors.DictCursor,use_unicode=True) #2.创建游标 cursor = conn.cursor() # cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) #更改获取数据结果的数据类型 #3.创建表添加数据 # sql = ‘create table test(id int,name varchar(20))‘ # sql1 = "insert into test values (1,‘zhang‘),(2,‘fei‘);" # cursor.execute(sql) # cursor.execute(sql1) #4.查询数据 sql2 = ‘select * from test;‘ cursor.execute(sql2) # num = cursor.fetchone() # print(cursor.fetchone()) # print(cursor.fetchmany(2)) print(cursor.fetchall()) #5.scroll 控制游标位置 # print(cursor.fetchone()) # print(cursor.fetchone()) # cursor.scroll(-1,mode=‘relative‘) # 相对当前位置移动 # cursor.scroll(0,mode=‘absolute‘) # 相对绝对位置移动 # print(cursor.fetchone()) conn.commit() conn.close()

 

    四、事务     

事务指逻辑上的一组操作,组成这组操作的各个单元,要不全部成功,要不全部不成功。

数据库开启事务命令
-- start transaction 开启事务
-- Rollback 回滚事务,即撤销指定的sql语句(只能回退insert delete update语句),回滚到上一次commit的位置
-- Commit 提交事务,提交未存储的事务
-- savepoint 保留点 ,事务处理中设置的临时占位符 你可以对它发布回退(与整个事务回退不同)
转账实例:

select * from test;
+------+-------+--------+
| id | name | salary |
+------+-------+--------+
| 1 | zhang | 8000 |
| 2 | fei | 10000 |
| 3 | kobe | 8000 |
| 4 | james | 9000 |
+------+-------+--------+
4 rows in set (0.00 sec)
start transaction;
update test set salary=salary-5000 where id=2;
rollback;
update test set salary=salary-5000 where id=2;
update test set salary=salary+5000 where id=1;
commit;

savepoint

create table test2(id int PRIMARY KEY auto_increment,name VARCHAR(20)) engine=innodb;
INSERT INTO test2(name) VALUE ("alvin"),
                              ("yuan"),
                              ("xialv");



start transaction;
insert into test2 (name)values(‘silv‘);
select * from test2;
commit;


-- 保留点

start transaction;
insert into test2 (name)values(‘wu‘);
savepoint insert_wu;
select * from test2;



delete from test2 where id=4;
savepoint delete1;
select * from test2;


delete from test2 where id=1;
savepoint delete2;
select * from test2;

rollback to delete1;


select * from test2;

savepoint

事务特性

<1> 原子性(Atomicity):原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。

<2> 一致性(Consistency):事务前后数据的完整性必须保持一致。在事务执行之前数据库是符合数据完整性约束的,无论事务是否执行成功,事务结束后的数据库中的数据也应该是符合完整性约束的。在某一时间点,如果数据库中的所有记录都能保证满足当前数据库中的所有约束,则可以说当前的数据库是符合数据完整性约束的。
比如删部门表前应该删掉关联员工(已经建立外键),如果数据库服务器发生错误,有一个员工没删掉,那么此时员工的部门表已经删除,那么就不符合完整性约束了,所以这样的数据库也就性能太差啦!

<3>隔离性(Isolation):事务的隔离性是指多个用户并发访问数据库时,一个用户的事务不能被其它用户的事务所干扰,多个并发事务之间数据要相互隔离。

<4>持久性(Durability):持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响。

四个隔离级别:
Serializable:可避免脏读、不可重复读、虚读情况的发生。(串行化)
Repeatable read:可避免脏读、不可重复读情况的发生。(可重复读)不可以避免虚读
Read committed:可避免脏读情况发生(读已提交)
Read uncommitted:最低级别,以上情况均无法保证。(读未提交)

安全性考虑:Serializable>Repeatable read>Read committed>Read uncommitted
数据库效率:Read uncommitted>Read committed>Repeatable read>Serializable

一般情况下,我们会使用Repeatable read、Read committed mysql数据库默认的数据库隔离级别Repeatable read

mysql中设置数据库的隔离级别语句:set [global/session] transaction isolation level xxxx;

如果使用global则修改的是数据库的默认隔离级别,所有新开的窗口的隔离级别继承自这个默认隔离级别如果使用session修改,则修改的是当前客户端的隔离级别,和数据库默认隔离级别无关。当前的客户端是什么隔离级别,就能防止什么隔离级别问题,和其他客户端是什么隔离级别无关。
mysql中设置数据库的隔离级别语句:select @@tx_isolation;

数据库学习之MySQL进阶

原文:https://www.cnblogs.com/zhangyafei/p/9649259.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!