MySQL的优化(一) —— 索引优化

- 日理万妓 2022-02-04 15:57 220阅读 0赞

## 索引的优点 ##

*  大大加快数据的查询速度
 *  使用分组和排序进行数据查询时，可以显著减少查询时分组和排序的时间
 *  创建唯一索引，能够保证数据库表中每一行数据的唯一性
 *  在实现数据的参考完整性方面，可以加速表和表之间的连接

## 索引的缺点 ##

*  创建索引和维护索引需要消耗时间，并且随着数据量的增加，时间也会增加
 *  索引需要占据磁盘空间
 *  对数据表中的数据进行增加，修改，删除时，索引也要动态的维护，降低了维护的速度

## 创建索引的原则 ##

*  更新频繁的列不应设置索引
 *  数据量小的表不要使用索引（毕竟总共2页的文档，还要目录吗？）
 *  重复数据多的字段不应设为索引（比如性别，只有男和女，一般来说：重复的数据超过百分之15就不该建索引）
 *  首先应该考虑对where 和 order by 涉及的列上建立索引

补充：确认索引是否已使用：explain select username from user where id=1

*结果为：*

![aHR0cHM6Ly9pbWFnZXMyMDE3LmNuYmxvZ3MuY29tL2Jsb2cvMTMyMzMyNC8yMDE4MDEvMTMyMzMyNC0yMDE4MDEyNTE2MzM1NDk0NC05MDQ1NTM5NzgucG5n][]

### 字段说明： ###

*  **select\_type**  simple表示简单查询 还有其他如primary，union，subquery等
 *  **table**  表名
 *  **partitions**  匹配的分区
 *  **type**  引擎在表中找到所需行的方式 由差到好为：all（全表扫描），index（只遍历索引树），range（索引范围扫描，常见于between，>，< 等查询中），ref（非唯一性索引扫描），eq\_ref（唯一性索引扫描），const / system（当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问），null（MySQL在优化过程中分解语句，执行时甚至不用访问表或索引）
 *  **possible\_keys**  可供选择的索引
 *  **key**  使用的索引
 *  **key\_len**  索引字节数的长度，数值越小，运行速度越快
 *  **ref**  连接匹配条件，即哪些列或常量被用于查找索引列上的值
 *  **rows**  返回的数据行数
 *  **filtered**  被表条件过滤的行数的百分比
 *  **extra**  额外信息  类型： using index（表示select操作中使用了覆盖索引），using where（mysql服务器在存储引擎受到记录后进行“后过滤“），using temporary（表示mysql需要使用临时表来存储结果集，常见于排序和分组查询）， using filesort（mysql中无法使用索引完成的排序操作，成为“文件排序”）

注意： 通过key就能判断索引是否执行

## 优化mysql查询语句 ##

*  不要在where条件语句 '=' 的左边进行函数，运算符或表达式的计算，如 select name from tb\_user where age/2=20，因为索引不会生效（引擎会放弃使用索引，进行全表扫描）
 *  不要使用 <>，！=，not in ，因为索引不会生效
 *  避免对字段进行null的判断，因为索引不会生效（可以用一个值代替null，如-999）
 *  使用like模糊查询时，like '%xx%'会导致索引不生效，like 'xx%' 索引能够被使用，所以避免使用第一种
 *  避免使用or，可以用union替代（要想使用or，又让索引生效，or条件中的每个列都必须加上索引）
 *  使用exist代替in（表中数据越多，exist的效率就比in要越大）
 *  数据类型隐形转换，索引不会生效：如 select name from user where phone=13155667788;（phone字段在数据库中为varchar类型，应改成 phone='13155667788'）
 *  联合索引必须要按照顺序才会生效：如创建的索引顺序为a,b，where a="xx" and b="xx" 生效，但 b="xx" and a="xx" 则不会生效，补充：a="xx" 没有后面的，索引也会生效
 *  尽量避免使用游标（游标效率低）
 *  不要使用 select \* 
 *  还有很多...

# #

## 在已经存在的表上创建索引 ##

## 删除索引 ##

1.  alter table table\_name drop index index\_name
2.  drop index index\_name on table\_name

\--------------------------------------------------------------------------------

作者：[羽觞醉月][Link 1]

来源：博客园

链接：[https://www.cnblogs.com/yushangzuiyue/p/8352796.html][https_www.cnblogs.com_yushangzuiyue_p_8352796.html]

[aHR0cHM6Ly9pbWFnZXMyMDE3LmNuYmxvZ3MuY29tL2Jsb2cvMTMyMzMyNC8yMDE4MDEvMTMyMzMyNC0yMDE4MDEyNTE2MzM1NDk0NC05MDQ1NTM5NzgucG5n]: /images/20220204/d80d8c97bb294ab0953d8044ce568487.png
[Link 1]: http://home.cnblogs.com/u/yushangzuiyue/
[https_www.cnblogs.com_yushangzuiyue_p_8352796.html]: https://www.cnblogs.com/yushangzuiyue/p/8352796.html