mysql 索引 及优化1

mysql 中的索引是提高select性能的很好途径，不同的引擎会有不同的索引要求，如数量 索引方式等都不同，myisam innodb 为btree 索引，一个表可以建最多16个索引，explain 用来查看索引产生结果的，explain select * from city where city ='nan'; 创建一个索引 create index_name on table_name (column_name(num));

索引设计的一些原则:最适合建立索引的列是出现在where子句中的列。

使用唯一索引，就是说索引的基数越大索引效果越好，就像存储出生日期与性别的列一样，最好建在出生日期的列上，

使用短索引，在对字符串进行索引的时候最好要指定一个前缀长度，

存储引擎类型:myisam mysql默认的存储引擎 不支持事物也不支持外键，访问速度快，每个MYIAM表都有三个文件来存储相关的信息，.frm存储表定义，.myd数据结构 myi 存储索引，数据文件与索引文件可以放在不同的目录，平均分配 IO，表损坏一般先用check table 检查一下，让后用repair table来修复，myisam支持三种不同的存储格式，静态表 动态表 ，和压缩表，默认的为静态表，他的优点是存储速度快，但比其他两种都占的空间大，而且他会默认把空的位置补全空格，在读取的时候，在去除空格，这就有可能我们在程序中有时候要存储空格，不小心可能被这种机制给去除了，例如存入如下数据的时候可能"    abcd","fgeg   ";用length来显示他们的长度 分别为8, 4可见他去除的是字符后面的空格，而前面的将不会被去除，动态表是表的空间可变，但频繁的增删可能导致过度的文件碎片，这要定期进行optimiz table  进行优化，而且数据损坏难恢复。 Innodb存储引擎支持外键，事物回滚等操作，但速度有些慢，下面是外键

create table country(
country_id smallint unsigned not null auto_increment,
country varchar(50) not null,
last_update timestamp not null default current_timestamp  on update current_timestamp,
primary key (country_id))engine = Innodb default charset = utf8;
create table city (
city_id smallint unsigned not null auto_increment,
city    varchar(50) not null,
country_id smallint unsigned not null,
last_update timestamp not null default current_timestamp on update current_timestamp,
primary key (city_id),
key idx_fk_country_id (country_id),
constraint 'fk_city_country' foreign key (country_id) references country(country_id) on delete restrict on update cascade
)engine = Innodb default charset=utf8;

 子表外键制定是  on delete restrict on update cascade 方式的那么在主表删除记录的时候，如果子表有对应的记录，则不允许被删除，主表在更新的时候如果字表有对应的则自动更新，可以关闭外键约束 set foreign_key_checks=0

text 与 blob 的不同 两者都是用来存储大的数据的，不同是blob可以用来保存二进制的，像相片，他们会引起一些性能问题特别是当执行了大数据操作后会给数据库留下很多空洞，所以要定期对表进行整理optimize talbe  ,可以用repeat函数来测试插入大量数据repeat("date" number) 就是把date 插入表中的次数number。

字符集的选用及修改：如果处理汉字较多而且要求速度那最好选用gbk 应为utf8 比较大他保存汉字用的是3个字节，而gbk是两个字节，相反用英文最好别用gbk 因为他保存的都为两个字节，浪费空间，修改：1导出表结构mysqldump -uroot -p --default-character -set=gbk -d databasename>createtab.sal

2,手工修改createtab.sql表中，表结构定义中的字符集为新字符集，3，确保记录不再更新，导出所有记录。4，打开data.sql文件将set names latin1 =>set names gbk;

5，用新的字符集创建数据库 creat database databasename default charset gbk;6,创建表 执行文件 ，导入数据。

sql安全问题:sql注入攻击  使用PrepareStatement +Bind-variable 来实现就是预处理语句，用正则表达式来处理，

mysql 优化：同样的语句还有SHOW VARIABLES;，SHOW STATUS是查看MySQL运行情况，和上面那种通过pma查看到的信息基本类似。

SHOW VARIABLES
SHOW VARIABLES是查看MySQL的配置参数，还可以使用类似SHOW VARIABLES LIKE ‘Key%’

SHOW PROCESSLIST
SHOW PROCESSLIST是查看当前正在进行的进程，对于有锁表等情况的排查很有用处。一般情况下，打开MySQL的慢查询记录同样有利于排查。

SHOW OPEN TABLES
SHOW OPEN TABLES是显示当前已经被打开的表列表。

mysqladmin status
使用MySQL自带的mysqladmin 工具查看status，使用以下命令
mysqladmin -uroot –password=’password’ status
显示的结果如下：
Uptime: 87117 Threads: 1 Questions: 5481626 Slow queries: 16 Opens: 2211 Flush tables: 1 Open tables: 512 Queries per second avg: 62.923
另外可以添加 -i 5 参数，让其每五秒自动刷新之。
mysqladmin -uroot –password=’password’ status -i 5

mysqladmin extended-status
同样的可以使用mysqladmin -uroot –password=’password’ extended-status来查看更多的MySQL运行信息，这种方式和第一种查看的信息基本

数据导出导入：

设计思路：

异常解决:

表损坏

密码丢失

数据死锁

 

评论

1 楼 myfreespace 2010-04-28