数据库的优化之索引！

最新推荐文章于 2023-06-06 00:14:04 发布

EasyChill

最新推荐文章于 2023-06-06 00:14:04 发布

阅读量767

点赞数 1

分类专栏：数据库

数据库专栏收录该内容

17 篇文章 0 订阅

订阅专栏

一、数据库的分类
- - - 关系型数据库
    - 非关系型数据库
二、数据库的引擎分类
三、索引优化数据库

网络资料散乱，查阅不便，所以做如下整理，内容出处均以标明，侵删！

一、数据库的分类

关系型数据库

关系型数据库通过外键关联来建立表与表之间的关系，另外，表和字段、数据和数据存在着关系

非关系型数据库

非关系数据库通常指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定

数据库类型	举例	优点	缺点	适用范围
关系型数据库	Mysql、Oracle	1、数据之间有关系，进行数据的增删改查的时候是非常方便的 2、关系型数据库是有事务操作的，保证数据的完整性和一致性，即ACID 3、易于维护：丰富的完整性(实体完整性、参照完整性和用户定义的完整性)大大减低了数据冗余和数据不一致的概率 4、支持SQL，可用于复杂的查询。	1、因为数据和数据是有关系的，底层是运行了大量的算法大量算法会降低系统的效率，会降低性能 2、面对海量数据的增删改查的时候会显的无能为力 3、海量数据对数据进行维护变得非常的无力	适合处理一般量级的数据，例如：银行转账等
非关系型数据库	redis、MangDB	1、海量数据的增删改查，维护和处理轻松 2、无需经过sql层的解析，读写性能很高 3、基于键值对，数据没有耦合性，容易扩展 4、存储数据的格式：nosql的存储格式是key,value形式、文档形式、图片形式等等，文档形式、图片形式等等，而关系型数据库则只支持基础类型	1、不提供sql支持，学习和使用成本较高； 2、无事务处理，附加功能bi和报表等支持也不好；	适合处理海量数据，保证效率，不一定安全，例如：微博数据等

二、数据库的引擎分类

以下内容转自：https://www.cnblogs.com/domi22/p/8059403.html

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。 $\color{red}{数据库的核心就是存储引擎}$

MyISAM存储引擎

MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物。
MyISAM主要特性有：
- 1、大文件（达到63位文件长度）在支持大文件的文件系统和操作系统上被支持
- 2、当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块自动完成
- 3、每个MyISAM表最大索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16
- 4、最大的键长度是1000字节，这也可以通过编译来改变，对于键长度超过250字节的情况，一个超过1024字节的键将被用上
- 5、BLOB和TEXT列可以被索引
- 6、NULL被允许在索引的列中，这个值占每个键的0~1个字节
- 7、所有数字键值以高字节优先被存储以允许一个更高的索引压
- 8、每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列，当INSERT和UPDATE操作的时候该列被更新，同时AUTO_INCREMENT列将被刷新。所以说，MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快
- 9、可以把数据文件和索引文件放在不同目录
- 10、每个字符列可以有不同的字符集
- 11、有VARCHAR的表可以固定或动态记录长度
- 12、VARCHAR和CHAR列可以多达64KB
  
  使用MyISAM引擎创建数据库，将产生3个文件。文件的名字以表名字开始，扩展名之处文件类型：frm文件存储表定义、数据文件的扩展名为.MYD（MYData）、索引文件的扩展名时.MYI（MYIndex）

InnoDB存储引擎

InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，InnoDB是默认的MySQL引擎。
InnoDB主要特点：
- 1、InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来，甚至在同一个查询中也可以混合
- 2、InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的
- 3、InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘文件）。这与MyISAM表不同，比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上
- 4、InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键
- 5、InnoDB被用在众多需要高性能的大型数据库站点上
  
  InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件

MEMORY存储引擎

