mysql调优1（profile,数据类型优化，索引优化，执行计划）

目录

性能监控：使用profile 来查询运行性能

schema和数据类型优化：

1、数据类型的优化

2、合理使用范式和反范式

3、主键的选择

4，字符集的选择

5、存储引擎的选择

6、适当的数据冗余——是要join回降低查询速度

7、适当拆分-表中有的字段非常大，且不常用，可以拆分出去，

执行计划

 

通过索引进行优化

1，索引基本知识

2、mysql为啥采用b+数结构？

3、myISAM和InnorDB都是b+树，但是有区别：

---

性能监控：使用profile 来查询运行性能

set profiling=1;  默认不开启，需要手动开启下

show profiles;  展示运行时间

show profile for query 1;  展示一条数据时间

show processlist; 

查看连接的线程个数，来观察是否有大量线程处于不正常的状态或者其他不正常的特征

 

schema和数据类型优化：

1、数据类型的优化

• 更小的更好，也就是说小的数据类型占用更小的磁盘空间，内存和cpu缓存
• 简单就好，能用整形不要用字符串。日期和时间使用data类型，不要使用字符串存
• 尽量避免null ,因为可以为null的列使得索引变得更加复杂
• 实际细则：

 整数类型：

可以使用的几种整数类型：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT分别使用8，16，24，32，64位存储空间。 尽量使用满足需求的最小数据类型

字符串：char(最大255，会自动删除末尾的空格)，varchar(可变长度)，text(不设置长度，一般尽量不要用)

• datetime（8个字节）和timestamp （4个字节，精确到秒），data(3个字节，函数计算)
• 使用枚举类代替字符串

 create table enum_test(e enum('fish','apple','dog') not null);  insert into enum_test(e) values('fish'),('dog'),('apple');  select e+0 from enum_test;

• 特殊类型数据 ，

INET_ATON()和INET_NTOA函数

 

2、合理使用范式和反范式

范式优点：防止数据冗余

反范式优点，不需要级联，有时候查询许多快，空间换时间

3、主键的选择

代理主键>自然主键

代理主键更容易维护

4，字符集的选择

  latin1  能用拉丁存储的，没必要用之外的字符集

   中文utf-8(占3字节)，不用GBK可能会乱码

5、存储引擎的选择

MyISAM 直接锁表，

InnorDB 锁行，mysql默认的，加锁是通过给索引上锁，所以如果给重复数据上索引，可能也会出现锁几行的问题

6、适当的数据冗余——是要join回降低查询速度

7、适当拆分-表中有的字段非常大，且不常用，可以拆分出去，

 

执行计划

详见-。。。

 

通过索引进行优化

1，索引基本知识

优点：减少需要扫描的数据量

          帮助服务器避免排序和临时表

          随机io表顺序io

索引分类：

       主键索引

       唯一索引

       普通索引

       全文索引

       组合索引

技术名词：

     回表（建普通索引查询全列数据）

    索引覆盖（建普通索引，只查询id,不要回表）

    最左匹配（组合索引使用，比如（name+age）,可以单独查name ,但是不能单独查age）

   索引下推

2、mysql为啥采用b+数结构？

二叉树-avl数-红黑树（缺点：层级深，每一层，相当于一次io）

B数：多叉树，枝干节点又存指针，又存实际的数据，mysql一次io默认读16k数据，那么假设一个磁盘块16k存一个节点，如果在这个节点里面data数据太大，则能存的指针就少了，则可能这个多叉数的叉数变少了，那么树的深度就要增加，

 

B+树：B树的升级，只在叶子节点存data，非叶子节点存指针，这样这个多叉则尽可能多点，减少数深度，

3、myISAM和InnorDB都是b+树，但是有区别：

InnorDB,索引和数据在同一个文件  聚簇索引

MyISAM 索引和数据分开存放      非聚簇索引