Mysql 优化 Notes

• 定位慢查询

  • 联表查询
    select * from dept, emp where dept.deptno=emp.deptno;[简单处理方式]
    select * from dept left join emp on dept.deptno=emp.deptno; [左外连接，更ok!]
  • reset query cache;
  • ，通过主键索引查找，只需要id,所以达到索引覆盖，然后再根据取出来的id，这也是运用主键索引来查找,这种技巧就叫做延迟索引.
    
  • 不要用子查询，很慢

• 索引种类
  索引使查询变快，但是增删改会变慢.
  1. 主键索引（当一张表，把某个列设为主键的时候，则该列就是主键索引）
  2. 唯一索引
  3. 普通索引
  4. 全文索引（主要是针对对文件，文本的检索, 比如文章, 全文索引针对MyISAM有用.
  ）–FULLTEXT (title,body)–
  select * from articles where match(title,body) against(‘database’);

  • myisam的索引，指向的是数据在磁盘的位置，
    innodb的索引，指向的是对主键值的引用
  • 聚簇索引，包含索引又包含数据，innodb的主键索引就是聚簇索引，其他的索引都是指向这个主键索引，从而避免数据冗余
  • 索引覆盖就是依靠索引就以及足够查到数据了，比如对一些经常查询的字段建立联合索引，这样就避免回行查找，就到对应的内存引用中找了
  • Innodb如果其他字段数据量过大，那么即使用上索引覆盖也会比较慢，因为索引数据比较大，导致各个节点之间相距较远，页数较多。
  • 表示索引覆盖
    
    Innodb如果其他字段数据量过大，那么即使用上索引覆盖也会比较慢，因为索引数据比较大，导致各个节点之间相距较远，页数较多。
  • 主键索引与联合索引
    
    Order by id 时使用的是InnoDB的主键索引，这个索引包含表的所有数据，所以查起来比较慢，而order by id，ver 使用的是id，ver的联合索引，这个索引比较小，因为它不包含表的所有数据，只需要指向主键索引就行了，而且这个联合所以已经包含id字段，所以不需要进行回行查找查询其他字段的内容。
    其实就是使用的索引不一样。
    如果是myisam,则几乎一样快，严格上id比id,ver稍微快。

• 如何选择mysql的存储引擎
  在开发中，我们经常使用的存储引擎 myisam / innodb/ memory

  • myisam 存储: 如果表对事务要求不高，同时是以查询和添加为主的，我们考虑使用myisam存储引擎. ,比如 bbs 中的 发帖表，回复表.
  • INNODB 存储: 对事务要求高，保存的数据都是重要数据，我们建议使用INNODB,比如订单表，账号表.
  • Memory 存储，比如我们数据变化频繁，不需要入库，同时又频繁的查询和修改，我们考虑使用memory, 速度极快. (能正常插入数据，查询数据，但重启mysql之后，数据全部丢失)
  • MyISAM 和 INNODB的区别
    
    1. 事务安全
    2. 查询和添加速度
    3. 支持全文索引
    4. 锁机制
    5. 外键 MyISAM 不支持外键， INNODB支持外键. (在PHP开发中，通常不设置外键，通常是在程序中保证数据的一致)

• 选择合适的数据类型

  1. 在精度要求较高的应用中，建议使用定点数来存储数据，以保证数据的准确性。使用deciaml而不是float
  2. 对于存储引擎是MyISAM的数据库，如果经常做删除和修改数据操作，要定时执行optimize table table_name 对表进行碎片整理。
     （如果你的数据库的存储引擎是myisam,请一定记住要定时进行碎片整理，因为Myisam 删除了数据之后，table.NYD大小不变化）

• 分表技术有(水平分割和垂直分割)

  • 当一张越来越大时候，即使添加索引还慢的话，我们可以使用分表
  • 如果一张表某个字段，信息量大，但是我们很少查询，则可以考虑把这些字段，单独的放入到一张表中，这种方式称为垂直分割

• 文件，图片等大文件用文件系统存储，数据库只存路径

---

索引

• 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件.select * from emp where sex = '男'
• 更新非常频繁的字段不适合创建索引
• 索引使查询变快，但是增删改会变慢（不建索引的话Select *会检索全部记录，建了索引也就是建立二叉树文件，通过算法提高效率。
  索引记录数据的物理位置，所以数据库不可以随便移动，否则数据在，但是查询速度变慢）
• 总结: 满足以下条件的字段，才应该创建索引.
  a: 肯定在where条经常使用
  b: 该字段的内容不是唯一的几个值(sex)
  c: 字段内容不是频繁变化.
• 使用索引的注意事项
  
  1. 对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用。
  2. 对于使用like的查询，查询如果是 ‘%aaa’ 不会使用到索引‘aaa%’ 会使用到索引。
  3. 如果条件中有or，即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段，都必须建立索引, 我们建议大家尽量避免使用or 关键字
  4. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。
  5. 如果mysql估计使用全表扫描要比使用索引快，则不使用索引。
