mysql 数据库优化

类型的选择

• 选取最适用的字段属性

• 表的类型

选取最适用的字段属性

            数据库最基本的优化方式之一是使数据在磁盘上占据的空间尽可能小。MySQL可以很好地支持大数据量的存取，但数据库中的表越小，执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，应该将表中字段的宽度设得尽可能小。

表的类型

           MyISAM表类型是如今MySQL默认的类型，它是以ISAM为基础发展的。MyISAM表比ISAM要小，因而占用更少的资源创建，访问速度快。创建MyISAM表时不需要定其类型。

使用索引

• 创建索引

• 索引表查询的影响

• 查看索引的使用

• 分析索引的使用

• 删除索引

•

创建索引

          给现有的表增加一个索引，可用ALTERTABLE语句来实现，其语法结构为：

          ALTER TABLE tb_name ADD INDEX index _name(column_list)

          ALTER TABLE tb_name ADD NIQUE index _name(column_list)

          ALTER TABLE tb_name ADD RIMARY KEY index_name(column_list)

          其中，tb_name是要增加索引的表名。INDEX、UNIQUE和RIMARYKEY为索引的三种类型，分别为普通索引、唯一索引和主键索引。index _name为索引名（可选），column_list为要进行索引的列。多个列时各列之间用逗号分隔。如为表classone设置索引。

          ALTER TABLE classone ADDINDEX math (age);

          ALTER TABLE classone ADD NIQUE math (age);

         ALTER TABLE classone ADD RIMARY KEY math (age) 

         ALTER TABLE classone ADD INDEX math (age,numb);

索引表查询的影响

          索引被用来快速找出在某列上使用某一特定值的行。没有索引，MySQL将从第一条记录开始一直读完整个表，直到找出与其相关的行。表越大，花费时间越多。如果表查询的列有一个索引，MySQL能快速去搜寻索引部分的数据，而不必考虑所有数据。

查看索引的使用

          可以通过EXPLAIN语句查看是否使用了索引。查看索引的使用与查询的结构类似，只需将查询语句后面加上“\G”即可。

           如果查询字段的类型为字符串，那么一定要在WHERE条件中把字符常量值用引号引起来，否则即便这个列上有索引，数据库也不会用到。下面通过实例来查看索引的使用。

分析索引的使用

          要分析索引所创建的索引是否合理，可以通过SHOWSTATUS命令查看索引的使用情况，例如查看test数据库下的class2的使用情况。

删除索引

          可利用DROP INDEX或ALTERTABLE语句来删除索引，其语法结构如下：

           DROP INDEX index_name on tb_name

           ALTER TABLE tb_name DROP INDEX index_name

             index_name为要删除的索引名，tb_name为索引所在的数据表。如果删除该字段上的索引，其字段不会被删除。

锁定表

• READ 锁定

• WRITE 锁定

• 查询表级锁 

READ锁定

           MyISAM表的READ锁定不会阻塞该用户的读请求，但更新或写入都会提示错误，同时不能查询其他没有锁定的表，也会阻碍其他用户对该表的写入。表锁可以通过LOCK TABLE实现。

WRITE锁定

           MyISAM的WRITE锁定，即不阻碍该用户的读操作，也不阻碍更新和写入操作，但会阻碍其他用户的读和写操作。下面通过实例讲解WRITE锁定问题。

查询表级锁

           可以通过查看table_locks_waited和table_locks_immediate状态，来分析系统上的表锁的锁定问题，如果table_locks_waited的比较高，则说明存在着较严重的表级锁争用情况，应加以改善。查看表class2表锁的使用情况。

定期优化检查表

• 分析表

• 检查表

• 优化表  

•

分析表

           使用ANALYZE TABLE分析表的索引以便更有效地连接，其执行的任务和使用-a参数的myisamchk一样，对MyISAM，InnoDB和BDB都有效。其对Persons表进行分析的结果 。

检查表

             CHECK TABLE检查表是否有错误，它执行的功能与带-m参数的myisamchk函数的使用方法一样，其语法结构为：

         CHECK TABLE name [,name…]

                [CHANGED][EXTENDED][MEDIUM]

             CHANGED选项只检查自上一次检查后，改过或没有正常关闭的表。EXTENDED选项检查每个索引中的每个条目。MEDIUM检查标记为删除的列，是CHECKTABLE的默认情况。其对persons表进行检查的结果 。

优化表

           OPTIMIZE TABLE语句用于优化表，如果连续性地插入和删除记录，表会变得零乱,可以使用OPTIMIZETABLE 经行对表的优化，它可以合并表中删除的空间碎片。使用OPTIMIZE TABLE对Persons表进行优化的结果 。

其他方法优化

• 创建临时表

• 使用外键

•

创建临时表

           临时表在连接MySQL期间存在，当断开时，MySQL将自动删除表并释放所用的空间，当然也可以在连接的时候删除表并释放空间，创建临时表可以有效地节省存储空间。创建临表时给正常的CREATE TABLE语句加上TEMPORARY关键字。

使用外键

           外键可以使保证表的对应关系，锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。例如外键可以保证每一条销售记录都指向某一个存在的客户，在这里，外键可以把cust表中的CustID映射到sales表中CustID。任何一条没有合法CustID的记录都不会被更新或插入到sales表中。

综合练习

1.为表class2中的math列指定索引，并查看表的字段及其类型。