MySql基础

MySql最常用的引擎为MyISAM和InnoDB

全文本搜索：

    MyISAM支持全文本搜索，InnoDB不支持。

    为什么用全文本搜索：
        1、通配符（LIKE）和正则表达式通常要求MySql匹配所有的行，搜索极少用到表索引，随着被搜索行数的增加，将会非常耗时。

        2、通配符（LIKE）和正则表达式很难明确匹配什么不匹配什么。

        3、通配符（LIKE）和正则表达式无法提供智能化的搜索结果。   

        4、全文本搜索使得MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行，速度非常快。

     启用全文本搜索：
         可以在创建表时指定FULLTEXT，或者在稍后指定。          

         不要在导入数据时使用FULLTEXT ，更新索引要花时间，虽然不是很多，但毕竟要花时间。如果正在导入数据到一个新表，此时不应该启用FULLTEXT索引。

         应该首先导入所有数据，然后再修改表， 定义FULLTEXT。 这样有助于更快地导入数据。 

     全文本搜索的一个重要部分就是对结果排序。具有较高等级的行先返回（因为这些行很可能是你真正想要的行）。 

     查询拓展，找到需要的行，并根据第一行返回的结果中的字符集查找其余行有没有包含，有的话则返回该行。

     布尔模式，搜索比较缓慢。

插入数据：

      一般使用INSERT INTO 表名（列名，）VALUES (列对应的值，)；

     如果表的定义允许可以在INSERT中省略列，一是该列定义为允许NULL值，二是该表定义中给出默认值。

     提高整体性能：

             1、可能降低等待处理的SELECT语句的性能。    

             2、如果数据检索是最重要的（通常是这样），则可以通过在INSERT和INTO之间添加关键字LOW_PRIORITY，指示MySQL降低INSERT语句的优先级，

                   这也适用于UPDATE和DELETE语句。  INSERT LOW PRIORITY INTO ...  

              3、当需要插入多个行时，单条语句插入多行值（INSERT INTO 表名（列名，）VALUES（...），VALUES（...）,....；）比多条语句插入一行性能高。

更新数据：

       UPDATE 表名 SET 列名=值，列名=值，... WHERE...;

删除数据:

      DELET FROM 表名 WHERE ...;

DELETE删除整行而不是删除列。为了删除指定的列，请使用UPDATE语句（UPDATE ... SET 列名 = NULL ...）

创建和操纵表：

      主键：可创建由多个列组成的主键

                 CREATE TABLE 表名 （列名 数据类型 NULL/NOT NULL  AUTO_INCREMENT，... ，PRIMARY KEY（列名1，列名2，...））ENGINE=InnoDB； 

                 允许NULL值的列不能作为唯一标识，主键只能使用不允许NULL值的列。

      确定AUTO_INCREMENT的值：SELECT last_insert_id() 返回最后一个AUTO_INCREMENT的值。

      指定默认值：DEFAULT关键字，MySql只支持常量不支持函数。

      引擎类型：

              为不同的任务选择不同的引擎能获得良好的功能和灵活性。     

              InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索。

              MEMORY在功能等同于MyISAM， 但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）。

              MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。 

              引擎类型可以混用，但外键不能跨引擎使用。

      更新表：

              增加列，ALTER TABLE 表名 ADD 列名 数据类型；

              删除列，ALTER TABLE 表名 DROP 列名；

              定义外键（为表中的一列，包含另一个表的主键值，定义了两个表的关系）

              表的结构更改：

                       1、用新的列布局创建一个新表。

                       2、使用INSERT SELECT语句从旧表复制数据到新表。

                       3、检验新表。

                       4、重命名旧表，（可以删除）。

                       5、旧名赋给新表。

                       6、根据需要，重新创建触发器，索引，存储过程和外键。

视图：

存储过程：

        一条或多条MySql语句的集合，可将其视为批处理文件。

        用存储过程代替单条sql语句的好处：简单、安全、高性能。

       创建存储过程

       执行存储过程

       删除存储过程

游标：

