Mysql数据优化

1.Linux上安装mysql

    虚拟机上装linux系统，启动，ifconfig查看当前linux，IP地址。通过远程工具SecureCRTPortable:远程命令行进行连接

    ①mysql官网下载对应安装包

    登录linux查看当前mysql安装包版本 rpm -qa | grep -i mysql

    卸载 rpm -e mysql-libs-5.1.71.e16.x86_64 --nodeps

    ②上传e盘下的mysql安装包  alt + p -----> put E:/test/mysql-5.5.22.tar

    ③创建mysql文件夹，mkdir mysql

     tar -xvf Mysql-5.6.22.tar -c /mysql  解压到同目录的mysql目录

     cd mysql，ll，查看mysql目录下文件

    ④安装依赖包，yum update...时，Is this ok [Y/N]:，需要按y，enter

    ⑤安装mysql客户端 rpm -ivh MYSQL-client...

      安装mysql服务端 rpm -ivh MySQL-server...

 

    

service mysql status  查看mysql是否启动

service mysql start  启动mysql

service mysql stop  停止mysql

mysql -u root -p

需要输入密码（在mysql-server端安装的时候，有生成随机密码(命令行往上翻)）

通过远程工具SecureCRTPortable，克隆一个会话，cat /root/.mysql_secret  查看这个文件内容的密码

输入密码连接后设置密码：set password = password('root');  ，root为密码，ctrl+c退出，用新密码重新登录试试

此时本地电脑无法连接mysql，授权远程连接，mysql> grant all privileges on *.* to 'root'@'%' identified by 'root';  root为密码

使授权生效：mysql> flush privileges;

试试能不能登录，还不能登录，linux防火墙导致的

查看防火墙状态：service iptables status    (is not running就是关闭)

关闭防火墙：service iptables stop

 

二、索引

1.索引结构

BTREE索引：最常见的索引类型，大部分索引都支持B树索引

HASH索引，R-tree索引，Full-text

 

2.5 索引语法

查看有几个表：show tables;

给city_name创建索引：creat index idx_city_name on city(city_name);   idx_city_name索引名称

查看索引：show index from city\G;     key_name索引名称，index_type索引类型

删除索引：drop index idx_city_name on city;

创建唯一索引：alter table city add unique idx_city_name(city_name)  给city表的city_name字段创建唯一索引，Non_unique为0，即是唯一索引

 

2.6 索引设计原则

查询频次高，且数据量比较大的表建立索引

索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合较多，应当挑选最常用，过滤效果最好的列的组合

使用唯一索引，区分度越高，使用索引的效率越高

使用短字段索引

索引不是越多越好

 

 

 

 

linux命令：

cat /root/.mysql_secret 查看这个文件内容

shift+insert 黏贴

 

 

 

MySQL数据库优化实践。https://edu.youkuaiyun.com/course/play/24675/277694

一、数据表结构优化

1.三范式

  a.原子性(不可拆分)

  b.唯一性(不能有完全相同数据)

  c.无冗余性(不能有多余的数据)

2.字段 字段类型

  设计表时，字段选择优先级 数字>时间>字符串

  字段大小设置够用就好，例如密码 char(32)和 char(255) 年龄 tinyint(3)和 int(10)，选前者

  也可以借助命令来修改表的结构。

  select * from 表名 procedure analyse() \G

3.表拆分 用户表和详情表

4.反三范式

  表结构中，通过管理一些冗余字段，可以提高表的读取速度。例如增加订单价格，分类商品统计，粉丝数量这些字段。

二、mysql锁

三、索引添加和删除

四、慢查询

  定位执行较慢的语句，启动mysql服务并设置参数

  可以借助mysql内置命令来查看日志：mysqldumpslow /tmp/slow_query.log

五、分析语句是否有索引，explain 命令使用和搜索 类型介绍

  explain select * from 表名 where description like "爱" \G

  possible_keys 可能用到的索引

  key 实际用到的索引

 

六、SQL常见优化

1> 不要使用select *

2> 加索引的话，在where字段上加索引（如where title like..）给title加索引

3> 模糊查询 % 前置不能使用索引，例如  like ‘%三’

4> 使用or语句要注意，两侧的语句都有索引才会使用索引

5> 分组查询（group_by）分组优化，对于分组的数据 mysql会自动对数据进行排序，所以可以强制其不排序（order by null），提高速度。

6 > 分页优化

     select id,title from table where id>9000 order by title limit 10;

七、

1. 表分割优化

水平分割：按时间拆，17年一张表，18年一张表；用户id取余拆分

垂直分割。取出部分字段放到另一个表（主表和详情表）

2.mysql主从复制，主服务器负责更新数据，从服务器负责查询

3.mysql中文分词

八、mysql其他技巧

1.插入数据时，不要循环插入 。 (一起一次插入。)

2.不要将图片存入数据库，用路径代替。(把图片上传到磁盘的某个位置，图片路径存入数据库)

3.伪删除，不真实的删除数据，增加一个字段来标识

4.analyze table 优化语句的执行计划，提高查询效率。(有时看的时候有索引，分析完后没索引。分析表，优化改进)

5.定期执行optimize table整理碎片。主要针对mysome表

6.尽量避免使用null，会增加额外的字节标注，可以使用默认值

7.缓存