MySQL支持多种存储引擎,以适用于不同领域的数据库应用需要,用户可以根据需要进行选择甚至是定制自己的引擎以提高应用效率。

索引
视图
事务
视图的操作

1.索引index

索引在创建表的时候可以同时创建,也可以随时增加新的索引

  • 查询索引是否已创建
1
2
-- 如果没创建过的话应该默认有一条主键的索引(有主键的前提下)
select index_name from all_indexes where table_name = '你的表名'
  • 创建新索引的语法
1
2
create [unique|fulltext|spatial] index index_name [using index_type] 
on table_name( col_name [(length)] [asc|desc] )
  • 删除索引
1
DROP INDEX index_name on table_name

索引创建原则

  1. 在经常用作过滤器where的字段上建立索引

  2. 在SQL语句中经常进行GROUP BY、ORDER BY的字段上建立索引

  3. 在某个字段field的不同值较少(即散列度较小)应避免建立索引,如性别字段(只有男女两个参数)

  4. 对于经常存取的列应避免建立索引,即增删改查

  5. 表的主键、外键必须有索引

  6. 不应该在小表上(数据量很小的表)创建索引

  7. 索引应该建在选择性高的字段上

  8. 索引应该建在小字段上,对于大的文本字段甚至超长字段,对于定义为text、image和bit等数据类型的列应避免创建索引

  9. 经常与其他表进行连接(join,left join,right join)的表,在连接字段上应建立索引

  10. 删除无用的索引,避免对执行计划造成负面影响

  11. 确定针对该表的操作是大量的查询操作还是大量的增删改操作。若为后者,则应避免创建索引

索引作用

合理的创建索引是提高查询速度最有效的方式之一:

  • 在没有建立索引的情况下,查询一条数据的过程是这样:MySQL是从第一条记录开始查表,在某一个地方查到了匹配的数据,停止?NO,继续查询剩下的数据,直到查完整张表。

  • 在建立合适索引的情况下,查询一条数据的过程又是这样:MySQL先根据索引快速到达一个位置去搜索数据文件,然后查出数据,而不必查看整张表的数据。

tips:

  1. 索引的效率取决于索引列的值是否散列,即该列的值如果越互不相同,那么索引效率越高。
  2. 索引的优点是提高了查询效率,缺点是在插入、更新和删除记录时,需要同时修改索引,因此,索引越多,插入、更新和删除记录的速度就越慢。
  3. 通过创建唯一索引,可以保证某一列的值具有唯一性。
  4. 当一个表的索引达到4个以上时,ORACLE的性能可能还是改善不了,因为OLTP系统每表超过5个索引即会降低性能,而且在一个sql 中, Oracle 从不能使用超过 5个索引
  5. 索引可能产生碎片,因为记录从表中删除时,相应也从表的索引中删除.表释放的空间可以再用,而索引释放的空间却不能再用.频繁进行删除操作的被索引的表,应当阶段性地重建索引,以避免在索引中造成空间碎片,影响性能.在许可的条件下,也可以阶段性地truncate表,truncate命令删除表中所有记录,也删除索引碎片. (建立索引影响了删除和更新操作)
1
2
3
4
5
6
7
8
9
10
11
/*添加唯一索引:*/

ALTER TABLE students

ADD UNIQUE INDEX unique_name(name);

/*只添加唯一约束而不添加唯一索引:*/

ALTER TABLE students

ADD CONSTRAINT unique_name UNIQUE(name);

因此,为了提高查询速度,合理建立索引是非常有必要的。


类型 描述
普通索引 基本的索引类型,允许定义索引的列中插入重复值和空值
唯一索引 索引列值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一
主键索引 特殊的唯一索引,不允许有空值
全文索引 类型为FULLTEXT,支持值的全文查找,允许在索引列中插入重复值和空值
组合索引 在表的多个字段组合上创建的索引

索引执行情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 单一索引:Create Index <Index-Name> On <Table_Name>(Column_Name);

 复合索引:Create Index <Index-Name> On emp(deptno,job); —>在emp表的deptno、job列建立索引。

  select * from emp where deptno=66 and job='sals' ->走索引。

  select * from emp where deptno=66 OR job='sals' ->将进行全表扫描。不走索引

  select * from emp where deptno=66 ->走索引。

  select * from emp where job='sals' ->进行全表扫描、不走索引。

  如果在where 子句中有OR 操作符或单独引用Job 列(索引列的后面列) 则将不会走索引,将会进行全表扫描。
同时在Oracle里用PL/SQL的F5可以对整个SQL查询来判断没加索引前和加完索引后的用时。

索引失效情况

