Developer Key Concepts

This topic describes the key concepts you need to understand to run commands or develop applications for a MemSQL database.

Indexes

MemSQL, like all SQL databases, allows the creation of indexes on tables, which speed up certain access patterns. As with MySQL, MemSQL supports two index type keywords: HASH and BTREE.

Hash Table Indexes

HASH indexes provide fast exact-match access to unique values. This means HASH indexes are a good choice for speeding up queries that use equality predicates, such as SELECT * FROM tbl WHERE id=3 OR id=714928.

On the other hand, HASH indexes cannot speed up range queries, such as SELECT * FROM tbl WHERE id > 714928 and HASH indexes cannot be used on non-unique columns in MemSQL.

MemSQL allows you to configure the number of buckets used by the hash table with the BUCKET_COUNT option. Just specify BUCKET_COUNT = N after the USING HASH syntax. The value is rounded to the nearest power of 2 (so BUCKET_COUNT = 20000 will create a hash index with 16384 buckets). Note that the BUCKET_COUNT must be between 512 and 2^30. A best practice is to set BUCKET_COUNT to around 1/2 the maximum number of rows you expect to be in the table. Setting the value too low will reduce the seek performance of the HASH index. Setting the value too high will waste memory on empty hash buckets.

Skip List Indexes

The default index type in MemSQL is a skip list. Skip lists in MemSQL are meant to replace the B-Tree indexes used by most other databases, including MySQL. Skip lists are optimized to run in memory as they can be implemented lock free and offer extremely fast insert performance. Like B-Trees, they offer an expected O(log(n)) lookup performance and can be traversed in sorted order.

Note

For compatibility, if you specify an index as BTREE, MemSQL will use a skip list.

Unlike B-Trees in MySQL, skip lists in MemSQL are uni-directional (singly linked). Each column in a compound skip list index can be specified as ascending (ASC) or descending (DESC). The default is ASC. Which one you pick will not impact lookup performance but it does impact scan performance depending on the direction the index is scanned. Scanning a skip list in reverse order is approximately twice as costly as scanning in forward order. So, if you have an ASC index and you run a query that would traverse the index in descending order (ORDER BY DESC for example), then the query will require a more expensive iteration than if the index were DESC.

Index Hints

MemSQL supports the following index hint syntax:

tbl_name [index_hint]

index_hint:
    USE {INDEX | KEY} (index_list)
  | IGNORE {INDEX | KEY} (index_list)
  | FORCE {INDEX | KEY} (index_list)

index_list:
    index_name [, index_name] ...
  • USE and FORCE hints force the use of one of the specified indexes to run the query. In MemSQL, there is no difference between a USE and FORCE hint.
  • IGNORE hints disallow the specified indexes from being used to run the query.

The EXPLAIN <query> statement can be used to show which indexes the query considers and which one it will actually use.

Distributed SQL Surface Area

MemSQL’s distributed system supports a large subset of the SQL surface area covered by single-box MemSQL, with the following limitations:

  • Both distributed and reference tables must have explicit primary keys.
  • An AUTO_INCREMENT column in a distributed table must be a BIGINT. Auto increment values are monotonically increasing on each aggregator but not consecutive across the cluster.
  • Every leaf-level subquery cannot contain more than one distributed table. For example, if both clicks and likes are distributed tables, you cannot directly join them together. If you do want to join these two tables together, you can join them by writing a subquery over each and joining the results of the subqueries. Note that each subquery completes on the aggregator, so you must take care to ensure that each subquery has a relatively small cardinality.
  • Distributed does not support unique keys (unless the primary key is a prefix of the unique key).
  • Distributed does not support multi-statement transactions
  • Distributed does not guarantee the read committed isolation level for cross shard write transactions. Furthermore, if a write query fails on any partition, the aggregator will not clean up intermediate results on other partitions. These semantics resemble the MyISAM storage engine in MySQL.
  • Distributed does not support ALTER TABLE.
  • Distributed does not support INSERT ... SELECT queries.
  • Distributed does not support UPDATE queries that modify the primary key.
  • Distributed does not support UPDATE...LIMIT or DELETE...LIMIT.

