3.MYSQL SQL语句优化 insert update count 主键 limit order by

insert优化

1.批量插入

推荐批量插入 一次1000条

insert into tb_user values (?),(?),(?)

2.手动提交事务

start transtcion;
insert ...
insert ...
insert ...
commit;

3.主键顺序插入

建议主键顺序插入

顺序插入:1,2,3,4,5,6,7,8,9
乱序插入:2,1,6,3,9,4 ....

• 大批量输入数据
  • 如果一次性需要插入大批量数据使用insert语句插入性能较低使用load指令插入
  • 

  • mysql --local-infile -uroot -p000000 
    
    mysql> show variables like 'local_infile';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | local_infile  | OFF   |
    +---------------+-------+
    1 row in set (0.01 sec)
    
    mysql> set global local_infile = 1;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select @@local_infile;
    +----------------+
    | @@local_infile |
    +----------------+
    |              1 |
    +----------------+
    1 row in set (0.00 sec)
    
    
    mysql> load data local infile '/root/root.log' into table tb_user fields terminated by ',' lines terminatted by '\n';
    Query OK, 999999 rows affected, 1 warning (19.87 sec)
    Records: 1000000  Deleted: 0  Skipped: 1  Warnings: 1
    
    
    mysql> select count(*) from tb_user;
    +----------+
    | count(*) |
    +----------+
    |   999999 |
    +----------+
    1 row in set (0.34 sec)
    

    生成数据脚本
    import random
    from datetime import datetime, timedelta
    
    # 生成随机用户名
    def generate_username():
        return ''.join(random.choices('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', k=10))
    
    # 生成随机密码
    def generate_password():
        return ''.join(random.choices('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890', k=10))
    
    # 生成随机姓名
    def generate_name():
        return ''.join(random.choices('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', k=5))
    
    # 生成随机生日
    def generate_birthday():
        start_date = datetime(1970, 1, 1)
        end_date = datetime(2000, 12, 31)
        random_date = start_date + timedelta(days=random.randint(0, (end_date - start_date).days))
        return random_date.strftime('%Y-%m-%d')
    
    # 生成随机性别
    def generate_sex():
        return random.choice(['M', 'F'])
    
    # 生成数据并写入文件
    def generate_data(filename, num_records):
        j=0
        with open(filename, 'w') as file:
            for _ in range(num_records):
                username = generate_username()
                password = generate_password()
                name = generate_name()
                birthday = generate_birthday()
                sex = generate_sex()
                data = f"{j},{username},{password},{name},{birthday},{sex}\n"
                j+=1
                file.write(data)
    
    # 调用函数生成数据
    generate_data('./root.log', 1000000)
    

  • 数据组织方式

  • 在InnoDB存储引擎中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表(index organized table IOT)

  • 主键顺序插入

  • 

    主键乱序插入

    页分裂现象

    当乱序插入的时候

    如果插入页中没有足够的内存插入

  • 会建立一个新的页将 1page多余50%的部分放到新的page

  • 在重新调整页的链表指针从新设置

  • 页合并

  • 当数据被删除的时候并不会从磁盘中删除而是 数据会被标识为删除状态,当删除的数据超过了设定的大小 就会从前面和后面查找是否有可能能有合并的页 发现后就会合并页的数据

  • 添加的新数据会被直接放到后放的页中

  • 

 

主键优化

乱序插入会导致列分裂照成性能变慢

尽量不要使用uuid或者其他主键,如身份证号等

业务操作时,避免对主键的修改

order by优化

"Using index"表示查询使用了索引，这意味着查询的条件中包含了索引字段，并且数据库可以使用索引来加速查询，而不需要全表扫描。
"Using filesort"表示查询需要对结果进行排序，但是无法使用索引来完成排序操作，因此需要在内存或者磁盘上进行临时排序操作，这可能会导致较慢的查询性能。
通常情况下，"Using index"是一个好的指标，表示查询可以利用索引进行快速访问。而"Using filesort"则可能需要进一步优化查询或者索引设计，以避免不必要的排序操作。


mysql> explain select id,age,phone from tb_user order by phone;
+----+-------------+---------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key                | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+---------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | tb_user | NULL       | index | NULL          | idx_user_age_phone | 47      | NULL |   25 |   100.00 | Using index; Using filesort |
+----+-------------+---------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

为索引创建倒序索引 
mysql> create index idx_user_age_pho_ad on tb_user(age asc,phone desc);
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

性能升级
mysql> explain select id,age,phone from tb_user order by age desc ,phone;
+----+-------------+---------+------------+-------+---------------+---------------------+---------+------+------+----------+----------------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key                 | key_len | ref  | rows | filtered | Extra                            |
+----+-------------+---------+------------+-------+---------------+---------------------+---------+------+------+----------+----------------------------------+
|  1 | SIMPLE      | tb_user | NULL       | index | NULL          | idx_user_age_pho_ad | 47      | NULL |   25 |   100.00 | Backward index scan; Using index |
+----+-------------+---------+------------+-------+---------------+---------------------+---------+------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)

构建的索引各不相同

group by优化

对统计字段增加索引后效率变高
mysql> explain select profession, count(*) from tb_user group by profession;
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys        | key                  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | index | idx_user_pro_age_sta | idx_user_pro_age_sta | 87      | NULL |   25 |   100.00 | Using index |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select profession, count(*) from tb_user group by profession;
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys        | key                  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | index | idx_user_pro_age_sta | idx_user_pro_age_sta | 87      | NULL |   25 |   100.00 | Using index |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
使用了临时组 性能不高 因为没有使用最左前缀法则
mysql> explain select age, count(*) from tb_user group by age;
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+------------------------------+
| id | select_type | table   | partitions | type  | possible_keys        | key                  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | tb_user | NULL       | index | idx_user_pro_age_sta | idx_user_pro_age_sta | 87      | NULL |   25 |   100.00 | Using index; Using temporary |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.10 sec)

limit

使用覆盖查询或者多表联查来查询数据比较好

count优化

自己计数 没太多优化的方法

count效率最高

update语句优化