MySQL从入门到应用 第四天

1. 索引

    索引（index）是帮助MySQL高效获取数据的数据结构（有序）。类似与一本书的目录结构。

    1.1.1 索引的特点

        1） 优点：提高数据检索的效率，降低数据库的IO成本；通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗；

        2） 缺点： 实际上索引也是一张表，保存的时主键与索引的映射关系，占用了一定的内存资源；增加了查询的速度，但是降低了增删改的速度，因为数据改变也需要同步维护索引；

    1.1.2 存储引擎对索引的支持

        

    1.1.3 MySQL的B+树结构

        请先阅读数据结构二叉平衡树，B树，B+树；

        MySql索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

        

    1.1.4 创建索引：

        1）CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type] ON table_name(index_colum_name,...)

        2）alter table tb_name add primary key(column_list);  添加一个主键，默认创建出一个主键索引；

             alter table tb_name add unique index_name(column_list);  创建唯一索引，值唯一但可以有多个NULL；

             alter table tb_name add index index_name(column_list);  添加一个普通索引；

             alter table tb_name add fulltext index_name(column_list); 添加一个全文索引；

    1.1.5 查看索引： show index from table_name；

    1.1.6 删除索引： DROP INDEX index_name ON table_name;

2. 索引的使用

    2.1 避免索引失效

        2.1.1 全值匹配 ，对索引中所有列都指定具体值，该情况下，索引生效，执行效率高；

        2.1.2 最左前缀法则，如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效；

        2.1.3 范围查询右边的列，不能使用索引；

        2.1.4 不要在索引列上进行运算操作， 索引将失效；

        2.1.5 字符串类型列不加单引号，造成索引失效；

        2.1.6 尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select *；

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

        2.1.8 以%开头的Like模糊查询，索引失效；如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效；通过查询列覆盖索引可以解决；

        2.1.9 如果MySQL评估使用索引比全表更慢，则不使用索引；

        2.1.10 is NULL ， is NOT NULL 有时索引失效；这个有时是因为底层分析全表扫描查询更快导致的；

        2.1.11 in 走索引， not in 索引失效；

        2.1.12 尽量使用复合索引，而少使用单列索引；因为组合索引相当与创建了好几个索引；

    2.2 查看索引使用情况

show [global | session] status like 'Handler_read%' ;

Handler_read_first ：索引中第一条被读的次数。如果较高，表示服务器正执行大量全索引扫描（这个值越低越好）。

Handler_read_key ：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的性能改善不高，因为索引不经常使用（这个值越高越好）。

Handler_read_next ：按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。

Handler_read_prev ：按照键顺序读前一行的请求数。该读方法主要用于优化 ORDER BY ... DESC 。

Handler_read_rnd ：根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL 扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应该建立索引来补救。

Handler_read_rnd_next ：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。 

3. 视图

    视图（View）是一种虚拟存在的表。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

视图相对于普通的表的优势主要包括以下几项：

    简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。

    安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。

    数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

 

    3.1 创建视图：CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]

    3.2 修改视图：ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]

    3.3 删除视图：DROP VIEW [IF EXISTS] view_name [, view_name] ...[RESTRICT | CASCADE]