Mysql(二)--索引使用及优化

Love The Way You Lie 2022-05-24 06:48 231阅读 0赞

强烈推荐一个大神的人工智能的教程：[http://www.captainbed.net/zhanghan][http_www.captainbed.net_zhanghan]

# <table>   <tbody>    <tr>     <td><font>索引简介</font></td>    </tr>   </tbody>  </table> #

索引是什么?索引是类似于书的目录这样的结构吗?可以这么讲,但是到底索引是什么,索引是排好序的快速查找数据结构。索引最常用的类型是BTree类型,这种类型同样可以体现出索引是排好序的快速查找数据结构,没有索引之前要想查找符合某个条件的数据就需要全表扫描,这样性能是非常低的,当有了BTree类型,可以按照一定的规则进行查找,如下是BTree结构: !\[这里写图片描述\](https://img-blog.csdn.net/20180522164837647?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poMTU3MzI2MjE2Nzk=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70)

# <table>   <tbody>    <tr>     <td><font>索引分类</font></td>    </tr>   </tbody>  </table> #

**1. 单值索引**  
一个索引只包含单个列,一个表可以包含多个单值索引。例如我们想查name字段等于特定值,  
表结构:

select * from user where name='张三01';
    +----+----------+
    | id | name     |
    +----+----------+
    |  1 | 张三01   |
    +----+----------+

在没有加索引的情况下:

加索引:

create index idx_user_name on user(name);

加完索引之后:

单值索引的创建方式有三种:

create index index_name on table_name(column)

alter table table_name add index index_name(column)

create table table_name(column1,column2...PRIMARY KEY (`id`),INDEX index_name (column)

**2. 复合索引**  
一个索引包含多个列  
如下表结构:

select * from article;
    +----+-----------+-------------+-------+----------+-------+---------+
    | id | author_id | category_id | views | comments | title | content |
    +----+-----------+-------------+-------+----------+-------+---------+
    |  1 |         1 |           1 |     1 |        1 | 1     | 1       |
    |  2 |         2 |           2 |     2 |        2 | 2     | 2       |
    |  3 |         1 |           1 |     3 |        3 | 3     | 3       |
    +----+-----------+-------------+-------+----------+-------+---------+

创建复合索引:

create index idx_article_cv on article(category_id,views);

**3. 唯一索引**  
索引值必须唯一,允许有空值

create unique index idx_test on article(content);

**4. 全文索引**  
FULLTEXT索引仅可用于 MyISAM 表；他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加,索引也是占用内存空间的,所以在数据量特别大的表上不要使用全文索引,因为在更新数据库数据的时候,会同时更新索引表,因此,性能会特别慢。

# <table>   <tbody>    <tr>     <td><font>索引法则及优化</font></td>    </tr>   </tbody>  </table> #

*  最左前缀原则,不跳过索引中的列  
    在name,age,pos三个字段上建立索引:

create index idx_staffs_nameAgePos on staffs;

举一个不遵从最左前缀的例子，where之后的条件是age和pos,没有索引的最左列name,因此是不遵从最左前缀的,导致索引失效,可以看到下图中的key为NULL,没有用到索引:  
![这里写图片描述][70]  
不能跳过索引中的列,如果跳过了,如果最左前缀列还在,则会使用部分索引,如果最左前缀列没有,则直接导致索引失效,例如,我们只使用最左前缀查，发现索引是使用了，key\_len是74:  
![这里写图片描述][70 1]  
跳过一个索引列,跳过中间的age列,如下，发现key\_len依旧是74，说明是用到了部分索引:  
![这里写图片描述][70 2]  
2. 不在索引列上做任何操作,否则会导致索引失效而转向全表扫描  
![这里写图片描述][70 3]  
3. 存储引擎不能使用索引中范围条件右边的列,范围之后全失效,将导致name和age被用到了，pos失效

select * from staffs where name='July' and age >25 and pos='manager';

4.尽量用覆盖索引(覆盖索引:查询的列和所建立的索引的列个数相同,字段相同),减少select \* 的使用  
5. 尽量不使用!=或<>,如果使用,则无法使用索引,会导致全表扫描

[6.is][] null, is not null无法使用索引

7.like查询,%要在最右侧(‘字符串%’),否则会进行全表扫描,那么如何解决like查询时’%字符串%'时索引不被使用的方法(可以使用覆盖索引)  
8. 字符串类型不加单引号会导致索引失效,因为mysql会自己做类型转换,相当于在索引列上进行了操作  
如下例子中category\_id是一个字符串,但是由于没有加单引号,就会降低性能

select * from article where category_id =1;

9.少用or,用它会索引失效

# <table>   <tbody>    <tr>     <td><font>小试牛刀</font></td>    </tr>   </tbody>  </table> #

!\[这里写图片描述\](https://img-blog.csdn.net/20180522173754957?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poMTU3MzI2MjE2Nzk=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70)

[http_www.captainbed.net_zhanghan]: http://www.captainbed.net/zhanghan
[70]: /images/20220524/11c118684c944cfc8bebc6752223c6c5.png
[70 1]: /images/20220524/d78c565755264db98abe770e92cc1cac.png
[70 2]: /images/20220524/a67d7b57b7e1461b9bc6bed9f6dfe051.png
[70 3]: /images/20220524/daaab49f495d451eb8a13eeb11ae32b2.png
[6.is]: http://6.is