MySQML进阶—MySQL的索引（三）

一、索引简介

1、什么是索引

索引是帮助MySQL高效获取数据的数据结构（索引是一种数据结构）。简单理解：排好序的快速查找数据结构（排序、查找）。都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都输使用B+树索引。还有哈希索引（hash index）等。

目的提高查询效率，类比字典，图书馆的条形码。

 注意：索引本身很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上

2、索引的优势和劣势

优势：a、提高数据检索的效率，降低数据的IO成本

           b、降低数据排序的成本，降低了CPU的消耗

劣势：a、索引多，占空间

           b、虽然提高了查询速度，但是降低更新表的速度（对表进行增删改，索引也会更新保存。）

          c、大数据量的表，需要花时间研究建立优秀的索引

3、索引的分类

• 单值索引：一个索引只包含单个列，一个表可有多个单列索引（最多不要超过5个）
• 唯一索引：索引列的值必须唯一，但允许有空值
• 复合索引：即一个索引包含多个列

基本语法：

创建：CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));

            ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(length)) 

删除：DROP INDEX [indexName] ON mytable

查看：SHOW INDEX FROM table_name\G 

ALTER命令

//该语句添加一个主键，这意味着索引值必须是唯一，且不能为NULL
  ALERT TABEL tbl_name ADD PRIMARY KEY(column_list)

//该语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
ALERT TABLE tbl_name ADD UNIQUE index_name(column_list)

//添加普通索引，索引值可出现多次
ALTER TABLE tble_name ADD INDEX index_name(column_list)

//该语句指定了索引为FULL TEXT,用于全文索引
ALTER TABLE tbl_name ADD FULL TEXT index_name(column_list)

4、索引的j结构

               BTree索引

        Hash索引

        full-text 全文索引

        R-Tree 索引

5、哪些情况需要创建引

        a、主键自动建立唯一索引

        b、频繁作为查询条件的字段应该创建索引

        c、查询中与其它表关联的字段，外键关系建立索引

        d、频繁更新的字段不适合创建索引

        e、where条件里用不到的字段不创建索引

        f、单键/组合索引的选择问题，who？（在高并发下倾向创建组合索引）

        g、查询中排序的字段，排序字段若通过索引去访问将大大提高排序的速度

        h、查询中统计或者分组字段

6、哪些情况不要创建索引 

        a、表记录太少

        b、经常增删改的表

        c、数据重复且分布平均的字段，因此应该只为最经常查询和最经常排序的数据列建立索引。如果每个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
例如：一个表有10万行记录，有个字段A只有true和false两种值，却每个值的分布概率大约为50%，那么对这种表A字段建立索引一般不会提高数据库的查询速度。
        索引的选择性是指索引列中不同值的数目与表中记录的数比：

 例如：一个表中有2000条记录，表索引列有1980个不同的值，那么这种索引的选择性是1980/2000=0.99，一个索引的选择性越接近1，这个索引的效率越高