Mysql安装以及索引优化详解

MySQL

Mysql下载地址

安装步骤

1. 从windows传过来的软件放在linux的opt中用rpm -qa|grep -i mysql查询是否安装过mysql解压安装包：rpm -ivh MySQL-server-5.5.48-1.linux2.6.x86_64.rpmi:install v:/var/log h:hash

若安装报错：

warning: MySQL-server-5.5.48-1.linux2.6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEYPreparing… ################################# [100%]file /usr/share/mysql/charsets/README from install of MySQL-server-5.5.48-1.linux2.6.x86_64 conflicts with file from package mariadb-libs-1:5.5.68-1.el7.x86_64file

2. 查询中是否多出这个冲突的包删除即可：yum remove mariadb-libs-5.5.68-1.el7.x86_64再用上面的命令安装即可。
3. rpm -ivh MySQL-client-5.5.48-1.linux2.6.x86_64.rpm
4. 安装客户端查看是否安装成功：cat /etc/passwd|grep mysql，出现mysql❌988:982:MySQL server:/var/lib/mysql:/bin/bash表示安装成功
5. 启动mysql服务：service mysql start
6. 关闭mysql服务：service mysql stop
7. 出错之后及时止损：卸载重装步骤-rpm -qa|grep mysql
8. 查询mysql的包- rpm -ev mysql-5.1.73-8.el6_8.x86_64删除掉这个包- find / -name mysql 查询mysql相关的文件及文件夹
9. • rm -rf 文件名/文件夹名 删除文件或者文件夹名
10. • rm -rf /etc/my.cnf配置文件的的删除
11. 重装成功 ：mysqladmin --version 出现版本号

mysql配置

1. /usr/bin/mysqladmin -u root password 123456设置用户名密码
2. 进入mysql命令 mysql -u root -pntsysv进入开机自启动起停界面。
3. 服务前面有* 说明该服务是开机自启动回忆：windows中用数据库连接工具创建数据库的库都在data文件夹中
4. 在linux下查看mysql ps -ef|grep mysql

路径	解释
/var/lib/mysql	mysql数据库文件的存放位置
/usr/share/mysql	配置文件目录
/usr/bin	相关命令目录比如:mysqladmin
/etc/init.d/mysql	起停相关脚本

6. 拷贝配置文件到/etc/my.cnf修改配置
7. 查看字符集:show variables like ‘%character%’
8. 查看配置文件:退出(exit;)—到my.cnf下(cd /etc)—编辑my.cnf(vim my.cnf)

	[client]
	#password = your_password
	port = 3306
	socket = /var/lib/mysql/mysql.sock
	default-character-set=utf8

    [mysqld]
    port = 3306
    character_set_server=utf8
    character_set_client=utf8
    collation-server=utf8_general_ci
    [mysql]
    no-auto-rehash
    default-character-set=utf8

10.主要配置文件：
- 二进制日志log-bin:主从复制
- 错误日志：log-error:默认是关闭的，记录每次错误和严重警告信息，每次关闭和开启信息等。
- 查询日志log:默认是关闭的，如果开启会降低mysql性能。
- 数据文件:1. frm文件:存放表结构2. myd文件:存放的是表数据3. myi文件:存放的是索引

tbl_dept:

tbl_emp;

笛卡尔积

1、inner join:A表和B表共有的

2、left join:共有的和A表独有的

3、right join:共有的和B表独有的

4、A表独有不包括共有的，就需要在2基础上加上where条件

5、B表独有的不包括共有的，就需要在3基础上加上where条件

6、FULL OUTER JOIN 在mysql中不支持，因此用两个left join union一下即可，因为union天生具有去重的作用。

7、FULL OUTER JOIN + WHERE 在Mysql中不支持，因此可以用一left一righ union+WHERE一下即可

索引：

概述