String Encoding and Collations

MemSQL’s default character set and collations deviate from MySQL. While MySQL has a default character set of latin1 and collation of latin1_swedish_ci, MemSQL defaults to a character set of utf8 and collation of utf8_general_ci.

We picked utf8 to support a wider set of alphabets out of the box. The trade off is that in some cases, the strings require more space to store. Utf8 includes ASCII/Latin-1 as a subset consisting of only the single-byte characters, as well as multi-byte Unicode characters. If you want fine-tuned control over the byte-length of your binary columns, use the BLOB and VARBINARY family of types.

MemSQL currently only supports the utf8 character set. Using a character set other then utf8 for a string column will results in an dev/unsupported feature error (see :doc:`unsupported). All utf8 collations supported by MySQL are also supported by MemSQL. Future releases of MemSQL will have full character set and collation support.

Please contact MemSQL support at support@memsql.com if you have any questions, or if you need a feature that is not currently supported.

Transactions and Isolation Levels

The only currently supported isolation level in MemSQL is READ COMMITTED. The SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL syntax is supported for compatibility with MySQL but has no effect on MemSQL and will result in an unsupported feature warning (see Unsupported Features). MemSQL supports snapshot isolation level for use by internal system transactions used to build database snapshots, but transactions at this isolation level are not exposed to users.

MemSQL only supports single query transactions. Every query begins and commits in its own transaction. The START TRANSACTION, BEGIN, COMMIT and ROLLBACK syntax is supported for compatibility with MySQL, but it has n o effect and will result in an unsupported feature warning (see Unsupported Features).

In the distributed system, transactions do not yet span partition boundaries. Therefore, read and write queries that span multiple partitions will use one transaction per partition. If a write query fails on one partition, MemSQL will rollback the query on that partition but not on other partitions.

Unsupported Features

MemSQL does not currently support all of the features of MySQL. In some cases, MemSQL can safely ignore unsupported features in a SQL statement and continue executing only the supported features. Please contact MemSQL support at support@memsql.com if you have any questions, or if you’d like to request a MySQL feature that is not currently supported.

warn_level System Variable

How MemSQL behaves when it encounters unsupported functionality is controlled via the warn_level system variable. The variable has two settings:

  • errors

    SQL statements with unsupported features will be rejected as errors. This is the most strict warn level. Use this level when your developing a new application on MemSQL and want to only use features fully supported by MemSQL.

  • warnings (default)

    Permits SQL statements that aren’t supported, but whose unsupported features can safely be ignored. Warnings will be issued when such queries are used. Use this mode when porting an existing application to MemSQL and to avoid having to change the application to not use unsupported features.

The warn_level variable is set just like other global system variables:

set global warn_level="errors";

SQL Statements and Data Types

The section MemSQL Reference describes the SQL statements and data types that MemSQL does and does not support.

Other Features

In addition, MemSQL does not support features such as:

  • Views
  • Prepared Queries
  • Stored procedures
  • User Defined Functions
  • Triggers
  • Foreign keys
  • Charsets other than utf8
::...
免责声明:
当前网页内容, 由 大妈 ZoomQuiet 使用工具: ScrapBook :: Firefox Extension 人工从互联网中收集并分享;
内容版权归原作者所有;
本人对内容的有效性/合法性不承担任何强制性责任.
若有不妥, 欢迎评注提醒:

或是邮件反馈可也:
askdama[AT]googlegroups.com



自怼圈/年番新

DU21.7
关于 ~ DebugUself with DAMA ;-)


关注公众号, 持续获得相关各种嗯哼:
zoomquiet


粤ICP备18025058号-1
公安备案号: 44049002000656 ...::