# 数据库优化及索引

最新推荐文章于 2024-05-20 20:26:39 发布

lxb_10323

最新推荐文章于 2024-05-20 20:26:39 发布

阅读量807

点赞数 25

分类专栏：数据库文章标签：数据库

本文链接：https://blog.csdn.net/lxb_10323/article/details/135751828

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

如何优化数据库

第一、优化索引、SQL语句、分析慢查询
第二、设计表的时候严格按照数据库的设计范式来设计数据库
第三、我们可以加上redis缓存，将经常被访问到的数据，但是不需要经常变化的数据放入至redis缓存服务器里面，这样的话能够节约太强盘I/0
第四、还可优化硬件，在硬件层面，我们可以使用更好的一些硬盘（固态硬盘），使用一些磁盘阵列技术（raid 0,raid1,raid5）
raid0 最简单的（两块硬盘硬盘相加100G + 100 G = 200G）
raid1 镜像卷把同样的数据写两份。可以随机从A/B里面读取，更高。硬盘坏了一块，数据也不会丢失
raid5 3块硬盘，坏了一块，另外两块也能工作。
第五、如果以上都做速度还是慢，先不要去切分可以使用MySQL内部自带的表分区技术，来将数据分成不同的文件。这样能够让磁盘在读取的时候效率更高。
第六、可以再做垂直分表，可以将不经常读数据放到另外一个表里面去。这样能够节约磁盘IO
第七、如果发现我们的效率还是不够高，我们可以采用主从的方式来将数据读写分离。
第八、数据量特别大，我们优化起来会很困难可以使数数据库中间件的方式，将数据进行分库分表分机器。（原理，数据路由）
第九、此外，我们还可以采用一些更快的存储方式，例如NoSQL来存储一些我们需要经常访问到的数据。从数据库里面取出来后，再到NoSQL取出一些其他的数据。
第十、此外还有一些表引擎选择，参数优化和一些相关小技巧都是优化MySQL的方式
第十一、我们还可以将我们的业务系统在架构级别进行缓存，静态化和分式式。
第十二、不使用全文索引，使用xunsearch，ES或者云服务器上的索引。

如何优化SQL语句—五星重要

1)尽量选择较小的列–需要哪些列就使用哪些列长度较小的列
2)将where中用的比较频繁的字段建立索引–有多个条件，使用索引的列排在前面
3)select子句中避免使用‘*’
4)避免在索引列上使用计算、not in 和<>等操作
5)当只需要一行数据的时候使用limit 1
6)保证单表数据不超过200W，适时分割表。针对查询较慢的语句，可以使用explain 来分析该语句具体的执行情况。
7)避免改变索引列的类型。
8)选择最有效的表名顺序，from子句中写在最后的表是基础表，将被最先处理，在from子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。
9)避免在索引列上面进行计算。
10)尽量缩小子查询的结果

索引类型

1.普通索引
是最基本的索引，它没有任何限制。它有以下几种创建方式：
（1）直接创建索引

CREATE INDEX index_name ON table(column(length))

（2）修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

2.唯一索引
与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
（1）创建唯一索引

CREATE UNIQUE INDEX indexName ON table(column(length))
create index index_name on table(name,city,age)

（2）修改表结构

ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

3.主键索引
是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
);

4.组合索引
指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age); 
select * from table where age=20  索引未生效
select * from table where city='大连' and age=20   索引未生效
select * from table where name=''
select * from table where name='' and city=''

5.全文索引
主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

索引的存储结构

\1. B-Tree索引

它使用B-Tree数据结构来存储数据，实际上很多存储引擎使用的是B+Tree。B+Tree和B-Tree的不同点在于：

(1) 非叶子节点只存储键值信息

(2) 所有叶子节点之间都有链指针

(3) 数据记录都存放在叶子节点中

B-Tree模型：

B+Tree模型：

B-Tree对索引列是顺序组织存储的，很适合查找范围数据。看下B-Tree索引适用哪些查询：

(1) 全值匹配

全值匹配是指查询中使用的条件和索引中的所有列进行匹配。比如有索引 index(last_name, first_name, date) ,查询的条件为：where last_name=? and first_name=? and date=? 。

(2) 匹配最左前缀

只使用索引的第一列。比如查询条件为：where last_name = ?

(3) 匹配列前缀

只匹配某一列值得开头部分。

(4) 匹配范围值

(5) 精确匹配某一列并范围匹配另一列

比如：where last_name = ? and first_name like kim%

(6) 只访问索引的查询

\2. 哈希索引

哈希索引基于哈希表实现。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。在mysql中，Memory引擎显式支持哈希索引。

