mysql进阶

mysql默认数据库引擎InnoDB,支持事务，支持行锁，支持外键约束。

MyISAM,不支持事务，不支持外键，支持表锁，不支持行锁，访问速度快。

重点⚠️⚠️⚠️⚠️⚠️⚠️⚠️⚠️⚠️⚠️⚠️⚠️⚠️⚠️⚠️⚠️⚠️⚠️⚠️⚠️⚠️⚠️⚠️⚠️⚠️⚠️⚠️⚠️⚠️⚠️⚠️⚠️⚠️⚠️

索引

查询时如果没有索引会进行全表扫描，性能会比较低。

 

 索引结构

 

 

 最左前缀法则：

如果索引使用了联合索引，就要遵守最左前缀法则，指查询从索引的最左列开始，并且不跳过索引中的列。如果跳过某一列，索引将部分失效（后面的字段索引失效）。

规则：联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。

在业务允许的情况下，尽可能的使用类似于 >= 或 <= 这类的范围查询，而避免使用 > 或 < 。

索引失效情况：

1.索引列上进行函数运算操作时，索引失效；

2.字符串类型字段使用时，不加引号，索引将失效。

例如：select * from tb where phone = 12345678990;此时索引失效

3.尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

例如：explain select * from tb_user where profession like '%工程';，此时索引失效

在like模糊查询中，在关键字后面加%，索引可以生效。而如果在关键字 前面加了%，索引将会失效。

4.用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

5.如果MySQL评估使用索引比全表更慢，则不使用索引。

 覆盖索引

尽量使用覆盖索引，减少select *。 那么什么是覆盖索引呢? 覆盖索引是指 查询使用了索引，并 且需要返回的列，在该索引中已经全部能够找到

6.前缀索引

当字段类型为字符串(varchar，text，longtext等)时，有时候需要索引很长的字符串，这会让 索引变得很大，查询时，浪费大量的磁盘IO， 影响查询效率。此时可以只将字符串的一部分前缀，建 立索引，这样可以大大节约索引空间，从而提高索引效率。

 create index idx_xxxx on table_name(column(n)) ;

在业务场景中，如果存在多个查询条件，考虑针对查询字段建立索引时，建议建立联合索引，而非单列索引。

2.7

索引设计原则：

1).  针对于数据量较大，且查询比较频繁的表建立索引

2). 针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。

3).  尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

4).  如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。

5).  尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

6). 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增 删改的效率。

7). 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含 NULL值时，它可以更好地确定哪个索引最有效地用于查询。

 SQL优化

如果我们需要一次性往数据库表中插入多条记录，可以从以下三个方面进行优化。

 insert into tb_test values(1,'tom');

insert into tb_test values(2,'cat');

insert into tb_test values(3,'jerry');

.....

优化方案一：

批量插入数据

 insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

优化方案二 手动控制事务

start transaction;

insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');

insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');

commit;

优化方案三：

主键顺序插入，性能要高于乱序插入。

4). 索引设计原则

 满足业务需求的情况下，尽量降低主键的长度。

插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。

尽量不要使用UUID做主键或者是其他自然主键，如身份证号。

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

 

order by优化原则:

 A. 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。

B. 尽量使用覆盖索引。

C. 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则(ASC/DESC)。

D. 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)。

group by优化

A. 在分组操作时，可以通过索引来提高效率。
B. 分组操作时，索引的使用也是满足最左前缀法则的。

limit优化

 优化思路: 一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查 询形式进行优化。

count优化

如果数据量很大，在执行count操作时，是非常耗时的。

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个 数，效率很高; 但是如果是带条件的count，MyISAM也慢。
InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出 来，然后累积计数。

count用法

count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是NULL，累计值就加 1，否则不加，最后返回累计值。

用法:count(*)、count(主键)、count(字段)、count(数字)

 

 

 按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count(*)，所以尽 量使用 count(*)。