MEMORY存储引擎将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问。
MEMORY主要特性有：
- 1、MEMORY表的每个表可以有多达32个索引，每个索引16列，以及500字节的最大键长度
- 2、MEMORY存储引擎执行HASH和BTREE缩影
- 3、可以在一个MEMORY表中有非唯一键值
- 4、MEMORY表使用一个固定的记录长度格式
- 5、MEMORY不支持BLOB或TEXT列
- 6、MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引
- 7、MEMORY表在所由客户端之间共享（就像其他任何非TEMPORARY表）
- 8、MEMORY表内存被存储在内存中，内存是MEMORY表和服务器在查询处理时的空闲中，创建的内部表共享
- 9、当不再需要MEMORY表的内容时，要释放被MEMORY表使用的内存，应该执行DELETE FROM或TRUNCATE TABLE，或者删除整个表（使用DROP TABLE）

MERGE存储引擎

Merge存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，merge表本身并没有数据，对merge类型的表可以进行查询，更新，删除操作，这些操作实际上是对内部的MyISAM表进行的。

三、索引优化数据库

mysql逻辑架构图

这里写图片描述
图片来自：http://www.mamicode.com

以下内容来自我同学的博客：https://blog.csdn.net/alan_gaohaodong/article/details/82525945

整理如下：

SQL优化

SQL优化，主要是优化索引
索引：相当于书的目录
索引：index是帮助MySQL高效获取数据的数据结构。索引是数据结构（树：B树、Hash树…）
索引的弊端：
- 1.索引本身很大，可以存放在内存/硬盘（通常为硬盘）
- 2.索引不是所有情况均适用：a.少量数据 b.频繁更新的字段 c.很少适用的字段
- 3.索引会降低增删改的效率
索引的优势：
- 提高查询效率（降低IO使用率）
- 降低CPU使用率（… order by age desc ,因为B树索引本身就是排好序的结构，在排序时即可直接使用）
为什么要优化sql
- 原因：性能低、执行时间长太长、等待时间太长、SQL语句欠佳（连接查询）、索引失效、服务器参数设置不合理（缓冲区、线程数）
- sql编写过程：
  
  select dinstinct .. from ..join .. on .. where .. group by .. having .. order by .. limit ..
- sql解析过程：
  
  from .. on .. join ..where .. group by .. having .. select dinstinct .. order by limit ..
索引分类
- 单值索引：单列，age；一个表可以有多个单只索引，name
- 主键索引：不能重复。Id 不能是null （如果不加索引，默认是主键索引）
- 唯一索引：不能重复。Id 可以是null
- 复合索引：多个列构成的所有（相当于二级索引： z: zhao）(name,age)(a,b,c,d,……)

创建索引：

创建的方式一：

create 索引类型索引名 on 表（字段）

单值：

create  index  dept_index  on  tb(dept);

唯一：

create  unique index  name_index  on  tb(name);

复合索引：

create  index  dept_name_index  on  tb(dept,name);

创建的方式二：

alter table 表名索引类型索引名（字段）

单值：

alter  table  tb  add  index  dept_index(dept);

唯一：

alter  table  tb  add  unique  index  name_index(name);

复合索引：

alter  table  tb  add  index  dept_name_index(dept,name);

注意：如果一个字段是primary key ,则改字段默认就是主键索引

删除索引：
- drop index 索引名 on 表名；
- drop index name_index on tb ;
查询索引：
- Show index from 表名;

创建索引以后，在查询时，就会有速度上的提升，由于机器原因，测试这项内容需要很大的数据量（上百万条）。网上有很多测试的实例，也可以直接去访问这里的第三点，比较具体的测试了数据优化的展示，测试数据量为300W。

优化的注意具体事项

若想利用索引达到预想的提高查询速度的效果，我们在添加索引时，必须遵循以下原则

1、最左前缀匹配原则，非常重要的原则，

create index ix_name_email on s1(name,email,)
- 最左前缀匹配：必须按照从左到右的顺序匹配
select * from s1 where name='egon'; #可以
select * from s1 where name='egon' and email='asdf'; #可以
select * from s1 where email='alex@oldboy.com'; #不可以
mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，
比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，
d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2、=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，

表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、
性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，
这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

4、索引列不能参与计算，保持列“干净”

比如from_unixtime(create_time) = ’2014-05-29’

就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，

但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。

所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

最左前缀示范

mysql> select * from s1 where id>3 and name='egon' and email='alex333@oldboy.com' and gender='male';
Empty set (0.39 sec)#sec是单位秒

