MySQL数据库及其优化

╰半夏微凉° 2022-04-24 13:08 150阅读 0赞

名词解释：mysqlD（……BMS）  
数据库管理系统

### 文章目录 ###

*  从数据库存储引擎说起：
 *   *   *  ==从引擎分析做数据库优化==：
         *   *   *  存储引擎管理
 *  索引
 *   *   *   *   *  索引的缺点：
                 *  MySQL中的索引种类
                 *  索引管理
         *  ==从索引做数据库优化==：
         *  ==SQL优化==：

# 从数据库存储引擎说起： #

存储引擎会影响数据库的性能和功能。

存储引擎建立在表之上

*  ISAM  
    不支持事务，不能容错，所以（查询）速度飞快
 *  MyISAM  
    ISAM扩展，5.5版本之前。增加索引、字段管理和一些工具，*对于每个表都生成3个文件*，所以（查询）速度快。
 *  InnoDB  
    支持事务和外建，所以没前两个快。如今的默认引擎。  
    *InnoDB将表和索引信息存在一个文件*（5.7以后分为两个存表结构和索引），索引为聚集索引  
    可以和其他两个引擎的表进行连接

### 从引擎分析做数据库优化： ###

1.拥有事务的引擎慢，所以最好把多条SQL 语言放在begin transaction 和commit 之间，组成一个事务；  
2.InnoDB是聚集索引，辅助索引找主键，主键找数据，  
主键很重要，所以主键名字要起的短  
3.InnoDB不保存具体的行，所以count(\*)是整表查询。  
MyISAM有个文件保存了表信息。  
4.多引擎表连接

##### 存储引擎管理 #####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5MzUwMQ_size_16_color_FFFFFF_t_70]

# 索引 #

##### 索引的缺点： #####

*  创建和维护耗时，所以增删改慢
 *  占用物理空间

##### MySQL中的索引种类 #####

1.  B-Tree索引  
    广（宽）度换深度  
    每个节点中存：以此为根节点树关键字个数，关键字，父节点指针，子节点指针。
2.  Full-text索引（全文索引）  
    结构也是B-Tree，主要解决部分like查询低效问题。  
    （只能解决’xxx%’的like 查询。如：字段数据为ABCDE，索引建立为- A、AB、ABC、ABCD、ABCDE 五个。）

##### 索引管理 #####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5MzUwMQ_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5MzUwMQ_size_16_color_FFFFFF_t_70 2]

### 从索引做数据库优化： ###

1.  经常搜索的列加索引（where后面的条件列都要加索引才有效）
2.  经常用在连接上的列加索引（外建）
3.  因为索引会自动排序，所以需要排序、查找范围的字段加索引。
4.  值很少的列不加索引（性别、部门）
5.  表主要用于增删改时不加索引
6.  只有使用like“a%”这种才能用全文索引。对于较大数据集，先插入数据，再添加全文索引。如果容量特别大，不建议使用全文索引。
7.  包含null的列会让索引失效，可以创建的时候加默认值
8.  不要在列上计算，会让索引失效
9.  查询只能使用一个索引，既有where又有order by会让索引失效。
10. or会让索引失效
11. 对字段做计算和函数都会使索引失效（用其他方式写sql）

### SQL优化： ###

1.  避免全表扫描，避免使用\*
2.  查记录数不用count（\*），用字段，因为有索引
3.  记录少的表放from的最后面（查询机制），交叉最多的表放后面。
4.  查询记录多的条件放where的最后面。
5.  减少子查询
6.  使用内部函数
7.  exists替代in
8.  表起别名

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5MzUwMQ_size_16_color_FFFFFF_t_70]: /images/20220215/cc89538a81594307a8450adb764c4663.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5MzUwMQ_size_16_color_FFFFFF_t_70 1]: /images/20220215/1b08d1f44fe24171a015117c47924579.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5MzUwMQ_size_16_color_FFFFFF_t_70 2]: /images/20220215/c1cfbe350a7a4aa1815ecb34e7009921.png