范式
第一范式 ----- 遵循原子性
原子性:原子性即操作不可再分。
第二范式----- 唯一性
数据库中的数据要有唯一性。
第三范式 ----- 减少冗余
冗余:重复的数据 , 减少冗余 尽量的不要出现重复的数据,并不能完全消除冗余。
数据库表的设计
1 对 1 : 一张表 二张表 (user_login user_info )
1对N : 二张表 通过外键维护关系。外键添加到多的一方(student grade )
N对N : 三张表 通过中间表维护关系。 (student teacher)
SQL优化
慢SQL
优化SQL的第一件事情就是定位慢查询。
slow_query_log
慢查询开启状态
slow_query_log_file
慢查询日志存放的位置(这个目录需要
MySQL
的运行帐号的可写权限,
一般设置为
MySQL
的数据存放目录)
long_query_time
查询超过多少秒才记录
查看慢查询相关参数
show variables like 'slow_query%';
show variables like 'long_query_time';
.设置慢查询参数
方法一:全局变量设置(临时性的设置,mysql服务器重启会失效)
将 slow_query_log 全局变量设置为“ON”状态
set global slow_query_log='ON';
set global slow_query_log_file='/var/lib/mysql/linux-base-slow.log';
set global long_query_time=1;
方法二:配置文件设置
修改配置文件my.cnf,在[mysqld]下的下方加入
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/linux-base-slow.log
long_query_time = 1
重启MySQL服务
service mysqld restart
我们可以通过这个SQL语句,模拟慢查询
SELECT SLEEP(15)
在日志文件中记录的慢查询sql
![](https://i-blog.csdnimg.cn/blog_migrate/b05f704ba119802456362353f8c6abe4.png)
索引
索引是帮助数据库高效获取数据的排好序的数据结构
索引分类:主键索引、唯一索引、普通索引、全文索引、组合索引
主键索引:即主索引,根据主键pk_clolum(length)建立索引,不允许重复,不允许空值
唯一索引:用来建立索引的列的值必须是唯一的,允许空值
普通索引:用表中的普通列构建的索引,没有任何限制
全文索引:用大文本对象的列构建的索引
组合索引:用多个列组合构建的索引,这多个列中的值不允许有空值
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
[index_type]
ON
tbl_name
(key_part,...)
[index_option]
[algorithm_option | lock_option] ...
key_part:
col_name [(length)] [ASC | DESC]
执行计划explain
使用
EXPLAIN
关键字可以模拟优化器执行
SQL
查询语句,从而知道
MySQL
是如何处理你的
SQL
语句
的。分析你的查询语句或是表结构的性能瓶颈。 通过
explain
我们可以获得以下信息:
表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询
explain select * from tb_test where c1=
'A0100'
explain select * from tb_test where c2=
'A0100'
索引的实现原理
MySQL支持诸多存储引擎,而各种存储引擎对索引的支持也各不相同,因此MySQL数据库支持
多种索引类型,如
BTree索引,B+Tree索引,哈希索引,全文索引
等等
哈希索引:
只有memory(内存)存储引擎支持哈希索引,哈希索引用索引列的值计算该值的hashCode,
然后在hashCode相应的位置存执该值所在行数据的物理位置,因为使用散列算法,因此访问速
度非常快,但是一个值只能对应一个hashCode,而且是散列的分布方式,因此哈希索引不支持
范围查找和排序的功能。
全文索引:
FULLTEXT(全文)索引,仅可用于MyISAM和InnoDB,针对较大的数据,生成全文索引非常
的消耗时间和空间。对于文本的大对象,或者较大的CHAR类型的数据,如果使用普通索引,那
么匹配文本前几个字符还是可行的,但是想要匹配文本中间的几个单词,那么就要使用LIKE
%word%来匹配,这样需要很长的时间来处理,响应时间会大大增加,这种情况,就可使用时
FULLTEXT索引了,在生成FULLTEXT索引时,会为文本生成一份单词的清单,在索引时及根据
据行。如果同时出现using where,表名索引被用来执行索引键值的查找;如
果没有同时出现using where,表名索引用来读取数据而非执行查询动作。
4、Using where :表明使用where过滤
5、using join buffer:使用了连接缓存
6、impossible where:where子句的值总是false,不能用来获取任何元组
7、select tables optimized away:在没有group by子句的情况下,基于索
引优化Min、max操作或者对于MyISAM存储引擎优化count(
*),不必等到
执行阶段再进行计算,查询执行计划生成的阶段即完成优化。
8、distinct:优化distinct操作,在找到第一匹配的元组后即停止找同样值
的动作。
这个单词的清单来索引。FULLTEXT可以在创建表的时候创建,也可以在需要的时候用ALTER
或者CREATE INDEX来添加
BTree索引
BTree是平衡搜索多叉树,设树的度为2d(d>1),高度为h,那么BTree要满足以一下条件:
每个叶子结点的高度一样,等于h;
每个非叶子结点由n-1个key和n个指针point组成,其中d<=n<=2d,key和point相互间隔,结
点两端一定是key;
叶子结点指针都为null;
非叶子结点的key都是[key,data]二元组,其中key表示作为索引的键,data为键值所在行的
数据;
B+Tree索引
B+Tree是BTree的一个变种,设d为树的度数,h为树的高度,B+Tree和BTree的不同主要在于:
B+Tree中的非叶子结点不存储数据,只存储键值;
B+Tree的叶子结点没有指针,所有键值都会出现在叶子结点上,且key存储的键值对应data
数据的物理地址;
B+Tree的每个非叶子节点由n个键值key和n个指针point组成
**带顺序索引的B+TREE****
很多存储引擎在B+Tree的基础上进行了优化,
添加了指向相邻叶节点的指针
,形成了带有顺序
访问指针的B+Tree,这样做是为了
提高区间查找的效率
,只要找到第一个值那么就可以顺序的
查找后面的值。
聚簇索引和非聚簇索引
MyISAM——非聚簇索引
MyISAM存储引擎采用的是非聚簇索引,非聚簇索引的主索引和辅助索引几乎是一样的,只
是主索引不允许重复,不允许空值,他们的叶子结点的key都存储指向键值对应的数据的物
理地址。
非聚簇索引的数据表和索引表是分开存储的。
非聚簇索引中的数据是根据数据的插入顺序保存。因此非聚簇索引更适合单个数据的查询。
插入顺序不受键值影响。
只有在MyISAM中才能使用FULLTEXT索引。(mysql5.6以后innoDB也支持全文索引)
InnoDB——聚簇索引
聚簇索引的主索引的叶子结点存储的是键值对应的数据本身,辅助索引的叶子结点存储的是
键值对应的数据的主键键值。因此主键的值长度越小越好,类型越简单越好。
聚簇索引的数据和主键索引存储在一起。
聚簇索引的数据是根据主键的顺序保存。因此适合按主键索引的区间查找,可以有更少的磁
盘I/O,加快查询速度。但是也是因为这个原因,聚簇索引的插入顺序最好按照主键单调的
顺序插入,否则会频繁的引起页分裂,严重影响性能。
在InnoDB中,如果只需要查找索引的列,就尽量不要加入其它的列,这样会提高查询效率。