触发器：
       某条语句在特定事件发生时自动执行，触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句。UPDATE、DELETE、INSERT，其他语句不支持。

       创建触发器：

               需要给出4条信息：唯一的触发器名、触发器关联的表、触发器应该响应的活动（UPDATE,INSERT,DELETE）、触发器何时执行。

               CREATE TRIGGER 触发器名 AFTER/BEFORE 活动 ON 关联的表名  FOE EACH ROW SELECT "hhh" ： 表示在每次对于每一行活动之前或之后都会显示hhh字符。

       只有表才支持触发器，临时表或者视图不支持。

       使用触发器：

              INSERT触发器 

              DELETE触发器

              UPDATE触发器

事务处理：

       InnoDB支持，MyISAM不支持。

       事务处理可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。

       为什么要用事务处理：

              事务处理是一种机制，用来管理必须成批执行的MySQL操作，以保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们或者作为整体执行，或者完全不执行（除非明确指示）。如果没有错误发生，整组语句提交给（写

              到） 数据库表。如果发生错误，则进行回退（撤销）以恢复数据库到某个已知且安全的状态。

        事务处理用来管理UPDATE，INSERT，DELETE语句。无法回退SELECT，CREATE，DROP（执行后不起作用）。事务其他处理对CREATE和DROP起作用。

        COMMIT、使用保留点

全球化和本地化：

       字符集和校对顺序： CHARACTER SET和COLLATE两者。

安全管理：
        访问控制：

               用户应该对他们所需的数据具有适当的访问权，既不能多也不能少。

        用户管理：

               CREATE USER 用户名 IDENTIFIED BY '密码'；   

               RENAME 用户名 TO 新用户名；

               DROP USER 用户名；

        设置访问权限：

                赋予权限：GRANT SELECT，INSERT...ON 数据库名.表名 TO 用户名； （黑体可在权限列表中查找对应的指令）

                撤销权限：REVOKE SELECT，INSERT...ON 数据库名.表名 TO 用户名； 

数据库维护：

改善数据库性能：

       1、添加索引：

            提高检索性能，损害数据插入、删除和更新的性能。

            什么是索引：

                   索引是对数据库中一列或多列的值进行排序的一种结构。 

