High performance MySQL-- 笔记

// High Performance MySQL 这本书我最近也在看。 // 好记性不如烂鼻头，得把一些记下来了。 // 这个笔记应该不会很条理，因为我会把书中所有我认为应该几下的东西记下 // 1, MySQL stores each database(also called a schema) as a subdirectory of its data directory in the underlying filesystem. 因此：在类 Unix 系统中 MySQL 对数据库名和表名区分大小写，在 Windows 系统中不区分。 2, 查看一个表使用的引擎： SHOW TABLE STATUS LIKE ‘tb_name' 3, 修改表的引擎 ALTER TABLE 'tb_name' ENGING = InnoDB; (修改引擎时表会被加上 读锁，因此在频繁修改的表上应慎重考虑修改引擎) (好的方法是：先 mysqldump 出所有数据，然后在到处的文本文件里修改表的引擎) (或者使用 INSERT INTO ... SELECT ...) CREATE TABLE innodb_table LIKE myisam_table; ALTER TABLE innodb_table ENGINE = InnoDB; INSERT INTO innodb_table SELECT * FROM myisam_table; // ------------------------------ // CHARPTER 4: Query Performance Optimization 1, most bad queries can be changed to access less data. 是否获取不需要的数据 A,是否或许不需要的行。使用 LIMIT B,是否或许多连接的所有列。指定列的名字。 C,是否使用 SELECT *。 2, look for queries that examine too much data while generating results 3, another way to slice up a query is to divide and conquer, keeping it essentially the same but running it in smalller 'chunks' affect fewer rows each time. // transactional storage engines may benefit from smaller transactions. 4, decompose a join by running multiple single-table queries instead of a multiple join, and then performing the join in the application. // //--- 1, SHOW FULL PROCESSLIST // each MySQL connection, or thread, has a state that shows what it is donging at any given time. //-- 1, Before even pasring a query,MySQL checks for it in the query cache,if the cache is enabled. // 在 MySQL 解析一个查询之前，它会现检查这个查询是否已经缓存了； // 如果缓存了，还需要检查是否有权限。