常见sql优化

朱雀 2024-02-19 22:12 57阅读 0赞

**常见sql优化**  
1.建立索引（where，group by 经常使用到的字段）

*  在建表的时候  
    INDEX (fileuploadercode)
 *  表已建立  
    Alter table table\_name add index(字段1，字段2.。。。)  
    删除索引：drop index index\_name on table\_name  
    或者 alter table table\_name drop index index\_name

2.尽量避免使用select \*，返回无用的字段会降低查询效率。如下：

SELECT * FROM t

优化方式：使用具体的字段代替\*，只返回使用到的字段。

3.尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE username LIKE '%li%'

优化方式：尽量在字段后面使用模糊查询。如下：

SELECT * FROM t WHERE username LIKE 'li%'

4.尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE score IS NULL

优化方式：可以给字段添加默认值0，对0值进行判断。如下：

SELECT * FROM t WHERE score = 0

5.尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t2 WHERE score/10 = 9
    SELECT * FROM t2 WHERE SUBSTR(username,1,2) = 'li'

优化方式：可以将表达式、函数操作移动到等号右侧。如下：

SELECT * FROM t2 WHERE score = 10*9
    SELECT * FROM t2 WHERE username LIKE 'li%'

6.索引不适合键值较少的列（重复数据较多的列）

假如索引列TYPE有5个键值，如果有1万条数据，那么 WHERE TYPE = 1将访问表中的2000个数据块。
    
    　　再加上访问索引块，一共要访问大于200个的数据块。
    
    　　如果全表扫描，假设10条数据一个数据块，那么只需访问1000个数据块，既然全表扫描访问的数据块
    
    　　少一些，肯定就不会利用索引了。

7.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)，要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

8.MySQL主要提供2种方式的索引：B-Tree索引，Hash索引

B树索引具有范围查找和前缀查找的能力，对于有N节点的B树，检索一条记录的复杂度为O(LogN)。相当于二分查找。
    
    　　哈希索引只能做等于查找，但是无论多大的Hash表，查找复杂度都是O(1)。
    
    　　显然，如果值的差异性大，并且以等值查找（=、 <、>、in）为主，Hash索引是更高效的选择，它有O(1)的查找复杂度。
    
    　　如果值的差异性相对较差，并且以范围查找为主，B树是更好的选择，它支持范围查找。