Mysql 索引

提升查询速度最快的方案就是索引的正确使用

不使用索引类似全表扫描

例如t1 , t2 , t3三张表，我们给关联字段加上索引，此时t1是全表扫描，而t2和t3则是进行带索引的搜寻，

mysql使用索引的方式有以下几种：

1 把与where子句所给出的条件相匹配的数据行尽快找出来，2是把关联操作中把与数据表里的数据行相匹配的数据行尽快找出来

3 把min 和 max函数的查询，如果该列带索引，那么他的最小值和最大值能够被迅速找到而不用通过检查的方法来查找

4 mysql经常使用索引来迅速的完成order by 子句和group by 子句的分类和分组的操作

索引的缺点：

索引降低了插入，删除，修改数值的速度，因为做这些操作时都需要让索引做出改变，索引越多改变越多

索引要占据磁盘空间，多个索引会占据更大的空间，MyISAM引擎中，大量的索引一个数据表有可能使索引文件比数据文件更快的到达他的最大尺寸

InnoDB共享表空间里的全部InnerDB数据表分享着同一个存储空间，索引会使表空间用于存储的空间更快的减少

索引的挑选：

尽量为用来搜索，分类，或分组的数据列编制索引，不要为作为输出显示的数据列编制索引，

也就是最适合建索引的数据列是那些在where子句中出现的数据列，在关联子句中给出的数据列，或者是

Order By 或Group By子句中出现的数据列，根据SELECT关键字出现在输出数据列清单里的数据列最好不要有

索引

1. 综合考虑各个数据列：

该列中独一无二的值越多，重复的值越少，索引的使用效果也就越好，当该列中出现的某个值的频率超过30%时

查询优化程序通常会跳过索引，进行全表扫描，还有很多考虑的因素

2. 对短小的值进行索引，也就是例如char的长度，对于InnoDB存储引擎而言，因为是聚集索引，他是把数据行和主键值

集中保存在一起的情况，其他的索引都是些二级索引，所以主键值比较长的话，就会导致每一个二级索引都需要占用更多的存储空间

3. 为字符串值的前缀编索引

例如对于CHAR(200),大多数前10个或20个字符都是唯一的，这时可以为前面的这些字符编索引，可以大大节省空间的浪费，使查询速度加快

4. 充分利用最左边的前缀

当创建n个数据列的符合索引时，一个复合索引在工作时相当于n个索引，例如state，city，zip建联合索引

建好的结果为：

state,city,zip

state,city

state

此时按照city来索引就不存在了

5. 适可而止不要建立太多的索引，每一个多出的索引都要占据额外的磁盘空间，而且都会影响写入操作的性能，

建立时还要考虑你要添加的索引是否是一个已经 存在的多个数据列索引的最左边的前缀，如果是这样就不需要建了

事实上已经有这个索引了，除了对于FULLTEXT索引例外.

6 让索引的类型与你打算进行的比较操作的类型保持匹配：

InnoDB总是使用"B树"索引，MyISAM也使用"B树"索引，但是遇到空间数据类型时会改用"R树"索引

MEMORY默认使用散列索引，但也支持"B"树，

挑选索引时，一定要考虑打算在被索引的数据列上进行什么类型的比较操作：

(1)，对于散列索引：使用 = 或 <=>操作符进行匹配比较比较快，但是用来查找一个范围的比较操作里表现不佳例如

id < 30

(2). "B树" 索引在使用< <= = >= <> 和between操作符进行的精确比较或范围操作很有效率，索引定义里加上USING BTREE

利用"慢查询"日志找出性能低劣的查询，这个日志可以帮助我们找出有可能受益于使用索引的查询命令，"慢查询"

日志是一个文本文件，可以用一种阅读程序去查看， 使用mysqldumpslow工具程序查询他的内容，如果在这个日志里经常出现就说明代码不够优化

应该尝试进行优化。

Mysql 查询优化程序

