MySQL优化

一、Linux中如何安装MySQL - YUM

 

    1、检查Linux中是否已经安装了MySQL

        rpm -qa | grep mysql

 

    2、卸载原来的mysql相关的服务

        rpm -e --nodeps xxxxxxx

 

    3、确定mysql的yum源的版本

        yum list | grep mysql

 

    4、下载新的yum源，获得更新的mysql版本

        wget http://repo.mysql.com/mysql57-community-release-el6-8.noarch.rpm

        rpm -ivh mysql57-community-release-el6-8.noarch.rpm

        yum list | grep mysql

 

    5、正式进行yum安装

         yum -y install mysql-server mysql

 

    6、安装完成后，设置MySQL的默认字符集（utf8）

        vim /etc/my.cnf

        添加如下配置：

        character-set-server=utf8

 

    7、启动mysql服务

        service mysqld start（stop|restart）

 

    8、第一连接mysql需要获得随机密码

        grep 'password' /var/log/mysqld.log |head -n 1

 

    9、连接mysql，并且修改密码

        mysql -uroot -p

 

        修改密码：

        set password = password('新的密码');

 

    10、配置MySQL的远程连接

    

        · 开发3306端口

        · 创建一个可以远程连接的账号

            create user 'root'@'%' identified by 'root';

        · 设置新账号的权限

            grant all privileges on *.* to 'root'@'%';

 

 

二、MySQL的逻辑架构

 

    

    

    第一层：连接处理层，在这一层主要用来处理客户端的连接、身份认证、授权管理等等...

    第二层：核心服务层，主要用来查询缓存、解析sql语句（解析内置函数、存储过程、视图、触发器...）以及优化查询

    第三层：存储引擎层，存储引擎层只是单纯的用来存储和提取数据。上层（核心服务层）不会关心下一层用的是什么存储引擎，只是通过调用统一的API接口，获得相应的数据。

    第四层：物理磁盘层

 

三、MySQL的存储引擎

 

    相关命令：

        show engines; - 查询所有的存储引擎

        show variables like '%engine%'; - 查询默认的存储引擎

        show table status like '表名'; - 查询某个表的信息（存储引擎）

 

    

    MyISAM VS InnoDB：

        

 

    如何选择存储引擎？

        直接选择InnoDB

        

 

四、数据类型的优化

 

    如何选择数据类型：

 

        1）选择更小的数据类型（注意，不要低估了这个数据的长度）

        2）选择更合适并且简单的数据类型（比如不要用varchar代替int）

        3）尽量避免一个字段为null（如果需要在一列上创建索引，则非null的列优化的效果会更好）

 

    MySQL中常见的数据类型：

        

        整数型：

            tinyint：1个字节

            smallint：2个字节

            mediumint：3个字节

            int：4个字节

            bigint：8个字节

 

        浮点数：

            float：4个字节

            double：8个字节

    

        精准型：

            decimal

 

        字符型：

            varchar：可变长字符串

            char：定长字符串

        

        大字段类型：

            blob：大二进制字段

            text：大文本字段

    

        日期类型：

            datatime：8个字节

            timestamp：4个字节

 

 

五、索引优化（重要）

 

    什么是索引？

        简单来说，索引就是一本书的目录。

        索引是一个用于快速查询的数据结构。

 

    索引为啥能够提高查询速度？

 

        没有索引的情况：

            

 

 

        假设name字段有一个索引：

            

 

    什么时候适合添加索引？什么时候不适合添加索引？

 

        适合创建索引的情况：

            1）主键自动有一个索引，而且是性能最好的索引

            2）where后面查询的字段，通常应该加索引（看情况）

            3）表连接的字段通常需要添加索引

            4）经常用来排序的列，可以添加索引（看情况）

            5）字段的选择性越大，越适合创建索引

 

        不适合创建索引的情况：

            1）表记录太少

            2）经常修改的字段

            3）选择性小的字段，比如性别

 

    索引的分类以及相关的命令：

        

        单列索引 - 表示当前索引只作用在这一列上：

            create index 索引名称 on 表名(字段名);

 

        复合索引 - 表示当前索引作用在一张表的多个列上：

            create index 索引名称 on 表名(字段名, 字段名2....);

 

            思考1：一个复合索引和多个单列索引，效果是否一样？ - 不一样

            思考2：复合索引的字段顺序是否重要？- 不一样

 

        唯一索引 - 添加索引的字段必须唯一，但是可以为空

            create unique index 索引名称 on 表名(字段名);

 

        全文索引 - MyISAM支持，InnoDB 5.6之后支持

            create fulltext index 索引名称 on 表明(字段名);    

 

        删除索引的命令：

            drop index 索引名 on 表名;

 

 

    MySQL索引的底层原理：

 

        MySQL底层的索引采用的是B+Tree的数据结构。

 

        为什么MySQL不选择搜索性能更佳的哈希表作为索引的底层数据结构？

        因为哈希表不支持范围查询

 

        为什么MySQL不选择红-黑树作为索引的底层数据结构？

        因为随着数据越来越大，索引肯定也会越来越大。可能内存就没办法加载整个索引树，每次获取下层节点时，都需要进行一次磁盘的io，如果磁盘io次数越多，查询性能就会越差。如何避免大量的磁盘io？这个时候就需要降低树形结构的层级，那么势必二叉树就需要升级成多路树，因此MySQL选择采用B+Tree作为索引的底层数据结构。

 

        什么是BTree?

            

 

        什么是B+Tree?

            

                

            和BTree的区别：

                1、叶子节点之间会形成一个双向链表

                2、所有的节点，一定会出现在叶子节点上

 

        

        模拟MySQL底层索引生成的过程：

 

        

 

 

        聚簇索引（拓展）：

 

        什么是聚簇索引？

            MyISAM没有聚簇索引，InnoDB的主键索引就是聚簇索引

 

        非聚簇索引的结构：

            

 

        聚簇索引的结构：

            

 

        覆盖索引（重要）

        

        什么是覆盖索引？

            覆盖索引并不是一种索引，而是一种索引的优化技巧。

            当你要查询的字段，在索引中已经可以获得了，不用在去表中查询一次，这种情况称之为覆盖索引。在实际的优化过程中，有可能需要人为的构造覆盖索引进行优化。