• 如何查看索引使用的情况:show status like ‘Handler_read%’; handler_read_key:这个值越高越好，越高表示使用索引查询到的次数。handler_read_rnd_next:这个值越高，说明查询低效。
+

慢查询

• show variables like ‘long_query_time’ ; //可以显示当前慢查询时间

• 在默认情况下mysql不记录慢查询日志，需要在启动的时候指定 
  bin\mysqld.exe - -safe-mode  - -slow-query-log [mysql5.5 可以在my.ini指定] 
   bin\mysqld.exe –log-slow-queries=d:/abc.log [低版本mysql5.0可以在my.ini指定] 
   

+

存储引擎

• myisam 存储: 如果表对事务要求不高，同时是以查询和添加为主的，我们考虑使用myisam存储引擎. ,比如 bbs 中的 发帖表，回复表.
• INNODB 存储: 对事务要求高，保存的数据都是重要数据，我们建议使用INNODB,比如订单表，账号表.
• MyISAM 和 INNODB的区别
  
  1. 事务安全
  2. 查询和添加速度
  3. 支持全文索引
  4. 锁机制
  5. 外键 MyISAM 不支持外键， INNODB支持外键. (在PHP开发中，通常不设置外键，通常是在程序中保证数据的一致)
• Memory 存储，比如我们数据变化频繁，不需要入库，同时又频繁的查询和修改，我们考虑使用memory, 速度极快.
  重启mysql之后，数据全部丢失
• 如果你的数据库的存储引擎是myisam,请一定记住要定时进行碎片整理,optimize table test100;


备份

(1)手动备份数据库(表的)方法cmd控制台:mysqldump –u root –proot 数据库 [表名1 表名2..]  > 文件路径比如: 把temp数据库备份到 d:\temp.bakmysqldump –u root –proot temp > d:\temp.bak如果你希望备份是，数据库的某几张表mysqldump –u root –prot temp dept > d:\temp.dept.bak如何使用备份文件恢复我们的数据.mysql控制台source d:\temp.dept.bak(2)使用定时器来自定完成把备份数据库的指令，写入到 bat文件, 然后通过任务管理器去定时调用 bat文件.mytask.bat 内容是:C:\myenv\mysql5.5.27\bin\mysqldump -u root -proot temp dept > d:\temp.dept.bak☞ 如果你的mysqldump.exe文件路径有空格，则一定要使用 “” 包括.把mytask.bat 做成一个任务，并定时调用在 2:00 调用一次步骤 任务计划->增加一个任务，选中你的mytask.bat文件 ，最后配置

表数据

• frm是表的结构，MYD是表的数据，MYI是表的索引

其他

• 查询当前连接数：netstate -an
• 显示进程号：netstate -anb

---

• 不使用in(子查询)
  SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2 WHERE id = 1)
  MySQL优化器优化之后：
  SELECT s1 FROM t1 WHERE EXISTS (SELECT t2.s1 FROM t2 WHERE t1.s1 = t2.s1 AND t2.id = 1
  优化语句：
  SELECT t1.s1 FROM t1 JOIN (SELECT s1 FROM t2 WHERE id = 1) tmp ON t1.s1 = tmp.s1;

• 尽量用单表查询，避免多表JOIN，禁止多于3表join，join的字段数据类型必须绝对一致

• 存储时间（精确到秒）建议使用TIMESTAMP类型(虽然最大到2038)，因为TIMESTAMP使用4字节，DATETIME使用8个字节，同时TIMESTAMP具有自动赋值以及自动更新的特性。
• 数字类型not null default 0，字符类型not null default ”，时间not null default ‘1970-01-01 00:00:00’或者 ‘0000-00-00 00:00:00’
• 新增排序要求：不鼓励在DB里排序，特别是只有1000行一下的，请在app server上排序，app server有上百台，而db仅仅个位数的服务器数量，排序都在db，会把db压垮的，特别是禁止上千行的排序在db这边。（看业务）
• 有别于char(x)和varchar(x),int(x)中的x表示的是 整型(tinyint smallint mediumint int bigint)在添加 了zerofill描述符后的显示宽度,所以在不添加 zerofill描述符的时候, int(1) 和 int(10) int(100) 都没什么区别。
  id int(20) zerofill DEFAULT NULL
• 优化LIMIT分页
  因为:select * from user limit 100000,1 对于 MySQL来讲是:先扫描100000行丢弃掉,再读取 一行。
  因此:千万不要用 LIMIT offset, row_count 来做 分页。
  优化思路是:
  
  1. 砍掉跳页功能(直接到XX页,最后一页等)
  2. 每次读取根据上一页的最大ID做范围查询
  3. 这也会优化掉频繁的count(*)
     select * from xxx where id>xxxx limit x;
select * from xxx where id>xxxx and etc. limit x;
• 隐式转换的问题
  char 类型查询一定要加’22333’，否则Explain 的type为ALL而不是const
• 在列上做计算导致用不上索引