1. 索引是帮助MySQL高效获取数据的数据结构，索引的本质是数据结构。一句话：排好序的快速查找数据结构。
2. 一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。
3. 我们通常所说的索引，都是指B树。其中聚集索引，次要索引，覆盖索引，前缀索引，唯一索引默认都是使用B+树索引。当然除了B+树这种类型的索引以外，还有Hash索引。

优势

1. 提高数据检索的效率，降低数据库的IO成本。
2. 通过索引列对数据进行排序，降低排序的成本，降低了CPU的消耗。

劣势

1. 索引本身也是一张表，保存了主键和索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
2. 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如INSERT 、update、DELETE。因为更新表的时候，Mysql不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新带来的键值变化后的索引信息。

索引分类

1. 单值索引：即一个索引只包含单个列，一个表中可以有多个单列索引。
2. 唯一索引：索引列的值必须唯一，但允许有空值。
3. 复合索引：一个索引包含多个列。

基本语法

1. 创建：
CREATE [UNIQUE] INDEX indexName ON mytable (columnname(length)); //加一列 ALTER mytable ADD [UNIQUE] INDEX[indexName] ON (columnname(length));
2. 删除：
DROP INDEX [indexName] ON myTable;
3. 查看：
SHOW INDEX FROM table_name

Mysql索引结构

1. Bree索引
2. Hash索引
3. full-text全文索引
4. R-tree索引

Btree索引（主要）

【初始化介绍】
一颗b+树，浅蓝色的快我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针p1、p2、p3，
P1表示小于17的磁盘块，p2表示在17和35之间的磁盘苦熬，p3表示大于35的磁盘块。
真实的数据存在于叶子节点 即3、5、9、10、13、15、28、29、36、60、75、79、90、99。
非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。
【查找过程】
如果要查找数据项29.那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比较磁盘的IO）可以忽略不计，通过磁盘块1的p2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。

哪些情况需要建立索引

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其他表关联的字段，外键关系建立索引。
4. 频繁更新的字段不适合创建索引—因为每次更新不单单是更新了记录，还会更新索引。
5. 单键/组合索引的选择问题，who?（在高并发下倾向创建爱你组合索引）
6. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。
7. 查询中统计或者分组字段。

哪些情况不要创建索引

1. 表记录太少
2. 经常增删改的表：不仅要保存数据，还要保存下索引文件，会降低更新表的速度。
3. 数据重复且分布平均的表字段，没有什么效果。

性能分析

1. Mysql Query Optimizer

• Mysql中有专门负责优化select语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划（他认为最优的，不见得是DBA认为最优的，这部分是最耗时间的）。
• 当客户端向MySQL请求一条Query，命令解析器模块完成请求分类，辨别出是SELECT并转发给MySQL QUERY Optimizer时，MySQL QUERY Optimizer首先会对整条Query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析Query中Hint信息，看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行假话，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。

2. MySQL常见瓶颈

• CPU:CPU在饱和的时候一般发生在从磁盘上读取数据或在数据装入内存的时候。
• IO:磁盘I/O瓶颈发生在装入数据远大于内存容量的时候。
• 服务器硬件的性能瓶颈：top,free,iostat和vmstat来查看系统的性能状态。

3. Explain

• 是什么？（查询执行计划）：可以模拟优化器执行sql查询语句，从而知道Mysql是如何处理你的sql语句。

• 能干嘛？

  1. 表的读取顺序
  2. 数据读取操作的操作类型
  3. 哪些索引可以使用
  4. 哪些索引被实际使用
  5. 表之间的引用
  6. 每张表有多少行被优化器查询

• 怎么玩？
  Explain + SQL语句
  | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

• 各字段解释：
  id:
  id相同，执行顺序由上至下
  id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
  id相同不同，同时存在
  select_type
  

  DERIVED:在FROM列表中包含的子查询被标记为DERIVED(衍生)，Mysql会递归查询执行这些子查询，把查询结果放在临时表中
  UNION:若第二个SELECT出现在UNION之后，则被标记为UNION;若UNION包含在FROM子句的子查询中，外层的SELECT将被标记为DERIVED
  UNION RESULLT:从UNION表获取结构的select

---