数据库索引优化-数据库索引优化大全

ernestwang 2019-08-26 1001 0

索引

索引的原理是利用特殊的查找算法（如二叉树算法），限制访问的行数，提高访问效率。
索引的分类：
    主键索引、普通索引、唯一索引、外键索引和全文索引。
文件：
    针对于mysql来说
    innodb:两个文件   .frm(表结构)   .ibd(索引文件)
    myisam:三个文件   .frm(表结构)   .MYD(表数据)   .MYI(表索引)

主键索引
    当一张表，把某个列设为主键的时候，则该列就是主键索引
普通索引
    一般来说，普通索引的创建，是先创建表，然后在创建普通索引
唯一索引
    当表的某列被指定为unique约束时，这列就是一个唯一索引 
    unique字段可以为NULL,并可以有多NULL,即Null！=Null。但是如果是具体内容，则不能重复
外键索引
    维护表的关联关系
    id name pwd nich 
    uid email phone dizhi
全文索引
    全文索引，只对MyISAM引擎有用。主要是针对文件，文本的检索, 比如文章或者段落,
    它会把某个数据表的某个数据列出现过的所有单词生成一份清单    【注】mysql自己提供的fulltext针对英文生效，想要搜索中文需要使用专门的全文检索引擎或者使用加强版的模糊查询。
    【注】全文索引不完全等同于模糊查询比如title字段有这么个数据’abcd20088ccaa’，
    使用模糊查询select * from articles where title like’%2008%’可以查找到，而使用全文检索select * from articles where match(title) against(‘2008’);是检索不到的，因为2008不是一个单词！

索引的添加、删除、查看

创建索引
    1、ALTER TABLE方法
        ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。
        ALTER TABLE table_name ADD INDEX index_name (column_list)
        ALTER TABLE table_name ADD UNIQUE (column_list)
        ALTER TABLE table_name ADD PRIMARY KEY (column_list)
    2、CREATE INDEX
        CREATE INDEX可对表增加普通索引或UNIQUE索引。
        CREATE INDEX index_name ON table_name (column_list)
        CREATE UNIQUE INDEX index_name ON table_name (column_list)
删除索引
    可利用ALTER TABLE或DROP INDEX语句来删除索引。
    DROP INDEX index_name ON talbe_name
    ALTER TABLE table_name DROP INDEX index_name
    ALTER TABLE table_name DROP PRIMARY KEY   //只能删除主键索引
查看索引
    show index from 表名\G
    Table:表的名称。
    Non_unique:如果索引不能包括重复词，则为0。如果可以，则为1。
    Key_name:索引的名称。
    Seq_in_index:索引中的列序列号，从1开始。
    Column_name:列名称。
    Collation:列以什么方式存储在索引中。在MySQL中，有值'A'（升序）或NULL（无分类）。
    Cardinality:索引中唯一值的数目的估计值，基数越大，联合查询时使用的概率越大
    Sub_part:如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
    Packet:关键词如何被压缩，如果没有被压缩，则为NULL
    Null:如果列含有NULL，则含有YES，如果没有，则该列为NO
    Index_type:通过的索引方法，如：BTREE、FULLTEXT、HASH、RTREE
    Comment:注释
    Index_comment:索引注释
全文索引
    创建表时添加
        CREATE TABLE articles (  
           id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,  
           title VARCHAR(200),  
           body TEXT,  
           FULLTEXT (title,body)  
         )engine=myisam charset=utf8;  
    创建表后添加
        ALTER TABLE articles ADD FULLTEXT (title,body); 
    全文索引的用法
        select * from articles where match(title,body) against('要搜索的单词');

索引的代价

SQL语言共分为四大类：数据查询语言DQL，数据管理语言DML(增删改)，数据定义语言DDL(create)，数据控制语言DCL(grant,commit,rollback)。
1、占用磁盘空间
    索引是有开销的，表现在添加索引后.ibd文件(innodb引擎)或者.myi文件(myisam引擎)会变大。
2、导致dml操作速度变慢
    添加索引后之所以会快，是因为表依据索引对数据按某种算法（二叉树等）进行排序，所以删除、增加、插入后二叉树要重新排序，导致执行效率降低。
    此时要看自己的数据库是dml语句执行的多还是dql语句执行的多
    使用以下语句可以查询
    show  status like 'com_select'; 
    show  status like 'com_insert';
    show  status like 'com_delete';
    show  status like 'com_update';
    一般来说，DQL语句操作比DML语句要多得多！接近9:1

什么情况不使用索引

1、数据唯一性差的字段不要使用索引
    比如性别，只有两种可能数据
2、频繁更新的字段不要使用索引
    比如登录次数，频繁变化导致索引也频繁变化，增大数据库工作量，降低效率。
3、字段不在where语句出现时不要添加索引
    只有在where语句出现，MySQL才会去使用索引
4、数据量少的表不要使用索引
    使用了没显著效果

添加了索引但是没有被使用

1、多列索引查询条件没有使用最左边的字段，
    id, name, email
    在SQL语句中只要没有使用最左边的字段，就不会使用索引
2、如果条件中有or
    只要条件中有一个字段没有添加索引，则不会使用索引
3、类型不对应
    比方说，如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引
4、MySQL优化器的决定
    如果mysql估计使用全表扫描要比使用索引快，则不使用索引
5、like '%aaa'不会使用到索引
    只要模糊查询的模糊值在字符串前面，则不会使用索引