sql为什么需要优化? 对于初学者来说,能够写出实现功能的SQL语句而不出错,查询出所需要的结果,就已经能够满足日常使用了。但在某些场景,对性能的要求比较高,因此,要求SQL的执行响应速度快,就需要对SQL进行一定程度的优化。 在
对于初学者来说,能够写出实现功能的SQL语句而不出错,查询出所需要的结果,就已经能够满足日常使用了。但在某些场景,对性能的要求比较高,因此,要求SQL的执行响应速度快,就需要对SQL进行一定程度的优化。
在实际应用场景中,Mysql经常会存在诸如性能低、执行时间过长、等待时间过长、SQL语句欠佳(尤其是连接查询)、索引失效、服务器参数设置不合理等问题,这时候就需要对SQL进行优化,从而达到我们所需要的的性能需求。
要对SQL进行优化,首先需要知道SQL的解析过程是什么样子的。在此之前,我们要明确SQL编写过程和解析过程的区别。
select [distinct] ... from ... join ... on ... where ... group by ... having ... order by ... limit ...;
from ... on ... join ... where ... group by ... having ... select [distinct] ... order by ... limit ...;
以上语法中sql关键字的含义,不是本文的重点,网络上有很多教程,此处不再说明,我们只需要知道,SQL的编写过程和实际解析过程并不是一致的。这点在后续的相关优化中将会进一步说明。
索引相当于字典的目录,其目的是帮助在mysql中更快的查询到所需要的数据。其本质是一种BTREE的数据结构。
所以可以得出一个结论:索引是一种数据结构。 如果您对数据结构有所了解,可以更明白的讲,索引是一种叫树的数据结构。树有很多种,如二叉树,哈希树等。索引是B树(和二叉树比较类似)。
举个例子说明:
假设我们有一张表student,其结构及其中的数据如下:
id | name | score |
---|---|---|
1 | zs | 75 |
2 | ls | 82 |
3 | ww | 62 |
4 | ll | 88 |
5 | wq | 77 |
6 | wb | 53 |
其中,score列是索引。那么,该索引的大致结构是如下图所示的样子:
因此,如果有这样一条SQL:
select score from student where score = 77;
如果没有索引,那么需要全表扫描,从第一条数据开始,需要到第5次才能查找到我们所需要的数据;而如果有了索引,则只需要3次就能查找到(75->62->77),由此可见,索引确实能够提升查询效率,尤其是当表中的数据量特别大,达到了百万级别,甚至千万级别的时候,索引的优势就更加明显。
Btree除了常见的二叉树,还有三叉树,三叉树的结构如下所示:
Btree一般指的都是B+树。实际上,索引的数据全部存储在叶节点中,这也就意味着,对于Btree中,查询任意数据的次数都是n次(n为树的深度)。
由于客户端和服务器之间主要是通过io,所以索引会大大降低IO的使用率,并且能一定程度的降低CPU的使用率。(比如SQL语句中有order by,由于索引的数据结构本身就是排好序的,所以直接省去了这一步,从而降低CPU使用率)。
索引固然有诸多好处,但也有一定的弊端:
主键索引和唯一索引的区别是:主键索引列的值不能为null,唯一索引列的值可以为null。
create 索引类型 索引名 on 表(字段)
alter table 表明 add 索引类型 索引名(字段)
drop index 索引名 on 表名
show index from 表名
以本文中的student表为例,加以说明:
mysql> desc student;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | char(20) | YES | | NULL | |
| score | double | YES | | NULL | |
+-------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
在student表上对name字段创建单值索引stu_idx1:
mysql> create index stu_idx1 on student(name);
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
在student表上对id字段创建唯一索引stu_idx2:
mysql> create unique index stu_idx2 on student(id);
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
在student表上对name,score字段创建复合索引stu_idx3:
mysql> create index stu_idx3 on student(name,score);
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
使用alter的方式对student表的score字段创建单值索引stu_idx4:
mysql> alter table student add index stu_idx4(score);
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
查看创建的索引:
mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 6 | NULL | NULL | | BTREE | |
|
| student | 0 | stu_idx2 | 1 | id | A | 6 | NULL | NULL | | BTREE | |
|
| student | 1 | stu_idx1 | 1 | name | A | 6 | NULL | NULL | YES | BTREE | |
|
| student | 1 | stu_idx3 | 1 | name | A | 6 | NULL | NULL | YES | BTREE | |
|
| student | 1 | stu_idx3 | 2 | score | A | 6 | NULL | NULL | YES | BTREE | |
|
| student | 1 | stu_idx4 | 1 | score | A | 6 | NULL | NULL | YES | BTREE | |
|
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.00 sec)
从以上表格,可以读出以下信息:
假如要删除索引stu_idx2和stu_idx4,则执行如下语句:
mysql> drop index stu_idx2 on student;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> drop index stu_idx4 on student;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
再次查询:
mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 6 | NULL | NULL | | BTREE | |
|
| student | 1 | stu_idx1 | 1 | name | A | 6 | NULL | NULL | YES | BTREE | |
|
| student | 1 | stu_idx3 | 1 | name | A | 6 | NULL | NULL | YES | BTREE | |
|
| student | 1 | stu_idx3 | 2 | score | A | 6 | NULL | NULL | YES | BTREE | |
|
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
--结束END--
本文标题: MySQL优化之索引
本文链接: https://www.lsjlt.com/news/5235.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-10
2024-05-10
2024-05-10
2024-05-10
2024-05-10
2024-05-10
2024-05-09
2024-05-09
2024-05-09
2024-05-09
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0