使用EXPLAIN语句查看索引是否被使用。SQL代码如下：

    EXPLAIN SELECT * FROM index1 where id=1；

            为什么能提高检索性能：

                   DB在执行一条Sql语句的时候，默认的方式是根据搜索条件进行全表扫描，遇到匹配条件的就加入搜索结果集合。如果我们对某一字段增加索引，查询时就会先去索引列表中一次定位到特定值的行数，大大减少遍历匹配的行数，所以能明显增加查询的速度。

            索引需要占用物理和数据空间。 

            什么是索引的最左匹配原则：

                   组合索引和最左匹配：

                   Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。

                      

 

                    

            索引的分类：

                   https://www.cnblogs.com/zsc1/p/9230096.html

                   https://blog.csdn.net/tomorrow_fine/article/details/78337735

                   index ----普通的索引,数据可以重复。

                   fulltext----全文索引，用来对大表的文本域(char，varchar，text)进行索引。语法和普通索引一样。 全文索引是MyISAM的一个特殊索引类型，主要用于全文检索。

                   unique ----唯一索引,唯一索引,要求所有记录都唯一。

                   primary key ----主键索引,也就是在唯一索引的基础上相应的列必须为主键。   

                   单列索引、多列索引。

                   空间索引：MyISAM支持空间索引，主要用于地理空间数据类型，例如GEOMETRY。

                   B-Tree、B+Tree和HASH索引。

            MySQL支持Hash索引和B+树索引。       

            https://www.cnblogs.com/weizhixiang/p/5914120.html

            B+树索引：

                  目前大部分数据库系统及文件系统都采用B-Tree（即B树）和B+Tree（B+树）作为索引结构。

                    

            为什么使用树结构：树的查询效率高，而且可以保持有序。

            为什么索引不使用二叉查找树：

                   二叉查找树的查找速度和比较次数都是最小的，但是考虑到磁盘IO，不可能将索引全部都加载进内存进行处理，所以我们需要在磁盘块上存储索引，最坏的情况下，磁盘IO次数等于树的高度。而B-树可以将瘦高的二叉查找树变的矮胖从而减少IO次数。

                   B树时多路平衡查找树，它的每一个节点最多包含k个孩子，k为B树的阶，k的大小取决于磁盘页的大小。

                   B树的插入和删除操作：https://mp.weixin.qq.com/s/rDCEFzoKHIjyHfI_bsz5Rw

            为什么说B+-tree比B 树更适合实际应用中操作系统的文件索引和数据库索引：

                 1) B+-tree的磁盘读写代价更低B+-tree的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键也

                 就越多。相对来说IO读写次数也就降低了。              

                 2) B+-tree的查询效率更加稳定由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

                 3) 个人觉得这两个原因都不是主要原因。数据库索引采用B+树的主要原因是B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题，B+树应运而生。B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据

                  库中基于范围的查询是非常频繁的，而B树不支持这样的操作（或者说效率太低）。

           为什么损害删除，查找和更新的性能：

                 B+树是一颗平衡树（平衡树：它是一棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树），如果我们对这颗树增删改的话，那肯定会破坏它的原有结构。

                 要维持平衡树，就必须做额外的工作。正因为这些额外的工作开销，导致索引会降低增删改的速度。

           什么情况下组合索引失效：               

                 （1）使用or做连接字时，若or左右两边有一边不是组合索引中的字段，失效。

                 （2）最左匹配原则。

                 （3）模糊查询时，当%在前缀时，索引失效。当前缀没有%，后缀有%时，索引失效。 不能使用索引中范围条件右边的列。

                 （4）如果列类型为字符串，则where查询时一定要用引号括起来，否则索引失效。

                 （5）当全表扫描速度比索引速度快时，MySQL会使用全表扫描，索引失效。

          使用B-树时的一些限制：

                  （1 ）查询必须从索引的最左边的列开始。关于这点已经提了很多遍了。例如你不能利用索引查找在某一天出生的人。

                  （2）不能跳过某一索引列。key(last_name, first_name, dob)，你不能利用索引查找last name为Smith且出生于某一天（dob）的人。

                  （3）存储引擎不能使用索引中范围条件右边的列。例如，如果你的查询语句为WHERE last_name="Smith" AND first_name LIKE 'J%' AND dob='1976-12-23'，则该查询只会使用索引中的前两列，因为LIKE是范围查询。    

          HASH索引：

                 只有Memory存储引擎显示支持hash索引，是Memory表的默认索引类型，Memory也可以使用B-Tree索引。

                 哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

                 本质上就是把键值换算成新的哈希值，根据这个哈希值来定位。

                 局限性：

                        不支持最左匹配原则。

                        在有大量重复键值情况下，哈希索引的效率也是很低的—–>哈希碰撞。

                        不支持范围查询。

                         

         需要注意的地方：

                 最左前缀匹配原则。这是非常重要、非常重要、非常重要（重要的事情说三遍）的原则，MySQL会一直向右匹配直到遇到范围查询（>,<,BETWEEN,LIKE）就停止匹配。

                 尽量选择区分度高的列作为索引，区分度的公式是 COUNT(DISTINCT col) / COUNT(*)。表示字段不重复的比率，比率越大我们扫描的记录数就越少。

                 尽可能的扩展索引，不要新建立索引。比如表中已经有了a的索引，现在要加（a,b）的索引，那么只需要修改原来的索引即可。

                 建立列时尽量不要允许有NULL值，因为对索引会带来不必要的麻烦。

         高性能的索引策略：

                聚簇索引： 聚簇索引保证关键字的值相近的元组存储的物理位置也相同（所以字符串类型不宜建立聚簇索引，特别是随机字符串，会使得系统进行大量的移动操作），且一个表只能有一个聚簇索引。因为由存储引擎实现索引，所以，并不是所有的引擎都支持                                       聚簇索引。目前，只有solidDB和InnoDB支持。

                覆盖索引

                利用索引进行排序

                索引和加锁

    2、使用EXPLAIN语句让MySQL解释它将如何执行一条SELECT语句。 

    3、LIKE很慢。一般来说，最好是使用FULLTEXT而不是LIKE。

    4、通过使用多条SELECT和UNION语句替换复杂的OR条件。

    5、总是有不止一种方法编写同一条SELECT语句。应该试验联结、并、子查询等，找出最佳方法。