Mysql 索引优化

  1. 索引的存储分类
    MyISAM 存储引擎的表数据和索引是自动分开存储的,各自是独一的一个文件
    Innodb 存储引擎的表数据和索引是存储在同一个表空间里面,但可以由多个文件构成。
    Mysql 目前不支持函数索引,但是能对列的前面某一部分进行索引
    例如 name 字段,可以只取 name 的前 4 个字符进行索引,可降低索引文件大小。
    Mysql> create index ind_company_name on company(name(4));
    说明:ind_company_name 索引名,company 表名
  2. 使用 like 的查询,后面如果是常量,并且 % 号不在第一个字符,索引才可能被用上
    eg: Mysql> select * from t1 where id like "3%"; 推荐
    Mysql> select * from t1 where id like "3%"; 不推荐 (索引用不上)
  3. 如果对大的文本进行索引,使用全文索引而不使用 like"%….%"
  4. 如果列名是索引,使用 column_name is Null 将使用索引。
    eg: Mysql> desc select * from company where name is null\G
  5. 存在索引但不使用索引
  6. 如果 Mysql 估计使用索引比全表扫描更慢,则不使用索引。
    例如:如果列 Key_part 均匀的分布在 1-100 之间,查询时使用索引就不好。
    eg:Mysql> select * from table_name where key_part>1 and key_part<90;
  7. 如果使用 MEMORY/HEAP 表并且 where 条件中不适用 “=” 进行索引列,那么不会用到索引。Heap 表只有在 “=” 的条件下会使用索引。
  8. 用 or 分割开的条件,如果 or 前的条件中的列有索引,而后面的列没有索引,那么涉及的索引都不会被使用。
  9. 如果不是索引列的第一部分,那么查询中这个索引也不会被 Mysql 采用。
    eg: Mysql> desc select * from sales where moneys=1\G
    Moneys 上面建有复合索引,但是 moneys 不是索引的第一列。
  10. 如果列类型是字符串,但是查询时把一个数值型常量复制给了一个字符型的列名 name,那么虽然 name 列有索引,也不会使用。
    eg: Mysql> desc select * from company where name=123\G (字符串索引使用整形数据查询,不会使用索引)
    Mysql> desc select * from company where name="123"\G (使用上索引)
    查看 Mysql 索引使用情况
    Mysql> show status like ‘Handle_read%‘;
    如果索引正在工作,Handler_read_key 的值将很高,这个值代表索引被使用次数。
    Handler_read_rnd_next 的值高意味着查询运行低效,并且应该建立索引补救。

    2个简单实用的优化技巧
    a、定期分析表和检查表
    Mysql> ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tab_name [,tab_name1]…;
    本语句用于分析和存储表的关键字分布,分析结果可以使系统得到准确的统计信息。使 SQL 能够生成正确的行计划。
    eg: Mysql> analyze table sales;
    分析表的语法如下: (检查一个或多个表是否有错误)
    Mysql> CHECK TABLE tab_name [,tab1_name]…[option]…option={QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
    eg: Mysql> check table sales;
    b、定期优化表
    优化表的语法格式:
    Mysql> OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] tab_name [,tab_name1]..
    如果已经删除了表的一大部分,或者已经对含有可变长度行的表进行了很多的改动,则需要定期优化。
    这个命令可以将表中的空间碎片进行合并,但是只对 MyISAM、BDB 和 Innodb 表起作用。
    eg: Mysql> optimize table sales; (不要在 Mysql 访问数大的情况下使用)

    常用 SQl 的优化
    大批量插入数据
    当用 load 命令导入数据的时候,适当设置可以提高导入速度
    对于 MyIASM 存储引擎的表,可以用个一下方式快速导入大量数据
    ALTER TABLE Tab_name DISABLE KEYS loading the data
    ALTER TABLE Tab_name ENABLE KEYS
    DISABLE KEYS 和 ENABLE KEYS 用来打开或关闭 MyISAM 表非唯一索引的更新,可以提高速度,对 Innodb 无效。

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。