Mysql数据库原理和Sql优化

一、引擎介绍

1、ISAM

2、MyISAM

3、InnoDB：

InnoDB给Mysql提供了具有提交、回滚和崩溃恢复能力的事物安全存储引擎。InnoDB锁定在行级并且也在Select语句中提供一个oracle风格一致的非锁定读，增加了多用户部署和性能

特点：

• 支持事物安装
• 数据多版本读取
• 锁定机制的改进
• 实现外键

二、Innodb和myisam区别：

1、innodb 支持事物，MyISAM不支持，对于innoDB每一条SQL语言都封装成事物，自动提交，但是会影响速度

2、Innodb支持外键，而MyISAM不支持。对于包含外键的InnoDB转换为MyISAM

3、innoDB是聚集索引，数据文件和索引绑在一起，必须要有主键。

4、innoDB不保存具体行数，执行count(*)需要全表扫描，而MyISAM用一个变量保存了整个表的行数

5、innoDB不支持全文索引，而MyISAM支持，查询效率MyISAM更高

如何选择？

1、如果需要事物，选择innodb

2、如果只需要查询，可以使用myisam，如果有读有写，使用innoDB

3、系统崩溃后，MyISAM恢复更困难

Mysql5.5之后innoDB为默认引擎，如不知道用什么，就用innoDB

 

 

三、操作引擎

1、查看数据库支持的引擎

show engines：

2、查看数据库当前的默认引擎

show variables like '%storage_engine%'

3、查看数据表使用的引擎

show create table table_name

4、修改表的存储引擎

alter table table_name engine='MyISAM'

 

四、数据库索引

索引作用：

优点：

        1、通过创建唯一性索引，保证数据库表中每一行数据的唯一性

         2、加快数据检索速度

         3、加速表与表之间的链接

         4、使用分组、排序子句检索时，减少查询分组、排序的时间

缺点：

        1、创建索引与维护索引要耗费时间，时间随数据量增加而增加

        2、索引需要占用物理空间，除了数据表占用空间外，每一个索引还要占用一定的物理空间。

        3、对数据进行DML时，索引也需要动态维护，增加了维护成本，降低了维护速度

什么字段适合索引？

1、经常需要搜索的列

2、主键的列，强制该列唯一性和组织表中数据的排列结构

3、经常用于链接的列，主要是一些外键，可以加快链接速度

4、经常需要根据范围搜索的列，因为索引是排序的，指定的范围是连续的

5、经常需要排序的列、因为字段已经排好序，这样查询可以利用索引排序，加快查询速度

6、在经常使用在where子句上的列创建索引，加快判断速度

7、如果where a and b 则 a、b均需要创建索引

什么字段不适合？

1、查询次数很少的列，如果将此列设为索引，非但不能提高查询速度，反而增加维护成本，降低维护速度

2、对于只有很少数据值的列

3、对于text、bclob等大类型的列不应该增加索引，这些列数据量要么非常大、要么取值很少

4、当DML大于查询时不应该创建索引，降低修改性能。

索引算法：

B-Tree索引

Full-Text索引

 

五、创建索引

1、创建索引

直接创建

create index 索引名 on 表名（字段(length  ##字段的值取的长度)）

修改表结构方法添加索引

alter table 表名 add index 索引名 （字段(length)）

2、查询索引

show index from 表名

3、删除索引

drop index 索引名 on 表名

六、SQL优化

1、对查询进行优化，应避免全表扫描，首先考虑where 和order by 设计的列上创建索引

2、尽量避免where子句对字段进行null值判断，否则导致引擎放弃使用索引而使用全盘扫描

如：slect * from t where num is null

解决：可以在num字段上设置默认值0，确保num没有null 值

3、尽量避免where 使用 ！=或者<>操作符，否则引擎将放弃索引而进行全表扫描

Mysql只对<、<=、=、>、>=,Between in ，link 某些情况下使用索引

如：select * from t where name like 'aa%' 使用索引

   select * from t where name like '%aa'不适用索引

4、尽量必满where 子句中使用 or 来链接，否则导致引擎放弃索引而进行全表扫描

如：select * from t where num=10 or num = 20

解决：

select * from t where  num=10 union all seelct * from t where num=20

5、in 和not in 也要慎用

如：select* from t where num in (1,2,3)

解决：

如果连续的数值，能用between and 就不用in

select * from t where num between 1 and 3

6、like %11%也导致全表扫描

7、尽量避免在where 子句中对字段进行表达式操作

如：select * from t where num/2=100

解决：

select * from t where num=100*2

8、尽量避免where与剧中进行函数操作

9、不要再where 子句中=左边进行函数运算、算术运算等其他表达式运算

10、使用索引字段作为条件时，如果索引是符合索引，那么必须使用索引中第一字段作为条件才能保证系统使用该索引，否则该索引不会被使用，并且应尽可能让字段顺序和索引顺序想一致

11、不要写一些没有意义的查询

12、很多时候可以用exists 代替in

如：select num from t where num in (select num from b)

解决：

select num from t where num exists(select 1 from x where num=t.num)

13、并不是所有索引都有效、sql根据表中数据来进行查询优化

如果索引列有大量数据重复，sql查询可能不会利用索引

14、索引并非越多越好，在提高查询效率的同时也降低了DML的效率

15、尽量使用数字型字段，若只包含数值信息则尽量不要用字符型，这样会降低查询效率，因为字符类型需要每个类型都比对一次，而数值类型只需比对一次即可

16、尽可能使用varchar 代替 char，因为边长字段存储空间小，节省空间，其次对于查询来说，在一个相对于较小的字段内搜索，效率更高一些

17、任何地方都不要使用select * from t,用* 虽然简单方便，但是可能返回一些用不到的字段，影响效率