EXPLAIN SELECT * FROM tbl_name WHERE FALSE;

EXPLAIN 返回较多信息，包括将要用于扫描数据表的索引(除NULL外)的信息，将要用到的连接类型，以及扫描的数据行数目

工作原理：小表放前，减少工作量，

数据表名字的后面利用FORCE INDEX, USE INDEX 和 IGNORE INDEX 限定词告诉服务器你想要使用的那些索引

还可以利用STRAGHT_JOIN强制优化器按限定的顺序使用数据表，MYSQL优化器会自行确定按照何种顺序扫描数据表

才能最快的把数据行检索出来，但是少数场合，优化器的决定不一定是最优秀的，此时可以使用STRAIGHT_JOIN关键字

"覆盖"优化器的选择，它类似于一个交叉关联，但将迫使数据按照他们在FROM子句里出现的先后顺序相互关联

STRAIGHT使用的位置有：其一放在SELECT与输出数据列清单之间，其二是放在FROM子句后

尽量使用数据类型相同的数据列进行比较，对于带有索引的数据列进行比较时，如果数据类型相同，查询性能会高一些

对WHERE子句来说：

像 SELECT * FROM mytbl WHERE YEAR(date_col) < 1990；这个表达式是不会带索引去比较的，会使用全表扫描

只有使用准确的日期，数据列date_col 的索引就可以使用，例如：  SELECT * FROM mytbl WHERE date_col < '1990-01-01'；

不要在LIKE模式的开始位置使用通配符，不要处于习惯将符号“%”放在字符串的两侧

利用优化器的长处：连接的优化效果比子查询的优化效果更好一些，

避免过多使用MySQL的自动类型转换功能，把一个字符串数据与一个数值相比较，也会阻止索引的使用，假设编写

下面命令：SELECT * FROM mytbl WHERE str_col = 4;

EXPLAIN输出的结果表明

type字段的值表明：优化器可以使用索引来搜索一个特定区间的值

possible key和 key： 前者表明可能的一个候选索引，后者表明最终会被实际用到的索引

row 字段表示会扫描的行数信息

具体到关联的操作，无论如何都需要对其中一个数据表进行全表扫描，而进行全表扫描是不需要任何索引的

运行 analyze table t1,t2命令，分析t1和t2数据表并再次运行EXPLAIN语句，就可以在rows字段里看到更精确的预估值了

为提高查询效率而挑选数据类型：

尽量使用数值操作，少用字符串操作，数值会比字符串运算快的多，字符越长比较的次数越多，多使用ENUM或SET

INET_ATON()和INET_NTOA()函数都可以帮我们完成IP地址在整数和字符串两种形式之间的转换

varchar或char定义的长度要适合最好，MyISAM引擎最好使用CHAR而不是VARCHAR，VARCHAR类型会造成

更多的碎片，需要定期使用OPTIMIZE TABLE语句保持性能，固定长度就没有这样的问题

固定长度的数据列虽然速度快，但是会占用更多的空间，对于InnoDB数据表使用Varchar类型会更有力

尽量声明数据列为NOT NULL，这样Mysql就不需要在处理期间检查该数据列的值是不是NULL

利用PROCEDURE ANALYSE()语句分析数据表，看它对数据列的声明提出哪些建议:

select * from t procedure analyse();

select * from t procedure analyse(16,256);

第二条含义是如果数据列的不同取值在16个以上或者长度超过了256字节，可以根据具体情况改变这两个数字

就不用提出使用ENUM类型的建议了。

对容易产生碎片的数据表进行清理，BLOB或TEXT数据列受到的影响往往最大，因为他们的长度变化最大

定期使用OPTIMIZE TABLE语句防止数据表查询性能降低

尽量避免对很大的BLOB或TEXT值进行检索，最好把BLOB或TEXT数据列剥离到单独一个数据表里

