mysql 高性能

1.字符串的长度定义不是字节数是字符数(char, varchar) 2.二进制字符串存储的是字节码不是字符(binary,varbinary \0填充,检索时不会去掉填充值) 3.字符串类型作为标识列,不太理想,因为它们消耗空间,并且通常比数字类型慢 4.myisam 对字符串使用压缩索引,会导

1.字符串的长度定义不是字节数是字符数(char, varchar)

2.二进制字符串存储的是字节码不是字符(binary,varbinary \0填充,检索时不会去掉填充值)

3.字符串类型作为标识列,不太理想,因为它们消耗空间,并且通常比数字类型慢

4.myisam 对字符串使用压缩索引,会导致查询慢

5.适当的冗余

6.innodb 使用的是B+tree(每一个叶子节点都包含指向下一个叶子节点的指针,从而方便叶子节点的范围遍历)

7.只有当索引帮助存储引擎快速查找到记录的好处大于其带来的额外工作的时候,索引才是有效的.

8.前缀索引, 比值为(0.0312)是相对合适的

9.innodb 没有主键索引,会有一个唯一的非空索引代替,不然就会隐式定义一个主键来作为聚簇索引,

10.二级索引(非聚簇索引) 可能比想象的更大,因为在二级索引的叶子节点包含了引用行的主键列

11.二级索引访问需要两次索引查找,而不是一次(二级索引的叶子节点保存的不是指向航的物理位置的指针而是行的主键值)(innodb 自适应哈希索引可以减少这样的重复工作)

12.innodb 在二级索引上使用共享(读,s)锁 ,但是访问主键索引需要排他(写)锁.

13.check table

14.优化是否请求了不需要的数据;多次查询相同的数据

15.衡量查询开销:响应时间(服务时间+排队时间),扫描行数,返回的行数(mysql 慢日志查询)

16.explain 的时候 Extra=using index 表示用了覆盖索引(包含了所有满足查询需要的数据的索引称为覆盖索引(covering index)

17.分解关联查询

18.mysql 客户端和服务器之间的通信协议是半双工的,意味着在任何一个时刻,要么是服务器像客户端发送数据,要么是客户端向服务器发送数据,这两个动作不能同时发生.

19.查询语句很长的时候:max_allowed_packet

20.php 的 mysql_query()将结果集缓存到了内存中,mysql_unbuffered_query()不会将结果集缓存到内存中.

21.查看当前状态:show full processlist;

sleep 线程正在等待客户端发送新的请求                                                                                
query 线程正在执行查询或者正在将结果发送给客户端                                                                    
locked 该线程正在等待表锁                                                                                           
analyzing and statistics 线程咋横在收集存储引擎的统计信息,并生成查询的执行计划                                      
copying to tmp table [on disk] 线程正在执行查询,将结果集复制到临时表中                                              
sorting result 线程对结果集进行排序  
sending data :线程可能在多个状态之间传送数据,或者在生成结果集,或者在向客户端发送数据                                

22.mysql 中 In并不完全等同于 or, mysql将 in 列表中的数据先进行排序,然后通过二分查找的方式来确定列表中的值是否满足条件,这是一个 o(log N)的复杂度的操作,等价的转换成 or查询的复杂度>

23.explain extended select …; show warnings;自己试试,

24.分区表:
进行 insert,update,delete,select 查询时都会先打开并锁住所有的底层表,在相应的底层表中进行操作..
25.分区表类型:
partition分区字句中可以使用各种函数,但是有一个要求,表达式的返回值要是一个确定的整数,且不能是一个常数.
键值分区,哈希分区,列表分区

注意: 在数据量超大的时候,B-tree 索引就无法起作用,除非是索引覆盖查询,否则数据库服务器需要根据索引扫描的结果回表,查询所有符合条件的记录.

26.分区策略:
全量扫描数据,不要任何索引:根据分区的规则大致定位需要的数据位置,只要能够使用 where 条件,将需要的数据限制在少数分区中,则效率是很高的.
索引数据,并分离热点:数据有明显的热点,除了这部分其他的很少访问,就可以将热点数据单独放在一个分区,让它有机会缓存到内存.

27.分区策略重要的假设:
查询能够过滤掉很多额外的分区
分区本身并不会带来很多额外的代价
导致出现问题的状况:
1.null 值会使分区过滤无效
2.分区列和索引列不匹配
3.选择分区的成本可能很高
4.打开并锁住所有底层表的成本可能很高
5.维护分区的成本很高

28.分区限制:

1.所有分区都必须使用相同的存储引擎
2.分区函数中可以使用的函数和表达式也有限制
3.某些存储引擎不支持分区
4.myisam 分区,不能在使用 load index into cache 操作
5.myisam,使用分区表时,需要打开更多的文件描述符

dht:distributed hash table 分布式哈希表

29,合并表 略

30.视图

5.0之后引入视图
合并算法(尽可能的使用);临时表算法
视图中包含了 group by,distinct,任何聚合函数,union,子查询等,
    只要无法在原表记录和视图简建立一一映射的场景,mysql 都将使用临时表算法来实现试图
针对视图:select * from view_name; select_type deriver 表示采用了采用了临时表算法实现
使用临时表算法实现的视图都无法被更新
mysql 不允许在视图上创建任何触发器
innodb 外键约束

31.mysql 允许通过触发器,存储过程,函数的形式存储代码. 存储过程和存储函数都被同称为存储程序

存储过程,触发器,游标
在存储程序中保留注释
绑定变量
用户自定义函数

32.插件
存储过程插件,后台插件,information_schema,全文解析插件,审计插件,认证插件,

33.字符集和校对

创建时,默认设置;
服务器和客户端通信时设置 客户端按照 character_set_client传输数据和 sql
服务器接收时,转换, character_set_connection
服务器返回错误信息 character_set_result

34.load data infile 数据库总是将文件中的字符集按照 character_set_database 解析,可以使用子句 character set 来设定字符集

35.select info outfield 的结果集不做任何转码的写入文件

36.不同字符集和校对规则之间的转换,可能会带来额外的系统开销

37.全文索引:支持对各种字符内容的搜索,(char, carchar,test)也支持自然语言搜索和不尔搜索

38.分布式(xa)事务;内部xa 事务,外部 xa 事务

39.查询缓存
mysql 命中缓存:缓存存放在一个引用表中,通过一个哈希值引用,这个哈希值包含了查询本身,当前要查询的数据库,客户端协议的版本等.

如果查询中包含了一个不确定的函数,mysql 是不会检查查询缓存的.

40,查询缓存存放在内存:
当有查询结果需要缓存的时候,mysql 会从大的空间块中申请一个数据块用于存储结果,这个数据块需要大于 query_cache_min_res_unit 的配置,即使查询结果小于这个,仍然至少需要这个参数的空间

缓存碎片,内存不足,数据修改都会造成缓存的失效

41.配置和维护查询缓存:
query_cache_type,query_cache_size,query_cache_min_res_unit,query_cache_limit,
query_cache_wlock_invalidate,

原文地址：mysql 高性能, 感谢原作者分享。