mysql一: 索引优化

一：基础知识

1.索引的存储类型有两种

myisam     btreeinnodb      btreememory/heap hash，btree

2.myisam表的数据文件和索引文件是分开的，innodb的数据和索引是存储在同一个表空间里面，但是可以有多个文件组成

二：检测

1.使用explain 检测

mysql> explain select count(*) from core_config_data where config_id=1;
+----+-------------+------------------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table            | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+------------------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | core_config_data | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |
+----+-------------+------------------+-------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)

type 这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALLpossible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好ref：显示索引的哪一列被使用了，如果可能的话，是一个常数  rows：MYSQL认为必须检查的用来返回请求数据的行数

其他不在详细，参看：http://blog.csdn.net/terry_water/article/details/46325275

配合语句，查看表结构：

show create table core_config_data;

2.使用系统参数查看 

<pre name="code" class="html">show status like '%Handler%';

 查看索引的使用量

详细参看：http://blog.csdn.net/terry_water/article/details/46324563

Handler_read_key，这个数值越大，说明使用的索引的次数越多

3.使用慢查询，把时间长的查询写入日志：

slow_query_log_file=/var/log/mysql-slow.loglong-query-time=10slow_query_log=1log-output=FILE

把时间长的查询找出来优化

4.通过访问前端，页面的加载速度，判定某个页面存在问题，然后在本地访问一个页面，开启日志：

在配置中添加

general_log=ONgeneral_log_file=/var/log/mysql/access.log

然后分析访问的sql语句，通过explain 查看每个语句执行，看看那个存在问题

5.

log-error=/var/log/mysql-error.log

查看错误日志。

6

 从 PROCEDURE ANALYSE() 取得建议

PROCEDURE ANALYSE() 会让 MySQL 帮你去分析你的字段和其实际的数据，并会给你一些有用的建议。只有表中有实际的数据，这些建议才会变得有用，因为要做一些大的决定是需要有数据作为基础的。

例如，如果你创建了一个 INT 字段作为你的主键，然而并没有太多的数据，那么，PROCEDURE ANALYSE()会建议你把这个字段的类型改成 MEDIUMINT 。或是你使用了一个 VARCHAR 字段，因为数据不多，你可能会得到一个让你把它改成 ENUM 的建议。这些建议，都是可能因为数据不够多，所以决策做得就不够准。

在phpmyadmin里，你可以在查看表时，点击 “Propose table structure” 来查看这些建议

 

一定要注意，这些只是建议，只有当你的表里的数据越来越多时，这些建议才会变得准确。一定要记住，你才是最终做决定的人。

7

查看索引使用情况

如果索引正在工作，Handler_read_key的值将很高，这个值代表了一个行被索引值读的次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使用。

Handler_read_rnd_next的值高则意味着查询运行低效，并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明表索引不正确或写入的查询没有利用索引。语法：
    mysql> show status like 'Handler_read%';

不使用索引的情况：

mysql估计索引比全表扫描更慢，会不使用索引，例如：如果key_part1均匀分布在1和100之间，下列查询中使用索引就不是很好：
    SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90

2.如果使用heap表并且where条件中不用＝索引列，其他> 、<、 >=、 <=均不使用索引；
3.如果不是索引列的第一部分；
4.如果like是以％开始；
5.对where后边条件为字符串的一定要加引号，字符串如果为数字mysql会自动转为字符串，但是不使用索引。