使用LOAD DATA语句比使用INSERT语句效率高，因为索引只是在所有数据行都处理完后才需要刷新，不是每处理一行都刷新

使用LOAD DATA语句比使用LOAD DATA LOCAL语句效率高，前者文件位于服务器上，必须要有FILE权限，而后者客户程序要从

网络上接收文件，会慢一些

使用mysqldump工具程序生成数据库备份文件，他会默认生成含有多个数据行的INSERT语句，你也可以使用--opt优化选项

它自动的打开--extended-insert 选项，生成含有多个数据行的INSERT语句，以及其他一些在重新加载时是转储文件得到高效处理的选项

要避免使用mysqldump工具程序的--complete-insert 选项，否则，产生的INSERT语句将用于单个的数据行，但是如果非要使用

--complete-insert选项，要尽量减少索引的刷新次数，对于事务性存储引擎，要在一个事务内发出INSERT语句而不用自动提交的方式来实现这一点

START TRANSACTION; INSERT INTO....; COMMIT;

对于非事务性的存储引擎，获得数据表的写锁定，在数据表被锁定时，发出INSERT语句

LOCK TABLES tbl_name WRITE; INSERT INTO....; COMMIT; UNLOCK  TABLES;

这样一来以上两种情况就获得了相同的处理，索引只在语句执行完成后彩刷新，而不是每执行一个insert就刷新一次

如果使用自动提交或是数据表没有锁定，就会出现这样的问题

在MyISAM数据表中，减少刷新次数的另一个策略就是DELAY_KEY_WRITE数据表索引，这样键缓存只在必要时刷新，不会每次插入新索引

就立刻刷新一次，要使用这个选项必须在启动mysqld程序时给出delay-key-write = ALL选项，启动服务器还给出了--myisam-recover=FORCE

这个选项将强制服务器在打开MyISAM数据表时对他们进行检查并自动的进行必要的修复， --compress命令来进行压缩操作

ALTER TABLE tbl_name DISABLE KEYS
ALTER TABLE tbl_NAME ENABLE KEYS 这两句是对非唯一索引进行关闭和打开.

调度和锁定问题

对于MyISAM，MERGE，MEMORY存储引擎来说，调度策略是由数据表锁完成的，只要客户访问数据表，首先锁定他，完成后进行解除

使用LOCK和UNLOCK语句来显示的获得和解除锁定

改变语句的执行优先级

使用延迟插入，当队列中没有读取者，然后服务器才开始延迟的将数据行队列插入数据行，

并发操作：

MyISAM类型的数据表检索时非常快，使用数据此数据表的锁定对混合检索和修改可能不利，而InnoDB数据表可能提供较好的性能，因为锁定只是

数据行级别，范围减小，减少竞争问题，提供并发，但是表锁不会有死锁，但是并发就可能产生死锁，这也是InnoDB的问题

系统管理员所完成的优化：

MyISAM的索引块的大小由服务器变量key_buffer_size 控制，尽量增大他

InnoDB存储引擎也有自己的缓冲数据和索引值的缓存，大小由innodb_buffer_pool_size 系统变量控制

使用查询缓存：

show variables like 'have_query_cache'系统变量查看

0 不缓存查询的结果

1 缓存查询，但是不包括select sql_no_cache开头的查询

2 只缓存以select sql_cache开头的查询

query_cache_type 决定查询缓存的操作模式

query_cache_size 决定为缓存分配的内存大小

query_cache_limit 设置缓存的最大的结果集的大小，比这个值大的查询结果不能被缓存

硬件优化：

在机器里安装更多的内存，在物理设备之间分散磁盘读写活动，提高并行读，通过多个物理设备将读和写分开，比在一个设备上读和写要快

例如数据库存储在一个设备上，将日志存储在另外一个设备上，这样也会快，注意在一个物理设备上采用不同分区是没有用的，不算并行

RAID设备的使用也可以给你一些并行的益处，使用多处理器硬件。