举例说明，有张表：

CREATE TABLE testhash (

fname VARCHAR(50) NOT NULL,

lname VARCHAR(50) NOT NULL,

KEY USING HASH(fname)

) ENGINE=MEMORY;

表中有如下数据：

fname	lname
Arjen	Lentz
Baron	Schwartz
Peter	Zaitsev
Vadim	Tkachenko

假设索引使用假想的哈希函数f(), 如下：

f(‘Arjen’) = 2323

f(‘Baron’) = 7437

f(‘Peter’) = 8784

f(‘Vadim’) = 2458

那么哈希索引的数据结构：

槽（Slot）值（Value）

2323 指向第1行的指针

2458 指向第4行的指针

7437 指向第2行的指针

8784 指向第3行的指针

如果使用一条sql语句进行查询：select lname from testhash where fname = ‘Peter’; 过程为：

计算‘Peter’的哈希值(f(‘Peter’)=8784)----->查找哈希值对应的指针----->根据指针或得相应的数据。

哈希索引的特点：索引的结构十分紧凑，因此查找速度非常快；哈希索引数据不是按照索引顺序存储的，无法用于排序；哈希索引不支持部分索引列匹配查找；哈希索引只支持等值比较查询(=,IN(),<=>)，不支持范围查询，如where price>100;

全文索引

全文索引查找的是文本中的关键词，不是直接比较索引中的值。

聚簇索引和非聚簇索引

聚簇索引与非聚簇索引是从文件存储的角度进行划分：

聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据（InnoDB）
非聚簇索引：将数据和索引进行分开存储（MyISAM）
聚簇索引

一个表仅有一个聚簇索引，默认是主键

聚簇索引性能最好而且具有唯一性，所以非常珍贵，必须慎重设置。一般要根据这个表最常用的SQL查询方式来进行选择，某个字段作为聚簇索引，或组合聚簇索引，这个要看实际情况。

在这里插入图片描述

聚簇索引不是一种索引类型，而是一种数据存储方式。innoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。当表有聚簇索引时，它的行数据实际上存放在索引的叶子页中，因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

优点：

可以把相关数据保存在一起，数据访问就更快。
聚簇索引将索引和数据保存在同一个B-Tree中，因此获取数据比非聚簇索引要更快。
使用聚簇索引扫描的查询可以直接使用页节点中的主键值。

缺点：

聚簇索引最大程度提高了IO密集型应用的性能，如果数据全部在内存中将失去优势。
更新聚簇索引列的代价很高，因为会强制每个被更新的行移动到新位置。
基于聚簇索引的表插入新行或主键被更新导致行移动时，可能导致页分裂，表会占用更多磁盘空间。
当行稀疏或由于页分裂导致数据存储不连续时，全表扫描可能很慢

索引失效

索引查询失效的几个情况：

1、like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。

2、or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效

3、组合索引，不是使用第一列索引，索引失效。

4、数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。

5、在索引列上使用 IS NULL 或 IS NOT NULL操作。索引是不索引空值的，所以这样的操作不能使用索引，可以用其他的办法处理，例如：数字类型，判断大于0，字符串类型设置一个默认值，判断是否等于默认值即可。（此处是错误的！）

解释以上错误：

此处我将重新创建一个emp表

创建新的索引

查看索引

执行SQL语句

由此可发现有使用到索引

总结：在索引列上使用 IS NULL 或 IS NOT NULL操作，索引不一定失效！！！

使用联合查询请参考：https://www.jianshu.com/p/3cae3e364946

错误详解请参考：https://mp.weixin.qq.com/s/CEJFsDBizdl0SvugGX7UmQ

6、在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。优化方法： key<>0 改为 key>0 or key<0。

7、对索引字段进行计算操作、字段上使用函数。（索引为 emp(ename,empno,sal)）

8、当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。

索引失效分析工具：

可以使用explain命令加在要分析的sql语句前面，在执行结果中查看key这一列的值，如果为NULL，说明没有使用索引。

索引的缺点

1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大

3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度

lxb_10323

关注

25
点赞
踩
18

收藏

觉得还不错? 一键收藏
0
评论
# 数据库优化及索引

使用联合查询请参考：https://www.jianshu.com/p/3cae3e364946错误详解请参考：https://mp.weixin.qq.com/s/CEJFsDBizdl0SvugGX7UmQ6、在索引字段上使用not，<>，!不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。优化方法： key<>0 改为 key>0 or key<0。7、对索引字段进行计算操作、字段上使用函数。（索引为 emp(ename,empno,sal)）
复制链接

扫一扫