当索引失效的情况下时,数据库会进行全表扫描

  • 应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描
  • 应尽量避免在 where 子句中使用 != 或 <> 操作符,否则将引擎放弃使用索引而进行全表扫描
  • 索引列上不能进行数学计算 :SELECT Col FROM tbl WHERE col / 10 > 10 则会使索引失效,应该改成SELECT Col FROM tbl WHERE col > 10 * 10
  • 应尽量避免在 where 子句中使用 or 来连接条件,如果一个字段有索引,一个字段没有索引,将导致引擎放弃使用索引而进行全表扫描
  • 更多的索引失效情况

参考网址:

3.视图(5.0.1提供视图功能)

    视图是一种虚拟存在的表,它存储的是查询语句,显示出来的是查询的结果;更直白的说就是当我们需要从表中查询一些信息时需要编写相关SQL语句,将这些SQL语句存储为视图,那么我们调用这些视图的时候就相当于执行了SQL语句,从而可以得到想要的结果
eg:创建视图并执行查询操作
create view view_name as select qty,price,qty*price as value from temp;

视图有什么作用

1.视图就像是一个封装了很多功能的函数,我们把一系列复杂的查询语句存储为一个视图,这样在需要频繁使用这些语句时不必反复编写,直接使用视图代替即可

1
2
3
4
5
6
7
8
如果要频繁获取表user的name和表goods的name。就应该使用以下sql语句:
select a.name as username, b.name as goodsname from user as a, goods as b, ug as c where a.id=c.userid and c.goodsid=b.id;

但有了视图就不一样了,创建视图other。示例
create view other as select a.name as username, b.name as goodsname from user as a, goods as b, ug as c where a.id=c.userid and c.goodsid=b.id;

创建好视图后,就可以这样获取user的name和goods的name:
select * from other;

2.对数据库进行重构但仍不会影响程序运行

1
2
3
4
5
6
7
8
假如因为某种需求,需要将user表拆成表usera和表userb,该两张表的结构如下:
测试表:usera有id,name,age字段
测试表:userb有id,name,sex字段
这时如果程序端一直使用的sql语句是:select * from user;那就会报错提示该表不存在,这时要么去更改程序的查询语句要么就创建视图。显然,创建视图更简单,成本更低。
以下sql语句创建视图:
create view user as select a.name,a.age,b.sex from usera as a, userb as b where a.name=b.name;
以上假设name都是唯一的。
这时程序端端使用的sql语句:select * from user;就不会报错。这就实现了更改数据库结构,而不用更改脚本程序的功能。

4.视图的操作

4.1 创建或修改视图

create [ or replace ] [ algorithm = { undefined | merge | temptable } ] view view_name [ (column_list) ] as select_statement [ with [ cascaded | local ] check option ]

  • 使用了OR REPLACE可以修改视图,或者直接就CREATE OR REPLACE 替换为ALTER 即变成修改语法;
  • UNDEFINED:未定义指定算法;MERGE:更新视图表数据的同时会更新真实表的数据(默认);TEMPTABLE:只能查询不能更新;
  • CASCADED(默认):必须满足所有针对该视图的所有视图的条件才可以更新;LOCAL:只需满足本视图的条件就可以更新;
  • WITH CHECK OPTION:需要满足相关的检查条件才能进行更新
    1
    2
    3
    4
    5
    --常量视图
    create or replace view pi as select 3.1415926 as pi;

    --select中包含子查询
    create or replace view city_view as select (select city from city where city_id=1);

4.2 查看当前数据库下所有视图 show full tables where table_type like ‘view’;

5.优化SQL语句一般步骤

5.1 通过show status 查看各语句的频率

6.数据库Leetcode

1.求薪水第二高,不存在就返回null

1
2
3
select ifnull((select distinct(Salary) 
from Employee
order by Salary desc limit 1,1),null) as SecondHighestSalary;
  1. 超过5名学生的课(having用法)

    1
    2
    3
    4
    select class
    from courses
    group by class
    having count(distinct student) >= 5
  2. DATEDIFF是两个日期的天数差集

    1
    2
    3
    select a.Id as newId
    from Weather a left join Weather b
    on datediff(a.RecordDate,b.RecordDate)=1 where a.Temperature>b.Temperature

5.事务

事务:一个事务是由一条或多条sql语句构成,这一条或者多条sql语句要么全部执行成功,要么全部执行失败

默认情况下,每条单独的sql语句就是一个单独的事务。
例:银行转账,A要向B转账1000元,这需要两条sql语句:①A的账户减去1000元 ②B的账户加上1000元。
设想如果在第一条sql语句执行成功后,在执行第二条sql语句之前程序被中断了,那么B的账户没有增加而A的账户却减少了1000,这肯定不是我们想要的结果。
因此我们需要事务来解决此类问题。

