mysql 优化规则，max() count() , limit 等优化，索引的优化，添加自增长的规则

清疚 2022-02-26 11:14 130阅读 0赞

**1. 查出一张表里的最大的那个时间：SELECT MAX(u.createtime)  FROM who\_user\_function u**

加 索引前：

![20190329164939814.png][]

给**createtime ** 加索引后：

![20190329165241456.png][]

**2 .  查询 users 表里面的 电话 1524459790 和 13639394862 各出现了多少次 ：**

SELECT COUNT(tel='18294459790' OR NULL ) AS self,COUNT(tel='13639394862' OR NULL ) AS other FROM users  u;

![20190329165646656.png][]

**3  如果who\_user\_function 中有40多万条数据，要查出 30万条之后的600条数据，并且其中表里面只有 id  一个主键索引，再没有其他的索引，全表扫描，查询情况如下：**

1>> 没有加order by  id 的情况下：SELECT w.id ,w.createtime FROM  who\_user\_function w  LIMIT 300000,600 ;

![20190329172246343.png][]

**    2 >> 加了 order by id 的情况下：SELECT w.id ,w.createtime FROM  who\_user\_function w order by id  LIMIT 300000,600 ;  使用了主键 id 索引，稍微比上面快点**

![20190329172449807.png][]

**4. 如何修改一个表里面的某一个字段为自增长：**

情景： 给有数据(40万条数据)的 who\_user\_function 表里面加上 auto\_index 字段 ，并且使该字段为自增长 的做法如下：

1>>     添加 auto\_index 字段，并且设置为主键，

2>>     给 auto\_index 添加索引  alter  table  who\_user\_function add index  auto\_index(\`auto\_index\`);

3>>      修改 auto\_index 字段为自增长之后，然后who\_user\_function 表里面的auto\_index 字段就会有值，并且都是自增长的

**5 . limit  的优化规则：**

** ** **   **情景：如何查出 who\_user\_function 中的1000 到1500 之间的500条数据, 查法如下：

方法一：>> EXPLAIN SELECT \* FROM  who\_user\_function w LIMIT 1000,500;

1） 扫描 385937 行,没有加order by auto\_index

2）图表：

![20190401113300183.png][]

方法二：>> EXPLAIN   SELECT  \* FROM who\_user\_function w ORDER BY  w.auto\_index LIMIT 1000,500;

1） 扫描1500行,加了order by auto\_index

2)  图表：

![20190401113449595.png][]

方法三：>> EXPLAIN  SELECT w.\* FROM who\_user\_function w WHERE w.auto\_index >=1000 AND w.auto\_index <= 1500                          ORDER BY  w.auto\_index   LIMIT 0,500;

1)   扫描500行 加了 order by auto\_index,还加了where 过滤条件

2) 图表：

![20190401113729244.png][]

** 总结： 很显然方法三 是最优的选择，只查询了500 条，并且 用了 索引 auto\_index ,type 方式也是 range **

** 6 ，索引在优化sql时是非常重要的，适当的使用索引，能起到很大的作用 一般在 where 条件，group by,  order by  后面使                 用索引优化**

** ** ** 8. limit 的其他的优化规则，limit 的基数比较大时使用 between   如：**

**   ** **  1）优化前**

**           select \* from admin order by admin\_id limit 100000,10**

**      2）优化后**

**            select \* from admin where admin\_id between 100000 and 100010 order by admin\_id。**

**   9.  sql 优化  or  和  union 及 unionall  的**优化，区别**：  
        union 将两个结果集进行追加，需要两个结果集的列数应该是相关的**，**并且相应列的数据类型也应该相当的 ，一般情况**

**       下，用union 代替where 字句中的or 效果会好点，但是前提是字段必须加了索引，对索引列使用OR将造成全表扫描，如果          该字段没有加索引，则使用union 则可能还没有or的效率高呢。。**

**   10. 给一个表里添加一个字段并且设置为自增长步骤：**

**       A. 先删除现有的主键  ALTER TABLE cash\_users DROP PRIMARY KEY;**

B. 然后填加想添加的这个字段 ALTER TABLE cash\_users ADD auto INT(10)  NOT NULL COMMENT '自增长';

C. 设置刚添加的这个字段为主键自增长 ALTER TABLE cash\_users MODIFY \`auto\` INT  AUTO\_INCREMENT PRIMARY KEY ;

[20190329164939814.png]: /images/20220226/7cc0131fabf4442685c935f910c2b214.png
[20190329165241456.png]: /images/20220226/cf513166a0cc424e9f29ea77ac3ffcef.png
[20190329165646656.png]: /images/20220226/bd81230231894e369c3ffd3da722204b.png
[20190329172246343.png]: /images/20220226/32fa82bdbde6428da50041d5c9d327de.png
[20190329172449807.png]: /images/20220226/4b7b4b9be73f46969b49ce7877d79cde.png
[20190401113300183.png]: /images/20220226/fa5abbac7f344660973632513004f103.png
[20190401113449595.png]: /images/20220226/050b38945dd24685910cbf5fa971fb9f.png
[20190401113729244.png]: /images/20220226/a8251a3434b741e18f2807da6d2c8318.png