优化-MYSQL数据库文档.docx
目录
TOC\o1-3\h\z\u一、mysql优化概述 1
二、分析需要优化的语句 1
1、使用mysql里面的慢查询日志， 1
2、使用profiles机制 3
三、索引 4
1、索引的类型 4
2、如何创建索引 4
3、删除索引 5
4、查询索引： 5
5、创建索引的注意事项 5
四、索引结构 6
五、explain(执行计划)工具的使用 7
六、使用执行计划，完成sql语句索引执行情况查询； 8
1、多列索引： 8
2、对于使用like的查询，查询如果是”%aaa”,不会使用到索引，‘aaa%’会使用到索引。 10
3、如果条件中有or，则要求or的索引字段都必须有索引，否则不能用到索引。 11
4、如果列类型是字符串，一定要在条件中将数据使用引号引用起来，否则不使用索引。 12
5、优化groupby语句。 12
6、当取出的数据量超过表中数据的20%，优化器就不会使用索引，而是全表扫描。 13
七、索引覆盖 14
八、前缀索引， 14
九、翻页优化 15
十、碎片整理 17
十一、锁机制讲解 18
1、表锁的演示： 19
2、行锁的演示： 21
十二、分区分表技术 21
1、分区类型： 22
一、mysql优化概述
页面静态化的内容，memcache，减少数据库的访问，提高网站的访问速度，无论如何优化，还是要操作数据库的，要从数据库的角度来优化，提高访问速度。
设计角度：存储引擎的选择，字段类型选择，范式
利用mysql自身的特性：索引，查询缓存，分区分表，存储过程，sql语句优化配置，
部署大负载架构体系：主从复制，读写分离。
硬件升级：
二、分析需要优化的语句
要查找执行速度比较慢的sql语句，
1、使用mysql里面的慢查询日志，
慢查询日志，是由mysql提供的，用于记录sql执行时间超过了某个时间界限，该时间界限我们可以自己设定，比如我们设定的时间界限为0.5秒，开启慢查询日志后，会自动记录执行时间超过0.5秒的sql语句。慢查询日志默认没有开启，默认的时间界限是10秒。
（1）如何开启慢查询日志，
方式一：打开mysql的配置文件,my.ini,添加如下语句：修改完成后，要重启mysql服务。
我们设置的时间界限。如果不设置则默认是10秒设置慢查询日志的保存位置，该保存位置，可以自己随意设置，此处的slow-log
我们设置的时间界限。如果不设置则默认是10秒
设置慢查询日志的保存位置，该保存位置，可以自己随意设置，此处的slow-log是慢查询日志的名称，该名称也是可以自己随意的设置。
如果log-slow-queries后面没有设置值，则会采用默认的名字保存在默认的位置。该默认位置和表文件的存储位置一致。
设置数据库和表文件的目录。
设置数据库和表文件的目录。
（2）开始测试，是否记录执行时间超过0.5秒的sql语句。
selectbenchmark(执行次数,表达式);
在慢查询日志文件里面查看是否记录：
（3）相关的一些命令，
查看慢查询日志的时间界限
可以在当前会话中进行修改慢查询的时间界限：
语法：setlong_query_time=时间界限
2、使用profiles机制
该机制开启后，会记录每个sql语句的执行时间，精确到小数点后8位。
如果开启：setprofiling=1|0值为1则是开启，为0则是关闭。
查看sql语句的执行时间:showprofiles
一般sql语句执行比较慢，原因是没有添加索引，
没有添加索引之前：
添加索引之后使用的时间：
三、索引
索引：利用字段的某些属性，快速的定位数据（磁盘，柱面，磁道，扇区）
1、索引的类型
唯一索引（uniquekey）:字段数据是唯一的，数据内容里面能否为null,在一张表里面，是可以添加多个唯一索引。
主键索引(primarykey)：数据记录里面不能有null,数据内容不能重复，在一张表里面不能有多个主键索引。
普通索引(index)：使用字段关键字建立的索引，主要是提高查询速度。
全文索引(fulltextindex)：在比较老的版本中，只有myisam引擎支持全文索引，在最新的版本中（可能是mysql.5.6）innodb引擎也支持全文索引，在mysql中全文索引不支持中文。
2、如何创建索引
（1）在创建表的时候，同时创建索引，
createtablestu(
idintprimarykeyauto_increment,
namevarchar(32)notnull,