1
2
3
4
5
6
-- 转账案例
SET autocommit = 0;
START TRANSACTION;
UPDATE account SET money=money-1000 WHERE NAME = 'A';
UPDATE account SET money=money+1000 WHERE NAME = 'B';
COMMIT;

查看MySQL的隔离级别

查看MySQL隔离级别

1
2
3
SELECT @@global.tx_isolation; 						
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;

修改MySQL隔离级别

1
2
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL 
{READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

事务的特性ACID

  • 原子性:事务中所有操作是不可分割的原子单位,事务中的所有操作要么全部执行成功,要么全部执行失败
  • 一致性:事务执行后,数据库状态与其他业务规则保持一致。如转账案例中,无论事务成功与否,参与转账的两个账户的金额之和应保持不变。
  • 隔离性:在并发操作中,不同事务之间是互相隔离的,不会互相干扰。
  • 持久性:一旦事务提交成功,事务中所有的数据都必须被持久化到数据库中,即使提交数据后数据库崩溃,在数据库重启时,也必须保证通过某种机制恢复数据。

MySQL中的事务

  1. 隐式事务:事务自动提交

没有明显的开始和结束标记
例如DML语句的 INSERT,UPDATE,DELETE 语句本身就是一条事务(一条DML增删改语句会自动提交一次事务,即执行sql语句后会即刻生效

  1. 显式事务:事务手动提交

具有明显的开始和结束标记,一般由多条sql语句组成,必须具有明显的开始和结束标记

  • 代码里使用事务的步骤:
  • 前需要先关闭自动提交
  • 然后开启事务,
  • 执行操作数据的SQL
  • 如果中间SQL语句抛异常了,就调rollback 回滚,将数据恢复到事务开始前的状态,事务结束
  • 如果中间SQL语句没有抛异常,就commit 提交,事务结束

显式事务用begin或start transaction开始事务后,若没有手动commit,或者遇错rollback回滚,执行后即使立马查询,所看见的数据都是临时的,并不是执行后真正写入磁盘里的数据。

PS:Oracle的事务默认是手动提交的,一定要注意

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# 查看事务自动提交的模式
show [session] variables like 'autocommit'; //会话级别
show global variables like 'autocommit'; // 全局级别

# 关闭自动提交:
set global autocommit=0;
set [session] autocommit=0;

# 开启自动提交:
set global autocommit=1;
set [session] autocommit=1;

# 设置事务【可选】(这些事务是互斥的,不能同时设置两个以上选项)
set transaction read only
set transaction read write
Set transaction isolation level read committed
Set transaction isolation level serializable

# 开启事务
begin;
或:
start transaction;

# 提交
commit;

# 回滚
rollback;

set transaction只对当前事务有效,事务终止,事务当前的设置将会失效

MySQL的事务隔离级别

  • Read Uncommitted(读未提交)

所有事务都可以看到其他未提交事务的执行结果,会产生脏读(读取未提交的数据),(存在脏读、不可重复读、幻读)

  • Read Committed(读提交)

一个事务只能看见已经提交事务所做的改变,会产生不可重复读问题,(解决脏读,存在不可重复读和幻读)

  • Repeatable Read(可重读)

这是MySQL的默认事务隔离级别,它确保同一事务的多个实例在并发读取数据时,会看到同样的数据行。不过理论上,这会导致另一个棘手的问题:幻读 (Phantom Read),(默认,解决脏读,不可重复读,存在幻读)

  • Serializable(串行化)

这是最高的隔离级别,读加共享锁,写加排他锁,读写互斥,从而解决幻读问题。在这个级别,可能导致大量的超时现象和锁竞争,如果业务并发的特别少,同时又要求数据及时可靠的话,可以使用。(解决所有并发问题,但效率较低)

隔离级别产生的问题

  • 脏读(Dirty Read):某个事务已更新一份数据,另一个事务在此时读取了同一份数据,由于某些原因,前一个RollBack了操作,则后一个事务所读取的数据不正确了
  • 不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致,这可能是两次查询过程中间插入了一个事务更新的原有的数据。
  • 幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致,例如有一个事务查询了几列(Row)数据,而另一个事务却在此时插入了新的几列数据,先前的事务在接下来的查询中,就会发现有几列数据是它先前所没有的。

参考文章


 评论

联系我 | Contact with me

Copyright © 2019-2020 谁知你知我,我知你知深。此恨经年深,比情度日久

博客内容遵循 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 协议