mysql> create index idx on s1(id,name,email,gender); #未遵循最左前缀
Query OK, 0 rows affected (15.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from s1 where id>3 and name='egon' and email='alex333@oldboy.com' and gender='male';
Empty set (0.43 sec)


mysql> drop index idx on s1;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index idx on s1(name,email,gender,id); #遵循最左前缀
Query OK, 0 rows affected (15.97 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from s1 where id>3 and name='egon' and email='alex333@oldboy.com' and gender='male';
Empty set (0.03 sec)
最左前缀匹配
 2 index(id,age,email,name)
 3 #条件中一定要出现id(只要出现id就会提升速度)
 4 id
 5 id age
 6 id email
 7 id name
 8 
 9 email #不行  如果单独这个开头就不能提升速度了
10 mysql> select count(*) from s1 where id=3000;
11 +----------+
12 | count(*) |
13 +----------+
14 |        1 |
15 +----------+
16 1 row in set (0.11 sec)
17 
18 mysql> create index xxx on s1(id,name,age,email);
19 Query OK, 0 rows affected (6.44 sec)
20 Records: 0  Duplicates: 0  Warnings: 0
21 
22 mysql>  select count(*) from s1 where id=3000;
23 +----------+
24 | count(*) |
25 +----------+
26 |        1 |
27 +----------+
28 1 row in set (0.00 sec)
29 
30 mysql>  select count(*) from s1 where name='egon';
31 +----------+
32 | count(*) |
33 +----------+
34 |   299999 |
35 +----------+
36 1 row in set (0.16 sec)
37 
38 mysql>  select count(*) from s1 where email='egon3333@oldboy.com';
39 +----------+
40 | count(*) |
41 +----------+
42 |        1 |
43 +----------+
44 1 row in set (0.15 sec)
45 
46 mysql>  select count(*) from s1 where id=1000 and email='egon3333@oldboy.com';
47 +----------+
48 | count(*) |
49 +----------+
50 |        0 |
51 +----------+
52 1 row in set (0.00 sec)
53 
54 mysql>  select count(*) from s1 where email='egon3333@oldboy.com' and id=3000;
55 +----------+
56 | count(*) |
57 +----------+
58 |        0 |
59 +----------+
60 1 row in set (0.00 sec)

索引无法命中的情况需要注意：

- like '%xx'
    select * from tb1 where email like '%cn';


- 使用函数
    select * from tb1 where reverse(email) = 'wupeiqi';


- or
    select * from tb1 where nid = 1 or name = 'seven@live.com';


    特别的：当or条件中有未建立索引的列才失效，以下会走索引
            select * from tb1 where nid = 1 or name = 'seven';
            select * from tb1 where nid = 1 or name = 'seven@live.com' and email = 'alex'


- 类型不一致
    如果列是字符串类型，传入条件是必须用引号引起来，不然...
    select * from tb1 where email = 999;

普通索引的不等于不会走索引
- !=
    select * from tb1 where email != 'alex'

    特别的：如果是主键，则还是会走索引
        select * from tb1 where nid != 123
- >
    select * from tb1 where email > 'alex'


    特别的：如果是主键或索引是整数类型，则还是会走索引
        select * from tb1 where nid > 123
        select * from tb1 where num > 123

#排序条件为索引，则select字段必须也是索引字段，否则无法命中
- order by
    select name from s1 order by email desc;
    当根据索引排序时候，select查询的字段如果不是索引，则不走索引
    select email from s1 order by email desc;
    特别的：如果对主键排序，则还是走索引：
        select * from tb1 order by nid desc;

- 组合索引最左前缀
    如果组合索引为：(name,email)
    name and email       -- 使用索引
    name                 -- 使用索引
    email                -- 不使用索引


- count(1)或count(列)代替count(*)在mysql中没有差别了

- create index xxxx  on tb(title(19)) #text类型，必须制定长度
- 避免使用select *
- count(1)或count(列) 代替 count(*)
- 创建表时尽量时 char 代替 varchar
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引（经常使用多个条件查询时）
- 尽量使用短索引
- 使用连接（JOIN）来代替子查询(Sub-Queries)
- 连表时注意条件类型需一致
- 索引散列值（重复少）不适合建索引，例：性别不适合