mysql调优

mysql 调优

1.性能监控

show variables like 'profiling';    -- 默认是off
set profiling=on;  -- 开启，只在当前客户端有效，每次都需要开启
select * from city where name='Tilburg';
show profiles;  -- 分析当前会话中sql语句执行的资源消耗情况的工具，可以分析cpu,内存，磁盘io次数等
show profile cpu for query 67;
show profile all for query 67; 

show processlist; -- 查看当前数据库线程的连接个数，线程的状态，sleep(睡眠)，locked(等待表锁)，query(查询状态)等

2. 正确存储数据的最小数据类型，更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，int 32(tinyint 8,smallint 16,bigint 64),varchar()

3. 避免使用null列，如果查询中包含可为NULL的列，对mysql来说很难优化，因为可为null的列使得索引、索引统计和值比较都更加复杂

4. char和varchar,固定长度和可变长度，char最大长度255字符，varchar 65535字节。char用来存储数据波动不是很大的数据，身份证，手机号，检索查询效率比varchar 高，以空间换时间。varchar 存储波动量大的数据，更能节省空间（存储一段话，文章等）。

5. MySQL 把每个 BLOB 和 TEXT 值当作一个独立的对象处理。两者都是为了存储很大数据而设计的字符串类型，分别采用二进制和字符方式存储。

6. 使用data类型保存日期时间（3个字节）。不要使用字符串存储日期类型，占用空间大，损失日期类型函数的便捷性

7. 存储引擎：myisam(只支持表锁，不支持事务和外键)。mariadb(支持行锁，表锁，事务，外键)

8. 适当拆分：当我们的表中存在类似于 TEXT 或者是很大的 VARCHAR类型的大字段的时候，如果我们大部分访问这张表的时候都不需要这个字段，我们就该义无反顾的将其拆分到另外的独立表中，以减少常用数据所占用的存储空间。这样做的一个明显好处就是每个数据块中可以存储的数据条数可以大大增加，既减少物理 IO 次数，也能大大提高内存中的缓存命中率。

9. explain+SQL语句来模拟优化器执行SQL查询语句（执行计划），从而知道mysql是如何处理sql语句的。id:大的先执行，相同从上到下执行。table 访问的表，type 访问类型：system>const>ref>all

10. 查询性能低下主要是查询太多不必要的数据，减少数据访问来优化。禁止使用select *,按需查询。使用limit 限制查询的数据数量

11. 确保任何的groupby和order by中的表达式只涉及到一个表中的列，这样mysql才有可能使用索引来优化这个过程

12. 只查询优化：使用关联查询代替子查询

13. 垂直拆分（业务） 水平拆分（大表分为小表）多字段的表不常用的字段拆分

14. 索引列order by asc/desc 要一致，否则索引失效

15. alter table xxx add （unique）index 索引名(列1,列2,列3); 组合索引,按照索引顺序写，范围后面失效

    alter table add key(city(7))

    show index from 表名;

16. 索引

    没有索引全表扫描，数据量大速度慢，索引大大减少了服务器扫描数据的数据量，提高查询效率。

    索引分类：主键索引、唯一索引（保证列数据唯一），普通索引、组合索引（多个字段组成的索引）

    聚集索引：InnoDB聚集索引的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引：

    （1）如果表定义了PK，则PK就是聚集索引；

    （2）如果表没有定义PK，则第一个not NULL unique列是聚集索引；

    （3）否则，InnoDB会创建一个隐藏的row-id作为聚集索引；

    ----聚集索引，叶子节点存储行记录；

    ----普通索引，叶子节点存储PK值，即id；

    覆盖索引：就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖

    回表：（1）先通过普通索引定位到主键值 （2）在通过聚集索引定位到行记录； 需要扫码两遍索引树：
    

    最左匹配：索引从左开始匹配，遇到范围查询>< between,like 时，后面的索引失效

17. myisam 和innodb ，锁

    lock table 表名 write; // 获得写锁（myisam 写锁独占锁，读锁共享锁）

    unlock tables; //释放锁

    myisam：获得写锁（独占锁），其他线程不能读和写。获得读锁：其他线程（包括自己）可以读，但是不能写，但是不能查询其他表

    innodb:读锁（共享锁），自己可以读也可以写，其他线程只能读。获得写锁:自己可以读也可以写，其他线程不能都和写。有索引加行锁，没索引加表锁